残業を減らす！Officeテクニック
Excelの新関数「XMATCH」関数は使いこなせば強力なツールに！
INDEX関数と組み合わせればクロス表から欲しいデータを一発取得できる
2022年1月17日 06:55
今回は、XLOOKUP関数とあわせて公開された「XMATCH関数」の定番の使い方を紹介したいと思います。「XMATCH」（エックス・マッチ）は、従来からあるMATCH関数の上位互換の関数です。INDEX関数と組み合わせてVLOOKUP関数よりも便利に使っているよ、という人も少なくないはず。
XLOOKUP関数と比べると霞んでしまいますが、引数のオプションはXLOOKUP関数と共通しており、MATCH関数よりもスマートに利用できるはずです。Excel 2021とMicrosoft 365で利用可能です。
※本稿の内容はプレビュー版である「Office LTSC Professional Plus 2021 Preview」で動作確認しています。
検索値の相対位置を求める「XMATCH関数」
XMATCH関数は、検索値の「相対位置」を取得します。でも、「相対位置」って言われてもよくわからないですよね。まずは構文を見てください。［検索値］が［検索範囲］の何番目にあるかを調べるために利用する関数です。［検索範囲］の先頭のセルを「1」として数えた「相対位置」が結果として返されます。
必須の引数は最初の2つ。3、4つめの引数は省略可能です。それぞれの引数には以下の意味があります。
- ［検索値］：検索する値。全角／半角は区別するが、英字の大文字／小文字は区別しない
- ［検索範囲］：検索値を検索する対象のセル範囲。1列または1行で指定する
- ［一致モード］：[検索値]と一致の判定基準を指定する。0、または省略で「完全一致」（任意）※
- ［検索モード］：検索方向を指定する。1、または省略で先頭から末尾（任意）※
※ オプション値については後述
上から“何番目”がポイント
実際の動きを見てみましょう。以下は都道府県別の人口と面積、県庁所在地の一覧表です。例えば「さいたま」が指定したセル範囲の「何番目」にあるかを調べてみます。セルH2に「=XMATCH(G2,E2:E48)」と入力しました。
セルE2～E48の範囲で「さいたま」は上から11番目なので、結果は「11」です。だから何なのかという結果ですが、これがXMATCH関数の基本動作です。引数［一致モード］は省略したので「完全一致」と判断されています。従来のMATCH関数で必須だった「完全一致：0」の引数を省略できるのは楽です。
定番のINDEX関数との組み合わせ
XMATCH関数で求めた「何番目」の結果を他の関数で利用するのが定番の使い方です。先ほど入力した数式にINDEX関数を組み合わせてみます。INDEX関数の構文は以下の通り。
［配列］はセル範囲と考えてください。その範囲の［行番号］と［列番号］が交差する位置にある値を取得します。ここでは［配列］を「人口」列のセルC2～C48の1列なので［列番号］は省略、［行番号］に先ほどH2セルに入力した「=XMATCH(G2,E2:E48)」を入れます。つまり、XMATCH関数で求めた「11」がセットされるので結果は……。
［配列］に指定したセルC2～C48の範囲で「11番目」（=XMATCH(G2,E2:E48)の結果）は「7,346,836」となりました。お気づきかもしれませんが、検索対象のセル範囲は、E列の「県庁所在地名」です。VLOOKUP関数の弱点である『検索値の対象セル範囲を左端に配置しておく』ことがクリアできています。VLOOKUP関数ではなくて、INDEX＋MATCH関数が使われる理由のひとつです。XMATCH関数でも同様で［一致モード］を省略可能な分、便利になっているといえます。
もうひとひねりしてみましょう。同じく「県庁所在地名」をキーとして、「都道府県」「人口」「面積」の項目を切り替えて結果を表示できるようにしてみます。各項目の値を含むセル範囲はセルB2～D48なので、INDEX関数の［配列］を「B2:D48」に変更します。［列番号］は、XMATCH関数で取得します。
［一致モード］と［検索モード］の意味
前述の通り、XMATCH関数の3、4つめの引数［一致モード］と［検索モード］の指定方法は、XLOOKUP関数の引数［一致モード］と［検索モード］と同じです。
どの値を一致と見なすかを指定する［一致モード］
［検索値］に対して一致と見なす基準を［一致モード］で指定します。省略した場合は、0（完全一致）となります。「1」は指定した値を超えない、「-1」は超える値を一致と見なします。「-1」と「1」は、近似一致として動作します。「2」は「*宿」といったワイルドカードを含む文字列を［検索値］に指定し、「*宿」であれば「新宿」や「原宿」が検索されます。
- 0：完全一致
- -1：完全一致、または次に小さい項目が一致する
- 1：完全一致、または次に大きい項目が一致する
- 2：ワイルドカードの文字と一致する
検索する方向を指定する［検索モード］
［検索モード］は［検索範囲］の検索方向を指定します。省略した場合は、1（先頭から末尾）となります。「2」「-2」は高速検索する場合に利用します。このオプションを利用するには、あらかじめ［検索範囲］を昇順、または降順で並べ替えておく必要があります。XMATCH関数で［検索モード］を指定するシーンは少ないでしょう。
- 1：先頭から末尾
- -1：末尾から先頭
- 2：バイナリ検索（昇順で並べ替え）
- -2：バイナリ検索（降順で並べ替え）
［一致モード］の利用例
［一致モード］の利用例をひとつ紹介します。先ほどの表で「人口が5,000,000以上の都道府県がいくつあるか」を調べたいとしましょう。
「5,000,000以上」の条件として、セルH10に「5,000,000」と入力しています。「以上」としたいため［一致モード］を「1」とすれば良さそうなので、セルG11に「=XMATCH(H10,C2:C48,1)」と入力してみます。
結果は「40」となりました。「5,000,000以上」の都道府県はそんなにないですよね。これは、C2:C48の降順で並べ替えていなかったため、条件「5,000,000以上」に対して「5,000,000以上でもっとも小さい数の項目」が上から40番目にあるという結果です。「人口」列で並べ替えれば解決しますが、Excel 2021なら、新しい「SORT関数」と組み合わせてみましょう。
SORT関数は、指定したセル範囲のデータを並べ替えられる関数です。元の表を並べ変えることなく内部的にデータを並べ替えることが可能です。詳しくはこちらの記事を参考にしてください。
