【関数】フィルターで表示中のレコードに連動して集計できる「SUBTOTAL」
AさんAさん

SUMCOUNTAは表の全レコードの集計に便利ですが、フィルターで絞り込んだ場合に対応してくれると、もっと便利なのですが。
こうした場合、何か便利な方法はありますか?

森田森田

その場合は、関数のSUBTOTALを活用すると良いですよ!
では、SUBTOTALの使い方について解説していきますね。

はじめに

この記事は関数の概要とSUMの使い方を把握していることが前提です。

参考記事

関数の概要とSUMの使い方については以下の記事をご参照ください。


フィルター操作と連動して集計結果を可変にしたい場合は「SUBTOTAL」が有効

SUMCOUNTAでの集計は、表の全レコードを対象にした場合に便利です。

ただし、商品別などの条件でフィルターを用いてレコードを絞り込んでも、非表示状態のレコードも集計対象のままとなり、条件に対応した集計に対応できません。

こんな場合、関数の「SUBTOTAL」を使うことで、フィルター操作で表示中のレコードのみを対象に集計することが可能となります。
ちなみに、SUBTOTALは「サブトータル」と呼びます。

なお、SUBTOTAL基本的な11種類の集計方法を指定することが可能です。

フィルター操作と連動して集計結果を可変にしたい場合はSUBTOTALを使用していきましょう。

SUBTOTALの構文

SUBTOTALの構文は以下の通りです。

=SUBTOTAL(集計方法,参照1,[参照2],...)
リストまたはデータベースの集計値を返します。

引数[参照2]以降は省略可能です。集計したいセル範囲が複数ある場合に指定しましょう。

SUBTOTALの使用結果イメージ

SUBTOTALで合計を集計したイメージは以下の通りです。
今回は「金額」列の全データを合計した後、フィルター操作で「商品名」列を「バナナ」で絞り込んでみました。

参考記事

フィルターの詳細を知りたい方は以下の記事をご参照ください。

SUBTOTALの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

  1. 関数を挿入するセルを選択
  2. =sub」と入力
  3. サジェストから「SUBTOTAL」を選択し、「Tab」キーで確定
  4. サジェストから任意の集計方法を選択し、「Tab」キーで確定
  5. コンマ(,)を入力
  6. 集計したいセル範囲を選択
  7. Enter」キーで確定

手順②の際にIMEを半角英数モードにすること。
手順④の数値は覚えているなら直接入力してもOK
手順⑤は矢印キーでもマウスでもOK

【参考】SUBTOTALで行の表示/非表示で集計結果を変えたい場合は

SUBTOTALはフィルター操作で非表示にしたレコードを集計の対象外にできますが、手動で非表示にした行を集計対象にするか否かは引数「集計方法」で指定することが可能です。
手動で非表示とは、任意の行で右クリック→「非表示」など。

非表示の行を集計対象に含むか、無視するかで引数「集計方法」で指定する番号は以下の通り変わります。

集計方法 使用関数 集計内容
非表示の行を含む 非表示の行は無視
1 101 AVERAGE 平均
2 102 COUNT 数値の個数
3 103 COUNTA 空白以外の個数
4 104 MAX 最大値
5 105 MIN 最小値
6 106 PRODUCT
7 107 STDEV 標準偏差(標本)
8 108 STDEVP 標準偏差(母集団)
9 109 SUM 合計
10 110 VAR 不偏分散
11 111 VARP 標本分散

実際に、手動で行を非表示にして合計を集計する場合に、「集計方法」が「9」と「109」で集計結果がどう変わるかを見てみましょう。

ご覧の通り、109」の方は非表示の行を無視して集計されています。

番号によって、このように集計結果が変わることも知っておくとベターです。

ただし、実務において、手動で特定のレコードを非表示にすることは望ましい動作ではないため、原則フィルター操作のみで非表示させる運用が望ましいでしょう。

よって、基本は「集計方法」で指定する番号は「1~11」で良いと考えます。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_SUBTOTAL.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 関数を挿入するセルを選択
    ※今回はG22セル
  2. =sub」と入力
  3. サジェストから「SUBTOTAL」を選択し、「Tab」キーで確定
    ※今回は「G2:G21
  4. サジェストから任意の集計方法を選択し、「Tab」キーで確定
    ※今回は「9
  5. コンマ(,)を入力
  6. 集計したいセル範囲を選択
  7. Enter」キーで確定

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

SUBTOTALはフィルター操作での可視セルのみを対象に集計でき、フィルターやスライサーと相性が良いです。

元データの表の中で集計する場合、知っておくと選択肢が増えて便利ですね。

なお、SUBTOTAL以外のExcelでのデータ集計の各種テクニックについて、拙著で解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

SUBTOTALはフィルター操作を前提にした表の集計時に役立つ関数ですね。
なお、SUBTOTALテーブルのオプション(テーブルスタイルオプション)の「集計行」をONにした場合に自動挿入される関数でもあります。

実務でフィルター操作を前提にした集計を行う機会がある方は、ぜひ覚えてみてください。