如何在 Excel 中對多個 vlookup 查找結果求平均值?
在許多實際情況中,查找值可能會在表格中多次出現,並且每次出現都可能有一個與之相關的值,您希望將其包含在計算中。如果您需要計算與特定查找值匹配的所有值的平均值——本質上是對多個 vlookup 查找結果求平均值——Excel 提供了幾種方法來高效地完成此操作。通過對所有符合查找值的目標值求平均值,您可以為銷售分析、質量控制或調查結果匯總等任務獲得更深入的見解。在這篇綜合文章中,您會找到從基於公式的解決方案到高級工具的一系列解決方案的明確說明,以及它們的使用場景、優點和限制。
- 使用公式對多個 vlookup 查找結果求平均值
- 使用篩選功能對多個 vlookup 查找結果求平均值
- 使用 Kutools for Excel 對多個 vlookup 查找結果求平均值
- 使用樞紐分析表對多個 vlookup 查找結果求平均值
- 使用 VBA 宏對多個 vlookup 查找結果求平均值
使用公式對多個 vlookup 查找結果求平均值
當您需要查找並對與同一查找項目相關的多個值求平均值時,使用直接公式是最快且最靈活的方法之一。AVERAGEIF 或數組公式可以輕鬆處理此問題,而無需創建額外的列。
在空白單元格(例如 F2)中輸入以下公式:
=AVERAGEIF(A1:A24,E2,C1:C24)
按下 Enter 鍵後輸入公式。這會立即給出 C 列中所有與 A 列中對應值匹配的查找值位於 E2 單元格中的平均值。請參見下圖:
參數解釋和提示:
- A1:A24:包含您的查找值的區域。
- E2:您要查找的特定值。
- C1:C24:您希望對匹配值求平均值的區域。
替代方法(適用于熟悉數組公式的用戶):
在空白單元格中輸入以下公式並使用 Ctrl + Shift + Enter 確認:
=AVERAGE(IF(A1:A24=E2,C1:C24))
數組公式分別處理每個比較,這在不支持動態數組的 Excel 版本中很有幫助。請仔細確保範圍大小完全相同以避免錯誤。
實際應用場景和注意事項:
- 適合未過濾且具有直觀查找需求的數據集。
- 如果任一範圍包含空單元格,則這些單元格在平均值計算中將被忽略。
- 在動態表格或添加數據時,考慮使用表格引用以獲得更穩健的公式。
- 注意不要意外錯配單元格範圍,這是導致平均值錯誤或計算錯誤的常見原因。
使用篩選功能對多個 vlookup 查找結果求平均值

Excel 中的篩選功能允許您臨時隱藏不符合特定條件的行,使您更容易專注於所需的結果。此技術使您可以隔離所有與查找值匹配的記錄,然後快速計算可見條目的平均值。
1. 選擇數據的標題行,然後導航至 數據 > 篩選./p>
2. 在包含查找值的列中,單擊篩選下拉箭頭並僅選擇您要檢查的項目。單擊確定以應用篩選。表格將只顯示與查找值匹配的條目。請參見左側的截圖:
3. 在空白單元格(如數據下方)中輸入以下公式:
=AVERAGEVISIBLE(C2:C22)
按下 Enter 以計算 C 列中所有當前可見(已篩選)單元格的平均值。這確保只有篩選後顯示的值包含在結果中。
優勢和場景:這種方法非常適合您想手動檢查或交互處理數據的情況,並且您的數據已經排列在帶有標題的表格中。在處理複雜篩選器或條件格式時特別有效。
局限性:如果您修改或移除篩選器,公式將調整為任何可見數據,並且您需要 Kutools for Excel 的 AVERAGEVISIBLE
函數(標準 Excel 不具備此函數)。此外,請確保沒有與篩選無關的隱藏行存在,因為這些行也將被排除。
示例:使用篩選功能對多個 vlookup 查找結果求平均值
使用 Kutools for Excel 對多個 vlookup 查找結果求平均值
如果您經常需要根據重複項彙總和聚合數據,Kutools for Excel 提供了一個實用的解決方案,即其 "Advanced Combine Rows" 工具。該工具可以快速將匹配記錄的值進行合併或計算(如平均值、總和或計數),一步到位,非常適合大型數據集或定期報告。
1. 高亮顯示數據表的範圍,包括查找列和要平均的值列。然後轉到 Kutools > 內容 > Advanced Combine Rows。請參見截圖:
2. 在彈出的對話框中:
- 選擇包含查找值的列,然後單擊主鍵。
- 選擇包含目標值的列,然後單擊計算 > 平均值。
- 根據需要設置其他列的組合或計算規則——例如用逗號合併文本或應用總和、最大值或最小值。
3. 點擊確定以應用設置。
現在,具有重複查找值的行已合併,並且指定列中的值會自動針對每個唯一的查找值進行平均。這對於準備摘要報告或壓縮數據特別有用。
實用技巧:使用 Advanced Combine Rows 可最大程度減少手動計算和出錯的可能性。該工具最適合那些經常處理具有重複查找值的數據並希望快速獲取可行摘要的用戶。始終仔細檢查是否正確分配了列,尤其是在數據結構發生變化時。
Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取
演示:使用 Kutools for Excel 對多個 vlookup 查找結果求平均值
使用樞紐分析表對多個 vlookup 查找結果求平均值
樞紐分析表提供了一種動態且視覺化的方法來彙總和分析數據。使用樞紐分析表,您可以自動按查找值分組條目,並顯示每組目標列的平均值,從而提供一個隨著數據變化而更新的互動式摘要。
最有效的場景:這種方法非常適合一次性對所有查找值進行總體摘要的情況,而不是專注於單一查找值。樞紐分析表也非常適合快速數據探索、報告生成,以及當您希望以可排序、可展開的格式呈現結果時。
指示:
- 選擇包含標題的整個數據集。
- 轉到 插入 > 樞紐分析表 > 從表格或範圍。根據需要選擇將樞紐分析表放置在新工作表或現有工作表上。
- 在樞紐分析表字段面板中,將包含查找值的列拖到行區域。
- 將要平均的列拖到值區域。點擊值字段,選擇值字段設置,然後將計算類型設置為平均值。
這將生成一個摘要表,列出每個唯一查找值及其相關數據的平均值。您可以根據需要輕鬆更改分組、篩選或深入查看詳細信息。
優點:不需要公式,支持動態更新,適合報告和數據探索。
缺點:數據更改後需要額外步驟刷新,不太適合直接提取單個值到其他公式,初始設置需要基本了解樞紐分析表。
故障排查提示:如果值顯示為計數或總和而不是平均值,請檢查字段計算設置。為獲得最佳效果,請確保列具有適當的標題,並在創建樞紐分析表之前澄清任何重複的列名稱。
使用 VBA 宏對多個 vlookup 查找結果求平均值
對於進階用戶和管理定期更新數據的人來說,使用 VBA 宏可以自動化匹配查找值的所有條目進行平均值計算的過程。此方法遍歷數據以找到每個匹配項並計算平均值,非常適合大型數據集或需要可重複工作流程的情況。
適用場景和注意事項:VBA 非常適合您經常需要進行平均值計算、自動化報告或需要靈活方法來處理異常數據佈局時使用。VBA 宏在您能夠舒適啟用工作簿宏並需要自定義輸出時效果最佳。
1. 轉到開發者選項卡,選擇 Visual Basic 或按 Alt + F11 鍵打開 VBA 編輯器,然後單擊 插入 > 模塊。將下面的代碼複製並粘貼到新模塊中:
Sub AverageVlookupMatches()
Dim lookupCol As Range
Dim avgCol As Range
Dim lookupValue As Variant
Dim total As Double
Dim count As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
Application.ScreenUpdating = False
total = 0
count = 0
For i = 1 To lookupCol.Rows.Count
If lookupCol.Cells(i, 1).Value = lookupValue Then
If IsNumeric(avgCol.Cells(i, 1).Value) Then
total = total + avgCol.Cells(i, 1).Value
count = count + 1
End If
End If
Next i
If count > 0 Then
MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
Else
MsgBox "No matches found.", vbExclamation, "Result"
End If
Application.ScreenUpdating = True
End Sub
2. 貼上代碼後,關閉 VBA 編輯器。返回 Excel 並按下 F5 鍵或點擊 運行 以執行宏。系統提示時,選擇查找列、要平均的值列,並輸入查找值。宏將在消息框中顯示計算出的平均值。
實用技巧和預防措施:確保您的查找列和值列具有相同的行數,並且所選區域內沒有空白行。目標列中非數字值的條目將被忽略。為了實現最佳自動化,請根據工作表佈局調整命名範圍或宏邏輯。
故障排除:如果您遇到「未找到匹配項」,請檢查查找列中的首尾空格或數據類型不一致。確保宏已啟用執行。
相關文章:
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!