VLOOKUPで別シートを参照する方法|シート名の書き方と、別ブックから取り込むときの注意点

教員向け 名簿シートから情報を引くVLOOKUP別シート 検索・参照関数

「名簿」のシートに、氏名と組が入っている。「提出」のシートに、氏名を入れたら、組が自動で出るようにしたい。教員の仕事では、別のシートの名簿から、情報を引いてくる場面があります。

ExcelのVLOOKUP関数は、別のシートの表からも、値を取り出せます。この記事では、教員の仕事でよく出る例を使って、書き方を説明します。

教員の仕事でVLOOKUPを使う場面|名簿シートから組を引く

表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「A2」は、A列の2行目のセルのことです。

AB
1氏名(名簿シート)組(名簿シート)
2青木1組
3伊藤2組
4上田1組

「提出」シートのB2に、次の式を入れます。A2に氏名を入れると、名簿の組が出ます。

=VLOOKUP(A2,名簿!A2:B31,2,FALSE)

「名簿!」の部分が、別のシートを指しています。最後の「FALSE」は、完全一致の指定です。

ここからは、別のシートを参照する書き方を順に見ていきます。

別シートを参照する書き方

別シートの範囲を指定するときは、範囲の前に「シート名!」をつけます。たとえば、「商品マスタ」シートのA2からC5を指定するなら、「商品マスタ!A2:C5」と書きます。

注文表シートのVLOOKUP関数が、商品マスタシートの範囲を参照して価格を取り出す式を示した図
  1. 結果を表示したいセル(C2)を選び「=VLOOKUP(」と入力し、検索値(A2)を選んでカンマを入れる
  2. 商品マスタのシート見出しをクリックして切り替え、範囲(A2:C5)をドラッグして選ぶ
  3. カンマを入れて列番号「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! になる:列番号が範囲の列数より大きい、または参照先のシートやブックがなくなっています
  • 範囲がずれる:「$」で絶対参照にしていないと、下にコピーしたときに範囲がずれます
  • シート名を変更したら:式のシート名は自動で更新されますが、別ブックの場合は更新されないことがあります

もう一歩進める|シート名や範囲を柔軟に指定する

別シートを参照する式は、シートを増やしたり範囲が変わったりすると、直す手間が増えがちです。式を長持ちさせる工夫を紹介します。

範囲に名前をつける

参照する範囲を選び、画面左上の名前ボックスに「商品表」のような名前を入力してEnterを押すと、範囲に名前をつけられます。式の中では、シート名と範囲の代わりに名前を使えます。

=VLOOKUP(A2,商品表,2,FALSE)

シート名の書き方に悩まずに済み、式も読みやすくなります。

INDIRECT関数でシート名を切り替える

月ごとにシートが分かれているときは、シート名を入力したセルを参照すると、シートの切り替えが簡単になります。

=VLOOKUP(A2,INDIRECT("'"&B1&"'!A:C"),2,FALSE)

B1に「10月」と入力すれば10月のシートを、「11月」と入力すれば11月のシートを検索します。なお、INDIRECTは参照先のシートが開いていないブックでは使えません。

表をテーブルにしておく

参照元の表を「テーブル」にしておくと、行を追加したときに範囲が自動で広がります。範囲を「A:C」のように列全体で指定する方法もありますが、データが多いときは計算が重くなることがあるので、状況に合わせて選びましょう。

別シート参照で起こりやすいトラブル

  • 参照先のシートを削除すると、式が#REF!になる
  • シート名を変えると、通常の式は自動で追従するが、INDIRECTで書いた式は追従しない
  • 参照先の範囲に行を追加しても、範囲の外側に追加すると式に含まれない

式を作ったあとに、シートの名前を変えたり行を追加したりしたら、結果が正しく出ているかを一度確かめる習慣をつけておくと安心です。特にINDIRECTを使った式は、シート名の入力ミスがそのまま#REF!になるので、シート名のセルを入力規則のプルダウンにしておくとミスを防げます。

別のブックを参照しているときの注意

別のブックを参照する式は、ファイルを開くたびに「リンクの更新」のメッセージが表示されることがあります。参照元のファイルの場所を変えたり、名前を変えたりすると、リンクが切れて、結果が正しく表示されなくなります。

参照元のブックを動かしたときは、「データ」タブの「リンクの編集」から、リンク先を指定し直しましょう。共有するファイルでは、参照元もあわせて渡すか、必要な値だけを取り込んでおくと、トラブルを防げます。

別のシートを参照するときの注意

  • シート名を変えると、式のシート名も自動で変わります。シートを削除すると、エラーになります。
  • 見つからないときの対処は、VLOOKUPで#N/Aが出る原因と直し方で説明しています。

あわせて読みたい

コメント

タイトルとURLをコピーしました