広告

INDEXとMATCH関数の組み合わせで行列の交差値が一瞬で取得出来るよ

2017年11月3日

膨大なデータ表から「行と列の交差する値」を一発で取得出来たら...

作業スピードの大幅UPに繋がりますよね(^^)

あひる
あひる
行と列の項目が可変しても値を取得できてる...おぉw

今回は以下2つの関数である

  • INDEX関数
  • MATCH関数

この2つを組み合わせ、行と列の交差値を一瞬で取得する方法についてご紹介させていただきます。

OYAKUN
OYAKUN
INDEX・MATCH関数を組み合わると、行列の交差値が簡単に取得できてしかも早い!

INDEX・MATCH関数を組み合わせた使い方

以前「INDEX関数の使い方」と「MATCH関数の使い方」についてご紹介させていただきました。

それぞれの関数の用途を簡単に説明すると

  • INDEX関数は指定した行・列の値を取得することができる
  • MATCH関数は検索値のセル位置を取得することができる

というもの。

INDEX関数とMATCH関数の定義をもう一度おさらい↓

INDEX関数の定義

または

MATCH関数の定義

では行列の交差値を取得したい場合、INDEX・MATCH関数の組み合わせではどのように組み合わせていけば良いのでしょうか?

INDEXとMATCH関数の組み合わせで行・列の交差値を取得する

INDEX関数とMATCH関数を組み合わせて「行・列の交差値」を取得するには、INDEX関数の中にMATCH関数を挿入して使います

INDEX × MATCH関数の例

以下のようにINDEXとMATCH関数を組み合わせた場合は、

=INDEX(A1:D4、MATCH(A1、2、0)MATCH(B2、2、0)

INDEXとMATCH関数を組み合わせた例

以下のような書式になります。

=INDEX(配列、MATCH(検索値、行番号、検索の種類)MATCH(検索値、列番号、検索の種類)

意味としてはINDEX関数の行番号・列番号MATCH関数で指定するわけですね(^^)

以下例題にて、式の作り方を解説していきます。

INDEX・MATCH関数で行列の交差値を取得する例

テーブルの行列が交差する値(値段)を、INDEXとMATCH関数で求めます。

ここで扱うテーブルとは「表」のことです。

上のテーブルから交差値を求める場合、例えば

  • セットが「セット1」
  • サイズが「S」

だとしたら、値段は「¥300」となります↓

セット1・サイズがSのとき¥300になる
あひる
あひる
セットの種類とサイズに応じた値段が求められる、という式を作るんだね

今回はE1セルに「INDEX+MATCH」関数の式を作っていきます。

E1セルにINDEX×MATCH関数を設定する

INDEX + MATCH関数で行範囲を指定する

まずINDEX関数とMATCH関数を組み合わせて、検索対象となる行範囲を指定します。

1. INDEX関数で検索範囲を指定する

初めにINDEX関数を挿入して検索したい配列(範囲)を設定します。

=INDEX(B4:D6,
INDEX関数に範囲を設定する

2. MATCH関数を挿入して行・列番号を取得する

次にINDEX関数の行番号を取得するために、MATCH関数を挿入します。

「MATCH(B1、」と入力して行番号の「検索値」を指定します。

=INDEX(B4:D6,MATCH(B1,
INDEX関数の中にMATCH関数を挿入する

3. MATCH関数の行番号を探す「行範囲」を指定する。

項目は、セット1〜セット3から任意のセルを選択するので「A4:A6」と入力して行の範囲を指定します。

=INDEX(B4:D6,MATCH(B1,A4:A6,
INDEX関数の中のMATCH関数に行範囲を指定する

4. MATCH関数の「検索の種類」を指定します。

完全一致の時の行番号を取得したいので「0」と入力して、カッコを閉じます。

=INDEX(B4:D6,MATCH(B1,A4:A6,0),
1つ目のMATCH関数のカッコを閉じる

ここまででINDEX関数の「=INDEX(配列、行、」までを指定する式を作成しました。

MATCH関数で列範囲を指定する

次に「列番号」を取得するために、もう一度MATCH関数を挿入します。

1. 列番号を取得するため再度MATCH関数を挿入する

INDEX関数の列番号を取得するために、再度MATCH関数を挿入し「列番号の検索値」を指定します。(例として「B2」を選択します)

=INDEX(B4:D6,MATCH(B1,A4:A6,0),MATCH(B2,
INDEX関数の中に2つ目のMATCH関数を設定する

2. MATCH関数の列番号を探す「列範囲」を指定する。

再度挿入したMATCH関数の列番号を探す範囲に「B3:D3」と入力します。

=INDEX(B4:D6,MATCH(B1,A4:A6,0),MATCH(B2,B3:D3),
2つ目のMATCH関数に列範囲を指定する

3. MATCH関数の「検索の種類」を完全一致で指定する

MATCH関数の「検索の種類」を完全一致(0)で指定して、カッコを閉じます。

=INDEX(B4:D6,MATCH(B1,A4:A6,0),MATCH(B2,B3:D3),0))
2つ目のMATCH関数のカッコを閉じる

INDEX関数も終わりなので、カッコ閉じ2つ➜ ” )) ” でINDEX+MATCH式を閉じましょう。

ここまでおつかれさまでした!次は実際に使ってみます(^^)

次のページへ >