VLOOKUP関数で別シートから参照コピーする方法
Excelではデータ量が多すぎても大丈夫なように、1ファイルで複数シート作成できるようなっています。
そして、今現在見ているシートとは別のシートの情報をVLOOKUP関数で参照コピーすることができ、閲覧等に不必要な情報を除外した閲覧専用シートと全ての情報がまとまったシートに分けて管理することができます。
VLOOKUP関数を使って別シートのセル情報を引用する方法はとても簡単なので紹介していきます。
VLOOKUP関数の参照範囲には「A1:B2、A1:E100」のようにセル情報を与えますが、ここにシート情報を含めることもできます。
シート情報を含める場合は以下のように、シート名とセル範囲を「!」で区切ります。
これだけで現在参照しているシートとは別のシートの情報を抽出でき、参照結果が反映されるようになります
別シートの情報を参照するVLOOKUP関数が完成したら、セル内の数式をコピーして好きなところに貼り付けてください。
この際セルのコピーだと、VLOOKUP関数の参照範囲を相対参照にしていると参照範囲が変わってしまいます。
失敗を避けたいのであれば、選択中セルの中身が表示されるテキストボックスの中身をコピーすると良いでしょう。
あくまで参照しているだけなので、参照しているセルを編集することはできず、必ず参照元のシートで編集する必要があるので覚えておきましょう。
慣れるまでは「一度参照元シートでVLOOKUP関数を記述し、最後に別シートに数式を貼り付ける」という方法がおすすめです。
「#N/A」と表示されてしまう原因とは？
別シートのセルをVLOOKUP関数で参照しようとすると、なぜか「#N/A」と表示されてしまうことがあります。
「#N/A」になってしまう原因は複数のあるのでそれぞれ解説していきます。
セルのコピーをしてしまっている
エクセルでは、数式が入ったセルをコピーして貼り付けると、自動的に指定しているセル番号も自動的に変換してくれます。
ですが、その影響の副作用でVLOOKUP関数の参照範囲と検索値のセルが変わってしまい、検索がうまく働かないことがあります。
実用するにあたって検索値は変わっても問題ないケースが多いですが、参照範囲が変わるのは困るという場合が多いと思います。
このように$を付けて絶対参照にするとセルのコピーを行っても参照範囲が変わらず、エラーを未然に防げます。
「#REF」になることも
これもVLOOKUP関数の参照範囲などが間違っているケースで、セルの中身を確認するとどこかに「#REF」の記述が見つかります。
手入力で直す場合は#REFとなっている箇所を修正して動作を確認するようにしましょう。
参照範囲・検索値のシート情報の入力が漏れている
参照範囲・検索値の両方とも別シートの情報を参照することもよくあります。
その際は必ず両方ともシート情報を指定する必要があります。
こちらの関数は、sample1シートのA1:B2範囲を参照していますが、検索値にはシートの指定がありません。
つまり「VLOOKUP関数を記述したシートのC4セルの値でsample1シートのA1:B2範囲を検索する」という処理になり、意図した動作にならないことがあります。
ですので、VLOOKUP関数が#N/Aを返す場合は両方ともシート情報を与えているか確認するようにしてください。
そもそも検索結果が0件だった
VLOOKUP関数は、検索値にヒットする行が0件だった場合、「#N/A」エラーを返すようになっています。
そのため、検索結果が0件だと別シートの参照の有無に関わらずエラーが発生するので、どの方法を試してもエラーが直らない・うまくいかないという場合は検索値や参照範囲を見直すようにしましょう。
別ファイルのシートは参照できない
VLOOKUP関数は別シートのセルを参照することはできても、別ファイルのセル情報までは参照できません。
別ファイルの参照はどうあがいてもできないので注意してください。