SUBTOTAL関数の構文と使用例 -非表示の行を含めるか含めないか選べる集計- DL有 –
えくせるサイトウ
本記事はSUBTOTAL関数を解説します。
SUBTOTAL関数はEXCEL 365、EXCEL 2016以降で利用可能です。
EXCELのSUBTOTAL関数は
非表示の行を含めるか含めないかを選択できる集計方法を提供します。
Microsoft Excelの「数学/三角関数」のカテゴリに属します。
こんな時に便利！
SUBTOTAL関数を使用すると、非表示の行を含めるか含めないかを選択できます。
つまり、表示非表示に応じて、インタラクティブに集計結果を変えることができます。
SUBTOTAL関数の機能と構文
機能
![[関数の挿入ダイアログ]から見るSUBTOTAL関数の機能説明](https://biz-data-analytics.com/wp-content/uploads/2024/06/Slide_792.webp)
[関数の検索]のテキスト内に、
「SUBTOTAL」と入力し、[検索開始]をクリックすると、関数がヒットします。
詳細の使用法に興味のある方はタブ[参考記事]をご拝読ください。
リストまたはデータベースの集計値を返します。
[関数の挿入]ダイアログより
分かりづらい説明かと思います。
簡略化すると、非表示の行を含めるか含めないかを選択できる集計方法を提供します。
ExcelのSUBTOTAL関数は、「サブトータル」と呼びます。
部分集計を意味し、特定の条件に基づいたニュアンスが読み取れます。
構文
![[関数の引数]ダイアログボックスから見るSUBTOTAL関数の構文](https://biz-data-analytics.com/wp-content/uploads/2024/06/Slide_793.webp)
=SUBTOTAL(集計方法,参照1)
参照1に対して、集計方法を集計します。
第1引数：集計方法
![[関数の引数]ダイアログボックスから見るROUNDUP関数の第1引数「集計方法」](https://biz-data-analytics.com/wp-content/uploads/2024/06/image-1.png)
集計方法: には、にはリストの集計に使用する関数を、 1 ~ 11 の番号で指定します。
[関数の挿入]ダイアログより
説明不足がなので、補足します。
第1引数は集計方法で、1～11または101～111の番号を指定します。
これらの番号は、非表示の行を集計に含めるかどうかを決定します。
以下の表は、各引数とその集計の挙動を示しています。
凡例：×＝非表示の行を集計に含まない、〇＝非表示の行を集計に含む
| 引数 | 機能 | 機能説明 | フィルター操作 | 手動操作 |
|---|---|---|---|---|
| 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 | 標準分散 | × | 〇 |
| 101 | AVERAGE | 平均 | × | × |
| 102 | COUNT | 数値の個数 | × | × |
| 103 | COUNTA | データの個数 | × | × |
| 104 | MAX | 最大値 | × | × |
| 105 | MIN | 最小値 | × | × |
| 106 | PRODUCT | 積 | × | × |
| 107 | STDEV.S | 不偏標準偏差 | × | × |
| 108 | STDEV.P | 標本標準偏差 | × | × |
| 109 | SUM | 合計 | × | × |
| 110 | VAR.S | 不偏分散 | × | × |
| 111 | VAR.P | 標準分散 | × | × |
第2引数：参照1
![関数の引数]ダイアログボックスから見るSUBTOTAL関数の第2引数「参照1」](https://biz-data-analytics.com/wp-content/uploads/2024/06/Slide_795.webp)
参照1: 参照1,参照2, …には集計するリストの範囲または参照を 1 ～254個まで指定します。
[関数の挿入]ダイアログより
第2引数には、集計を行いたいデータの参照1を指定します。
範囲はセルの範囲やテーブルの列を指定することができます。
第3引数以降は任意で、第2引数「参照1」と同じルールに従います。
この引数は、
一つ目の範囲とは異なる、バラバラの場所にあるセルや値を指定するのに特に便利です。
これを使用すると、
表の中で隣接していない、別々のセル範囲にあるデータをカウントできます。
たとえば、B列の２行目からD列の4行目までのデータだけでなく、
追加でB列の7行目からC列の9行目までのデータもカウントする場合、
第2引数として「B7:C9」を指定します。
SUBTOTAL関数の使用例
サンプルデータに沿い、基本操作を通して、使用例を解説します。
基本操作
オートフィルタ操作で非表示にした行を集計に含まないケース
売上列の合計を求めるために、
以下のようにSUBTOTAL関数を入力します。
![オートフィルタ操作で非表示にしたデータを含む場合の使用例：[関数の挿入]ダイアログボックスSUBTOTAL関数の入力結果](https://biz-data-analytics.com/wp-content/uploads/2024/06/Slide_796.webp)
[OK]をクリックすると、下記関数式が挿入されます。
=SUBTOTAL(9, B2:B4)
9を指定しているため、
オートフィルタで非表示されている行は集計に含まれず、手動操作で非表示している行は集計に含まれます。
なお、平均を求めるために、第1引数の9→1に変更します。
このように、第1引数を変更するだけ、多様な集計ができます。
手動操作で非表示にした行を集計に含まないケース
売上列の合計を求めるために、
以下のようにSUBTOTAL関数を入力します。
![手動操作で非表示にしたデータを含む場合の使用例：[関数の挿入]ダイアログボックスSUBTOTAL関数の入力結果](https://biz-data-analytics.com/wp-content/uploads/2024/06/Slide_797-1.webp)
=SUBTOTAL(109, B2:B4)
第1引数の9→109に変更すると、
オートフィルタまたは手動操作で非表示した行の両方ともが集計に含まれません。
オートフィルタ操作または手動操作の表示非表示で関数の結果が変化しています。
なお、平均を求めるために、第1引数の109→101に変更します。
このように、第1引数を変更するだけ、多様な集計ができます。
最後に
SUBTOTAL関数は、リストやデータベースの集計値を返す関数です。
また、ExcelのSUBTOTAL関数は、EXCEL 365、EXCEL 2007以降で利用可能です。
非表示の行を含めるか含めないかを選択できる集計方法を提供します。
是非、ダウンロードファイルを操作してみてください。
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
参考記事：
Excel関数を操作するための基本概念です。基本概念を理解すると、各関数の理解がより深まります。
構文に関する記事
Excel関数の引数と戻り値は何だっけ？という時に、役立つ記事です。
ダイアログボックスの使い方に関する記事
Excel関数名は何だっけ？VLOOKUP関数の使い方は何だっけ？という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。
参照形式に関する記事
関数式をコピーする時はどうしたらいい？ $マークは、どう付けたらいいだろう？
といった悩みをお持ちの方は以下をご覧ください。
