Office Hack

ExcelのSUBTOTAL関数の使い方|集計方法によって様々な集計をする

  • Release
  • Update

SUBTOTAL関数は集計方法の条件を指定することでさまざまな集計を求めることができます。合計の他にも平均や積も1つのSUBTOTAL関数で求めることができます。

とくに小計やオートフィルターを使うときはSUM関数やSUMIF関数ではなくSUBTOTAL関数を使用しましょう。

SUBTOTAL関数の基本的な使い方から応用まで幅広くご紹介しています。

SUBTOTAL関数とは?

SUBTOTAL(サブトータル)関数は、リストまたはデータベースの集計値を返す関数です。

SUBTOTAL関数の書式

まずはSUBTOTAL関数の書式を確認しましょう。

SUBTOTAL関数の書式

書式は「=SUBTOTAL(集計方法,参照1,[参照2],…)」のように記述します。少なくとも2つの引数を指定します。

※引数(ひきすう)とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。

第1引数(集計方法)

第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引数(参照)

2番目の引数は「参照」です。「参照」は少なくとも1つは必須です。最大で254個まで指定することが可能です。

集計したいセル範囲またはセル参照を指定します。

SUBTOTAL関数の使い方(実践)

実際にSUBTOTAL関数を使用する例をご紹介します。引数をどのように使用するか見てみましょう。

作業時間:5分

  1. 表データをを準備する

    表データをを準備する

    上図は日付、商品、単価が入力されている表データです。表の一番下17行目に単価の総合計を求める欄があります。表データで表示されている単価の合計を求めましょう。【D17セル】を選択します。

  2. 集計方法「9」を指定する

    集計方法を指定する

    D17セルに直接『=SUBTOTAL(9,』と入力します。SUBTOTAL関数の集計方法として「9」を指定しました。「9」を指定すると合計を求めます。

  3. 参照を指定する

    参照を指定する

    次に集計したい範囲を指定します。「=SUBTOTAL(9,」に続いて『D3:D16)』と入力します。セルに入力するかわりにマウスでドラッグして選択することでも同様です。数式が入力できたら【Enter】キーを押して確定します。

  4. SUBTOTAL関数の結果

    SUBTOTAL関数の結果

    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関数

上図の単価の合計をSUM関数とSUBTOTAL関数を使ってそれぞれ求めます。

まずSUM関数を記述します。D2セルを選択し、数式バーに『=SUM(D6:D19)』と入力します。

SUBTOTAL関数

次に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関数の結果が反映される方法を以下の記事で説明しています。

SUBTOTAL関数とフィルターの組み合わせ

SUBTOTAL関数はフィルターと一緒に使われることが多いです。フィルター(オートフィルター)の設定方法から様々な使い方を以下の記事でご紹介しています。

おすすめの商品をご紹介

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top