【EXCEL | XMATCH関数】配列や範囲から一致する場所を探す!

スポンサーリンク

配列や範囲から一致する場所を探す!

今回は、配列またはセル範囲内で指定された項目を検索し、最初に一致した項目の相対的な位置を返すXMATCH関数を紹介します。

おなじみのMATCH関数の上位互換関数みたいな感じです(^o^)

この関数も、スピル機能が搭載されたことでできた、新しい関数です(^o^)

XMATCH関数

構文

= XMATCH( 検索値, 検索範囲, [一致モード], [検索モード] )

検索値・・・検索する値の設定(必須)

検索範囲・・・検索する範囲、または配列を指定。1行か1列で設定する(必須)

[一致モード]・・・「0」完全一致、「-1」完全一致、または次に小さい項目、「1」完全一致または次に大きい項目、「2」ワイルドカード文字との一致(省略した場合0の完全一致) ※ワイルドカード文字:「?」任意の一文字、「*」任意の一文字

[検索モード]・・・検索の向きの指定。「1」先頭から末尾hげ検索、「-1」末尾から先頭へ検索、「2」バイナリ検索/昇順で並べ替え、「-2」バイナリ検索/降順で並べ替え。(省略した場合1先頭から末尾)

使い方

次のサンプルで使い方をみていきましょう。

一致モード・検索モードを省略する

一番基本の形である、一致モードと検索モードの引数を省略した形です。

F3セルに次の数式を入力します。

= XMATCH( E3, A:A )

検索値にはE3を指定して、ワークシート上のセルに入力した値を使います。検索範囲はA列を列ごと指定(A:A)しています。

引数「一致モード」と「検索モード」は省略しているので、それぞれ、一致モード:0の完全一致、検索モード:1の先頭から末尾へ検索となります。

E2セルに「からあげ」と入力すると、次の図のように数式を入れたF3セルには2が表示されます。

指定したA列で何番目に、「からあげ」があるかを探してくれます。

今回、範囲を列ごと指定しているので、変える値は行番号と同じ値になります。

範囲を、「A2:A11」と指定すると「1」が返ります。

基本のこの形では、単純に検索値が範囲の上から(行方向に範囲を指定した場合には、左から)何番目にあるかを返します。

一致モード

一致モードを指定した、次の数式をF3セルに入力します。

= XMATCH( E3, C:C , -1)

数値の検索をするので、検索範囲はC列(C:C)にします。

一致モードの引数は、-1(完全一致、または次に小さい項目)に指定。

サンプルのE3セルのは、適当に155を入れてみます。

一致モードの引数が「-1」の場合、完全一致したものがない場合、次に小さいものの場所が返されます。

サンプルの場合、検索値を155にしているので、完全一致した数値はありません。次に小さい数字は150なので、10番目の10が返ります。

感の良い方ならもう分かっていると思いますが、一致モードの引数を「1」にすると、次に大きい300が選ばれ、7が返ります。

= XMATCH( E3, C:C , 1)

一致モード「2」は、ワイルドカードの指定が出来ます。

文字列の検索のために、検索範囲をA列(A:A)にします。複数の答えを一気に配列で返すために、検索値も範囲で指定しています。

【ワイルドカード

「*」は、任意の文字列、「?」は、任意の1文字となります。これを組み合わせてあいまい検索を実現出来る記号です。

検索地に指定した、あいまい検索に一致する文字列がF列に返っていることが分かります。

検索モード

検索モードについては、大きく通常の「1」と「-1」、バイナリ検索の「2」と「-2」があります。

通常の「1」「-1」は、簡単に言うと検索値が複数一致した場合、一番最初に見つかったものの場所を返すか、最後に見つかったものの場所を返すのかの違いになります。

サンプルで確認します。

検索モード1(省略したときと同じ)

= XMATCH( E3, B:B, 0, 1 )

検索モード「1」のときは、検索範囲の上から(行方向の時は左から)検索をします。一番最初に見つかったものの場所を返すので、この場合「2」が返ります。

検索モード-1(省略したときと同じ)

= XMATCH( E3, B:B, 0, -1 )

検索モード「-1」のときは、検索範囲の下から(行方向の時は右から)検索をします。一番最初に見つかったものの場所を返すので、この場合「7」が返ります。

※結果的に、範囲の一番最後の一致が返るのと同じ

バイナリ検索「2(昇順で並べ替え)」、「-2(降順で並べ替え)」

ここでは、あまり詳しくやりませんが、VLOOKUPのなどの関数と同じ様に、XMATCH関数も検索に時間がかかる関数です。

もともとのデータを降順や昇順に並べ替えた状態にするとこのバイナリ検索モードが使え、高速に検索することができるようになります。

まとめ

XMATCH関数を紹介しました。単体で使うというよりは、他の関数と組み合わせて威力を発揮する関数です。