どうも、毎日仕事が地道な調査でブログで発散している節のあるしがないITエンジニアです。
今回はExcelのMATCH関数について書いていきたいと思う。
目次
MATCH関数とは
検索範囲から検索値を探索し、最初に引っ掛かった値の相対的な位置を返す関数。
単体ではあまり使い道はないが、
INDEX関数と組み合わせることで真価を発揮すると言っても過言ではない。
検索範囲は１行もしくは１列のみ、行列を指定した場合はエラーとなる。
また、ワイルドカードを指定することができ、”?”（疑問符）や”*”（アスタリスク）が使用できる。
検索結果が見つからなかった場合はエラーを返すため、IFERROR関数、ISERROR関数を併用するとよい。
書式
MATCH(検査値, 検査範囲, [照合の型])- 検索値 検索対象の文字列、数値、論理値を指定（必須入力）
また、ワイルドカード”*”、”?”を指定することができる。 - 検索範囲 検索するセルの範囲を指定（必須入力）
- 照合の型 -1、0、1のいずれかを指定。通常は完全一致の0を使用するとよい
次の表は、照合の型に基づいて関数が値を検索する方法を示しています。
| 照合の型 | 動作 |
|---|---|
| 1 または省略 | 検索値以下の最大の値を検索する。検査範囲の引数の値は、 昇順の並べ替えでは、1 ～ 9、A ～ Z、あ～ん、FALSE ～ TRUE の順に配置される。 |
| 0 | 検索値と完全一致する最初の値を検索する。 |
| -1 | MATCHは、 の値以上の最小の値 lookup_value。 lookup_array引数 の 値は、TRUE、FALSE、Z-A、… など、降順に配置する必要があります。2、1、0、-1、-2、…など。 |
※生憎筆者は照合の型は0しか使用したことがない。今までの経験上、全く使用する機会が無かった。
仕様
- MATCH は、値自体ではなく、lookup_array 内の一致する値の位置を返します。
たとえば 、MATCH(“b”,{“a”,”b”,”c”},0) は、
配列 {“a”,”b”,”c”} 内の “b” の相対位置である 2 を返します。 - MATCH 関数では、英字の大文字と小文字は区別されません。
- MATCH 関数で検査値が見つからない場合は、#N/A エラー値が返されます。
- 検索の型 が 0 で、検索値が文字列の場合は、 検査値の引数で、
疑問符(?) やアスタリスク (*) をワイルドカード文字として使用できます。
ワイルドカード文字の疑問符は任意の 1 文字を表し、
アスタリスクは 1 文字以上の任意の文字列を表します。
ワイルドカード文字ではなく、通常の文字として疑問符やアスタリスクを検索する場合は、
その文字の前に半角のチルダ (~) を付けます。
動作確認
以下の例では、果物データ内に指定した果物があるかを確認している。
実行結果の通り、関数単体の機能は表内の検索結果の位置情報を取得する関数となっている。
また、検索結果が存在しない場合は、#N/Aというエラーが出力される。
※$C$4:$C$8の$（ドルマーク）はコピーした時に参照位置が動かないように固定する。
文字列前の＄で行（横方向）の位置を固定し、
文字列の後～数値の前の＄で列（縦方向）の位置を固定
関数の内容としては第一パラメータでB12のセルを参照し、
”きんかん”という文字列を検索値としている。
第二パラメータは果物データの”りんご”～”すもも”までのセルを検索範囲として渡している。
この際、＄でコピー＆ペーストした際に参照位置がずれないように固定している。
第三パラメータは完全一致の0を指定する。
=MATCH(B12,$B$4:$B$8,0)INDEX関数との組み合わせ
MATCH関数とINDEX関数と組み合わせることでセルの値を抽出することができる。
VLOOKUP関数やHLOOKUP関数でも同様のことができるが、
あちらはセルの値渡しであるのに対し、こちらはセルの参照渡しであるなど挙動に違いがある。（VLOOPUP関数と異なりそのままの値を抽出できる。）
=INDEX($C$4:$C$8,MATCH(B12,$B$4:$B$8,0)) INDEX関数の書式はINDEX(配列, 行番号, [列番号])となっており、
INDEX関数の第一パラメータは単価項目の”300”～”440″までのセルを渡している。
第二パラメータにMATCH関数を入れており”みかん”の位置情報を取得し行番号として使用している。
第三パラメータは省略している。
なお、第一パラメータは１列だけでなく複数列指定することも可能である。
IFERROR関数、ISERROR関数との組み合わせ
IFERROR関数との組み合わせ
前述の関数は検索対象が見つからない場合は単価と単価を参照している合計でエラーになる。
そのため、実際に使用する場合はIFERROR関数を組み合わせるとよい。
IFERROR関数の書式はIFERROR(値, エラーの場合の値)となっており、
第一パラメータを見てエラーの有無を確認し、
エラーが発生していた場合は第二パラメータの値を返す。
また、エラーが発生していない場合は第一パラメータを返す。
IFERROR関数の第一パラメータにはINDEX関数とMATCH関数の組み合わせを設定している。
第二パラメータには0を指定している。
今回の場合、INDEX関数、MATCH関数のいずれかでエラーが発生した場合に0を設定し、
エラーが発生していない場合は検索条件に引っ掛かった単価を取得する内容になっている。
=IFERROR(INDEX($C$4:$C$8,MATCH(B12,$B$4:$B$8,0)),0)ISERROR関数との組み合わせ
ISERROR関数はパラメータがエラーかどうかを判定し、
真偽値（TRUE（真）、FALSE（偽）を返す型）を返します。
IF文など真偽値で判定する関数と相性がいいです。
ISERROR関数の第一パラメータにMATCH関数を指定している。
これにより、MATCH関数がエラーの場合はTRUE（真）、
MATCH関数が正常終了した場合はFALSE（偽）が出力される。
=ISERROR(MATCH(B12,$B$4:$B$8,0))なお、ISERROR関数の実行結果は真偽値のため、IF文にそのまま条件として使用できる。
=IF(C12,B12&"は果物データに存在しない",B12&"は果物データに存在する")