SUBTOTAL関数の使い方と集計方法をわかりやすく解説
SUBTOTAL関数でフィルター後の集計を効率よく確認しよう
Excelで表を集計するとき、フィルターで絞り込んだ後の合計や件数が思った通りに表示されず、困ったことはありませんか。SUBTOTAL関数を使うと、表示されている行だけを対象にして、合計・平均・件数・最大値・最小値などを集計できます。
この記事では、SUBTOTAL関数の基本的な使い方から、関数番号の選び方、フィルター後の集計、非表示行を除外する方法までわかりやすく解説します。
デスクトップパソコンをお探しの方はこちらこの記事でわかること
- SUBTOTAL関数は、フィルター後の表示行だけを集計できるExcel関数です。
- 関数番号9は合計、109は手動の非表示行も除外し、平均や件数も指定できます。
- 売上表や在庫表で関数番号を使い分け、小計作成や目的に合う集計結果を確認しましょう。
目次
SUBTOTAL関数とは？フィルター後の集計に使えるExcel関数
SUBTOTAL関数は、Excelで合計・平均・件数・最大値・最小値などを求めるための関数です。通常のSUM関数やAVERAGE関数と違い、フィルターで絞り込んだ後に表示されている行だけを集計できる点が特徴です。
たとえば、売上表で「担当者A」だけに絞り込んだとき、SUM関数では非表示になった行も含めて合計される場合があります。一方、SUBTOTAL関数を使うと、フィルター後に画面上へ表示されているデータをもとに集計できます。
SUBTOTAL関数がよく使われる表は、次のようなものです。
- 商品別・担当者別・月別などで絞り込みたい売上表
- カテゴリごとの小計と全体の合計を分けたい一覧表
- フィルター後の件数や平均値を確認したい管理表
- 一部の行を非表示にして、表示中のデータだけを集計したい表
このように、SUBTOTAL関数は「表を絞り込んだ後の結果を確認したい」ときに使いやすい関数です。
SUM関数やCOUNT関数でも集計はできますが、フィルターや非表示行を含む表では、SUBTOTAL関数のほうが目的に合う場合があります。Excelで一覧表を扱う機会が多い場合は、基本の使い方を覚えておくと集計作業を進めやすくなります。
SUBTOTAL関数の基本的な書式と引数の指定方法
SUBTOTAL関数の基本的な書式は、次の通りです。
=SUBTOTAL(関数番号,参照1,[参照2],...)
SUBTOTAL関数では、最初の引数に「関数番号」を指定し、次の引数に集計したい範囲を指定します。関数番号によって、合計・平均・件数など、どの方法で集計するかが決まります。
それぞれの引数の役割は、次の表で整理できます。
| 引数 | 内容 | 入力例 |
|---|---|---|
| 関数番号 | 集計方法を指定する番号 | 9、109、1、101など |
| 参照1 | 集計したいセル範囲 | B2:B20 |
| 参照2以降 | 追加で集計したい範囲 | D2:D20など |
SUBTOTAL関数では、関数番号と集計範囲の組み合わせが重要です。たとえば、売上金額の合計を出したい場合は、合計を意味する関数番号を指定して、金額が入力されているセル範囲を選択します。
合計を求める基本的な入力例は、次の通りです。
=SUBTOTAL(9,D2:D20)
この数式では、D2からD20までの範囲を合計します。フィルターで一部の行が非表示になっている場合、非表示になった行は集計から外れ、表示されている行だけの合計を確認できます。
SUBTOTAL関数は、複数の範囲を指定することもできます。
=SUBTOTAL(9,D2:D20,F2:F20)
複数範囲を指定できるため、離れた列にある数値をまとめて集計したい場合にも利用できます。ただし、実務では表の構成をわかりやすく保つため、まずは1つの範囲を指定する使い方から覚えると扱いやすくなります。
SUBTOTAL関数の関数番号一覧と使い分け
SUBTOTAL関数では、関数番号によって集計方法を切り替えます。よく使う番号を理解しておくと、合計だけでなく、平均や件数の集計にも活用できます。
利用頻度の高い関数番号は、次の通りです。
| 関数番号 | 集計内容 | 使用例 |
|---|---|---|
| 1 | 平均 | 単価や点数の平均を出す |
| 2 | 数値の件数 | 数値が入っているセル数を数える |
| 3 | 空白以外の件数 | 文字列を含む入力済みセルを数える |
| 4 | 最大値 | 売上や点数の最大値を出す |
| 5 | 最小値 | 売上や点数の最小値を出す |
| 9 | 合計 | 売上金額や数量を合計する |
まずは「9は合計」と覚えておくと、SUBTOTAL関数を使い始めやすくなります。平均を求めたい場合は1、入力済みの件数を数えたい場合は3を使うといったように、目的に合わせて番号を選びます。
SUBTOTAL関数には、1～11の関数番号と、101～111の関数番号があります。両者の大きな違いは、手動で非表示にした行を集計に含めるかどうかです。
| 関数番号の種類 | フィルターで非表示の行 | 手動で非表示にした行 |
|---|---|---|
| 1～11 | 集計しない | 集計する |
| 101～111 | 集計しない | 集計しない |
フィルターで絞り込んだ行は、どちらの番号でも集計から外れます。一方、手動で非表示にした行を除外したい場合は、101～111の関数番号を使います。
たとえば、合計を求める場合は9または109を使います。
- 9：フィルターで非表示の行は除外し、手動で非表示にした行は含める
- 109：フィルターで非表示の行も、手動で非表示にした行も除外する
非表示行をどのように扱いたいかによって、9と109を使い分けることが大切です。
SUBTOTAL関数で合計・平均・件数を集計する方法
SUBTOTAL関数は、合計だけでなく平均や件数の集計にも使えます。ここでは、実務で使う機会が多い集計方法を例にして説明します。
売上金額の合計を求める場合は、関数番号9を使います。
=SUBTOTAL(9,D2:D20)
この数式では、D2からD20までの売上金額を合計します。商品カテゴリや担当者でフィルターをかけると、表示されている行だけの合計に自動で切り替わります。
平均を求めたい場合は、関数番号1を使います。
#DIV/0!
この数式では、D2からD20までの平均値を求めます。単価、点数、作業時間など、数値の平均を確認したい場合に使いやすい指定です。
件数を数える場合は、数値だけを数えるか、文字列も含めて数えるかによって関数番号を使い分けます。
| 目的 | 関数番号 | 入力例 |
|---|---|---|
| 数値が入っているセルを数える | 2 | 0 |
| 空白以外のセルを数える | 3 | 4 |
数値だけを対象にしたい場合は2、商品名や担当者名など文字列を含めて入力済みの件数を数えたい場合は3を使います。
SUBTOTAL関数でよく使う集計方法を整理すると、次のようになります。
- 売上金額を合計したい場合：関数番号9
- 単価や点数の平均を出したい場合：関数番号1
- 数値データの件数を数えたい場合：関数番号2
- 商品名や担当者名など、空白以外の件数を数えたい場合：関数番号3
- 最大値や最小値を確認したい場合：関数番号4または5
集計したい内容に合わせて関数番号を選ぶことで、SUBTOTAL関数をさまざまな表に活用できます。
SUBTOTAL関数でフィルター後の表示行だけを集計する方法
SUBTOTAL関数を使う大きなメリットは、フィルター後の表示行だけを集計できることです。売上表や在庫表など、条件を絞り込みながら数値を確認したい表では、SUBTOTAL関数が役立ちます。
たとえば、次のような売上表があるとします。
| 日付 | 担当者 | 商品カテゴリ | 売上金額 |
|---|---|---|---|
| 4/1 | Aさん | ノートパソコン | 120000 |
| 4/2 | Bさん | デスクトップPC | 180000 |
| 4/3 | Aさん | 周辺機器 | 15000 |
| 4/4 | Cさん | ノートパソコン | 135000 |
この表で売上金額の合計を出す場合、D列に対して次のように入力します。
=SUBTOTAL(9,D2:D5)
その後、担当者で「Aさん」だけにフィルターをかけると、表示されているAさんの売上だけが合計されます。フィルター条件を「ノートパソコン」に変更すれば、ノートパソコンだけの売上合計に切り替わります。
SUBTOTAL関数とフィルターを組み合わせる基本手順は、次の通りです。
- 集計したい表を用意する
- 集計結果を表示したいセルにSUBTOTAL関数を入力する
- 関数番号と集計範囲を指定する
- 表にフィルターを設定する
- 条件を絞り込んで、表示行だけの集計結果を確認する
この手順で設定しておくと、フィルター条件を変更するたびに集計結果が自動で変わります。
フィルター後の合計を頻繁に確認する表では、SUM関数よりもSUBTOTAL関数を使うほうが管理しやすくなります。特に、月別・担当者別・カテゴリ別など、複数の切り口で同じ表を確認する場合に便利です。
SUBTOTAL関数で非表示行を除外して集計する方法
SUBTOTAL関数を使うときは、フィルターで非表示になった行と、手動で非表示にした行の扱いを分けて考える必要があります。
フィルターで非表示になった行は、1～11の関数番号でも、101～111の関数番号でも集計されません。しかし、手動で行を非表示にした場合は、使う関数番号によって結果が変わります。
合計でよく使う9と109の違いは、次の通りです。
| 数式 | フィルターで非表示の行 | 手動で非表示にした行 |
|---|---|---|
| =SUBTOTAL(9,D2:D20) | 除外する | 含める |
| =SUBTOTAL(109,D2:D20) | 除外する | 除外する |
手動で非表示にした行も集計から外したい場合は、109を使います。平均なら101、件数なら102または103のように、100番台の関数番号を選びます。
非表示行を除外したい場合は、次のような指定が使いやすいです。
- 合計を求める：=SUBTOTAL(109,D2:D20)
- 平均を求める：=SUBTOTAL(101,D2:D20)
- 数値の件数を数える：=SUBTOTAL(102,D2:D20)
- 空白以外の件数を数える：=SUBTOTAL(103,A2:A20)
手動で行を非表示にする運用がある表では、100番台の関数番号を使うと、表示されている行に近い条件で集計しやすくなります。
ただし、列を非表示にした場合の扱いは行の非表示とは違います。SUBTOTAL関数で非表示行を扱うときは、行を非表示にしているのか、列を非表示にしているのかも確認すべきポイントです。
SUBTOTAL関数で小計を入れた表を作る方法
SUBTOTAL関数は、カテゴリごとの小計を入れた表にも向いています。部署別、商品カテゴリ別、月別などで小計を出し、最後に総計を表示したい場合に使いやすい関数です。
たとえば、商品カテゴリごとに売上を整理している表では、各カテゴリの下に小計行を入れることで、内訳を確認しやすくなります。
小計行の入力方法は以下となります。
- 小計を表示したい行にカーソルを置く
- =SUBTOTAL(9, 対象範囲) を入力する
- カテゴリごとに繰り返す
- 総合計行にも同様にSUBTOTAL関数を入れる
入力結果の例は以下のようになります。
| 商品カテゴリ | 商品名 | 売上金額 |
|---|---|---|
| ノートパソコン | 商品A | 120000 |
| ノートパソコン | 商品B | 135000 |
| ノートパソコン小計 | 0 | |
| 周辺機器 | 商品C | 15000 |
| 周辺機器 | 商品D | 8000 |
| 周辺機器小計 | =SUBTOTAL(9,C5:C6) |
このように小計行を入れると、カテゴリごとの金額を確認しやすくなります。さらに、表全体の総計にもSUBTOTAL関数を使うと、SUBTOTAL関数で計算された小計行を重複して集計しにくくなります。
小計を作るときのポイントは、次の通りです。
- 小計したいカテゴリごとにデータを並べる
- 各カテゴリの下に小計行を入れる
- 小計行にはSUBTOTAL関数を使う
- 総計にもSUBTOTAL関数を使う
- 小計行を通常のSUM関数で重複集計しないようにする
小計と総計を同じ表に入れる場合は、重複計算を防ぐ設計が大切です。SUBTOTAL関数を使うことで、集計行を含む表でも扱いやすくなります。
また、Excelのフィルターと組み合わせれば、小計を含む表でも表示条件に応じた集計ができます。月別の売上や担当者別の成果を確認したい場合にも、SUBTOTAL関数を活用できます。
SUBTOTAL関数が使えない・結果がおかしい場合の対処法
SUBTOTAL関数で集計結果が想定と合わない場合は、関数番号や参照範囲、フィルター設定を順番に確認します。特に、9と109の使い分けを間違えると、非表示行の扱いで結果が変わるため注意が必要です。
確認すべき主なポイントは、次の通りです。
- 関数番号が目的に合っているか
- 集計範囲に不要な行や列が含まれていないか
- フィルターが正しく設定されているか
- 手動で非表示にした行を含めたいのか、除外したいのか
- 数値が文字列として入力されていないか
- 小計行や見出し行まで範囲に含めていないか
これらを確認すると、SUBTOTAL関数の結果がずれる原因を見つけやすくなります。
たとえば、合計したいのに平均の関数番号1を指定していると、当然ながら合計値は表示されません。また、数値に見えるデータが文字列として入力されている場合、COUNTやSUM相当の集計で想定通りに扱われないことがあります。
よくある原因と対処法は、次の表で整理できます。
| 状況 | 考えられる原因 | 対処法 |
|---|---|---|
| 合計が合わない | 関数番号が違う | 合計なら9または109を指定する |
| 非表示行が含まれる | 9を使っている | 手動非表示を除外するなら109を使う |
| 件数が合わない | COUNTとCOUNTAの使い分け違い | 数値のみは2、空白以外は3を使う |
| エラーが出る | 引数や範囲指定の入力ミス | カンマや範囲指定を確認する |
| フィルター後に変化しない | 対象範囲がずれている | フィルター対象の表と集計範囲を確認する |
SUBTOTAL関数は便利ですが、関数番号と範囲指定を間違えると結果が変わります。集計結果がおかしいと感じた場合は、数式を削除して入れ直す前に、関数番号・参照範囲・非表示行の扱いを順番に確認すると修正しやすくなります。
SUBTOTAL関数を使って表示行の集計を正しく確認しよう
SUBTOTAL関数は、フィルター後の表示行だけを集計したいときに役立つExcel関数です。合計・平均・件数・最大値・最小値などを関数番号で切り替えられるため、売上表や在庫表、一覧データの確認を効率よく進められます。
特に、フィルターで絞り込んだ結果を集計したい場合や、手動で非表示にした行を除外したい場合は、SUM関数ではなくSUBTOTAL関数を使うことで、目的に合った集計結果を確認しやすくなります。
まずは利用頻度の高い「9」と「109」の違いを押さえ、合計したい範囲にSUBTOTAL関数を設定してみましょう。日々のExcel作業で正確な集計を行いたい場合は、表の目的に合わせて関数番号を使い分けることが大切です。
