INDEX関数とMATCH関数★★★
INDEX関数とMATCH関数の組み合わせは、マトリックスの集計において非常に強力です。
この関数の組み合わせにより、行・列それぞれに単一の条件を設定してマトリックス集計ができるだけでありません。 なんと、行・列で複数の条件を設定することもできます。 これを知ったとき、私は感動しました。
例えば、会社別と年月の組み合わせで売上高を算出したい場合に、INDEX関数とMATCH関数を使って効率的に集計できます。
まずは、INDEX関数の基本から見ていきましょう。
INDEX関数の基本
INDEX関数は、指定された行と列の交点にある値を返す関数です。
引数の2つ目と3つ目は番号なので、ここに直接数値を入れることもできますが、そのように使うことはほとんどありません。 通常は、MATCH関数を使って動的にセルの位置を取得して使います。
例として、テーブル表から「4月」「ミカン」の売上高である「1,232」をINDEX関数で抽出してみます。 引数の2つ目と3つ目に、直接数値を入れているのでこういう使い方はしませんが、INDEX関数の挙動の確認です。
INDEX関数の挙動確認_引数に直接数値を入れる
=INDEX($C$4:$N$7,2,4)実際には以下のように、 引数の2つ目と3つ目に直接数値を入れないで、MATCH関数を使い動的にセルの番号(位置)を返して使います。 事例は後ほど見ていきます。
MATCH関数の基本
MATCH関数は、Excelで特定の値を検索し、その相対的な位置を返す強力な関数です。 MATCH関数もこれ単体でつかうことは少ないです。
VLOOKUP関数やINDEX関数と組み合わせて使ことで、より柔軟で効果的なデータ操作が可能になります。
例として、MATCH関数を使って「ミカン」の行番号と「4月」の列番号を取得してみます。 その後、INDEX関数の数字をMATCH関数の値が返ったセルを指定し、「4月」「ミカン」の売上高である「1,232」を抽出します。
MATCH関数-単一条件検索
=MATCH(D11,$C$3:$N$3,1)次に、MATCH関数の複数検索を確認します。 行には担当者名と製品種類の2種類があります。 ここで「クチヒゲ」さんの「ミカン」の行番号を取得してみます。 その後、INDEX関数の数字をMATCH関数の値が返ったセルを指定し、「4月」「ミカン」の売上高である「1,232」を抽出します。
MATCH関数-複数条件検索
=MATCH(B25&C25,$B$4:$B$19&$C$4:$C$19,0)検索値と検索範囲をイメージで覚えましょう
Excelの関数に入力していると、「ダイアログボックス」が表示されます。 これをみながら、どの引数にどのセル(範囲)を指定すればよいかわかるので、実際には何番目の引数に何を入れるかの暗記は必要ありません。
- 検索値：これは1つのセルを指します。
- 検索範囲：これは複数のセルを指します。
INDEX関数とMATCH関数を組み合わせたときには、マトリックス表を指定することがわかります。 このイメージを覚えておきましょう。
INDEX-MATCHの組み合わせは強い！
先ほどのMATCH関数の事例でもありましたが、INDEX関数とMATCH関数を組み合わせることで、より柔軟で効果的なデータ操作が可能になります。
INDEX関数とMATCH関数は行と列の複数条件でも使えます。 これにより、データ分析の幅が大きく広がり、より高度な集計が可能になります。 エクセルを使う上で、これらの関数は是非とも知っておくべきです。
なお、 INDEX関数に直接MATCH関数を入れずに、MATCH関数は別のセルに入れて、そのセルをINDEX関数の引数に入れてもよいです。 セルに計算過程を分けるのは、何も恥ずかしいことではありません。むしろ、正しいアプローチです。 慣れないうちは、分けて考えることをお勧めします。 おそらく、10回くらい分けていると、そのうち自然と１つにまとまるようになります。
基本的な使用例
=INDEX(返す値の範囲, MATCH(検索値, 検索範囲, 0),MATCH(検索値,検索範囲,
0)) ※上記はMATCH関数で完全一致検索(引数の3つ目が`0`)していますが、近似値検索でも使えます。
複数条件の使用例
例えば、売上データを分析する際に、担当者別✖製品種別に売上数量を抽出したい場合、INDEX関数とMATCH関数を使って効率的に集計できます。 MATCH関数の行番号に複数条件を設定することで、担当者別✖製品種別の売上数量を抽出できます。
=INDEX(データ範囲, MATCH(会社名&年月), MATCH(会社名列&年月列,
0)) なお、販売数量データ-年間のデータベースがあれば、SUMIFS関数でも集計できます。 詳細は、「演習_01_SUMIFS関数の具体例」を参照してください。
このように、データベースからSUMIFS関数でマトリクス表(中間テーブル)を作成し、INDEX関数とMATCH関数を使ってマトリクス表から値を抽出するといったことが実務では頻発します。 たとえば、各連結修正仕訳のシートから、SUMIFS関数で精算表に集計します。 次に、財務諸表を作る際には、INDEX関数とMATCH関数を使って精算表から値を抽出したりします。
関連：VLOOKUP関数よりINDEX/MATCH関数を使う
完全一致が必要な場合にまでVLOOKUP関数を使用するのはおすすめしません。少なくとも私は使っていません。 代わりに、INDEXとMATCHの組み合わせを使用することで、データの精度を保ちつつ効率的に検索が行えます。 VLOOKUPでは検索値が最初の列にある必要があり、参照元にジャンプするショートカットキー( Ctrl +[ )が使えないため、特に経理やバックオフィスの業務において非常に不便です。
詳細は、「VLOOKUP関数よりINDEX/MATCH関数を使う」を参照してください。