Aさん SUMやCOUNTAは表の全レコードの集計に便利ですが、フィルターで絞り込んだ場合に対応してくれると、もっと便利なのですが。
こうした場合、何か便利な方法はありますか？
森田 その場合は、関数の「SUBTOTAL」を活用すると良いですよ！
では、SUBTOTALの使い方について解説していきますね。
はじめに
この記事は関数の概要とSUMの使い方を把握していることが前提です。
関数の概要とSUMの使い方については以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照 …
フィルター操作と連動して集計結果を可変にしたい場合は「SUBTOTAL」が有効
SUMやCOUNTAでの集計は、表の全レコードを対象にした場合に便利です。
ただし、商品別などの条件でフィルターを用いてレコードを絞り込んでも、非表示状態のレコードも集計対象のままとなり、条件に対応した集計に対応できません。
こんな場合、関数の「SUBTOTAL」を使うことで、フィルター操作で表示中のレコードのみを対象に集計することが可能となります。
ちなみに、SUBTOTALは「サブトータル」と呼びます。
なお、SUBTOTALは基本的な11種類の集計方法を指定することが可能です。
フィルター操作と連動して集計結果を可変にしたい場合はSUBTOTALを使用していきましょう。
SUBTOTALの構文
SUBTOTALの構文は以下の通りです。
=SUBTOTAL(集計方法,参照1,[参照2],...)
リストまたはデータベースの集計値を返します。
引数[参照2]以降は省略可能です。集計したいセル範囲が複数ある場合に指定しましょう。
SUBTOTALの使用結果イメージ
SUBTOTALで合計を集計したイメージは以下の通りです。
今回は「金額」列の全データを合計した後、フィルター操作で「商品名」列を「バナナ」で絞り込んでみました。
フィルターの詳細を知りたい方は以下の記事をご参照ください。
表の中で任意の条件に合致するレコードのみ表示したい場合は「フィルター」が有効 表データから、特定の条件に合致す …
SUBTOTALの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
- 「=sub」と入力
- サジェストから「SUBTOTAL」を選択し、「Tab」キーで確定
- サジェストから任意の集計方法を選択し、「Tab」キーで確定
- コンマ（,）を入力
- 集計したいセル範囲を選択
- 「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
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はG22セル - 「=sub」と入力
- サジェストから「SUBTOTAL」を選択し、「Tab」キーで確定
※今回は「G2:G21」 - サジェストから任意の集計方法を選択し、「Tab」キーで確定
※今回は「9」 - コンマ（,）を入力
- 集計したいセル範囲を選択
- 「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
SUBTOTALはフィルター操作での可視セルのみを対象に集計でき、フィルターやスライサーと相性が良いです。
元データの表の中で集計する場合、知っておくと選択肢が増えて便利ですね。
なお、SUBTOTAL以外のExcelでのデータ集計の各種テクニックについて、拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 SUBTOTALはフィルター操作を前提にした表の集計時に役立つ関数ですね。
なお、SUBTOTALはテーブルのオプション（テーブルスタイルオプション）の「集計行」をONにした場合に自動挿入される関数でもあります。
実務でフィルター操作を前提にした集計を行う機会がある方は、ぜひ覚えてみてください。
