オートフィルターで絞り込んだ範囲で集計したい

2023年4月8日2023年4月23日

概要

データを分析する際に、オートフィルターを設定することは多いですよね。また、その分析には売上であれば合計、テストの点数であれば平均といった計算式も設定しているかと思います。より細かく分析するために、オートフィルターで絞り込んでも、その合計や平均、件数といった集計・計算値は表全体であって、フィルターで絞り込んだ範囲の合計、平均、件数ではありません。

今回は、オートフィルターで絞り込んだ範囲で、その合計や平均、件数を計算する方法になります。

オートフィルターで絞り込んだ範囲で、集計等を行うには関数：SUBTOTALを利用します。

関数の定義

SUBTOTAL(集計方法, 参照1, 参照2, ・・・)

機能：表示データのみの集計

引数	必須/任意	説明
集計方法	必須	集計方法を指定。 1～11（下表参照）を指定すると、オートフィルターで絞り込んだ範囲が集計対象になります（手で非表示にしても集計対象になる）。 101～11（下表参照）は上記に対して、手で非表示にした部分が除外されます。
参照1	必須	集計対象範囲。
参照2～	任意	集計対象範囲。最大参照254まで指定可能。

集計方法

集計方法	機能
1 or 101	平均値を求める
2 or 102	数値の個数を求める
3 or 103	空白以外のデータの個数を求める
4 or 104	最大値
5 or 105	最小値
6 or 106	積
7 or 107	不偏標準偏差
8 or 108	標本標準偏差
9 or 109	合計
10 or 110	不偏分散
11 or 111	標本分散

表示されているデータ件数、売上額の合計、売上額の平均を求める

下図のようなデータの集計をSUBTOTAL関数を使って計算します。計算対象の列は「売上額」でそのデータ件数、合計、平均を求めましょう。ただし、「商品」列でフィルターをかけて表示された商品のみの計算を行います。

上記データ表の上に、下図のように集計表示用に「件数」、「売上合計」、「売上平均」の各列を作り、そして「合計」と「ﾌｨﾙﾀ小計」行を作ります。ここで、「合計」行は表示されていない行も含めてすべての行を対象に集計。「ﾌｨﾙﾀ小計」行はオートフィルターで絞り込んで表示されている行のみを集計します。

また、下図では、各セルの計算式を表示しています。「合計」行は、件数：COUNT関数、売上合計：SUM関数、売上平均：AVERAGE関数を使って計算しています。「ﾌｨﾙﾀ小計」行は、どの列をSUBTOTAL関数を使います。集計方法の引数を、それぞれ、件数：2、売上合計：9、売上平均：1として計算します。