我最喜歡在Excel表中使用數據的方法
- Barbara Streisand原創
- 2025-02-06 16:54:13815瀏覽
Excel 數據提取和匯總的三個實用函數
假設您擁有一個大型工作簿，其中包含格式精美、已篩选和排序的表格。您可能認為工作已經完成，但實際上，Excel 正在等待您對這些表格進行更多操作，渴望幫助您充分利用您到目前為止所做的辛勤工作。
本文將介紹三個我經常使用的函數或函數組合，它們可以用來提取或匯總 Excel 表格中的信息。
VLOOKUP 和 HLOOKUP
VLOOKUP 和 HLOOKUP 都用於查找和檢索表格中特定位置的值。
- VLOOKUP 依賴於垂直數據表，並查找表中的第一列（垂直）。
- HLOOKUP 依賴於水平數據表，並查找表中的第一行（水平）。
VLOOKUP
這裡，我有一個考試成績列表和每個等級所需的成績（我們稱之為表 1）。我還有一張包含學生成績的表（從這一點起稱為表 2）。我希望 Excel 使用表 1 中的信息來完成表 2 中缺少的列。
我將使用 VLOOKUP，因為我希望 Excel 查找表 1 的第一列中的值以返回表 2 中每個學生的成績。 VLOOKUP 函數的語法如下：
<code>=VLOOKUP(a,b,c,d)</code>
其中：
- a 是要查找的值（在上例中，那是 E 列中的值），
- b 是包含參考值的表格（在本例中，它是單元格 A1 到 B9，或表 1），
- c 是該表中的列號（我希望它返回等級，所以它是表 1 的第二列），以及
- d 是一個可選條件，它告訴 Excel 近似查找值（“TRUE”）還是精確查找值（“FALSE”）。如果留空，則默認值為 TRUE。
因此，在我的例子中，我將在單元格 F2 中輸入此公式來計算 Tom 的成績，然後使用自動填充來查找表中其他成績：
<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>
我使用了 $ 符號來為上面的值 b 創建一個絕對引用，因為我希望 Excel 持續使用單元格 A1 到 B9 來查找值。我還為值 d 使用了“TRUE”，因為分數邊界表包含範圍，而不是分配給單個分數的等級。
HLOOKUP
這裡，我們有相同的等級邊界信息，但這次是水平顯示的。這意味著我們想要獲取的數據位於邊界表的第二行。
HLOOKUP 函數的語法與 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 查找並返回值的位。它們一起可以實現動態數據檢索。
單獨的語法
在我們一起查看這些函數之前，讓我們簡要地單獨查看它們。
INDEX 的語法是
<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>
其中 a 是包含數據的單元格範圍，b 是要評估的行號，c 是要評估的列號。
以此為基礎，
<code>=HLOOKUP(a,b,c,d)</code>
將評估單元格 B2 到 D8，並返回該範圍內的第四行和第二列中的值。
對於 MATCH，我們遵循
<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>
其中 x 是我們要查找的值，y 是要查找值的範圍，z（可選）是匹配類型。
以此為基礎，
<code>INDEX(a,b,c)</code>
將告訴我數字 5 在範圍 B2 到 B8 中的位置，而 0 告訴 Excel 執行精確匹配。
結合使用
在此示例中，我希望 Excel 告訴我指定球員在一個給定月份進球的數量。更具體地說，我想知道球員 C 在第三個月進了多少個球，但我將創建此公式，以便我可以隨時更改這些條件。
為此，我需要 Excel 確定球員 C 在表格中的位置，然後告訴我數據第三列中的值。
在單元格 G4 中，我將從 INDEX 函數開始，因為我希望 Excel 從我的原始數據中查找並返回一個值。然後，我將告訴 Excel 在哪裡查找該數據。
<code>INDEX(B2:D8,4,2)</code>
INDEX 語法的下一部分是行號，這將根據我在單元格 G2 中聲明的球員而有所不同。例如，如果我想查找球員 A，它將是第一行。為此，我將啟動 MATCH 函數，因為我希望 Excel 將我鍵入單元格 G2 中的球員與球員列 (A2:A8) 中的相應單元格匹配，併計算出它位於哪一行。我還添加了最後的 0，因為我希望 Excel 返回精確檢索。
<code>=VLOOKUP(a,b,c,d)</code>
現在我已經告訴 Excel INDEX 函數的行號，我需要用列號來完成。在我的例子中，列號代表我鍵入單元格 G3 中的月份號。
<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>
當我按下 Enter 鍵時，Excel 正確地告訴我球員 C 在第三個月進了五個球。
現在，我可以更改我的查找表中的任何值以查找任何球員在任何月份的總計。
COUNTIF 和 SUMIF
從它們的名稱中可以看出，這兩個函數根據您設置的條件來計數和求和值。任何未包含在您條件中的內容都不會被添加或計數，即使它在您指定的範圍內也是如此。
COUNTIF
COUNTIF 計數包含特定條件的單元格。語法是
<code>=HLOOKUP(a,b,c,d)</code>
其中 a 是您要計數的範圍，b 是計數的條件。
同樣，如果我想包含多個條件，我將使用 COUNTIFS：
<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>
其中a 和b 是第一個範圍條件配對，c 和d 是第二個範圍條件配對（您可以最多有127 個配對）。
如果任何條件是文本或邏輯或數學符號，則必須將其括在雙引號中。
在我的下面的工資表中，我想計算收入超過 40,000 英鎊的人數，以及單獨計算獲得超過 1,000 英鎊獎金的服務人員人數。
要計算工資超過 40,000 英鎊的員工人數，我需要在單元格 D8 中輸入此公式：
<code>INDEX(a,b,c)</code>
其中 C2:C6 是工資所在的範圍，">40000" 是條件。
要計算獲得超過 1,000 英鎊獎金的服務人員人數，我將使用 COUNTIFS，因為我有兩個條件。
<code>INDEX(B2:D8,4,2)</code>
B2:B6,"Services" 部分是第一個範圍條件配對，D2:D6,">1000" 是第二個。
即使我的上表中用逗號分隔千位數，我也沒有在公式中包含這些逗號，因為逗號在此處具有不同的功能。
SUMIF
SUMIF 根據您設置的條件對單元格求和。它的工作原理與 COUNTIF 類似，但在括號中包含更多參數。語法是
<code>MATCH(x,y,z)</code>
其中a 是您要在求和之前評估的單元格範圍，b 是該評估的條件（這可以是值或單元格引用）， c（可選）是如果與a 不同則要添加的單元格。
這次，我們要計算三件事：超過 40,000 英鎊的工資總額、服務部門的總工資以及工資超過 35,000 英鎊的員工的獎金總額。
首先，要計算超過 40,000 英鎊的工資總額，我需要在單元格 D8 中輸入以下公式：
<code>=VLOOKUP(a,b,c,d)</code>
其中 C2:C6 引用表中的工資，">40000" 告訴 Excel 只對超過此金額的值求和。
接下來，我想找出服務部門的總工資。因此，在單元格 C9 中，我將輸入
<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>
其中B2:B6 引用部門列，"Services" 告訴Excel 我正在特別尋找服務部門的員工，C2:C6 告訴Excel 對這些員工的工資求和。
我的最後一個任務是找出收入超過 35,000 英鎊的員工獲得了多少獎金。在單元格 C10 中，我將輸入
<code>=HLOOKUP(a,b,c,d)</code>
其中C2:C6 告訴Excel 評估工資，">35000" 是這些工資的條件，D2:D6 告訴Excel 對滿足條件的個人的獎金求和。
Excel 還具有 SUMIFS 函數，它執行相同的過程，但針對多個條件。它與 SUMIF 的語法大相徑庭：
<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>
其中a 是要相加的單元格範圍，b 是要評估的第一個範圍，c 是b 的條件，d 和e 是下一個範圍條件配對（您可以最多有127 對）。
使用上表，假設我想對收入超過 45,000 英鎊的員工的獎金求和。這是我將輸入的公式：
<code>INDEX(a,b,c)</code>
掌握了上述函數後，請嘗試使用 XLOOKUP 函數，該函數旨在通過查找查找值列左側和右側的值來解決 VLOOKUP 的一些缺點，而無需重新排列數據。
以上是我最喜歡在Excel表中使用數據的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！