ExcelのSUBTOTAL関数の使い方を解説いたします。
SUBTOTAL関数
SUBTOTAL関数は集計方法によって様々な集計をしてくれる関数になります。合計の他にも平均や積も1つのSUBTOTAL関数で求めることができます。
特に小計やオートフィルターを使うときはSUM関数やSUMIF関数ではなくSUBTOTAL関数を使用すると便利です。
SUBTOTAL関数の基本的な使い方から応用まで一緒に勉強していきましょう！
まずは、SUBTOTALの数式を確認します。書式は「=SUBTOTAL(集計方法,参照1,[参照2],…)」の様に記述して行きます。
少なくても2つの引数を指定しないといけないです。
※今まで行ってきたように「引数」とはExcelの関数を利用するために必要な情報になります。（）カッコの中に入力します。
1つ目の引数は、『=SUBTOTAL(集計方法,参照1,[参照2],…)』「集計方法」です。この引数は必須になります。
集計に使用する方法を1～11の番号で指定します。
番号ごとにどんな集計ができるかを下記の表を参考にして下さい。目的の集計方法の番号を指定してください。
非表示の値を含めるか、含めないかで番号が異なります。
非表示の値を含める場合は「1～11」、含めない場合は「101～111」を指定します。
オートフィルタ―で絞り込んだ抽出結果に関しては「1～11」「101～111」どちらを指定しても結果は同一になります。
あくまでも手動で行を非表示にした場合に結果が異なってきます。
2つ目の引数は、『=SUBTOTAL(集計方法,参照1,[参照2],…)』「参照」です。「参照」は少なくとも1つは必須になります。最大で254個まで指定することが可能です。
集計したいセル範囲またはセル参照を指定します。
例1：下の表は、日付、商品、単価が入力されている表です。
表の一番下17行目に単価の総合計を求める欄があります。表データで表示されている単価の合計を求めましょう。【D17セル】を選択します。
「D17」のセルに『 =SUBTOTAL(9,』と入力します。SUBTOTAL関数の集計方法として上記の表を参照すると合計の関数は「9」を指定すると合計を求めてくれます。
次に集計したい範囲を指定します。『=SUBTOTAL(9,』に続いて『D3:D16)』をマウスでドラッグしてください。
数式が入力できたら【Enter】キーを押して確定します。
フィルターを付けてみてください。（データの中をクリックして『データのタブ』をクリックし、並べ替えとフィルターグループのフィルターをクリックします。
列見出し横の【▼】ボタンを押します。②並び替えや絞り込む項目の候補が表示されますので、
②【（すべて選択）】のチェックを外し、③【バナナ】を選択し、④【OK】ボタンを押します。
「バナナ」で絞り込んだ結果が表示されましたね。総合計を確認するとフィルターで絞り込んだ「バナナ」だけの合計が自動的に計算されています。便利です！
例2：非表示の値を含める場合/含めない場合
フィルター機能をクリアしてください。最初のデータに戻ります。
SUBTOTAL関数の引数「集計方法」では、非表示の値を含める場合と含めない場合で指定する番号が違ってきますよ。
先ほどの集計方法の表を参考にして、含める場合は「1～11」、含めない場合は「101～111」を指定するんでしたよね。
実際にどのような結果になるかを確認してみましょう。
では実際に7行目の『バナナ』を非表示にするとSUBTOTAL関数の合計結果が変更されるか確認しましょう。
7行選択して、選択した範囲で右クリックします。非表示をクリックすると7行目に『バナナ』が非表示になりました。
７行目の『バナナ』が非表示になりましたがSUBTOTAL関数の結果はそのままになっています。
これは引数「集計方法」に非表示の値を含める場合の「9」を指定しているからになります。
非表示の値を含めて集計したい場合は「1～11」を指定しましょう。
今度は非表示の値を含めない場合を確認してみましょう。数式は「=SUBTOTAL(109,D3:D16)」としています。
非表示の値を含めない場合は引数に入れる数値は「109」でしたね。「109」を引数に指定すると7行目の数値が反映されなくなります。
では、SUM関数とSUBTOTAL関数の違いを見ていきましょう。
SUBTOTAL関数の方がどんな所が便利なのか見ていきましょう。
先程の表SUM関数も追加してみてみます。「D2」に =SUM(D6:D19) で合計を出します。「4072」と出ますよね。
次にSUBTOTAL関数を記述します。D3セルを選択し、数式バーに『=SUBTOTAL(9,D6:D19)』と入力します。
合計の結果としてはSUM関数、SUBTOTAL関数の両方とも「4072」になりましたね？
SUM関数、SUBTOTAL関数どちらも同じと思うかもしれませんよね。、フィルターをかけたときに違うんです。
商品のフィルターで「バナナ」に絞ってみてください。
SUBTOTAL関数の方はフィルターで絞り出した「バナナ」の合計に変わってくれます。「990」と合計が変化しました。
なんて便利なんでしょう。
SUM関数はフィルターをかける前に指定したすべての単価を合計します。
方やSUBTOTAL関数はフィルターで絞り込まれた後に表示されている単価のみを合計します。
フィルターをかけ、合計を求めたいときはSUBTOTAL関数を利用しましょうね。
因みに、先ほどの表をフィルターを解除して、フィルターを使って商品の昇順に並べ替えてください！
表のデータの中をアクティブセルにします。データのタブのアウトラインのグループの小計を選択します。
集計の設定のダイヤログボックスが出てきます。グループの基準は「商品」を選択、集計の方法は「合計」を選択します。
集計するフィールドは「数量」を選択し、「OK」をクリックすると下の表の様になります。
商品グループ毎の小計と総計が算出されています、小計と総計をクリックし数式バーで確認してください！
勝手にSUBTOTAL関数が入って合計が出されていることがわかります。
この方法を使うと関数の挿入をしなくても勝手にSUBTOTAL関数で計算してくれるんですね。とっても便利です。
SUBTOTAL関数のまとめ
SUBTOTAL関数はフィルタ機能をより便利に使える関数なんですね。今回は集計方法「9 – SUM」を使用してフィルタで絞り込んだ数値を合計しました。
他にもSUBTOTAL関数は集計方法を変更することでSUM以外の別の集計をすることができます。
例えば「2 – COUNT」を使用することでフィルタで絞られたセル数をカウントすることもできます。
このように皆さんもSUBTOTAL関数を使用してフィルタ機能をより便利に使ってくださいね！
これからもソフトキャンパスのブログ、Youtubeで色々な関数の使い方をご紹介しいて参ります。
是非参考にしてください！応援よろしくお願いいたします。
