会社でデータの処理をする時に、私はやたらとVLOOKUPを使います。
VLOOKUPって関数は何をしてくれるのかと言うと、表の中から目的の文字や数字を探してくれます。
そして、見つかったデータから右側にXX個目のセルの値を取り出してくれます。
サンプルを一つ紹介します。
例えば、二つの名簿ファイルがあって片方の名簿の人が全員もう一つの名簿に記載されているかどうかなど。
二つのファイルは作業しやすいように、同一ファイルのシート1と2に配置すると楽です。
下記のG3のセルに”=vl” と入力しただけでセルのすぐ下にエクセルが勝手にVLOOKUPを探して表示してくれます。
これが出てきたら青い部分をダブルクリックしましょう。
ダブルクリックすることで、VLOOKUP関数が選択されて先頭の”(”も自動で入力されます。さらにセルのすぐ下に入力すべき値の説明が表示されます。
この状態で「検索値(他の表の中から探したい値)」をマウスでクリックします。ここではセルD3の”高島”をクリックします。
すると親切なエクセルさんはクリックしただけで、”D3”と入力してくれます。
ここですかさず”,”(半角のコンマ)を入力します。
すると、コンマを入力することでセルの下の太字で濃く表示されていたところが”検索値”から”範囲”に移動して
範囲の入力状態になったことがわかります。
次に、Sheet1の表全体を選びます。
まずSheetを切り替えるためSheet1のタブをクリックします。
そして、表をドラッグして選択状態にします。今回の目的では、一列(ここではD列)だけ選べば良いのですが、とりあえずF列まで選択しておきます。
このときに探したい値がある列(ここではD列)が選択範囲の一番左にあるようにします。ここは重要です。VLOOKUPは選択範囲の一番左しか検索してくれません。
ここで慌てずに、F4キーを押します。そうすると、セルの入力欄の範囲指定に$マークが4つ付いたはずです。
F4キーを押すたびに$マークの付く場所と数が変わるのですが、ここは$マーク4つがついた状態にします。
この意味については後で説明します。
そしてまたここで、”,”(コンマ)を入力します。すると「列番号」の入力状態になります。
ここではマウスでセルを選ぶのではなく、コンマに続けて直接数字を”1”と入力します。
さらに続けて”,”を入力すると、この後に続けて入力すべき値として”TRUE”と”FASLE”を表示してくれますので、マウスで”FALSE”をダブルクリックします。”TRUE”を使うと、数値を探すときには一番近い値を探してくれるのですが氏名などの完全に一致するデータを検索する場合には”FALSE”を使います。
そして、忘れずに最後の”)”を忘れずに直接キーボードから入力して終了です。
ENTER(↵)キーを押してください。
すると、Sheet2が下記の状態になって関数を入力したセルに”高島”と入ります。
これが何を意味するかというと、Sheet1の選択範囲内の一番左の列に”高島”が見つかったので、その見つけたセルを含めて1つ右のセル(要するに同じセル)の値を表示したことになります。
今回の目的に対しては、Sheet1にも”高島”が存在することはわかりました。
さらにSheet2の全ての行に対して同じくVLOOKUP関数を入力します。ここはエクセルの得意なセルのコピーで一気にやってしまいます。セルG3の右下の小さな■(四角形)を一番下までドラッグします。
下記のような結果になります。
“蒲田”と”長田”の部分に”#N/A”が表示されています。これはSheet1には見つからなかったということです。
この例の場合は二つのSheetの一致がお題なので、Sheet2に対しても同じことをしないとSheet2にのみ記載されている人を抽出できません。ここでは省略します。
さて、VLOOKUP関数に必要な値を入力していたときに、F4キーで$マークを選択範囲に付けましたがこの意味について説明します。
エクセルのセルのコピーは便利で賢く、コピーとは言ってもセルの中に含まれるセルの番地はコピーするごとに自動的にずらしてコピーしてくれます。これは皆さんご存じだと思います。
ここで、Sheet1の選択範囲に$マークを4つ付けた理由ですが、$マークが付いたセルの番地はセルのコピーをしても自動的にセルをずらしたりされません、固定されたままです。
固定しておかないと、ドラッグしてコピーした時に14列目のセルでは、Sheet1の選択範囲が下記のようなことになってしまいます。
さらに大事な注意点です。
VLOOKUPは同じ値が2つ以上見つかると先に見つけた方を表示します。例えば名字だけで検索すると同じ名字の人が識別できません。同じ名字の人を確実に検索するには、フルネームのデータを用意するとか、メールアドレスなど同じものが存在しないデータを利用すれば心配ありません。
VLOOKUPは、色々応用ができるので使い方をマスターしておくと何かと便利です。
応用例についても今後追加したいと思います。
スポンサーリンク