【VLOOKUP関連記事】

VLOOKUPとは？使い方や活用方法をわかりやすく解説

VLOOKUP関数で複数条件を指定する？簡単にできる方法を図解

VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説

VLOOKUPで別シートを参照するには

VLOOKUP関数は、Excelで使用できる関数です。この関数を利用すれば、Excel表のデータが多くても効率的に検索し、必要な情報を取得できます。

VLOOKUPは同じシート内から検索したデータを取得できますが、別のシートからデータを引っ張ることも可能です。別シートからのデータ取得を活用すると、より便利にExcelを使うことができます。

この記事では、ExcelでVLOOKUPをより活用したい方のために、VLOOKUPで別シートを参照してデータを検索する具体的な手順や、活用できるシーンなどを解説していきます。

プログラミングの関数とは？役に立たない？学び方と実務で役立つスキル

VLOOKUPの基本

ここでは、VLOOKUPの概要と構文、引数の意味について解説していきます。まずは基本を押さえましょう。

VLOOKUPは、Excelで表内のデータを縦方向に検索し、指定された条件に一致する情報を取得します。

例えば、商品IDから商品名を抽出したり、社員番号から氏名を引き出したりする場合に使われます。

VLOOKUPを使用することで、大量のデータの中からでも特定の値を検索して求める情報を取り出すことができます。これにより、目視や手作業による検索や、コピー＆ペーストなどの作業の手間を大幅に削減できます。

また、今回解説する別シートの参照によって、シートを更新すると自動的に他のシートのデータに反映されるようにすることもできます。これにより手動でのデータ更新作業が減り、作業ミスを減少させることができます。

VLOOKUPは、「検索値」「範囲」「列番号」「検索方法」の4つの引数を指定して使います。それぞれの引数の役割について解説します。

■検索値 検索値はVLOOKUPの最初の引数で、探したい数値や文字列を指定します。例えば、検索範囲から「消しゴム」という商品の価格を見つけたい場合、検索値として「消しゴム」を入力します。

関数内に直接文字列を入力する際は「=VLOOKUP("消しゴム",J4:L12,2,FALSE)」のように前後を「"」（ダブルクォーテーション）で囲んで記述します。

セル参照も可能で、例えば「=VLOOKUP(D4,J4:L12,2,FALSE)」と記述すれば、D4セルに入力されている文字列や数値が検索値となります。

■範囲 範囲は、VLOOKUPの2つ目の引数です。検索対象となるデータの範囲を指定します。

注意点として、検索値はこの範囲の1番左の列に位置しなければなりません。例えば、指定した範囲の左から2列目を検索対象にしようとすると、範囲の設定の誤りとなり「#n/a」エラーが発生します。

■列番号 列番号は、VLOOKUPの3つ目の引数で、範囲内で取り出したい値がある列を指定します。左端の列から数えて何列目にあるかを入力します。

例えば、「商品名」が1列目、「価格」が2列目、「カテゴリ」が3列目であれば、カテゴリを取り出すには列番号として「3」を指定します。

■検索方法 検索方法は、VLOOKUPの4つ目の引数です。検索値の検索方法が、完全一致か近似一致かを指定します。

完全一致を指定するには「FALSE」、近似一致の場合は「TRUE」を入力します。この引数は省略可能ですが、省略すると「TRUE」で検索されます。

完全一致で検索するケースが多いことや、意図しない検索結果を取得することを避けるため、「FALSE」を入力するのが一般的です。

VLOOKUPで別シートを参照する方法

VLOOKUP関数は、同じシートのデータを検索できるだけでなく、異なるシートにあるデータを検索して取得することで、さらに便利に使用できます。ここでは、別シートからデータを参照する基本的な方法を解説します。

まず、VLOOKUP関数を入力したいセルに、「=VLOOKUP(検索値, 」と、1つ目の引数である検索値までを入力します。

別シートを参照する場合、次に記述する2つ目の引数である、範囲がポイントです。指定するセル範囲の前に、別シートの「シート名!」を記述します。

例えば、別シート名が「商品一覧」の場合、「商品一覧!C4:E12」のようになります。

検索値の記述の後に続けて、直接範囲を文字入力するのではなく、マウスで別シートに切り替えてドラッグで範囲を選択すると、セル範囲の前に「シート名!」が自動的に入力されます。

次に、取得したい値が存在する列番号を指定します。これは、範囲内で検索したい値が含まれている列の番号です。例えば、範囲内の3列目にある値を取得したい場合、「3」と入力します。これが3番目の引数となります。

最後に、検索方法を指定します。完全一致の場合は「FALSE」、近似一致の場合は「TRUE」を入力します。意図せず「TRUE」になっていると検索がうまくいかないことがあり、完全一致で検索することが多いため、基本的には「FALSE」を入力することをおすすめします。

全ての引数を入力したら、数式を確定します。このようにして、別シートからデータを参照するVLOOKUP関数を入力することができます。

VLOOKUPを入力する時、直接記述するのではなく、関数の挿入機能を使って入力することもできます。この方法は他の関数でも利用できるので、知っておくと便利でしょう。

VLOOKUP関数を使用したいセルをクリックし、数式バーの横にある「fx」ボタンを押して「関数の挿入」ウィンドウを開きます。次に、「VLOOKUP」を検索して選択し、「OK」を押します。

「関数の引数」で、VLOOKUPの引数を順に入力します。検索値は直接値を入力するか、マウスでセルを指定できます。

範囲の欄の入力では、範囲の値を直接入力することも、マウスで範囲指定することもできます。

マウスで範囲指定する場合は、「範囲」欄の右にある上向き矢印をクリックし、参照先の別シートに切り替えて、対象のセル範囲を選択してEnterキーを押します。

例えば「商品マスタ」シートを選択して範囲指定すると、「商品マスタ!B2F20」のように、別シートの名前が付与された範囲が自動的に入力されます。

あとは、列番号、検索方法を入力してOKボタンを押して数式を確定します。すると、セルにVLOOKUPの数式が入力されます。

VLOOKUPで別シートを参照する実践例

ここでは、実際にVLOOKUPで別シートの検索を行ってデータを取得し、より便利に使う実践例を紹介します。

VLOOKUP関数を使用して「価格表」シートで商品の価格を一元管理し、価格情報が変更されたら、異なる複数の受注シートの情報をまとめて更新する方法を解説します。

まず、「東京都受注」「千葉県受注」などエリアごとの受注シートを作成し、それぞれ、「受注コード」「商品名」「価格」「個数」の項目を用意します。

この受注シートに、商品価格をその度に入力するのは手間がかかりますし、商品価格は変更される場合もあります。そのため、この価格が自動的に入力されるようにしたいです。

そこで、「商品名」と「価格」のデータを持つ「価格表」シートから「東京都受注」「千葉県受注」シートに商品価格を取得するようにします。

「価格表」シートには、B列に商品名、C列に価格を入力します。

「東京都受注」「千葉県受注」それぞれのシートには、「価格」列のD3セルに以下のVLOOKUP関数を入力します。範囲は、フィルハンドルを行ったときに動かないよう「$」を付けます。

=VLOOKUP(C3,価格表!$B$3:$C$12,2,FALSE)

これで、商品名を検索値として、「価格表」シートのデータがある範囲から一致する価格を検索し、結果を表示します。

関数をC列の他のセルに縦にフィルハンドル入力することで、他の商品コードに対応する価格を自動的に取得します。

こうすることで、例えば「ラジオ」の価格が変更された場合、「価格表」シートの該当セルを更新するだけで、「東京都受注」「千葉県受注」シート両方に反映されます。

VLOOKUPを使用して複数の別シートから情報を取得し、1つのシートにまとめる方法を紹介します。

ここでは、自社の各営業課の情報をまとめる「営業課情報」シートに、別シートの「売上」シートや「担当エリア」シートから営業課ごとの情報をまとめる例を見てみましょう。

「営業課情報」シートには、「営業課」「売上」「担当エリア」があり、このシートに情報をまとめます。「営業課」を検索値としてデータを取得します。

「売上」シートには「営業課」と「売上」のデータ、「担当エリア」シートには「営業課」と「売上」のデータがあります。

「営業課情報」シートで売上を取得するために、「売上」列の1番上のC3セルに次のVLOOKUP関数を入力します。

=VLOOKUP(B3,売上!$B$3:$C$6,2,FALSE)

この数式で、「営業課情報」シートの「営業課」を検索値として、「売上」シートの指定範囲から売上データを検索し、対応する売上金額を取得します。

そして、担当エリアを取得するために、「担当エリア」列の1番上のD3セルに次のVLOOKUP関数を入力します。

=VLOOKUP(B3,担当エリア!$B$3:$C$6,2,FALSE)

これで、「営業課情報」シートの「営業課」を検索値として、「担当エリア」シートから対応する担当エリアを取得します。

売上、担当エリアともに、VLOOKUP関数を同じ列の下のセルにもコピーします。

これで、「売上」や「担当エリア」などの個別の情報はそれぞれのシートで管理して、「営業課情報」シートで各営業課の情報を一目で把握することができます。

さらに便利なVLOOKUPの使い方

ここでは、VLOOKUP関数をより便利に使うための応用を紹介します。複数条件での参照方法、COLUMN関数を使った応用、そして別ファイルとの連携を解説します。

VLOOKUP関数は、通常、1つの検索値に基づいて検索結果を取得します。そのため、複数条件の検索は行えませんが、複数つの検索値を1つに結合したセルを用意することで実現できます。

例えば、「出身地」と「名字」を条件にして「職業」を探したい場合、表に「出身地・名字」の列を準備し、「出身地」と「名字」を「&」で結合します。上図では、「=B3&C3」を入力しています。

これで、2つの検索値を結合した文字列を、新たに検索値として使えるようになりました。

次に、作成したセルをオートフィル機能を使って表の1番下まで伸ばします。これで、全てのデータに対して出身地と名字が結合された状態となり、VLOOKUP関数の検索値として利用できるようになります。

COLUMN関数は、Excelでセルの列番号を返す関数です。VLOOKUP関数とCOLUMN関数を併用することで、複数の列に横方向のフィルハンドルでVLOOKUPを簡単に入力できます。

例えば、「受注」シートに、「商品マスタ」シートから各列の情報を参照する場合を考えます。

「受注」シートにVLOOKUP関数を入力する場合、列番号にCOLUMN関数を記述しましょう。

=VLOOKUP("消しゴム", 商品マスタ!$B$2:$F$34, COLUMN(商品マスタ!B$2), FALSE)

こうすることで、「受注」シートの左端にVLOOKUPを入力し、右方向にフィルハンドルを行うことでCOLUMN関数により動的に列番号を取得して、対応した列のデータを取得します。

VLOOKUPを使用して、別のExcelファイルのデータを参照することもできます。これにより、異なるファイル間でデータを一元管理することができます。

例えば、商品情報ファイルで価格が変更された場合、それを参照元とする他のファイルでもその変更が反映されるため、1ファイルの更新だけで済むメリットがあります。

ただし、複数のファイル間でリンクを管理する必要があるため、ファイルの移動や名前変更によってリンクが切れて、参照エラーが発生しやすくなります。

また、別ファイルが他のユーザと共有されている場合には運用に注意が必要です。ファイルが他のユーザによりロックされて作業できない、アクセス権限が不足してデータを取得できないケースが発生することがあります。

VLOOKUPで別シートを参照してデータ処理の幅を広げよう

VLOOKUP関数は、Excelで大量のデータを効率的に検索し、特定の情報を抽出するための便利な関数です。同じシート内だけでなく、別シートのデータを参照することで、VLOOKUPを使用したデータ処理の幅がさらに広がることが分かりました。

VLOOKUPで別シートからデータを取得して、変動する情報を一元的に管理したり、別々のシートから情報をまとめたりして、日々のデータ処理の効率化に繋げましょう。