ExcelのVLOOKUP関数で別ブックを参照する

VLOOKUP関数は別ブックや別シートの表データを参照、検索することが可能です。

特にブックが複数あり一つのブックにVLOOKUP関数で集約させたい場合は有効ですね。

けれど手書きでブックのパスを指定することうまく反映されずに四苦八苦することが多いのではないでしょうか。

そこで今回は前回の3ステップでVLOOKUP関数を設定する方法をもとに

別ブックや別シートを参照する方法をご紹介したいと思います。

【Excelテクニック】カンタン!3ステップでVLOOKUP関数を設定



まずは前回のおさらい “エク短”的 カンタンVLOOKUP関数設定について

①VLOOKUP関数の設定はウイザード経由!
まず、VLOOKUP関数を設定する場合はウイザード経由で指定するようにします。なぜかというと手書きの場合は指定ミスが多いからです。

②「検索値」を指定
続いて「検索値」は値が入っているセルを指定します。こちらは通常通りですね。

③「範囲」は列ごと指定する!
「範囲」は列ごと、例えばA:Cのように指定します。

④「列番号」はまずは2で指定してみる!
「列番号」は数値で指定します。今回は1つ先のセルですので3を指定します。

⑤「検索方法」は”FALSE”一択!
ここは”FALSE”のみにしましょう。


VLOOKUP関数で別ブックのデータを参照する

サンプルの表は在庫管理表と商品マスタがあり、在庫管理表と商品マスタはそれぞれ別のブックになります。

今回は在庫管理表の単価の値を別ブックの商品マスタから参照し、合計値を出していきたいと思います。

①C3を選択し「fx」をクリックします。

②ウイザードが表示されたらVLOOKUPを指定します。

③値を挿入します。
・「検索値」は3行目のB3を指定。
・「範囲」は商品マスタがあるブックの列を選択。

・「列番号」は3を指定。
・「検索方法」は”FALSE”。

最後に「OK」をクリックします。

④うまく表示されるか確認します。

はい、商品マスタブックの「商品A」に該当する「単価」が参照されていますね。

⑤数式をコピーしましょう。
次にセルC3を他の単価セルにドラッグ&ドロップして数式をコピーします。

はい、完成です!



補足:参照先のブックを閉じた場合の数式の表示について

参照先のブック(今回は商品マスタブック)を閉じた場合と表示させた場合の数式の表示形式が異なります。

・「商品マスタ」ブックを表示させている場合
パスが省略表記となります。

なお、「商品マスタ」ブックの表データの更新があればリアルタイムで参照元ブックの表にも反映されます。

・「商品マスタ」ブックを表示させていない場合
はい、パスの部分がドライブから始まるフルパス表記に代わっていますね。

こちらは「商品マスタ」ブックの更新があった場合は手動による更新が必要となります。

手動更新の方法は参照元ブックを一度閉じて、起動し、以下のメッセージ
「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。
リンクを信頼できる場合、リンクを更新し最新データを取り込みます。信頼できない場合は、データをそのまま手元で処理してかまいません。」

がでたら「更新する」をクリックします。

もしくは、「データ」タブの「リンク編集」をクリックし、

該当するリンク先をクリックし右側の「値の更新」をクリックすると変更されます。

このタイミングで「商品マスタ」ブックの表の単価データが再反映されます。参照元をみると値が変更されていることがわかりますね。

参照先ブックが表示されているか否かでパスの表記が異なるということと、

参照先が表示されている場合、参照先データの更新がリアルタイムに参照先に反映されますが、

参照先ブックを閉じている場合は手動の更新が必要になるということを覚えてもらえばと思います。

余談ですが、このメッセージを消したい場合は定義情報にあるリンク先情報を削除し、

参照データから固定データに変換すればよいです。

「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。
リンクを信頼できる場合、リンクを更新し最新データを取り込みます。信頼できない場合は、データをそのまま手元で処理してかまいません。」

その方法は、先ほどのリンク編集画面の「リンクの解除」をクリックすれば

リンク解除および固定データ化できます。

固定データに変換されました。

メッセージを消したい場合の手段として覚えていただければと思います。



おわりに

いかがでしょうか。

普段使う関数だからこそ省力化し確実なものにしておきたいですね。

併せてデータの更新タイミングやメッセージ解除の方法も併せて覚えてもらえば、
いざトラブルや質問が来た場合に即座に対応できると思います。

タカヒロ@extanおすすめのVlookup本!

 

2 件のコメント

  • 夜分に失礼致します。
    やっとこちらのサイトに辿り着きました。
    上司より早急にと言われております。
    ご説明のとおり、他のブックの範囲を入れましが、OKを押してもパスがセルに出てくるのです。
    Excelは基本ほどのスキルです。
    vlookupで対応し、ブックの範囲が増えてもいいようにとの指示です。
    何卒、ご教示願います。

    • 返信遅れまして申し訳ございません。
      参照先のブックを表示した上、パスの部分がブック名のみになるか確認いただけますか。
      ブックのみにならない場合はパスの指定が誤っている可能性がありますので、
      ウイザードからパスの再指定をお願いします。

  • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です