VLOOKUP関数は使わないと損 超基本から疑問点までひととおりおさらい

スポンサードリンク




Excelでかなり使えるVLOOKUP関数。私はよく助けてもらっています。

このVLOOKUPについて、基本的なことをまとめてみました。

VLOOKUPは実務でも大活躍

私がExcelで使えると感じているスキルはいくつかあります。

特にこの3つ、

  1. テーブル
  2. ピボットテーブル
  3. VLOOKUP

は、Excelを使うなら、ぜひ使っておきたいところです。

テーブル、ピボットテーブルについては、こちらの記事をご覧いただくとして

Excelの「テーブル」は超スグレモノ 見やすさ、使いやすさ100点の表に一瞬で変わる!

2016.10.16

店舗別売上の集計はExcelのピボットテーブルが便利 いろんな角度からの集計が一瞬でできる

2016.09.28

今回、取り上げたいのはVLOOKUP。

今日も「エクセル VLOOKUP 0表示されない」というキーワードで検索いただいていたようですし、注目されている関数でしょう。

会計ソフトからダウンロードしたCSVファイルをExcelのシートに貼り付けると、別の用意しているシートに

こうして準備したシートに科目で検索して、数字を抽出することができます。

このときに使っているのが、VLOOKUP関数です。

 

VLOOKUPを使うことで、かなり仕事も効率化できます。ぜひ使っていきましょう。

 

VLOOKUP関数の算式の謎をとく

「VLOOKUP、よくわからないんだよね」という声もあるかと。私もそうでしたし。

というわけで。ここでもう一度VLOOKUPについてかんたんな表で確認をしてみることにします。

まずVLOOKUP関数の使いどころですが、こういった感じです。

VLOOKUP関数の式ですが、こういうものです。

VLOOKUP関数の算式

VLOOKUP(検索値,検索範囲,列番号,検索方法)

これについて、1つずつ見ていきます。

検索値

検索値は売上表と単価リストをつなぐものです。

上記の例であれば、商品コードA001を入力することで、商品リストから商品名と単価を検索し、抽出します。

検索範囲

抽出する元データは、どの範囲かを決めます。

この例でいうと、単価リストの「H3:J6」ということになります。

$マークで絶対参照として、式をコピーしてもずれないようにしています。

列番号

列番号というのは、検索範囲のうち何列目のデータを抽出するか。

商品名は検索範囲でいうと2列目になるので、「2」です。同様に単価の場合には「3」。

どうです?思ったより難しくない、いけそうですよね?

検索方法

ややこしいところがあるとしたら、この検索方法です。

VLOOKUP関数を入力していくとわかるのですが、検索方法は[TRUE]と[FALSE]のどちらかです。

このケースならリストにある商品コードのうち、一致するものを探すので、この場合は完全一致の[FALSE]を選択するということになります。

[TRUE]と[FALSE]についてちょっと解説

ここで[TRUE]と[FALSE]について、簡単に解説しておきましょう。

 

TRUEとFALSEの違いって?
  • [TRUE] … 近似一致
  • [FALSE]  … 完全一致

ということで、まず「FALSE(完全一致)」は、リストにある一致する検索値を探すという場合に使います。

じゃあ、[TRUE]を使う場面はどこなんだろ??ということです。

これについては、別の表でみてみましょう。

例えば、札幌店で見ると、評点148を検索値として、評価基準から探しにいっているのですが、同じ148というのはどこにもないわけです。

この場合に検索方法をTRUE(近似一致)とすることで、一番近い値、「その行の値以上、次の行の値未満」の値を抽出します。

補足
J列の「意味は?」を見ていただければ、「その行の値以上、次の行の値未満」の意味がわかると思います。

札幌店の評点「148」なら評価基準のリストで「120以上、160未満」なので、「B」を表示する。ということになります。

これ、if関数でもできるのですが、数式が長くなり、ミスがあってもどこが間違っているかを探すのも結構大変。

その点、VLOOKUP関数ならシンプルです。

注意
元データが昇順に並んでいないと、正しく抽出されませんので、事前に整理しておきましょう。

範囲で検索したいといったときは、[TRUE]ってことです。

 

最初に知っておきたい VLOOKUPのわからないこと

VLOOKUPについて、よくある疑問をまとめてみました。

といっても、これまでに私が感じていたものも多いのですが…。

Q1 リストが追加されたら範囲を変えないといけないんじゃないの?

それはそうなんですが、最初から範囲指定で列全体を指定しておけば、大丈夫です。

先程の例で見ると、検索範囲を[$H$3:$J$6]としているのですが、ここを[$H:$J]とします。

補足
絶対参照($)はF4をクリックすると設定できます。

そうすれば、単価リストにデータを追加しても困りません。

もう1つ言うと、算式の最初、検索値の列にも「$」を使うようにしています。

Q2 数式を入れておいてもエラー表示されないようにしたいんだけど…

VLOOKUP関数。

検索値である商品コードが入っていないと、こんな感じで#N/A(エラー)表示されます。

これを回避するために、IFERROR関数を使います。

こういった感じになります。

IFERROR(VLOOKUP($B,$H:$J,3,0),””)

「値がない場合には””(ブランク)にしてね」ってことになります。

ちょっとif関数のようなイメージもしますが。

その辺りの話、詳しくはこちらの記事をご覧いただければ。

VLOOKUP関数でエラー値を表示させないためにIFERROR関数を使う

2017.06.06

Q3 式を他のセルにもコピーしたいけど崩れるよね?

VLOOKUP関数。

検索値や検索範囲をF4で絶対参照をしているものの、そのままでは算式をコピーしてもうまくいかないことが多いです。

その原因は列番号があるから。

列番号が数字なので、算式をコピーした時に「2」のままということに…。

これを回避するためには、「2」を算式に変えることが必要です。

COLUMN関数を使って列番号を返すことができます。

補足
例えば、COLUMN(B2)なら、B列は2番目の列なので、「2」となります。COLUMN(B3)でも同じB列なので答えはやっぱり「2」になります。

ここまで説明したことを全部盛り込んでみるとこうなります。(FALSEは省略)

=IFERROR(VLOOKUP($B6,$H:$J,COLUMN(B5),FALSE,),””)

だんだん、ごっつい数式になってきた…ところで締めたいと思います。

 

ということで。VLOOKUP関数、ここまで使うかどうかは別として、[TRUE]と[FALSE]を使い分けるだけでも、結構救われます。

ぜひ使ってみていただければ。

 


【編集後記】
昨日はお客様訪問。お客様のEvernote(Windows版)でなぜか同期エラーが…。ログを触ったり色々やってみて、もう一度インストールしたら無事回復しました。そのあとの仕事はなんとか順調に進みました。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
グラマシーニューヨーク グレープゼリー
コンビニのネットワークプリントで写真を印刷
経営革新等支援機関の変更手続き書類の作成


スポンサードリンク