2次元検索のExcelでのインデックスマッチマッチ

このチュートリアルでは、Excelで2次元ルックアップを実行するためのいくつかの異なる式を紹介しています。代替案を見て、お気に入りを選択してください:)

Excelスプレッドシートで何かを検索するとき、ほとんどの場合、列または列の水平方向に垂直に検索します。ただし、行と列の両方を見る必要がある場合があります。言い換えれば、特定の行と列の交差点で値を見つけることを目指しています。これはマトリックスルックアップ（別名2次元または2ウェイルックアップ）と呼ばれ、このチュートリアルは4つの異なる方法でそれを行う方法を示しています。

• 行と列のルックアップに一致するインデックスマッチ
• vlookupを使用した双方向の検索
• Xlookupを使用した2ウェイルックアップ
• supproductを使用した2次元検索
• 名前付き範囲を使用したマトリックスルックアップ（明示的な交差点）

Excel Index Matchの一致式

Excelで双方向の検索を行う最も人気のある方法は、インデックスマッチマッチを使用することです。これは、行番号と列番号の両方を取得するために、もう1つの一致関数を追加するクラシックインデックスマッチ式のバリエーションです。

index（ data_array 、match（ vlookup_value 、 lookup_column_range 、0）、match（ hlookup value 、 lookup_row_range 、0））

例として、下の表から特定の年に特定の動物の個体群を引き出すための式を作りましょう。手始めに、すべての議論を定義します。

• data_array -b2：e4（row and column headersが含まれないデータセル）
• vlookup_value -h1（ターゲット動物）
• lookup_column_range -a2：a4（row headers：animal names）-a3：a4
• hlookup_value -h2（ターゲット年）
• lookup_row_range -b1：e1（列ヘッダー：年）

すべての議論をまとめると、双方向の検索のためにこの式が得られます。

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

2つ以上の基準で双方向の検索を行う必要がある場合は、この記事をご覧ください：行と列の複数の基準とのインデックスマッチ。

この式の仕組み

一見して少し複雑に見えるかもしれませんが、フォーミュラの論理は本当に簡単で理解しやすいです。インデックス関数は、行番号と列番号に基づいてデータアレイから値を取得し、2つの一致関数がそれらの数値を提供します。

INDEX(B2:E4, row_num, column_num)

ここでは、Match（lookup_value、lookup_array、[match_type]）の能力を活用して、 lookup_arrayでlookup_valueの相対的な位置を返します。

したがって、行番号を取得するために、行ヘッダー（A2：A4）を越えて関心のある動物（H1）を検索します。

MATCH(H1, A2:A4, 0)

列番号を取得するには、列ヘッダー（B1：E1）で目標年（H2）を検索します。

MATCH(H2, B1:E1, 0)

どちらの場合も、3番目の引数を0に設定することにより、正確な一致を探します。

この例では、vlookup値（ホッキョクグマ）がA3にあるため、最初の一致は2を返します。これはA2：A4の2 ^NDセルです。 2番目の一致は3を返します。これは、B1：E1の3 ^RDセルであるD1にHlookup値（2000）が見つかったためです。

上記を考えると、式は次のように減少します。

INDEX(B2:E4, 2, 3)

データアレイB2：E4の2 ^nd行と3 ^rd列の交差点で値を返します。これは、セルD3の値です。

2ウェイルックアップのためのvlookupおよびマッチフォーミュラ

Excelで2次元検索を行う別の方法は、vlookupとMatch関数の組み合わせを使用することです。

vlookup（ vlookup_value 、 table_array 、match（ hhlookup_value 、 lookup_row_range 、0）、false）

サンプルテーブルの場合、式は次の形状を取得します。

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

どこ：

• Table_Array -A2：E4（行ヘッダーを含むデータセル）
• vlookup_value -h1（ターゲット動物）
• hlookup_value -h2（ターゲット年）
• lookup_row_range -a1：e1（列ヘッダー：年）

この式の仕組み

式のコアは、正確な一致（falseに設定された最後の引数）に設定されたvlookup関数です。これは、テーブル配列（A2：E4）の最初の列でルックアップ値（H1）を検索し、同じ行の別の列から値を返します。値を返す列を決定するには、正確な一致（0に設定された最後の引数）にも構成されている一致関数を使用します。

MATCH(H2, A1:E1, 0)

列ヘッダー（A1：E1）全体のH2の値を検索し、発見されたセルの相対位置を返します。私たちの場合、目標年（2010）はE1にあります。E1はルックアップアレイで5^番目です。したがって、5番はvlookupのcol_index_num引数に送られます。

VLOOKUP(H1, A2:E4, 5, FALSE)

vlookupはそこからそれを取り、A2のルックアップ値と正確な一致を見つけ、同じ行、つまりセルE2の5^列から値を返します。

重要なメモ！式が正しく機能するためには、vlookupのtable_array （a2：e4）と一致のlookup_array （a1：e1）には同じ数の列が必要です。そうしないと、 col_index_numに一致する数字は間違っています（テーブル_arrayの列の位置には対応しません）。

xlookup関数の行と列を見る

最近、MicrosoftはExcelにもう1つの機能を導入しました。これは、vlookup、Hlookup、Index Matchなどのすべての既存のルックアップ関数を置き換えることを目的としています。とりわけ、Xlookupは特定の行と列の交差点を見ることができます。

xlookup（ vlookup_value 、 vlookup_column_range 、xlookup（ hlookup_value 、 hlookup_row_range 、 data_array ）））

サンプルデータセットの場合、式は次のようになります。

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

注記。 Xlookup関数は、Microsoft 365、Excel 2021、およびExcel for the WebのExcelでのみ利用できます。

この式の仕組み

フォーミュラは、Xlookupの能力を使用して、行全体または列を返します。内部関数は、ヘッダー行の目標年を検索し、その年のすべての値を返します（この例では、1980年のこの例で）。これらの値は、外側のxlookupのreturn_array引数に送られます。

XLOOKUP(H1, A2:A4, {22000;25000;700}))

外側のXlookup関数は、列ヘッダー全体のターゲット動物を検索し、Return_Arrayから同じ位置で値を返します。

双方向ルックアップのための標準式

等式関数は、Excelのスイスナイフのようなものです。特に複数の基準を評価することに関しては、指定された目的を超えて多くのことを行うことができます。

行と列で2つの基準を調べるには、この汎用式を使用します。

sumproduct（ vlookup_column_range = vlookup_value ） *（ hllookup_row_range = hlookup_value ）、 data_array ）

データセットで2ウェイルックアップを実行するには、次のように式が表示されます。

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

以下の構文も機能します：

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

この式の仕組み

フォーミュラの中心にあるのは、行と列ヘッダー（H1のターゲット動物（A2：A4のすべての動物名）に対して2つのルックアップ値を比較し、B1：E1のすべての年にH2の目標年と比較してください）：

(A2:A4=H1) * (B1:E1=H2)

これにより、Trueの表現が一致する2つのアレイがTrueとfalse値と誤った値が表示されます。

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

乗算操作は、真の値とfalse値を1と0に強制し、4列と3行の2次元配列を生成します（行はセミコロンで分離され、各列はコンマで分離されます）：

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

等式関数は、上記のアレイの要素に同じ位置にあるb2：e4のアイテムを掛けます。

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

また、ゼロを乗算するとゼロが得られるため、最初の配列の1に対応するアイテムのみが生き残ります。

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

最後に、Sumproductは結果の配列の要素を合計し、2000の値を返します。

注記。テーブルに同じ名前の複数の行または/および列ヘッダーがある場合、最終配列にはゼロ以外の複数の数値が含まれ、それらすべての数値が追加されます。その結果、両方の基準を満たす値の合計が得られます。これが、supproductの式をインデックスマッチとvlookupとは異なるものにしている理由です。

名前付き範囲を使用したマトリックスルックアップ（明示的な交差点）

Excelでマトリックスルックアップを行うためのもう1つの驚くほど簡単な方法は、名前の範囲を使用することです。方法は次のとおりです。

パート1：名前の列と行

各行とテーブルの各列に名前を付ける最速の方法は次のとおりです。

1. テーブル全体を選択します（この場合はA1：E4）。
2. [式]タブの[定義済みの名前]グループで、 [選択]から[作成]をクリックするか、 CTRLシフトF3ショートカットを押します。
3. [選択]ダイアログボックスの[名前の作成]ボックスで、最上段と左の列を選択し、[OK]をクリックします。

   

これにより、行と列ヘッダーに基づいて名前が自動的に作成されます。ただし、いくつかの注意事項があります。

• 列および/または行のヘッダーが数字であるか、Excel名で許可されていない特定の文字が含まれている場合、そのような列と行の名前は作成されません。作成された名前のリストを表示するには、名前マネージャー（ CTRL F3 ）を開きます。いくつかの名前が欠落している場合は、Excelの範囲の名前を付ける方法で説明されているように、それらを手動で定義します。
• いくつかの行または列ヘッダーにスペースが含まれている場合、スペースはアンダースコア、たとえばpolar_bearに置き換えられます。

サンプルテーブルの場合、Excelは行名のみを自動的に作成しました。列ヘッダーが数字であるため、列名を手動で作成する必要があります。これを克服するために、 _1990のようなアンダースコアで数値を序文を序文することができます。

その結果、次の名前の範囲があります。

パート2：マトリックスルックアップ式を作成します

特定の行と列の交差点で値を引くには、空のセルの次の汎用式のいずれかを入力するだけです。

= row_name column_name

またはその逆：

= column_name row_name

たとえば、1990年にブルークジラの人口を獲得するには、次のように式が簡単です。

=Blue_whale _1990

誰かがより詳細な指示を必要とする場合、次の手順でプロセスを説明します。

1. 結果を表示したいセルで、等式記号（=）を入力します。
2. ターゲット行の名前の入力を開始します。たとえば、 Blue_Whale 。いくつかの文字を入力した後、Excelはあなたの入力に一致するすべての既存の名前を表示します。希望の名前をダブルクリックして、式に入力します。

   

3. 行名の後、この場合の交差演算子として機能するスペースを入力します。
4. ターゲット列名（この場合は_1990 ）を入力します。

   

5. 行名と列名の両方が入力されるとすぐに、Excelは対応する行とテーブルの列を強調表示し、 Enterを押して式を完成させます。

   

マトリックスの検索が完了し、以下のスクリーンショットは結果を示しています。

これが、Excelの行と列を調べる方法です。お読みいただきありがとうございます。来週のブログでお会いしましょう！

利用可能なダウンロード

2次元ルックアップサンプルワークブック

以上が2次元検索のExcelでのインデックスマッチマッチの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。