フィルターで抽出した値だけを集計したいことってありませんか？
フィルターでデータ抽出をして、その抽出された値だけを集計したい・・・そんなことってありませんか？
例えば、合計値を求める関数に、SUM関数がありますが、このSUM関数を使ってある一列全体の合計値を計算したとします。
これはこれで、問題ないでしょう。
問題となるのは、フィルターでデータを抽出することが予定されるような場合には、注意が必要です。
どんな問題が生じるのかは、下記の「フィルター＋SUM関数の失敗例」で見ていきましょう。
結論から言うと、フィルターで抽出した値だけを集計するには、SUBTOTAL関数を使います。
「関数」というと敬遠される方もいらっしゃるかもしれませんが、図を多目に書いて行きますので、はじめての方にもわかりやすい内容にしていきますね。
まずは、トライしてみましょう。もし、わかりにくい表現があったらコメント欄にご意見をいただけるとうれしいです。
SUBTOTAL関数の使い方（SUM関数との違い）
フィルター ＋ SUM関数の失敗例
図１のような表で、売上を合計するためには・・・。
合計と言えば「SUM関数」を一番最初に思い浮かぶかもしれません。
もちろん、フィルターでデータを抽出することが予定されていない表の場合には、「SUM関数」を使うのが一般的でしょう。
ところが、フィルダーで条件を絞り込んだ場合の「合計」としては、困ったことが起きます。
どんな困ったことが起きるのでしょう。
たとえば・・・
図１では、２日間の果物の売上を表にし、セルＦ１にSUM関数で売上合計を出しました。
２日間の果物全体の売上合計は「10,504」円です。
ここまでは問題ありませんよね。
＜図１＞
問題は次です。
図２では、フィルターで「りんご」だけを絞り込みました。
ところが、売上合計を見ると、２日間の果物全体の売上合計「10,504」円のままになってしまっています。
ここは、りんごだけの売上合計を表示させたいところですよね。
＜図２＞
SUBTOTAL関数を使ってみよう
このように、フィルターでデータを抽出することが予定されている表の場合は、SUBTOTAL関数を使うことで解決できます。
実際にSUBTOTAL関数を使ってみて、先ほどのSUM関数との違いを見てみましょう。
今度は、図３のセルＦ１には「SUBTOTAL関数」を使用し、売上合計を計算するようにしました。
２日間の果物全体の売上合計は「10,504」円。
＜図３＞
次に、図４のように、フィルターを使って「りんご」だけを絞り込んでみました。
フィルターで「りんご」を抽出したと同時に、売上合計は、りんごだけの売上合計「1,760」円に表示が変更されました。
＜図４＞
SUBTOTAL関数の書式
ここで、SUBTOTAL関数の書式を確認しておきましょう。
【関数は、「関数名」と「引数」から成り立っています】
「SUBTOTAL」は関数名。
関数名の後のカッコ（ ）に入っているのが引数（ひきすう）です。
引数の要素は、使用する関数名によって異なります。
【「引数」は、関数によって異なります】
SUBTOTAL関数の引数は「集計方法」と「範囲」です。
「集計方法」は、数字で指定します。この「集計方法」を数字で指定する方法については、下の「SUBTOTAL関数でできる集計の種類」のところで詳しく見ていきましょう。
「範囲」は連続したセルの固まりであれば「範囲１」だけでＯＫです。「範囲２」以降は不要です。
SUBTOTAL関数でできる集計の種類
ご覧いただいたように、SUBTOTAL関数は、フィルターを使ってデータを抽出したときに、その抽出した値だけを集計するための関数です。
その際の「集計」には、「合計値を求める」(SUM関数の代わり）ということの他にも、様々な集計をする必要あるでしょう。
SUBTOTAL関数でできる集計の種類を下の表にまとめました。
例えば、りんごの売上合計の他、りんごを買っていった人の件数（行数）を知りたければ、「データの個数を求める」＝「3」を引数の「集計方法」とすることになります。
=SUBTOTAL(3,C3:C27)
今回使用したのは、「合計値を求める」(SUM関数の代わり）ことが目的だったので、「9」を使用しました。
ワンポイント
「SUM関数」と「SUBTOTAL関数」の違い、「SUBTOTAL関数」を使うことのメリット、なんとなくでもご理解いただけましたでしょうか。
ところで、特に大き目な表の場合には、合計欄は一番下よりも一番上に配置したほうが使いやすいですよ。フィルターで抽出するたびに集計欄の位置が変化してしまい、スクロールして探さなければならないという手間が省けます。
常に同じ位置に合計欄が配置されるているので、見やすいデータ作成のためのひとつのポイントになります。
といっても、もちろん、決算書や社内文書などで「合計欄はココ！」といったように、フォームがきちんと決まっているものの場合は、そのフォーム通りの配置をお願いしますね。
いかがでしたでしょうか。
このサイトでは、Excelを使い始めの方にもお役に立てる情報をシェアできるよう心掛けていきたいと思います。
では、また。
コメント