式の例を備えたExcelマッチ機能

このチュートリアルでは、式の例を備えたExcelで一致関数を使用する方法について説明します。また、VlookupとMatchを使用して動的な式を作成することにより、ルックアップ式を改善する方法も示しています。

Microsoft Excelでは、さまざまな細胞で特定の値を見つけるのに役立つ多くの異なるルックアップ/参照関数があり、一致することもその1つです。基本的に、それはさまざまなセルのアイテムの相対的な位置を識別します。ただし、一致関数は、その純粋な本質以上のことを行うことができます。

• Excel Match機能 - 構文と基本的な用途
• Excelでマッチを使用する方法 - フォーミュラの例
  • ワイルドカードと一致します
  • ケースに敏感な一致式
  • 一致と違いについて2つの列を比較します（ISNA一致）
  • vlookup and match
  • hlookup and match

Excel Match機能 - 構文と使用

Excelの一致関数は、さまざまなセルで指定された値を検索し、その値の相対位置を返します。

一致関数の構文は次のとおりです。

match（lookup_value、lookup_array、[match_type]）

lookup_value （必須） - 見つけたい値。それは、数値、テキスト、または論理値、およびセルの参照である場合があります。

lookup_array （必須） - 検索するセルの範囲。

match_type （オプション） - マッチタイプを定義します。これらの値の1つになる可能性があります：1、0、-1。 Match_type引数は0に設定されているため、正確な一致のみが返されますが、他の2つのタイプではおおよその一致が可能になります。

• 1または省略（デフォルト） - おおよその一致（最も近い）。ルックアップ値以下のルックアップアレイで最大の値を見つけます。ルックアップアレイを昇順で、最小から最大、またはAからZまで並べ替える必要があります。
• 0-正確な一致。ルックアップ値に正確に等しいアレイ内の最初の値を見つけます。選別は必要ありません。
• -1-おおよその一致（最も近い）。ルックアップ値以上の最小値を見つけます。ルックアップアレイは、最大から小さなもの、またはZからAまで、下降順にソートする必要があります。

一致関数をよりよく理解するために、このデータに基づいて単純な式を作成しましょう。列Aの学生名と列Bの試験スコアは、最大から最小までソートされます。特定の学生（たとえば、 Laura ）が特に立っている場所を見つけるには、この単純な式を使用してください。

=MATCH("Laura", A2:A8, 0)

オプションで、ルックアップ値を一部のセル（この例のE1）に配置し、Excelマッチフォーミュラのセルを参照できます。

=MATCH(E1, A2:A8, 0)

上記のスクリーンショットにあるように、学生名は任意の順序で入力されるため、この一致タイプのみがルックアップ配列のソート値を必要としないため、 Match_Type引数を0（正確な一致）に設定します。技術的には、マッチフォーミュラは、ローラの相対的な位置を範囲内で返します。しかし、スコアは最大から最小までソートされているため、ローラはすべての学生の中で5^番目のベストスコアを持っていることもわかります。

ヒント。 Excel 365およびExcel 2021では、モダンでより強力な試合の後継者であるXmatch関数を使用できます。

一致関数について知っておくべき4つのこと

ご覧のとおり、ExcelでMatchを使用するのは簡単です。ただし、他のほぼすべての機能の場合と同様に、次のことに注意する必要があるいくつかの特異性があります。

1. 一致関数は、値自体ではなく、配列のルックアップ値の相対位置を返します。
2. 一致はケースに依存しないことです。つまり、テキスト値を扱うときに小文字と大文字の文字を区別しません。
3. ルックアップ配列にルックアップ値のいくつかの発生が含まれている場合、最初の値の位置が返されます。
4. ルックアップアレイにルックアップ値が見つからない場合、＃n/aエラーが返されます。

Excelでマッチを使用する方法 - フォーミュラの例

Excel Match関数の基本的な用途がわかったので、基本を超えたいくつかのフォーミュラの例について説明しましょう。

ワイルドカードとの部分的な一致

他の多くの機能と同様に、Matchは次のワイルドカード文字を理解しています。

• 質問マーク（？） - 単一の文字を置き換えます
• Asterisk（*） - 文字のシーケンスを交換します

注記。ワイルドカードは、 match_typeが0に設定されたマッチ式でのみ使用できます。

ワイルドカードとのマッチフォーミュラは、テキスト文字列全体ではなく、文字列の一部の一部または一部のみを一致させたい場合に役立ちます。ポイントを説明するには、次の例を考えてください。

過去1か月間、地域の再販業者とその売上高のリストがあるとします。リスト内の特定の再販業者の相対的な位置を見つけたい（降順で販売額でソートされた）が、最初のキャラクターをいくつか覚えているにもかかわらず、彼の名前を正確に覚えることはできません。

再販業者の名前が範囲A2：A11にあると仮定し、「車」で始まる名前を探していますが、フォーミュラは次のとおりです。

=MATCH("car*", A2:A11,0)

マッチフォーミュラをより汎用性の高いものにするには、一部のセルのルックアップ値を入力し（この例ではE1）、このようなワイルドカード文字とそのセルを連結できます。

=MATCH(E1&"*", A2:A11,0)

以下のスクリーンショットに示すように、フォーミュラは「カーター」の位置である2を返します。

ルックアップ値の1つの文字だけを置き換えるには、「？」を使用します。このようなワイルドカードオペレーター：

=MATCH("ba?er", A2:A11,0)

上記の式は「ベイカー」という名前と一致し、その相対位置（5）を再実行します。

ケースに敏感な一致式

このチュートリアルの冒頭で述べたように、一致関数は大文字と小文字の文字を区別しません。ケースに敏感な一致式を作成するには、キャラクターケースを含むセルを正確に比較する正確な関数と組み合わせて一致を使用します。

データを一致させるための一般的なケースに敏感な式は次のとおりです。

マッチ（true、exact（ lookup array 、 lookup value ）、0）

式は、次のロジックで動作します。

• 正確な関数は、ルックアップアレイの各要素とルックアップ値を比較します。比較セルが正確に等しい場合、関数はtrue、それ以外の場合はfalseを返します。
• そして、一致関数は、正確に返された配列内の各値とtrue（ lookup_value ）を比較し、最初の一致の位置を返します。

それは、 ctrlシフトを押す必要があるアレイ式であることに留意してください。

ルックアップ値がセルE1にあり、ルックアップアレイはA2：A9であると仮定すると、式は次のとおりです。

=MATCH(TRUE, EXACT(A2:A9,E1),0)

次のスクリーンショットは、Excelのケースに敏感な一致式を示しています。

一致と違いについて2つの列を比較します（ISNA一致）

一致と違いの2つのリストをチェックすることは、Excelで最も一般的なタスクの1つであり、さまざまな方法で実行できます。 Isna/Matchフォーミュラはその1つです。

if（isna（match（ list1 、 list2、0の1つの値））、「リスト1にない」、 ""）

リスト1に存在しないリスト2の任意の値について、式は「リスト1にはない」を返します。そして、ここにあります：

• 一致関数は、リスト2内のリスト1の値を検索します。値が見つかった場合、相対位置は＃n/aエラーを返します。
• ExcelのISNA関数は、＃n/aエラー（「利用できない」を意味する）をチェックします。指定された値が＃n/aエラーである場合、関数はtrue、falseを返します。私たちの場合、trueとは、リスト1からの値がリスト2内にないことを意味します（つまり、＃n/aエラーが一致して返されます）。
• リスト1に表示されない値に対してユーザーが真であると見ることは非常に混乱する可能性があるため、ISNAの周りに関数をラップして、代わりに「リスト1にない」、または必要なテキストを表示します。

たとえば、列Bの値を列Aの値と比較するには、式は次の形状（B2が最上部のセル）を取得します。

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

覚えているように、Excelの一致関数はそれ自体ではケース非感受性です。キャラクターケースを区別するために、 Lookup_Array引数に正確な関数を埋め込み、 Ctrl Shift Enterを押してこの配列式を完了することを忘れないでください。

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

次のスクリーンショットは、両方の式の動作を示しています。

Excelの2つのリストを比較する他の方法を学ぶには、次のチュートリアルをご覧ください。Excelの2つの列を比較する方法。

Excel vlookupとMatch

この例では、Excel vlookup関数の基本的な知識が既にあることを前提としています。そして、もしそうなら、あなたがその多数の制限に遭遇する可能性があります（その詳細な概要は、Excel vlookupが機能していない理由で見つけることができます）。

vlookupの最も厄介な欠点の1つは、ルックアップテーブル内に列を挿入または削除した後に動作を停止することです。これは、vlookupが指定した（インデックス番号）（インデックス番号）の返品列の数に基づいて一致する値を引くために発生します。インデックス番号は式で「ハードコーディング」されているため、Excelは新しい列がテーブルに追加または削除されたときに調整できません。

Excel Match関数は、ルックアップ値の相対的な位置を扱うため、vlookupのcol_index_num引数にぴったりです。言い換えれば、return列を静的番号として指定する代わりに、一致を使用してその列の現在の位置を取得します。

物事を理解しやすくするために、生徒の試験スコアを再度テーブルで使用してみましょう（このチュートリアルの冒頭で使用したスコアと同様）が、今回はその相対的な位置ではなく、実際のスコアを取得します。

ルックアップ値がセルF1にあると仮定すると、テーブルアレイは$ 1：$ c $ 2です（式を他のセルにコピーする予定がある場合は、絶対セル参照を使用してロックすることをお勧めします）、式は次のとおりです。

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

3 ^rd引数（ col_index_num ）は3に設定されています^。以下のスクリーンショットでわかるように、この通常のvlookup式はうまく機能します。

ただし、列を挿入または削除するまでのみです。

だから、なぜ#ref！エラー？ col_index_numが3に設定されているため、3番目の列から値を取得するようにExcelに指示しますが、テーブル配列には2つの列のみがあります。

そのようなことが起こらないようにするために、次の一致関数を含めることにより、vlookupの式をより動的にすることができます。

MATCH(E2,A1:C1,0)

どこ：

• E2はルックアップ値であり、これは返された列の名前、つまり値を引く列（この例の数学スコア）にまったく等しくなります。
• A1：C1は、テーブルヘッダーを含むルックアップアレイです。

そして、このようなvlookup式のcol_index_num引数にこの一致関数を含めます。

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

追加または削除する列の数に関係なく、それが完璧に機能することを確認してください：

上記のスクリーンショットでは、ユーザーがワークシートの別の場所に移動しても、フォーミュラのすべてのセル参照を正しく動作させるようにロックしました。以下のスクリーンショットで見ることができます。列を削除した後、式は正常に機能します。さらに、Excelは、この場合に絶対的な参照を適切に調整するのに十分賢いです。

Excel hllookupとMatch

同様に、Excel Match関数を使用してHlookup式を改善できます。一般原則は、vlookupの場合と本質的に同じです。一致関数を使用して、return列の相対位置を取得し、その数をhlookup式のrow_index_num引数に供給します。

ルックアップ値がセルB5にあると仮定します。テーブル配列はB1：H3、リターン行の名前（一致のルックアップ値）はセルA6、行ヘッダーはA1：A3です。完全な式は次のとおりです。

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

ご覧のとおり、Hlookup/vlookup＆Matchの組み合わせは、確かに通常のHlookupとvlookupフォーミュラよりも改善されています。ただし、一致関数はすべての制限を排除しません。特に、vlookupマッチフォーミュラはまだ左を見ることができず、Hlookup Matchは最上部の試合以外の任意の行で検索できません。

上記の（および他のいくつかの）制限を克服するには、インデックスマッチの組み合わせを使用することを検討してください。これは、多くの点で、VlookupやHlookupよりも優れているExcelでルックアップを行うための非常に強力で多才な方法を提供します。詳細なガイダンスとフォーミュラの例は、Excelのインデックスとマッチに記載されています。これは、vlookupのより良い代替手段です。

これは、Excelで一致式の使用方法です。うまくいけば、このチュートリアルで説明した例があなたの仕事に役立つことを証明するでしょう。お読みいただきありがとうございます。来週のブログでお会いしましょう！

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

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

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