Excel(エクセル)のSUBTOTAL関数を解説！オートフィルタに連動して合計、平均などを集計する

SUBTOTAL関数の書き方
=SUBTOTAL(集計方法, 参照)
集計方法：書きたい関数を番号※1で指定します(1~11と101~111)
参照：集計したい範囲を指定します

特徴
・オートフィルタに対応し、表示されている数値だけを集計する
※SUM関数やAVERAGE関数はオートフィルタに対応せず、常にすべての数値を集計する
・1~11は「セルの非表示」「グループ化」には対応しない
・101~111は「セルの非表示」「グループ化」にも対応する

代替手段
・AGGREGATE関数：様々なエラー除外や設定が可能
・ピボットテーブル：集計結果を確認したい場合は便利

集計方法	関数名	機能
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関数	標本分散を出す

SUM関数やAVERRAGE関数など、エクセルには数字を集計するための便利な関数がたくさんあります。

しかし関数のほとんどはオートフィルタに対応しておらず、常に表示されている行だけを集計したい場合はSUBTOTAL関数を書く必要があります。

この記事ではSUBTOTAL関数の機能や書き方をわかりやすく紹介します。

この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、解説いたします。

SUBTOTAL関数とは

SUBTOTAL関数とは、オートフィルタで抽出した数字だけを集計できる関数です。

たとえば全支店の売上合計を計算したいとき、このようにSUBTOTAL関数を書きます。

書き方：=SUBTOTAL(9,C6:C13)

9はSUM関数を表す番号です。C6:C13で合計する範囲を指定しています。

最後にEnterキーを押すとSUM関数の結果である「売上の合計値」が計算されます。

するとSUBTOTAL関数の結果も、自動でA支店の売上額に書き換わりました。

SUBTOTAL関数の書き方

SUBTOTAL関数の書き方
=SUBTOTAL(集計方法, 参照)
条件範囲：書きたい関数を番号※1で指定します(1~11と101~111)
参照：集計したい範囲を指定します

特徴
・オートフィルタに対応し、表示されている数値だけを集計する
※SUM関数やAVERAGE関数はオートフィルタに対応せず、常にすべての数値を集計する
・1~11は「セルの非表示」「グループ化」には対応しない
・101~111は「セルの非表示」「グループ化」にも対応する

SUBTOTAL関数では「集計方法」と「参照(範囲)」の2つを指定します。

集計方法については、以下の関数の中から番号で入力します。

集計方法	関数名	機能
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関数	標本分散を出す

集計方法「1~11」と「101~111」の違い

1~11：非表示のセル、グループ化で隠したセルを集計する
101~111：非表示のセル、グループ化で隠したセルを集計しない

非表示やグループ化など、オートフィルタ以外でセルが隠れた際に数値を集計したいかどうかで使い分けます。

集計したい場合は「1~11」、集計したくない場合は「101~111」を使います。

上のSUBTOTALはSUM関数を「9」で、下のSUBTOTAL関数はSUM関数を「109」で指定しました。

今のところ、集計結果はどちらも同じです。

オートフィルタを使っても集計結果は同じままです。

しかし、グループ化で一部のセルを隠すと集計結果が変わります。

「9」でSUM関数を記述した方は変わらず¥12,440,000のままですが、「109」で記述した方はE～H支店の合計金額が表示されています。

さらに細かな設定がしたい方向け：AGGREGATE関数

AGGREGATE関数

SUBTOTAL関数よりも多くの関数を集計でき、多くの除外設定ができる

書き方：=AGGREGATE(集計方法, オプション, 参照, [順位])
集計方法：19種類の関数から番号で指定(1~19)
オプション：8つの設定から番号で指定(0~7)
参照：集計したい範囲を指定
[順位]：入力が必要な関数のときのみ記載

AGGREGATE関数で扱える「集計方法」は以下19種類です。

集計方法	関数名	機能
1	AVERAGE関数	平均を出す
2	COUNT関数	数値データの個数を数える
3	COUNTA関数	データの個数を数える
4	MAX関数	最大値を表示する
5	MIN関数	最小値を表示する
6	PRODUCT関数	掛け算をする
7	STDEV.S関数	不偏標準偏差を出す
8	STDEV.P関数	標本標準偏差を出す
9	SUM関数	合計を出す
10	VAR.S関数	不偏分散を出す
11	VAR.P関数	標本分散を出す
12	MEDIAN関数	中央値を出す
13	MODE.SNGL関数	最頻値を出す
14	LARGE関数	○番目に大きい数字を出す
15	SMALL関数	○番目に小さい数字を出す
16	PERCENTILE.INC関数	百分位数を出す
17	QUARTILE.INC関数	四分位数を出す
18	PERCENTILE.EXC関数	0%と100%を除いた百分位数を出す
19	QUARTILE.EXC関数	0%と100%を除いた四分位数を出す

また、オプションもSUBTOTAL関数よりも細かく指定できます。

オプション	意味
0(または空白)	SUBTOTAL関数とAGGREGATE関数が入力されたセルを無視
1	0かつ非表示・グループ化の行を無視
2	0かつエラーのセルを無視
3	0かつ非表示・グループ化、エラーのセルを無視
4	なにも無視しない
5	非表示・グループ化の行を無視
6	エラーのセルを無視
7	非表示・グループ化、エラーのセルを無視

ただしあまり知られていない関数なので「他の人が見て読み解けるか」という観点で使用時には注意が必要です。

なるべくマニアックな関数でなく、一般的な関数を使い、他者が見てわかりやすい資料作りを心がけましょう。

ピボットテーブルの活用

表に集計結果を残しておきたい場合は、セルにSUBTOTAL関数やAGGREGATE関数を書くことがおすすめですが、

結果を残す必要がなく、ただ集計数値を確認したいだけであればピボットテーブルの利用もおすすめです。

休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修

私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。

※10点満点中の評価の平均値。

休日1日を研修に使うのは「やや重たい」というご意見もいただきます。

しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。

実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。

そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。

参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。

動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！

※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。

現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。

全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。

著書「神速Excel」も絶賛発売中