VLOOKUP 関数を使って、情報の足りない表を加工することがあります。
たとえば今回の例では、左側の表に社員番号を入れたら該当する名前とメールアドレスが表示されるようにすることで、左側の表には社員番号だけを入れれば足りない情報が表示される、としたいと考えています。
右側にはデータを “探しに行く場所” となる表を作成しています。
今回は同じワークシートのなかに、データを ”探しに行く場所” となる表を作成しています。この表の特徴は、
- 1 列目に検索に使うキーワードである [社員番号] の列を配置 (もちろん重複なし)
- 1 列目は昇順ではない ([部署] で並べ替えてまとめてあるので社員番号の順序はバラバラ)
- 2 列目に [氏名]、4 列目に [メールアドレス] を配置
- テーブルではない標準のセル範囲
です。
数式に必要な材料 (引数) の指定について
VLOOKUP 関数の構文はこちら↓
=VLOOKUP (検索値 , 範囲 , 列番号 , [検索方法])
検索値 には、検索したい値、またはその値が格納されているセルを指定します。
検索したい値を固定の文字列で指定する場合は “1020” “石田” のようにダブル クォーテーションで括ります。
範囲 には、検索したい値やそれに関連するデータが含まれる表のセル範囲を指定します。
1 行目の見出しは通常は範囲に含めません (含めてもエラーにはならないけれど探す必要がないのでいれない)。
列番号 には、範囲の左端列を列番号「1」として列番号を数えて、返したい値が指定した範囲の左から何列目にあるのかを番号で指定します。
[検索の型] には、検索したいデータが表の左端列で見つからなかった場合にどうするかを指定します。
検索値と完全に一致するデータだけを検索し、検索結果が見当たらない場合はエラーを表示する場合は FALSE を指定します。TRUE を指定、もしくは入力を省略した場合は、そのデータを超えない最大値が検索されます。このように動かしたいときには範囲の左端列のデータは昇順に並べ替えておく必要があります。
数式の作成
今回の例では、最終的に D 列の [氏名] に複数の名前が返ってくるようにしたいのですが、まずは先頭のセル D5 に数式を作成してコピーします。
セル C5 の値を検索値として使用し、J5 から N24 を範囲として指定し、返してほしい値の格納されている列として左から 2 列目 ([名前] 列)、完全一致 (FALSE) の検索方法を指定します。
=VLOOKUP (C5 , J5：N24 , 2 , FALSE)
① 結果を表示するセルに数式を作成します。
セルやセル範囲は、入力してもよいしクリックやドラッグで選択してもよいです。
3 つ目の列番号を指定したあと、「，」を指定すると検索方法の候補が表示されます。[↓] で移動して [Tab] キーで候補から選択できます。
② [）] を入力して [Enter] キーで確定します。
参照するセル範囲の固定
ここまでの手順で数式を作成したセルを下方向へコピーすると、相対参照で指定しているセル範囲にずれが生じるため、求めている処理結果になりません。
コピーしたセルの数式を確認すると、検索値のセルはずれることで正しく同じ行の C 列を参照できていますが、範囲は下方向にずれることで適切な範囲を参照できていないという状態になっています。
① 最初に作成した数式の範囲の部分を絶対参照に修正します。
相対参照から絶対参照への変更は、「＄」を手入力してもよいですが、選択して [F4] キーを押して「＄」を追加して絶対参照にすることも可能です。
② 絶対参照に修正した数式のセルを再度コピーしなおします。
③ コピーしたセルの数式を確認すると、範囲も相対参照によるずれがなくなり、適切な範囲を参照できている状態になっています。
あとでセルをコピーすることを考えて、相対参照のままではずれてしまうということを事前に理解できているのなら、最初に数式を作成する段階で絶対参照にしておけばよいでしょう。
実務のデータは大量であり、列数も多くてぱっとみでは正しく動いているように見えるけれど、実際は・・・ということになりかねません。セルのコピーを行ったら、必ず最後のセルを [F2] キーなどで編集モードにして適切なセルやセル範囲を参照できているかを確認することをおすすめします。
メールアドレスは表の左から 5 列目です。
前の手順で作成した数式の列番号のところだけ異なる数式で対応できますが、セルをコピーすると検索値のセルの参照がずれるので、コピーをしたいのなら中身 (数式) だけコピーして再利用するか、セルのコピーをした場合は参照を修正してください。
もちろん、参照する範囲をテーブルに変換している場合はテーブル名で指定できるため、絶対参照に変更する必要はありません。
エラー対策
VLOOKUP 関数の数式で、探したけれど該当する値がない、というときに #N/A エラーが返ります。
検索値が格納されるべきセルが空白のときにも同じことがいえます。
検索値のセルが空白のときは (処理してもエラーになるのだから) VLOOKUP での処理をせずに、ここ (数式のセル) も空白にしてください、という処理をしたいのなら IF 関数で対応できます。
検索値のセルが空白ではない ＝ なにかキーワードとなる値が入っている というときは VLOOKUP による処理をしてください、という数式です。
エラーの原因が検索値のセルが空白だからかどうかによらず、VLOOKUP での処理結果がエラーの場合はすべて、空白にしてください、という処理をしたいのなら IFERROR 関数で対応できます。
この場合は、該当する社員番号 (検索値) がない、というときにも空白が返ります。
検索方法の FALSE と TRUE の指定による違いについては、こちら もご覧ください。
VLOOKUP 関数を使って複数の表の必要なデータを 1 か所にまとめられる、といった加工は非常によく行われます。
参照する範囲がセルなのかテーブルなのか、セルの場合はどうやったらコピーをしたときにずれが生じないのかなど、Excel の基礎に関わる部分も併せて理解しておくべきでしょう。
石田 かのこ
