EXCEL – SUBTOTAL関数とLOOKUP関数で複数の小計と合計に対応するコピペ計算式

Excelで小計を求める時、SUM関数よりもSUBTOTAL関数を使いますが、参照範囲の最初や最後に行を挿入して追加した場合にはそのセルは集計に含まれず、参照範囲を選択しなおす必要があったりします。
また、例えば見積書などのように、項目ごとに小計があり、複数の小計がある場合など、それぞれに参照の引数を範囲選択したり入力したりしないといけないので面倒ですし、ミスの元にもなりかねません。
そこで、同じ式をコピペするだけで項目ごとの複数の小計や合計に対応出来る計算式を作りました

まずは完成形

最終的な完成形はこちらの式になります。この式が完成するまでの過程を以下に説明します。

=IF(G20="合計",SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))):H19),IF(IF(ISERROR(LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19))),0,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))>IF(ISERROR(LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19))),1,LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19))),SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="合計"),
ROW(H$1:H19)))):H19),SUBTOTAL(9,IF(ISERROR(INDEX(H:H,LOOKUP(1,0/(G$1:G19="小計"),
ROW(H$1:H19)))),H$1,INDEX(H:H,LOOKUP(1,0/(G$1:G19="小計"),ROW(H$1:H19)))):H19)))

SUBTOTAL関数とLOOKUP関数で作るこの計算式の考え方

画像はSUBTOTAL関数のみで集計した表です。B列の数値を数カ所で小計し、最終行に合計を出したものです。A列のA・B・Cで小計し、D・E・F・Gで小計し…で、最後にすべての合計です。

SUBTOTAL関数は

=SUBTOTAL(集計方法,参照1,[参照2],…)

ですので、それに従って集計方法を9(合計値=SUM)とします。また、SUBTOTAL関数はのその集計に他のSUBTOTAL関数の値を含みませんので、B列の小計セルと合計セルは以下のようになっています（合計セルはB1セルからB18セルのすべてを参照しています）。

=SUBTOTAL(9,B1:B3)
=SUBTOTAL(9,B5:B8)
=SUBTOTAL(9,B10:B11)
=SUBTOTAL(9,B13:B17)
=SUBTOTAL(9,B1:B18)

今回作る式の考え方は、SUBTOTAL関数が他のSUBTOTAL関数の値を含まないという性質を利用しまして、当該セルより上の直近の「小計」行セルから当該セル直上セルまでを集計します。
B9セルの小計であれば、B4セルからB8セルまでを集計するということになります。

解説

LOOKUP関数を使い、検索範囲内で一番下にある「小計」行の行番号を取得する

SUBTOTAL関数の参照範囲にINDEX関数とLOOKUP関数を用いて、当該セルより上にある直近の小計から当該セル直上セルまでの合計値を計算します。

B9セルには下記の計算式が入っています。これをB4セルとB12セルとB18セル、B19セルにコピペします。直近の小計セルにもSUBTOTAL関数が入るので、集計には含まれません。
例えばこのB9セルの式の場合は、B4セルからB8セルまでを集計しますが、小計であるB4セルの値は加算されません。

=SUBTOTAL(9,INDEX(B:B,LOOKUP(1,0/(A$1:A8="小計"),ROW(B$1:B8))):B8)

ここで疑問になるのはLOOKUP関数だと思います。

LOOKUP(1,0/(A:A8=”小計”),ROW(B:B8)) の謎

ここでLOOKUP関数は、INDEX関数の行番号の役割を果たしています。

LOOKUP(検査値,検査範囲,[対応範囲])

引数の検査範囲 0/(A:A8=”小計”) の意味から解明

A$1:A8=”小計”

1. A$1:A8 を行単位で検索し、その値を”小計”と比較。
2. “小計”と等しいならTRUE(1)を返し、等しくなければFALSE(0)を返す。

0/(A$1:A8=”小計”)

1. A$1:A8=”小計” の結果がTRUE(1)なら 0/1 で 0、FALSE(0)なら 0/0（0で除算） でエラー（#DIV/0!）を返す。

LOOKUP(1,0/(A$1:A8=”小計”),ROW(B$1:B8))

1. 第二引数の検査範囲が 0 の場合、検査値の 1 より小さいので次を検索し、第二引数の検査範囲がエラーの場合は無視してスルーされる。つまり、1より小さい0セル（”小計”セル）とエラーセルの2通りしかない。
2. 上記を繰り返し、最後に検索された（検査値の 1 より小さい 0 の）”小計”セルが LOOKUP関数の検査結果セルとなる。
3. 第三引数により、検査結果に対応した B$1:B8 の行番号が返される。

これがINDEX関数の行番号となります。

INDEX関数でSUBTOTAL関数の参照範囲の一端を求める

INDEX(B:B,LOOKUP(1,0/(A$1:A8="小計"),ROW(B$1:B8)))

LOOKUP関数で求めた行番号を持つB列セルを返します。
つまりこれが、当該セルより上の直近の「小計」行セルということになります。
この「小計」行セルから当該セル直上のセル（この場合はB8セル）をSUBTOTAL関数の参照範囲とすれば、複数小計対応の計算式になります。

=SUBTOTAL(9,INDEX(B:B,LOOKUP(1,0/(A$1:A8="小計"),ROW(B$1:B8))):B8)

ただ、これだけでは、最上位の「小計」セル（B4セル）はエラー（#N/A）となり（最上位なので当該セルより上にLOOKUP関数の検査値が見つからない）、「合計」行の値は 0 となってしまいます（直上にSUBTOTAL関数の「小計」セルがあるので合計値が0となる）。

IF関数で最上位の「小計」セルと「合計」セルに対応する

画像は同様の表をD列とE列に作ったものです。SUBTOTAL関数の参照範囲の一端を求めるINDEX関数がエラーの場合とD列セルが”合計”の場合に1行目（E$1セル）IF関数を追加します。
これにより、すべての「小計」セルと「合計」セルにコピペで使用できる複数小計対応の計算式となります。

=SUBTOTAL(9,IF(OR(ISERROR(INDEX(E:E,LOOKUP(1,0/(D$1:D3="小計"),ROW(E$1:E3)))),D4="合計"),
E$1,INDEX(E:E,LOOKUP(1,0/(D$1:D3="小計"),ROW(E$1:E3)))):E3)

しかしこれだと、例えば項目ごとに合計する場合など、合計行が複数ある場合に対応できません。