INDIRECT関数で範囲を自動化する

INDIRECT関数で合計のSUMでもCOUNTでも何でも、範囲を自動化、可変化させる方法です。

以下の表があります。納品書や請求書など、決められたフォーマットの為、営業部と管理部を別の列にする事はできない、と仮定します。

	A	B	C	D	E	F
1	営業部	100
2		200
3		500
4	営業部　計		800
5
6	管理部	1,000
7		1,200
8	管理部　計		2,200
9

C4には、シンプルに　=SUM(B1:B3)　が入っています。C8にも　=SUM(B6:B7)　です。

営業部の500の下に400を追加する為、4行目の左端の青い部分で右クリック　→　挿入　をします。
500の下に400を入力しました。　さて、合計範囲は変わるでしょうか？
答えは、「変わらない」です。下のように、合計は800のままです。

	A	B	C	D	E	F
1	営業部	100
2		200
3		500
4		400
5	営業部　計		800
6
7	管理部	1,000
8		1,200
9	管理部　計		2,200

C5の計算式は　=SUM(B1:B3)　のままだからです。合計する範囲をB4まで自動で変えておいて欲しい所ですが、変わりません。
実務でこれに気付かなければミス確定です。たまに見かけるのではないでしょうか。

少し話がずれますが、こういったミスは、チェック不足によるミス、ケアレスミスではないと私は考えます。
フォーマットを作った者が想定していなかった事自体がミスであり、作業者が「業務中に数式を手直しする必要があるものを直さず放置していた」事がミスです。

ちなみに、3行目で挿入した場合（範囲の内側で行を挿入した場合）には範囲は自動で広がります。

さて、ここでは、「管理部」「管理部　計」の文字列に着目してみます。「営業部」の隣から「営業部　計」の隣までを合計範囲にできればよい、という考えです。

まず、それぞれが一番上から何番目にあるかを調べる数式を作ります。
D1に　=MATCH("営業部",A:A,0)　→　１
D5に　=MATCH("営業部　計",A:A,0)　→　５

	A	B	C	D	E	F
1	営業部	100		1
2		200
3		500
4		400
5	営業部　計		800	5
6
7	管理部	1,000
8		1,200
9	管理部　計		2,200

ここで欲しいのは、「B1:B5」という範囲です。
これを、INDIRECT関数を使って作ります。

INDIRECT関数は「指定される文字列への参照を返す」という、とてもわかりづらい説明の関数です。
よく見る使われ方は、事前に範囲に名前を付けておき、名前を参照して合計範囲を変える、というものでしょうか。

ここでは、計算式の中身として使います。

最終的に欲しいのは、　=SUM(B1:B5)　です。
イメージは、　=SUM("B"&1&":"&"B"&5)　です。ただ、このままでは文字列として認識されてしまうので、エラーになってしまいます。
これを解決するのがINDIRECT関数です。
=SUM(INDIRECT("B"&1&":B"&5))　とすれば、きちんと合計の1,200を返してくれます。
そして、１と５の部分をD1、D5に置き換えて、　=SUM(INDIRECT("B"&D1&":b"&D5))　とすれば営業部のほうは完成です。

	A	B	C	D	E	F
1	営業部	100		1
2		200
3		500
4		400
5	営業部　計		1,200	5
6
7	管理部	1,000
8		1,200
9	管理部　計		2,200

同様に、
D7に　=MATCH("管理部",A:A,0)
D9に　=MATCH("管理部　計",A:A,0)
C9に　=SUM(INDIRECT("B"&D7&":b"&D9))
で全て完成となります。