検索

キーワード


【Excel】大きな表からデータを転記・検索する!VLOOKUP関数の使い方

  • 公開日:2020-10-01 16:36:32
  • 最終更新日:2021-01-25 12:31:58
【Excel】大きな表からデータを転記・検索する!VLOOKUP関数の使い方

こんにちは、新人エンジニアの田口です。

Excelをデータベースとして活用する際、データの転記や検索を驚くほど楽にしてくれるVLOOKUP関数について解説していきます。

関数についての基本的な知識があることを前提としているので、引数や戻り値といった用語がわからない方は先に以下の記事をご覧ください。

【Excel】関数入門|数値と文字列の違いや、代表的な関数について解説


VLOOKUP関数とは 

VLOOKUP関数とは、データを検索・転記する関数です。

たとえば、電話帳を使って電話番号を調べる場面をイメージしてください。

まずは上から順に目的の名前を探し、見つけたらその横に書かれている電話番号を調べるかと思います。

VLOOKUP関数の検索イメージ


VLOOKUP関数は、このようなL字型に情報を検索する作業をExcelに代行してもらうための関数といえます。


VLOOKUP関数の動きを見る

書式の解説に入る前に、実際にこの関数がどんな場面でどのように働くのかを見てみます。


下の図のように、「商品別売上表」と商品コードと商品名を対応させた「商品一覧」があるとします。

この時、商品コードに対応する商品名を売上表に入力したいのですが、いちいち一覧表から探して入力するのは面倒です。

そこで、VLOOKUP関数を使えば、一瞬でほしいデータを探し出して入力することがで来ます。


例:コードに対応する商品を転記する

VLOOKUP関数を使うサンプルデータ

この時入力したVLOOKUP関数も、次のようにL字型に検索をして転記しています。


VLOOKUP関数の動作例


引数が多くて大変に見えますが、注意することはそれほど多くはありません。

次はこの関数の書き方について解説します。


VLOOKUP関数の書式

VLOOKUP関数:データを縦方向に検索する

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


検索値検索したい「値」を指定

範囲 :値を検索して取り出す「セル範囲」を指定

列番号:取り出したい値が「範囲」の何列目にあるかを指定

検索の型:2通りの検索方法 TRUE(部分一致)かFALSE(完全一致)かを指定


これまで述べてきたような「名前を電話帳から探して電話番号を調べる」「指定したコードを一覧から探して、対応する商品名を調べる」という2つの例はいずれも、「〇〇から△△を探して、対応する□□を調べる」という形になっています。

そしてこの穴あき部分が最初の3つの引数、「検索値」、「範囲」、「列番号」に対応しています。

第4引数の「検索の型」については、特別な理由がない限りFALSEを指定してください。

別の記事にて、TRUEを使うケースについても解説します。


FALSE:「検索値」に一致する値のみを取得

TRUE:「検索値」に一致する値を検索する。ただし、一致する値がない場合は「検索値未満」で一番近い値を検索する。


VLOOKUP関数の注意点、ルール

VLOOKUP関数の記述において、特に注意が必要な点について述べておきます。


引数の注意点

引数の指定を誤ると、データの転記や検索がうまくいかないことがあります。以下ではよくある間違いや、注意することについて解説します。


▼ 列はシート基準ではなく、指定した範囲を基準とする

以下はよくある勘違いの例です。

先述したコードに対応する商品を転記する例を使って確認します。


取り出したい値である商品名列はC列にあります。C列はシート全体から見て、3列目です。

そこで、VLOOKUP関数の「列番号」は3と指定しました。すると下記のようにエラーとなります。


存在しない列を指定して参照エラーが返ってくる例


第3引数の「列番号」は、第2引数で指定した範囲の何列目のデータを転記するかを指定することを覚えておきましょう。


▼ セル範囲は、取り出す値を探す範囲も指定する

同じくコードに対応する商品を転記する例を使って、よくある勘違いの例を確認します。


検索値B4の値であるA001を検索します。

商品コードを探すために、第2引数にB列のみを範囲指定しました。

すると以下のようにエラーが発生します。


セル範囲を誤って指定して、参照エラーが返ってくる例


第2引数の「範囲」には、第3引数で指定して取り出す値が含まれている列も指定しましょう。


▼ 「検索値」は「範囲」以外のセルを指定する

同じくコードに対応する商品を転記する例を使って、よくある間違い例を確認します。

今回は失敗例を表示するためあえて商品別売り上げ表の順番を入れ替えています。


以下の表には、検索値で指定したい商品コードが2か所にあります。

どちらを指定したらよいかわからなかったので、商品一覧にある商品コードを指定することにしました。

すると以下のようにエラーが発生します。


範囲以外の列を指定して、正しい結果が得られなかった例


売上表では、本来「りんご」であるはずのA001が「みかん」になっていたり、C6セルとC7セルではエラー値が返されています。

このように正しい検索結果が得られない可能性があるので、第1引数の「検索値」と第2引数の「範囲」で、同一のセルを指定することがないようにしてください。


参照表のルール

参照表とは一般的な用語ではありませんが、ここでは「範囲」で指定する表という意で使用します。

先ほどの例でいうと、「商品一覧」の表を指します。


参照する表は別シートで管理する

たとえば先ほどの表の商品別売り上げ表に行や列を追加・削除を行うとします。

この時同じシートに参照表があると、参照表にも行や列を追加・削除が反映されて、表の内容が壊れてしまう恐れがあります。

このような場合でも、参照表を別シートで管理しておけば、どんなに入力用の表に修正が加わっても影響が出なくなります。


参照する表を同一シートで扱い、列を挿入して表の内容を壊した例


VLOOKUP関数の活用例

VLOOKUP関数の様々な使い方について解説しています。

【Excel】VLOOKUPで検索|検索値に対応する二つの値を表示させる

【Excel】IF関数の代わりにVLOOKUP関数を使った条件分岐のやり方。

【Excel】VLOOKUP関数で「TRUE」を使って近似値検索を行うパターンの動きを確認する


【著者】

田口

元建築・リフォーム営業のエンジニア。現在はDXの需要に合わせてネットワークやクラウド、セキュリティといったインフラ方面の勉強中。

よく読まれている記事
【Excel】箱ひげ図の見方と作成方法について

【Excel】箱ひげ図の見方と作成方法について

こんにちは。新人エンジニアの前山です。Excel グラフの作り方 ではグラフの作成方法とレイアウトの編集について基本的な事項を解説しました。本記事では、Excelで作成できる箱ひげ図の見方とを作成方法についての解説を行います。箱ひげ図とは箱ひげ図とは、データのバラツキ、どの部分に集中しているかなどを「箱」と「ひげ」を用いてわかりやすく表したものとなります。大量のデータを扱う場合、平均とのみを活用す

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

【Excel】複数条件で判定|AND関数,OR関数を使った条件分岐|IF関数

AND関数、OR関数を使って、IF関数で複数の条件を指定するこんにちは、新人エンジニアの田口です。今回は具体的な例を用いてIF関数の活用場面を見ていきます。主に以下の内容について解説します。・IF関数で部分一致、完全一致の条件指定を行う・OR関数・AND関数説明にあたって、以下の事柄について知識があることを前提とします。・IF関数の基本的な書式・引数など関数に関わる用語本文中でわからないことがあっ

【Excel】グラフの作り方、レイアウトの編集方法

【Excel】グラフの作り方、レイアウトの編集方法

こんにちは。新人エンジニアの前山です。本記事では、Excelでのグラフの作り方とタイトルや目盛線、凡例といったよく使用する機能の解説を行います。データテーブルや近似曲線はあまり使用頻度は高くありませんが、ここぞというところで使用すれば、グラフの品質を1段階上げることができます。グラフの作り方1.グラフにしたい表の選択 2.挿入>おすすめグラフのクリック 3.作成するグラフ種類の選択今回は例として集

【Excel】日付処理の活用例 - 前日、翌日の求め方

【Excel】日付処理の活用例 - 前日、翌日の求め方

こんにちは。新人エンジニアの前山です。前回の記事 ではExcelでの日付の取り扱いと基本的な使い方を記載しました。本記事ではその応用として、任意の日付の前日、翌日日付の求め方と関数の紹介をしたいと思います。関連記事: 【Excel】日付処理の活用例 - 指定した日付までの日数の求め方 【Excel】日付処理の活用例 - 月初、月末の求め方前日、翌日の計算方法前日、翌日日付には主に3つの求め方があり

【Excel】日付処理で使用する関数、シリアル値について

【Excel】日付処理で使用する関数、シリアル値について

こんにちは。新人エンジニアの前山です。本記事ではExcelで使用される日付関連の情報をご紹介します。Excel内では日付はシリアル値という数値で管理されており、仕組みを知れば、柔軟な日付計算が可能になります。また、日付には様々な関数が用意されており、翌日前日、期間、月初、月末、営業日などが求められます。日付についての理解を深めれば日々の業務がぐっと楽になります。シリアル値とはシリアル値とは、Exc