Excelでvlookupを使ってデータを参照する!2つの落とし穴に注意(^^)/

Image

Excelは発売以来たぶんずっと使っていて、これなしでは仕事ができないという感じで使ってます。vba簡単な関数を使って色々やってるつもりだったのですが、VLOOKUPという関数を今まで使ったことがありませんでした(^_^;。今日初めて使って便利だな~と思ったり、落とし穴に落ちたりしたのでそのことについて書きます。

ExcelのVLOOKUPとは何か?

今日はずっとデータの加工をしていました。データベースから抽出したExcelデータを使ってたのですが、プログラムなら簡単にマスタからデータを参照してカラムに入れる、ということができるのですがそういえばExcelの関数でそういうのが簡単にできるのがないかなー(vba書くのは面倒だな)と思って調べたところ VLOOKUP という関数を見つけました。

VLOOKUP

簡単に言うとマスタみたいなデータから名前とかidとかを元にidとか名前を持ってきてくれる関数なのだと思います。

お客様idからお客様の名前をセルに入れてみる

例として、注文を受けたときにお客様のidしか分からなかったとします(そんなこと、あるかな~)

Image

こういうExcelになってる時にお名前を、

Image

住所録 というシートからコピーしてペーストするのはちょっと面倒です。そこでVLOOKUPを使います。

名前を入れたいセルをダブルクリックして 

と入力します。

Image

最初の値は 何を元にして検索するか を入れるので、

Image

この場合は同じ行のお客様idをクリックします。このような式が入ります。B2の後に半角のカンマ , を入れます。

次に、どこを参照するか を指定するので、

Image

住所録シートのidと名前をクリック&ドラッグで選択します。

Image

元のシートに戻るとこのように表示されています。ここが1つめの落とし穴だったのですが、このように普通にやってると 住所録 のシートを参照したいのに

のようにシート名が入れ替わってしまいます。これを防止するには、住所録シートのidと名前をクリック&ドラッグで選択した状態で リターンキー を押して下さい。

Image

このように注意メッセージが出ますが、ちゃんとシート名は 住所録 となります。あるいは単にこの 注文 というシート名を 住所録 に書き換えちゃっても大丈夫です。

あと一手間いるのですが、この A2:B31

Image

$A$2:$B$31 と書き換えます。このようにしておくと後ほど式を引っ張ったときでもこの値は変わらないで済むからです。

次に何を持って来たいのかという列の番号を指定します。列番号はAが1でBが2と順番になっています。住所録シートの名前を持ってきたいので列番号は2になります。なので、

Image

区切りのカンマ と 2 を入れます。そして、完全一致にしたいので、

Image

カンマ false と入れて最後に半角のカッコを加えます。

Image

リターンを押すとこのようにidに対応する名前が入りました(^^)/

Image

このセルの右下にある ■ をクリックして、

Image

下にドラッグするとこのようにそれぞれの行のお客様の名前が入りました。

現実的には、名前から住所とか?

現実的には名前から住所、かなんかですよね。先ほどと同じような感じでできるのですが、注意する点は 参照先(この場合住所録)で一致させる項目(今回の場合は名前)は一番左の列にある前提ということです。

Image

このように名前しかない注文から住所を得るには、

Image

名前が一番左にある、こういう住所録からとなります。これで、先ほどやったように住所録の

Image

この辺を参照して、式を

Image

このように書くて、引っ張ると

Image

名前に対応する住所が入りました(^^)/

2つめのの落とし穴。完全一致の指定

既に落とし穴に落ちていたので、先ほどはちゃんとやったのですが、

Image

この最後のパラメータ [検索方法] は必須ではないのです。なので、最初これを指定しないで式を書いていました。今回のこの場合もこの指定を取ってみます。

Image

こんな感じ、これで見てみると

Image

このような結果になります。住所を見ると全く同じ住所は無いはずなのでいくつかダブって入っている住所があります。これが2つめの落とし穴で最後に 完全一致 を意味する false を入れないかもしくは true を指定したときは 「近似値」 で一致するので、このような結果になります。

ということで2つの落とし穴に注意してVLOOKUPを便利に使っていきたいと思います(^^)/

兎本美佳

ブログを見た人がそのままできたらいいなと思って、できるだけ丁寧に書いています。blogに書いたようなネタの有償対応のご相談は「ゆうそうと」へいただければと思います(^^)/
無償での対応をご希望の場合は、コメントをいただけましたら可能な場合はコメントを返させていただきます。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

スポンサーリンク
スポンサー広告
ブログをメールで購読

メールアドレスを記入して購読すれば、更新をメールで受信できます。

261人の購読者に加わりましょう

トップへ戻る