2019年10月27日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.まとめ1.ExcelのVLOOKUP関数を使用するときに注意すべきポイント1-1.VLOOKUPのキーにする値のデータ型の確認を怠ることなかれ例を出そう。2018年の売上データがある。2019年の売上目標を立てるときに2018年の実績を取得したいケースだ。実務でもよくあるパターンだ。東京支店の支店コードだけ左によっていて警告の緑縁が出ている。この時点で気づいてほしいがデータが数万件あると無理だ。予定通り東京の2018年売上実績データの取得に失敗している。私だったら自分で取得したデータでないとデータ型に不安があるので一度すべて支店コードを数値型に揃える作業をする。書式設定で変更しようとする人がいるが、最初に決まったデータ型は書式では直らないため関数で変換して上げる必要がある。文字を数値に変換する指揮はValue関数だ。D列似できた数値の値をコピーして、A列に値貼り付けをする。すると2019年売上目標シートにも東京支店の結果が反映されるようになった。ちなみに検索キーに文字が入っているので数値から文字に変換しないといけないという場合がある。いう場合はTEXT関数で数字を文字に変換してやる。前に0を何個つけるか、全体の桁数を見て合わせる必要がある。1-2.第4引数のFalse、Trueの意味の確認を怠ることなかれ以前、後輩がVLOOKUPの式で悩んでいた。想定していない値まで一致してしまうとのことだ。見てみると以下のような式になっていた。=VLOOKUP(A4,'2018年売上実績'!A:C,3,True)システムエンジニアを22年やっているが、VLOOKUP関数の第4引数をTrueで使った事は一度もない。近似値を求めるときに使うそうなのだが、そんな事は覚えておかず、False一択だと覚えておけば良い。1-3.データ配置するシートと式を書くシートは分けるべしデータを貼り付けるシートは分けて余計なものは入れるなたまに以下のように一つのシートに上下に表を配置している人がいる。馬かと、鹿かとコンコンと問い詰めたくなる。このビッグデータ時代にデータがいつ増えるかもわからないのでデータを貼り付けるシートとVLOOKUP式を埋め込むシートは分けて置くことを激しくおすすめする。データは範囲指定ではなくて列ごとガッツリ指定するこのビックデー(以下略)検索対象のレンジを指定するときに現時点でデータが入っているアドレスしか指定しない人がいる。データが増えたら式を直さないといけないのが非常に面倒だし、作業ミスのもとになる。最初から=VLOOKUP(A5,'2018年売上実績'!A:C,3,FALSE)と入れておけばデータが増えても式の修正は必要ない。実はレンジから漏れていた事に気づかないというミスもなくなる。2.ExcelのVLOOKUP関数を使うときに合せて使うべき関数2-1.列だけでは主キーにならない場合、文字列結合で主キーを作るべし下のようなマスタがある。得意先と商品によって販売価格が違うというケースだ。VLOOKUPだとキーを一つしか指定できないため、得意先コードと商品コードで検索する事ができない。いうときは得意先コードと商品コードを&(アンパサンド)でつなげてキーになる列を左端に追加する。この追加したA列に対して検索をかければ得意先コードと商品コードで一意の行を特定することが出来る。VLOOKUPの式を埋め込む側にも検索キーを作成しておく必要がある。2-2.客向けに出す用のIFERROR関数VLOOKUPの式で該当する行が見つからないとN/Aエラーになる。私は一向にかまわないのだが、このエラーが出ているとだらしないからきれいにしろと指摘してくる人がいる。したら私はフォントを白くして背景と同化させてしのいでいる。効率よく働かなければならない。必要に迫られて、1つ目の表になかったら2つ目の表を見る必要が出てくる。先程の得意先コード、商品コード毎の単価マスタに登場してもらおう。この単価マスタに登録が無い場合は下の単価マスタを参照して売上金額を算出するという計算ロジックが追加になったとする。その時はIFERROR関数の出番だ。定価マスタ以下の式を解説すると、もし単価マスタに該当する得意先コード&商品コードが登録されていなかったら定価マスタを商品コードで検索するということをしている。つかった複合キーによるVLOOKUPできない場合がある。列を挿入してしまうとマクロが動かなくなる場合などだ。VLOOKUPの弱点は検索キーが表の一番左にないといけないということである。検索キーが表の右の方にあったらどうするの?と思った人は鋭いですね。いう事は先人の偉い人が考えてくれている。INDEX関数とMATCH関数を組み合わせれば出来る。生徒これがあるならVLOOKUP関数イラねえんじゃね?と思った人は鋭いですね。下記の表を見てください。検索キーが表の右側にある。文字列として定義されている。この表を検索するにはINDEX関数とMATCH関数を組み合わせれば可能です。式を詳細に見ていきましょう。=INDEX('2018年売上実績'!C:D,MATCH(E4,'2018年売上実績'!D:D,0),1)最初のINDEX関数は選択したレンジから、行番号と列番号を指定してその値を取得する関数になっている。どこの行を取得するかはMATCH関数の検索結果から受け取る。検索対象は前年度売上実績のD列。"01"で検索してるから4行目となり、4という数字が取得できる。それでINDEX関数はC列からD列の範囲で4行目の1列目を戻してくれる。時に「もう質問無いですか?」って言わせない。出たけど動かん2019/11/28システム設計能力を個人でどうやったら効率的に上げられるのか?2019/11/26ビジネス中学システムエンジニアの仕事をして22年経ちました。PMP、PMSYoutubeご遠慮無くチャネル登録してください。