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

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

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

VLOOKUP 和 HLOOKUP

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

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

VLOOKUP

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

我將使用 VLOOKUP，因為我希望 Excel 查找表 1 的第一列中的值以返回表 2 中每個學(xué)生的成績。 VLOOKUP 函數(shù)的語法如下：

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

其中：

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

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

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

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

HLOOKUP

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

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

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

其中：

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

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

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

INDEX 與 MATCH

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

單獨的語法

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

INDEX 的語法是

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

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

以此為基礎(chǔ)，

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

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

對於 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 告訴我指定球員在一個給定月份進(jìn)球的數(shù)量。更具體地說，我想知道球員 C 在第三個月進(jìn)了多少個球，但我將創(chuàng)建此公式，以便我可以隨時更改這些條件。

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

在單元格 G4 中，我將從 INDEX 函數(shù)開始，因為我希望 Excel 從我的原始數(shù)據(jù)中查找並返回一個值。然後，我將告訴 Excel 在哪裡查找該數(shù)據(jù)。

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

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

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

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

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

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

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

COUNTIF 和 SUMIF

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

COUNTIF

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

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

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

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

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

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

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

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

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

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

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

要計算獲得超過 1,000 英鎊獎金的服務(wù)人員人數(shù)，我將使用 COUNTIFS，因為我有兩個條件。

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

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

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

SUMIF

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

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

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

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

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

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

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

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

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

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

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

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

其中C2:C6 告訴Excel 評估工資，">35000" 是這些工資的條件，D2:D6 告訴Excel 對滿足條件的個人的獎金求和。

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

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

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

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

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

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

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