【引用して効率アップ】VLOOKUP関数の特徴を知りVLOOKUP関数マスターになろう

Excel

ブイルックしてますか?

エクセルで作業をしているときに、自分で入力したデータと他のデータテーブルを関連させて新たなデータを作成する場合があります。そんな時にはVLOOKUP関数を使っていると思います。

データテーブルとは、商品リストだったり顧客リストだったり社員リストだったり、地味で基礎的ではあるけれども重要なリストである場合が多いと思います。
このようなデータをマスタデータ(マスターデータ)と読んだりもします。
マスタデータは、更新していく必要がありますが一度作ると様々な用途で使いまわす事が出来ます。いわば「資産(ストック)」のようなものです。

このように考えると、VLOOKUP関数は既にある資産を活用して素早く新たな情報/価値を作っていくためのツールであり、使いこなせれば仕事の生産性は上がっていくものです。

 

ただし、VLOOKUP関数にはいくつかの特徴(欠点)があり、それらを知らずにVLOOKUP関数を使いこなす事は出来ません。

今回はVLOOKUP関数の使い方に加え、特徴とその対応方法についても紹介します。

 

スポンサーリンク

VLOOKUP関数の使い方

VLOOKUP関数は、「データテーブルから抽出したいデータを検索して参照する関数」です。
データテーブルを用意しておけば、その中から参照したいデータを自動で入手する事ができるため効率が良い上に転記ミスもありません。大量のデータを扱う場合には必須の関数です。

ある卸売業者の企画部門の人が「出荷データを元に取引先別の利益データを作成する事」を想定して、使い方を簡単に確認しておきます。

次のように、オレンジ枠のような商品コード別の出荷データをあるシステムからダウンロードしてきました。
この結果を元に取引先別に利益状況をまとめる場合には単価情報が必要となります。
単価と数量があれば、あとは掛け算で利益金額を算出することが出来ます。

 

VLOOKUP関数で参照するデータは、「商品マスタ」シートに保存したデータテーブルから引っ張る例とします。商品コードの属性として、セグメント/商品名/売価(単価)/原価(単価)が管理されています。
利益を算出するために商品名/売価(単価)/原価(単価)をVLOOKUP関数で参照します。
※複数の部門で運営されている会社であれば、このようなマスタデータを管理している部門があると思います。

 

それでは、関数を用いて必要なデータを実際に引っ張ってみます。

下の図のように、G3セルに「 =vlookup( 」※左括弧まで を入力すると、引数について補足のポップアップが表示されます。ポップアップは VLOOKUP(検索値,範囲,列番号,[検索方法]) となっています。このようにポップアップを参考にすれば、複雑な引数が必要な関数も使いやすくなります。
今回の例では、商品コードをキーとして商品名や単価を参照したいため、検索値には同じ行の商品コードを指定します。E3セルをクリックして選択した後にF4キーを3回押して列方向を固定する絶対参照にしておくと便利です。

 

次に、VLOOKUP(検索値,範囲,列番号,[検索方法])を指定します。下の図が範囲と列番号の関係性です。
範囲にはデータテーブルを指定します。今回の例では、「商品マスタ」シートのデータテーブル全体を選択しても良いのですが、データテーブルが含まれる範囲を選択しても機能するため、C列からG列をドラッグして選択します。その後F4キーを1回押して列方向を固定する絶対参照にしておくと便利です。
列番号検索値を探す対象列を1列目として、参照したい属性が何列目かを指定します。商品コードに対応した商品名なら列番号:3となり、商品コードに対応した原価(単価)なら列番号:5となります。

 

[検索方法]は「TRUE」か「FALSE」の2つが選べますが、基本的にはFALSEを入力しましょう。FALSEは、検索値と検索値を探す対象が完全に一致した場合のみ検索結果を参照する事を意味します。FLASEを選択しておけば、データテーブルに忠実に必要なデータを引っ張ることが出来るので、基本的にはFALSEが望ましいと思います。[検索方法]を入力したら括弧を閉じて数式入力は完了です。
VLOOKUP関数によって、G3セルにA001という商品コードに対応する商品名を表示することが出来ました。

 

同様に、売価と原価についてはG3セルの数式をコピーし、列数を変更すればデータテーブルから値を引っ張ってくる事が出来ます。売上と原価と利益は簡単な数式を入力します。
これで9/15にX社に対して、商品コードA001という商品で利益がどれだけ出たかが計算できました。

 

最後に、G3からL3セルのコピーして、数量データがある4行目から15行目にペーストします。
これでVLOOKUP関数を使って「出荷データを元に取引先別の利益データを作成する事」が出来ました。数量データやデータテーブルが膨大であっても、簡単に処理する事が出来ます。
※実際にはここからピボットテーブルを使って分析したりするわけですが、今回はVLOOKUP関数の紹介なのでここまでにしておきます。

スポンサーリンク

VLOOKUP関数の特徴とその対応方法

VLOOKUP関数は便利ですが、使いこなす上で覚えておくべきことがあります。

検索範囲の1番左の列でしか検索できない

VLOOKUP関数は、検索範囲の1番左の列でしか検索が出来ません。
検索範囲としては何等かのデータテーブルを指定することになりますが、データテーブルの左側に検索したい属性がレイアウトされていない場合があります。そのままではVLOOKUP関数を使うことが出来ません。

このようなケースには頻繁に遭遇しますので、対応例を紹介しておきます。

対応例

対応は簡単で、データテーブルの一番左に「検索キー」列を追加しましょう。検索キーは数式で商品コードと一致するようにします。
検索キーをVLOOKUP関数の検索範囲の一番左の列に指定すれば、「商品名」も参照可能になります。
※商品コードを一番左の列に移動させても良いのですが、既に商品コードを検索値として使用しているシートがあった場合には列数がずれてしまいます。複数人で更新しているファイルでは、データテーブルの形はなるべく変更せず、新しく追加していく方が良いです。

 

検索範囲の1番上にある検索値しか参照できない

VLOOKUP関数は、検索範囲の1番上にある検索値しか参照できません。
同じ商品コードでも取引先別に売価を変えているデータテーブルを例に説明します。このような場合に、商品コード「A001」を検索値としてVLOOKUP関数を使うと、3つ存在する「A001」のどのデータを参照するかというと、一番上にある「A001」です。
つまり、重複した値がある場合には、1番上の検索値に対応するデータしか参照することが出来ません。

Y社やZ社の売価を参照するための対応例を紹介します。

対応例

まず、データテーブルの一番左に「検索キー」列を追加しましょう。検索キーは数式で「=商品コード&取引先」として、重複の無い値を作成します。
検索キーをVLOOKUP関数の検索範囲の一番左の列に指定すれば、取引先別に設定されている「売価」を参照可能になります。

 

また、検索値としても「検索キー」列を追加しましょう。検索値を「商品コード」から「検索キー」に変更することで、取引先別に異なる売価を適切に参照できるようになります。

 

最後に

以上、VLOOKUP関数の使い方と、特徴とその対応方法について紹介しました。

VLOOKUP関数を使いこなせればデータ処理スピードが跳ね上がります。しかし、重複したデータの扱い方を知らないまま大量データを処理すると大惨事になるリスクを秘めた「諸刃の剣」のようなものです。

「彼を知り己を知れば百戦殆うからず(孫氏)」という言葉があります。
今回の記事によって、VLOOKUP関数の特徴を知り、VLOOKUP関数マスターになる手助けになれば幸いです。

コメント

タイトルとURLをコピーしました