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

スポンサードリンク

更新:2024/6/17;作成:2014/7/1

はじめに　　topへ

下図のように1組～3組の表があります。
クラスによって参照する範囲を変えて、データを取り出したい・・・といった例です。

SWITCH関数はExcel2019,Excel2021,Excel for Microsoft 365 で使用することができます。
詳細は　SWITCH関数の使い方:Excel関数をご覧ください。
構文:=SWITCH(式,値1,結果1,[値2,結果2,・・・],規定値)
SWITCH関数でB4セルの値が　A組～C組　の時、参照するセル範囲をそれぞれ　F3:G15,H3:I15,J3:K15　と切り替えることができます。
SWITCH(B4,"A組",F3:G15,"B組",H3:I15,"C組",J3:K15)
=VLOOKUP(B3,セル範囲,2,FALSE)　のセル範囲にSWITCH関数を組み込むだけでデータを取り出すことができます。
=VLOOKUP(B3,SWITCH(B4,"A組",F3:G15,"B組",H3:I15,"C組",J3:K15),2,FALSE)

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

クラスによって参照範囲が異なるので、クラスごとに数式を作成する方法を最初に思い浮かべるかもしれません。
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)
  SWITCH関数を使うのと同じ感じです。

上記のように表(セル範囲)に名前を定義すると、かなり便利なことが分かりましたが、名前の定義では先頭に数字を使用することができません。
下図のようにクラスが「1組、2組、3組」といったケースではそのままの名前で定義することができません。
- 名前ボックスに「1組」と入力すると、下図のように名前に使用できないとのメッセージが表示されます。
  下図は説明用にちょっと加工していますので、ご了承ください。
名前の定義で数値の前に _(アンダーバー)などの記号を付け加えます。
_1組、_2組、_3組と名前を定義します。
数式を　=VLOOKUP(B3,INDIRECT("_"&B4),2,FALSE)　とします。

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))　となります。