複数の基準とのExcelインデックスマッチ - 式の例

チュートリアルでは、インデックスとマッチを使用してExcelの複数の基準を使用して検索する方法を示しています。

Microsoft Excelは垂直および水平方向のルックアップに特別な機能を提供しますが、エキスパートユーザーは通常、それらをインデックスマッチに置き換えます。とりわけ、列と行で2つ以上の基準を調べることができます。このチュートリアルでは、特定のニーズに合わせて式を簡単に調整できるように、構文と内部メカニックを詳細に説明しています。例を容易にするには、サンプルワークブックをダウンロードできます。

• 複数の基準とのインデックスマッチ
• 2つ以上の基準で非アレイインデックスが一致します
• インデックスは行と列の複数の基準を一致させます（マトリックスルックアップ）

複数の基準とのExcelインデックスマッチ

大規模なデータベースを操作する場合、何かを見つける必要があるが、検索の一意の識別子がない場合に自分自身を見つけることがあります。この場合、いくつかの条件での検索が唯一の解決策です。

別々の列の複数の基準に基づいて値を検索するには、この汎用式を使用します。

{= index（ return_range 、match（1、（ criteria1 = range1 ） *（ criteria2 = range2 ） *（…）、0）}

どこ：

• return_rangeは、値を返す範囲です。
• Criteria1 、 Criteria2 、…は満たす条件です。
• range1 、 range2 、…は、対応する基準をテストする範囲です。

重要なメモ！これは配列式であり、 Ctrl Shift Enterで完了する必要があります。これにより、フォーミュラが{curlyブラケット}に囲まれます。これは、Excelの配列式の視覚的な兆候です。ブレースを手動で入力してみないでください。

フォーミュラは、単一の基準に基づいて一致を返す象徴的なインデックスマッチの高度なバージョンです。複数の基準を評価するために、配列式の演算子として機能する乗算操作を使用します。以下に、現実の例とロジックの詳細な説明があります。

ヒント。 Excel 365および2021では、複数の基準でXlookup式を使用できます。

いくつかの基準とのインデックスマッチ - 式の例

この例では、独自の行に個別の基準の組み合わせ（私たちの場合は地域の月のアイテム）を使用した、いわゆる「フラットファイル」形式のテーブルを使用します。私たちの目標は、特定の地域と月の特定のアイテムの販売数値を取得することです。

次のセルのソースデータと基準を使用してください。

• return_range （販売）-D2：D13
• Criteria1 （ターゲット領域）-G1
• Criteria2 （ターゲット月）-G2
• Criteria3 （ターゲットアイテム）-G3
• range1 （領域）-a2：a13
• range2 （月）-B2：B13
• range3 （アイテム）-C2：C13

フォーミュラは次の形をとります。

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

式を入力します。たとえばG4で、Ctrl Shift Enterを押して完了すると、次の結果が得られます。

この式の仕組み

最も難しい部分は一致関数ですので、最初に把握しましょう。

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

覚えているように、Match（lookup_value、lookup_array、[match_type]）は、ルックアップ配列のルックアップ値を検索し、配列内のその値の相対位置を返します。

私たちの式では、議論は次のとおりです。

• lookup_value ：1
• lookup_array ：（g1 = a2：a13） *（g2 = b2：b13） *（g3 = c2：c13）
• match_type ：0

第1 ^st引数はクリスタルクリアです - 関数は数1を検索します。3rd^引数は0に設定されていることを意味します。

問題は、なぜ「1」を検索するのですか？答えを得るために、各基準を対応する範囲と比較するルックアップアレイを詳しく見てみましょう。G1のターゲット領域（A2：A13）、G2のターゲット月（B2：B13）、G3のすべてのアイテムに対してターゲット項目（C2：C13）を見てみましょう。中間結果は、trueがテストされた条件を満たす値を表す真とFalseの3つの配列です。これを視覚化するには、式の個々の式を選択し、F9キーを押して、各式が評価するものを確認できます。

乗算操作は、それぞれ真および偽の値を1と0に変換します。

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

また、0を乗算すると常に0が与えられるため、結果の配列はすべての基準を満たす行に1のみを持っています。

{0;0;1;0;0;0;0;0;0;0;0;0}

上記の配列は、一致のlookup_array引数に移動します。 lookup_value 1では、関数はすべての基準が真である行の相対位置を返します（この場合は行3）。配列にいくつかの1がある場合、最初の位置の位置が返されます。

一致によって返される数は、インデックスのrow_num引数（array、row_num、[column_num]）関数に直接送られます。

=INDEX(D2:D13, 3)

そして、それは115ドルの結果をもたらします。これはD2：D13アレイの3 ^RD値です。

複数の基準を持つ非アレイインデックスマッチ式

前の例で説明した配列の式は、経験豊富なユーザーにとってうまく機能します。しかし、あなたが他の誰かのために式を構築していて、誰かが配列関数を知らないという場合、彼らは誤ってそれを壊すかもしれません。たとえば、ユーザーはフォーミュラをクリックして調べてから、 Ctrl Shift Enterの代わりにEnterを押します。そのような場合、アレイを避け、より弾丸のある通常の式を使用することが賢明でしょう。

index（ return_range 、match（1、index（（ criteria1 = range1 ） *（ criteria2 = range2 ） *（）、0、1）、0））

サンプルデータセットの場合、式は次のとおりです。

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

この式の仕組み

インデックス関数はアレイをネイティブに処理できるため、別のインデックスを追加して、2つ以上のTrue/False配列を掛けることで作成される1と0の配列を処理します。 2番目のインデックスは、式の0 row_num引数で構成されており、単一の値ではなく列アレイ全体を返すようにします。とにかく1列の配列なので、 column_numに1を安全に供給できます。

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

この配列は、マッチ関数に渡されます。

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

一致は、すべての基準が真である行番号を見つけ（より正確には、指定された配列内のその行の相対位置）、最初のインデックスのrow_num引数にその番号を渡します。

=INDEX(D2:D13, 3)

行と列の複数の基準とのインデックスマッチ

この例は、行と列で2つ以上の基準をテストすることにより、ルックアップを実行する方法を示しています。実際、これは、いわゆる「マトリックスルックアップ」または「双方向のルックアップ」のより複雑なケースであり、複数のヘッダー列があります。

これは、行と列に複数の基準を持つ一般的なインデックスマッチ式です。

{= index（ table_array 、match（ vlookup_value 、 lookup_column 、0）、match（ hhlookup_value1 ＆ hlookup_value2 、 lookup_row1 ＆ lookup_row2、0 ）}}

どこ：

Table_Array-内部で検索するマップまたは領域、つまり列ヘッダーと行ヘッダーを除くすべてのデータ値。

vlookup_value-列で垂直に探している値。

lookup_column-検索する列の範囲、通常は行ヘッダー。

hlookup_value1、hlookup_value2、… - 列で水平方向に探している値。

lookup_row1、lookup_row2、… - 行は検索する範囲、通常は列ヘッダーです。

重要なメモ！式が正しく機能するには、 Ctrlシフトが入力されたアレイ式として入力する必要があります。

これは、特定の行と列の交差点で値を検索する古典的な双方向ルックアップ式のバリエーションです。違いは、複数の列ヘッダーを評価するために、いくつかのhlookup値と範囲を連結することです。ロジックをよりよく理解するには、次の例を考慮してください。

複数の基準を備えたマトリックスルックアップ - 式の例

以下のサンプル表では、行ヘッダー（アイテム）と2つの列ヘッダー（領域とベンダー）に基づいた値を検索します。フォーミュラを構築しやすくするには、まずすべての基準と範囲を定義しましょう。

• Table_Array -B3：E4
• vlookup_value （ターゲットアイテム）-H1
• lookup_column （row headers：items）-a3：a4
• hlookup_value1 （ターゲット領域）-H2
• hlookup_value2 （ターゲットベンダー）-H3
• lookup_row1 （列ヘッダー1：領域）-b1：e1
• lookup_row2 （列ヘッダー2：ベンダー）-B2：E2

そして今、引数を上記の一般的な式に提供すると、この結果が得られます。

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

CTRLシフトを押して式を完成させることを忘れないでくださいショートカットを入力すると、複数の基準でマトリックスの検索が正常に行われます。

この式の仕組み

垂直および水平方向に検索しているため、インデックス（array、row_num、column_num）関数の行数と列番号の両方を供給する必要があります。

Row_numは、H1のターゲットアイテム（リンゴ）をA3：A5のRow Headersと比較するMatch（H1、A3：A5、0）によって配信されます。これは、「リンゴ」が指定された範囲の最初の項目であるため、1の結果が得られます。

column_numは、2つのルックアップ値と2つのルックアップアレイを連結することで解決されます。

成功の重要な要素は、ルックアップ値が列ヘッダーと正確に一致し、同じ順序で連結する必要があることです。これを視覚化するには、一致式の最初の2つの引数を選択するには、 F9を押してください。各引数が評価するものがわかります。

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

「ノースベンダー2」が配列の2番目の要素であるため、関数は2を返します。

この時点で、長い2次元インデックスマッチ式がこのシンプルなものに変換されます。

=INDEX(B3:E5, 1, 2)

範囲B3：E5の1列目と2番目の列の交差点で値を返します。これは、セルC3の値です。

これが、Excelで複数の基準を検索する方法です。お読みいただきありがとうございます。来週のブログでお会いしましょう！

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

Excel Indexは複数の基準（.xlsxファイル）を一致させる

以上が複数の基準とのExcelインデックスマッチ - 式の例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。