式の例を備えたExcel XMatch関数

このチュートリアルでは、新しいExcel XMatch関数を紹介し、いくつかの一般的なタスクを解決するために一致するよりも優れていることを示しています。

Excel 365では、一致関数に優先するためにXmatch関数が追加されました。しかし、既存の式のアップグレードを開始する前に、新しい機能のすべての利点とそれが古い機能とどのように異なるかを理解することが賢明でしょう。

要約すると、Xmatch関数は一致と同じですが、より柔軟で堅牢です。垂直と水平の両方の配列で検索し、最初から最後まで検索または最後から最後まで検索し、正確、近似、部分的な一致を見つけ、より高速なバイナリ検索アルゴリズムを使用できます。

• ExcelのXMatch関数
• 基本的なExcel XMatch式
• Excel-式の例でXmatchを使用する方法
  • 正確な一致と近似一致
  • ワイルドカードとの部分的な一致
  • XMatchリバース検索
  • 一致の2つの列を比較します
  • インデックスxmatch式
  • インデックスXMatch XMatch式
  • ケースに敏感なXmatch式
• XMatch vs. Excelの試合

Excel XMatch関数

ExcelのXmatch関数は、配列またはさまざまなセル内の値の相対位置を返します。

次の構文があります。

xmatch（lookup_value、lookup_array、[match_mode]、[search_mode]）

どこ：

lookup_value （必須） - 探す値。

lookup_array （必須） - 検索する場所のセルの配列または範囲。

match_mode （オプション） - 使用する一致タイプを指定します。

• 0または省略（デフォルト） - 正確な一致
• -1-正確な一致または次の最小値
• 1-正確な一致または次に最大の値
• 2-ワイルドカードマッチ（*、？）

search_mode （オプション） - 検索方向とアルゴリズムを指定します。

• 1または省略（デフォルト） - 最初から最後まで検索します。
• -1-最後から最初まで逆の順序で検索します。
• 2-バイナリ検索昇順。 Lookup_arrayを昇順でソートする必要があります。
• -2-下降するバイナリ検索。 lookup_arrayを降順でソートする必要があります。

バイナリ検索は、ソートされた配列で効率的に機能する高速アルゴリズムです。詳細については、検索モードを参照してください。

どのExcelバージョンにxmatchがありますか？

Xmatch関数は、Microsoft 365およびExcel 2021のExcelでのみ利用可能です。Excel2019、Excel 2016および以前のバージョンでは、この関数はサポートされていません。

Excelの基本Xmatch式

関数が何ができるかについての一般的なアイデアを得るために、XMatchフォーミュラを最も単純なフォームに作成し、最初の2つの必要な引数のみを定義し、オプションの引数をデフォルトに任せます。

想定して、あなたはそのサイズ（C2：C6）でランク付けされた海洋のリストを持っていると仮定し、あなたは特定の海の階級を見つけたいと思っています。それを完了するには、単に外観の値として、 「インド語」と言われ、名前のリスト全体をルックアップアレイとして使用するだけです。

=XMATCH("Indian", C2:C6)

フォーミュラをより柔軟にするには、一部のセルに興味のある海を入力してください。

=XMATCH(F1, C2:C6)

その結果、垂直配列で検索するXmatch式を取得します。出力は、アレイのルックアップ値の相対位置であり、私たちの場合、海のランクに対応しています。

同様の式は、水平アレイにも完全に機能します。あなたがする必要があるのは、 lookup_arrayリファレンスを調整することです。

=XMATCH(B5, B1:F1)

Excel XMatch関数 - 覚えておくべきこと

ワークシートでXmatchを効果的に使用し、予期しない結果を防ぐには、これらの3つの簡単な事実を覚えておいてください。

• ルックアップ配列にルックアップ値が2つ以上発生すると、 Search_Mode引数が1に設定されているか省略されている場合、最初の一致の位置が返されます。 search_modeが-1に設定されている場合、関数は逆の順序で検索され、この例で示されているように最後の一致の位置を返します。
• ルックアップ値が見つからない場合、＃n/aエラーが発生します。
• Xmatch関数は本質的にはケースに依存しないものであり、文字ケースを区別することはできません。小文字と大文字を区別するには、このケースに敏感なXmatch式を使用します。

Excel-式の例でXmatchを使用する方法

次の例は、Xmatch関数とその実用的な用途についてより多くの理解を得るのに役立ちます。

正確な一致と近似一致

Xmatchの一致する動作は、オプションのmatch_mode引数によって制御されます。

• 0または省略（デフォルト） - フォーミュラは正確な一致のみを検索します。正確な一致が見つからない場合、＃n/aエラーが返されます。
• -1-フォーミュラは最初に正確な一致を検索し、次に次の小さなアイテムを検索します。
• 1-フォーミュラは、最初に正確な一致を検索し、次に次の大きなアイテムを検索します。

そして今、異なる一致モードがフォーミュラの結果にどのように影響するかを見てみましょう。特定のエリア、たとえば80,000,000 km ²がすべての海の中でどこにあるかを知りたいとします。

正確な一致

match_modeに0を使用する場合、＃n/aエラーが表示されます。これは、式がルックアップ値と正確に等しい値を見つけることができないためです。

=XMATCH(80000000, C2:C6, 0)

次の最小のアイテム

Match_modeに-1を使用する場合、式は3を返します。これは、ルックアップ値よりも最も近い一致が70,560,000であり、ルックアップアレイの3^番目のアイテムです。

=XMATCH(80000000, C2:C6, -1)

次の最大のアイテム

Match_Modeに1を使用する場合、式は2を出力します。これは、ルックアップ値よりも最も近い一致が85,133,000であるため、ルックアップ配列の2^つの項目であるためです。

=XMATCH(80000000, C2:C6, -1)

下の画像は、すべての結果を示しています。

Excelの部分テキストをワイルドカードと一致させる方法

XMatch関数には、ワイルドカードの特別なマッチモードがあります。Match_Mode引数は2に設定されています。

ワイルドカードマッチモードでは、XMatchフォーミュラが次のワイルドカード文字を受け入れます。

• 単一の文字に一致する質問マーク（？）。
• アスタリスク（*）の文字の任意のシーケンスを一致させます。

ワイルドカードは、数字ではなくテキストでのみ動作することに注意してください。

たとえば、「南」から始まる最初のアイテムの位置を見つけるために、式は次のとおりです。

=XMATCH("south*", B2:B6, 2)

または、いくつかのセルにワイルドカード式を入力し、F1と入力し、 LookUP_Value引数のセル参照を提供することもできます。

=XMATCH(F1, B2:B6, 2)

ほとんどのExcel関数を使用すると、Tilde（〜）を使用して、アスタリスク（〜*）または質問マーク（〜？）をワイルドカードではなく文字通りのキャラクターとして扱います。 xmatchでは、ティルドは必要ありません。ワイルドカードマッチモードを定義しない場合、XMatchはそれを想定しますか？ *通常の文字です。

たとえば、以下の式では、アスタリスク文字の範囲A2：A7を正確に検索します。

=XMATCH("*", A2:A7)

XMatch逆の検索最後の一致を見つける

ルックアップアレイにルックアップ値のいくつかの発生がある場合、最後の発生の位置を取得する必要がある場合があります。

検索の方向は、 search_modeという名前のxmatchの4^番目の引数です。逆の順序で検索するには、つまり垂直配列の下から上へ、および水平アレイで右から左へと検索するには、 search_modeを-1に設定する必要があります。

この例では、特定のルックアップ値について最後のレコードの位置を返します（以下のスクリーンショットを参照してください）。このために、次のように引数を設定します。

• lookup_value- H1のターゲット営業担当者
• lookup_array- c2：c10の営業担当者の名前
• match_modeは0または省略されています（正確な一致）
• search_modeは-1（最後から2番目）です

4つの引数をまとめると、この式が得られます。

=XMATCH(H1, C2:C10, 0, -1)

ローラが行った最後の販売の数を返します：

Excelの2つの列を試合に比較する方法

一致の2つのリストを比較するには、XMatch関数をifおよびISNAと一緒に使用できます。

if（isna（xmatch（ target_list 、 search_list 、0））、 "no match"、 "mate"））

たとえば、b2：b10のリスト2をA2：a10のリスト1に対して比較するには、次の形式を取ります。

=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")

この例では、一致のみを識別するため、IF関数のvalue_if_true引数は空の文字列（ ""）です。

最上部のセル（この場合はC2）に上記の式を入力し、 Enterを押して、他のセルに自動的に「こぼれる」（こぼれた範囲と呼ばれます）。

この式の仕組み

式の中心にあるXmatch関数は、リスト1内のリスト2の値を検索します。値が見つかった場合、その相対位置が返されます。私たちの場合、Xmatchの結果は次の配列です。

{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}

この配列は、＃n/aエラーをチェックするISNA関数に「供給」されます。 ＃n/aエラーごとに、isnaはtrueを返します。他の値の場合 - false。その結果、次の論理値を生成します。ここで、Trueは非マッチを表し、Falseは一致します。

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

上記の配列は、IF関数の論理テストに移動します。最後の2つの引数の構成方法に応じて、式は対応するテキストを出力します。私たちの場合、それは非マッチ（ value_if_true ）の空の文字列（ ""）であり、マッチ（ value_if_false ）の「リスト1の一致」です。

注記。この式は、動的配列をサポートするExcel 365とExcel 2021でのみ機能します。 Excel 2019、Excel 2016または以前のバージョンを使用している場合は、Excelの2つの列を比較する方法を確認してください。

ExcelのインデックスXMatch

XMatchをインデックス関数と組み合わせて使用​​して、インデックスマッチ式と同様に、ルックアップ値に関連付けられた別の列から値を取得できます。一般的なアプローチは次のとおりです。

index（ return_array 、xmatch（ lookup_value 、 lookup_array ）

ロジックは非常に簡単で、簡単に従うことができます。

Xmatch関数は、ルックアップ配列のルックアップ値の相対位置を計算し、それをインデックスのrow_num引数に渡します。行番号に基づいて、インデックス関数は、指定した任意の列から値を返します。

たとえば、E1の海の領域を調べるには、この式を使用できます。

=INDEX(B2:B6, XMATCH(E1, A2:A6))

インデックスxMatch xMatch 2次元ルックアップを実行します

列と行を同時に検索するには、2つのXmatch関数と一緒にインデックスを使用します。最初のxmatchは行番号を取得し、2番目のxmatchは列番号を取得します。

index（ data 、xmatch（ lookup_value 、 vertical _ lookup_array ）、xmatch（ lookup value 、 horizo​​ntal _ lookup_array ））

式は、 Match_Mode引数がデフォルトで正確な一致になるため、Match_Mode引数を省略できることを除いて、インデックスマッチマッチに似ています。

たとえば、特定の月（G2）で特定のアイテム（G1）の販売番号を取得するには、式は次のとおりです。

=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))

ここで、B2：D8は行ヘッダーと列ヘッダーを除くデータセル、A2：A8はアイテムのリストであり、B1：D1は月名です。

ケースに敏感なXmatch式

すでに述べたように、Excel XMatch関数は設計によるケース非感受性です。テキストケースを区別するように強制するには、正確な関数と組み合わせてxmatchを使用します。

マッチ（true、acture（ lookup_array 、 lookup_value ））

最後から最初に逆順序で検索するには：

Match（true、exact（ lookup_array 、 lookup_value ）、0、-1）

次の例は、この汎用式が動作していることを示しています。 B2：B11のケースに敏感な製品IDのリストがあると仮定します。 E1でアイテムの相対的な位置を見つけたいと思っています。 E2のケースに敏感な式は、これと同じくらい簡単です。

=XMATCH(TRUE, EXACT(B2:B11, E1))

この式の仕組み：

正確な関数は、ルックアップアレイ内の各アイテムに対してルックアップ値を比較します。キャラクターの場合を含む比較値が正確に等しい場合、関数はtrue、falseを返します。この論理値（Trueは正確な一致を表す場所）の配列は、XmatchのLookup_Array引数に課されます。また、ルックアップ値がtrueであるため、Xmatch関数は、 Search_Mode引数の構成方法に応じて、最初に見つけられた正確な一致または最後の正確な一致の位置を返します。

XMatch vs. Excelの試合

XMatchは、より強力で多用途の交換用に設計されているため、これらの2つの機能には多くの共通点があります。ただし、本質的な違いがあります。

異なるデフォルト動作

一致関数は、正確な一致または次に最小のアイテム（ match_typeが1に設定されているか、省略）にデフォルトです。

Xmatch関数は、正確な一致（ match_modeが0に設定または省略）にデフォルトです。

おおよその一致の異なる動作

match_mode / match_type引数が1に設定されている場合：

• 正確な一致または次の最小の検索を一致させます。ルックアップアレイを昇順でソートする必要があります。
• XMatchは、正確な一致または次に最大の検索を検索します。選別は必要ありません。

match_mode / match_type引数が-1に設定されている場合：

• 正確な一致または次の最大の検索を一致させます。ルックアップアレイを降順で並べ替える必要があります。
• XMatchは、正確な一致または次の最小を検索します。選別は必要ありません。

ワイルドカード検索

xmatchとの部分的な一致を見つけるには、 match_mode引数を2に設定する必要があります。

一致関数には、特別なワイルドカードマッチモードオプションがありません。ほとんどの場合、正確なmatch（ match_typeセット0に設定）に設定します。これは、ワイルドカード検索でも機能します。

検索モード

新しいXlookup関数と同様に、Xmatchには、検索の方向を定義できる特別な検索_mode引数があります。

• 1または省略（デフォルト） - 最初からラストを検索します。
• -1-逆検索最後からファースト。

バイナリ検索アルゴリズムを選択します。これは、ソートされたデータで非常に迅速かつ効率的です。

• 2-昇順でソートされたデータのバイナリ検索。
• -2-データソートされたデータのバイナリ検索。

ハーフインターバル検索または対数検索とも呼ばれるバイナリ検索は、配列の中央要素と比較することにより、配列内のルックアップ値の位置を見つける特別なアルゴリズムです。バイナリ検索は、定期的な検索よりもはるかに高速ですが、ソートされたリストでのみ正しく機能します。解散データでは、一目ぼれがかなり正常に見える可能性のある間違った結果を返す可能性があります。

一致の構文は、検索モードの引数をまったく提供していません。

XMatchはアレイをネイティブに処理します

前身とは異なり、Xmatch関数は、Ctrl Shift Enterを押す必要がなく、動的Excelおよびハンドルアレイ用にネイティブに設計されました。これにより、特にいくつかの異なる関数を一緒に使用する場合、式はビルドと編集がはるかに簡単になります。次のソリューションを比較するだけです。

• ケースに敏感な式：XMatch |マッチ
• 一致の2つの列またはリストを比較します：xmatch |マッチ

XMATCHとマッチの可用性

XMatchは新しい機能であり、Microsoft 365およびExcel 2021のExcelでのみ利用できます。

一致関数は、Excel 2007からExcel 365の任意のバージョンで利用できます。

これがExcelでXmatch関数を使用する方法です。お読みいただきありがとうございます。来週のブログでお会いしましょう！

ダウンロード用のワークブックを練習します

Excel Xmatchフォーミュラの例（.xlsxファイル）

以上が式の例を備えたExcel XMatch関数の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。