ExcelのVLOOKUP関数で別シートを参照する方法
- Release
- Update
VLOOKUP関数で表を作成する際に、2つのテーブルを別のシートにそれぞれ分けると管理しやすくなります。
シート1のVLOOKUP関数の範囲を、シート2から参照する方法をお伝えします。
また最後に、#N/Aエラーが出てしまう場合の対処法をお伝えいたします。
VLOOKUP関数の別シート参照の使い方
![別シートにする表の確認](../../wp-content/uploads/vlookup-another-sheet1.png)
今回は、価格テーブルの表を別のシートに移行し、受注シートの「種類」「商品名」「単価」の項目を別シートにある価格テーブルから参照する方法をお伝えします。
別シートの準備
![シートを追加](../../wp-content/uploads/vlookup-another-sheet2.png)
「価格テーブル」の別シートを作成します。赤枠の【+】を押します。
![シート名の変更](../../wp-content/uploads/vlookup-another-sheet3.png)
シート名を『価格テーブル』に変更しましょう。
![表の切り取り](../../wp-content/uploads/vlookup-another-sheet4.png)
「受注シート」のシートへ戻って、価格テーブルを切り取りしましょう。【範囲を選択】して、Ctrl+Xで切り取れます。
![表の貼り付け](../../wp-content/uploads/vlookup-another-sheet5.png)
「価格テーブル」のシートへ行き、貼り付けたいセル上で、Ctrl+Vで貼り付けれます。これで準備が完了しました。
仕組みの説明
![受注シートの説明](../../wp-content/uploads/vlookup-another-sheet6.png)
「受注シート」入力説明をします。項目の「受注日」「商品コード」「個数」は手入力をし、「金額」は単価と個数を掛け算して自動算出しております。
![商品コードの入力](../../wp-content/uploads/vlookup-another-sheet7.png)
「受注シート」の「商品コード」を手入力した時点で、、
![価格テーブルの参照](../../wp-content/uploads/vlookup-another-sheet8.png)
別シートの「価格テーブル」の「種類」「商品名」「単価」をVLOOKUP関数で引っ張ってきます。
別シートを参照する
![VLOOKUP関数のクリア](../../wp-content/uploads/vlookup-another-sheet9.png)
それではVLOOKUP関数で別シートを参照するため、一度赤枠内をクリアします。
![検索値の入力](../../wp-content/uploads/vlookup-another-sheet10.png)
『=VLOOKUP(』ではじめ、検索値のセルを参照します。すぐ左のセルの「商品コード」を検索値とするので「C4セル」を指定してカンマで区切ります。
![別シートで範囲指定](../../wp-content/uploads/vlookup-another-sheet11.png)
「価格テーブル」のシートへ移動し、検索の範囲を指定します。今回は、B列からE列の全てのデータを対象としたいため、赤枠の箇所を【BからEまでドラッグ】し、カンマで区切ります。。こうすることにより、新しく商品コードが1007以降追加された場合、自動で検索範囲としてくれますので便利です。
![列番号の指定](../../wp-content/uploads/vlookup-another-sheet12.png)
「種類」の項目を取得したいので、左から数えて2番目に「種類」の列が存在しているので、『2』と入力し、カンマで区切ります。
![FALSEの指定](../../wp-content/uploads/vlookup-another-sheet13.png)
完全一致検索にするので『FALSE』と入力します。
![別シートの参照結果](../../wp-content/uploads/vlookup-another-sheet14.png)
【Enter】を押すと、受注シートのD4セルに商品コード1001の「種類」項目が「果物」であると正しく参照されました。
![セルの固定](../../wp-content/uploads/vlookup-another-sheet15.png)
それでは他のセルへ反映していきます。反映するとセルの参照がずれてしまうので、適切な箇所を「$」で固定していきます。
商品コードと範囲を赤枠の箇所で固定します。
![フィルハンドルで反映](../../wp-content/uploads/vlookup-another-sheet16.png)
赤枠の【フィルハンドルをクリック】したまま、「単価」のセルまで【ドラッグ】します。
![列番号が同じ](../../wp-content/uploads/vlookup-another-sheet17.png)
正しい値が反映されたかと思ったら、全て同じ値が抽出されました。これは列番号がすべて「2」のため、すべて「種類」の列を参照しているためです。
こちらの解消方法は以下のページにて書かれております。
![正しい列番号の指定](../../wp-content/uploads/vlookup-another-sheet18.png)
「商品名」と「単価」の列番号を変更します。すると正しい値が抽出されました。
![フィルハンドルで反映](../../wp-content/uploads/vlookup-another-sheet19.png)
【D4~F4のセルを範囲指定】し、フィルハンドルを9行目まで【ドラッグ】します。
![反映結果](../../wp-content/uploads/vlookup-another-sheet20.png)
全ての値が反映されました。これで完成です。
このページを見ている人におすすめの商品
参照できない場合
別シートが参照できない事例をご紹介します。
#N/Aエラーが出てしまう
![範囲を固定](../../wp-content/uploads/vlookup-another-sheet21.png)
n/aエラーが出る理由としては、別シートの範囲の参照を赤枠のように固定にしてしまっているケースです。
![範囲を固定を見る](../../wp-content/uploads/vlookup-another-sheet22.png)
別シートの価格表を見ると、赤枠の範囲を固定で参照しております。
![価格テーブルに追加](../../wp-content/uploads/vlookup-another-sheet23.png)
今回新たに「価格テーブル」に「トマト」を追加しました。
![受注シートのエラー確認](../../wp-content/uploads/vlookup-another-sheet24.png)
そして「トマト」の受注が入ったので「受注シート」にトマトを追加しましたが、#N/Aエラーが出てしまいました。
![検索範囲の確認](../../wp-content/uploads/vlookup-another-sheet25.png)
「価格テーブル」の検索範囲は固定にしていたので、検索範囲から外れているため#N/Aエラーになります。
これを解消するためには上部で説明したとおり、検索範囲を列ごと指定しましょう!
その他、様々なVLOOKUP関数の使い方
XLOOKUP関数も合わせてチェック!
VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。
他の関数も合わせてチェック!
Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。
この記事はお役に立ちましたか?
- はい
- いいえ