Excel～SUBTOTAL関数でオートフィルタにより抽出したデータを合計しよう～エクセル
フィルター付きの表においてデータを非表示にした時にSUBTOTAL関数を使って非表示のデータを除外して合計を算出する方法を解説します。
- SUBTOTAL関数の書式はSUBTOTAL(集計方法,範囲 1,[範囲 2],...)になります。
- 集計方法は集計に使用する関数に対応した番号を指定します。
- 範囲 1は合計したい数値の範囲を指定します。
- 範囲 2は今回は使用しません。
集計方法は以下の表に対応しています。フィルターを使う場合は1~11番でも101~111番でも非表示のデータは含めずに計算結果を表示します。手動でデータを非表示にする場合は1~11番は非表示のデータも含めて計算結果を表示し101~111番は非表示のデータを含めずに計算結果を表示します。
| 番号 (非表示行を除く場合) | 集計方法 | 説明 |
|---|---|---|
| 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 | 母集団分散を計算 |
フィルターの表示
下の画像の例で表にフィルターを付けたい場合は表を全て選択しデータタブのフィルターをクリックすると各列の項目にフィルターボタンが表示されます
SUBTOTAL関数の入力
下の画像の例でフィルターで班を絞った際に除外した班のポイントを含まずにポイント合計をセルB1に表示したい場合はセルB1に=SUBTOTAL(9,C4:C9)と入力しEnterをおします。
集計方法は合計を計算したい場合は9を設定します。
範囲 1は合計したい数値の範囲、今回はポイントなのでセルC4からセルC9の範囲を設定するのでC4:C9になります。
範囲 2は今回は使用しません。
フィルターで何も絞らない状態でセルB1に=SUBTOTAL(9,C4:C9)と入力しEnterをおすと、A、B、C、D、E、Fさんのポイントの合計9200が表示されます。
班のフィルターをクリックし3班のチェックを外すと3班のCさんとFさんの行が非表示になりB1の値もCさん、Fさんのポイントを除外した6500円に変わります。
手動で非表示にする場合
フィルターを使わずに手動でデータを非表示にする場合は集計方法の1~11番は非表示行も含めた計算結果が表示されます。101~111番は非表示行を含めない計算結果を表示します。たとえば合計の場合は集計方法が9の場合は非表示行を含めた合計を表示し、109の場合は非表示行を含めない合計を表示します。フィルターの場合は集計方法が9でも109でも非表示行を含めない合計を表示するので違いに注意してください。