Excel初心者

[Excel]絶対に覚えておきたい『VLOOKUP関数』の使い方

しんたろ。
  1. 「表引き」を活用したい。
  2. 入力したら自動で表示されるようにしたい。
  3. 違うSheetもしくはブックに入っている情報をコピペせずに簡単にデータを引っ張りたい。

 そんなあなたにとっておきの関数があるんです。Excelを使うなら、絶対覚えていてほしい関数ランキング1位を誇るVLOOKUP関数!

しんたろ。

快適なExcelライフを過ごせるようになりましょう!

そもそもVLOOKUP関数の条件とは

まずはどんな関数なのかを説明すると・・・

VLOOKUP1
赤枠に青枠の「ID」を入力。すると
VLOOKUP2
「果物マスター」に入っている情報が勝手に反映される。「小計」にSUM関数を入れているので「数量」に必要な数量を入力すると小計も表示されます。

引数説明

 今回の「VLOOKUP関数」を使用する箇所はE列果物名とF列単価の2箇所になります。

しんたろ。

VLOOKUP関数の引数は

=VLOOKUP(検索値,範囲,列番号,FALSE)

で構成されているよ♪

1つ目の引数「検索値」はA001の『IDになります。

2つ目の引数「範囲」はマスターデータが入力されているセル範囲を指定します。

 上図でいうと果物マスターの『B10:D14』の範囲を指定します。

3つ目の引数「列番号」は『マスターデータの何列目の値を表示するか』を指定します。

4つ目の引数は「データの検索方法」を指定できます。通常の表引きをする際に「FALSE」にしておけば問題ないです。

まずは「果物名」をVLOOKUP関数で組んでみよう

実際に入力していきましょう。

※「果物名」は「ID」から数えて左から2番目
  1. セル「E4」を選択。
  2. 検索値(引数1つ目)に、セルB4の「ID」を指定します。
  3. 範囲(引数2つ目)に、セルB11:セルD16の「果物マスターのリスト」を指定します。
  4. 列番号(引数3つ目)に、果物名の列番号「」を指定します。
  5. 検索方法についてはFALSEを入力。

\ 完成! /

=VLOOKUP(B4,B11:D16,2,FALSE)

同じ考えで「単価」をVLOOKUP関数で組んでみよう

※「単価」は「ID」から数えて左から3番目
  1. セル「F4」を選択。
  2. 検索値(引数1つ目)に、セルB4の「ID」を指定します。
  3. 範囲(引数2つ目)に、セルB11:セルD16の「果物マスターのリスト」を指定します。
  4. 列番号(引数3つ目)に、果物名の列番号「3」を指定します。
  5. 検索方法についてはFALSEを入力。

\ 完成! /

=VLOOKUP(B4,B11:D16,3,FALSE)

・完成動画

 「果物名」「単価」『VLOOKUP関数』を入力し終わりましたね。

 セルB4の「ID」にマスターデータにあるセルB列のIDを入力すると、情報が反映されます。そして任意の数量を入力すると「小計」が表示されます。

▼動画で詳細を確認できます。

コピペすると選択した範囲がズレてしまう・・・。そんな方には「絶対参照」を活用しよう

 「完成したからこのままコピペしよう!」

 実はこのまま数式をコピーして下の欄にペーストすると、セルがズレた分だけズレます。

そんな時は、「絶対参照」を活用するとズレなくなります。

▼動画で詳細を確認できます。

・変更前

・「絶対参照」を活用後

「ID」のセルは正しくズレていますが、マスターデータの範囲は変わりません。

▼絶対参照の使い方については、以下の記事に詳細がありますのでご参照ください。

>>コピペするとズレる?絶対参照で簡単に解決する方法

B列のIDをいちいち手打ちで入力することが面倒くさい・・・。そんな方には「データの入力規則」で『リスト』から選択式へ

 『A001』を手打ちで打っていたら、いつかミスをしてしまいます。

マスターデータがある場合は、マスターデータを引用して『リスト』から選択できるようにしましょう。

▼動画で詳細を確認できます。

ドロップダウンによる『リスト化』のやり方は、「データの入力規則」で対応可能です。

▼データの入力規則の使い方については、以下の記事に詳細がありますのでご参照ください。

>>入力を《リスト化で選択式へ》。複数連動と自動追加の方法

エラー値の意味と非表示対策:IFERROR関数

IFERROR
あずたろ。

だいたいわかったけど、さっきの図、エラーが出てて見にくいね

しんたろ。

そんな時は、「IFERROR関数」が便利♪

IFERROR関数の使い方はIFERROR(対象のセル範囲,エラー時に表示する内容)になります♪

IFERROR
「IFERROR」を使用して表がスッキリなりました♪

▼IFERROR関数の詳しい解説をみる

>>IFERROR関数を使用してエラー時の表示を変える方法

データの重複を2つの方法で確認する方法

 例えばマスターデータにあるB列「ID」値が重複している場合VLOOKUP関数は上から順に行のデータを使用するため、②のいちごがヒットしません。

そもそも値が重複していること自体が誤りで、入力間違いなどで起きる可能性があります。データが正しく運用されていない、このような状態は非常に危険です。

 重複を簡易的に確認する方法が2あります。まず1つ目「COUNTIF関数」。2つ目「条件付き書式」。以上の2つで識別することをオススメします。

COUNTIF関数

アイコン名を入力

COUNTIF関数は

=COUNTIF(対象のセル範囲,検索値

になるよ♪

▼COUNTIF関数の詳しい解説をみる

>>徹底解説!COUNTIF関数で特定の条件にあった〇〇だけを合計する方法

条件付き書式

ホームタブ → セルの強調ルール → 重複する値 をクリック
色で分けるとより見やすくなる。オススメです♪

▼条件付き書式の詳しい解説をみる

>>セルに色付を自動で10倍早くする方法

検索値の情報が複数あった場合に、全検索値の結果を表示する方法

 上記図のように「果物名」によって「取り扱い者」が違う場合、上から下にかけてヒットした検索値しか表示できません。

 ですが、例えば「果物名」の『りんご』を取り扱った人を全員一括で取り出せる方法があります。

▼動画で詳細を確認できます。

▼全検索値をする方法の詳しい解説をみる

>>VLOOKUP関数で複数ある検索値の全データを表示する方法

まとめ

VLOOKUP関数のまとめ
  1. =VLOOKUP(検索値,範囲,列番号,FALSE)
  2. コピペでズレる時は「絶対参照」を活用
  3. 手打ちで入力ミスを防ぐために「データの入力規則」を活用

 VLOOKUP関数は覚えておきたい基本の関数です。

色々な関数と組み合わせることで更に使いやすくなります。

 その中でも「COUNTIF関数」とは相性が良いので、単独でしか検索できないVLOOKUP関数を複数で検索できるようになります。

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました