エクセルでデータを検索、引用して転記したい! (VLOOKUP 関数)

Blurb

エクセル (Excel) で、マスター ファイルにあるデータを、自動的に検索、引用して転記する方法を、画面写真と会話でご紹介します。

public/ja/PCTalk/Faces/a-b_co-worker-sad.gif

エクセル (Excel) で見積書を提出したんですけど、商品マスター ファイルから転記するときに、商品名と単価を間違えてしまったんです。それで怒られてしまって……。間違えないようにする、いい方法ありませんか?

public/ja/PCTalk/Faces/q-g_co-worker-panic.gif

商品マスター ファイルをちゃんと見たの?

public/ja/PCTalk/Faces/a-b_co-worker-panic.gif

ちゃんと商品マスター ファイルを見ながら、商品番号を探して、その行にある商品名と単価を入力しましたよ。でも、違う行のデータをコピーしたことに、気が付かなくて。

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

だったら、その作業をエクセルで自動化すればいいのよ。そうすれば商品番号を打つだけで、商品名と単価が自動的に入力されるようになるの。

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

それなら転記ミスがなくなりますね。その方法、教えてください。

public/ja/PCTalk/Faces/q-g_co-worker-wonder.gif

まずエクセルで 2 枚のシートを作って。1 枚目は見積書のシート、2 枚目は商品番号、商品名、単価をまとめた商品マスターのシート。

public/ja/PCTalk/Faces/a-b_co-worker-say.gif

2 枚目のシートに、商品マスターのデータをコピーしました。
商品マスターのシート

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

OK。次は見積書の書式を作って。ただし、商品名と単価はまだ入力しないでよ。その部分を自動的に引用して転記するようにするから。

public/ja/PCTalk/Faces/a-b_co-worker-standard.gif

できました。
見積書のシート

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

まず仕組みから説明するわ。基準になるのは 「商品番号」 の列よ。商品番号 を基準に検索して、商品マスターから商品名や単価を引用するんだからね。
2 つのシートで共通する基準になる列

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

見積書に入力した商品番号と一致する商品名や単価を、商品マスターからコピーしてくれるんですね。

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

そうよ。そのために使う関数が VLOOKUP 関数というわけ。

public/ja/PCTalk/Faces/a-b_co-worker-question.gif

VLOOKUP 関数?

public/ja/PCTalk/Faces/q-g_co-worker-standard.gif

商品マスターから転記するとき、今まで商品名と単価をどうやって探していたか、思い出してみて。

public/ja/PCTalk/Faces/a-b_co-worker-soursmile.gif

商品番号の列を探して、見つかったらその行にある商品名と単価をコピーして転記していました。

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

まさにそれを自動的にやってくれるのが、VLOOKUP 関数よ。別の表から条件にあった項目を検索して、その行にあるデータを引用して転記してくれるの。

まず商品名の引用からやってみましょう。VLOOKUP 関数を入れるのは、今は空欄になっている商品名のセル。そのC10 を選択した状態で、[Fx] のボタンをクリックして。
C10 の選択

public/ja/PCTalk/Faces/a-b_co-worker-standard.gif

はい。[関数の挿入] の画面が表示されました。
関数の挿入

public/ja/PCTalk/Faces/q-g_co-worker-standard.gif

次に [関数の分類] の欄で [検索/行列] を選ぶと、下に [VLOOKUP] が出てくるわ。それを選択して、[OK] をクリックすると、VLOOKUP 関数の設定画面が表示されるわよ。
VLOOKUP の選択

public/ja/PCTalk/Faces/a-b_co-worker-question.gif

VLOOKUP 関数の設定画面で何を入力すればいいんですか?
VLOOKUP の設定 1

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

ここで 4 つの項目を指定していくわ。
最初の項目、[検索値] は、基準になる列のこと。

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

商品番号ですね。

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

だから、[検索値] の欄をクリックしてから、その行にある商品番号のセル B10 をクリックして。
VLOOKUP の設定 2

public/ja/PCTalk/Faces/a-b_co-worker-standard.gif

[検索値] の欄に 「B10」 と入力されました。次は [範囲] ですね。

public/ja/PCTalk/Faces/q-g_co-worker-wonder.gif

ここには引用元の範囲を指定すればいいの。だから [範囲] の欄を選択してから、商品マスターのシートのタブをクリック。次に商品マスターのセルをドラッグで範囲選択して。
VLOOKUP の設定 3

[範囲] のところが 「商品マスター! A4:C16」 と表示されたでしょう。これは、「商品マスターのシートの A4 から C16 のセル」 を範囲として設定したという意味よ。

ここでのポイントは、基準になる列が一番左になるように選択すること。VLOOKUP 関数は、選択範囲の一番左の列を、基準となる列と認識する仕組みになっているから。

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

ここでもう 1 つやっておきたいのは、絶対参照という作業。「A4」 と 「C16」、それぞれの文字列をクリックしてから F4 キーを押して。すると 「$A$4:$C$16」 という表示になるわ。
VLOOKUP の設定 4

public/ja/PCTalk/Faces/a-b_co-worker-question.gif

ここでついた 「$」 は何ですか?

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

「$」 は、そこを絶対参照に設定したという意味よ。この商品マスターの範囲は 「A4 から C16」 でしょう。絶対参照に設定すると、その範囲がずれなくなるの。

public/ja/PCTalk/Faces/a-b_co-worker-question.gif

範囲がずれるって、どういうことですか?

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

今、C10 に設定している VLOOKUP 関数は、「A4 から C16」 を参照しているでしょう。ここで作った関数の数式を、1 つ下のセルのC11 にコピーするとどうなると思う? 絶対参照にしておかないと、参照範囲も 1 つ下にずれて、「A5 から C17」 に変わっちゃうの。それだと商品マスターじゃないところを参照してしまうじゃない。だからずれないように絶対参照に設定しておくの。

public/ja/PCTalk/Faces/a-b_co-worker-say.gif

わかりました。次は [列番号] ですね。

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

これは、選択範囲の何列目のデータを引用するかを指定するの。C10 のセルに引用するのは、商品名でしょ。
VLOOKUP の設定 5

public/ja/PCTalk/Faces/a-b_co-worker-exclamation.gif

商品名は、商品マスターの 2 列目ですね。

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

だから、[列番号] には、「2」 と入力して。
VLOOKUP の設定 5

public/ja/PCTalk/Faces/a-b_co-worker-question.gif

[検索方法] には何を入れればいいんですか?

public/ja/PCTalk/Faces/q-g_co-worker-standard.gif

基準になる列と一致する内容を引用して転記するときは、「FALSE」 と入力するの。
VLOOKUP の設定 6

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

はい。全部入力が終わったから [OK] を押しますよ。あ、商品名が自動的に入力されました。商品マスターのデータと合っています。ちゃんと転記されましたよ。
VLOOKUP の設定 7

public/ja/PCTalk/Faces/q-g_co-worker-explain.gif

だったら、セルの中の文字列を折り返して、セルの幅を揃えたら?

public/ja/PCTalk/Faces/q-g_co-worker-notice.gif

やり方はわかったわね。今度は単価の部分をやってみて。

public/ja/PCTalk/Faces/a-b_co-worker-say.gif

[検索値] と [範囲]、[検索方法] は、さっきと同じ設定ですね。違うのは [列番号]。「単価」 は 3 列目だから、「3」 にすればいいんだ。
VLOOKUP の設定 8

public/ja/PCTalk/Faces/q-g_co-worker-smile.gif

そうよ。ほら、「単価」 も自動的に引用されたでしょ。
VLOOKUP の設定 9

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

あとは下のセルにも、VLOOKUP 関数の数式をコピーすればいいんですね。

public/ja/PCTalk/Faces/q-g_co-worker-notice.gif

そのとおり。
VLOOKUP 関数の完成

VLOOKUP 関数を使えば 「商品番号」 を入力するだけで、それに一致するデータを商品マスターから自動的に引用されるようになるから、便利でしょ。ちなみに 「商品番号」 を書き換えたら、「商品名」 や 「単価」 も自動的に修正されるわ。

public/ja/PCTalk/Faces/a-b_co-worker-smile.gif

ありがとうございます。これで転記ミスをなくせます。

public/ja/PCTalk/Faces/q-g_co-worker-wonder.gif

VLOOKUP 関数は、社員名簿や取引先リストなどを扱うときも便利よ。なれたら、いろいろなことに応用できるわ。ぜひマスターしてね。
プロパティ

文書番号:2502926 - 最終更新日: 2016/09/29 - リビジョン: 1

Microsoft Excel 2010, Microsoft Office Excel 2007, Microsoft Office Excel 2003

フィードバック