Office Hack

ExcelのVLOOKUP関数で別シートを参照する方法

  • by kenji
  • Release
  • Update

VLOOKUP関数で表を作成する際に、2つのテーブルを別のシートにそれぞれ分けると管理しやすくなります。

シート1のVLOOKUP関数の範囲を、シート2から参照する方法をお伝えします。

また最後に、#N/Aエラーが出てしまう場合の対処法をお伝えいたします。

VLOOKUP関数の別シート参照の使い方

別シートにする表の確認

今回は、価格テーブルの表を別のシートに移行し、受注シートの「種類」「商品名」「単価」の項目を別シートにある価格テーブルから参照する方法をお伝えします。

別シートの準備

シートを追加

「価格テーブル」の別シートを作成します。赤枠の【+】を押します。

シート名の変更

シート名を『価格テーブル』に変更しましょう。

表の切り取り

「受注シート」のシートへ戻って、価格テーブルを切り取りしましょう。【範囲を選択】して、Ctrl+Xで切り取れます。

表の貼り付け

「価格テーブル」のシートへ行き、貼り付けたいセル上で、Ctrl+Vで貼り付けれます。これで準備が完了しました。

仕組みの説明

受注シートの説明

「受注シート」入力説明をします。項目の「受注日」「商品コード」「個数」は手入力をし、「金額」は単価と個数を掛け算して自動算出しております。

商品コードの入力

「受注シート」の「商品コード」を手入力した時点で、、

価格テーブルの参照

別シートの「価格テーブル」の「種類」「商品名」「単価」をVLOOKUP関数で引っ張ってきます。

別シートを参照する

VLOOKUP関数のクリア

それではVLOOKUP関数で別シートを参照するため、一度赤枠内をクリアします。

検索値の入力

『=VLOOKUP(』ではじめ、検索値のセルを参照します。すぐ左のセルの「商品コード」を検索値とするので「C4セル」を指定してカンマで区切ります。

別シートで範囲指定

「価格テーブル」のシートへ移動し、検索の範囲を指定します。今回は、B列からE列の全てのデータを対象としたいため、赤枠の箇所を【BからEまでドラッグ】し、カンマで区切ります。。こうすることにより、新しく商品コードが1007以降追加された場合、自動で検索範囲としてくれますので便利です。

列番号の指定

「種類」の項目を取得したいので、左から数えて2番目に「種類」の列が存在しているので、『2』と入力し、カンマで区切ります。

FALSEの指定

完全一致検索にするので『FALSE』と入力します。

別シートの参照結果

【Enter】を押すと、受注シートのD4セルに商品コード1001の「種類」項目が「果物」であると正しく参照されました。

セルの固定

それでは他のセルへ反映していきます。反映するとセルの参照がずれてしまうので、適切な箇所を「$」で固定していきます。

商品コードと範囲を赤枠の箇所で固定します。

フィルハンドルで反映

赤枠の【フィルハンドルをクリック】したまま、「単価」のセルまで【ドラッグ】します。

列番号が同じ

正しい値が反映されたかと思ったら、全て同じ値が抽出されました。これは列番号がすべて「2」のため、すべて「種類」の列を参照しているためです。

こちらの解消方法は以下のページにて書かれております。

正しい列番号の指定

「商品名」と「単価」の列番号を変更します。すると正しい値が抽出されました。

フィルハンドルで反映

【D4~F4のセルを範囲指定】し、フィルハンドルを9行目まで【ドラッグ】します。

反映結果

全ての値が反映されました。これで完成です。

参照できない場合

別シートが参照できない事例をご紹介します。

#N/Aエラーが出てしまう

範囲を固定

n/aエラーが出る理由としては、別シートの範囲の参照を赤枠のように固定にしてしまっているケースです。

範囲を固定を見る

別シートの価格表を見ると、赤枠の範囲を固定で参照しております。

価格テーブルに追加

今回新たに「価格テーブル」に「トマト」を追加しました。

受注シートのエラー確認

そして「トマト」の受注が入ったので「受注シート」にトマトを追加しましたが、#N/Aエラーが出てしまいました。

検索範囲の確認

「価格テーブル」の検索範囲は固定にしていたので、検索範囲から外れているため#N/Aエラーになります。

これを解消するためには上部で説明したとおり、検索範囲を列ごと指定しましょう!

VLOOKUP関数専門の本をご紹介

VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?実際の操作画面を示しながら、徹底的にわかりやすく解説したフルカラー入門書です。

VLOOKUP関数のツボとコツがゼッタイにわかる本

立山秀利(著)
206ページ

その他、様々なVLOOKUP関数の使い方










XLOOKUP関数も合わせてチェック!

VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。