SUBTOTAL（サブトータル）関数とは？

SUBTOTALは「小計」を意味する英語です。小計を含むデータ表でスムーズな集計を実行する関数です。非表示の行を除外した集計やフィルターで抽出後の集計にも応用できます。

SUBTOTAL関数の引数と書式

「数式」タブ「関数ライブラリ」の「数学/三角」からSUBTOTALをクリックします。

SUBTOTALの引数は「集計方法」と「参照1」が必須で、「参照2」以下は任意です。

書式の構成はこうなります。

SUBTOTAL関数の基本的な使い方

SUBTOTAL関数で実際に集計してみましょう。一番よく使われる「小計」と「合計」を例にします。

見やすい売上表を作るのに、商品カテゴリーごとに小計を設け、最終行に合計・総計を置くレイアウトがよく採用されます。オートSUMで総計を計算すると、小計の行も集計されてしまいます。そこで、SUBTOTALを使い、小計を除外して個々のデータだけが合計されるように実装します。

SUBTOTAL関数の集計方法9と109の使い分け

「合計」に対応する番号は「9」と「109」の２種類あります。上記例では「9」を指定して実行しましたが、「109」はどんな時に使われるでしょう？

小計除外の応用として、非表示のデータを除外して集計を行いたい場合があります。「109」を指定すると、非表示になった行を無視して集計してくれるのです。

下図の表では４行目と７行目を非表示にして、トータルを計算し直そうとしていますが、集計方法が「9」になっているので「小計」「総計」の数値は変わりません。

「総計」「小計」の数式の「集計方法」を「109」に変更します。

金額が非表示のデータを除外して計算し直されました。

SUBTOTAL関数で平均や分散を求める

「合計」以外の集計方法を実行してみましょう。

下図は幾つかの行が非表示になっている売上表です。
「平均」の入るセルを選択して、SUBTOTAL関数の引数ダイアログボックスを開き、「集計方法」に非表示の行を除外して平均値を求める「101」を指定、「参照1」にデータ範囲をドラッグで指定します。

非表示の行を除外して平均値が集計されました。

もう一つ、同じ表で「分散」を求めてみます。分散とはデータのバラつきを示す指標です。
「分散」の入るセルを選択して、SUBTOTAL関数の引数ダイアログボックスを開き、「集計方法」に非表示の行を除外して平均値を求める「111」を指定、「参照1」にデータ範囲をドラッグで指定します。

非表示の行を除外して分散が求められました。

SUBTOTAL関数でフィルターをかけて抽出したデータの集計

非表示データの除外と同じことで、フィルターで抽出したデータの集計にもSUBTOTAL関数が使われます。
データ表を選択して「ホーム」→「編集」グループ→「並べ替えとフィルター」→「フィルター」をクリックして機能をアクティブにします。

ヘアサロンの売上表でフィルターを実行します。「施術メニュー」から「カット」だけを抽出して表示させました。

SUBTOTAL関数でフィルターで抽出されたデータの「数量」を「集計方法＝9」「参照1＝F列」を指定して計算すると、

フィルターで抽出されたデータの「数量」だけが合計されました。この数式を金額のセルにオートフィルコピーします。

テーブルとSUBTOTAL関数

テーブル化した表で集計行を表示させると合計金額が自動で計算されますが、このセルに挿入されている数式は「=SUBTOTAL(109,[合計])」です。

テーブルの集計行にはあらかじめSUBTOTALが組み込まれているわけで、右横の▼からプルダウンリストを引き出して「平均値」「データの個数」「標本分散」などを数式を組み立てずに手軽に集計できるようになっています。

売上表などのデータベースはテーブル化すると格段に便利です。

テーブル機能の使い方・完全ナビ

テーブルの設定手順、機能の使い方を順を追って解説するとともに、テーブル化すると何が変わる？　テーブル機能を使う利点とは？　デメリットは無いの？　に答えます。

www.tschoolbank.com

Excel2010からは、引数のオプション指定で非表示データ・エラー値を除外できる強化版の関数AGGREGATE（アグリゲート）が登場。降順・昇順なども追加対応。

https://www.tschoolbank.com/tb-articles/excel-function-aggregate/

www.tschoolbank.com