No.012

フィルターで抽出されたデータの件数、合計をセルに表示したい 《SUBTOTAL》

2003/2007/2010/2013

フィルターの抽出された件数はステータス バーに表示されますが、セルに件数や合計値などを表示したい、というお問い合わせです。
それを可能にするのは関数、SUBTOTAL関数（分類: 数学/三角）を使用します。

SUBTOTAL関数は、指定した範囲において、集計方法を選んで求めるという変わった関数です。
引数は、［集計方法］と［参照］の2つです。
引数［集計方法］は下記の数値で設定し、通常の関数と次のような対応になっています。

• 1 …AVERAGE（平均）
• 2 …COUNT（数値の個数）
• 3 …COUNTA（データの個数）
• 4. …MAX（最大値）
• 5 …MIN（最小値）
• 6 …PRODUCT（数値の積）
• 7 …STDEV（標準偏差）
• 8 …STDEVP（標準偏差）
• 9 …SUM（合計）
• 10 …VAR（不偏分散）
• 11 …VARP（標本分散）

例えば、次のようなデータがあり、5行目は項目、6行目から実際のデータが入力されているとします。

抽出されたデータの件数を求めます。
引数［参照］にはどれか1列の全データ範囲を指定します。
複数列を指定すると、抽出されたその複数列のセルの集計となります。

　　=SUBTOTAL(3,B6:B20)

2つ目の引数［参照］で指定したのはB列で、文字列データです。文字列のセルを数えるために引数［計算方法］を「3」として、COUNTA関数の方法で計算しています。

では、次の式で抽出されたF3列（D列）の合計を計算してみましょう。

　　=SUBTOTAL(9,D6:D20)

引数［計算方法］は「9」の合計を指定しました。

これらの結果は、抽出を変えて表示された結果に応じて更新されます。

このようにSUBTOTAL関数は、フィルターの抽出表示されたデータの集計をすることができるのですが、対応している関数であるSUM関数などを「=SUM(D6:D20)」というように設定しても、常にその範囲の集計となり、抽出に応じた集計はできません。

また、上記の引数［集計方法］は、非表示にした行も含めた結果が返されます。
非表示にした行は含めないで集計する場合は、以下の100を足した次の引数が用意されています。

• 101 …AVERAGE（平均）
• 102 …COUNT（数値の個数）
• 103 …COUNTA（データの個数）
• 104 …MAX（最大値）
• 105 …MIN（最小値）
• 106 …PRODUCT（数値の積）
• 107 …STDEV（標準偏差）
• 108 …STDEVP（標準偏差）
• 109 …SUM（合計）
• 110 …VAR（不偏分散）
• 111 …VARP（標本分散）