「商品マスタは別のシートにあって、注文表のシートから価格を引っ張りたい」。こんなときも、VLOOKUP関数は使えます。範囲の指定にシート名をつけるだけです。
この記事では、別シートを参照する書き方を図解し、範囲を固定する方法や、別のブックを参照するときの注意点まで説明します。
=VLOOKUP(A2,商品マスタ!$A$2:$C$5,3,FALSE)別シートを参照する書き方
別シートの範囲を指定するときは、範囲の前に「シート名!」をつけます。たとえば、「商品マスタ」シートのA2からC5を指定するなら、「商品マスタ!A2:C5」と書きます。
- 結果を表示したいセル（C2）を選び「=VLOOKUP(」と入力し、検索値（A2）を選んでカンマを入れる
- 商品マスタのシート見出しをクリックして切り替え、範囲（A2:C5）をドラッグして選ぶ
- カンマを入れて列番号「3」、検索方法「FALSE」を入力し、「)」で閉じて Enter を押す
手順2のように、シートを切り替えながら範囲をドラッグすると、シート名と「!」が自動で入力されます。
シート名にスペースや記号があるとき
シート名にスペースや記号が含まれる場合は、シート名を半角のシングルクォーテーション「’」で囲みます。たとえば「商品 マスタ」という名前なら、次のように書きます。
=VLOOKUP(A2,'商品 マスタ'!$A$2:$C$5,3,FALSE)ドラッグして範囲を選べば自動で付くので、手入力するときだけ気をつけてください。
別のブックを参照するとき
別のExcelファイル（ブック）のデータも参照できます。参照元のブックを開いた状態で範囲を選ぶと、ブック名とシート名が自動で入力されます。
=VLOOKUP(A2,[価格表.xlsx]商品マスタ!$A$2:$C$5,3,FALSE)- 参照元のブックを閉じると、式に保存先のパスが入る：パスが長くなりますが、動作は変わりません
- 参照元のファイルを移動・名前変更すると、リンクが切れる：#REF! になることがあります
- ファイルを開くときに、リンク更新の確認が出る：内容を確認したうえで更新します
うまく表示されないときの確認ポイント
- #N/A になる：検索値が範囲の左端の列にない、またはスペースや文字列の違いで一致していません
- #REF! になる：列番号が範囲の列数より大きい、または参照先のシートやブックがなくなっています
- 範囲がずれる：「$」で絶対参照にしていないと、下にコピーしたときに範囲がずれます
- シート名を変更したら：式のシート名は自動で更新されますが、別ブックの場合は更新されないことがあります
まとめ
- 別シートを参照するときは、範囲の前に「シート名!」をつける
- シート名にスペースがあるときは、「’」で囲む
- コピーしてもずれないよう、範囲は「$」で固定する
- 別ブックを参照するときは、ファイルの移動や名前変更でリンクが切れる
- #N/Aや#REF!は、検索値・列番号・参照先を確認する
データを別シートにまとめておくと、管理表がすっきりします。ぜひ試してみてください。
コメント