VLOOKUP関数で別シートから参照コピーする方法!作業を効率化しよう

VLOOKUP関数で別シートから参照コピーする方法!作業を効率化しよう

VLOOKUP関数では別シートの情報も参照できるということを知っていますか?同一シートだけでなく、別シートの範囲セルからデータを抽出することができる大変便利な関数です。今回はそんなVLOOKUP関数を使って別シートから参照コピーする方法を紹介します。


目次[非表示]

  1. VLOOKUP関数で別シートから参照コピーする方法
  2. 「#N/A」と表示されてしまう原因とは?

VLOOKUP関数で別シートから参照コピーする方法

Excelではデータ量が多すぎても大丈夫なように、1ファイルで複数シート作成できるようなっています。

そして、今現在見ているシートとは別のシートの情報をVLOOKUP関数で参照コピーすることができ、閲覧等に不必要な情報を除外した閲覧専用シートと全ての情報がまとまったシートに分けて管理することができます。

VLOOKUP関数を使って別シートのセル情報を引用する方法はとても簡単なので紹介していきます。

複数のシート
実践する場合はあらかじめシートを2枚以上用意しておいてください。
VLOOKUP関数の記述
シートを用意したら参照したいシートを開き、一旦VLOOKUP関数を記述します。

VLOOKUP関数の参照範囲には「A1:B2、A1:E100」のようにセル情報を与えますが、ここにシート情報を含めることもできます。

シート情報を含める場合は以下のように、シート名とセル範囲を「!」で区切ります。

シート情報の記述
実際に記述するとこのようになります。

これだけで現在参照しているシートとは別のシートの情報を抽出でき、参照結果が反映されるようになります

別シートの情報を参照するVLOOKUP関数が完成したら、セル内の数式をコピーして好きなところに貼り付けてください。

この際セルのコピーだと、VLOOKUP関数の参照範囲を相対参照にしていると参照範囲が変わってしまいます。

セルのコピー、ペースト
必ずセル内テキストを編集できる状態でコピー、貼り付ける際もセルの編集画面に入ってから貼り付けるようにしてください。

失敗を避けたいのであれば、選択中セルの中身が表示されるテキストボックスの中身をコピーすると良いでしょう。

別シートのセル情報の参照コピー
これで無事別シートのセル情報を参照コピーすることができました。

あくまで参照しているだけなので、参照しているセルを編集することはできず、必ず参照元のシートで編集する必要があるので覚えておきましょう。

慣れるまでは「一度参照元シートでVLOOKUP関数を記述し、最後に別シートに数式を貼り付ける」という方法がおすすめです。

「#N/A」と表示されてしまう原因とは?

別シートのセルをVLOOKUP関数で参照しようとすると、なぜか「#N/A」と表示されてしまうことがあります。

「#N/A」になってしまう原因は複数のあるのでそれぞれ解説していきます。

セルのコピーをしてしまっている

エクセルでは、数式が入ったセルをコピーして貼り付けると、自動的に指定しているセル番号も自動的に変換してくれます。

ですが、その影響の副作用でVLOOKUP関数の参照範囲と検索値のセルが変わってしまい、検索がうまく働かないことがあります。

実用するにあたって検索値は変わっても問題ないケースが多いですが、参照範囲が変わるのは困るという場合が多いと思います。

セルの列・行番号の前に「$」を付ける
そういう場合はセルの列・行番号の前に「$」を付けて絶対参照にしてください。

このように$を付けて絶対参照にするとセルのコピーを行っても参照範囲が変わらず、エラーを未然に防げます。

「#REF」になることも

#REFの表示画面
範囲が変わってしまうコピーをしてしまった場合、「#N/A」ではなく「#REF」が表示されることもあります。

これもVLOOKUP関数の参照範囲などが間違っているケースで、セルの中身を確認するとどこかに「#REF」の記述が見つかります。

手入力で直す場合は#REFとなっている箇所を修正して動作を確認するようにしましょう。

参照範囲・検索値のシート情報の入力が漏れている

参照範囲・検索値の両方とも別シートの情報を参照することもよくあります。

その際は必ず両方ともシート情報を指定する必要があります。

検索値にシートの指定がない関数
こちらの関数をみてください。

こちらの関数は、sample1シートのA1:B2範囲を参照していますが、検索値にはシートの指定がありません。

つまり「VLOOKUP関数を記述したシートのC4セルの値でsample1シートのA1:B2範囲を検索する」という処理になり、意図した動作にならないことがあります。

ですので、VLOOKUP関数が#N/Aを返す場合は両方ともシート情報を与えているか確認するようにしてください。

そもそも検索結果が0件だった

VLOOKUP関数は、検索値にヒットする行が0件だった場合、「#N/A」エラーを返すようになっています。

そのため、検索結果が0件だと別シートの参照の有無に関わらずエラーが発生するので、どの方法を試してもエラーが直らない・うまくいかないという場合は検索値や参照範囲を見直すようにしましょう。

別ファイルのシートは参照できない

VLOOKUP関数は別シートのセルを参照することはできても、別ファイルのセル情報までは参照できません。

別ファイルの参照はどうあがいてもできないので注意してください。


関連記事