SUBTOTAL関数の使い方|集計方法の指定で様々な種類の集計を実行

UBTOTAL関数の使い方
Excelの一覧

Excel(エクセル)のSUBTOTAL関数の使い方|集計方法の指定で様々な種類の集計を実行

使用関数:SUBTOTAL

SUBTOTAL(サブトータル)関数とは?

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

SUM関数とSUBTOTAL関数の違い

SUBTOTALは合計・平均・標準偏差・分散など様々な種類の集計を行える関数です。

SUM関数は合計する関数であり、SUBTOTAL関数が対応する「集計方法」の一つです。SUBTOTALの引数「集計方法」は決められた番号で指定します。「合計=SUM関数」に対応する番号は「9」及び「109」で、これらをSUBTOTALに指定すれば参照範囲が「合計」されます。他に、例えば「1」「101」を指定すればAVERAGE関数に対応する集計が行われ、平均値が求められますし、「2」「102」を指定すればCOUNT関数に対応する集計が行われて数値データの個数がカウントされます。

SUBTOTAL関数の引数と書式

「数式」タブ「関数ライブラリ」の「数学/三角」からSUBTOTALをクリックします。
「関数ライブラリ」→「数学/三角」→ SUBTOTALをクリック
SUBTOTALの引数は「集計方法」と「参照1」が必須で、「参照2」以下は任意です。

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

第一引数「集計方法」の指定

最初の引数の「集計方法」は以下に掲載した計算する種類番号「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.S
8 108 標本標準偏差 STDEV.P
9 109 合計 SUM
10 110 不偏分散 VAR.S
11 111 標本分散 VAR.P

SUBTOTAL関数の基本的な使い方

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

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

「小計」もSUBTOTAL関数で集計する

SUBTOTAL関数で小計を除いて集計するためには、小計自体もSUBTOTALで集計されている必要があります。小計がオートSUMで集計されていたりすると除外されません。

Webデザインのサービスごとに小計行を設けた売上表があります。
途中に小計行を設けた売上表
小計のセルを一つ選択して、SUBTOTAL関数の引数ダイアログボックスを開き、「集計方法」に合計する「9」を指定、「参照1」に集計するデータ範囲をドラッグで指定します。
SUBTOTAL関数の引数ダイアログで集計方法に9、参照1にセル範囲を指定
他の小計セルも同様にSUBTOTALで集計します。
他の小計セルもSUBTOTALで集計

小計を除いて合計する

「総計」の入るセルを選択して、SUBTOTAL関数の引数ダイアログボックスを開き、「集計方法」に合計する「9」を指定、「参照1」に小計も含めたセル範囲をドラッグで指定します。
「総計」の入るセルを選択し、SUBTOTAL関数で小計行も含めて集計
各「小計」の数値が除外されて、集計されました。
SUBTOTAL関数で小計を除外して集計された

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

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

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

下図の表では4行目と7行目を非表示にして、トータルを計算し直そうとしていますが、集計方法が「9」になっているので「小計」「総計」の数値は変わりません。
行を非表示にした表
「総計」「小計」の数式の「集計方法」を「109」に変更します。
SUBTOTAL関数の「集計方法」を109に変更
金額が非表示のデータを除外して計算し直されました。
SUBTOTAL関数で非表示のデータを除外して計算

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

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

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

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

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

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

ヘアサロンの売上表でフィルターを実行します。「施術メニュー」から「カット」だけを抽出して表示させました。
フィルターで「施術メニュー」から「カット」だけを抽出して表示
SUBTOTAL関数でフィルターで抽出されたデータの「数量」を「集計方法=9」「参照1=F列」を指定して計算すると、
SUBTOTAL関数でフィルターで抽出されたデータの「数量」を「集計方法=9」「参照1=F列」を指定して計算
フィルターで抽出されたデータの「数量」だけが合計されました。この数式を金額のセルにオートフィルコピーします。
SUBTOTAL関数でフィルターで抽出されたデータの「数量」だけ合計

テーブルとSUBTOTAL関数

テーブル化した表で集計行を表示させると合計金額が自動で計算されますが、このセルに挿入されている数式は「=SUBTOTAL(109,[合計])」です。
テーブル化した表の集計行にはSUBTOTAL関数が組み込まれている
テーブルの集計行にはあらかじめSUBTOTALが組み込まれているわけで、右横の▼からプルダウンリストを引き出して「平均値」「データの個数」「標本分散」などを数式を組み立てずに手軽に集計できるようになっています。
テーブルのプルダウンリストでSUBTOTAL関数の集計方法を選択できる

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

テーブル機能の使い方・完全ナビ
テーブルの設定手順、機能の使い方を順を追って解説するとともに、テーブル化すると何が変わる? テーブル機能を使う利点とは? デメリットは無いの? に答えます。
SUBTOTAL関数のまとめ
  • SUBTOTAL関数は様々な種類の集計(合計・平均・分散・個数・標準偏差・最大値と最小値など)を実行できる便利な関数です
  • SUM関数はSUBTOTAL関数の集計方法の一つです
  • SUBTOTAL関数は小計や非表示データを除外して集計できます
  • 上記を実行するには小計もSUBTOTAL関数で集計されていることが絶対条件です
  • 引数「集計方法」には対応する番号を指定します
  • 1桁の番号(1~11)は小計のみを除外します
  • 3桁の番号(101~111)は非表示データ及びフィルターで隠されたデータも除外します
  • テーブルにした表の集計行には既にSUBTOTALが組込まれ、合計や分散、標準偏差、個数などをプルダウンリストで簡単に実行できます
タイトルとURLをコピーしました