本教程顯示了如何使用索引和匹配以及其他幾種方式在Excel中使用多個(gè)標(biāo)準(zhǔn)查找。

盡管Microsoft Excel為垂直和水平查找提供了特殊功能，但專家用戶通常會(huì)用索引匹配替換它們，這在許多方面都優(yōu)于vlookup和hlookup。除其他外，它可以在列和行中查找兩個(gè)或多個(gè)標(biāo)準(zhǔn)。本教程詳細(xì)說明了語(yǔ)法和內(nèi)部力學(xué)，以便您可以輕松地調(diào)整特定需求的公式。為了使示例更容易遵循，歡迎您下載我們的示例工作簿。

Excel索引與多個(gè)標(biāo)準(zhǔn)匹配

在使用大型數(shù)據(jù)庫(kù)時(shí)，您有時(shí)可能會(huì)發(fā)現(xiàn)自己需要找到某些東西，但沒有唯一的搜索標(biāo)識(shí)符。在這種情況下，有幾種條件的查找是唯一的解決方案。

要根據(jù)單獨(dú)列中的多個(gè)標(biāo)準(zhǔn)查找值，請(qǐng)使用此通用公式：

{= index（ return_range ，match（1，（ criteria1 = range1 ） *（ criteria2 = range2 ） *（…），0）}}}}

在哪里：

• return_range是返回值的范圍。
• 標(biāo)準(zhǔn)1 ，標(biāo)準(zhǔn)2 ，…是要滿足的條件。
• Range1 ， Range2 ，…是應(yīng)測(cè)試相應(yīng)標(biāo)準(zhǔn)的范圍。

重要說明！這是一個(gè)數(shù)組公式，必須在Ctrl Shift Enter中完成。這將使您的公式包含在{curly brokkets}中，這是Excel中數(shù)組公式的視覺跡象。不要嘗試手動(dòng)鍵入牙套，那將行不通！

公式是標(biāo)志性索引匹配的高級(jí)版本，該版本根據(jù)單個(gè)標(biāo)準(zhǔn)返回匹配項(xiàng)。為了評(píng)估多個(gè)標(biāo)準(zhǔn)，我們使用在數(shù)組公式中用作和運(yùn)算符的乘法操作。下面，您將找到一個(gè)現(xiàn)實(shí)生活中的示例和邏輯的詳細(xì)說明。

提示。在Excel 365和2021中，您可以使用具有多個(gè)標(biāo)準(zhǔn)的Xlookup公式。

索引與多個(gè)標(biāo)準(zhǔn)匹配 - 公式示例

在此示例中，我們將在其自己的行上使用所謂的“扁平文件”格式中的表格（在我們的情況下為月度數(shù)字）。我們的目標(biāo)是在特定地區(qū)和月份中檢索某些項(xiàng)目的銷售數(shù)字。

借助以下單元格中的源數(shù)據(jù)和標(biāo)準(zhǔn)：

• return_range （銷售）-D2：D13
• 標(biāo)準(zhǔn)1 （目標(biāo)區(qū)域）-G1
• 標(biāo)準(zhǔn)2 （目標(biāo)月）-G2
• 標(biāo)準(zhǔn)3 （目標(biāo)項(xiàng)目）-G3
• Range1 （區(qū)域）-A2：A13
• 范圍2 （月）-B2：B13
• 范圍3 （項(xiàng)目）-C2：C13

該公式采用以下形狀：

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

輸入公式，例如在G4中，通過按CTRL Shift Enter來完成它，您將獲得以下結(jié)果：

該公式如何工作

最棘手的部分是匹配功能，所以讓我們首先弄清楚：

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

您可能還記得，匹配（lookup_value，lookup_array，[match_type]）在查找數(shù)組中搜索查找值，并在數(shù)組中返回該值的相對(duì)位置。

在我們的公式中，論點(diǎn)如下：

• Lookup_value ：1
• Lookup_array ：（ g1 = a2：a13） *（g2 = b2：b13） *（g3 = c2：c13）
• match_type ：0

第1^個(gè)參數(shù)是晶體清晰的 - 函數(shù)搜索數(shù)字1。設(shè)置為0的3 ^rd參數(shù)表示“精確匹配”，即公式返回第一個(gè)發(fā)現(xiàn)的值，該值完全等于查找值。

問題是 - 為什么我們要搜索“ 1”？要獲取答案，讓我們仔細(xì)查看查找陣列，在該陣列中，我們將每個(gè)標(biāo)準(zhǔn)與相應(yīng)的范圍進(jìn)行比較：G1中的目標(biāo)區(qū)域與所有區(qū)域（A2：A13）（A2：A13），G2中的目標(biāo)月對(duì)所有月份（B2：B13）和G3中的目標(biāo)項(xiàng)目（C2：C2：C13）。中間結(jié)果是3個(gè)真實(shí)和錯(cuò)誤的陣列，而true表示滿足已測(cè)試條件的值。要可視化這一點(diǎn)，您可以在公式中選擇單個(gè)表達(dá)式，然后按F9鍵查看每個(gè)表達(dá)式評(píng)估的內(nèi)容：

乘法操作分別將真實(shí)值和錯(cuò)誤值分別轉(zhuǎn)換為1和0：

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

而且由于乘以0總是給出0，因此結(jié)果數(shù)組僅在滿足所有標(biāo)準(zhǔn)的行中只有1個(gè)：

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

上面的數(shù)組轉(zhuǎn)到匹配的Lookup_array參數(shù)。使用Lookup_value為1，該函數(shù)返回所有標(biāo)準(zhǔn)為真的行的相對(duì)位置（在我們的情況下第3行）。如果數(shù)組中有幾個(gè)1，則返回第一個(gè)位置。

匹配返回的數(shù)字直接轉(zhuǎn)到索引的row_num參數(shù)（array，row_num，[column_num]）函數(shù)：

=INDEX(D2:D13, 3)

它產(chǎn)生的結(jié)果為$ 115，這是D2：D13數(shù)組中的3 ^rd值。

具有多個(gè)標(biāo)準(zhǔn)的非陣列索引匹配公式

上一個(gè)示例中討論的陣列公式適合經(jīng)驗(yàn)豐富的用戶。但是，如果您正在為他人建立一個(gè)公式，并且某人不知道數(shù)組功能，他們可能會(huì)無意中打破它。例如，用戶可以單擊您的公式檢查它，然后按Enter而不是Ctrl Shift Enter 。在這種情況下，明智的做法是避免陣列并使用更具防彈性的常規(guī)公式：

index（ return_range ，match（1，index（（ criteria1 = range1 ） *（ criteria2 = range2 ） *（..），0，1），0），0））

對(duì)于我們的示例數(shù)據(jù)集，公式如下：

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

該公式如何工作

由于索引函數(shù)可以本地處理數(shù)組，因此我們添加了另一個(gè)索引來處理1和0的數(shù)組，該數(shù)組是通過乘以兩個(gè)或多個(gè)true/false數(shù)組創(chuàng)建的。第二個(gè)索引配置為0 row_num參數(shù)，以使公式返回整個(gè)列數(shù)組而不是單個(gè)值。由于它是一個(gè)單列數(shù)組，因此我們可以安全地為Column_num提供1：

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

此數(shù)組傳遞給匹配功能：

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

匹配找到所有標(biāo)準(zhǔn)為真的行（更準(zhǔn)確地說，是指定數(shù)組中該行的相對(duì)位置），并將該編號(hào)傳遞給第一個(gè)索引的Row_num參數(shù)：

=INDEX(D2:D13, 3)

索引與行和列中的多個(gè)標(biāo)準(zhǔn)匹配

此示例顯示了如何通過在行和列中測(cè)試兩個(gè)或多個(gè)標(biāo)準(zhǔn)來執(zhí)行查找。實(shí)際上，這是一個(gè)更復(fù)雜的情況，即帶有多個(gè)標(biāo)頭行的所謂“矩陣查找”或“雙向查找”。

這是一行和列中有多個(gè)標(biāo)準(zhǔn)的通用索引匹配公式：

{= index（ table_array ，match（ vlookup_value ， lookup_column ，0），match（ hlookup_value1 ＆ hlookup_value2 ， lookup_row1 ＆lookup_row1＆ lookup_row2，0 ）}）}））}）

在哪里：

table_array-在內(nèi)部搜索的地圖或區(qū)域，即所有數(shù)據(jù)值不包括列和行標(biāo)頭。

vlookup_value-您在列中垂直尋找的值。

Lookup_column-通常要搜索的列范圍，通常是行標(biāo)頭。

hlookup_value1，hlookup_value2，… - 在行中水平尋找的值。

Lookup_row1，Lookup_row2，… - 行搜索范圍，通常是列標(biāo)題。

重要說明！為了使公式正確工作，必須將其作為帶有Ctrl Shift Enter的數(shù)組公式輸入。

它是經(jīng)典的雙向查找公式的變體，該公式在某個(gè)行和列的交點(diǎn)處搜索一個(gè)值。不同之處在于，您將幾個(gè)Hlookup值和范圍串聯(lián)以評(píng)估多個(gè)列標(biāo)題。為了更好地理解邏輯，請(qǐng)考慮以下示例。

帶有多個(gè)標(biāo)準(zhǔn)的矩陣查找 - 公式示例

在下面的示例表中，我們將根據(jù)行標(biāo)頭（項(xiàng)目）和2列標(biāo)題（區(qū)域和供應(yīng)商）搜索一個(gè)值。為了使公式易于構(gòu)建，讓我們首先定義所有標(biāo)準(zhǔn)和范圍：

• table_array -b3：e4
• vlookup_value （目標(biāo)項(xiàng)目）-H1
• Lookup_column （行標(biāo)頭：項(xiàng)目） - A3：A4
• hlookup_value1 （目標(biāo)區(qū)域）-H2
• hlookup_value2 （目標(biāo)供應(yīng)商）-H3
• Lookup_row1 （列標(biāo)題1：區(qū)域）-B1：E1
• Lookup_row2 （列標(biāo)題2：供應(yīng)商）-B2：E2

現(xiàn)在，將論點(diǎn)提供到上面解釋的通用公式中，您將獲得此結(jié)果：

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

請(qǐng)記住，通過按CTRL Shift輸入快捷方式來完成該公式，并且您的矩陣查找具有多個(gè)標(biāo)準(zhǔn)：將成功完成：

該公式如何工作

當(dāng)我們垂直和水平搜索時(shí)，我們需要為索引（array，row_num，column_num）函數(shù)提供行和列號(hào)。

ROW_NUM通過Match（H1，A3：A5，0）提供，將H1中的目標(biāo)項(xiàng)目（蘋果）與A3：A5中的行標(biāo)頭進(jìn)行比較。這給出了1的結(jié)果，因?yàn)椤疤O果”是指定范圍中的第一項(xiàng)。

column_num是通過連接2個(gè)查找值和2個(gè)查找數(shù)組來制定的：匹配（H2＆H3，B1：E1＆B2：E2，0））

成功的關(guān)鍵因素是查找值應(yīng)與列標(biāo)題完全匹配，并以相同的順序連接。要可視化這一點(diǎn)，請(qǐng)?jiān)谄ヅ涔街羞x擇前兩個(gè)參數(shù)，請(qǐng)按F9 ，您將看到每個(gè)參數(shù)評(píng)估的內(nèi)容：

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

由于“ Northvendor 2”是陣列中的第二個(gè)元素，因此函數(shù)返回2。

在這一點(diǎn)上，我們?nèi)唛L(zhǎng)的二維索引匹配公式將其轉(zhuǎn)換為這個(gè)簡(jiǎn)單的索引：

=INDEX(B3:E5, 1, 2)

并在b3：e5范圍內(nèi)的第一行和第二列的相交處返回一個(gè)值，該值是單元格C3中的值。

這就是如何查找Excel中的多個(gè)標(biāo)準(zhǔn)。我感謝您閱讀，并希望下周在我們的博客上見到您！

練習(xí)工作簿下載

Excel索引匹配多個(gè)標(biāo)準(zhǔn)（.xlsx文件）

以上是Excel索引與多個(gè)標(biāo)準(zhǔn)匹配 - 公式示例的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！