Excel

複数の基準を持つINDEXとMATCH

Index Match With Multiple Criteria

Excelの数式:複数の基準を持つINDEXとMATCH一般式
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
概要

複数の基準を使用してINDEXおよびMATCHで値を検索するには、配列数式を使用できます。示されている例では、H8の式は次のとおりです。





 
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

注:これは 配列式 、およびコントロール+シフト+入力で入力する必要があります。 Excel 365

説明

これはより高度な式です。基本については、を参照してください。 INDEXとMATCHの使用方法





Excelの正味現在価値計算機

通常、INDEX MATCH数式は、1列の範囲を調べ、指定された基準に基づいて一致を提供するようにMATCHを設定して構成されます。の値を連結せずに ヘルパー列 、または数式自体では、複数の基準を指定する方法はありません。

この式は、を使用してこの制限を回避します ブール論理 を作成するには 配列 1と0の組み合わせで、3つの基準すべてに一致する行を表し、MATCHを使用して最初に見つかった1つに一致します。 1と0の一時的な配列は、次のスニペットで生成されます。



 
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

ここでは、H5のアイテムをすべてのアイテムと比較し、H6のサイズをすべてのサイズと比較し、H7の色をすべての色と比較します。最初の結果は、次のようなTRUE / FALSE結果の3つの配列です。

 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

ヒント: F9を使用してこれらの結果を確認します 。数式バーで式を選択し、F9キーを押すだけです。

数学演算(乗算)は、TRUEFALSE値を1と0に変換します。

Excelで自動入力を使用する方法
 
{1110001}*{0010010}*{1010001}

乗算後、次のような単一の配列ができます。

 
{0010000}

これは、ルックアップ値1でルックアップ配列としてMATCH関数に入力されます。

 
 MATCH (1,{0010000})

この時点で、式は標準のINDEXMATCH式です。 MATCH関数は3をINDEXに返します。

 
= INDEX (E5:E11,3)

INDEXは$ 17.00の最終結果を返します。

アレイの視覚化

上で説明した配列は、視覚化するのが難しい場合があります。下の画像は基本的な考え方を示しています。列B、C、およびDは、例のデータに対応しています。列Fは、3つの列を乗算することによって作成されます。 MATCHに渡された配列です。

複数の基準を持つINDEXとMATCH-配列の視覚化

非アレイバージョン

この数式に別のINDEXを追加して、control + shift + enterを使用して配列数式として入力する必要をなくすことができます。

 
= INDEX (rng1, MATCH (1, INDEX ((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

INDEX関数は配列をネイティブに処理できるため、2番目のINDEXは、ブール論理演算で作成された配列を「キャッチ」し、同じ配列を再びMATCHに返すためにのみ追加されます。これを行うために、INDEXは0行1列で構成されます。行がゼロのトリックにより、INDEXは配列から列1を返します(とにかくすでに1列です)。

なぜ非アレイバージョンが必要なのですか? Ctrl + Shift + Enterを使用して配列数式を入力するのを忘れると、数式が誤った結果を返すことがあります。したがって、非配列式はより「防弾」です。ただし、トレードオフはより複雑な式です。

注:で Excel 365 、特別な方法で配列数式を入力する必要はありません。

日付から年をExcelでプル
添付ファイル ファイル 複数のcriteria.xlsxを使用したINDEXおよびMATCH 著者デイブブランズ


^