Excel 數(shù)據(jù)提取和匯總的三個(gè)實(shí)用函數(shù)

假設(shè)您擁有一個(gè)大型工作簿，其中包含格式精美、已篩選和排序的表格。您可能認(rèn)為工作已經(jīng)完成，但實(shí)際上，Excel 正在等待您對(duì)這些表格進(jìn)行更多操作，渴望幫助您充分利用您到目前為止所做的辛勤工作。

本文將介紹三個(gè)我經(jīng)常使用的函數(shù)或函數(shù)組合，它們可以用來提取或匯總 Excel 表格中的信息。

VLOOKUP 和 HLOOKUP

VLOOKUP 和 HLOOKUP 都用於查找和檢索表格中特定位置的值。

• VLOOKUP 依賴於垂直數(shù)據(jù)表，並查找表中的第一列（垂直）。
• HLOOKUP 依賴於水平數(shù)據(jù)表，並查找表中的第一行（水平）。

VLOOKUP

這裡，我有一個(gè)考試成績(jī)列表和每個(gè)等級(jí)所需的成績(jī)（我們稱之為表 1）。我還有一張包含學(xué)生成績(jī)的表（從這一點(diǎn)起稱為表 2）。我希望 Excel 使用表 1 中的信息來完成表 2 中缺少的列。

我將使用 VLOOKUP，因?yàn)槲蚁Ｍ?Excel 查找表 1 的第一列中的值以返回表 2 中每個(gè)學(xué)生的成績(jī)。 VLOOKUP 函數(shù)的語法如下：

<code>=VLOOKUP(a,b,c,d)</code>

其中：

• a 是要查找的值（在上例中，那是 E 列中的值），
• b 是包含參考值的表格（在本例中，它是單元格 A1 到 B9，或表 1），
• c 是該表中的列號(hào)（我希望它返回等級(jí)，所以它是表 1 的第二列），以及
• d 是一個(gè)可選條件，它告訴 Excel 近似查找值（“TRUE”）還是精確查找值（“FALSE”）。如果留空，則默認(rèn)值為 TRUE。

因此，在我的例子中，我將在單元格 F2 中輸入此公式來計(jì)算 Tom 的成績(jī)，然後使用自動(dòng)填充來查找表中其他成績(jī)：

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

我使用了 $ 符號(hào)來為上面的值 b 創(chuàng)建一個(gè)絕對(duì)引用，因?yàn)槲蚁Ｍ?Excel 持續(xù)使用單元格 A1 到 B9 來查找值。我還為值 d 使用了“TRUE”，因?yàn)榉謹(jǐn)?shù)邊界表包含範(fàn)圍，而不是分配給單個(gè)分?jǐn)?shù)的等級(jí)。

HLOOKUP

這裡，我們有相同的等級(jí)邊界信息，但這次是水平顯示的。這意味著我們想要獲取的數(shù)據(jù)位於邊界表的第二行。

HLOOKUP 函數(shù)的語法與 VLOOKUP 類似：

<code>=HLOOKUP(a,b,c,d)</code>

其中：

• a 是要查找的值（在此示例中，是 C 列中的值），
• b 是包含查找值的單元格的絕對(duì)引用（在本例中，它是 A1 到 I2），
• c 是該表中的行號(hào)（我希望它返回等級(jí)，所以它是第二行），以及
• d（可選）是“TRUE”（近似值）或“FALSE”（精確值）。

因此，我將在單元格 C5 中輸入此公式來計(jì)算 Tom 的成績(jī)，然後使用自動(dòng)填充來查找表中其他成績(jī)：

<code>=VLOOKUP(a,b,c,d)</code>

INDEX 與 MATCH

另一種有效查找和檢索值的方法是通過 INDEX 和 MATCH，尤其是在一起使用時(shí)。 INDEX 查找並返回已定義位置的值，而 MATCH 查找並返回值的位。它們一起可以實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)檢索。

單獨(dú)的語法

在我們一起查看這些函數(shù)之前，讓我們簡(jiǎn)要地單獨(dú)查看它們。

INDEX 的語法是

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

其中 a 是包含數(shù)據(jù)的單元格範(fàn)圍，b 是要評(píng)估的行號(hào)，c 是要評(píng)估的列號(hào)。

以此為基礎(chǔ)，

<code>=HLOOKUP(a,b,c,d)</code>

將評(píng)估單元格 B2 到 D8，並返回該範(fàn)圍內(nèi)的第四行和第二列中的值。

對(duì)於 MATCH，我們遵循

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中 x 是我們要查找的值，y 是要查找值的範(fàn)圍，z（可選）是匹配類型。

以此為基礎(chǔ)，

<code>INDEX(a,b,c)</code>

將告訴我數(shù)字 5 在範(fàn)圍 B2 到 B8 中的位置，而 0 告訴 Excel 執(zhí)行精確匹配。

結(jié)合使用

在此示例中，我希望 Excel 告訴我指定球員在一個(gè)給定月份進(jìn)球的數(shù)量。更具體地說，我想知道球員 C 在第三個(gè)月進(jìn)了多少個(gè)球，但我將創(chuàng)建此公式，以便我可以隨時(shí)更改這些條件。

為此，我需要 Excel 確定球員 C 在表格中的位置，然後告訴我數(shù)據(jù)第三列中的值。

在單元格 G4 中，我將從 INDEX 函數(shù)開始，因?yàn)槲蚁Ｍ?Excel 從我的原始數(shù)據(jù)中查找並返回一個(gè)值。然後，我將告訴 Excel 在哪裡查找該數(shù)據(jù)。

<code>INDEX(B2:D8,4,2)</code>

INDEX 語法的下一部分是行號(hào)，這將根據(jù)我在單元格 G2 中聲明的球員而有所不同。例如，如果我想查找球員 A，它將是第一行。為此，我將啟動(dòng) MATCH 函數(shù)，因?yàn)槲蚁Ｍ?Excel 將我鍵入單元格 G2 中的球員與球員列 (A2:A8) 中的相應(yīng)單元格匹配，併計(jì)算出它位於哪一行。我還添加了最後的 0，因?yàn)槲蚁Ｍ?Excel 返回精確檢索。

<code>=VLOOKUP(a,b,c,d)</code>

現(xiàn)在我已經(jīng)告訴 Excel INDEX 函數(shù)的行號(hào)，我需要用列號(hào)來完成。在我的例子中，列號(hào)代表我鍵入單元格 G3 中的月份號(hào)。

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

當(dāng)我按下 Enter 鍵時(shí)，Excel 正確地告訴我球員 C 在第三個(gè)月進(jìn)了五個(gè)球。

現(xiàn)在，我可以更改我的查找表中的任何值以查找任何球員在任何月份的總計(jì)。

COUNTIF 和 SUMIF

從它們的名稱中可以看出，這兩個(gè)函數(shù)根據(jù)您設(shè)置的條件來計(jì)數(shù)和求和值。任何未包含在您條件中的內(nèi)容都不會(huì)被添加或計(jì)數(shù)，即使它在您指定的範(fàn)圍內(nèi)也是如此。

COUNTIF

COUNTIF 計(jì)數(shù)包含特定條件的單元格。語法是

<code>=HLOOKUP(a,b,c,d)</code>

其中 a 是您要計(jì)數(shù)的範(fàn)圍，b 是計(jì)數(shù)的條件。

同樣，如果我想包含多個(gè)條件，我將使用 COUNTIFS：

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中a 和b 是第一個(gè)範(fàn)圍條件配對(duì)，c 和d 是第二個(gè)範(fàn)圍條件配對(duì)（您可以最多有127 個(gè)配對(duì)）。

如果任何條件是文本或邏輯或數(shù)學(xué)符號(hào)，則必須將其括在雙引號(hào)中。

在我的下面的工資表中，我想計(jì)算收入超過 40,000 英鎊的人數(shù)，以及單獨(dú)計(jì)算獲得超過 1,000 英鎊獎(jiǎng)金的服務(wù)人員人數(shù)。

要計(jì)算工資超過 40,000 英鎊的員工人數(shù)，我需要在單元格 D8 中輸入此公式：

<code>INDEX(a,b,c)</code>

其中 C2:C6 是工資所在的範(fàn)圍，">40000" 是條件。

要計(jì)算獲得超過 1,000 英鎊獎(jiǎng)金的服務(wù)人員人數(shù)，我將使用 COUNTIFS，因?yàn)槲矣袃蓚€(gè)條件。

<code>INDEX(B2:D8,4,2)</code>

B2:B6,"Services" 部分是第一個(gè)範(fàn)圍條件配對(duì)，D2:D6,">1000" 是第二個(gè)。

即使我的上表中用逗號(hào)分隔千位數(shù)，我也沒有在公式中包含這些逗號(hào)，因?yàn)槎禾?hào)在此處具有不同的功能。

SUMIF

SUMIF 根據(jù)您設(shè)置的條件對(duì)單元格求和。它的工作原理與 COUNTIF 類似，但在括號(hào)中包含更多參數(shù)。語法是

<code>MATCH(x,y,z)</code>

其中a 是您要在求和之前評(píng)估的單元格範(fàn)圍，b 是該評(píng)估的條件（這可以是值或單元格引用）， c（可選）是如果與a 不同則要添加的單元格。

這次，我們要計(jì)算三件事：超過 40,000 英鎊的工資總額、服務(wù)部門的總工資以及工資超過 35,000 英鎊的員工的獎(jiǎng)金總額。

首先，要計(jì)算超過 40,000 英鎊的工資總額，我需要在單元格 D8 中輸入以下公式：

<code>=VLOOKUP(a,b,c,d)</code>

其中 C2:C6 引用表中的工資，">40000" 告訴 Excel 只對(duì)超過此金額的值求和。

接下來，我想找出服務(wù)部門的總工資。因此，在單元格 C9 中，我將輸入

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

其中B2:B6 引用部門列，"Services" 告訴Excel 我正在特別尋找服務(wù)部門的員工，C2:C6 告訴Excel 對(duì)這些員工的工資求和。

我的最後一個(gè)任務(wù)是找出收入超過 35,000 英鎊的員工獲得了多少獎(jiǎng)金。在單元格 C10 中，我將輸入

<code>=HLOOKUP(a,b,c,d)</code>

其中C2:C6 告訴Excel 評(píng)估工資，">35000" 是這些工資的條件，D2:D6 告訴Excel 對(duì)滿足條件的個(gè)人的獎(jiǎng)金求和。

Excel 還具有 SUMIFS 函數(shù)，它執(zhí)行相同的過程，但針對(duì)多個(gè)條件。它與 SUMIF 的語法大相徑庭：

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

其中a 是要相加的單元格範(fàn)圍，b 是要評(píng)估的第一個(gè)範(fàn)圍，c 是b 的條件，d 和e 是下一個(gè)範(fàn)圍條件配對(duì)（您可以最多有127 對(duì)）。

使用上表，假設(shè)我想對(duì)收入超過 45,000 英鎊的員工的獎(jiǎng)金求和。這是我將輸入的公式：

<code>INDEX(a,b,c)</code>

掌握了上述函數(shù)後，請(qǐng)嘗試使用 XLOOKUP 函數(shù)，該函數(shù)旨在通過查找查找值列左側(cè)和右側(cè)的值來解決 VLOOKUP 的一些缺點(diǎn)，而無需重新排列數(shù)據(jù)。

以上是我最喜歡在Excel表中使用數(shù)據(jù)的方法的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！