【Excel】SUBTOTAL関数の使い方【基本から応用まで】

SUBTOTAL関数は合計や平均などをに使う関数です。特に合計に関しては小計の要素を考えると非常に使い勝手が良い関数です。今回はこのSUBTOTAL関数について見てみましょう。

SUM関数との違い

検索関数に関しては様々な形があるので、作りたいものによって使い分けられるようにしておくと便利です。

今回も実際に使用したものを練習用に貼っておくのでご活用ください。

SUBTOTAL関数は

＝SUBTOTAL(集計方法,参照)

となります。
集計方法には1～11の数字が入り、それぞれに対応した関数によって参照元のデータに数式を当てはめていきます。

ただ、合計や平均ならばSUMやAVERAGE関数があるのでこれだけでは必要性が分かりづらいので実際の使い方を見ていきます。

地域別支店合計と全支店合計を出してみます。

SUM関数だと小計は各地域の範囲をSUM(F2:F4)やSUM(F6:F10)、SUM( F11:F14)とSUM関数で括ります。
一方で全支店合計を出すには、SUM(F5,F11,F15)と範囲を指定しなければなりません。さらに地方が増えた際にはいちいち全支店合計は範囲を追加する必要があります。

これに対して、SUBTOTAL関数なら小計欄はSUBTOTAL(9,F2:F4)やSUBTOTAL(9,F6:F10)、SUBTOTAL(9,F11:F14)となりますが最後の全支店合計はSUBTOTAL(9,F2:F15)になります。

SUBTOTAL関数を入れていれば、合計の範囲は全部の支店の範囲を選択していれば純粋な全支店合計になります。
一方でSUM(F2:F15)としてしまうと、各支店の合計と一緒に小計の欄も合計してしまう為、2重の計算となってしまいます。

注意しなければならないのは、小計欄をSUM関数で計算してしまうとSUBTOTAL関数では除外できずに小計も含めての計算になってしまいます。SUBTOTAL関数を使うならSUBTOTAL関数で統一をする必要があります。

SUBTOTAL関数の集計方法①は平均となっているので、今度はAVERAGE関数との比較をしてみます。

先程のSUM関数と同様に、AVERAGE関数では平均する部分が2重で計算されてしまうので全支店平均もぶれてしまいます。

今回は参照している範囲の値の最小値と最大値の差が大きくないのでそこまで大きくぶれませんが、桁が1つ2つと変わるとそれだけ平均もぶれやすくなってしまうので、平均範囲をミスしないようにするためにもSUBTOTAL関数のほうが安全とも言えます。

集計方法に1～11で集計方法を選択するというのがSUBTOTAL関数の数式の形です。
そしてExcelはデータ集計・分析ソフトですが、一口にデータといっても合計のほうが伝わりやすかったり、平均のほうが伝わりやすかったり様々です。

その都度「合計」なのか「平均」なのか「最大・最少値」を探すのかによって数式を変更するのが面倒なので検索関数を使って集計方法をセル操作1つで簡単に変更できるようにします。

このような表を用意しておきます。必ず機能の種類が1～11と一致するようにしてください。
必要ないものに関してはリストから外しても問題ありません。
以下のような形で大丈夫です。

続いてデータを表示させたいところに関数対応表のリストをドロップダウンリストで設定をしておきましょう。

この表であれば、情報収集したいセルの範囲を変化させられるようにしながら、収集する項目を選びつつ項目に併せて平均なのか合計なのかを算出できるようにしています。

E7セル(検索結果を出すセル)の数式は以下のようになっています。

=SUBTOTAL(VLOOKUP(E18,SUBTOTAL関数表!$A$2:$B$12,2,FALSE),(OFFSET($A$2,MATCH($E$3,$A$3:$A$33,0),MATCH($E$5,$B$2:$D$2,0),E16-E15+1,1)))

VLOOKUP関数でE6(平均などの算出方法)を検索値にして、検索範囲である関数一覧表から対応した検索方法の数字をSUBTOTAL関数に埋め込むという意味ですね。

関数や機能を組み合わせればこういったデータ集計も簡単に出来るようになります。使いこなしていきましょう。