Excel(エクセル)関数逆引き MATCH+INDEXの参照範囲にデータが追加された場合自動追加する。

ExcelでMATCH+INDEXを使用すると、範囲指定をするため、最後の行のデータを追加するたびに範囲指定を変えなければ為らない。
そこで、データが追加されても自動で範囲指定を更新する関数を使用する。

Excel VBA小技集AでMATCH+INDEXの使い方を記載したが、参照範囲を設定しなければならず、データを追加した場合、参照範囲を変更しなければならない。

グラフにデータが追加された時、自動追加できるようにした様に(こちらを参照)、MATCH+INDEXも自動追加できるようにする。

テンプレートはこちら。ダウンロードはこちら


INDEX関数で行の最後の値の行数を検索し参照範囲を最後の行までとして、MATCH関数で検索する。



D8に「=MATCH(A8,C2:INDEX(C:C,COUNTA(C:C)+1,1))」を入れている。

C2:INDEX(C:C,COUNTA(C:C)+1でC2から最後の行を参照範囲としている(C2:C6)。

COUNTA(C:C)+1はC1が空白行でカウントされないため1を足しておく。

C2:INDEX・・・の記載方法は、INDEX関数で返される値は行列番号(C6)で、表示するときは=C6で値を表示しているので、このような記載方法が可能。

参照範囲が決まれば、MATCH関数でA8「英ポンド(円)(9日、21:46) 」を検索し、行数3を返す。


次はその行の左側1列のB4の値「C」を検索してみる。INDEXとMATCHで検索する。



D9に「=INDEX(B2:INDEX(G:G,COUNTA(G:G)+1),MATCH(A8,C2:INDEX(C:C,COUNTA(C:C)+1,1)),1)」を入れている。

B2:INDEX(G:G,COUNTA(G:G)+1)でINDEXの参照範囲を決める。

B2からGの最終行ということで、INDEX(G:G,COUNTA(G:G)+1)でGの最終行を決める(G6)。

MATCH(A8,C2:INDEX(C:C,COUNTA(C:C)+1,1))はA8「英ポンド(円)(9日、21:46) 」のある行番号を検索。

1はINDEX参照範囲の1列目を検索。

すると、「英ポンド(円)(9日、21:46) 」の左となりの「C」という値が検索される。

VLOOKUPとほとんど機能は同じであるが、拡張性は高いので使うのも良いかもしれません。


忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP