2020年02月02日

VLOOKUPのズレを防止する / 関数 / Excel

Excelの “VLOOKUP関数” で検索する時の検索場所の指定は検索範囲の中の左からの列番号で指定します。
そこで、検索場所の列の左側に列を挿入してしまうと本来検索する列が右側にズレるため間違った列を検索してしまうことになってしまいます。
そこで、そのズレを防止するために、検索する列番号を“MATCH関数”を使って求めてやります。
20200119-00.jpg

“COLUMN関数”でも同じような効果があるのですが、“MATCH関数”の方がより間違いなく検索できます。

こんな様な3列の表があります。その右側でVLOOKUP関数で得意先コードから都道府県を検索しています。
20200202-01.JPG


@は都道府県の列番号を“3”としたVLOOKUP関数です。
20200202-03.JPG

Aが“COLUMN関数”で見出しの“都道府県”のセル“D3”の列番号を求めてそれを列番号にしています。ただここではD3の列番号は“4”になるため“-1”して“3”にしています。
20200202-04.JPG

Bが“MATCH関数”で表の見出しの範囲(B3:D3)から“都道府県”の列番号を求めています。
20200202-05.JPG

いずれも、得意先コード“2”に対し“福岡県”が正しく検索されています。


では、意地悪テストです。
得意先コードの右側に列を挿入します。
すると都道府県の列は右にズレて表の中で4列目に変わります。
すると@の検索列を“3”と指定したVLOOKUP関数では“小料理なんごく”が表示されてしまいました。
20200202-06.JPG

@では列の挿入によりVLOOKUP関数の検索範囲は$B$3:$E$23に拡大されるため都道府県の検索列は左から“4”番目にならなければいけません。
20200202-08.JPG

Aの“COLUMN関数”では列の挿入により検索セルも相対的に右にズレて“E3”を検索するようになったため、“E3”の列番号“5”“-1”で4になって都道府県が表示されています。
20200202-09.JPG

Bの“MATCH関数”は列の挿入により検索範囲がB3:E3に拡大し“都道府県”と言う文字を検索し“4”を返しVLOOKUP関数は都道府県から検索しています。
20200202-10.JPG

次は、表の中ではなく外側(右側)に列が送入された場合です。
ここで、都道府県が正しく表示されないのはAの“COLUMN関数”で指定した場合です。
20200202-11.JPG

@では“VLOOUP”の検索範囲が表の右側の列の挿入により“$C$3:$E$23”に右に移動するため“都道府県”の列は表の左から3番目であることに変わりはありません。ので正しく“福岡県”と表示されました。
20200202-13.JPG

では問題の“COLUMN関数”を使用したAの場合です。
列の挿入により検索するセルは“E3”になり、右にズレました。ので、“5”“-1”で“4”になり、VLOOKUP関数の検索範囲の外を指定してしまっています。
20200202-14.JPG

“MATCH関数”を使用したBの場合は列の挿入により検索範囲が“C3:E3”と右に移動しているだけなので“都道府県”の検索結果は“3”になり正しく“福岡県”が表示されました。
20200202-15.JPG

と言うことで、VLOOKUP関数を使って検索したいのは絶対的な列番号ではなく表の中の“都道府県”の列番号であるわけなので、表の中の“都道府県”の列番号を返してくれる“MATCH関数”を使うのが妥当なのは当たり前と言うことになる。。。



関連ページ
VLOOKUPで列方向の検索をして表示させてみた
COLUMN(COLUMNS)関数で列番号を求めてみた〜





posted by haku1569 at 22:11| Excel関数応用 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。