記事の詳細

こんにちは。

今日は久しぶりの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のページを参考にしています。

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を使うととても早く計算をする事ができますのでおすすめです。

是非活用してみてください。

 

この記事であなたの課題を解決することができましたか?

疑問点があったり、解決できなかったことがありましたら、お気軽にご相談してください。


ほかの投稿も読んでみませんか?

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

相模原のお店やサービスをどんどん紹介!

行ってみよう相模原 相模原市のお店限定

飲食店

ページ上部へ戻る