この記事では「エクセルのVLOOKUP関数にて別シートに適用する方法」について解説していきます。
VLOOKUP関数にて別シートに適用するには、
・数式内に別シートの範囲を指定する
・複数シートに適用する場合は、INDIRECT関数を使う
といいです。
実際のサンプルを用いて詳細を見ていきましょう。
エクセルのVLOOKUP関数にて別シートに適用する方法
まずは、VLOOKUP関数にてデータベースを別シートに適用する方法をご紹介します。
下記のエクセルデータの表から、【シート1】社員データベースを【シート2】社員データ検索シートに適用してみましょう。
【シート1】
【シート2】
具体的には、シート2名前列のC3セルに =VLOOKUP(B3,テーブル5[#すべて],2,FALSE)と入力します。ダイアログボックスを使用します。
この数式について解説します。
VLOOKUP関数とは、
=VLOOKUP（検索値,範囲,列番号,検索方法）
と入力します。
今回は、社員番号を入力することで、シート1のデータベースから名前と所属のデータを取り出したいので、
・検索値：B3 シート2で社員番号を入力するセル番地
・範囲：テーブル5[#すべて] シート1データベースのセル範囲(※)
・列番号：2 シート1データベースの名前列の列番号(左から2番目)
・検索方法：FALSE 完全一致の場合、FALSEと入力
範囲にシート1のセル範囲を指定するには・ダイアログボックスの範囲欄をクリックし、シート1を押して、セル範囲を指定します。
※シート1データベースは『テーブル』として設定しています。理由については、「テーブル設定について」にて解説しています。
範囲にシート1 データベースB2:D10を指定しますが、上記理由からテーブル5[#すべて]と入力されます。
次に、所属列のD3セルに同じ要領でVLOOKUP関数を入力します。
ただし、シート1データベースの所属列が左から3番目のため、列号は3と入力します。
ダイアログボックスのOKボタンを押すと、この時点では#N/Aとエラー表示されます。
B3セルに検索したい社員番号を入力すると、下記のとおり表示されます。
テーブル設定について
データベースは『テーブル』として設定しています。
データベースにデータを追加する場合、VLOOKUP関数の値（範囲）をその都度変更しなくてもいいように、『テーブル』として設定すると良いです。
テーブルとして設定する方法は以下のとおりです。
テーブルとして設定したいセル範囲を選択します。今回は、B2:D10を選択します。
次に、挿入タブ内のテーブルをクリックします。
下記のように、『テーブルの作成』ダイヤルボックスが表示されます。
データ範囲には自動的に選択していたB2:D10が絶対参照されて、B$2:$D$10と指定されます。先頭行をテーブルの見出しとして使用するを選択し、最初の行を見出しにします。
OKを押すと、テーブルが設定されます。
エクセルのVLOOKUP関数にて複数の別シートに適用する方法
ここでは、複数の別シートに適用する方法をご紹介します。
下記のエクセルデータの表に、【名前】、【所属】、【残業時間】を、【社員データ】に適用してみましょう。
※【 】はシート名。それぞれのシートの社員番号、シート名のデータは同列・行に作成します。
【名前】
【所属】
【残業時間】
【社員データ
具体的には、【社員データ】のC3セルに=VLOOKUP($B3,INDIRECT(C$2&”!B:C”),2,FALSE)と入力します。
この数式について解説します。
VLOOKUPにて複数シートを適用するには、数式内の範囲にてINDIRECT関数を使用します。
INDIRECT関数は、文字列で指定したセル番地の内容を表示する関数です。
=INDIRECT（参照文字列,参照形式）
と入力します。
他の関数と組み合わせると便利で、VLOOKUP関数にも使用でき、別のシートにあるセル範囲を指定できます。
今回は、VLOOKUP関数に、
・検索値：$B3 【社員データ】で社員番号を入力するセル番地
・範囲：INDIRECT(C$2&”!B:C”) C2セルに入力した名前と同じシート名の中のデータベースのセル範囲を指定
・列番号：2 【名前】データベースの名前列の列番号(左から2番目)
・検索方法：FALSE 完全一致の場合、FALSEと入力
と入力します。
VLOOKUP関数の範囲に別シートの範囲を適用させたい場合、
=INDIRECT(参照シート&”!参照開始セル:参照終了セル”)）
と入力します。
参照シート&には、指定したいシート名が入力されたセル番地を入力します。
今回は名前シートを選択したいので、C$2と入力しています。文字列と連結させるために『&』連結演算子をセル番地の後ろに付けます。
“!参照開始セル:参照終了セル”には、指定したいシート内にあるセル範囲を入力します。
今回は名前シート内のB列～C列を指定します。“!B2:C10”とデータ範囲のみを入力したいところですが、”!B:C”とB列～C列全てを指定すると、データを追加した時も範囲を変更する必要がないのでオススメです。
検索値、INDIRECT関数内のセルを絶対参照(行もしくは列のみ固定)しているのは、オートフィルした時に、行もしくは列のみを変更させて求めたい結果を表示させるためです。
ENTERを押し、C3～E3をオートフィルすると、#N/Aとエラー表示されます。
B3セルに検索したい社員番号を入力すると、複数シートのデータが反映されます。
まとめ エクセルのVLOOKUP関数にて別シートに適用する方法
この記事では「エクセルのVLOOKUP関数にて別シートに適用する方法」について解説しました。
VLOOKUP関数の範囲に別シートのセル範囲を指定し、複数シートの場合はINDIRECT関数を使用すると、別シートに適用できます。
エクセルでのさまざまな処理を理解し、業務に役立てていきましょう。
