ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.012

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

Excel2003/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)

SUBTOTAL関数

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

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

  =SUBTOTAL(9,D6:D20)

SUBTOTAL関数

引数[計算方法]は「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(標本分散)

ヘルプmemo

<SUBTOTAL関数 (数学/三角関数)>

リストまたはデータベースの集計値を返します。
 数式: =SUBTOTAL(集計方法, 参照)
 引数:
  集計方法... リストの集計に使用する関数を番号で指定
      非表示の値も含める/非表示の値は含めない... 対応する関数
      1/101... AVERAGE(平均)
      2/102... COUNT(数値の個数)
      3/103... COUNTA(データの個数)
      4/104... MAX(最大値)
      5/105... MIN(最小値)
      6/106... PRODUCT(数値の積)
      7/107... STDEV(標準偏差)
      8/108... STDEVP(標準偏差)
      9/109... SUM(合計)
      10/110... VAR(不偏分散)
      11/111... VARP(標本分散)
  参照... 集計するリストの範囲または参照を254個まで指定

関連ヘルプ

奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》
検索条件に一致するデータの合計を求める 《SUMIF》
時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》
OR条件に当てはまるデータの合計を求める 《SUMIF・SUMIFS・SUMPRODUCT・DSUM》
10進数の数値を2進数と16進数に変換するには 《DEC2BIN・DEC2HEX》
ページのトップへ戻る