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

公開日: : Excel, 関数 , , , , ,

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

MATCH関数

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

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

売上表

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

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

結果は以下となります。

2

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

ADDRESS関数

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

=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

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

スポンサードリンク

関連記事

no image

[Excel][関数] 合計を求めるsum関数

Excelで合計を求めるsum関数を解説します。 書式 =sum([セル範囲])[/text

記事を読む

no image

[Excel][関数] 文字列関数(LEFT、RIGHT、MID)

文字列関数を紹介します。 LEFT、RIGHT、MID関数です。 LEFT関数

記事を読む

no image

[VBA] 文字列中の特定文字を検索する(InStr関数)

文字列中の特定文字を検索 InStr関数 使い方 InStr(開始位置, 対象文字列, 検

記事を読む

no image

[Excel VBA] ループ処理・構文(Do While - Loop、For - Next)

Excel VBAでループ処理を記述する構文 条件判定でループを行う場合(Do While ~ L

記事を読む

no image

[Excel] 時間の加算・減算(time関数)

時間の加算、減算の方法を紹介します。 Excelの日付や時間値は、表示値では整形されていますが

記事を読む

no image

[Excel VBA] 正規表現判定を行う(RegExp)

Excel VBAで正規表現判定を行う方法 今回は置換など考えず、シンプルに判定のみ行います。

記事を読む

スポンサードリンク

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

スポンサードリンク

no image
[Re:dash] BigQuery接続で日本語タイトルを出す(v2.0.0)

BigQueryは現在マルチバイトを含むSQLを発行することができない

no image
[Python] Nullの判定方法(None)

Nullの判定方法(None) 表記方法 Pythonでは、Nul

no image
[Python] 型の検査・判定

Pythonでの型の検査・判定方法 isinstance サン

no image
[python] 日付の取り扱い(datetime) 現在日付、計算、文字列変換(format)

現在日付 日付の計算 [crayon-5a165af39f98

no image
[python] 文字列結合

pythonでの文字列結合 サンプル

→もっと見る

  • 2017年11月
    « 10月    
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  
  • 2017年11月
    « 10月    
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  
PAGE TOP ↑