Excel で INDEX と MATCH が VLOOKUP より優れている理由

VLOOKUP（英語VLOOKUP、略称は「垂直ルックアップ機能」）の基本機能の使い方を初心者向けに解説してきました。 また、経験豊富なユーザーには、さらに複雑な式がいくつか示されました。

この記事では、垂直検索を使用する別の方法についての情報を提供しようとします。

「なぜこれが必要なの？」と疑問に思うかもしれません。 これは、考えられるすべての検索方法を表示するために必要です。 さらに、多数の VLOOKUP 制限により、多くの場合、目的の結果が得られません。 この点で、INDEX( ) MATCH( ) ははるかに機能的で多様であり、制限も少なくなっています。

基本 INDEX MATCH

このガイドの目的は、この機能がいかに優れているかを示すことであるため、 その動作原理に関する基本情報を見てみましょう。 また、例を示し、VLOOKUP () よりも優れている理由についても検討します。

INDEX 関数の構文と使用法

この機能は、列または行番号に基づいて、指定された検索領域の中から目的の値を見つけるのに役立ちます。 構文：

=INDEX(配列、行番号、列番号):

  • 配列 – 検索が行われる領域。
  • 行番号 – 指定された配列で検索される行の番号。 行番号が不明な場合は、列番号を指定する必要があります。
  • 列番号 – 指定された配列で検出される列の番号。 値が不明な場合は、行番号が必要です。

簡単な式の例:

=INDEX(A1:S10,2,3)

関数は、A1 から C10 の範囲で検索します。 数字は、目的の値を表示する行 (2) と列 (3) を示しています。 結果はセルC2になります。

シンプルですね。 しかし、実際のドキュメントを扱う場合、列番号やセルに関する情報はほとんどありません。 それが MATCH() 関数の目的です。

MATCH 関数の構文と使用法

MATCH() 関数は、目的の値を検索し、指定された検索領域にそのおおよその数を表示します。

searchpos() 構文は次のようになります。

=MATCH(検索する値、検索する配列、一致タイプ)

  • 検索値 – 検索する数値またはテキスト。
  • 検索された配列 – 検索が行われる領域。
  • 一致タイプ – 正確な値またはそれに最も近い値を探すかどうかを指定します。
    • 1 (または値が指定されていない) – 指定された値以下の最大値を返します。
    • 0 – 検索された値と完全に一致することを示します。 INDEX() MATCH() の組み合わせでは、ほぼ常に完全一致が必要になるため、0 を書き込みます。
    • -1 – 数式で指定された値以上の最小値を示します。 ソートは降順で行われます。

たとえば、範囲 B1:B3 には、ニューヨーク、パリ、ロンドンが登録されています。 ロンドンはリストの 3 番目であるため、次の式では数字 XNUMX が表示されます。

=EXPOSE(ロンドン,B1:B3,0)

INDEX MATCH 関数の使用方法 

おそらく、これらの機能の共同作業が構築される原理を理解し始めているでしょう。 要するに、 INDEX() は、指定された行と列の中から目的の値を検索します。 MATCH() は、これらの値の数を示します。

=INDEX(値が返される列、MATCH(検索する値、検索する列、0))

それがどのように機能するかを理解するのにまだ苦労していますか？ 多分例はよりよく説明するでしょう。 世界の首都とその人口のリストがあるとします。

日本の首都など、特定の首都の人口規模を調べるには、次の式を使用します。

=INDEX(C2:C10, MATCH(日本, A2:A10,0))

説明：

  • MATCH() 関数は、配列 A2:A10 で値「Japan」を検索し、数値 3 を返します。これは、Japan がリストの XNUMX 番目の値であるためです。 
  • この数字は行番号」を INDEX() 式で使用し、この行から値を出力するように関数に指示します。

したがって、上記の式は標準式になります インデックス (C2:C10,3)。 この数式は、セル C2 から C10 までを検索し、この範囲の 4 番目のセル (CXNUMX) からデータを返します。これは、カウントダウンが XNUMX 行目から開始されるためです。

式に都市名を入れたくないですか？ 次に、任意のセル (F1 など) に書き込み、MATCH() 式の参照として使用します。 そして、動的検索式になります。

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Excel で INDEX と MATCH が VLOOKUP より優れている理由

重要な！ 行数 配列 INDEX() は、行数と同じでなければなりません 考慮された配列 そうしないと、間違った結果が得られます。

ちょっと待ってください。なぜ VLOOKUP() 式を使用しないのでしょうか?

=VLOOKUP(F1, A2:C10, 3, 偽)

 INDEX MATCH のこれらすべての複雑さを理解しようとして時間を無駄にする意味は何ですか?

この場合、どの関数を使用するかは問題ではありません。 これは、INDEX() 関数と MATCH() 関数がどのように連携するかを理解するための単なる例です。 他の例では、VLOOKUP が無力な状況でこれらの関数がどのようなことができるかを示します。 

INDEX MATCH または VLOOKUP

どの検索式を使用するかを決定する際、INDEX() と MATCH() が VLOOKUP よりもはるかに優れていることに多くの人が同意します。 ただし、多くの人はまだ VLOOKUP() を使用しています。 第一に、VLOOKUP() はより単純であり、第二に、ユーザーは INDEX() と MATCH() を使用することのすべての利点を完全には理解していません。 この知識がなければ、複雑なシステムの研究に時間を費やすことに同意する人は誰もいないでしょう。

VLOOKUP() に対する INDEX() と MATCH() の主な利点は次のとおりです。

 

  • 右から左に検索します。 VLOOKUP() は右から左に検索できないため、探している値は常にテーブルの一番左の列にある必要があります。 しかし、INDEX() と MATCH() はこれを問題なく処理できます。 この記事では、実際にどのように見えるかを説明します: 左側で目的の値を見つける方法.

 

  1. 列の安全な追加または削除。 VLOOKUP() 式は、列を削除または追加するときに誤った結果を示します。これは、VLOOKUP() が成功するために正確な列番号が必要であるためです。 当然、列が追加または削除されると、その番号も変更されます。 

また、INDEX() および MATCH() 式では、個々の列ではなく、列の範囲が指定されます。 その結果、式を毎回更新しなくても、列を安全に追加および削除できます。

  1. 検索ボリュームに制限なし. VLOOKUP() を使用する場合、検索基準の合計数が 255 文字を超えてはなりません。そうしないと #VALUE! が返されます。 したがって、データに多数の文字が含まれている場合は、INDEX() と MATCH() が最適なオプションです。
  2. 処理速度が速い。 テーブルが比較的小さい場合、違いに気付く可能性はほとんどありません。 ただし、テーブルに数百または数千の行が含まれ、それに応じて数百および数千の数式がある場合、INDEX () および MATCH () は VLOOKUP () よりもはるかに高速に処理されます。 実際には、Excel はテーブル全体を処理するのではなく、数式で指定された列のみを処理します。 

VLOOKUP() のパフォーマンスへの影響は、ワークシートに VLOOKUP() や SUM() などの多数の数式が含まれている場合に特に顕著になります。 配列内の各値を解析するには、VLOOKUP() 関数を個別にチェックする必要があります。 そのため、Excel は膨大な量の情報を処理する必要があり、これにより作業が大幅に遅くなります。

数式の例 

これらの関数の有用性についてはすでに理解したので、最も興味深い部分である実際の知識の適用に進むことができます。

右から左に検索する式

既に述べたように、VLOOKUP はこの形式の検索を実行できません。 したがって、目的の値が一番左の列にない場合、VLOOKUP() は結果を生成しません。 INDEX() および MATCH() 関数はより用途が広く、値の場所は機能する上で大きな役割を果たしません。

たとえば、テーブルの左側にランク列を追加し、わが国の首都が占める人口のランクを把握しようとします。

セル G1 に、検索する値を書き込み、次の数式を使用して範囲 C1:C10 を検索し、A2:A10 から対応する値を返します。

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Excel で INDEX と MATCH が VLOOKUP より優れている理由

促す。 この数式を複数のセルに使用する場合は、絶対アドレス指定を使用して範囲を修正してください (たとえば、 $A$2: $A$10 および $C$2: 4C$10).

INDEX 露出度が高い 露出度が高い  列と行で検索する

上記の例では、これらの関数を VLOOKUP() の代わりとして使用して、定義済みの行範囲から値を返しました。 しかし、マトリックスまたは両側検索を行う必要がある場合はどうでしょうか?

複雑に聞こえますが、このような計算の式は標準の INDEX() MATCH() 式に似ていますが、XNUMX つだけ違いがあります。 MATCH() 式は XNUMX 回使用する必要があります。 XNUMX 回目は行番号を取得し、XNUMX 回目は列番号を取得します。

=INDEX(配列, MATCH(縦検索値, 検索列, 0), MATCH(横検索値, 検索行, 0))

下の表を見て式を作ってみましょう INDEX() エクスプレス() エクスプレス() 選択した年の特定の国における人口統計を表示するため。

対象国はセル G1 (垂直ルックアップ) にあり、対象年はセル G2 (水平ルックアップ) にあります。 式は次のようになります。

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Excel で INDEX と MATCH が VLOOKUP より優れている理由

この式のしくみ

他の複雑な数式と同様に、個々の方程式に分解することで理解しやすくなります。 そして、個々の関数が何をするかを理解できます。

  • マッチ(G1,A2:A11,0) – A1:A2 の範囲で値 (G11) を探し、この値の数を表示します。この場合は 2 です。
  • 検索(G2,B1:D1,0) – B2:D1 の範囲で値 (G1) を探します。 この場合、結果は 3 でした。

見つかった行番号と列番号は、INDEX() 式の対応する値に送信されます。

=INDEX(B2:D11,2,3)

その結果、範囲 B2:D3 の 2 つの行と 11 つの列の交点にあるセルにある値が得られます。 式は、セル D3 にある目的の値を示しています。

INDEXとMATCHで複数条件検索

VLOOKUP() のガイドを読んだことがあれば、おそらく複数の検索式を試したことがあるでしょう。 ただし、この検索方法には XNUMX つの重大な制限があります。補助列を追加する必要があることです。

しかし、良いニュースはそれです INDEX() と MATCH() を使用すると、ワークシートを編集または変更することなく、複数の条件を検索できます。

INDEX() MATCH() の一般的な複数条件検索式は次のとおりです。

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

ノート： この式は、キーボード ショートカットと一緒に使用する必要があります CTRL+SHIFT+ENTER。

2 つの条件に基づいて、探している値を見つける必要があるとします。 買い手 и 製品。

これには、次の式が必要です。

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

この式では、C2:C10 は検索が行われる範囲です。 F1 – この状態、 A2:A10 — 条件を比較する範囲、 F2 – 条件 2, V2：V10 – 条件 2 の比較範囲。

数式で作業の最後にコンビネーションを押すことを忘れないでください Ctrl + Shift + Enter – Excel は、例に示すように、式を中括弧で自動的に閉じます。

Excel で INDEX と MATCH が VLOOKUP より優れている理由

作業に配列数式を使用したくない場合は、別の INDEX() を数式に追加して Enter キーを押すと、次の例のようになります。

Excel で INDEX と MATCH が VLOOKUP より優れている理由

これらの数式の仕組み

この式は、標準の INDEX() MATCH() 式と同じように機能します。 複数の条件を検索するには、正しい条件と正しくない個々の条件を表す複数の False 条件と True 条件を作成するだけです。 そして、これらの条件は、配列の対応するすべての要素に適用されます。 この数式は、False と True の引数をそれぞれ 0 と 1 に変換し、1 が文字列で見つかった一致する値である配列を出力します。 MATCH() は、1 に一致する最初の値を見つけ、それを INDEX() 式に渡します。 次に、目的の列から指定された行に既に目的の値が返されます。

配列を持たない数式は、INDEX() が独自に処理できるかどうかに依存します。 式の 0 番目の INDEX() は偽 (XNUMX) に一致するため、それらの値を含む配列全体を MATCH() 式に渡します。 

これは、この式の背後にあるロジックのかなり長い説明です。 詳細については、記事「複数条件のINDEX MATCH'。

INDEX と MATCH の AVERAGE、MAX、MIN

Excel には、平均値、最大値、および最小値を見つけるための独自の特別な機能があります。 しかし、これらの値に関連付けられたセルからデータを取得したい場合はどうでしょうか? この場合 AVERAGE、MAX、および MIN は、INDEX および MATCH と組み合わせて使用​​する必要があります。

INDEX MATCH と MAX

列 D の最大値を見つけて列 C に表示するには、次の式を使用します。 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH と MIN

D 列の最小値を見つけて C 列に表示するには、次の式を使用します。

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

検索インデックスとSERPENT

列 D の平均値を見つけて、この値を C に表示するには:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

データの書き込み方法に応じて、MATCH() の 1 番目の引数は 0、1、または -XNUMX のいずれかになります。

  • 列が昇順で並べ替えられている場合は、1 を設定します (その後、式は平均値以下の最大値を計算します)。
  • 並べ替えが降順の場合は -1 (式は平均以上の最小値を出力します)。
  • ルックアップ配列に平均と正確に等しい値が含まれている場合は、それを 0 に設定します。 

 この例では、人口は降順でソートされているため、-1 を入力します。 人口値 (13,189) が平均値 (000) に最も近いため、結果は東京になります。

Excel で INDEX と MATCH が VLOOKUP より優れている理由

VLOOKUP() もこのような計算を実行できますが、配列数式としてのみ実行できます: AVERAGE、MIN、および MAX を指定した VLOOKUP.

INDEX MATCH と ESND/IFERROR

数式が目的の値を見つけられない場合、エラーがスローされることに既にお気付きでしょう。 ＃N / A. 標準エラー メッセージをより有益なものに置き換えることができます。 たとえば、式に引数を設定します XNUMXthで:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

この式では、表にないデータを入力すると、指定されたメッセージがフォームに表示されます。

Excel で INDEX と MATCH が VLOOKUP より優れている理由

すべてのエラーをキャッチしたい場合は、 XNUMXthで 使用することができる IFエラー:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), 「問題が発生しました!」)

ただし、標準エラーは式の違反を報告するため、この方法でエラーをマスキングすることはお勧めできません。

INDEX MATCH() 関数の使用に関するガイドがお役に立てば幸いです。

コメントを残す