位置を特定するMATCH関数
エクセルを使って仕事をしていると目的のものがどこにあるのかを探す必要が出てきます。
MATCH関数はそんな目的のものの位置の列位置又は行位置を返してくれます。
引数は=MATCH(検索値,範囲,照合の種類)以上です単純な関数ですがその分使える関数です。
上の表では『福島』の位置が範囲指定の一番上から相対的に何行目にあるのかが返ってきます。まあこの程度の表で使うことは通常は無いと思いますが、何かと使える関数です。
VLOOKUP関数と合わせて使う
MATCH関数を単独で使う場面は少ないかもしれません。しかし、他の関数と組み合わせることで大き
な効果を発揮します。
例えばVLOOKUP関数の引数である列番号などが代表的ではないでしょうか。
VLOOKUP関数の引数『列番号』をMATCH関数を使って指定しています。
検索値は今までも出てきましたが、上の表の様にセル指定でも直接入力でもどちらでも可です。
範囲指定も今までの関数と同様にセル範囲を指定します。
セル指定やセル範囲していの時は相対参照か絶対参照かを意識して入力して下さい。
問題は照合の種類で0、1、-1の３つの中から目的にあったものを使用します。
この照合の種類について簡単に説明すると
〇『0』は検索値と一致する値の場所を返します
〇『1』は検索値と一致するものが無い場合、検索値より一つ小さいものの場所を返します。
〇『-1』は検索値と一致するものが無い場合、検索値より一つ大きいものの場所を返します。
『0』以外の照合の種類を指定する場合は注意点が『1』検索対象を昇順(小さい順)に並べて置く『-1』検索対象を降順(大きい順)に並べて置く必要があります。
『0』以外を使える場面がありますので後ほど紹介します。
では実務でMATCH関数が必要となるのはどの様な場合なのか次の表を見て下さい。
上の表は給与データをG1から貼り付けA列からE列に必要な項目だけを持ってきた表です。
VLOOKUP関数の『列番号』を指定する位置にMATCH(C$1,$G$1:$AV$1,0)で『総支給額』欄のG列からの相対『列番号』を持ってきています。
これでデータの項目が違う位置に移動したとしても違うデータを持ってきてしまったり、列数をいちいち数えなおす必要が無くなります。
MATCH関数の引数『範囲』はデータ項目の移動に適応できる様に『範囲』指定しておきましょう
ではMATCH関数の照合の種類で『1』や『-1』を使う場面とはどんな時でしょう。
一つには検索値が曖昧な場合で、もし無くても近い値の場所を返したい時。もう一つは一番端の場所を知りたい時です。
『1』の場合であれば存在しえない大きい値を検索値に指定する。『-1』の場合はその逆です。
行数と列数を指定して値を取り出すINDEX関数
INDEX関数もMATCH関数同様に単独ではほとんど使うことは無いかもしれません。
INDEX関数の引数は下記の通り2種類あります
① =INDEX(配列,行番号,列番号) もしくは
② =INDEX(参照,行番号,列番号,領域番号)
引数の配列と参照は同じ様でちょっと違いがあります。
①のパターンは実務ではほとんど使う場面は無いと思いますので、別な機会に解説します。
ここでは②のパターンを解説していきます。
引数の参照は範囲を指定します指定の仕方は他の関数と同様 A2:D5の様にします。
参照の範囲は一つもしくは複数の範囲をカンマで区切り複数の場合は（ ）で囲います。このカッコ内
の範囲が左から領域1、領域2として扱われます。
ちょっと式が複雑ですが解説していきます。
=INDEX((領域1,領域2),行番号,列番号,領域番号)となっています。
行番号と列番号はMATCH関数で求めています。
実際にはこの様な表は余り無いとは思いますが年度別に分かれた表や、支店別の表等を参照しなければ
ならない様な場面には使えるのではないでしょうか。
その時は領域番号はどこかで入力できる様にする必要があるでしょう。
参照の範囲は通常で使うのは恐らく一つでしょう。一つの場合『領域番号』は省略します。
INDEX関数で行番号を省略すると『1』を指定したことになります。=INDEX(参照,,1)
また参照そのものが１列しか無い場合は列番号も省略することが出来『1』を指定したことになります。
=INDEX(参照,1,)又は=INDEX(参照,,)も可能です。
領域番号も省略すると『1』を指定したことになります。しかし、行番号も列番号も省略することはほとんど無いと思います。
MATCH関数と合わせて使う
INDEX関数は行数と列数を指定して値を取りだせるのとMATCH関数はその行数と列数を返してくれます。
この二つを組み合わせることでVLOOKUP関数と同様に必要な値を取り出すことが可能になります。
「だったらVLOOKUPで良いのでは？」と思のは当然ですが、VLOOKUP関数では出来ないことが出来るんです。
VLOOKUP関数では出来ないこと。それは検索対象の列の左側のデータを取り出すということ。
Excel2019以降ではXLOOKUP関数という新しい関数で同様のことができますが、実際にはそれより前のExcelがまだまだ主流です。
実務では良くあることですが、実際に求められる表と元となるデータの並び順が違うということは良くあることです。
元データの左側にVLOOKUPの検索対象をコピーしてしまうことも可能ですが、それが叶わない場面も多々あります。
そんな時にはINDEX関数にMATCH関数を組み合わせることで上表の様に目的の値を取り出すことが出来てしまいます。