表示されたデータだけ集計SUBTOTAL関数とオートフィルタ

抽出結果のデータだけを自動的に合計したい

オートフィルタで抽出したデータを、表示されたものだけ集計したい時は、SUBTOTAL(サブトータル)関数を使います。
抽出結果をSUM関数で合計しようとすると、非表示のデータも一緒に合計されてしまいますが、SUBTOTAL(サブトータル)関数をつかえば、表示された抽出結果のみを集計することができます。
(サンプルファイルは、こちらから 関数技56回抽出した結果のみを合計する方法サンプルデータ

オートフィルタで表示した結果のみを合計する方法

Excelにはオートフィルタという機能があります。

オートフィルタ矢印をクリックして、リストから目的のデータを
選択するだけで、簡単にデータの絞り込みが行える便利な機能です。

オートフィルタを使うには、(Excel2003以前)   
(1)見出し行をクリックし、
(2)「データ」→「フィルタ」→「オートフィルタ」を選択。
(3)各列の見出しに▼ボタンが表示され、オートフィルタ機能が設定されます。
オートフィルタを使うには、(Excel2007以降)
(1)見出し行をクリックし、
(2)「データ」タブの「並べ替えとフィルター」グループにある「フィルター」ボタンをクリック
(3)各列の見出しに▼ボタンが表示され、オートフィルタ機能が設定されます。

オートフィルタ機能が設定されたので、7月のデータだけ表示してみます。
抽出結果のデータを合計SUBTOTAL関数

表示しているデータだけを集計する

抽出された7月の結果だけを合計したいので、E5セルに式を入力します。
このとき使う関数が、SUBTOTAL関数です。
SUM関数を使うと、折りたたまれて非表示になっているデータも合計してしまいます。
非表示になった行を無視して、抽出結果のみを集計するには、「SUBTOTAL(サブトータル)関数」を使うのです。
抽出結果のデータを合計SUBTOTAL関数2
さて、現在の表では、抽出した7月の売上が表示されていますが、抽出項目を変更してみましょう。
抽出結果のデータを合計SUBTOTAL関数3

データベースやリストの集計

表示したデータだけを合計SUBTOTAL関数4
アイスコーヒーだけにチェックをいれました。
スポンサーリンク
スポンサーリンク

抽出結果のデータを合計SUBTOTAL関数5
SUBTOTAL関数が入力されたE15セルの合計が、7月のアイスコーヒーだけの合計額に変わっています。
このようにSUBTOTAL関数は、一度入力すると、常に抽出結果のみを集計します。

オートフィルタと相性のいいSUBTOTAL関数ですが、Excel2003からは、オートサム ボタンをクリックすると、SUBTOTAL関数が自動的に入力されるようになりました。Excelの方で、オートフィルタを使っているからSUBTOTAL関数だと判断してくれるようになったのです。
この機能は、2003では装備されていますが、以前のバージョンでは、
SUM関数が入ってしまいますから、手動で、SUBTOTAL関数を入力してください。

SUBTOTAL関数

セルE15に入力されている式 =SUBTOTAL(9,E2:E4)

SUBTOTAL関数の書式
抽出結果のデータを合計SUBTOTAL関数6
SUBTOTAL( 集計方法, 範囲1, 範囲2・・・)


書式にある、→ SUBTOTAL(集計方法,範囲1,範囲2・・・)
集計方法という項目ですが、Excel2003からは項目が増えています。
抽出結果のデータを合計SUBTOTAL関数7
1・・・平均、4・・・最大値、5・・・最小値
というように、引数の集計方法を変更するだけて様々なデータを得ることができます。
スポンサーリンク
スポンサーリンク