Excelを利用しているとフィルター機能を使って条件を絞った上で合計を算出したい場面があると思います。
その時に利用するEXCELの関数は、「SUBTOTAL関数」です。
合計値を算出する関数といえば、SUM関数が有名ですし、EXCELを始めた方は誰しもが最初に覚える関数とも言えるのではないでしょうか。そのためフィルター機能を使って合計値を算出する際にもSUM関数を利用することを考えてしまいがちですが、SUM関数ですとフィルターで隠れてしまっているセルの数値も合計されてしまいます。
もしSUM関数を使用するのであれば、SUMIF関数を使うことになり、条件式も多少複雑になってしまいます。しかし、フィルター機能を使った上で見えている部分だけの合計をするにはSUBTOTAL関数を使用されるのをオススメしますので、使い方をわかりやすく説明します。
SUBTOTAL関数とは
SUBTOTAL（サブトータル）関数とは、フィルター機能を使って絞られた状態で合計を算出したい場合に使うオススメの関数です。SUM関数=合計値を算出する関数ということは誰しもが知っていると思いますし、よく使用する関数ですね。
しかしSUM関数は、フィルター機能を使って絞った条件上では、フィルターで隠れてしまった部分も合計値に算出されてしまいます。そこでフィルターで絞った状態、つまり見えているセルだけの合計値を求める場合にはSUM関数ではなく、SUBTOTAL関数を使えば一発で算出することができます。
=SUBTOTAL（集計方法, 範囲1, ・・・)
式としては上記を使うのですが、一番最初に入れる「集計方法」には自分が算出したい方法（合計や平均など）に合わせて、該当の数字を入力する必要があります。
- 1番：平均を計算
- 2番：セルの数（数値のみ。COUNT関数の働き）
- 3番：セルの数（数値・文字列など全て。いわゆるCOUNTA関数の働き）
- 4番：最大値を計算
- 5番：最小値を計算
- 9番：合計を計算
ちなみに合計であれば「9」、平均であれば「1」という感じに入力します。全て覚える必要はないですが、自分の求めたい数字は何かを調べた上で決定してください。
範囲に関しては算出したいセルの範囲になります。
SUBTOTAL関数の使い方
上記の例で実際に見てみましょう。
19行目の「合計」は現在「D5:D17」のセルの合計をSUM関数を使用して算出（118,600円）しています。
式としては、=SUM(D5:D17)となります。
ここにフィルター機能を使って「食料代」だけで絞ってフィルターをかけた状態でSUM関数で範囲を指定しても同じ結果となってしまいました。
こちらのD20にセルを合わせると数式が「=SUM(D5:D17)」となっており、フィルターが隠れている部分も計算内に入ってしまうのがSUM関数です。
ではこれをSUBTOTAL関数に変更した場合を見てみましょう。
D20を「=SUM(D5:D17)」から「=SUBTOTAL(9, D5:D17)」に変更した所、合計値が11,500円となり、フィルターで見えているセルだけの合計値が算出できました。
このようにSUBTOTAL関数はフィルターを使用した際に合計値や平均値など算出したい場合に、使えるEXCEL関数となります。
SUBTOTAL関数で平均を算出方法
SUBTOTAL関数は、合計値を出す場合は、集計方法＝9にしました。
もしフィルターをかけた状態で平均値を出したい場合には、SUBTOTAL関数の集計方法を1番にすることで算出できます。
C21のセルに「食料代の平均」という形でD21にSUBTOTAL関数で算出しました。金額は2,875円となりましたが、その際の計算式は以下となります。
=SUBTOTAL(1,D5:D17)
このように集計方法の番号を1番に設定するだけでフィルターで見えているセル範囲の平均を算出できます。
SUBTOTAL関数でセルの数の算出方法
次はSUBTOTAL関数は、フィルターをかけた際にセルの数が多い場合には目視でセル数をカウントするのが大変です。その場合には、COUNT関数を使用するのではなく、SUBTOTAL関数を使用します。その際のセル数の集計方法は、集計方法＝2にします。
C22のセルに「食料代のセル数」という形でD22にSUBTOTAL関数で算出しました。セルの数は4となりましたが、その際の計算式は以下となります。
=SUBTOTAL(2,D5:D17)
このように集計方法の番号を2番に設定するだけでフィルターで見えているセル数を算出できます。
SUBTOTAL関数で最大値を算出方法
SUBTOTAL関数は、フィルターをかけた際の「合計値・平均値・セル数」についてみてきましたが、次は選択したセル範囲で最大となるものが何かを知りたい場合のやり方です。先ほど同じように集計方法の番号を変更するだけなのですが、最大値の場合は「4番」にします。
=SUBTOTAL(4,D5:D17)
こちらでは最大値が5,000円と算出されました。とても簡単ですね。
次はフィルターをかけた際のセル範囲内の最小値の算出方法です。
SUBTOTAL関数で最小値の算出方法
SUBTOTAL関数でフィルターをかけた際の最小値にお算出方法は、「5番」にするだけです。
=SUBTOTAL(5,D5:D17)
こちらで最小値が1,500円と算出されました。番号を指定するだけで簡単に算出ができました。
SUBTOTAL関数の他の集計方法は何？
SUBTOTAL関数の集計方法でよく使用されるものをご紹介しましたが、他にも集計方法の番号は以下があります。
- 標準偏差：7番
- 不偏分散：10番
標準偏差や不偏分散を算出するのは稀だと思います。おそらくマーケターの方やデータアナリストの方なら使用する場面があるとは思いますが、もし気になった方は試してみてください。
SUBTOTAL関数とは？フィルターで見えている部分を合計のまとめ
SUBTOTAL関数についてわかりやすく説明しましたが、いかがでしたでしょうか。
フィルターでセルを条件で絞った場合にSUM関数やAVERAGE関数、MAX関数、MIN関数を使うとフィルターで見えているセル範囲での算出が難しい場合あがあります。
その際にはSUBTOTAL関数を使用するのですが、集計方法の番号を算出したい該当番号を指定するだけです。数式も非常にわかりやすいので是非使用してみてください。
コメント