[Excel][関数] 検索した文字列の位置から合計やカウントをするワザ（ADDRESS関数、MATCH関数、INDIRECT関数）

ピボットテーブルを使った場合など、横軸が可変の表になる場合があったりします。
あくまで一例にすぎませんが、動的な集計をMATCH関数、ADDRESS関数、INDIRECT関数を使って行ってみます。

MATCH関数

MATCH関数は、指定した範囲のセルから指定した文字列を検索し、位置を返します。
検索する方向は、横または縦のどちらかでなくてはなりません。

実際に例を使って試してみます。

以下の関数を書いてみます。

=MATCH("2013 集計",$A$1:$D$1,0)

結果は以下となります。

2

これは、「A1～D1の中で、A1から二つ目のB位置に"2013 集計"があるよ」という意味です。
縦に検索範囲を広げると、縦方向の位置が帰ります。
BOX型で指定してしまうと、エラーとなります。

ADDRESS関数

ADDRESS関数は、指定した位置のセル参照文字列を返します。
「縦１、横２」のセル参照が欲しい場合は以下のようにします。

=ADDRESS(1,2)

結果は以下のようになります。

$B$1

ここで、MATCH関数と組み合わせると、例えば「2013 集計」のセル参照を作ることができます。

=ADDRESS(1,MATCH("2013 集計",$A$1:$D$1))

結果は以下です。

$B$1

INDIRECT関数

ADDRESS関数でセル参照まで作成したら、次は値をとりたいところです。
INDIRECT関数を使用すると、セル参照の値が取得できます。
"$B$1"セルの値を取得する場合は、以下のようにします。

=INDIRECT("$B$1")

結果は以下のようになります。

2013 集計

ここで、今までに出てきたMATCH関数とADDRESS関数を併用すると、
「ある文字列を検索して、その周辺のセルの値をとる」ことが可能になります。
まずは単純に「2013 集計」文字列を取得してみます。

=INDIRECT(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)))

結果は以下です。

2013 集計

次に、一番やりたい「2013 集計」列の合計値を取得してみます。
ここからは、かなり細かくなりますので、丁寧に説明します。

まずは、結論から。「2013 集計」列はB列ですので、「$B:$B」文字列を作ります。

そのため、"$B", ":", "$B"の3つの文字列を作成して、INDIRECT関数で内容を参照しつつ、
sum関数で合計します。

"$B"を作成するためには、"$B$1"から"$B"を切り取ります。
アルファベットの列は桁数が増えるので（ABなど）、「後ろから2文字切り取る」ことをします。

以下の関数になります。

①=ADDRESS(1,MATCH("2013 集計",$A$1:$D$1))とすると、
=LEFT(①, LEN(①) - 2)になります。
よって、以下のようになります。
=LEFT(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)), LEN(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1))) - 2)

結果は以下です。

$B

最後、sum関数を使って、集計してみます。

①=ADDRESS(1,MATCH("2013 集計",$A$1:$D$1))
②=LEFT(①, LEN(①) - 2)
=SUM(INDIRECT(② & ":" & ②))となります。

=SUM(INDIRECT(LEFT(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)),LEN(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)))-2)&":"&LEFT(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)),LEN(ADDRESS(1,MATCH("2013 集計",$A$1:$D$1)))-2)))

結果は以下となります。

56811

色々なことに応用が効くので、試してみてください。