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

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

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

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

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

OFFSET関数をまず使いこなせるようにする。



C1に「=OFFSET(A1,1,1,1,5)」を入れている。本来は値は出てこないが、参照範囲を1行とすると値は出てくる。

A1を基準として、1行、1列移動してB2を新たな基準とする。

B2を基準として15列をVLOOKUP等で使用する参照範囲とする。
ここではB2:F5が参照範囲となる。

参照範囲を1行とすると、なぜか参照範囲の一番上の値(C列の1行目)が出てくるので、A1を基準としてどこかに移動する場合にどこに移動したか確かめるのに使用しても良い。


次はOFFSET関数とVLOOKUPを使用して値を得る。



C9に「=VLOOKUP(A9,OFFSET(A1,1,1,5,5),2,0)」を入れている。

OFFSET(A1,1,1,5,5)でB1を基準として、55列(B2:F6、図の太枠部分)を参照範囲とする。

VLOOKUPでB2:F6の範囲からA9「英ポンド(円)(9日、21:46) 」を探し出し、2行目の147.66−147.75 を得る。


次にCOUNTA関数を使用して最後の行を得る。最後の行であるかを確かめるためにINDEX関数を使用する。



C10に「=INDEX(OFFSET(A1,1,1,COUNTA(B:B),5),COUNTA(B:B),1)」を入れている。

INDEX、COUNTAの使用方法はこちらを参照。

COUNTA(B:B)では値が5個あるので「5」と言う値を得る。

OFFSET(A1,1,1,COUNTA(B:B),5)で指定したB2:F6の中で、5行目1列目の値「豪ドル(円)(9日、21:46) 」を得る。

データが追加されても、COUNTAで値が6に増え、参照範囲がB2:F7となって、VLOOKUPで検索できるようになる。


最後に、VLOOKUP、OFFSET、COUNTAを総動員して、データが追加されてもVLOOKUPで検索できるようにする。



C11に「=VLOOKUP(A9,OFFSET(A1,1,1,COUNTA(B:B),5),2,0)」を入れている。

OFFSET(A1,1,1,COUNTA(B:B),5)により、データが増えても参照範囲が変わるようになった。

後はVLOOKUPの参照範囲にOFFSET(A1,1,1,COUNTA(B:B),5)をいれ、通常通りに使えば、データが追加されても、自動的にVLOOKUPの参照範囲が変わって検索可能となる。

データを追加するたびにVLOOKUPの参照をしなくても良くなる。


PS.

MATCH+INDEXの検索の場合は1列検索なので、INDEXをそのまま持ってこれば良いと思うので試してみる。

こちらで試してみたのでよろしく!!


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

実践!ExcelデータベースTOP