INDEXは検索関数の一つで、VLOOKUPよりは知名度は低い関数ですが、柔軟な検索が可能となっており、VLOOKUPではできない検索も可能となります。INDEX単体で使ってもあまり意味がなく、MATCHなどとセットで使うのが基本だと覚えてください。MATCHの使い方も一緒に解説します。
エクセル関数-INDEX
目次
INDEX関数の使い方
基本的な使い方
INDEX関数の使い方について実際の表をもとに解説します。下の表は500人が受けた5教科の学力テストの結果を集計した表です。
IDを入力すると右側に各教科の点数が抽出されるように関数を入力する
VLOOKUP関数で使用したものと同じ表から、同じように検索IDを入力すると各教科の点数が表示されるように、INDEX関数を使って再現します。
INDEX関数の作り方
INDEX関数の基本構成
ポイント
INDEX関数は①配列の中から指定した行番号（下方向に何番目か）と列番号（右方向に何番目か）にある値を返す関数です。
| ①配列 | 抽出したいデータを全て含む範囲を指定します。 ここではセルC8からセルI507。 |
| ②行番号 | ①配列の中で何行目（下方向）を抽出するか指定する。 ここでは5を入力しているのでID00005の行を指定。 |
| ③列番号 | ①配列の中で何列目（右方向）を抽出するか指定する。 ここでは2を入力しているので社会の列を指定。 |
上記の返り値はID00005の社会の点数である15になります。これがINDEX関数の基本的な使い方です。
MATCH関数と組み合わせて使う
冒頭でも述べましたが、INDEXはMATCHと組み合わせて使って初めて本領を発揮する関数です。上記の②行番号や③列番号に数値を入れて指定するのではなく、MATCHの検索結果によって入る数値が変わるようにします。MATCHを使ってセルC4に入力する数式は次の通りです。
※MATCHの詳細はこちらを確認ください。
行番号と列番号にMATCHをセットする
あとはこの数式をコピーしてセルD4からセルI4まで貼り付けすれば、入力された検索IDに応じた結果が各項目に返ってきます。下が完成した表になります。検索IDを打ち換えればそれに応じた点数結果が返ってくるようになります。
完成した数式を貼り付けた結果の表
領域番号を使って複数の表を切り替えて参照する
やりたいこと
1年生・2年生の点数結果が入力された2つの表から、セルB2に学年を入力、セルB4に受験者IDを入力すると全ての点数結果が表示されるようにする。
少し難しくなりますが、INDEXでは複数の表を切り替えて参照することができます。参照、行番号、列番号、領域番号の4つの引数を指定します。かなり長い数式になりますが、セルC4に入力する数式は下の図を参照ください。INDEXの中に2つのMATCHがあり、それぞれのMATCHの中にINDEXがあります。
セルC4に入力する数式
3つのINDEXの参照と領域番号については下の図を参照ください。セルB2に領域番号を入力して参照する表を切り替えます。1を指定した時は1年生の表を、2を指定した時は2年生の表を参照します。
3つのINDEXの参照と領域番号
数式の全体構成は下の図になります。INDEX-Aが全体になり、その中にMATCH-XとMATCH-Yがあり、さらにその2つのMATCHの中に、それぞれINDEX-BとINDEX-Cがあります。
数式の全体構成
| ①参照 | 参照したい複数の表をカンマで区切って指定する。 ④領域番号の値に応じた範囲を参照する。 ここでは1つ目に1年生の点数結果があるC8:I12を 2つ目に2年生の点数結果があるC16:I20を指定する。 |
| ②行番号 | ①参照の表から抽出する行番号を指定する。 ここではMATCH-Xの返り値が行番号となる。 （MATCH-Xについては下記参照） |
| ③列番号 | ①参照の表から抽出する列番号を指定する。 ここではMATCH-Yの返り値が列番号となる。 （MATCH-Yについては下記参照） |
| ④領域番号 | ①参照で指定した複数の範囲の中から選択したい番号を指定する。 カンマで区切った範囲を1つの領域とし、一番左の範囲を1とする。 ここでは1を指定すると1年生の点数結果範囲（C8:I12）、 2を指定すると2年生の点数結果範囲（C16:I20）となる。 |
次はMATCHとその中にあるINDEXについて。MATCHの詳細は下記を参照ください。①検査値の値が②検査範囲の中で何番目にあるかを完全一致方式で検索して値を返します。①検査値に指定したセルB4に入力された検索IDを②検査範囲の何番目にあるのかを返します。②検査範囲の値は次に説明するINDEXによって返ってくるセル範囲になります。
MATCH-XとYの解説
MATCH-Xの中にあるINDEX-Bは、1年生と2年生の受験者ID範囲を④領域番号によって切り替えて返します。
④領域番号が1の時は1年生の受験者ID範囲（B8:B12）を返し、2の時は2年生の受験者ID範囲（B16:B20）を返します。
INDEX-BとCの解説
MATCH-XとINDEX-Bは受験者IDを検索する縦方向の検索だったのに対し、MATCH-YとINDEX-Cは科目を検索する横方向の検索になり、内容は同じです。
一つ一つの数式については以上で、まとめると下のような流れになります。
領域INDEXの流れ
- セルB2の値によって3つのINDEXの①参照が確定する。（1の時は1年生、2の時は2年生の範囲になる）
- セルB4に入力した検索IDをINDEX-Bの中から何行目にあるかを
MATCH-Xで検索して返す。 - INDEX-Cの中から指定された科目が何列目にあるかを
MATCH-Yで検索して返す。 - ２と３で返ってきた行列番号をINDEX-Aの参照範囲に当てはめて該当する行列番号の値を返す。
MATCH関数の使い方
MATCHは検査値が検査範囲の中で何番目に位置するかを調べる関数です。照合の種類の設定によって検査方法が変わり、返り値が変わります。使い方は下の通りです。
完全一致の検索
個人的には一番使用頻度が高い検査方法になります。検査値と完全に一致する値を検査し、一番最初に一致した位置を返します。
①検査値が②検査範囲の中で何番目にあるかを返す
MATCHの構成
ポイント
| ①検査値 | 検査したい値を入力するセルを選択する。ここではセルB4を選択する。 |
| ②検査範囲 | 検査する範囲を指定する。ここではセルB8からB507までを選択する。 |
| ③照合の種類 | 検査方法を3つの中から指定する。 1を指定：検査値以下の最大値を返す。ただし検査範囲を昇順に並べておかないと正しい結果が得れない。 0を指定：検査値に完全に一致する値を検索し、最初の値を返す。 -1を指定：検査値以上の最小値を返す。ただし検査範囲を降順に並べておかないと正しい結果が得れない。 ここでは0を指定する。 |
セルB4に入力された検索IDが、セルB8からセルB507の範囲内で上から何番目にあるかを返す数式を作りました。これが上記の【MATCH関数と組み合わせて使う】の②行番号の正体です。上記③列番号はこれの横バージョンになりますので、同じ要領で作ってもらえればと思います。
近似値の検索
？以上～？未満の範囲にある場合はこうなりますというような表から位置を特定する時はこちらを使用します。例えば一般のサラリーマンの方の給与から控除される所得税や健康保険料には、下記のような表（あくまでも参考です）によって控除される金額が決定する仕組みになっています。
入力された給与金額によって等級が決まる表
近似値（？以下の最大値）を検査する場合は最後を1にする
ポイント
このような表から検査値の位置を特定する場合は、③照合の種類を1にします。そうすることにより検査方法が変わり、①検査値以下で最大値がある位置が返ってきます。上記の図では検査値335,000を②検査範囲の中で検査し、335,000以下で最大値にあたる300,000が特定され、②検査範囲の上から6番目に位置するため6が返り値として返ってきます。
※照合の種類を1（-1）にして検査する場合は、検査範囲が昇順（降順）に並んでいないと正しい結果が返ってきません。
まとめ
INDEXはMATCHと組み合わせることにより、VLOOKUPよりも柔軟なデータ抽出が可能となります。少し長い数式になり始めて見る方には抵抗があるかもしれませんが、一つずつ基本から覚えていけばそれほど難しくはありません。まずはMATCHの基本をマスターしてください。それを利用することによりINDEXを自由自在に操ることができ、VLOOKUPではできないような複雑な表からのデータ抽出が可能となります。
本サイトでは常に最新機能を兼ね備えるMicrosoft365 PersonalのExcelを使用しております。旧バージョンでは使用できない便利な機能や最新関数が盛りだくさんなのでぜひ下記よりご購入ください。
[/st-mybox]
