VLOOKUP関数で別ファイルを参照しデータを引っ張る方法

ここでは、エクセルのVLOOKUP関数で「別ファイル(ブック)を参照しデータを引っ張る方法」を解説します。

 

別のブックをVLOOKUP関数上で指定する時のポイントの他に、別ファイル参照がうまくいかない時の対処法も説明します。

1.まずはやりたいことを確認

まずは、やりたいことを例を含めて確認します。

 

1-1.VLOOKUP関数で別ファイルからデータを引っ張りたい

やりたいことは、「VLOOKUP関数で別のファイルからデータを引っ張る」です。

 

例えば、月の売上表と年報が違うエクセルファイルの場合、VLOOKUP関数である店舗のみの売り上げを抜き出す、などといったことです。

 

ポイントは、「どうやってVLOOKUP関数で別ファイルを参照させればいいか?」、「別ファイル参照をすることでエラーになるケースはあるのか?」です。

 

1-2.例で確認

一つ例を見てみましょう。

Book1のSheet1に「№の振られた都道府県名が入った表」があります。

 

Book2のSheet2には、検索№を入力するセルと抽出された都道府県名を表示させるセルがあります。

 

このように、「Book1のSheet1」から「Book2のSheet2」にVLOOKUP関数で抽出する時にどのようにすればいいか?といったことを説明します。

2.別ファイルからデータを引っ張る方法

では、「VLOOKUP関数で別ファイルからデータを引っ張る方法」を見てみましょう。

 

先ほどの例を使いながら説明します。

 

2-1.数式の形は

別ファイルからデータを引っ張るとき、数式の形は次のようになります。

=VLOOKUP(検索値,[ブック名.xlsx]シート名!範囲,列番号,検索方法)

 

ポイントは、「『範囲』にブック名とシート名を付ける」ことです。検索値や列番号は、基本的に別ファイルを指定することはありません。使いづらくなるので。

 

なお、ブック名とシート名は1字でも間違うとエラーになります。そこで、簡単にブック名とシート名を入れることのできる方法を次で見てみます。

 

2-2.数式の作り方

以下手順です。

 

(1)セルに「=VLOOKUP(」と入力し、fxボタンを押す

 

(2)検索値、列番号、検索方法は普段通りに入力する

 

(3)引数「範囲」をクリックした後、Book1を選択し、Sheet1をクリックする

 ※ ここがポイントです

 

(4)対象の範囲をマウスの左ドラッグで選択する

 

(5)OKボタンを押せば完了

このようにすることで、ブック名とシート名が正しく指定できます。

 

ちなみに、数式は次のようになります。

=VLOOKUP(A2,[Book1.xlsx]Sheet1!A2:B7,2,FALSE)

 

2-3.ポイントのまとめ

VLOOKUP関数で別ファイルから値を抜き出すときのポイントをまとめると、次の通りです。

ポイント
  • ① 別ファイル参照の指定をする引数は基本的に「範囲」
  • ② 範囲には「[ファイル名]シート名!範囲」と入力する
  • ③ ファイル名とシート名を自動入力できる方法がある

 

まず別ファイル参照の時、普段と違うのは「『範囲』の指定の仕方」です。他の引数は、普段と同じです。

 

次に、範囲には「[ファイル名]シート名!範囲」と入力します。

 

先ほど作ってみた数式を見ると、範囲に「[Book1.xlsx]Sheet1!」とあるのが分かりますが、VLOOKUP関数に限らず、エクセルの数式で別のファイルを参照する時の書き方がこれです。

 

そしてこれは手入力の必要がなく、手順の(3)のように「参照したいファイルをクリック→シートをクリック」で、自動入力されます。

 

これらのポイントを押さえることで、VLOOKUP関数で別ファイルから値を抜き出すことができます。

3.別ファイル参照できない場合の対処法

大前提として、VLOOKUP関数は引数で別ファイルを参照してもエラーにはなりません。参照先のファイルが閉じていても大丈夫です。

しかし、実際に使ってみると別ファイルを参照できないことがあります。

 

その場合には、大きく2つの原因が考えられます。

2つの原因
  • ① VLOOKUP関数の引数の指定の仕方を間違っている
  • ② 参照先のファイルからデータをとれない

 

3-1.引数の指定の仕方を間違っている

一つ目は、単純にVLOOKUP関数の引数の指定の仕方をどこか間違えているケースです。

 

この場合は、数式を作っている時からエラーになるので分かりやすいです。

 

もう一度、VLOOKUP関数の使い方や基本などを確認しましょう。

⇒ VLOOKUP関数の使い方・基本はこちら

 

3-2.参照先のファイルからデータをとれない

二つ目は、数式作成時に正しい結果が返ってきたのに後でファイルを開いたらエラーになっているケースです。

 

この場合の問題点は、数式の作り方ではありません。

 

3-2-1.まずは基本的なところから確認する

まずは、次のことを確認しましょう。

確認すること
  • ファイル名を変えていないか?
  • ファイルの保存場所を変えていないか?
  • シート名を変えていないか?
  • ファイルのアクセス権限を変更していないか?
  • そもそも参照先のファイルは開けるか?(壊れていないか?)

 

数式は、別ファイル参照だとあるタイミングを境に「パス」と呼ばれる「ファイルの置き場所を示したもの」が自動で付加されます。

 

また、パスが付加された後でもファイル名や保存場所が変わった場合には、ある程度自動で修正してくれます。

 

しかし一方で、自動で修正できないケースもあります。

 

そのため、特にファイル名や保存場所、シート名を変更してからエラーになった場合には、再度数式を作り直した方が確実です。

 

3-2-2.パスが「IPアドレス」になっていないか?

いろいろと試してみたところ、「パスがIPアドレスになっていると#REF!エラーになる」ことが分かりました。

 

このことについては、次のページで詳しく解説していますので、参考にしてください。

⇒ エクセルで別シート参照時に#REF!や#N/Aエラーの原因は

 

以上、参考になれば幸いです。

 

次のページでは「VLOOKUP関数横コピー時の列番号変更は、可変にすると秒殺で簡単」を解説します。

⇒ VLOOKUP関数横コピー時の列番号変更は、可変にすると秒殺で簡単


口コミ・評判のいいExcel本の紹介】


Top