1. Home »
2. エクセル関数の技 »
3. 複数の表(セル範囲)を切り替えてデータを取り出す

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

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

更新:2024年6月17日;作成:2014年7月1日

はじめに topへ

• 下図のように1組〜3組の表があります。
  クラスによって参照する範囲を変えて、データを取り出したい・・・といった例です。
  
  A B C D E F G H I J K
  1 A組 B組 C組
  2 委員名 保健委員 井上 達也 図書委員 松山 利香 新聞委員 中林 文鑑
  3 クラス C組 保健委員 羽田 弘吉 城野 保淑 長谷部 鎭生
  4 益田 禮次郎 風紀委員 森脇 宏 鶴岡 和男
  5 堂山 和樹 放送委員 下村 健二郎 成瀬 孝司 放送委員 田路 覚
  6 新聞委員 丸谷 吉之 盛田 江利子 土江 克志
  7 丸谷 和博 保健委員 西崎 敬次 保健委員 堂山 和樹
  8 岩崎 専司 西村 正深 図書委員 飯尾 雅文
  9 図書委員 宮田 吉隆 西野 一夫 品田 喜久
  10 佐伯 英樹 放送委員 大月 一仁 風紀委員 福嶋 東
  11 若松 郁夫 大江 政勝 堀川 勝之
  12 秋山 善和 新聞委員 中元 英寿 矢吹 泰男
  13 風紀委員 勝谷 隆一 礒山 博治 矢鍋 日出夫
  14 柚江 武治 森 博之 矢鍋 和仁

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

• 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)
  

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

• 数式に名前を利用することができるので、表の範囲に名前を定義して利用することができます。
  短い数式にすることができそうです。

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

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)
    SWITCH関数を使うのと同じ感じです。
    

1組〜3組の場合(名前の定義を一工夫する) topへ

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

スポンサードリンク
(追記) (追記ここまで)

---

(追記) (追記ここまで)

---

よねさんのWordとExcelの小部屋 » エクセル関数の技 » 複数の表(セル範囲)を切り替えてデータを取り出す

PageViewCounter
Counter
Since2006/2/27