vlookupサムネ

Excel

【Excel関数】応用の前にまずは基本!vlookupの使い方

vlookup関数は、別シートや別表のデータを参照できる関数です。でも、設定する値が4つもあるのでエラーが発生しやすい関数でもあります。が、vlookup関数を使いこなせるようになると、データ分析のスキルが飛躍的にアップします!基本中の基本から、エラーが出にくい設定手順を紹介します。

vlookup関数のエラー解決の方法を確認する場合はこちら
vlookupがエラーで使えない!全7ケースの解決方法

vlookup関数の応用編はこちら
vlookup関数の応用編

1.vlookupの基本的な使い方

早速、具体例を。次の場合C5=社員番号015一致するの、シート「データ」7列目の値71をD5に表示します。

=vlookup(C5,データ!A:H,7,FALE)

 1-1.関数の使い方

vlookup関数の中身はvlookup(①,②,③,④)と全部で4つ。

①:参照したい箇所を指定します。なんでもOK
②:範囲を指定。この範囲の一番左の列が、①と同じ項目であること。
③:②の範囲内で何列目を表示するか。
④:わからなければ一旦  FALSE

 1-2.Microsoft的表現

(1-1を小難しく表現してみただけです。システマチックに理解したい方以外は、読み飛ばして1-3.解説に進んでください。)

vlookup関数の引数(ひきすう)は順に、検索値,範囲,列番号,[検索方法]と全部で4つ。

検索値  :文字列、数値などデータ形式を問いません
範囲   :検索値を最左列として範囲を設定
列番号  :範囲内で任意の列番号を設定
[検索方法]:TRUE-近似一致,FALSE-完全一致
※なお[検索方法]は任意の引数であり、省略した場合はTRUE-近似一致とみなされます。

 1-3.解説

vlookup関数は、①の値を、②で指定した範囲の最左の列から探し出し、③で指定した列番号の値を表示します。

④の設定は、①と同じ値だけOKとするか(=FALSE-完全一致)、①と同じ値がない場合は近くの値でもよしとするか(TRUE-近似一致)とするかを指定します。ほとんどの場合は、①と同じ値を参照しますので、バ○の一つ覚えのようにFALSEとしておけばよいでしょう。

私はデータ分析を10年やっている中で、④にTRUEを設定するシーンには一度も遭遇したことがありません。考えられるシーンとしては、緯度経度のような位置情報など、おおよそ一致するだけでも価値あるデータベースを参照する場合に使われるでしょう。

 1-4.留意事項

(1)①は直接値を入力してもOK

②~④は省略しますが、例えば、=vlookup("015",②,③,④)とこんな記載でもよいです。

(2)①は関数を入力するセルの左でもOK

vlookup関数をC列に、①をD列にしてもOK。勘違いしやすいのは、②範囲の最左の列が①と同じ項目でないといけないってところ。
vlookup1-3

(3)④を省略すると勝手にTRUE

④にTRUEを設定する場合は、よっぽどの目的がある場合でしょう。意識的に④にTRUEを設定している場合は問題ないですが、④を省略すると自動的にTRUEと認識され、自動的に近似一致で処理されます。冒頭の例で使用したデータに、次の2つの修正を加えてみると、

  • ④を省略
  • ②範囲から①指定セルの値"015"を削除

vlookup関数の結果として表示されているのは168となっており、これは"014"の7列目です。①で指定したのは"015"でも、結果は"014"のものが表示されました。

vlookup1-2

 

2.エラーになりにくい設定手順

 2-1. =vl まで入力して[tab]キーで候補選択

パソコンに慣れてくると、関数もタイピングした方が断然早い!数式バーの左にある[関数の挿入]はツカワずに、=vl と3文字タイピングしてみてください。すると、関数の候補が登場しますので(以下のイメージの通り)、vlookupが登場したら[Tab]キーを押すと候補の関数を選択することができます。vlookup1-4

エラー解決の記事でも紹介していますが、vlookupの入力ミスでエラーとなることもあるのです(ネタではなくて、本当にあるのです)。入力ミスを回避するためにも、関数候補の中から選択することをおすすめします。

 2-2.②[範囲]は列単位で指定

列単位で指定するとは、②範囲をA:Hと設定すること。行列単位での指定は A1:H31と数字(行)も入った状態のこと。列単位で指定すると2つのメリットがあります。

vlookup1-5

【メリット1】 データレコード(行)が増えても大丈夫

vlookupを使用するシーンでは、範囲に設定するデータ更新し(張替え)、データレコード(行)が増えることもしばしば。行列指定の場合は、データレコード増えた場合、範囲が足りなくなる可能性があります。が、列指定の場合は、データレコードが増えても大丈夫!上のイメージの場合32行目以降にデータがあっても、vlookup関数を修正することなく、そのまま使えます

【メリット2】 vlookup関数のコピーで範囲行がずれない

上記の例の場合、第4課の社員番号002、005あたりからエラー#N/Aとなってます。これは、F5にvlookup関数を入力後、関数をコピーした際に範囲の行が相対参照によりズレてしまったのです。

第4課の社員番号002においては、関数を入力したF5に対し11行下に位置しているので、②の範囲も11行下にずれておりvlookup(①,A12:H42,③,④)となってしまったからです。行を指定せず、列指定のみとすることでエラーが発生する可能性を低下させることができるのです。

 2-3.動作確認のため[列番号]にはまずは  1

タイトルの通り、③つ目の引数にはまず1を設定しましょう。すると、正しく設定できていれば、左したの図のように、vlookup関数の箇所も同じ社員番号が表示されます。同じ番号が表示されているので、参照先のデータが正しいことを確認できます。

この確認までできていれば、ここまでのvlookup関数の設定は正しいことが確認できます。その後、列番号を入力してエラーとなった場合は、列番号の設定に不備があることがわかるのです。一度の関数を設定しきってしまわずに、徐々に、段階的に、設定をしていくことが全体としてはムリ・ムダがなくて結果的に効率的になるのです。vlookup1-6

 2-4.エラー処理はIferror

2-3まで順調に確認してすすめた上でエラーが表示された場合は、②範囲に、①指定セルの値がないことが考えられます。(事例を説明するために、強制的に②範囲のデータを削除してみました。)

エラー処理をしていないvlookup関数だけの場合は、結果に#N/Aとエラー表示されています。ここで、vlookup関数をまるごと、IFERROR関数で包んであげましょう。包んだ後は、エラーの場合何を表示するかを指定します。以下の例では、""(ブランク)を指定していますが、ゼロや"-"(ハイフン)の設定も可能です。
vlookup1-7

このようにエラー処理しておくと、参照先にデータが存在しない場合でもエラー表示させずに、意図した表示にすることができます。

3.最後に

2-4エラー処理まで設定すれば#N/Aや"NAME?のようなエラーは発生しません。横着してステップを飛ばすと逆にエラーや手戻りとなり時間を取られることがあります。応用が上手な人ほど基本がしっかりとしているものです(あのイチローも基本が全てっていってました)。が、エラーが発生してしまったらこちらをどうぞ。
【Excel】vlookupが使えない全7ケースと解決方法

今回は基本編でしたが次は応用編として、私が10年間データサイエンティストとして(当時はそんなかっこいい呼び名なんてなかったですがね)従事してきた中で作ったマニアックな使い方を紹介したいと思います。
vlookup関数の応用編

スポンサーリンク
スポンサーリンク

関連記事

  1. illust_pc_excel_title2

    Excel

    【Excel関数】生年月日から年齢を計算する方法

    生年月日と一言でいっても数値、日付、文字列といろんな形式があります。そ…

  2. サムネイル折線

    Excel

    【Excel】折れ線グラフを塗りつぶして見やすくする作り方

    サムネイルのグラフのような、折れ線グラフを塗りつぶして効果的に魅せる方…

  3. 折れ線グラフ点線サムネ (2)

    Excel

    【Excel】実線の一部を点線に替える「折れ線グラフ」の作り方

    実績と将来予測をグラフで示したい場合など、一本の折れ線グラフの中を実線…

  4. vlookup応用

    Excel

    【Excel関数】vlookup関数の使い方<応用編>

    vlookup関数は是非使いこなしてほしい関数の一つ。全4パターンの応…

  5. Excel

    【Excel】重複を抽出して削除する方法(Countif 他全4種)

    "重複チェック"や"重複削除"の方法は、ひとつではありません。その方法…

  6. vlookupErrorサムネ

    Excel

    【Excel関数】vlookupがエラーで使えない!原因と回避方法7パターン

    vlookup関数が#N/A、#NAME?などよくエラーになってしまい…

スポンサーリンク




特集

  1. 折れ線グラフ特集1
  2. 特集働き方改革サムネ
  3. SC特集サムネ

スポンサーリンク




仕事効率アップ系記事

  1. サムネ-揃える
  2. illust_biz_filng
  3. 折れ線グラフ点線サムネ (2)
  4. Altキーサムネ
  5. ショートカットサムネ

スキルアップ系記事

  1. ハイローマトリクス-サムネ
  2. not_dateadd
  3. 体重計
  4. SCAMPER
  5. 3CPS
  6. サムネイル折線

スポンサーリンク




ピックアップ記事

  1. プレミアムフライデー
  2. analysis
  3. プレミアムフライデー
  4. 管理職マネジャー
  5. regret
  1. 折れ線グラフ特集1

    PC

    【特集】"使い方"では足りない!「折れ線グラフ」の"使いこなし方"
  2. 特集働き方改革サムネ

    Column

    【特集】「働き方改革」を独り歩きさせない!「働き方改革」の実現に向けて
  3. SC特集サムネ

    PC

    【特集】ショートカットキーで仕事効率化!働き方改革の小さな一歩👣…
PAGE TOP