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

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

複數(shù)の基準(zhǔn)とのExcelインデックスマッチ

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

別々の列の複數(shù)の基準(zhǔn)に基づいて値を検索するには、この汎用式を使用します。

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

どこ：

• return_rangeは、値を返す範(fàn)囲です。
• Criteria1 、 Criteria2 、…は満たす條件です。
• range1 、 range2 、…は、対応する基準(zhǔn)をテストする範(fàn)囲です。

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

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

ヒント。 Excel 365および2021では、複數(shù)の基準(zhǔn)でXlookup式を使用できます。

いくつかの基準(zhǔn)とのインデックスマッチ - 式の例

この例では、獨(dú)自の行に個(gè)別の基準(zhǔn)の組み合わせ（私たちの場(chǎng)合は地域の月のアイテム）を使用した、いわゆる「フラットファイル」形式のテーブルを使用します。私たちの目標(biāo)は、特定の地域と月の特定のアイテムの販売數(shù)値を取得することです。

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

• return_range （販売）-D2：D13
• Criteria1 （ターゲット領(lǐng)域）-G1
• Criteria2 （ターゲット月）-G2
• Criteria3 （ターゲットアイテム）-G3
• range1 （領(lǐng)域）-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を押して完了すると、次の結(jié)果が得られます。

この式の仕組み

最も難しい部分は一致関數(shù)ですので、最初に把握しましょう。

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

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

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

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

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

問題は、なぜ「1」を検索するのですか？答えを得るために、各基準(zhǔn)を?qū)潖辘工牍?fàn)囲と比較するルックアップアレイを詳しく見てみましょう。G1のターゲット領(lǐng)域（A2：A13）、G2のターゲット月（B2：B13）、G3のすべてのアイテムに対してターゲット項(xiàng)目（C2：C13）を見てみましょう。中間結(jié)果は、trueがテストされた條件を満たす値を表す真とFalseの3つの配列です。これを視覚化するには、式の個(gè)々の式を選択し、F9キーを押して、各式が評(píng)価するものを確認(rèn)できます。

乗算操作は、それぞれ真および偽の値を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が與えられるため、結(jié)果の配列はすべての基準(zhǔn)を満たす行に1のみを持っています。

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

上記の配列は、一致のlookup_array引數(shù)に移動(dòng)します。 lookup_value 1では、関數(shù)はすべての基準(zhǔn)が真である行の相対位置を返します（この場(chǎng)合は行3）。配列にいくつかの1がある場(chǎng)合、最初の位置の位置が返されます。

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

=INDEX(D2:D13, 3)

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

複數(shù)の基準(zhǔn)を持つ非アレイインデックスマッチ式

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

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

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

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

この式の仕組み

インデックス関數(shù)はアレイをネイティブに処理できるため、別のインデックスを追加して、2つ以上のTrue/False配列を掛けることで作成される1と0の配列を処理します。 2番目のインデックスは、式の0 row_num引數(shù)で構(gòu)成されており、単一の値ではなく列アレイ全體を返すようにします。とにかく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}

この配列は、マッチ関數(shù)に渡されます。

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

一致は、すべての基準(zhǔn)が真である行番號(hào)を見つけ（より正確には、指定された配列內(nèi)のその行の相対位置）、最初のインデックスのrow_num引數(shù)にその番號(hào)を渡します。

=INDEX(D2:D13, 3)

行と列の複數(shù)の基準(zhǔn)とのインデックスマッチ

この例は、行と列で2つ以上の基準(zhǔn)をテストすることにより、ルックアップを?qū)g行する方法を示しています。実際、これは、いわゆる「マトリックスルックアップ」または「雙方向のルックアップ」のより複雑なケースであり、複數(shù)のヘッダー列があります。

これは、行と列に複數(shù)の基準(zhǔn)を持つ一般的なインデックスマッチ式です。

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

どこ：

Table_Array-內(nèi)部で検索するマップまたは領(lǐng)域、つまり列ヘッダーと行ヘッダーを除くすべてのデータ値。

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

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

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

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

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

これは、特定の行と列の交差點(diǎn)で値を検索する古典的な雙方向ルックアップ式のバリエーションです。違いは、複數(shù)の列ヘッダーを評(píng)価するために、いくつかのhlookup値と範(fàn)囲を連結(jié)することです。ロジックをよりよく理解するには、次の例を考慮してください。

複數(shù)の基準(zhǔn)を備えたマトリックスルックアップ - 式の例

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

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

そして今、引數(shù)を上記の一般的な式に提供すると、この結(jié)果が得られます。

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

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

この式の仕組み

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

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

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

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

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

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

この時(shí)點(diǎn)で、長い2次元インデックスマッチ式がこのシンプルなものに変換されます。

=INDEX(B3:E5, 1, 2)

範(fàn)囲B3：E5の1列目と2番目の列の交差點(diǎn)で値を返します。これは、セルC3の値です。

これが、Excelで複數(shù)の基準(zhǔn)を検索する方法です。お読みいただきありがとうございます。來週のブログでお會(huì)いしましょう！

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

Excel Indexは複數(shù)の基準(zhǔn)（.xlsxファイル）を一致させる

以上が複數(shù)の基準(zhǔn)とのExcelインデックスマッチ - 式の例の詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國語 Web サイトの他の関連記事を參照してください。