縦集計で小計がある場合の総合計の処理

小合計、中合計、大合計が複合する時、大きい方の合計に小さい方の合計がダブらないようにする方法
SUBTOTAL関数を使う。
SUBTOTAL関数サンプル
(画像をクリックすると、このサンプルがダウンロードできます)
※途中行だけ表示しています。見にくいので実際にExcelで開いて見て下さい。
小合計、中合計、大合計など、中間に「小計」が含まれている範囲での合計処理では、SUM関数で合計してしまうと、「小計」をさらに加えてしまいます。ならばと「小計」の行だけを拾って通常の「+」で加算してゆく方法もあり、実際はこの方が効率的なのですが、式を作る上でミスを誘発します。「小計」の行をSUMIF関数で探すのでは、新たな検索が発生してさらに効率が悪くなります。(このサンプルは、「データ」タブの「小計」で作成しました。)
そこでSUBTOTAL関数を使います。これは、集計範囲内にあるSUBTOTAL関数がある「小計」行を避けて集計してくれる便利な関数です。カッコ内の最初の「9」は合計を意味しており、この指示子を変えることで「平均(1)」「最大値(4)」「最小値(5)」なども集計できます。
Excelの「集計」機能を使って集計する場合も、実際にはこのSUBTOTAL関数が使われます。
画面の方は、合計行だけを画面に残せるように「グループ機能」を使っています。
R1C1参照形式の場合は

 =SUBTOTAL(9,R[-29]C4:R[-1]C4)
となります。

SUBTOTAL関数のカッコ内の最初の数字は、「集計方法」です。
集計関数 意味
1 AVERAGE 平均
2 COUNT 数値の個数
3 COUNTA データの個数
4 MAX 最大値(数値)
5 MIN 最小値(数値)
6 PRODUCT
7 STDEV 標準偏差
8 STDEVP 標準偏差
9 SUM 合計
10 VAR 分散
11 VARP 分散
この値を変えることで「平均」を算出したり、「最大値」「最小値」を算出したりできますが、通常ほとんどの場合は「合計」なので「9」をセットします。