INDEX-MATCH は、VLOOKUP 関数の制限を解決し、使いやすくなったため、Excel のツールとして人気が高まっています。 Excel の INDEX-MATCH 関数には、VLOOKUP 関数に比べて多くの利点があります。
- INDEX と MATCH は Vlookup よりも柔軟で高速です
- 水平ルックアップ、垂直ルックアップ、双方向ルックアップ、左ルックアップ、大文字と小文字を区別したルックアップ、さらには複数の基準に基づくルックアップを実行することができます。
- 並べ替えられたデータでは、INDEX-MATCH は VLOOKUP より 30% 高速です。これは、大規模なデータセットでは 30% 高速化する方が合理的であることを意味します。
まずは各INDEXとMATCHの詳しい概念から見ていきましょう。
INDEX関数
Excel の INDEX 関数は、範囲内の特定の位置の値を取得する柔軟なツールであると同時に、非常に強力です。言い換えると、行と列のオフセットで指定されたセルの内容を返します。
構文：
=INDEX(reference, [row], [column])>
パラメーター：
- 参照: オフセットされるセルの配列。データ テーブル内の単一の範囲またはデータセット全体を指定できます。 row [オプション]: オフセット行の数。これは、テーブル参照範囲を A1:A5 として選択した場合、抽出するセル/コンテンツがどのくらいの垂直距離にあるかを意味します。ここで、A1 行は 1、A2 行 = 2 などとなります。 row = 4 を指定すると、A4 が抽出されます。 row はオプションなので、行番号を指定しないと、参照範囲内の行全体が抽出されます。この場合はA1～A5です。列 [オプション]: オフセット列の数。これは、テーブル参照範囲を A1:B5 として選択した場合、抽出するセル/コンテンツが水平方向の距離にあることを意味します。ここで、A1 の場合は行が 1 で列が 1 になり、B1 の場合は行が 1 になりますが列が 2 になります。同様に、A2 の場合は行 = 2 列 = 1、B2 の場合は行 = 2 列 = 2 などとなります。行 = 5、列 2 を指定すると、B5 が抽出されます。列はオプションなので、行番号を指定しないと、次に、参照範囲内の列全体が抽出されます。たとえば、行 = 2、列を空にすると、(A2:B2) が抽出されます。行と列の両方を指定しない場合は、参照テーブル全体 (A1:B5) が抽出されます。
参照表: 次の表は、INDEX 関数のすべての例の参照表として使用されます。最初のセルは B3 (FOOD) にあり、最後の斜めのセルは F10 (180) にあります。
例: 以下にインデックス関数の例をいくつか示します。
ケース 1: 行と列については言及されていません。
入力コマンド: =インデックス(B3:C10)
ケース 2: 行のみが言及されています。
入力コマンド: =INDEX(B3:C10,2)
ケース 3: 行と列の両方が記載されています。
入力コマンド: =INDEX(B3:D10,4,2)
ケース 4: 列のみが記載されています。
入力コマンド: =INDEX(B3 : D10 , , 2)
INDEX 関数の問題: INDEX 関数の問題は、探しているデータの行と列を指定する必要があることです。 10,000 行と列の機械学習データセットを扱っていると仮定しましょう。その場合、探しているデータを検索して抽出するのは非常に困難になります。ここで、何らかの条件に基づいて行と列を識別する Match Function の概念が登場します。
MATCH関数
範囲内の項目/値の位置を取得します。これは、VLOOKUP または HLOOKUP のあまり洗練されていないバージョンで、実際のデータではなく位置情報のみを返します。 MATCH では大文字と小文字が区別されず、範囲が水平か垂直かは関係ありません。
構文：
=MATCH(search_key, range, [search_type])>
パラメーター：
- search_key: 検索する値。たとえば、42、Cats、I24 などです。 range: 検索する 1 次元配列。単一の行または単一の列にすることができます。例: A1:A10 、 A2:D2 など。 search_type [オプション]: 検索方法。 = 1 (デフォルト) は、範囲が昇順でソートされている場合に、search_key 以下の最大値を検索します。
- = 0 は、範囲がソートされていない場合に正確な値を検索します。
- = -1 は、範囲を降順に並べ替えたときに、search_key 以上の最小値を検索します。
行番号または列番号は match 関数を使用して見つけることができ、index 関数内で使用できるため、アイテムに関する詳細がある場合は、match を使用してアイテムの行/列を見つけることで、そのアイテムに関するすべての情報を抽出できます。それをインデックス関数にネストします。
参照表: 次の表は、MATCH 関数のすべての例の参照表として使用されます。最初のセルは B3 (FOOD) にあり、最後の対角セルは F10 (180) にあります。
例: 以下に MATCH 関数の例をいくつか示します。
ケース 1: 検索タイプ 0。完全一致を意味します。
入力コマンド: =MATCH(南インド,C3:C10,0)
ケース 2: 検索タイプ 1 (デフォルト)。
入力コマンド: =MATCH(南インド、C3:C10)
BFS vs DFS
ケース 3: 「-1」と入力して検索します。
入力コマンド: =MATCH(南インド,C3:C10,-1)
インデックスマッチを一緒に
前の例では、行と列の静的な値は INDEX 関数で提供されました。行と列の位置に関する事前知識がないと仮定すると、行と列の位置は MATCH 関数を使用して提供できます。これは、値を検索して抽出する動的な方法です。
構文：
=INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition], [Match(SearchKey,Range,Type)/StaticColumnPosition])>
参照表: 以下の参照表を使用します。最初のセルは B3 (FOOD) にあり、最後の対角セルは F10 (180) にあります。
例： タスクがマサラ ドーサのコストを見つけることだとしましょう。列 3 がアイテムのコストを表すことはわかっていますが、マサラ ドーサの行の位置は不明です。この問題は 2 つのステップに分けることができます。
ステップ1： 次の式を使用してマサラ ドーサの位置を見つけます。
=MATCH('Masala Dosa',B3:B10,0)> ここで、B3:B10 は列 Food を表し、0 は完全一致を意味します。マサラドーサの行番号を返します。
ステップ2： マサラドーサのコストを求めてください。 INDEX 関数を使用して、マサラ ドーサのコストを見つけます。マサラドーサの正確な位置が必要な箇所で、INDEX関数内の上記のMATCH関数クエリを代入することで、コストの列番号は既知の3になります。
=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>
INDEX-MATCH を併用した双方向ルックアップ
前の例では、コスト属性の列位置がハードコーディングされていました。つまり、完全に動的ではありませんでした。
ケース 1: Cost の列番号についても知識がないと仮定します。その場合、次の式を使用して取得できます。
=MATCH('Cost',B3:F3,0)> ここで、B3:F3 はヘッダー列を表します。
ケース 2: 行と列の値が MATCH 関数を介して (静的な値を与えずに) 提供される場合、それは双方向ルックアップと呼ばれます。これは次の式を使用して実現できます。
=INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>
左の検索
VLOOKUP 関数に対する INDEX と MATCH の主な利点の 1 つは、左検索を実行できることです。これは、右側の任意の属性を使用して項目の行位置を抽出でき、左側の別の属性の値を抽出できることを意味します。
たとえば、140ルピーの食べ物を買うとします。間接的にビリヤニを買えと言っていることになります。この例では、コスト Rs 140/- がわかっているため、食品を抽出する必要があります。 Cost 列は Food 列の右側に配置されているため。 VLOOKUP を適用すると、コスト列の左側を検索できなくなります。そのため、VLOOKUPを使用して食品名を取得することはできません。
この欠点を克服するには、INDEX-MATCH 関数の左検索を使用できます。
ステップ1： まず、次の式を使用してコスト 140 Rs の行位置を抽出します。
=MATCH(140, D3:D10,0)>
ここで、D3: D10 は、Cost 140 Rs 行番号の検索が行われる Cost 列を表します。
ステップ2： 行番号を取得したら、次のステップは、INDEX 関数を使用して、次の式を使用して食品名を抽出することです。
=INDEX(B3:B10, MATCH(140, D3:D10,0))>
ここで、B3:B10 は食品列を表し、140 は食品のコストです。
大文字と小文字を区別した検索
MATCH 関数自体は大文字と小文字を区別しません。これは、食品名 DHOKLA があり、次の検索ワードで MATCH 関数が使用された場合を意味します。
- ドクラ
- ドクラ
- ドクラ
すべては DHOKLA の行位置を返します。ただし、EXACT 関数を INDEX および MATCH とともに使用すると、大文字と小文字を考慮した検索を実行できます。
正確な関数: Excel EXACT 関数は、大文字と小文字を考慮して 2 つのテキスト文字列を比較し、同じ場合は TRUE を返し、異なる場合は FALSE を返します。 EXACT では大文字と小文字が区別されます。
例:
- EXACT(DHOKLA,DHOKLA): これは True を返します。 EXACT(DHOKLA,Dhokla): これは False を返します。 EXACT(DHOKLA,dhokla): これは False を返します。 EXACT(DHOKLA,DhOkLA): これは False を返します。
例： タスクが食品の種類 Dhokla を大文字と小文字を区別して検索することであるとします。これは次の式を使用して行うことができます。
=INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))> ここで、EXACT 関数は、列 B3:B10 の値が同じ大文字と小文字の Dhokla と一致する場合に True を返し、それ以外の場合は False を返します。これで、MATCH 関数が列 B3:B10 に適用され、正確な値が TRUE の行が検索されます。その後、INDEX 関数は、MATCH 関数によって返された行の列 C3:C10 (食品タイプ列) の値を取得します。
複数の基準の検索
Excel で最も厄介な問題の 1 つは、複数の基準に基づく検索です。言い換えれば、同時に複数の列に一致するルックアップです。以下の例では、INDEX 関数と MATCH 関数、およびブール ロジックを使用して 3 つの列を照合します。
- 食べ物。
- 料金。
- 量。
総コストを抽出します。
例： タスクがパスタの総コストを計算することであるとします。
- 食べ物：パスタ。コスト: 60。数量: 1。
したがって、この例では、一致を実行するための 3 つの基準があります。以下は、複数の条件に基づいて検索する手順です。
ステップ1： まず、次の式を使用して Food Column (B3:B10) と Pasta を一致させます。
'PASTA' = B3:B10>
これにより、B3:B10 (食品列) の値がブール値として変換されます。食べ物がパスタである場合は True で、それ以外の場合は False です。
ステップ2： その後、次の方法でコスト基準を一致させます。
60 = D3:D10>
これにより、D3:D10 (コスト列) の値がブール値として置き換えられます。 Cost=60 の場合は True、それ以外の場合は False です。
ステップ 3: 次のステップでは、次の方法で Quantity = 1 という 3 番目の基準を照合します。
1 = E3:E10>
これにより、E3:E10 列 (数量列) が数量 = 1 の場合は True に置き換えられ、それ以外の場合は False になります。
ステップ 4: 1 番目、2 番目、および 3 番目の基準の結果を乗算します。これはすべての条件の交差点となり、ブール値の True / False を 1/0 として変換します。
ステップ5: 結果は、0 と 1 の列になります。ここでは、MATCH 関数を使用して、1 を含む列の行番号を見つけます。列の値が 1 である場合、それは 3 つの基準をすべて満たしていることを意味するためです。
ステップ6: 行番号を取得した後、INDEX 関数を使用してその行の合計コストを取得します。
=INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))> ここで、F3:F10 は合計コスト列を表します。