1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. INDEX関数の使い方

INDEX関数の使い方(行と列を指定して値を取り出す):Excel関数

• リストから値を引き出す関数としてはVLOOKUP関数やHLOOKUP関数が代表的ですが、これらの関数はキーとなるデータがリストの左端列や最上行にあることが前提条件となります。
• これらの関数で表引きできないケースではINDEX関数を利用します。ただし、INDEX関数単独ではなくMATCH関数と組み合わせることが多いでしょう。

2021/06/13

範囲の中から、行位置と列位置を指定して値を取り出す：配列形式　　topへ
　インデックス
=INDEX(範囲,行番号,列番号)

　マッチ
=MATCH(検索値,範囲 [,照合の型])
　　　照合の型：「0」完全に一致する値を検索する
　　　　　　　　「1」一致するものがない場合、検索値以下の最大の値を検索する。（データは昇順に並んでいることが必要）
　　　　　　　　「-1」一致するものがない場合、検索値よりも大きい最小の値を検索する。（データは降順に並んでいることが必要）

• INDEX関数は、セル範囲または配列の中から、指定した行と列が交差したセルの値を返すことができます。
  指定した行と列を求めるには MATCH関数を利用します。
  つまり、INDEX関数とMATCH関数とを組み合わせて、表でクロスする位置の値を引き出します。

• 【問題1】2018年〜2020年の桜島の噴火回数のデータ表があります。
  年と月を入力すると、噴火回数が表示されるように D8セルに数式を入力しなさい。
  

  	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
  2	年	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月	合計	桜島の噴火回数
  3	2018年	13	7	44	69	96	35	29	64	44	8	14	56	479	2020年12月07日現在
  4	2019年	8	15	29	10	15	5	9	0	32	62	137	71	393
  5	2020年	104	129	26	51	51	28	0	1	7	2	8	4	411
  6
  7	年	月	回数
  8	2019年	9月	32

• 【問題1の解答例】
  2019年9月の噴火回数を求める数式は
  データ範囲は　C3:O5 です。
  行番号は　2019年は範囲の2行目なので 2 となります。
  列番号は　9月は1月〜合計の 9列目なので 9 となります。
  よって、=INDEX(C3:O5,2,9)　で求めることができます。
• この行番号と列番号を数式で求めるには、Match関数を使って求めます。
  行番号は　B3:B5セルの値2018年〜2020年が昇順に並んでいるので、MATCH(B8,B3:B5) で求めることができます。
  昇順に並んでいるのがはっきりしない場合は、完全一致で検索して　MATCH(B8,B3:B5,0) とすることもできます。
  列番号は C2:O2セルの値 1月〜合計が昇順ではなくランダムに並んでいるので、完全一致で求めるため　　MATCH(C8,C2:O2,0) として求めることができます。
• D8セルの数式は　=INDEX(C3:O5,MATCH(B8,B3:B5),MATCH(C8,C2:O2,0))　としました。
  
  • INDEX関数を関数の引数から利用すると、引数の選択が表示されます。
    「配列,行番号,列番号」を選択します。
    
    INDEX関数の引数ダイアログボックスには配列,行番号,列番号の引数が指定できます。
    配列に　C3:O5
    行番号に　MATCH(B8,B3:B5)
    列番号に　MATCH(C8,C2:O2,0)
    と入力します。
    
• Excel2019,Microsoft 365ではXLOOKUP関数を使うことができるようになりました。
  XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。
  D8セルに　=XLOOKUP(C8,C2:O2,XLOOKUP(B8,B3:B5,C3:O5))　と入力します。
  

• 【問題2】下図のようなリストがあります。
  F3セルに氏名を入力すると、G3セルにコードNoを表示するようにしなさい。
  

  	B	C	D	E	F	G
  2	コードNo	氏名	住所		氏名	コードNo
  3	A1001	相沢一郎	鹿児島市山下町		井上弘毅	A1003
  4	A1002	池田肇	鹿児島市易居町
  5	A1003	井上弘毅	鹿児島市泉町
  6	A1004	内田洋二	鹿児島市中町
  7	A1005	榎本秀樹	鹿児島市千日町

• 【問題2の解答例】
  • この問題は検索する列が左端列ではないので、VLOOKUP関数が使用できない例です。
    このように検索す列が左端列でない場合はINDEX関数を使います。
  • INDEX関数の引数の範囲は、求めるコードNoの範囲 B3:B7 となります。
    INDEX関数の引数の行番号はMATCH関数の完全一致 MATCH(F3,C3:C7,0) で求めました。
    氏名は昇順になっていないので、完全一致で求めています。
    ちなみに、氏名の昇順は(ここの問題ではふりがな情報がないので)、井上弘毅、榎本秀樹、相沢一郎、池田肇、内田洋二　の順になります。
    範囲が1列なので、INDEX関数の引数の「列番号」は省略しています。
  • よって、G3セルに　=INDEX(B3:B7,MATCH(F3,C3:C7,0))　と入力します。
    
  • ちなみに、関数の引数から数式を入力する場合は下図のようになります。
    INDEX関数の　配列には　B3:B7　、行番号には　MATCH関数を入力します。列番号は　空欄のままです。
    
    MATCH関数は検査値に　Sheet1!A2　、検査範囲には　Sheet2!$B$2:$B$6　、照合の種類には　完全一致で検索したいので　0(ゼロ)を入力します。
    
• Excel2019,Microsoft 365ではXLOOKUP関数を使うことができるようになりました。
  XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。
  M3セルに　=XLOOKUP(F3,C3:C7,B3:B7)　と入力します。
  

• 【問題3】成績表ができました。この成績表は生徒Noの順番に並んでいますので、成績の良い方(合計の大きい方)から順番に並べ替えなさい。
  なお、このページはINDEX関数の練習ですので、INDEX関数を用いてください。
  また、合計得点に同点は存在しないものとします。
  (同点がある場合はちょっと面倒になるため、簡略化した例で練習します)

  • 	B	C	D	E	F	G	H	I
    2	No	氏名	ふりがな	性別	国語	数学	英語	合計
    3	1	赤羽 美幸	アカバネ ミユキ	女	85	91	83	259
    4	2	葛西 祐基	カサイ ユウキ	男	95	91	70	256
    5	3	川井 美里	カワイ ミサト	女	69	57	86	212
    6	4	笹川 満	ササガワ ミツル	男	66	79	64	209
    7	5	柴崎 優	シバザキ ユウ	女	80	48	48	176
    8	6	平 奈央	タイラ ナオ	女	78	94	54	226
    9	7	高畑 勇介	タカハタ ユウスケ	男	95	88	70	253
    10	8	浜田 希	ハマダ ノゾミ	女	82	95	69	246
    11	9	平岡 那奈	ヒラオカ ナナ	女	86	77	69	232
    12	10	藤本 ヒロ	フジモト ヒロ	男	85	65	68	218
    13	12	横田 まさし	ヨコタ マサシ	男	61	58	70	189

• 【問題3の解答例】
  • 下図はM3セル以降に書き出しています。
    M3セルの数式は
    =INDEX($B$3:$I$13,MATCH(LARGE($I$3:$I$13,ROW($A1)),$I$3:$I$13,0),COLUMN(A$1))
    としました。
    この数式を右方向へコピーし、さらにコピーした3行目の数式を下方向へコピーすれば完成します。
    
  • =INDEX($B$3:$I$13,MATCH(LARGE($I$3:$I$13,ROW($A1)),$I$3:$I$13,0),COLUMN(A$1))　の説明
    • LARGE($I$3:$I$13,ROW($A1)　はLARGE関数で合計得点を大きい方から順番に取り出します。
      LARGE(I3:I13,1)、LARGE(I3:I13,2)、LARGE(I3:I13,3)、・・・　と順番に取り出すために　ROW($A1)　を使っています。
      下方向にコピーしたとき、1,2,3・・・となるようにしています。
    • MATCH(LARGE($I$3:$I$13,ROW($A1)),$I$3:$I$13,0)　は一番大きい合計得点から順番に、I3:I13　から探して、一致した行位置を返します。
      I3:I13　の合計得点は昇順ではないので、MATCH関数の引数の照合の種類を 0 とします。
    • =INDEX($B$3:$I$13,MATCH(LARGE($I$3:$I$13,ROW($A1)),$I$3:$I$13,0),COLUMN(A$1))
      この数式は　INDEX(データ範囲,合計の一致した行,列位置)　となります。
      列位置は列方向へコピーしたときに、1,2,3・・・となるように　COLUMN(A$1)　としています。
  • INDEX関数は配列に　$B$3:$I$13　、列番号には　COLUMN(A$1)　としました。行番号は次のMATCH関数を参照してください。
    
  • MATCH関数の検査値は次の　LARGE関数を参照してください。検査範囲には　$I$3:$I$13　、照合の種類は　0　と入力しました。
    
  • LARGE関数の配列には　$I$3:$I$13　、順位には　ROW($A1)　と入力しました。
    　$A1 は横にコピーしたときにB,C,D・・・と変化するのを嫌っただけです。$Aと同じ列番号でコピーされるほうがきれいだと思い、このようにしています。見た目だけです。
    
• Microsoft 365ではSORT関数を使うことができるようになりました。
  SORT関数の使い方は　SORT関数でデータを並べ替える:Excel関数　をご覧ください。
  M3セルに　=SORT(B3:I13,8,-1)　と入力するだけでデータを降順に並べ替えることができます。
  

指定された行と列が交差する位置にあるセルの参照を返します：セル範囲形式　　topへ
　インデックス
=INDEX(範囲,行位置,列位置 [,領域番号])
　　　　隣接しない複数のセル範囲を指定した場合、領域番号でその中から任意の領域を選択できます。

• INDEX関数を関数の引数から利用すると、引数の選択が表示されます。
  「参照,行番号,列番号,領域番号」を選択します。
  
  INDEX関数の引数ダイアログボックスには参照,行番号,列番号,領域番号の引数が指定できます。
  

• 【問題4】INDEX関数を使って、第一四半期(4月〜6月)の合計売上額を求めなさい
  

  	B	C
  2	月	売上額
  3	4月	1,500
  4	5月	1,200
  5	6月	1,600
  6	7月	1,800
  7	8月	1,200
  8
  9	上期の売上合計	4,300

• 【問題4の解答例】
  • 求めるのは4月〜6月の合計値なので、=SUM(C3:C5)　となります。
    セルの位置を数値で指定すると、(説明を簡略するため、あらかじめ C3は決め打ちしています)
    =SUM(C3:INDEX(B3:C7,3,2))　で求めることができます。
    INDEX(B3:C7,3,2)　で　C5　がセル参照で返されるので、　=SUM(C3:C5)=4300　となります。
  • 「4月」と「6月」を検索して計算する場合は
    =SUM(INDEX(B3:C7,MATCH("4月",B3:B7,0),2):INDEX(B3:C7,MATCH("6月",B3:B7,0),2))
    とMATCH関数を使って求めます。
    • INDEX(B3:C7,MATCH("4月",B3:B7,0),2)
      はINDEX関数で　B3:C7から「4月」を検索して、見つかった行の2列目 C3セル を返します。
    • INDEX(B3:C7,MATCH("6月",B3:B7,0),2)
      はINDEX関数で　B3:C7から「6月」を検索して、見つかった行の2列目 C5セル を返します。
    • =SUM(C3:C5)を計算することができます。
  • 
• Excel2019,Microsoft 365ではXLOOKUP関数を使うことができるようになりました。
  XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。
  C9セルに　=SUM(XLOOKUP("4月",B3:B7,C3:C7):XLOOKUP("6月",B3:B7,C3:C7))　と入力します。
  INDEX関数とMATCH関数の組み合わせをXLOOKUP関数で代用できます。
  

• 【問題5】INDEX関数とMATCH関数を使って運賃を求めなさい。
  

  	B	C	D	E
  2	運賃表
  3		東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000
  5	宮崎	45,000	35,000	30,000
  6	熊本	40,000	30,000	25,000
  7	福岡	30,000	20,000	15,000
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	運賃は	30,000

• 【問題5の解答例】C11セルに　=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0))　と入力します。
  

• INDEX関数での検索範囲はデータの範囲C4:E7で、
  行位置はMATCH関数で出発地の熊本（C10)の位置をB4:B7の範囲から検索し、
  列位置はMATCH関数で到着地名古屋（C9)の位置はC3:E3の範囲から検索します。
• INDEX関数の配列に　C4:E7、行番号に　MATCH(C10,B4:B7,0)　、列番号に　MATCH(C9,C3:E3,0)　と入力します。
  MATCH関数の照合の型は「完全に一致」ですので「0」を指定しています。
  B列の出発地と3行目の到着地が昇順に並んでいないためです。
  
• Excel2019,Microsoft 365ではXLOOKUP関数を使うことができるようになりました。
  XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索する:Excel関数　をご覧ください。
  C11セルに　=XLOOKUP(C10,B4:B7,XLOOKUP(C9,C3:E3,C4:E7))　と入力します。
  

• 【問題6】大人運賃表と子供運賃表を使って運賃を求めなさい

  	B	C	D	E	F	G	H	I	J
  2	運賃表 （A:大人)					運賃表 （B:子供)
  3		東京	名古屋	大阪			東京	名古屋	大阪
  4	鹿児島	50,000	40,000	35,000		鹿児島	25,000	20,000	17,500
  5	宮崎	45,000	35,000	30,000		宮崎	22,500	17,500	15,000
  6	熊本	40,000	30,000	25,000		熊本	20,000	15,000	12,500
  7	福岡	30,000	20,000	15,000		福岡	15,000	10,000	7,500
  8
  9	到着地	名古屋
  10	出発地	熊本
  11	大人/子供	子供
  12	運賃は	15,000

• 【問題6の解答例】
  • =INDEX(範囲,行位置,列位置 ,領域番号)の形式を使います。 領域番号で返る値で範囲を切り替えます。
  • 範囲に(C4:E7,H4:J7)の2つの領域を、領域番号で使い分けることができます。
    領域番号の指定を　MATCH(C11,{"大人","子供"},0)　として、大人なら「1」、子供なら「2」が返るようにしています。
    2つの領域(C4:H7,H4:J7)のうち　領域番号で1が返れば C4:H7 となり、領域番号で2が返れば H4:J7　となります。
  • MATCH(C11,{"大人","子供"},0) の部分は　IF関数を使って　IF(C11="大人",1,2)　とすることもできます。
    すると、数式は　=INDEX((C4:E7,H4:J7),MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0),IF(C11="大人",1,2))　となります。
  • 
  • INDEX関数の参照に　　(C4:H7,H4:J7)　と入力すると　#VALUE! と赤文字でエラー表示になりますが、ちゃんと計算されますので無視してください。原因は知りません。(;^_^A
    行番号は　MATCH(C10,B4:B7,0)　、列番号は MATCH(C9,C3:E3,0)　と入力しました。
    
    領域番号のMATCH関数は下図のように、検査値にC11、検査範囲には配列で {"大人","子供"}　と入力し、照合の種類は　0　としました。
    

• 【問題7】B12セルにNoを入力すると、下図の成績表から国語〜合計の値を抜き出してC12:G12へ転記するような数式を入力しなさい。
  

  	B	C	D	E	F	G
  2	No	氏名	国語	数学	英語	合計
  3	1	葛西 祐基	80	35	66	181
  4	2	宮下 涼	38	95	87	220
  5	3	浜田 希	50	97	34	181
  6	4	川井 美里	38	32	81	151
  7	5	石坂 雅和	69	54	91	214
  8	6	笹川 満	99	97	52	248
  9
  10
  11	No	氏名	国語	数学	英語	合計
  12	3	浜田 希	50	97	34	181

• 【問題7の解答例】
  1. C12:G12セルを選択します。
  2. 数式バーに　=INDEX(C3:G8,MATCH(B12,B3:B8,0),0)　と入力し、[Shift]+[Ctrl]+[Enter] で数式の入力を確定します。
     
     • 関数の引数ダイアログボックスを使う場合は、
       参照にC3:G8　、行番号に MATCH(B12,B3：B8,0)　、列番号に 0 を入力します。
       [Shift]+[Ctrl] キーを押した状態で、[OK]ボタンをクリックします。
       
  3. {=INDEX(C3:G8,MATCH(B12,B3:B8,0),0)}　数式の前後に括弧 {　} が入力され、配列数式が入力できました。
     選択していたセル範囲に 指定行のデータを求めることができました。
     ポイントは　=INDEX(C3:G8,MATCH(B12,B3:B8,0),0)　列指定を 0 としているところです。
     
  4. B12セルの値を変えるとC12:G12セルの値の変わります。
     
  • INDEX関数の行番号を 0 とすると、列データを得ることができます。
    下図はI3:I8セルを選択して、配列数式　{=INDEX($C$3:$G$8,0,MATCH(I$2,$C$2:$G$2,0))}　を入力しています。
    
• 上記のように、なかなか難解な数式を使ってデータを抽出することができました。
  Microsoft 365では FILTER関数が使えるようになりました。
  FILTER関数の詳細な使い方は　FILTER関数でデータを抽出する:Excel関数　をご覧ください。
  C12セルに　=FILTER(C3:G8,B3:B8=B12)　と入力するだけで、データを抽出することができます。
  

• 【問題8】下表には種類が重複する商品が入力されています。
  種類（B11セル）を入力すると、2つ目の商品を D11セルに表示しなさい。
  

  	B	C	D
  2	種類	商品
  3	野菜	人参
  4	肉	鶏肉
  5	果物	みかん
  6	野菜	大根
  7	肉	牛肉
  8	果物	りんご
  9
  10	種類		商品
  11	肉	の2つ目は	牛肉

• 【問題8の解答例】
  D15セルには　=VLOOKUP(B11,INDEX(B3:B8,MATCH(B11,B3:B8,0)+1):C8,2,FALSE)　と入力しました。
  
  • INDEX関数を使って、VLOOKUP関数で商品を検索するセル範囲を変更しています。
    MATCH(B11,B3:B8,0)　で最初に見つかる商品の行位置を計算します。商品が「肉」の場合は 2 となります。
    VLOOKUP関数ではこの次の行位置以降で検索すればよいので、　MATCH関数で求めた 2 の次の行番号と一致するように　+1　とします。
    INDE関数で　INDEX(B3:B8,MATCH(B11,B3:B8,0)+1)　とすると　INDEX(B3:B8,2)　は B5 を返します。
    よって、数式は　=VLOOKUP(B11,B5:C8,2,FALSE)　となりますので、種類「肉」の2つ目は「牛肉」と返すことができます。

スポンサードリンク

PageViewCounter

Since2006/2/27