Excelでかなり使えるVLOOKUP関数。私はよく助けてもらっています。
このVLOOKUPについて、基本的なことをまとめてみました。
VLOOKUPは実務でも大活躍
私がExcelで使えると感じているスキルはいくつかあります。
特にこの3つ、
- テーブル
- ピボットテーブル
- VLOOKUP
は、Excelを使うなら、ぜひ使っておきたいところです。
テーブル、ピボットテーブルについては、こちらの記事をご覧いただくとして
今回、取り上げたいのはVLOOKUP。
今日も「エクセル VLOOKUP 0表示されない」というキーワードで検索いただいていたようですし、注目されている関数でしょう。
会計ソフトからダウンロードしたCSVファイルをExcelのシートに貼り付けると、別の用意しているシートに
こうして準備したシートに科目で検索して、数字を抽出することができます。
このときに使っているのが、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] … 完全一致
ということで、まず「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関数のようなイメージもしますが。
その辺りの話、詳しくはこちらの記事をご覧いただければ。
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新」→詳細はコチラ
グラマシーニューヨーク グレープゼリー
コンビニのネットワークプリントで写真を印刷
経営革新等支援機関の変更手続き書類の作成
