[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 VBA] ループ処理・構文(Do While - Loop、For - Next)

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

記事を読む

no image

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

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

記事を読む

no image

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

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

記事を読む

no image

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

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

記事を読む

no image

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

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

記事を読む

no image

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

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

記事を読む

スポンサードリンク

Message

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

スポンサードリンク

no image
CentOSにdockerをインストールしてみた

前置き dockerをcentos7にインストールしてみました

no image
awkでのgsubを使った文字列置換(正規表現)

まえおき bashなどのシェルスクリプトで、lsした結果とかを

no image
bashで並列処理する方法(xargs)

並列処理とは 通常、意識せずにプログラムを書くと、大体の場合は

no image
AWS Redshiftの使用ストレージ容量をクエリで調べる方法

用途 AWS Redshfitのコンソールを見れば、使用中のス

no image
pythonでstorage transfer serviceを使ってみた。

準備 pythonのインストールとかする。 このあたり。

→もっと見る

  • 2024年3月
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • 2024年3月
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
PAGE TOP ↑