残業を減らす！Officeテクニック

凡ミスを減らすExcel関数の実践テクニック！すぐに使えるSUBTOTAL関数の活用方法

2023年5月22日 06:55

SUBTOTAL関数で凡ミスを回避しよう

　社会人になってから本格的にExcelを使いはじめた人も多いでしょう。実務で使う関数は限られており、基本的な関数をおさえておけば十分とも言えますが、もしかすると我流の使い方で凡ミスの原因を作ってしまっている可能性もあります。今回は、すぐに実践できるSUBTOTAL関数のテクニックをまとめました。

小計が含まれる時はSUBTOTAL関数を使う

　多くの人が最初に覚えるSUM関数にも落とし穴があります。連続したセル範囲のほか、「,」で区切ったセルを合計できるため、以下のような表を作成してしまいがちです。

SUM関数で求めた小計を対象に、さらに合計行でSUM関数を利用している

　間違いではありませんが、実務では効率の悪い表の構造です。合計するセルを正しく参照できているか気を付けなければなりません。このように「小計」が含まれる表では、SUBTOTAL関数の利用をおすすめします。

　「SUBTOTAL」（小計）のための関数と考えてしまって構いません。構文は以下の通りです。ひとつめの引数［集計方法］によって動作が切り替わり、「9」もしくは「109」を指定することで、指定するセル範囲を合計します。

SUBTOTAL関数の構文。［集計方法］によって動作が切り替わる。「1」～「11」、「101」～「111」の数値を指定可能だが、合計の「9」とカウントの「3」（後述）を覚えておけば十分。［参照］には集計するセル範囲を指定する

　先ほどの表に含まれるSUM関数の数式をSUBTOTAL関数で書き換えると以下のようになります。SUBTOTAL関数で合計する際、SUBTOTAL関数の入力されたセル（小計）は除外されるのがポイントです。SUM関数の参照するセル範囲を間違えないように気を付けながらクリックしなくてもいいのです。

セルD5（小計）の数式を「=SUBTOTAL(9,D2:D4)」と書き換える。同様にセルD9、D13もSUBTOTAL関数に修正しておく

合計行に入力する数式は「=SUBTOTAL(9,D2:D13)」となる。SUBTOTAL関数の入力されたセル（小計）は除外されるので、合計行のSUBTOTAL関数の参照するセル範囲は「D2:D13」として構わない

　小計と合計を示したい各セルに入力した数式は以下のようになります。

正しく合計されていることがわかる

　ただし、SUM関数とSUBTOTAL関数の併用は厳禁です。SUBTOTAL関数が除外するのは、SUBTOTAL関数が入力されたセルのみ。SUBTOTAL関数が参照するセル範囲にSUM関数が含まれていると正しく集計されません。

　データの数を数える時はCOUNTA関数を使いますが、フィルターをかけてもレコード数が変わらない……、と悩んだことはありませんか？これはCOUNTA関数の正常な動作で、フィルターの有無に関わらず、指定したセル範囲のデータがカウントされます。

COUNTA関数を使ってデータを数えている。数式は「=COUNTA(B2:B10)」

フィルターで絞り込んでもCOUNTA関数の結果は変わらない

　このような場合も、SUBTOTAL関数が有効です。引数［集計方法］に「3」もしくは「103」と指定します。

COUNTA関数の代わりにSUBTOTAL関数を使って「=SUBTOTAL(3,B2:B10)」と指定した

フィルターで絞り込むと、表示されているデータのみが数えられる

　COUNTA関数と異なり、SUBTOTAL関数では表示されているデータのみがカウントされます。

　SUBTOTAL関数の引数［集計方法］には「9」と「109」、「3」と「103」のように、1桁と3桁の指定方法があります。手動で非表示にした行を数えるかどうかの違いです。

　行番号を右クリックして［非表示］にした行が、指定したセル範囲に含まれる場合、3桁（「109」や「103」）では集計の対象外となります。つまり、フィルターでの絞り込みも［非表示］の行も除いて、とにかく“表示されている行のみ”を集計の対象とする場合は3桁で指定してください。

　上記のように数式を入力すると以下のように結果が変わります。

ここでは、5～7行目を手動で非表示にしている。引数［集計方法］を3桁で指定した場合、行番号を右クリックして［非表示］にした行も集計対象外となる