2024/1/16：INDEX関数

2025年1月18日 01:23

≪毎週木曜12:15～ZOOMで開催中！Excel関数お勉強会の内容です。》
※【2025/1/18】一部補足などの追記を行いました。

◇関数のざっくり紹介

特定の配列や範囲などの位置（行・列）を特定して、データを見つけます。

■INDEX関数

▼配列形式
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。INDEX 関数の 1 番目の引数が配列定数のときは、配列形式を使います。
=INDEX(配列, 行番号, [列番号])
　
▼セル範囲形式
指定された行と列が交差する位置にあるセルの参照を返します。隣接しない複数のセル範囲を指定した場合、その中から任意の領域を選択できます。
=INDEX(参照, 行番号, [列番号], [領域番号])

INDEX 関数 - Microsoft サポート

▽配列形式での書き方（というか基本的な使い方）

　=INDEX(配列, 行番号, [列番号])

公式説明に「INDEX 関数の 1 番目の引数が配列定数のときは、配列形式を使います。」とありますが、配列定数に限らず配列はすべて同様です。
このちょっとわかりづらい公式説明文が、何を言いたいかというと、次項で説明する「領域番号」での切り替えなどを使いたくても、第1引数が配列の場合は無理だよ！という事かと思います(´・ω・`)

※H21セル（上図で結果として「E」が出ている部分）には実際にINDEX式が入っています。
第1引数は「=INDEX(C17#,～)」のように範囲で指定してもいいし
（…厳密には範囲での指定は「セル範囲形式」項目で書くべきなんだろうけども）
{～}という風に配列定数で指定してもいいし、関数式結果の配列でもOK

▽セル範囲形式での書き方（むしろ領域番号の説明メイン）

=INDEX(参照, 行番号, [列番号], [領域番号])

・・・領域ってなんぞ？
と思うかもしれませんが、数式の第1引数の「参照」の部分に、
(範囲, 範囲)　という感じで書けば、複数の範囲を1つめの領域、2つ目の領域…（もっと指定できる）というように指定でき、第4引数の領域番号部分を「1」「2」などに切り替えれば、それぞれ1つめの範囲から、2つ目の範囲から・・・と切り替えて結果を返してくれます。

尚、先の項目でも書いた通り、この書き方は第1引数がセル範囲などの参照じゃないと使えません。（配列では使えません）
※ちなみにOFFSET関数は結果として参照を返すので使えます。
　（けどOFFSET関数は揮発性関数で重くなりがち。ご利用は計画的に。）
※第1引数の範囲を『(範囲➊,範囲➋…)』という様に、複数の範囲を
　指定して領域番号でを切り替える場合、「範囲」同士がすべて
　同じシート上にないと#VALUE!エラーになるので注意。
　（式を入力しているシートと範囲で指定したシートが違ってもそれはOK）
例）
『=INDEX( (Sheet1!●,Sheet1!▲) ,1,1,1)』をSheet2に記入➡問題なし
『=INDEX( (Sheet1!●,Sheet2!▲) ,1,1,1)』をSheet2に記入➡#VALUE!

先のC17#の範囲を1つめの領域、
↑で画面に映っているM17#の範囲を2つ目の領域として設定し、
領域番号を「2」としている場合の結果は「ｈ」

◇ INDEX関数の強み

➊基本的に表は、四角い範囲で扱われることが多いので
　大抵のことはINDEXで処理できる。（無理やりな処理を含む）

❷配列が扱える（特に2019以前で重要）
　※2019以前のVer.で結果を一気に出すにはCSE数式にする必要がある。
　（Ctrl＋Shift＋Enter の同時押しで入力を確定する数式）

　　尚、ネストしている場合等には、INDEX(,0)で囲っても配列が死んだり
　　生きたり、INDEX(,0)の多段階ネストで配列が生きる時も死ぬ時もあり、
　　正直なところ、決定的な基準は上級者でも判明できていない。
　　（現状、実際に配列処理ができるかは、実機で確認するしかない状態）

❸参照として結果を返すことができる。（後述）

❹配列内のデータを行番号・列番号を数字で指定できる。

➡
総合的な結果として、おなじみMATCH関数を始めとした様々な各種関数との親和性がとても高く、いい感じに組み合わせやすい。

◇ 一般的ではないINDEX関数の使い方紹介

行や列の0指定や、領域番号での指定もまぁあまり一般的には使われてないけど、まぁそのあたりは一応MS公式でも書かれている内容なので。

以下のような使い方は、Excel関数上級者層（Twitter：現XのExcel関数界隈）には割と普通（は言い過ぎ？）に使われてたりはする。（どのレベルから上級者とするかとかそういう話はスルーでお願いします。別に中級者としてもどちらでも誰も気にしないのでそういうのは好きなように捉えてください）

■任意の複数行や複数列を指定し、特定行や列全体を取得する

新関数でいう所の「CHOOSECOLS」「CHOOSEROWS」みたいな。

相方側（行の場合は列・列の場合は行）に1からの連番配列（範囲分）を指定しておく。
特定の複数行・複数列を指定する場合、配列の向き（縦配列・横配列）に注意。
ちなみに、Googleスプレッドシートでは使えない方法なので両方使っている人は注意！

■範囲を可変的に指定する

関数の結果として「参照」返せるというのはこういうこと。
この書き方は、『範囲を可変的に指定できるようにしたいけど、OFFSET関数やINDIRECT関数などの揮発性関数を使いたくない！』という時などに、ひっそり古の時代から一部のコア層が重宝していた技（？）。
なので、2019以前でも当然使えます。（スピルはしないけどね💦）

INDEX関数の結果が「参照」として返されているので、セル番地と同様に
範囲指定時の「:」（コロン）の前後につなげて範囲とすることが可能というわけ。

■数字の繰り返し配列を組合せて、自由自在にデータを抜き出す

ここまで変な抜き出し方は・・・まぁ普通はしないと思うけどｗ
この考え方はREDUCE関数などを使う時にも結構使えたりします。
自在に数字を操るための頭の体操は、ぜひ最終週の「数字・配列遊び回」に参加してね！（宣伝

■Excel2019以下のバージョン向けな使用例

・おなじみINDEX＆MATCH

=INDEX(B81:D85,MATCH(G81,B81:B85,0),MATCH(H81,B81:D81,0))
詳細はぐぐってください

・2016以下用のMAXIFS
SUMPRODUCTでもいけるけどね。ちなみにMINIFSは少々めんどい。
（このやりかただと、条件に一致しない物が0になるので）

=MAX(INDEX(($B$90:$B$98=$F$90)*($C$90:$C$98=$G$90)*$D$90:$D$98,0))
MAX関数だけでは配列同士の計算処理をしてくれないけど、
INDEX関数をかませることで配列処理が可能になる。

・結合セルの間をうめる
色んな方法があるけどね。1例として。
（実はLOOKUP関数が最強という説があるけど、それはまた機会があれば）

=INDEX($B$103:$B$111,MATCH(ROW(),INDEX(1/1/($B$103:$B$111<>"")*ROW($B$103:$B$111),0)))
=INDEX(【結合範囲】,MATCH(ROW(),INDEX(1/1/(【結合範囲】<>"")*ROW(【結合範囲】),0)))
=LET(範囲,【結合範囲】,INDEX(範囲,MATCH(ROW(範囲),1/(範囲<>"")*ROW(範囲))))
=SCAN("",【結合範囲】,LAMBDA(a,b,IF(b="",a,b)))　←INDEX関数関係なし

◇あとがき

何か知識が増えるような内容はありましたでしょうか？

昔からこういう感じのこねこね式を使っているので、割と何が一般的なのかそうでないのか、正直一般的な感覚がよくわからなかったりすることもよくありますが・・・関数BBAからの気持ちとしては、何か少しでも皆様の知識や感覚・楽しさの+になる事ができれば幸いです（*´∀｀*）

去年11月・12月のLAMBDA系とMMULT（ついでにSUMPRODUCT）で力尽きたので、少なくとも今後はしばらくゆるゆる気味で行きたいと思ってますので、何卒よろしくお願いいたしますm(_ _)m

基本毎週のつもりだったけど、元々も心身共に不健康民なので、たまにお休みしちゃったりしてるし、どこかに定期で休み入れた方がいいのかな？とも思ったり。（木曜に5週目がある月は、5週目は休みにするとかそういうの）

さてさて来週は何にしましょうかねー？
INDEXとくればMATCH?　全然違うのでもいいけどなー。
もしリクエストとかあれば、お気軽にどうぞーヾ(・ω・)ノ