残業を減らす！Officeテクニック
凡ミスを減らすExcel関数の実践テクニック！ すぐに使えるSUBTOTAL関数の活用方法
2023年5月22日 06:55
社会人になってから本格的にExcelを使いはじめた人も多いでしょう。実務で使う関数は限られており、基本的な関数をおさえておけば十分とも言えますが、もしかすると我流の使い方で凡ミスの原因を作ってしまっている可能性もあります。今回は、すぐに実践できるSUBTOTAL関数のテクニックをまとめました。
小計が含まれる時はSUBTOTAL関数を使う
多くの人が最初に覚えるSUM関数にも落とし穴があります。連続したセル範囲のほか、「,」で区切ったセルを合計できるため、以下のような表を作成してしまいがちです。
間違いではありませんが、実務では効率の悪い表の構造です。合計するセルを正しく参照できているか気を付けなければなりません。このように「小計」が含まれる表では、SUBTOTAL関数の利用をおすすめします。
「SUBTOTAL」（小計）のための関数と考えてしまって構いません。構文は以下の通りです。ひとつめの引数［集計方法］によって動作が切り替わり、「9」もしくは「109」を指定することで、指定するセル範囲を合計します。
先ほどの表に含まれるSUM関数の数式をSUBTOTAL関数で書き換えると以下のようになります。SUBTOTAL関数で合計する際、SUBTOTAL関数の入力されたセル（小計）は除外されるのがポイントです。SUM関数の参照するセル範囲を間違えないように気を付けながらクリックしなくてもいいのです。
小計と合計を示したい各セルに入力した数式は以下のようになります。
- セルD5（小計）：=SUBTOTAL(9,D2:D4)
- セルD9（小計）：=SUBTOTAL(9,D6:D8)
- セルD13（小計）：=SUBTOTAL(9,D10:D12)
- セルD14（合計）：=SUBTOTAL(9,D2:D13)
ただし、SUM関数とSUBTOTAL関数の併用は厳禁です。SUBTOTAL関数が除外するのは、SUBTOTAL関数が入力されたセルのみ。SUBTOTAL関数が参照するセル範囲にSUM関数が含まれていると正しく集計されません。
フィルターとの組み合わせにもSUBTOTAL関数は有効
データの数を数える時はCOUNTA関数を使いますが、フィルターをかけてもレコード数が変わらない……、と悩んだことはありませんか？ これはCOUNTA関数の正常な動作で、フィルターの有無に関わらず、指定したセル範囲のデータがカウントされます。
このような場合も、SUBTOTAL関数が有効です。引数［集計方法］に「3」もしくは「103」と指定します。
COUNTA関数と異なり、SUBTOTAL関数では表示されているデータのみがカウントされます。
「9」と「109」、「3」と「103」の違い
SUBTOTAL関数の引数［集計方法］には「9」と「109」、「3」と「103」のように、1桁と3桁の指定方法があります。手動で非表示にした行を数えるかどうかの違いです。
行番号を右クリックして［非表示］にした行が、指定したセル範囲に含まれる場合、3桁（「109」や「103」）では集計の対象外となります。つまり、フィルターでの絞り込みも［非表示］の行も除いて、とにかく“表示されている行のみ”を集計の対象とする場合は3桁で指定してください。
- セルE1（集計方法「3」）の数式：=SUBTOTAL(3,B2:B10)
- セルE2（集計方法「103」）の数式：=SUBTOTAL(103,B2:B10)
上記のように数式を入力すると以下のように結果が変わります。
