記事の詳細
フィルター後の計算も楽々!SUM関数よりも合計値を算出するのにとても便利な関数
こんにちは。
今日は久しぶりのExcelネタで、SUM関数とSUBTOTAL関数についてです。
「SUM関数は知っているけれど、SUBTOTAL関数は聞いたことない」
という方が多いかもしれませんね。
でも、これからはSUM関数を使わず、SUBTOTAL関数を使うようになるかもしれません。
なぜなら
とても便利だから!
今回のブログの見出しはコチラです!
SUM関数とは
SUM関数は選択した範囲の合計値を算出してくれる関数です。
式は
=SUM(範囲)
です。
例えば、下記のような期末試験のテスト結果があると仮定します。
生徒はAさんからEさんまでの5人、国語から家庭科までの9教科のテスト結果を管理している表です。
各生徒のテスト結果の合計点をSUM関数を使用する事で求める事ができます。
SUBTOTAL関数とは
使い方はSUM関数とほとんど同じで、選択した範囲を計算します。
式は
=SUBTOTAL(集計方法,参照)
です。
SUM関数と異なる部分としては、SUBTOTALの式におきましては集計方法があります。
この集計方法には種類が11種類あります。(2018年9月4日時点)
SUBTOTALの集計方法について
下記表はMicrosoft SUBTOTALのページを参考にしています。
集計方法1 | 集計方法2 | 関数 |
---|---|---|
1 | 101 | AVERAGE |
2 | 102 | COUNT |
3 | 103 | COUNTA |
4 | 104 | MAX |
5 | 105 | MIN |
6 | 106 | PRODUCT |
7 | 107 | STDEV |
8 | 108 | STDEUP |
9 | 109 | SUM |
10 | 110 | VAR |
11 | 111 | VARP |
集計方法1と集計方法2の値の違いですが、手動で非表示にした値を含めない場合は集計方法2の値を使用します。
集計方法1と集計方法2の違いは下の写真のようなイメージになります。
9行目~12行目を非表示にしましたが、集計方法1は値が、非表示にする前と後で変化がありません。
一方、集計方法2では非表示のものは計算に含まれていません。
フィルターを使用する場合は集計方法1、集計方法2どちらで行っても同じ値となります
それでは話はもどります。
今回は合計値を求めるので、集計方法は”9”(もしくは109)となります。
参照には集計する範囲を指定します。
そして、SUM関数、SUBTOTAL関数をそれぞれ計算をしますと
となります。
この画像だけ見ますと、SUM関数とSUBTOTAL関数の違いがわからないですよね。
次のセクションでSUM関数とSUBTOTAL関数の違いをご説明致します。
SUBTOTAL関数が力を発揮するのは、フィルターをかけた時の計算
SUBTOTAL関数の便利さがわかるのは
Excelの表にフィルターをかけた時
です。
フィルターとは
今回のケースで言いますと、
「9教科を特定の教科のみ表示させたい」
と言った時にフィルターを活用します。
下の画像はイメージです。
フィルターを使用する事で9教科から国語、数学、英語の3教科に絞る事ができます。
この他にも
80点以上の教科のみを表示する事や
色付けした項目のみを表示する事もできます。
SUM関数とSUBTOTAL関数の話に戻りますが、
「9教科から国語、数学、英語、理科、社会の5教科のみ表示させたい」という事で、さっそくフィルターをかけてみましょう。
すると、SUM関数のAさん~Eさんのそれぞれの値は、フィルターをかける前と後を比較しても、値の変化はしていません。
一方、SUBTOTALの方では、5教科の合計が算出されています。
表示させたい教科を変更しても、変更された教科の合計値で算出してくれます。
今回は教科で検証をしてみましたが、都道府県や性別、血液型といった集計なども簡単に行う事ができます。
今回のように全体の合計を出すだけであれば、SUM関数で対応できると思います。
しかし、今回のように部分的な合計の算出も必要な場合はSUBTOTALを使うととても早く計算をする事ができますのでおすすめです。
是非活用してみてください。
コメント
この記事へのトラックバックはありません。
この記事へのコメントはありません。