複数のリストを参照してデータを表示(3)
複数のドロップダウンリストを切り替えながら、INDIRECT関数とVLOOKUP関数で参照先を切り替える方法を紹介します。
複数シート上にある参照リスト VLOOKUPとINDIRECT
VLOOKUP関数で複数の表を切り替えてデータを取り出す
33回の講義で、INDIRECT 関数とVLOOKUP関数で複数の参照先を切り替えて使う方法を紹介しましたが、参照する表は、皆同じシート上でした。
参照する表が複数の別シートの場合は、どうしたらいいのでしょうか?データのあるシートがクラス別になっている場合にVLOOKUP関数でクラスを切り替えながら表引きしたいケースなどです。
実はシートが違っても、考え方は同じでいいのです。
今回は、別シートの場合で、説明しましょう。
(サンプルファイルは、こちらから 2013関数技33c回サンプルデータ)
生徒名簿の範囲に名前を定義して参照しやすく
下記のような生徒検索シートがあり、3クラス分の生徒名簿が別シート上にあります。生徒名簿がいくつあっても大丈夫ですが、今回は3クラスです。
クラスの名簿は、別シートです。
準備として、それぞれのクラスごとに、範囲を名前定義します。
準備として、それぞれのクラスごとに、範囲を名前定義します。
名前の管理ダイアログボックスが表示されたら、新規作成ボタンをクリックします。
名簿のクラスごとに名前をつけ、範囲を指定します。これをクラスの数だけ作成します。
名前定義を作成し終わりました。
では、検索シートの氏名を表示したいセルにVLOOKUP関数の式を入力しましょう。
VLOOKUP(検索値, 範囲, 列番号, [検索の型])を
シートに当てはめると、E2セルに入力する式は、
=VLOOKUP(B3,INDIRECT(B2),2)
(検索の型は、省略)
シートに当てはめると、E2セルに入力する式は、
=VLOOKUP(B3,INDIRECT(B2),2)
(検索の型は、省略)
INDIRECT関数は、文字列で間接的にセル範囲を指定できる関数です。
INDIRECT(B2) でB2セルに入力された文字列に対応する範囲という意味になります。
ちなみに、INDIRECT関数を使わずに、セル範囲を指定すると、
Aクラスは、
=VLOOKUP(B3,AClass!B2:C24,2)
Bクラスは、
=VLOOKUP(B3,BClass!B2:C24,2)
Cクラスは、
=VLOOKUP(B3,CClass!B2:C24,2)
となり、式が3種類必要になります。
この3つの式を1つにまとめるのが、
=VLOOKUP(B3,INDIRECT(B2),2)
INDIRECT(B2) でB2セルに入力された文字列に対応する範囲という意味になります。
ちなみに、INDIRECT関数を使わずに、セル範囲を指定すると、
Aクラスは、
=VLOOKUP(B3,AClass!B2:C24,2)
Bクラスは、
=VLOOKUP(B3,BClass!B2:C24,2)
Cクラスは、
=VLOOKUP(B3,CClass!B2:C24,2)
となり、式が3種類必要になります。
この3つの式を1つにまとめるのが、
=VLOOKUP(B3,INDIRECT(B2),2)
クラス名を入力するセルB2は、3つのクラス名をリストにしておけば、なおいっそう入力がスピーディになります。
VLOOKUP関数のエラー対策
さて、式はこれでほぼ完成ですが、番号が入力されていないとエラーが表示されてしまいます。
そこで、IF関数を使って、もしB3が空白なら、何も表示しないで、空白出ない時は、VLOOKUP(B3,INDIRECT(B2),2)を実行という式に修正します。
もしB3が空白なら、何も表示しないでの部分、
=IF(B3="","",
つなげると、
=IF(B3="","",VLOOKUP(B3,INDIRECT(B2),2))
もしB3が空白なら、何も表示しないでの部分、
=IF(B3="","",
つなげると、
=IF(B3="","",VLOOKUP(B3,INDIRECT(B2),2))
VLOOKUP 関数で、複数の表を切り替えてデータを取り出す組み合わせの説明は以上です。お疲れ様でした。