- 公開日：
- 更新日：
ExcelのSUBTOTAL関数の使い方｜集計方法によって様々な集計をする
SUBTOTAL関数は集計方法の条件を指定することでさまざまな集計を求めることができます。合計の他にも平均や積も1つのSUBTOTAL関数で求めることができます。
とくに小計やオートフィルターを使うときはSUM関数やSUMIF関数ではなくSUBTOTAL関数を使用しましょう。
SUBTOTAL関数の基本的な使い方から応用まで幅広くご紹介しています。
SUBTOTAL関数とは？
SUBTOTAL（サブトータル）関数は、リストまたはデータベースの集計値を返す関数です。
SUBTOTAL関数の書式
まずはSUBTOTAL関数の書式を確認しましょう。
書式は「=SUBTOTAL(集計方法,参照1,[参照2],…)」のように記述します。少なくとも2つの引数を指定します。
※引数（ひきすう）とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。
第1引数（集計方法）
1番目の引数は「集計方法」です。この引数は必須です。集計に使用する方法を1～11の番号で指定します。番号ごとにどんな集計ができるかを下記の表でまとめています。目的の集計方法の番号を指定します。
非表示の値を含めるか、含めないかで番号が異なります。非表示の値を含める場合は「1～11」、含めない場合は「101～111」を指定します。
オートフィルターで絞り込んだ抽出結果に関しては「1～11」「101～111」どちらを指定しても結果は同一になります。あくまで手動で行を非表示にした場合に結果が異なってきます。
| 集計の目的 | 集計方法 （非表示の値も含める） |
集計方法 （非表示の値を無視する） |
同等の集計関数 |
|---|---|---|---|
| 平均値を求める | 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 |
第2引数（参照）
2番目の引数は「参照」です。「参照」は少なくとも1つは必須です。最大で254個まで指定することが可能です。
集計したいセル範囲またはセル参照を指定します。
SUBTOTAL関数の使い方（実践）
実際にSUBTOTAL関数を使用する例をご紹介します。引数をどのように使用するか見てみましょう。
上図は日付、商品、単価が入力されている表データです。表の一番下17行目に単価の総合計を求める欄があります。表データで表示されている単価の合計を求めましょう。【D17セル】を選択します。
D17セルに直接『=SUBTOTAL(9,』と入力します。SUBTOTAL関数の集計方法として「9」を指定しました。「9」を指定すると合計を求めます。
次に集計したい範囲を指定します。「=SUBTOTAL(9,」に続いて『D3:D16)』と入力します。セルに入力するかわりにマウスでドラッグして選択することでも同様です。数式が入力できたら【Enter】キーを押して確定します。
D17セルにSUBTOTAL関数を使って合計した値が表示されました。
フィルターをかけた際、SUBTOTAL関数の結果がどのようになるのか確認してみましょう。
①絞り込みたい項目（例では商品）の列見出し横の【▼】ボタンを押します。②並び替えや絞り込む項目の候補が表示されますので②【（すべて選択）】のチェックを外し、③【バナナ】を選択し、④【OK】ボタンを押します。
「バナナ」で絞り込んだ結果が表示されました。総合計を確認するとフィルターで絞り込んだ「バナナ」だけの合計が自動的に反映されています。
非表示の値を含める場合/含めない場合
SUBTOTAL関数の引数「集計方法」では、非表示の値を含める場合と含めない場合で指定する番号が異なります。含める場合は「1～11」、含めない場合は「101～111」を指定します。
実際にどのような違いがあるかを確認してみましょう。
非表示の値を含める場合
上図の場合、単価の総合計をD17セルにSUBTOTAL関数で求めています。数式は「=SUBTOTAL(9,D3:D16)」を入力しています。
集計方法では非表示の値を含める場合の合計値を求める番号として「9」が引数に指定されています。
| 集計の目的 | 集計方法 （非表示の値も含める） |
集計方法 （非表示の値を無視する） |
同等の集計関数 |
|---|---|---|---|
| 合計値を求める | 9 | 109 | SUM |
では実際に行を非表示にするとSUBTOTAL関数の結果が変更されるか確認しましょう。
①10行目の上で【右クリック】し、②コンテキストメニューが表示されるので【非表示】を選択します。
10行目が非表示になりましたがSUBTOTAL関数の結果はそのままになっています。これは引数「集計方法」に非表示の値を含める場合の「9」を指定したからになります。
非表示の値を含めて集計したい場合は「1～11」を指定しましょう。
非表示の値を含めない場合
今度は非表示の値を含めない場合を確認してみましょう。数式は「=SUBTOTAL(109,D3:D16)」としています。
非表示の値を含めない場合として合計値を求める「109」を引数に指定しています。
| 集計の目的 | 集計方法 （非表示の値も含める） |
集計方法 （非表示の値を無視する） |
同等の集計関数 |
|---|---|---|---|
| 合計値を求める | 9 | 109 | SUM |
10行目が非表示になっているので10行目以外の合計が総計になっています。これは引数「集計方法」に非表示の値を含めない場合の「109」が指定されているからになります。
非表示の値を含めない集計をしたい場合は「101～111」を指定しましょう。
SUBTOTAL関数とSUM関数の違い
合計を求める関数としてSUM関数があります。SUM関数と比べてSUBTOTAL関数はどんなところが便利なのでしょうか？その違いを見ていきましょう。
非表示にした行を集計対象から除外できるかどうか
上図の単価の合計をSUM関数とSUBTOTAL関数を使ってそれぞれ求めます。
まずSUM関数を記述します。D2セルを選択し、数式バーに『=SUM(D6:D19)』と入力します。
次にSUBTOTAL関数を記述します。D3セルを選択し、数式バーに『=SUBTOTAL(9,D6:D19)』と入力します。
合計の結果としてはSUM関数、SUBTOTAL関数の両方とも「3672」になりました。SUM関数、SUBTOTAL関数どちらを使っても同じと思うかもしれませんが、フィルターをかけたときに違いが出てきます。
上図は商品を「バナナ」で絞り込んだ結果です。SUM関数とSUBTOTAL関数の結果に違いがでました。
SUM関数はフィルターをかける前に指定したすべての単価を合計しています。一方SUBTOTAL関数はフィルターで絞り込まれた後に表示されている単価のみを合計しています。
フィルターをかけ、合計を求めたいときはSUBTOTAL関数にしましょう。
参照内の他のSUBTOTAL関数を集計対象から除外できるかどうか
小計が入っている表で総計を求める場合、SUM関数で求めると小計も一緒に合計されてしまうことがあります。小計を除いて総計を求めたいときはSUBTOTAL関数を使用しましょう。
D7セルにあるりんごの小計を確認するとSUBTOTAL関数で求めた結果「392」が表示されています。
D22セルにある総計を確認するとSUBTOTAL関数で求められており、参照内に含まれている小計の他のSUBTOTAL関数の結果は除外されています。
SUBTOTAL関数とCOUNTIF関数の組み合わせ
COUNTIF関数はフィルターを考慮しないためフィルターしても結果が変わりません。SUBTOTAL関数と組み合わせることでフィルターをかけてもCOUNTIF関数の結果が反映される方法を以下の記事で説明しています。
ExcelのCOUNTIF関数の使い方｜条件に一致するデータの個数表示
SUBTOTAL関数とフィルターの組み合わせ
SUBTOTAL関数はフィルターと一緒に使われることが多いです。フィルター（オートフィルター）の設定方法から様々な使い方を以下の記事でご紹介しています。
エクセルのフィルター（オートフィルター）の設定から様々な使い方
他の関数も合わせてチェック！
Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。