【エクセル】Vlookup関数の使い方を教えます

ecoslymeと申します。

会社で営業の売上データを集計・加工している時に、システムからデータを出力すると、商品コード商品名は書いてあるけど、単価が記載されていなかったり、欲しい必要なデータが足りない

もしくは、今見ているデータだと社員コード社員名は書かれているけれども、肝心の部署名が書いていない。それも複数名に対して連絡しなくてはいけないけど、一つ一つ社員コードを台帳から探して見つけるのが面倒

SAPなどを導入していて、利益センタ原価センタは表示されているけれども、事業部名部署名が分からないため集計できない。

そういった経験はありませんか?

 

そんな時に便利なのが、この「Vlookup関数」です。

例えば、社員コードや社員名、事業部名などが記載された○○マスタと呼ばれる台帳は普通どこかに存在します。そのマスタ(エクセル)を利用して、売上データなどと組み合わせて欲しい情報を瞬時に取ってくることができます。

 

元データと結果

 

Vlookup関数の使い方

(Sheet1)売上データ、(Sheet2)商品マスタ

上記のように売上データ(Sheet1)と商品マスタ(Sheet2)があるとします。

売上データでは単価とメーカー名がなく、この単価とメーカーのデータが欲しいのですが、利用しているシステムからはそのデータが吐き出せませんでした。

かたや手元に持っている商品マスタには、その欲しいデータが登録されています。

この2のデータを紐づけるために、Vlookup関数を使用します。

 

Vlookup関数の入力

E2セルへカーソルを移動してから、以下の数式を入力します。

「 =VLOOKUP(B2,Sheet2!$A$1:$D$11,3,FALSE) 」

(ポイント)

・関数の中で、4つのブロックに分かれています。

マウスを使ってセルや範囲を選択していきますがその場合は、ブロックの間に「 」を入力し忘れないようにしましょう。

ブロック名入力内容意味説明
検索値B2共通する項目どの項目をキーにして情報を持ってきたいかを選択します。
範囲Sheet2!$A$1:$D$11商品マスタの範囲$“で挟むのがポイント。これによりコピペしても数式の参照範囲が固定されて動きません。

「A1:D11」を選択して「F4」ボタン1回押せばOKです。

列番号3商品マスタの商品コードを1列目として、何列目のデータが欲しいか3列目(C列)に単価の情報があります。
検索方法FALSE完全一致TRUEもありますが、基本的には「FALSE」しか使わなくてOKです。

・商品マスタにも「商品コード」以外に、「商品名」という共通の項目がありますが、この場合は「商品コード」を利用します。商品名の場合、「スティックガム」が「ステイツクガム」などと登録されている可能性があるためです。

・商品マスタでは、一番左の列に商品コードがなくてはいけません。一番左にない場合は、一番左に来るように列を移動するか、一番左に列を挿入してから「=商品コードがある列」として無理やり一番左に持ってくるようにしましょう。

 

(よくあるミス)

・商品コードが全て数値の場合、「文字列」になっている可能性があります。Vlookup関数が反応しない可能性があり、「#N/A」と表示されてしまいます。その場合、文字列のセルを選択すると「」が出てきますので、「!」をクリックして「数値に変換する」をクリックすれば解消されます。

・「範囲」で”$“を付け忘れて、下へコピペしていくと参照される範囲も一緒にズレてしまうため、データはあるのに「#N/A」と表示されてしまいます。

 

結果1

上記の操作が終わり、数式を下にコピぺすれば欲しい単価が表示されます。

 

結果2

同様にメーカーの列についても関数を入力してみましょう。

E2セルを選択してから、以下の数式を入力します。

 =VLOOKUP(B2,Sheet2!$A$1:$D$11,4,FALSE) 

列番号は、”4“となることに注意してください。商品マスタではメーカーの情報は4列目(D列)にあります。

 

最終結果

同様に下へ数式をコピぺすれば、ようやく欲しいデータが完成です。

 

最後に

いかがでしたでしょうか。

数式のブロックが4つあるため、難しいと感じたかもしれません。ですが、慣れれば非常に使い勝手の良い関数となります。

営業でも管理部でも絶対に習得して欲しい関数となります。

これを機会に、ぜひデータ加工の効率化を計って頂ければと思います。