【EXCEL】SUBTOTAL関数で小計は含めずに合計を集計する方法

請求書などをExcelで作成すると、同じ列に小計と合計が記載される場合があります。この記事では、SUBTOTAL関数を使って小計を含めずに合計をかんたんに集計する方法を画像付きで解説しています。

SUBTOTAL関数について

Microsoftの公式サポートには、SUBTOTAL関数についての説明が載せられています。

リストやデータベースの集計値を返します。

SUBTOTAL＝小計（日本語）ですので、小計を求める時にも使える関数です。

構文

• ＝SUBTOTAL(集計方法,範囲1,[範囲2],…)

第1引数に「集計方法」を指定します。第2引数として「範囲」を設定します。

集計方法の種類

以下の表の通り、11種類あります。集計結果に非表示の値を含むか含まないかで、第1引数（集計方法）の値が変わります。

今回は、合計を求めるため「９」　を使います。

注意点

SUBTOTAL関数は、以下の特徴があるため注意して使いましょう。

• 列（縦方向）の範囲が集計される
• 集計方法の値に関係なく、フィルターの結果に含まれない非表示の行はすべて無視される

行（横方向）の範囲を集計する関数ではありません。

オートフィルターで非表示になっている行は、集計方法として1～11を使用しても、101～111を使用しても結果は同じになります。手動で非表示にした行を除外したい場合は、101～111を使用しないと正しい数値が得られません。この点は別の記事で解説します。

小計を含めないで合計を集計する方法

SUBTOTAL関数の特徴を生かします。参照範囲にSUBTOTAL関数がある場合、その値は集計から除外してくれるという特徴です。

つまり、SUBTOTAL関数で小計を計算し、それを含む範囲で合計を集計しても小計の値は除外して計算してくれるということです。SUM関数との違いで説明すると分かりやすいので、以下に記します。

SUM関数の場合

以下の表で考えてみます。月ごとの売上が「小計」にまとめられており、同じ列にすべての「合計」があります。

まず、SUM関数で月ごとの小計を出します。上の画像の通り、B7にSUM関数で範囲（B4:B6）を指定します。最後に「 ) 」を入力してEnterで小計値がでます。

同じように、5月、6月分の小計もSUM関数で集計しておきます。

最後に「合計」欄にもSUM関数を設定します。範囲はB4:B15にしてみます。予想がつくと思いますが。。。

「小計」の値まで含めて「合計」を計算しています。これでは、正確な集計ができません。

SUBTOTAL関数を使うとどうなるでしょうか。

SUBTOTAL関数の場合

同じ表を隣にコピーして、今度はSUBTOTAL関数で集計します。

セル（E7）で「=sub」と入力すれば候補が表示されます。↓キーでsubtotalを選択してTabキーで入力できます。

その後、第1引数（集計方法）の候補が表示されます。上の画像のように、今回は「9-SUM」を選択します。

第2引数（範囲）の選択です。第1引数（集計方法）の後にカンマ「,」を入れてから、SUM関数の時と同じように4月の売上（E4:E6）を選択します。SUM関数と変わらず、4月の小計が集計されます。5，6月の小計もSUBTOTAL関数で設定します。前述の通り、E7をコピーしてE11、E15にペーストすればOKです。

最後に「合計」(E16)にもSUBTOTAL関数を設定します。範囲はE4:E15です。結果はどうなるでしょうか。

各月の「小計」の値は除外して集計されていることが分かります。

参照範囲にSUBTOTAL関数がある場合、その値は集計から除外してくれるということです。つまり、小計をSUBTOTAL関数を使って計算しておけば、同じ列で合計を計算する時には小計の値を除外して計算してくれます。

まとめ

いかがでしたか。SUBTOTAL関数を使うと、同じ列にある小計を含めずに合計を集計することができます。様々な機会に活用できそうですね。

次回は、SUBTOTAL関数の特徴の一つ、非表示のセルを集計から除外する方法についてです。次回もお楽しみに！