XMATCH関数の解説
ExcelのXMATCH関数は、配列内の項目と一致する項目を検索し、その位置を返すための新しい関数です。この記事では、XMATCH関数の基本的な使用方法や応用例を詳細に解説します。
関数カテゴリー
XMATCH関数は「検索/行列関数」のカテゴリーに属します。このカテゴリーの関数は、データ内の特定の情報を検索または参照するための関数群です。
処理の概要
XMATCH関数は、指定した値と一致する項目の位置を配列内で返します。従来のMATCH関数に比べて、一致モードや検索モードのカスタマイズが容易です。
意義
XMATCH関数は、データの検索や参照がより柔軟に、そして簡単になります。特に大きなデータセットや複雑なデータ構造の中での検索に威力を発揮します。
構文・書式
XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
引数
検索値: 検索する値。
検索範囲: 検索値を検索する配列。
一致モード (オプション): 一致する値の種類を指定します。省略すると、デフォルトで「0」となり、完全一致のみを検索します。また、「1」（以下）および「-1」（以上）のいずれかを指定することで、最も近い値に対する一致を検索することができます。「2」ではワイルドカード検索が可能です。
検索モード (オプション): 検索を行う方向と範囲を指定します。省略すると、デフォルトで「1」となり、最初から最後まで検索します。また、-1（最後から最初まで）、2（最初から最後までの二分探索・バイナリサーチ）、および-2（最後から最初までの二分探索・バイナリサーチ）のいずれかを指定することができます。「二分探索・バイナリサーチ」は大きなデータセットで高速に検索させる手法です。一般的なデータセットでは採用する必要性は低いです。
関数の使用サンプル
=XMATCH("田中", A1:A10)
この例では、範囲A1:A10内で「田中」という名前を検索し、一致する位置を返します。一致モードも検索モードも指定していないので「完全一致・上から」の位置検索となります。
| スコア基準 |
|---|
| 0 |
| 100 |
| 500 |
| 3500 |
| 12000 |
| 30000 |
| 80000 |
=XMATCH(5500,A2:A8,1)
テーブルAをもとに上記の式を作成すると「5」が返ります。「5500」より大きい値の位置は上から5つめです(一致モード：1)。
=XMATCH(5500,A2:A8,-1)
テーブルAをもとに上記の式を作成すると「4」が返ります。「5500」より大きい値の位置は上から5つめです(一致モード：-1)。
=XMATCH("*中", A1:A10,2)
この例では、範囲A1:A10内で「中」で終わる名前を検索し、一致する位置を返します。「*」へは何が入ってもよしとします(一致モード：2)。
=XMATCH("水曜日",A1:A10,,-1)この例では、範囲A1:A10内で一番下にある「水曜日」検索し、一致する位置を返します(検索モード：-1)。
関数の応用例
INDEX関数とXMATCH関数を組み合わせて、特定の行と列の交差するセルの値を抽出する方法を紹介しましょう。
| ＼ | 店舗甲 | 店舗乙 | 店舗丙 |
|---|---|---|---|
| 月曜日 | 38 | 28 | 25 |
| 火曜日 | 55 | 50 | 29 |
| 水曜日 | 59 | 22 | 59 |
| 木曜日 | 52 | 41 | 57 |
| 金曜日 | 56 | 24 | 50 |
| 月曜日 | 20 | 43 | 31 |
| 火曜日 | 60 | 48 | 24 |
| 水曜日 | 27 | 55 | 37 |
| 木曜日 | 23 | 63 | 58 |
| 金曜日 | 53 | 60 | 54 |
| 月曜日 | 40 | 55 | 61 |
| 火曜日 | 62 | 63 | 65 |
| 水曜日 | 39 | 52 | 51 |
| 木曜日 | 51 | 33 | 40 |
| 金曜日 | 49 | 63 | 27 |
以下の式を使用して、表の中で「乙」店舗の中で一番下にある「水」の値をクロス抽出します。
=INDEX(A1:D16,XMATCH("水*",A1:A16,2,-1),MATCH("*乙",A1:D1,0))
この式の動作について詳しく説明します。
- XMATCH(“水*”,A1:A16,2,-1): A1:A16の範囲で、”水”で始まる最後のセルの位置を返します。ここで「2」はワイルドカードを使用しての検索を、そして「-1」は下から上への検索を指示しています。
- MATCH(“*乙”,A1:D1,0): A1:D1の範囲で、”乙”で終わるセルの位置を返します。
- INDEX(A1:D16, … , … ): 指定された行と列の交差するセルの値を返します。
この結果、表の中で「乙」店舗の中で一番下にある「水」の値、「52」が抽出されます。
類似している関数
MATCH関数: 指定した値と一致する項目の位置を返しますが、XMATCH関数のように下方からの検索はできません。
関連する関数
INDEX関数: リストの中から行の位置、列の位置を指定することでクロス抽出を可能にします。行や列の位置の検索にはXMATCH関数が適しています。
注意事項
XMATCH関数はExcelの最新バージョンでのみ利用可能です。旧バージョンのExcelには含まれていませんので、その点を注意してください。
FAQセクション
- XMATCH関数はMATCH関数の代わりとして常に使用できますか？
- 基本的には代わりとして使用できますが、旧バージョンのExcelでは使用できないため、その点を考慮する必要があります。