XMATCH関数(MATCH関数を拡張した新関数)
XMATCH関数は、配列またはセル範囲内で指定された項目を検索し、最初に一致した項目の相対的な位置を返します。
検索値が見つからない場合は、#N/Aを返します。
MATCH関数を機能強化した新関数です。
XMATCH関数の書式
検索する値を指定します。
XMATCH関数は文字数制限がなくなりました。
ただし、エクセルの仕様として32768文字以上はセルに入れられません。
検索する範囲または配列を指定します。
指定範囲または配列は、1行または1列のみです。
複数行かつ複数列を指定した場合は、#VALUE!のエラーになります。
どのような状態を一致とするかを指定します。
0 : 完全一致
-1 : 完全一致または次に小さい項目
1 : 完全一致または次に大きい項目
2 : ワイルドカード文字との一致
省略した場合は、0(完全一致)になります。
(*)半角のアスタリスク:任意の文字列
通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に、"~*" のように半角のチルダ (~) を付けます。
検索の向きを指定します。
1 : 先頭から末尾へ検索
-1 : 末尾から先頭へ検索
2 : バイナリ検索(昇順で並べ替え)
-2 :バイナリ検索(降順で並べ替え)
省略した場合は、1 (先頭から末尾)になります。
従来のMATCH関数との違い
MATCH関数の書式
0 : 完全一致
-1 : 以上
省略した場合は、1(以下)になります。
違いは以下になります。
文字数制限が無くなったことと、
一致モード、検索モードを指定した場合になります。
一致モードの使い方
0 : 完全一致
A列全体を検索範囲にしているので、行位置がそのまま戻ります。
検索範囲をA2:A50のようにした場合は、3が戻ります。
これは、MATCH関数と同じです。
2行全体を検索範囲にしているので、列位置がそのまま戻ります。
検索範囲をA2:F2のようにした場合は、3が戻ります。
これは、MATCH関数と同じです。
-1 : 完全一致または次に小さい項目
※検索値の順序をわざと崩しています。
一致する値が無かった場合は、検索値を超えない最初に見つかった行になります。
検索値が15の場合は、15以下で15に最も近い10の行になっています。
検索範囲をA2からにしているので、4が戻されています。
これは、MATCHの1(以下)と同様の一致方法になります。
上図の場合は、昇順に並んでいないのでMATCHは使えません。
ただし、大小比較で検索するなら検索範囲は並べ替えておくべきでしょう。
そうしておかないと、結果を見た時に分かりづらくなってしまいます。
1 : 完全一致または次に大きい項目
検索値と完全に一致していればその行になりますが、
一致する値が無かった時は、検索値を超えた最初に見つかった行になります。
検索値が15の場合は、15以上で15に最も近い100の行になっています。
1000000超の検索値では#N/Aとなります。
2 : ワイルドカード文字との一致
(*)半角のアスタリスク:任意の文字列
検索モードの使い方
1 : 先頭から末尾へ検索
ここまでの使用例が全て1(先頭から末尾)です。
検索値が見つかった最初の行の項目が返されます。
-1 : 末尾から先頭へ検索
このように検索値が複数存在し、その一番下の行を取得したい場合に使います。
2 : バイナリ検索(昇順で並べ替え)
これはXLOOKUPやXMATCHが改善されたと言っても、やはり時間がかかります。
検索範囲のどこに検索値があるか分からないのですから、全て探すしかありません。
最初の方で見つかれば良いですが、一番最後の方にあったとしたら・・・
-2 :バイナリ検索(降順で並べ替え)
検索範囲が降順に並んでいる場合は、こちらを指定してください。
XMATCH関数をスピルさせる
横にスピルさせる
これはMATCHをスピルさせることでも同じことができます。
縦にスピルさせる
これはMATCHをスピルさせることでも同じことができます。
XMATCHの応用例
VLOOKUPの縦横同時スピルの代替
※INDEX関数の戻りは書式設定されないので日付はシリアル値で表示されています。
INDEXもXMATCHも検索値に範囲を指定することでスピルします。
これは、単純にINDEXの行番号と列番号に配列を指定して縦横スピルさせたものになります。
これなら、MATCHで検索できさえすれば自由に条件設定できますし、項目順序の入れ替えも可能です。。
なお、このXMATCHはMATCHでも同じことができます。
OFFSETの行/列に使用する
P2セルの商品でA列を検索して、各月の売上、予算、予算比を取得します。
スピルによって新しく追加された関数
関数名 | 説明 |
FILTER | フィルターは定義した条件に基づいたデータ範囲です。 |
SORT | 範囲または配列の内容を並べ替えます。 |
SORTBY | 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。 |
UNIQUE | 一覧表または範囲内から重複データを削除した一覧を返します。 |
RANDARRAY | 0から1までのランダムな数値の配列を返します。 |
SEQUENCE | 1、2、3、4など、配列内の連続した数値の一覧を生成します。 |
XLOOKUP | 範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。 一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。 |
XMATCH | 配列またはセル範囲内の項目の相対的な位置を返します。 |
同じテーマ「エクセル入門」の記事
LET関数(数式で変数を使う)
新着記事NEW ・・・新着記事一覧を見る
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。