Contents
行、列が交差した値を抽出する（INDEX,MATCH関数の使い方）
エクセルで作成した表が大きくなったり、データが大量になってくると、表内から目的のセルや値を探すのが大変になってきます。
そんな時に、列や行番号、または表の見出しを参考に目的の値を取り出してくれるのが、今回の2つの関数です。
=INDEX(配列, 行番号, 列番号)
[配列] = 直接「{ }」と「,（カンマ）」を使用して値を入力するか、または、参照するセル範囲を指定します。
[行番号] = 抽出したい対象の、[範囲]内における行番号を数値で指定します。（※[範囲]で指定した最初の行を1行目とします）
[列番号] = 抽出したい対象の、[範囲]内における列番号を数値で指定します。（※[範囲]で指定した最初の列を1列目とします）
| 関数の説明 | 指定された[行・列番号]を元に、[範囲]内のデータを抽出する。 |
|---|---|
| EXCELバージョン | Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016 |
=MATCH(検査値, 検査範囲, [照合の型])
[検査値] = 検索する値、またはセルを指定します。
[検査範囲] = 検索するセルの範囲を指定します。
[照合の型] = 省略可。「1」、「0」、「-1」のどれかの数値で指定します。省略すると「1」となります。
「1」・・・検査値以下の最大の値で検索します。検査範囲 を昇順に並べ替えておく必要があります。
「0」・・・検査値に一致する値のみが検索の対象となります。データの並べ替えは不要。検査値が文字列の場合は、ワイルドカード（あいまい検索）を使用することができます。
「-1」・・・検査値以上の最小の値で検索します。検査範囲 を降順に並べ替えておく必要があります。
| 関数の説明 | [検査範囲]の中で指定された[検査値]が何行目にあるかを求める。 |
|---|---|
| EXCELバージョン | Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016 |
使い方例）INDEX・MATCH関数
下の表は、ある学校の学期末テストの結果一覧表です。
生徒が多く、テストの種類が5教科ありますので、この中から一つの得点を探すのはとても目の疲れる作業となりますね。
そこで、INDEX・MATCH関数を使って、「桂大五郎の算数の点数」と、「小久保利通の席番号」をそれぞれ抽出してみましょう。
① 表の中から「桂大五郎の算数の点数」を求める。
[配列] ： 参照するセル範囲[C4:I25]を指定します。
[行番号] : [範囲]の中で、「桂大五郎」は8行目なので、「8」を指定します。
[列番号] : [範囲]の中で、「算数」は3列目なので、「3」を指定します。
=INDEX(C4:I25,8,3)
② 「小久保利通の席番号」を求める。
[検査値] ： 「小久保 利通」を「”」で囲んで指定します。 →”小久保 利通”
[検査範囲] : 検索するセル範囲[C4:C25]を指定します。
[照合の型] : 完全一致するデータを検索したいので、「0」を指定します。
=MATCH(“小久保 利通”,C4:C25,0)
以上で完了となります。関数の説明が難しく見えるのとは裏腹に、慣れれば使い方はそれほど複雑ではありません。
INDEX関数では、2つ以上の表が参照範囲として設定できるようになっており、その場合、上記とは少し違う以下のような数式の組み立て方が存在します。
=INDEX(範囲, 行番号, 列番号, 領域番号)
[範囲] = 参照する複数のセル範囲を、「,」で区切り全体を「( )」で囲んで指定します。
[行番号] = 上記と同様の指定方法です。
[列番号] = 上記と同様の指定方法です。
[領域番号] = 省略可。[範囲]で指定したセル範囲の、何番目の範囲を参照するかを数値で指定します。省略すると最初に見つかったセルが参照されます。
複数の範囲を指定したINDEX関数）
① 表の中から「トマトの金額」を求める。
[範囲] ： 参照する2つのセル範囲を「,」で区切り、全体を「( )」で囲んでを指定します。
（B4:C7 , E4:F7）
[行番号] : [範囲]の中で、「トマト」は4行目なので、「4」を指定します。
[列番号] : [範囲]の中で、「金額」は2列目なので、「2」を指定します。
[領域番号] : 2つ目のセル範囲を見たいので、「2」を指定します。
=INDEX((B4:C7,E4:F7),4,2,2)
以上となります。
複数の表や参照したいセル範囲がある場合には、こちらの方法で抽出をしましょう。
複数条件に合致するデータを取得する（INDEX,MATCH関数）
表内から、「○○で、かつ、××の場合の金額・・・」といった風な、『複数の条件』での抽出も可能です。
使い方例）INDEX・MATCH関数を使って複数条件の抽出をする
下の表は、あるパソコン教室の地域別1月度売上表です。右の売上高検索の欄を使って、①「支店名が東京本社」で、かつ、②「講座種別が初心者コース」の売上高を抽出してみましょう。
① 表の中から「東京本社の初心者コース売上高」を求める。
[配列] ： 参照するセル範囲[B4:D13]を指定します。
[行番号] : [範囲]の中で、「東京本社」で「初心者コース」の行を探したいので、MATCH関数を使って次のように指定します。
（1）「東京本社」のセルF4と、「初心者コース」のセルG4を「&」記号を使って結合して指定します。
→F4&G4（「東京本社初心者コース」という意味になります）
（2）「支店名」の範囲[B4:B13]と「講座種別」の範囲[C4:C13]も、「&」記号でそれぞれ結合するように指定します。
→B4:B13&C4:C13（各行ごとに結合された文字列が参照されます）
（3）完全一致するデータを検索したいので、最後に「0」を指定し、MATCH関数が完成します。
MATCH(F4&G4,B4:B13&C4:C13,0)
[列番号] : INDEX関数の[範囲]の中で、「売上高」は3列目なので、「3」を指定します。
これで、INDEX関数とMATCH関数を使った数式が以下のように完成しました。
=INDEX(B4:D13,MATCH(F4&G4,B4:B13&C4:C13,0),3)
しかし、実はこのまままでは「#VALUE」というエラーが出てしまい、抽出結果を正常に表示出来ません。
そこで仕上げとして、INDEX関数とMATCH関数を使い複数の行を参照したい場合などは、数式全体を「{ }」で囲って指定するようにして完了となります。（※最初の「=」も括弧の中に含める）
{ =INDEX(B4:D13,MATCH(F4&G4,B4:B13&C4:C13,0),3) }
INDEX,MATCH関数で複数該当した場合、最後に該当した値を抽出するには？
INDEXとMATCH関数を両方使用すると、様々な条件の抽出が可能となりますが、1つ注意しなければいけないことがあります。
それは、INDEX関数は「範囲内で（上から探して）最初に見つかった」セル（値）を結果として表示するという点です。つまり、参照する範囲内に「条件に有った内容が2つ以上存在する場合」には、2つ目以降は表示されないのです。
そのような時の解決方法の1つとして、「最初」ではなく「最後」に見つかったセル（値）を表示するように工夫してみましょう。
使い方例）「複数の条件に合う抽出結果」がある場合に、最後の結果を表示する
下の表はある地方議員の政務活動費一覧です。
この中から「タクシー代として最後に領収をもらった日」を検索したいと考えています。
左側の表内には「タクシー代」が複数存在しますが、通常のINDEX関数の使い方では、（上から探して）最初に見つかる『4月28日』が結果として表示されてしまいますので、条件内から一番大きい数値を表示する「DMAX関数」を新たに加えて工夫をしてみましょう。 →DMAX関数の詳細についてはこちら
① 表の中から「タクシー代として最後に領収をもらった日」を求める。
[配列] ： 参照するセル範囲[B4:F14]を指定します。
[行番号] : [範囲]の中で、「最後の日=一番大きい日付」で「タクシー代」の行を探したいので、MATCH関数とDMAX関数を使って、次のように指定します。
（1）DMAX関数にて次のように指定します。
表のセル範囲[B3:F14]内にて、条件[H3:H4]に合う「領収日（C3）」の最大値をまず求めます。
DMAX(B3:F14,C3,H3:H4)
次に、求めた最終日のセルと今回の検索対象である「タクシー代（セルH4）」を「&」記号で結合します。
DMAX(B3:F14,C3,H3:H4)&H4
これで、「タクシー代最後の領収日」を表す、『4月28日タクシー代』という意味になります。
（2）「領収日」のセル範囲[C4:C14]と「適用」のセル範囲[D4:D14]も、「&」記号でそれぞれ結合するように指定します。
→各行ごとに結合された文字列が参照されます。
（3）完全一致するデータを検索したいので、最後に「0」を指定し、MATCH関数が完成します。
MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0)
[列番号] : INDEX関数の[範囲]の中で、「領収日」は2列目なので、「2」を指定します。
これで、INDEX・MATCH・DMAX関数それぞれを使用した数式が以下のように完成しました。
=INDEX(B4:F14,MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0),2)
仕上げとして数式全体を「{ }」で囲って指定して完了となります。
{ =INDEX(B4:F14,MATCH(DMAX(B3:F14,C3,H3:H4)&H4,C4:C14&D4:D14,0),2) }
非常に複雑な数式となりましたが、その他にもOFFSETやMAX関数、COUNTIF関数を使用した方法もあるようですが、どの方法にしても『最後の検索値をどのように調べるか』がカギとなる数式の組み立てとなりますので、自分にとって、より分かりやすい式を色々研究してみるといいかもしれませんね。
