今回の記事では、VLOOKUP関数を使う際に参照範囲を別シートに指定する方法を解説したいと思います。
VLOOKUP関数では参照するマスタデータを別シートに分けることでマスタデータの追加や削除などの管理が分かりやすく容易になり大変便利です。
特にこだわりがない場合は、範囲に指定するマスタデータは別シートに指定することをお勧めします。
VLOOKUP関数で別シートからマスタデータを参照するには、=VLOOKUP(検索値,範囲,列番号,[検索条件])のうちの2つ目の引数の”範囲”を指定する際に別シートの範囲を指定することで簡単に指定することができます。表示はシート名!セル範囲になります。
このようにとても簡単に使用できる機能ですが、なぜマスタデータを別シートにした方がいいのかやエラーが出る時の対処法を含め更に詳しく解説していきたいと思います。
↓VLOOKUP関数の詳しい使い方はこちら↓
最強の便利関数？VLOOKUP関数の使い方とは
↓VLOOKUP関数のエラー対策はこちら↓
VLOOKUP関数がうまく表示されない時の8つの対処法とは？【エラー対策】
↓別ファイルからデータを参照する方法はこちら↓
VLOOKUP関数で別ファイルからデータを参照する方法とは？
1．VLOOKUP関数で別シートを範囲に設定する方法
ここからは実際にVLOOKUP関数の参照範囲を別シートに指定する方法を解説したいと思います。
VLOOKUP関数の計算式は、
=VLOOKUP(検索値,範囲,列番号,FALSE)
でした。
この計算式の2つ目の引数にあたる範囲を指定する際に、別シートの範囲を指定することで簡単に別シートのデータを参照範囲に指定することができます。
どれでは実際に上の表を使って、商品マスタを別シートに移動してから、範囲に指定するまでの流れを解説したいと思います。
すでにマスタデータが別シートにある方はこちらの手順からお読みください。
1-1．別シートにマスタデータを移動させる方法
まずは、表左側の商品マスタの項目を別シートに移動する必要があります。
Excelシートの下部にあるシート名の隣の丸に＋と書かれたボタンを押し、新しいシートを追加します。
※この時に使っていないシートがすでにある状態でしたらこの操作は行わずに次に進んでください。
↓シートの追加方法の詳しい解説はこちら↓
エクセルでシートを追加する4つの方法と削除方法とは？
今回は”Sheet2”が追加されました。
シート名を変更することで、マスタデータの管理がより分かりやすくなります。
シート名にカーソルを合わせ右クリックを押すことでシート名を変更できるので、
”商品マスタ”などのわかりやすいシート名に変更しておきましょう。
次にマスタデータを別シートに移動させます。
別シートに移動させる商品マスタの範囲を選択し、Ctrl+Xのショートカットキーか右クリック→切り取り(T)を選択します。
移動させたいシートを選択し、一番左上のA1セルを選択してCtrl+Vか右クリック→貼付け(P)を選択します。
↓コピーペーストについて詳しくはこちら↓
意外と知らない？エクセルのコピーペーストの種類
↓ショートカットキーについては詳しくはこちら↓
【時短ワザ】便利なExcelのショートカットキー45選
これで、別シートにマスタデータを移動させることができました。
最後に、元々データがあった列を削除（今回はAからD列）して見た目を整えます。
これでマスタデータを別シートに移動させることができました。
1-2．VLOOKUP関数で範囲に別シートを指定する方法
それではいよいよVLOOKUP関数で参照範囲に別シートのマスタデータを指定する方法を解説します。
今回は試しに”A03”の商品コードから対応する商品名を検索してみたいと思います。
VLOOKUP関数の1つ目の引数の検索値は通常と同じく”=VLOOKUP(A03,”と入力します。
2つ目の引数の範囲指定時にマスタデータがある商品マスタのシートを選択（赤枠）し、そのままマスタデータをマウスでドラッグし範囲を設定（青枠）します。
すると、範囲の引数の部分に”=VLOOKUP(A03,商品マスタ!A3:C7,”とシート名と!（ビックリマーク）が表示されました。
この表示が別のシートを指定しているということを示しています。
今回の”商品マスタ!A3:C7”の表示の場合”商品マスタ”というシートのA3セルからC7セルまでを参照しているということを示しています。
残りの3つ目と4つ目の引数の列番号と検索方法は通常のVLOOKUP関数と同じように設定します。
今回の場合、商品名は指定した範囲の2列目にデータがあるので列番号には”2”を検索方法は通常通り”FALSE”を指定します。
出来上がった計算式は、
=VLOOKUP(“A03”,商品マスタ!A3:C7,2,FALSE)
となります。
↓絶対参照について詳しくはこちら↓
2．マスタデータを別シートにした方がいい理由とは？
VLOOKUP関数でのマスタデータを別シートにすることをおすすめしてきましたが、その理由を解説したいと思います。
その理由としてはマスタデータの情報を将来的に増やす場合に、計算式などが入っているシートと同じシートを使っていると、列や行を追加したときに他の表のレイアウトが崩れてしまう為です。
マスタデータのみを入力したシートを別シートに分けることで見た目にもスッキリとし、マスタデータの手直しやデータの追加や修正を容易にすることができるようになります。
2-1.後からシート名を変えても大丈夫？
VLOOKUP関数の範囲を別シートに指定した後に指定したシートの名前を変えた場合、自動的に計算式のシート名も変更されるため、計算式をわざわざ変更する必要はありません。
計算式を組んだ後でも、気軽にシート名を変更することができます。
↓VLOOKUP関数で参照範囲を可変にする方法はこちら↓
VLOOKUPとネストで範囲が可変に！OFFSET関数の使い方とは？
2-2．別ファイルからデータを参照するのもありなの？
結論から言うと別のファイルからマスタデータを参照することはおすすめしません。
どうしても別ファイルを範囲に指定したい場合はこちの記事に参照方法とメリットやデメリットをまとめています。
↓別ファイルからデータを参照する方法はこちら↓
VLOOKUP関数で別ファイルからデータを参照する方法とは？
↓外部リンクのエラーの対処法はこちら↓
外部リンクのエラーの検索方法と3つの対処方法とは？
3．VLOOKUP関数で別シートからの参照がうまくいかないときの対処法
ここまでVLOOKUP関数で別シートからデータを参照する方法を解説してきましたが、ここでは別シートからの参照がうまくいかないときの対処法を解説したいと思います。
3-1．VLOOKUP関数で別シートからの参照がうまくいかない4つの原因
別シートからの参照がうまくいかない原因として次の点が考えられます。
- VLOOKUP関数の使い方が間違っている
- 数値と文字列など参照先とのデータの型が間違っている
- 別シートの指定の方法が間違っている
- 数式を確定できない
3-2．VLOOKUP関数の使い方が間違っている
一番多い原因としてはVLOOKUP関数の引数の指定方法などそもそもの使い方が間違っているパターンです。
VLOOKUP関数は、
=VLOOKUP(検索値,範囲,列番号,FALSE)
の4つの引数を指定する必要があります。
VLOOKUP関数のエラーの対処法についてはこちらの記事にまとめてありますので、合わせてお読みください。
↓VLOOKUP関数のエラーの対象方はこちら↓
VLOOKUP関数がエラーでうまく表示されない時の8つの対処法とは？
3-3．数値と文字列など参照先とのデータの型が間違っている
次に多い原因として検索値に指定する値が数字の時に起こりやすい、見た目は同じ数字なのに「数値」と「文字列」というエクセル上での型が違っているためエラーになるパターンです。
この場合の対処法は、検索値に指定した数字と参照先のマスタの数字の型をVALUE関数やCONCATENATE関数で合わせる必要があります。
数値と文字列の違いによるエラーについてはこちらの記事で詳しく解説していますので、合わせてお読みください。
↓VLOOKUP関数の数値と文字列についての記事はこちら↓
【数値と文字列】VLOOKUP関数で同じ数字なのにエラーが出るときの対処法とは？
3-4．VLOOKUP関数での別シートの参照方法が間違っている
別シートを参照しているときのエラーとして起こるのがこのパターンです。
別シートの参照をするときにマウスでドラッグして範囲を指定するのが簡単ですが、範囲を指定した後に間違って別のセルをクリックしてしまうことで、範囲がクリックしたセルに上書きされてしまいます。
上の表のように別シートの参照範囲を本来なら、「Sheet2!A2:C6」と指定しないといけないところ、範囲指定時にA2セルを間違えてクリックしてしまったため、「Sheet2!A2」と上書きされてしまったためエラーになってしまっています。
マウスのドラッグで簡単に指定できる分、すぐにデータが上書きされてしまうので注意しましょう。
3-5．数式が確定できない
別シートの参照方法が間違っていると同じ原因ですが、計算式を修正するときにシート名を消さずに別の範囲をしていしまうことでシート名だけが増えてしまい「入力した数式にエラーがあります。」というポップアップが出て、計算式が確定できなくなってしまいます。
上の表の赤線部分を見ると、「Sheet2!Sheet2!Sheet1」と何回もシート名が入力されてしまっています。
これでは、どのシートを参照しているかわからず計算式にエラーがあると判断されてしまいます。
対処法としては一度範囲に指定指定している2つ目の引数部分をすべて消し、改めて範囲を指定することで計算式を確定することができます。
今回は、VLOOKUP関数でのマスタデータの範囲を別シートから参照する方法を解説しました。
VLOOKUP関数では範囲に別シートを参照する場合も簡単に指定することができるので、マスタデータの項目ごとにシートを分けて分かりやすく管理しやすい表を作って頂ければと思います。
最後までお読みいただきありがとうございました。
