二維查找中Excel中的索引匹配

教程展示了一些不同的公式，以在Excel中執行二維查找。只需瀏覽替代方案，然後選擇您的喜歡:)

在搜索Excel電子表格中的某些內容時，大多數時候您會在列中或水平瀏覽。但是有時您需要瀏覽行和列。換句話說，您的目標是在某個行和列的交集處找到一個值。這稱為Matrix查找（又稱2維查找），該教程顯示瞭如何以4種不同的方式進行操作。

• 索引匹配與行和列查找
• 使用Vlookup的雙向查找
• Xlookup的2向查找
• 二維查找和蘇馬的產品
• 帶有命名範圍的矩陣查找（顯式交叉）

Excel索引匹配公式

在Excel中進行雙向查找的最流行方式是使用索引匹配。這是經典索引匹配公式的一種變體，您可以在其中添加一個匹配功能，以便獲得行號和列號：

索引（ data_array ，match（ vlookup_value ， lookup_column_range ，0），匹配（ hlookup value ， lookup_row_range ，0））

例如，讓我們製作一個公式，以從下表中從給定的一年中拉出某種動物的種群。對於初學者，我們定義了所有論點：

• data_array -b2：e4（數據單元，不包括行和列標題）
• vlookup_value -H1（目標動物）
• Lookup_column_range -A2：A4（行標頭：動物名稱） - A3：A4
• Hlookup_value -H2（目標年）
• Lookup_row_range -B1：E1（列標題：年）

將所有參數放在一起，您將獲得此公式進行雙向查找：

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

如果您需要使用兩個以上標准進行雙向查找，請查看本文：索引與行和列中的多個標準匹配。

該公式如何工作

乍一看可能看起來有些複雜，但公式的邏輯確實很簡單且易於理解。索引函數根據行和列號從數據陣列中檢索一個值，並且兩個匹配函數提供了這些數字：

INDEX(B2:E4, row_num, column_num)

在這裡，我們利用匹配的能力（lookup_value，lookup_array，[match_type]）返回lookup_value在lookup_array中的相對位置。

因此，要獲取行號，我們在行標頭（A2：A4）上搜索感興趣的動物（H1）：

MATCH(H1, A2:A4, 0)

要獲取列號，我們在整個列標題（B1：E1）上搜索目標年度（H2）：

MATCH(H2, B1:E1, 0)

在這兩種情況下，我們都通過將第三個參數設置為0來查找確切的匹配。

在此示例中，第一匹匹配返回2，因為我們的Vlookup值（北極熊）在A3中找到，該值是A2：A4中的2^個單元格。第二個匹配返回3，因為在D1中發現了Hlookup值（2000），即B1：E1中的3 ^rd單元格。

鑑於上述，該公式減少為：

INDEX(B2:E4, 2, 3)

並在數據陣列b2：e4中的^第2行和3^列列的相交處返回一個值，該值是單元格D3中的一個值。

vlookup和2向查找的匹配公式

在Excel中進行二維查找的另一種方法是使用vlookup和匹配函數的組合：

vlookup（ vlookup_value ， table_array ，match（ hlookup_value ， lookup_row_range ，0），false），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（列標題：年）

該公式如何工作

公式的核心是為精確匹配配置的vlookup函數（最後一個參數設置為false），該函數在表陣列（A2：e4）的第一列中搜索查找值（H1），並從同一行中的另一列返回一個值。要確定要從哪個列返回值的列，您使用的匹配函數也配置為確切匹配（最後一個參數設置為0）：

MATCH(H2, A1:E1, 0)

匹配搜索在列標頭（A1：E1）的H2中的值，並返回發現的單元格的相對位置。在我們的情況下，目標年（2010年）在E1中發現，該陣列位於查找陣列中的^第5個。因此，數字5轉到vlookup的col_index_num參數：

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

Vlookup從那裡拿走它，找到A2中其查找值的確切匹配，並從同一行中的^第5列返回一個值，即單元E2。

重要說明！為了使公式正確工作，vookup的table_array （A2：e4）和Match的Lookup_array （A1：E1）必須具有相同數量的列，否則匹配的數字通過col_index_num傳遞給col_index_num將不正確（與table_array中的列的位置不符）。

xlookup函數以行和列查看

最近，Microsoft在Excel中引入了另一個功能，該功能旨在替換所有現有的查找功能，例如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的Excel中可用。

該公式如何工作

該公式使用Xlookup返回整個行或列的能力。內部功能在標題行中搜索目標年份，並返回該年的所有值（在本示例中為1980年）。這些值轉到外部Xlookup的return_array參數：

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

外部Xlookup函數在列標頭上搜索目標動物，並從return_array返回相同位置的值。

雙向查找的Sumproduct公式

Sumproduct功能就像Excel中的瑞士刀一樣 - 它可以做很多超出其指定目的的事情，尤其是在評估多個標準時。

要查找以行和列中的兩個標準，請使用此通用公式：

sumproduct（ vlookup_column_range = vlookup_value ） *（ hlookup_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中的所有動物名稱和H2中的所有動物名稱與B1：E1中的所有年份）進行了比較）：

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

這將產生2個真實和錯誤值的數組，其中True's代表匹配：

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

乘法操作將真實值和錯誤值脅迫到1和0，並產生4列和3行的二維數組（行被分號分離，每列數據列以逗號分隔）：

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

SumProduct的功能將上述數組的元素乘以同一位置的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年的值。

筆記。如果您的表具有具有相同名稱的一行或/和列標題，則最終數組將包含以上的一個以上的數字，並且所有這些數字都將被添加。結果，您將獲得符合這兩個條件的值的總和。這就是使Sumproduct公式與索引匹配和Vlookup不同的原因，後者返回第一個發現的匹配。

帶有命名範圍的矩陣查找（顯式交叉）

在Excel中進行矩陣查找的一種更簡單的方法是使用命名範圍。以下是：

第1部分：名稱列和行

命名每一行的最快方法和表格中的每一列是：

1. 選擇整個表（在我們的情況下，A1：E4）。
2. 在“公式”選項卡上，在定義的名稱組中，單擊“從選擇”或按CTRL Shift F3快捷方式。
3. 在“從選擇”對話框的“創建名稱”中，選擇頂行和左列，然後單擊“確定”。

   

這將根據行和列標題自動創建名稱。但是，有幾個警告：

• 如果您的列和/或行標頭是數字或包含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. 在行名之後，鍵入A空間，在這種情況下，該空間可用作交集操作員。
4. 輸入目標列名稱（在我們的情況下為_1990 ）。

   

5. 一旦輸入了行和列名，Excel將突出顯示表中的相應行和列，然後按Enter完成公式：

   

您的矩陣查找已完成，以下屏幕截圖顯示了結果：

這就是如何在Excel中以行和列查找的方法。我感謝您閱讀，並希望下週在我們的博客上見到您！

可用下載

二維查找樣本工作簿

以上是二維查找中Excel中的索引匹配的詳細內容。更多資訊請關注PHP中文網其他相關文章！