【Excel】かっこいい？MATCH関数の使い方

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

ちょっとかっこいいと思ったMATCH関数の使い方をご紹介します。

MATCH関数について

今回紹介するのは、エクセルに備わっているMATCH関数です。
まず初めに、MATCH関数の基本的な使い方をご紹介しようと思います。
MATCH関数は、指定した値を指定した範囲から探し「範囲の中の何番目にあるのか」を教えてくれる関数です。
使い方は、MATCH(検査値、検査範囲、[照合の型])
の順に指定します。
①検査値：これが探したい値です。
②検査範囲：①で設定した値を探したい範囲です。
③照合の型：これはどのような検索方法をするか、です。
　ここには1,0,-1の値が設定でき、
　1：検査値以下の最大の値
　0：検査値に完全一致する値
　-1：検査値以上の最小の値
　を検索します。
例として、次の図のような場合にMATCH関数を用いると、
「銃士A」の検査範囲内での位置、「3」が返されます。

かっこいい？使い方

ゲーム開発でマスターデータをエクセルで作成している場合、よくVLOOKUP関数というものを用いて、キャラや装備に対応したコード等を検索するということがあると思います。このVLOOKUP関数は便利な関数なのですが、一つ欠点があり、それは「基準値より左側のセルを検索できない」ということです。
しかし、ゲーム開発ではたまに、コードから逆にキャラを検索したい！という場面に出くわしたりします。
そんな時、MATCH関数とOFFSET関数という二つの関数を組み合わせることで、VLOOKUP関数できなかった、柔軟な検索が可能になります。

◎VLOOKUP関数
VLOOKUP関数は、VLOOKUP(検査値、検査範囲、列番号、検査方法)と指定することで、検査範囲の一番左の列の中から検査値を見つけ、列番号に対応した値を出力しれくれる関数です。この関数は、前述のとおり、「検査値よりも左側の列を検索できない」という性質を持っています。
例として下図の赤矢印方向には検索できますが、青矢印方向の検索はできません。

つまり、VLOOKUP関数では、上図の「キャラからコードの検索」は可能ですが、「コードから逆引きでキャラを検索」することは(列追加等しない限り)出来ないのです。
しかし、OFFSET関数とMATCH関数を組み合わせると、VLOOKUP関数ではできなかった、左側検索が可能になります。

◎OFFSET関数
OFFSET関数は基準となるセルから指定した行・列数分だけ移動したセル内容を返す関数です。
OFFSET(基準,行数,列数)で指定し、基準から行数、列数分だけ移動した場所の内容を教えてくれます。下図の例では、「キャラ名」から下へ2行、右へ1列だけ移動したセルを参照しています。

通常、行数や列数の指定に正の値を入れると、基準のセルより下の行、右の列を対象に移動します。ただ、このOFFSET関数は「マイナス」の行数列数が指定でき、基準セルより上側・左側にも移動可能です。

◎かっこいい？使い方
そこで、
OFFSET(基準セル , MATCH(検査値,検索範囲,0)-1 , -1)
のように、OFFSET関数の行数指定にMATCH関数を用いると、MATCH関数で見つけた場所まで移動し、その後左側へ1列移動する、「左側検索」が可能になるのです！

ここで行数指定を
MATCH(検査値,検索範囲,0)-1
と、「-1」しているのは、OFFSET関数が基準セルを「0番目」と数えるのに対し、MATCH関数は基準セルを「1番目」と数えるためです。

終わりに

いかがでしょうか。実はINDEX関数とMATCH関数の組み合わせでも同様のことが可能です。また今回はVLOOKUP関数を比較対象にしましたが、同様にHLOOKUP関数の代用としても使えます。この技には検索方向の制約はなく、応用すればどこのセルでも検索対象にすることが可能になります。
しかし、複数の関数を組み合わせている以上、後から見て分かりにくくなることには注意が必要です。