Excel のSUBTOTAL関数について、前回の「小計を含む表で集計する場合」に続いて、「フィルターで抽出したデータを集計する場合」について説明していきます。
Excel SUBTOTAL関数の使い方（フィルターで抽出した場合）
下図のように、合計を表示するセル（ここではC2セル）でSUM関数を使ったとします。
合計は ”902,000” となっていますが、これはこの表にある全てのデータの合計です。
ここで「支店」でフィルターをかけて ”東京” だけ抽出したとします。合計欄を見ると…
先ほどと変わらず ”902,000” となっています。SUM関数で合計を出すと、フィルターで抽出しても常にSUMで指定した範囲の合計を出すことになります。
では、SUMの代わりにSUBTOTALを使ったらどうなるか。
フィルターで抽出していない状態では、合計はもちろん ”902,000” です。先ほどと同じように表のすべてのデータの合計になっています。
ここで「支店」でフィルターをかけて ”東京” だけ抽出して合計欄を見ると…
今度は ”159,000” となっています。これは ”東京” のデータだけを合計した結果です。
このようにSUBTOTAL関数はフィルターを使って抽出したときに、抽出したデータのみを集計してくれます。
次のページでは、SUBTOTAL の集計方法 “101” とか “109” について説明します。
ニューバランスのおすすめスニーカー
New Balance Japanのストアをチェック！
SUBTOTAL の集計方法 “101” とか “109” って何？
SUBTOTAL の集計方法をよく見ると、”101” のように、”10x” という選択肢があります。
1 – AVERAGE
2 – COUNT
・・・
101 – AVERAGE
102 – COUNT
など、集計方法は下一桁の数字で同じものになっています。では何が違うのでしょうか。
この違いは何かというと、「非表示」にした行を集計対象とするかしないか、ということになります。
「非表示」というのは、右クリックして行全体を非表示にする、といったものです。
※フィルターによる抽出とは異なります。
「集計方法」は数字で指定します。“1” なら「平均」、“9” なら「合計」といったように、各数字に割り当てられた集計方法で集計しますが、「非表示」にした行を集計対象とするかしないかで使い分けができるようになっています。
＝SUBTOTAL（9, A1:A10）
→ 非表示の行があっても常にA1:A10の合計を求める
＝SUBTOTAL（109, A1:A10）
→ 非表示の行があったらその行のデータを除外して合計を求める
SUBTOTALは小計やフィルターなどを使うときに便利な関数です。集計方法の指定なども含めてSUBTOTALの機能を理解して実務でも使っていきましょう。
ニューバランスのおすすめスニーカー
New Balance Japanのストアをチェック！
Excel の SUBTOTAL関数とは（1） – oshigotono.com
Excel の関連記事