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

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

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

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

集計方法関数名機能
1か101AVERAGE関数平均を出す
2か102COUNT関数数値データの個数を数える
3か103COUNTA関数データの個数を数える
4か104MAX関数最大値を表示する
5か105MIN関数最小値を表示する
6か106PRODUCT関数掛け算をする
7か107STDEV.S関数不偏標準偏差を出す
8か108STDEV.P関数標本標準偏差を出す
9か109SUM関数合計を出す
10か110VAR.S関数不偏分散を出す
11か111VAR.P関数標本分散を出す
集計方法と関数の対応表

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

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

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

ExcelCamp講師 野沢先生

オートフィルタの使い方やショートカットにピンときていない方にはまず、こちらの記事をお読みいただくことをおすすめします。

ExcelCampはExcel初心者・苦手な方のための研修です。
・たった1日でExcel操作が「激変」します。
・講義中に反復練習するから、その場でExcelが得意になります。
・延べ2,000名以上が参加し、満足度は9.71(10点満点)です。

目次

SUBTOTAL関数とは

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

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

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

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

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

ExcelCamp講師 野沢先生

この状態でオートフィルタ機能を使って、支店を「A支店」だけに絞ってみます。

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

ExcelCamp講師 野沢先生

SUBTOTAL関数ではなくSUM関数で合計を書いていた場合は、オートフィルタで絞り込んでも合計値は「¥12,440,000」のままです。

SUBTOTAL関数は、オートフィルタで抽出した結果に応じて集計値を変えられる関数です。

SUBTOTAL関数の書き方

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

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

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

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

集計方法関数名機能
1か101AVERAGE関数平均を出す
2か102COUNT関数数値データの個数を数える
3か103COUNTA関数データの個数を数える
4か104MAX関数最大値を表示する
5か105MIN関数最小値を表示する
6か106PRODUCT関数掛け算をする
7か107STDEV.S関数不偏標準偏差を出す
8か108STDEV.P関数標本標準偏差を出す
9か109SUM関数合計を出す
10か110VAR.S関数不偏分散を出す
11か111VAR.P関数標本分散を出す

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

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

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

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

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

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

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

A~C支店のみにフィルタリング

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

A~D支店の情報をグループ化で隠しました

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

ExcelCamp講師 野沢先生

このようにオートフィルタ以外の要素も考慮に入れて集計したい場合101~111を使うことがおすすめです。

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

AGGREGATE関数

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

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

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

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

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

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

①SUBTOTAL関数とAGGREGATE関数を無視
②非表示・グループ化を無視
③エラーのセルを無視

上記3つの設定を自由に組み合わせて使うことができるため、機能の充実度はSUBTOTAL関数の上位互換といえます。

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

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

ピボットテーブルの活用

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

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

ExcelCamp講師 野沢先生

関数を書く必要が無いため、慣れれば簡単な集計はピボットテーブルを使ったほうが速いです。

詳しい使い方は以下の記事を参考にしてください。

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

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

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

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

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

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

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

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

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

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

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

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

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

目次
閉じる