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

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

Excel Index Matchの一致式

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

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つ以上の基準で雙方向の検索を行う必要がある場合は、この記事をご覧ください：行と列の複數(shù)の基準とのインデックスマッチ。

この式の仕組み

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

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番目の引數(shù)を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関數(shù)の組み合わせを使用することです。

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に設定された最後の引數(shù)）に設定されたvlookup関數(shù)です。これは、テーブル配列（A2：E4）の最初の列でルックアップ値（H1）を検索し、同じ行の別の列から値を返します。値を返す列を決定するには、正確な一致（0に設定された最後の引數(shù)）にも構(gòu)成されている一致関數(shù)を使用します。

MATCH(H2, A1:E1, 0)

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

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

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

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

xlookup関數(shù)の行と列を見る

最近、MicrosoftはExcelにもう1つの機能を?qū)毪筏蓼筏?。これは、vlookup、Hlookup、Index Matchなどのすべての既存のルックアップ関數(shù)を置き換えることを目的としています。とりわけ、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関數(shù)は、Microsoft 365、Excel 2021、およびExcel for the WebのExcelでのみ利用できます。

この式の仕組み

フォーミュラは、Xlookupの能力を使用して、行全體または列を返します。內(nèi)部関數(shù)は、ヘッダー行の目標年を検索し、その年のすべての値を返します（この例では、1980年のこの例で）。これらの値は、外側(cè)のxlookupのreturn_array引數(shù)に送られます。

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

外側(cè)のXlookup関數(shù)は、列ヘッダー全體のターゲット動物を検索し、Return_Arrayから同じ位置で値を返します。

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

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

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

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

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

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

以下の構(gòu)文も機能します：

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

この式の仕組み

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

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

これにより、Trueの表現(xiàn)が一致する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}

等式関數(shù)は、上記のアレイの要素に同じ位置にある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は結(jié)果の配列の要素を合計し、2000の値を返します。

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

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

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

パート1：名前の列と行

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

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

   

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

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

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

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

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

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

= row_name column_name

またはその逆：

= column_name row_name

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

=Blue_whale _1990

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

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

   

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

   

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

   

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

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

利用可能なダウンロード

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

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