| 集計方法 | 関数名 | 機能 |
|---|---|---|
| 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関数の結果である「売上の合計値」が計算されます。
ExcelCamp講師 野沢先生この状態でオートフィルタ機能を使って、支店を「A支店」だけに絞ってみます。
するとSUBTOTAL関数の結果も、自動でA支店の売上額に書き換わりました。
ExcelCamp講師 野沢先生SUBTOTAL関数ではなくSUM関数で合計を書いていた場合は、オートフィルタで絞り込んでも合計値は「¥12,440,000」のままです。
SUBTOTAL関数は、オートフィルタで抽出した結果に応じて集計値を変えられる関数です。
SUBTOTAL関数の書き方
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支店の合計金額が表示されています。
ExcelCamp講師 野沢先生このようにオートフィルタ以外の要素も考慮に入れて集計したい場合は101~111を使うことがおすすめです。
さらに細かな設定がしたい方向け：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 | 非表示・グループ化、エラーのセルを無視 |
ExcelCamp講師 野沢先生①SUBTOTAL関数とAGGREGATE関数を無視
②非表示・グループ化を無視
③エラーのセルを無視
上記3つの設定を自由に組み合わせて使うことができるため、機能の充実度はSUBTOTAL関数の上位互換といえます。
ただしあまり知られていない関数なので「他の人が見て読み解けるか」という観点で使用時には注意が必要です。
なるべくマニアックな関数でなく、一般的な関数を使い、他者が見てわかりやすい資料作りを心がけましょう。
ピボットテーブルの活用
表に集計結果を残しておきたい場合は、セルにSUBTOTAL関数やAGGREGATE関数を書くことがおすすめですが、
結果を残す必要がなく、ただ集計数値を確認したいだけであればピボットテーブルの利用もおすすめです。
ExcelCamp講師 野沢先生関数を書く必要が無いため、慣れれば簡単な集計はピボットテーブルを使ったほうが速いです。
詳しい使い方は以下の記事を参考にしてください。
休日「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」も絶賛発売中
