EXCEL で VLOOKUP 関数を使ってみる
EXCEL であるデータと他のデータを比較して、一致するデータを探したい時、またその逆に一致しないデータを探したい時、VLOOKUP という関数を使うと簡単に一致・不一致データを探し出すことが出来ます。同じことは SQL を使えば可能ですが、データが EXCEL だった場合には VLOOKUP 関数を使った方が素早くデータの差異を確認することが出来ます。しかし EXCEL では扱えるデータ件数が限られており、また大量のデータを EXCEL で比較すると、EXCEL の動作がとてつもなく遅くなります。
大量のデータを素早く比較したい場合には SQL の方が圧倒的に素早くデータを比較することが可能です。SQL であれば JOIN、LEFT OUTER JOIN を使えば VLOOKUP 関数と同じことを実現可能です。
しかし少量のデータを比較する場合には EXCEL の VLOOKUP 関数を使って比較したほうが、データの一致・不一致を探すには非常に便利です。ここでは EXCEL の VLOOKUP 関数の簡単な例を実際に試しながら見ていきましょう。
比較するデータを準備する
VLOOKUP 関数を使うには、比較したい２つのデータが必要です。ここでは下記画像のようなデータを準備します。実際に EXCEL シートに２つのデータを入力してみましょう。
VLOOKUP 関数を使うことにより、「全部のくだもの」の中で「食べたい物」がどれなのかを知ることが出来るとともに、逆に「全部のくだもの」の中で、どの食べ物を食べたくないのかを知ることができます。つまり食べたいものと食べたくないものがどれなのか、VLOOKUP 関数で簡単に知ることができます。
VLOOKUP 関数を使ってデータを比較する
VLOOKUP 関数を使ってデータを比較する場合、データが一致するかどうかという結果を表示する必要があります。そのため、データが何も入っていない空白の列をクリックして選択します(下図参照)。D2 のセルを選択します。
次に VLOOKUP 関数を使うために、選択した D2 セルで「=VLOOKUP(」とキーボードで入力します。すると下図のように「VLOOKUP(検索値, 範囲, 列番号, [検索方法])」というヒントが表示されます。
VLOOKUP 関数は「VLOOKUP(検索値, 範囲, 列番号, [検索方法])」というヒントが教えてくれているように、 検索値, 範囲, 列番号, [検索方法] という４つの情報を必要とします。これら全ての情報を VLOOKUP 関数に教えてあげることによってデータの比較が可能となります。それぞれの情報を簡単にまとめると次のようになっています。
VLOOKUP(検索値, 範囲, 列番号, [検索方法])
検索値 : 一致するかどうかを調べたい値
範囲 : 検索値を探したいデータの範囲
列番号 : VLOOKUP 関数は比較した列とは異なる列の値を返せます。複雑なのでここでは常に "1" を指定
検索方法 : 値を完全一致で検索するか、近似一致 (SQL で言えば LIKE) で検索するかを指定 (TRUE=近似一致 or FALSE=完全一致)。通常は完全一致を指定します。
次に下記動画のように VLOOKUP 関数で必要となる情報を埋めていきます。各情報はカンマ区切り(,)で指定していきます。そして完成した関数は「=VLOOKUP(B2, G2:G4, 1,FALSE)」となりますが、「=VLOOKUP(B2, $G$2:$G$4, 1,FALSE)」として $ マークを付けます(赤字部分を変更)。$ マークは、必ず指定した場所のデータを対象とするという意味です。
もしくは上記動画のような操作をするのが大変な場合には、D2 セルに
=VLOOKUP(B2, $G$2:$G$4, 1,FALSE)
という値を貼り付けて下さい。
最終的に D2 セルの VLOOKUP 関数は下記画像の通り「=VLOOKUP(B2, $G$2:$G$4, 1,FALSE)」となります(下記画像の赤枠内)。
VLOOKUP 関数の結果、「全部のくだもの」と「食べたい物」の両方に「リンゴ」が存在しますので、D2 セルには「リンゴ」と表示されます。つまり VLOOKUP 関数によって、一致するデータが存在する場合には「検索した値 (ここではリンゴ)」が表示されるようになります。
ここで設定した VLOOKUP 関数の動きをおさらいしてみましょう。
1. B2 セルの「リンゴ」を検索したいデータとする。
2. 検索されるデータは G2・G3・G4 の範囲のデータ。
3. 検索したいデータ(B2 セル) の値が、検索されるデータ(G2・G3・G4 の範囲)に存在するか検索する。
4. 値を比較する条件は FALSE なので「完全一致」検索を行う。
換言すると、VLOOKUP 関数は左側の "全部のくだもの" の「リンゴ」という値を、右側の「リンゴ・イチゴ・スイカ」の中から検索し、一致したら D2 セルに一致した「リンゴ」という値を表示してくれます。
これで "全部のくだもの" の「リンゴ」が "食べたい物" の中に存在することがわかりました。次に "全部のくだもの" にある全ての値が "食べたい物" の中に存在するかどうか(一致するかどうか)を調べてみましょう。
そのためには D2 セルに設定した VLOOKUP 関数をコピーするだけです。下記画像のように設定してみましょう。
D2 セルをマウスでクリックし、セルの右下の部分を左クリックボタンを押しながら、D6 セルまでマウスをドラッグします。すると D2 セルに設定した VLOOKUP 関数がコピーされ、左側の "全部のくだもの" の値それぞれが右側の "食べたい物" に存在するかどうか(一致するかどうか)を検索してくれます。
つまり VLOOKUP 関数は次の矢印のようにデータを検索して一致するものの値を表示してくれます。
しかし逆に一致しなかったデータには "#N/A" と表示され、一致しなかったことを示してくれます。"#N/A" の意味ですが、"該当無し" (Not Applicable) という意味であり、データが一致しなかったことを示しています。
まとめ
ここまで見てきたように、VLOOKUP 関数を使うことによって、２つのデータ間で一致するデータと一致しないデータを簡単に調べることが出来ます。ただし冒頭でも述べたように、データ量が少ない場合には VLOOKUP 関数で素早くデータの一致・不一致を調べることができますが、データ量が１万件などのように大量になった場合には SQL で処理するほうがベターです。
