エクセル2013基本講座：オートフィルター後の合計や平均などの計算について

フィルターを利用して、データを絞り込んだデータの計算方法について書いています。
フィルターを掛けると該当しないデータが非表示になるので、表示されているセルだけを計算する必要があります。
SUBTOTAL関数を使って、合計や平均や件数などを計算をすることができます。

はじめに	SUBTOTAL関数を入力する

はじめに　　　　　 Topへ

計算結果を表示するセルはデータから1行または1列の空白を設けて配置すると便利です。
- フィルターを実行するとき、データ範囲を指定してからフィルターを実行すればよいのですが、毎回データ範囲を選択するのは面倒です。
  データリスト内のセルを選択して、フィルターを実行すると、アクティブセルを含むまとまったデータ範囲をExcelが一つのまとまりと判断するため、1行離して、計算結果を表示するようにするのが便利です。
  - データリストとくっついているケース
    データリスト内のセルを選択した状態でフィルターを掛けると・・・・
    
    ↓　フィルターを掛けると・・・
    売上合計や売上平均を表示するセルまで非表示になってしまいます。
  - 1行空白行を設ける
    
    ↓　フィルターを掛けると・・・
    売上合計や売上平均を表示するセルは表示されたままです。

SUBTOTAL関数を入力する　　　　　 Topへ

SUBTOTAL関数を使ってみます。
1. G24セルを選択して、　=SUBT　と入力すると入力した文字と一致する関数が表示されます。
  SUBTOTAL をダブルクリックすると、 =SUBTOTAL(　と入力されます。
2. 引数のヒントがリスト表示されますので、ここは合計を計算したいので、「9 - SUM」をダブルクリックします。
  引数の9が入力され、=SUBTOTAL(9　となります。
3. カンマ , を入力して合計するセル範囲を指定します。
  =SUBTOTAL(9,G6:G22)　とします。
  - =SUBTOTAL(109,G6:G22)　としてもOKです。
4. 同様に、平均は　「 1 - AVERAGE」を利用して、 =SUBTOTAL(1,G6:G22)　とします。
  - =SUBTOTAL(101,G6:G22)　としてもOKです。
フィルターを掛けて計算結果を確認してみます。
- 担当者で「岡田」のデータを表示しました。正しく表示されたセルの計算が行われているのが分かります。
- 件数を追加すると変わりやすいかもしれませんね。
  表示されているセルの個数は=SUBTOTAL(2,G6:G22)　で求めることができます。
  - =SUBTOTAL(102,G6:G22)　としてもOKです。
- 担当者で「井上」のデータを表示しました。計算結果が変化したのが分かります。

ちなみに、SUBTOTAL関数の引数はExcel2003以降は3桁の指定ができるようになっています。

ヘルプなどでは「非表示の値の含める」との記載がありますが、オートフィルターでは非表示の値を無視されますので、どちらの引数を使ってもOKとなっています。
「非表示の値も含める」はオートフィルターでの非表示という意味ではないので注意が必要です。

引数で指定する値

非表示の値も含める	非表示の値を無視する	関数
Excel2002以前はこちらだけが指定できます	Excel2003以降はこちらも使用できます
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関数の引数の集計方法の違いと表示/非表示時の計算結果の違いについて例示しておきます。
1. ヘルプなどでの非表示にするというのは「行を表示しない」という機能を使ったケースになります。
  
  または、右クリックメニューの「非表示」という機能です。
  - 行を非表示にしたときのSUBTOTAL関数の引数と計算結果の違いは下図のようになります。
2. グループ化してアウトラインを折りたたんだ時も下図のように違いがあります。

よねさんのExcelとWordの使い方｜エクセル2013基本講座：目次｜フィルター(データ抽出)｜オートフィルター後の合計や平均などの計算について