MENU

＼Excelショートカット集もらえる／

【無料】LINE登録する
  1. ホーム
  2. Microsoft
  3. Excel
  4. ExcelのVLOOKUP関数マスター：異なるシートやファイルからのデータ参照テクニック

ExcelのVLOOKUP関数マスター：異なるシートやファイルからのデータ参照テクニック

Excel
長内孝平
目次

はじめに：VLOOKUP関数の新たな使い方

ExcelのVLOOKUP関数は、データ分析や情報管理において非常に強力なツールです。これまで同一シート内でのデータ参照に使用してきた方も多いでしょう。しかし、VLOOKUP関数の真の力は、異なるシートや別のファイルからデータを参照できる点にあります。

本記事では、以下のようなお悩みを抱える方々に向けて、VLOOKUP関数の高度な使用方法をご紹介します：

  • 複数のシートやファイルに散らばったデータを効率的に統合したい
  • 大量のデータを含む別ファイルから特定の情報だけを取得したい
  • データの更新作業を自動化し、作業時間を短縮したい

VLOOKUP関数を使いこなすことで、これらの課題を簡単に解決できます。さらに、作業効率が大幅に向上し、ミスのリスクも減らすことができるのです。

VLOOKUP関数の基本おさらい

本題に入る前に、VLOOKUP関数の基本的な構造を簡単におさらいしましょう。VLOOKUP関数は以下の構文を持ちます：

=VLOOKUP(検索値, テーブル範囲, 列番号, [検索方法])
  • 検索値：探したい値
  • テーブル範囲：検索を行うデータの範囲
  • 列番号：返したい値が含まれる列の番号
  • 検索方法：TRUE（あいまい検索）またはFALSE（完全一致）

これまでの使用方法では、通常同一シート内のデータを参照していました。しかし、ビジネスの現場では、異なるシートや別のファイルにデータが存在することも多々あります。そこで、VLOOKUP関数を使って異なるシートやファイルからデータを参照する方法を詳しく見ていきましょう。

異なるシートからのデータ参照

まず、同じExcelファイル内の異なるシートからデータを参照する方法を説明します。この技術を使えば、複数のシートに分散したデータを効率的に統合できます。

手順：

  1. VLOOKUP関数を入力し始めます：=VLOOKUP(
  2. 検索値を指定します（例：C13）
  3. カンマを入力し、テーブル範囲の指定に移ります
  4. ここで重要なポイントがあります。シートを切り替えるには、以下のショートカットを使用します：
    • Windowsの場合：Ctrl + Page Down
    • Macの場合：Option + 右矢印
  5. 目的のシートに移動したら、テーブル範囲を選択します（例：B4:D9）
  6. 範囲選択後、自動的に元のシートに戻ります
  7. 数式バーを確認すると、次のような形式で表示されているはずです：
    =VLOOKUP(C13,シート名!$B$4:$D$9,
  8. 列番号と検索方法を指定して関数を完成させます（例：3,FALSE)

完成した関数の例：

=VLOOKUP(C13,シート名!$B$4:$D$9,3,FALSE)

重要なポイント：

  • シート名の後には感嘆符（!）が付きます。これはExcelの内部表記で、その前にシート名が入力されていることを示しています。
  • テーブル範囲は絶対参照（$記号）にすることをお勧めします。これにより、関数をコピーしても範囲が変わらないようになります。

この方法を使えば、大量のデータを含む複数のシートから必要な情報だけを効率的に抽出し、一つのシートにまとめることができます。例えば、各部門の売上データを別々のシートで管理している場合、この技術を使って全部門の売上を一つの集計表にまとめることが可能になります。

練習問題1：異なるシートからのデータ参照

問題：
あなたは人事部門で働いています。「社員情報」シートに社員の基本情報があり、「給与情報」シートに各社員の給与データがあります。社員番号を使って、「社員情報」シートに各社員の給与情報を表示したいと思います。VLOOKUP関数を使ってこれを実現してください。

回答：
「社員情報」シートのD2セルに以下の関数を入力します：
=VLOOKUP(A2,給与情報!$A$2:$B$100,2,FALSE)

解説：
この関数は以下のように機能します：

  • A2：現在のシートの社員番号（検索値）
  • 給与情報!$A$2:$B$100：「給与情報」シートのデータ範囲（テーブル範囲）
  • 2：給与データが含まれる列番号
  • FALSE：完全一致検索

この関数を使用することで、社員番号を基に別シートから給与情報を自動的に取得し、「社員情報」シートに表示することができます。人事部門の業務効率化に大きく貢献するでしょう。

異なるファイルからのデータ参照

次に、完全に別のExcelファイルからデータを参照する方法を説明します。この技術は、大規模なデータ管理や、部門間でのデータ共有が必要な場合に特に有用です。

手順：

  1. VLOOKUP関数を入力し始めます：=VLOOKUP(
  2. 検索値を指定します（例：C13）
  3. カンマを入力し、テーブル範囲の指定に移ります
  4. ここで、参照したい別のExcelファイルを開きます
  5. 目的のシートに移動し、テーブル範囲を選択します
  6. 選択後、元のファイルに自動的に戻ります
  7. 数式バーを確認すると、次のような形式で表示されているはずです：
    =VLOOKUP(C13,'[ファイル名.xlsx]シート名'!$B$4:$D$9,
  8. 列番号と検索方法を指定して関数を完成させます（例：3,FALSE)

完成した関数の例：

=VLOOKUP(C13,'[ファイル名.xlsx]シート名'!$B$4:$D$9,3,FALSE)

重要なポイント：

  • ファイル名とシート名は角括弧と単一引用符で囲まれます。
  • 異なるファイルを参照する場合、Excelは自動的にテーブル範囲を絶対参照（$記号）に設定します。これは非常に便利な機能です。

この方法を使用することで、異なるファイルに保存されている大量のデータを効率的に統合し、分析することが可能になります。例えば、各支店の売上データが別々のファイルで管理されている場合、この技術を使って全支店の売上を一つの集計ファイルにまとめることができます。

練習問題2：異なるファイルからのデータ参照

問題：
あなたは経理部門で働いています。「当月経費.xlsx」というファイルに今月の経費データがあり、「予算管理.xlsx」というファイルに各部門の予算上限が記録されています。部門コードを使って、「当月経費.xlsx」に各部門の予算上限を表示し、経費が予算を超過していないかチェックしたいと思います。VLOOKUP関数を使ってこれを実現してください。

回答：
「当月経費.xlsx」のD2セルに以下の関数を入力します：
=VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$B$50,2,FALSE)

解説：
この関数は以下のように機能します：

  • A2：現在のシートの部門コード（検索値）
  • ‘[予算管理.xlsx]Sheet1’!$A$2:$B$50：別ファイルの「予算管理.xlsx」内のデータ範囲（テーブル範囲）
  • 2：予算上限が含まれる列番号
  • FALSE：完全一致検索

この関数を使用することで、部門コードを基に別ファイルから予算上限を自動的に取得し、「当月経費.xlsx」に表示することができます。さらに、IF関数と組み合わせることで、経費が予算を超過している部門を自動的に特定することも可能です：

=IF(C2>VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$B$50,2,FALSE),"予算超過","予算内")

このような高度な使用法により、経理部門の業務効率が大幅に向上し、予算管理の精度も高まることが期待できます。

VLOOKUP関数使用時のヒントとコツ

VLOOKUP関数を異なるシートやファイルで使用する際、以下のヒントとコツを覚えておくと、より効率的にデータ管理を行うことができます。

1. 名前の定義を活用する

頻繁に参照するテーブル範囲には名前を定義しておくと、VLOOKUP関数の記述が簡潔になり、可読性が向上します。

例：

=VLOOKUP(A2,社員データ,2,FALSE)

ここで「社員データ」は事前に定義した名前です。この方法を使えば、長いファイルパスやシート名を毎回入力する必要がなくなります。

2. エラー処理を組み込む

VLOOKUP関数が適切な値を見つけられない場合、#N/A エラーが返されます。これを防ぐために、IFERROR関数を使用することをお勧めします。

例：

=IFERROR(VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$B$50,2,FALSE),"データなし")

この方法を使えば、エラーが発生した場合でも「データなし」などのメッセージを表示できます。

3. パフォーマンスの最適化

大量のデータを扱う場合、VLOOKUP関数の使用によりファイルのパフォーマンスが低下する可能性があります。以下の方法でパフォーマンスを改善できます：

  • 可能な限り、検索範囲を限定する
  • 大規模なデータセットでは、VLOOKUP関数の代わりにINDEX-MATCH関数の組み合わせを検討する
  • 頻繁に更新されないデータについては、値のみを貼り付けて関数を削除することを検討する

4. 大文字小文字の区別

VLOOKUP関数は基本的に大文字と小文字を区別しません。しかし、完全に一致させたい場合は、EXACT関数と組み合わせて使用することができます。

例：

=IF(EXACT(A2,VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$B$50,1,FALSE)),VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$B$50,2,FALSE),"不一致")

5. 動的な列番号の使用

VLOOKUP関数の列番号を動的に変更したい場合、MATCH関数と組み合わせることで実現できます。

例：

=VLOOKUP(A2,'[予算管理.xlsx]Sheet1'!$A$2:$D$50,MATCH(C1,'[予算管理.xlsx]Sheet1'!$A$1:$D$1,0),FALSE)

この方法を使用すると、C1セルの値に基づいて動的に列を選択することができます。

練習問題3：高度なVLOOKUP使用

問題：
あなたは販売部門で働いています。「商品マスター.xlsx」というファイルに商品コード、商品名、標準価格が記録されており、「販売実績.xlsx」というファイルに日々の販売データ（日付、商品コード、販売数量）が記録されています。以下の要件を満たす関数を作成してください：

  1. 「販売実績.xlsx」に商品名と標準価格を表示する
  2. 販売価格が標準価格を下回っている場合は “値引き” と表示する
  3. 商品コードが見つからない場合は “未登録商品” と表示する

回答：
「販売実績.xlsx」のC2セル（商品名用）に以下の関数を入力します：

=IFERROR(VLOOKUP(B2,'[商品マスター.xlsx]Sheet1'!$A$2:$C$1000,2,FALSE),"未登録商品")

D2セル（標準価格用）に以下の関数を入力します：

=IF(C2="未登録商品","",VLOOKUP(B2,'[商品マスター.xlsx]Sheet1'!$A$2:$C$1000,3,FALSE))

E2セル（値引き判定用）に以下の関数を入力します：

=IF(OR(C2="未登録商品",D2=""),"",IF(E2<D2,"値引き",""))

解説：
これらの関数は以下のように機能します：

  • 商品名の関数：IFERROR関数を使用して、VLOOKUP関数が#N/Aエラーを返した場合に “未登録商品” と表示します。
  • 標準価格の関数：IF関数を使用して、商品名が “未登録商品” の場合は空白を表示し、それ以外の場合はVLOOKUP関数で標準価格を取得します。
  • 値引き判定の関数：OR関数とIF関数を組み合わせて、未登録商品でない場合かつ販売価格が標準価格を下回る場合に “値引き” と表示します。

この高度な使用例では、複数のVLOOKUP関数と条件分岐を組み合わせることで、より複雑な業務要件に対応しています。これにより、販売データの分析や異常値の検出が容易になり、業務効率と精度が向上します。

VLOOKUP関数使用時によくある間違いと対策

VLOOKUP関数は非常に便利なツールですが、使用する際にいくつかの一般的な間違いがあります。これらの間違いを理解し、回避することで、より効果的にVLOOKUP関数を活用できます。

1. 検索範囲の指定ミス

間違い： テーブル範囲の指定が不適切で、必要なデータが含まれていない。

対策： テーブル範囲を指定する際は、将来的なデータ追加を考慮して、十分に広い範囲を設定しましょう。ただし、あまりに広すぎるとパフォーマンスに影響する可能性があるので注意が必要です。

2. 列番号の間違い

間違い： 返したい値が含まれる列の番号を間違えて指定している。

対策： 列番号は必ずテーブル範囲の左端を1として数えます。複雑なテーブルの場合は、一度テスト用のセルで確認してから本番の関数に組み込むとよいでしょう。

3. 検索方法（4番目の引数）の誤用

間違い： TRUEとFALSEの使い分けを間違える。

対策： 完全一致を求める場合は必ずFALSEを指定します。TRUEは近似値を返すため、意図しない結果になる可能性があります。基本的にはFALSEの使用をお勧めします。

4. 大文字小文字の区別が必要な場合の対応漏れ

間違い： 大文字小文字を区別すべきケースでVLOOKUP関数をそのまま使用している。

対策： 前述のEXACT関数との組み合わせや、VLOOKUP関数の前にUPPER関数やLOWER関数を使用して、大文字小文字を統一してから検索を行います。

5. 動的な参照の欠如

間違い： ハードコードされた範囲を使用しているため、データの追加や削除に対応できない。

対策： テーブル機能を使用するか、OFFSET関数やINDIRECT関数を組み合わせて動的な範囲を作成します。これにより、データの変更に柔軟に対応できます。

練習問題4：VLOOKUP関数の間違い修正

問題：
以下のVLOOKUP関数には複数の問題があります。これらの問題を特定し、修正してください。

=VLOOKUP(A2,Sheet2!A:C,3,TRUE)

回答：
修正後の関数：

=VLOOKUP(A2,Sheet2!$A$2:$C$1000,3,FALSE)

解説：
元の関数には以下の問題がありました：

  1. テーブル範囲が全列（A:C）を指定しており、パフォーマンスに影響を与える可能性があります。
  2. テーブル範囲が相対参照になっており、関数をコピーした際に問題が生じる可能性があります。
  3. 4番目の引数がTRUEになっており、近似値マッチを行ってしまいます。

修正後の関数では：

  1. テーブル範囲を適切な範囲（$A$2:$C$1000）に限定しています。
  2. 絶対参照（$記号）を使用して、関数をコピーしても範囲が変わらないようにしています。
  3. 4番目の引数をFALSEに変更し、完全一致検索を行うようにしています。

これらの修正により、関数の信頼性とパフォーマンスが向上します。

VLOOKUP関数の高度なテクニック

ここまでVLOOKUP関数の基本的な使用方法と注意点を見てきましたが、さらに高度なテクニックを習得することで、より複雑なデータ処理や分析が可能になります。

1. 複数条件でのVLOOKUP

通常、VLOOKUP関数は1つの条件（検索値）でのみ検索を行いますが、複数の条件で検索したい場合があります。これを実現するには、VLOOKUP関数とMATCH関数を組み合わせて使用します。

例：社員番号と部門コードから給与を検索する

=VLOOKUP(A2&B2,{給与テーブル!A:A&給与テーブル!B:B,給与テーブル!C:C},2,FALSE)

この関数では、検索値と検索テーブルの両方で、社員番号と部門コードを結合しています。これにより、2つの条件を満たすデータのみを取得できます。

2. VLOOKUP関数の結果を動的に変更

VLOOKUP関数の結果を、別のセルの値に基づいて動的に変更したい場合があります。これはCHOOSE関数とVLOOKUP関数を組み合わせることで実現できます。

例：ユーザーが選択した情報（給与、部門、入社日）を動的に表示する

=VLOOKUP(A2,社員データ,CHOOSE(D2,2,3,4),FALSE)

ここでD2セルには1、2、3のいずれかの値が入力されており、それぞれ給与、部門、入社日に対応しています。この方法により、ユーザーの選択に応じて異なる情報を動的に表示できます。

3. VLOOKUP関数で横方向に検索

通常、VLOOKUP関数は縦方向にのみ検索を行いますが、INDEX関数とMATCH関数を組み合わせることで、横方向の検索も可能になります。

例：月名から該当月の売上を検索する

=INDEX(売上データ!A2:M2,MATCH(A2,売上データ!A1:M1,0))

この関数では、MATCH関数で指定した月名の列番号を特定し、INDEX関数でその列の値を取得しています。これにより、縦方向だけでなく横方向のデータ検索も可能になります。

練習問題5：高度なVLOOKUPテクニック

問題：
あなたは人事部門で働いています。以下の3つのシートがあります：

  • 「社員マスター」：社員番号、氏名、部門コード
  • 「給与テーブル」：部門コード、職級、基本給
  • 「評価テーブル」：社員番号、評価点

社員番号を入力すると、その社員の氏名、基本給、評価点を表示する関数を作成してください。ただし、基本給は評価点に応じて以下のように変動します：

  • 評価点が80以上：基本給の110%
  • 評価点が60-79：基本給の100%
  • 評価点が60未満：基本給の90%

回答：
以下の関数を使用します（A2に社員番号が入力されていると仮定）：

氏名：
=VLOOKUP(A2,社員マスター!$A$2:$C$100,2,FALSE)

基本給：
=VLOOKUP(VLOOKUP(A2,社員マスター!$A$2:$C$100,3,FALSE)&VLOOKUP(A2,社員マスター!$A$2:$C$100,4,FALSE),給与テーブル!$A$2:$C$100,3,FALSE)

評価後の給与：
=VLOOKUP(A2,社員マスター!$A$2:$C$100,2,FALSE)&" "&
IF(VLOOKUP(A2,評価テーブル!$A$2:$B$100,2,FALSE)>=80,
    VLOOKUP(VLOOKUP(A2,社員マスター!$A$2:$C$100,3,FALSE)&VLOOKUP(A2,社員マスター!$A$2:$C$100,4,FALSE),給与テーブル!$A$2:$C$100,3,FALSE)*1.1,
    IF(VLOOKUP(A2,評価テーブル!$A$2:$B$100,2,FALSE)>=60,
        VLOOKUP(VLOOKUP(A2,社員マスター!$A$2:$C$100,3,FALSE)&VLOOKUP(A2,社員マスター!$A$2:$C$100,4,FALSE),給与テーブル!$A$2:$C$100,3,FALSE),
        VLOOKUP(VLOOKUP(A2,社員マスター!$A$2:$C$100,3,FALSE)&VLOOKUP(A2,社員マスター!$A$2:$C$100,4,FALSE),給与テーブル!$A$2:$C$100,3,FALSE)*0.9
    )
)

解説：

  1. 氏名の取得: 社員マスターから社員番号に対応する氏名を取得します。
  2. 基本給の取得: まず社員マスターから部門コードと職級を取得し、それを使って給与テーブルから基本給を取得します。
  3. 評価後の給与計算: 評価テーブルから評価点を取得し、条件に応じて基本給を調整します。

この例では、複数のVLOOKUP関数を組み合わせて使用し、さらにIF関数で条件分岐を行っています。これにより、複数のテーブルから必要な情報を取得し、条件に基づいた計算を一つの数式で実現しています。

VLOOKUP関数の代替手段

VLOOKUP関数は非常に強力で多用途ですが、状況によってはより適した代替手段があります。ここでは、VLOOKUP関数の代替となる方法とその利点について説明します。

1. INDEX-MATCH関数の組み合わせ

INDEX-MATCH関数の組み合わせは、VLOOKUP関数よりも柔軟性が高く、パフォーマンスも優れています。

例：

=INDEX(C2:C100,MATCH(A2,B2:B100,0))

利点：

  • 検索列が左端である必要がない
  • 列の挿入や削除に強い
  • 大規模なデータセットでより高速

2. XLOOKUP関数（Excel 2021以降）

XLOOKUP関数は、VLOOKUP関数の進化版と言えます。より直感的で柔軟性が高く、エラー処理も改善されています。

例：

=XLOOKUP(A2,B2:B100,C2:C100,,"完全一致")

利点：

  • 左右どちらの方向にも検索可能
  • 完全一致がデフォルト
  • エラー時の戻り値を指定可能
  • 逆順検索が可能

3. FILTER関数（Excel 2021以降）

FILTER関数は、条件に合う全ての行を返すことができます。複数の結果を返したい場合に特に有用です。

例：

=FILTER(C2:C100,B2:B100=A2)

利点：

  • 複数の結果を一度に返せる
  • 複数の条件を簡単に組み合わせられる
  • 動的な範囲を返せる

練習問題6：VLOOKUP代替手段

問題：
以下のVLOOKUP関数を、INDEX-MATCH関数の組み合わせとXLOOKUP関数に書き換えてください。

=VLOOKUP(A2,Sheet2!$B$2:$D$100,3,FALSE)

回答：

INDEX-MATCH版：

=INDEX(Sheet2!$D$2:$D$100,MATCH(A2,Sheet2!$B$2:$B$100,0))

XLOOKUP版：

=XLOOKUP(A2,Sheet2!$B$2:$B$100,Sheet2!$D$2:$D$100,,"完全一致")

解説：

  • INDEX-MATCH版では、MATCHで検索値の位置を特定し、INDEXで該当する値を返します。
  • XLOOKUP版では、検索値、検索範囲、返り値の範囲を直接指定します。
  • どちらの方法も、VLOOKUPよりも柔軟性が高く、列の挿入や削除に強いという利点があります。

これらの代替手段を習得することで、より効率的で柔軟なデータ検索が可能になります。状況に応じて適切な関数を選択することが、Excel作業の効率化につながります。

まとめ：VLOOKUP関数を使いこなし、業務効率を向上させよう

本記事では、Excel VLOOKUP関数の高度な使用方法、特に異なるシートやファイルからのデータ参照技術について詳しく解説しました。以下に、主要なポイントをまとめます：

  • VLOOKUP関数は、異なるシートやファイルからデータを参照する強力なツールです。
  • シート間でのデータ参照には、シート名と感嘆符（!）を使用します。
  • ファイル間でのデータ参照には、ファイル名を角括弧で囲み、シート名と組み合わせて使用します。
  • 絶対参照（$記号）を適切に使用することで、関数のコピーや移動時のエラーを防げます。
  • エラー処理、大文字小文字の区別、動的な列指定など、高度なテクニックを活用することで、より柔軟で強力なデータ管理が可能になります。
  • INDEX-MATCH関数の組み合わせやXLOOKUP関数など、状況に応じた代替手段を知ることで、さらに効率的なデータ処理が可能になります。

VLOOKUP関数をマスターすることで、以下のような業務改善が期待できます：

  • データ入力や集計作業の大幅な時間短縮
  • 人為的ミスの削減
  • 大規模データの効率的な管理と分析
  • 部門間やファイル間でのデータ連携の簡素化
  • 動的なレポート作成の自動化

本記事で学んだテクニックを実際の業務に適用し、練習問題を通じて理解を深めることで、Excelスキルを向上させ、業務効率を大幅に改善することができるでしょう。

最後に、VLOOKUP関数は非常に強力なツールですが、これはExcelの機能の一部に過ぎません。より高度なデータ分析や業務効率化を目指すなら、Power QueryやPower Pivotなどの高度なExcel機能の習得もおすすめです。

Excel学習の次のステップとして、ユースフルのExcelPro講座パワーコースがおすすめです。このコースでは、VLOOKUP関数を含む高度なExcel機能を実践的に学ぶことができ、ビジネスにおけるデータ分析スキルを大幅に向上させることができます。

ExcelPro講座パワーコースの無料トライアルに申し込む

皆様のExcel技能向上と業務効率化の一助となれば幸いです。さらなる疑問や具体的な使用例については、ぜひコメント欄でお聞かせください。

Excel
気に入ったらシェア！
  • URLをコピーしました！
目次