複数の表(セル範囲)を切り替えてデータを取り出す:Excel関数の技

VLOOKUP関数を使って複数の表やセル範囲を参照して、データを取り出す方法です。

スポンサードリンク

IF関数で表(数式)を切り替える	表(セル範囲)に名前を定義する
表(セル範囲)をChoose関数などで選択する	INDEX関数で領域を切り替える

はじめに　　topへ

下図のように3つの表があり、この表を参照してデータを取り出したい・・・といったケースです。

IF関数で表(数式)を切り替える　　 topへ

クラスによって参照範囲が異なるので、クラスごとに数式を作成する方法を最初に思い浮かべるかもしれません。
VLLOKUPの数式をIF関数で切り替えると考えると以下のようになります。
- B6セルの数式は
  =IF(B4="A組",VLOOKUP(B3,F3:G15,2,FALSE),IF(B4="B組",VLOOKUP(B3,H3:I15,2,FALSE),VLOOKUP(B3,J3:K15,2,FALSE)))
  としました。
  - A組のときは
    VLOOKUP(B3,F3:G15,2,FALSE)
    B組のときは
    VLOOKUP(B3,H3:I15,2,FALSE)
    C組の時（A,B組でないとき)は
    VLOOKUP(B3,J3:K15,2,FALSE)
    これらの数式をIF関数を使って分岐させることになります。
  - A組のときとそうでないときの数式は↓のようになります。
    IF(B4="A組",VLOOKUP(B3,F3:G15,2,FALSE),A組でないときの数式)
    B組のときとそうでないときの数式は↓のようになります。
    IF(B4="B組",VLOOKUP(B3,H3:I15,2,FALSE),VLOOKUP(B3,J3:K15,2,FALSE))
    この2つの数式をつなげて、↓になります。
    =IF(B4="A組",VLOOKUP(B3,F3:G15,2,FALSE),IF(B4="B組",VLOOKUP(B3,H3:I15,2,FALSE),VLOOKUP(B3,J3:K15,2,FALSE)))
VLOOKUP関数の参照するセル範囲をIF関数で切り替える・・・と考えると以下のようになります。
- 上記よりいくらか短い数式になりました。
  =VLOOKUP(B3,INDIRECT(IF(B4="A組","F3:G15",IF(B4="B組","H3:I15","J3:K15"))),2,FALSE)

表(セル範囲)に名前を定義する　　 topへ

数式に名前を利用することができるので、表の範囲に名前を定義して利用することができます。
短い数式にすることができそうです。
各表(セル範囲)に名前を定義します。
1. A組の表のセル範囲 F3:G15 を選択して、名前ボックスに「A組」と入力して、名前を定義します。
  B組の表のセル範囲 H3:I15 を選択して、名前ボックスに「B組」と入力して、名前を定義します。
  C組の表のセル範囲 J3:K15 を選択して、名前ボックスに「C組」と入力して、名前を定義します。
  - 数式タブの[名前の定義]から設定することもできます。
    
    ↓
2. INDIRECT(B4)　で表のセル範囲を指定することができますので、数式は非常に単純なものになります。
  =VLOOKUP(B3,INDIRECT(B4),2,FALSE)

表(セル範囲)をChoose関数などで選択する　　 topへ

上記のように表(セル範囲)に名前を定義すると、かなり便利なことが分かりましたが、名前の定義では先頭に数字を使用することができません。
下図のようにクラスが「1組、2組、3組」といったケースではそのままの名前で定義することができません。
- 名前ボックスに「1組」と入力すると、下図のように名前に使用できないとのメッセージが表示されます。
  下図は説明用にちょっと加工していますので、ご了承ください。
Choose関数でセル範囲を選択することを考えました。
- Choose関数で各表のセル範囲を選択するため、
  CHOOSE(MATCH(B4,{"1組","2組","3組"},0),"F3:G15","H3:I15","J3:K15")
  - Choose関数の引数(インデックスの部分)にはMatch関数で「{"1組","2組","3組"}　を組み合わせて、何番目のセル範囲化を選択するようにしています。
    完全一致での検索にするためMatch関数の照合の種類は 0 としています。
    MATCH(B4,{"1組","2組","3組"},0)
- セル範囲はこのままでは単なる文字列扱いになるので、Indirect関数でセル範囲として参照できるようにします。
  INDIRECT(CHOOSE(MATCH(B4,{"1組","2組","3組"},0),"F3:G15","H3:I15","J3:K15"))
- 数式は↓のようにVLOOKUP関数に組み込みました。
  =VLOOKUP(B3,INDIRECT(CHOOSE(MATCH(B4,{"1組","2組","3組"},0),"F3:G15","H3:I15","J3:K15")),2,FALSE)
なお、クラスの名前が1,2,3・・となっているのを利用することができるのでは？・・・と考えると以下のように変えることができます。
- 「1組」に左の文字1個を取り出すと「1」となり、「2組」に左の文字1個を取り出すと「2」となるのを利用した数式になります。
  =VLOOKUP(B3,INDIRECT(CHOOSE(LEFT(B4),"F3:G15","H3:I15","J3:K15")),2,FALSE)
VLOOKUPの中にVLOOKUP関数を組み込むこともできます。
- クラス名が「1組」とか数字を含まないときにも利用可能です。
  =VLOOKUP(B3,INDIRECT(VLOOKUP(B4,{"1組","F3:G15";"2組","H3:I15";"3組","J3:K15"},2,FALSE)),2,FALSE)

INDEX関数で領域を切り替える　　 topへ

INDEX関数は領域を切り替えることができます。　詳細は INDEX関数の使い方を参照してください。
INDEX関数の構文は　=INDEX(範囲,行位置,列位置 [,領域番号])　です。
3つの領域を"A組","B組","C組"によって切り替えるには　　MATCH(B4,{"A組","B組","C組"},0)　として、"A組"で1、"B組"で2,"C組"で3を返します。
領域は　INDEX((F3:F15,H3:H15,J3:J15),,0,組)　(組：上のMatch関数で求めた値)　で3つの領域の列をセル参照せることができます。
INDEX関数の引数で行位置は空欄にして、列位置のところに 0 を設定して列をセル参照しているのがポイントです。
この参照する列から求める委員名の行位置は　=MATCH(B3, INDEX関数で求めた列,0)　で求めることができます。
これらを組み合わせると、　=MATCH(B3,INDEX((F3:F15,H3:H15,J3:J15),,0,MATCH(B4,{"A組","B組","C組"},0)),0)　となります。
名前の領域を切り替えるには　=INDEX((G3:G15,I3:I15,K3:K15),上で求めた行位置,,MATCH(B4,{"A組","B組","C組"},0))　となります。
これらを組み合わせると　=INDEX((G3:G15,I3:I15,K3:K15),MATCH(B3,INDEX((F3:F15,H3:H15,J3:J15),,0,MATCH(B4,{"A組","B組","C組"},0)),0),,MATCH(B4,{"A組","B組","C組"},0))　となります。