ExcelのVLOOKUP関数をしっかり理解しないと悲惨な事になるぞ

Excelはビジネスでは必須のツールである。そしてVLOOKUP関数はその中でも最頻出の関数の一つと言っていい。
今まで仕事をしてきたなかでVLOOKUP関数がまともに使えないために痛い目にあっている人を何人も見てきた。
これだけ抑えておけばExcelのVLOOKUP関数はOKだろうという内容を書いておきたい。

• ExcelのVLOOKUP関数を使用するときに注意すべきポイントが分かる。
• ExcelのVLOOKUP間数を使うときに合せて使うべき関数が分かる。

システムエンジニアとしてExcelを使って22年の私がVLOOKUP関数ができないために
散っていった沢山の同士達の思いを皆さんに受け取ってほしい。

目次【本記事の内容】

• 1.ExcelのVLOOKUP関数を使用するときに注意すべきポイント
• 　1-1.VLOOKUPのキーにする値のデータ型の確認を怠ることなかれ
• 　1-2.第4引数のFalse、Trueの意味の確認を怠ることなかれ
• 　1-3.データ配置するシートと式を書くシートは分けるべし
• 2.ExcelのVLOOKUP関数を使うときに合せて使うべき関数
• 　2-1.列だけでは主キーにならない場合、文字列結合で主キーを作るべし
• 　2-2.客向けに出す用のIFERROR関数
• 　2-3.INDEX関数、MATCH関数で検索キーが一番左に無い場合の対応
• 3.まとめ

１．ExcelのVLOOKUP関数を使用するときに注意すべきポイント

１－１．VLOOKUPのキーにする値のデータ型の確認を怠ることなかれ

例を出そう。2018年の売上データがある。2019年の売上目標を立てるときに2018年の実績を取得したいケースだ。
実務でもよくあるパターンだ。

東京支店の支店コードだけ左によっていて警告の緑縁が出ている。

この時点で気づいてほしいがデータが数万件あると無理だ。

 

予定通り東京の2018年売上実績データの取得に失敗している。
私だったら自分で取得したデータでないとデータ型に不安があるので

一度すべて支店コードを数値型に揃える作業をする。

たまに書式設定で変更しようとする人がいるが、最初に決まったデータ型は書式では直らないため
関数で変換して上げる必要がある。

文字を数値に変換する指揮はValue関数だ。
D列似できた数値の値をコピーして、A列に値貼り付けをする。

 

そうすると2019年売上目標シートにも東京支店の結果が反映されるようになった。

 

１－２．第4引数のFalse、Trueの意味の確認を怠ることなかれ

以前、後輩がVLOOKUPの式で悩んでいた。想定していない値まで一致してしまうとのことだ。
よくよく見てみると以下のような式になっていた。

システムエンジニアを22年やっているが、VLOOKUP関数の第4引数をTrueで使った事は一度もない。近似値を求めるときに
使うそうなのだが、そんな事は覚えておかず、False一択だと覚えておけば良い。

１－３．データ配置するシートと式を書くシートは分けるべし

データを貼り付けるシートは分けて余計なものは入れるな

たまに以下のように一つのシートに上下に表を配置している人がいる。
もう馬かと、鹿かとコンコンと問い詰めたくなる。

このビッグデータ時代にデータがいつ増えるかもわからないのでデータを貼り付けるシートと
VLOOKUP式を埋め込むシートは分けて置くことを激しくおすすめする。

データは範囲指定ではなくて列ごとガッツリ指定する

このビックデー（以下略）

検索対象のレンジを指定するときに現時点でデータが入っているアドレスしか指定しない人がいる。
データが増えたら式を直さないといけないのが非常に面倒だし、作業ミスのもとになる。
最初から

=VLOOKUP(A5,'2018年売上実績'!A:C,3,FALSE)

と入れておけばデータが増えても式の修正は必要ない。実はレンジから漏れていた事に気づかないというミスもなくなる。

 

 

２．ExcelのVLOOKUP関数を使うときに合せて使うべき関数

２－１．列だけでは主キーにならない場合、文字列結合で主キーを作るべし

下のようなマスタがある。得意先と商品によって販売価格が違うというケースだ。
VLOOKUPだとキーを一つしか指定できないため、得意先コードと商品コードで検索する事ができない。

 

こういうときは得意先コードと商品コードを&（アンパサンド）でつなげてキーになる列を左端に追加する。

 

この追加したA列に対して検索をかければ得意先コードと商品コードで一意の行を特定することが出来る。
VLOOKUPの式を埋め込む側にも検索キーを作成しておく必要がある。

 

２－２．客向けに出す用のIFERROR関数

VLOOKUPの式で該当する行が見つからないとN/Aエラーになる。
私は一向にかまわないのだが、このエラーが出ているとだらしないからきれいにしろと指摘してくる人がいる。
そしたら私はフォントを白くして背景と同化させてしのいでいる。効率よく働かなければならない。

でも必要に迫られて、1つ目の表になかったら2つ目の表を見る必要が出てくる。
先程の得意先コード、商品コード毎の単価マスタに登場してもらおう。

もし、この単価マスタに登録が無い場合は下の単価マスタを参照して売上金額を算出するという
計算ロジックが追加になったとする。その時はIFERROR関数の出番だ。

 

以下の式を解説すると、もし単価マスタに該当する得意先コード&商品コードが登録されていなかったら
定価マスタを商品コードで検索するということをしている。

 

２－３．INDEX関数、MATCH関数で検索キーが一番左に無い場合の対応

2-1 でつかった複合キーによるVLOOKUPできない場合がある。列を挿入してしまうとマクロが動かなくなる場合などだ。
VLOOKUPの弱点は検索キーが表の一番左にないといけないということである。

と思った人は鋭いですね。そういう事は先人の偉い人が考えてくれている。

INDEX関数とMATCH関数を組み合わせれば出来る。

と思った人は鋭いですね。そういう噂もあります。

下記の表を見てください。検索キーが表の右側にある。文字列として定義されている。

 

この表を検索するにはINDEX関数とMATCH関数を組み合わせれば可能です。式を詳細に見ていきましょう。

最初のINDEX関数は選択したレンジから、行番号と列番号を指定してその値を取得する関数になっている。
どこの行を取得するかはMATCH関数の検索結果から受け取る。

MATCHの検索キーはE4="01"　で検索に行く。検索対象は前年度売上実績のD列。"01"で検索してるから4行目となり、4という数字が取得できる。
それでINDEX関数はC列からD列の範囲で4行目の1列目を戻してくれる。

VLOOKUP関数いるのかな 😥