今回の記事では、VLOOKUP関数を使う際の参照範囲に別ファイルにあるデータを指定する方法を解説したいと思います。
複数人などでマスタデータを管理している場合などに、そのマスタデータからVLOOKUP関数でデータを抜きだすことで転記ミスなども減り、常に最新のデータを参照できるというメリットもあります。
しかし、参照先のファイル名やファイルの場所を変えると参照できなくなってしまうというデメリットもあります。
VLOOKUP関数で別ファイルからマスタデータを参照するには、=VLOOKUP(検索値,範囲,列番号,[検索条件])のうち2番目の引数の”範囲”を指定する際に別ファイルの範囲を指定することで簡単に指定することができます。表示は[ブック名]シート名!セル範囲になります。
この記事では別ファイルからデータを参照する方法とデメリットについても解説したいと思います。
↓VLOOKUP関数の詳しい使い方はこちら↓
最強の便利関数？VLOOKUP関数の使い方とは
↓別シートからデータを参照する方法はこちら↓
VLOOKUP関数で別シートからデータを参照する方法とは？
↓外部リンクのエラーの対処法はこちら↓
外部リンクのエラーの検索方法と3つの対処方法とは？
1．VLOOKUP関数で別ファイルを範囲に設定する方法
ここからは実際にVLOOKUP関数の参照範囲を別ファイルに指定する方法を解説したいと思います。
VLOOKUP関数の計算式は、
=VLOOKUP(検索値,範囲,列番号,FALSE)
でした。
計算式の中の2つ目の引数の範囲を指定する際に、別ファイルの範囲を指定することで簡単に別ファイルのデータを参照範囲に指定することができます。
1-1．VLOOKUP関数で別ファイルのデータを範囲に指定する方法
それでは実際に計算式を入れる左のファイルと赤枠の「商品マスタ」という別ファイルを用意して解説します。
今回は試しに”A03”の商品コードから対応する商品名を検索してみたいと思います。
VLOOKUP関数の1つ目の引数の検索値は通常と同じく“=VLOOKUP(“A03”,と入力します。
2つ目の引数の範囲指定時にマスタデータがある商品マスタのファイル（赤枠）を選択し、そのままマスタデータをマウスでドラッグし範囲を設定（青枠）します。
すると、範囲の引数の部分に“=VLOOKUP(“A03”,[商品マスタ.xlsx]Sheet1!$A$3:$C$7,“と[]で囲まれたファイル名とシート名と!（ビックリマーク）と絶対参照したセルの範囲が表示されました。
この表示のうち、
[]で囲まれた部分が参照先のファイル名
!までの部分がシート名
それより先の部分がセルの参照範囲
となっています。
↓絶対参照について詳しくはこちら↓
$（ドルマーク）の意味は？参照する列や行を固定する絶対参照とは？
残りの3つ目と4つ目の引数の列番号と検索方法は通常のVLOOKUP関数と同じように入力をします。
今回の場合は、商品名は指定した範囲の2列目にデータがあるので列番号は”2”を、検索方法は通常通り”FALSE”を指定します。
出来上がった計算式は、
=VLOOKUP(“A03”,[商品マスタ.xlsx]Sheet1!$A$3:$C$7,2,FALSE)
となります。
2．別ファイルを範囲に指定するメリットやデメリット
ここまでVLOOKUP関数で別のファイルからマスタデータを参照する方法を解説してきましたが、結論としては「できれば別ファイルからの参照はしない方がいい」です。
ここでは別ファイルからデータを参照するメリットやデメリットについて解説します。
2-1．別ファイルを範囲に指定するメリット
最初はメリットを解説します。
2-1-1．複数人での作業に向いている
複数人で顧客データなどのマスタデータを常に更新している場合は、複数人が更新をするファイルと別ファイルを使用することでマスタデータを壊してしまうリスクを減らすことができます。
また、マスタデータを更新した場合にすぐに最新のデータに更新されるというメリットもあります。
2-2．別ファイルを範囲に指定するデメリット
次にデメリットを解説します。
2-2-1．ファイル名を変更した場合に更新できなくなる
参照先のファイル名を変更したり、ファイルの場所を移動させた場合にデータが参照できなくなります。
VLOOKUP関数においては、参照できなくなってもすぐにエラー表示になることはありませんが、マスタデータ側が更新されても最新のデータに更新されなくなるので注意が必要です。
また、参照不能になった状態で計算式を変更するとエラーになってしまいます。
↓外部リンクのエラーの対処法はこちら↓
外部リンクのエラーの検索方法と3つの対処方法とは？
2-2-2．計算式が煩雑になる
参照先のファイルを同時に開いていないとファイルの参照先が自動的にディレクトリを含んだものに更新されます。
C:\Users\file\vlookup\[商品マスタ.xlsx]のように更にわかりづらい計算式になってしまいます。
計算式が長くなると修正したいときなどに修正場所が分かりづらくなり、非効率になってしまいます。
2-3．別ファイルからの参照はするべき？
別ファイルから範囲を参照するメリットとデメリットについて触れてきましたが、メリットよりデメリットの方が多いので特別な理由がないなら同一ファイルにマスタデータをコピペして使用するのが無難といえます。
その際にマスタデータを計算式を入れるシートと別シートにコピペすることで管理がしやすくなります。
↓シートを別ファイルに移動させる方法はこちら↓
エクセルでシートを別ファイルに移動させる3つの方法とは？
↓別シートを範囲に指定する詳しい方法はこちら↓
VLOOKUP関数で別シートからデータを参照する方法とは？
今回の記事ではVLOOKUP関数で別のファイルからデータを参照する方法を解説しました。
別のシートからデータを参照するように簡単に設定はできますが、参照先のファイルの場所を変更するとデータの更新ができなくなるなどのデメリットが目立ちます。
できるだけ別シートを参照先に指定せずに同じファイルにマスタデータをコピペするなどシンプルな計算式を使うようにすることでミスを減らすことに繋がります。
最後までお読みいただきありがとうございました。
