如何根據條件在 Excel 中對唯一值求和?
在處理 Excel 數據集時(例如訂單日誌、財務記錄或調查結果),您可能經常需要根據另一列中的篩選條件或標準,計算某一列中唯一值的總和。例如,考慮一個包含兩列的數據表:姓名和訂單。如果您想計算每個姓名所對應的唯一訂單值總和(忽略重複值),該如何在 Excel 中高效實現呢?這在許多商業或數據分析場景中是一項常見的需求,因為簡單地將所有匹配項目相加可能會由於重複值而導致結果膨脹。
下面的範例截圖展示了一個典型場景:給定一個包含姓名及其對應訂單值的列表(包括重複值),您希望通過分別對每個姓名的唯一訂單值求和來匯總數據。
在此任務中常見的挑戰包括根據特定條件識別唯一條目,確保僅計數首次出現的值,並防止因手動複製和粘貼篩選數據而可能引發的錯誤。幾種實用的 Excel 方法,包括陣列公式、Kutools 和 Power Query,可以幫助您解決這個問題,每種方法都適用於不同的使用場景。
<h4"> 根據一或多個條件對唯一值求和的陣列公式
一種有效且靈活的方法是使用陣列公式,它允許您匯總符合特定條件的唯一值。當您希望隨著數據或條件的變化自動更新計算時,這種方法特別有用。
要根據另一列中的篩選條件或條件僅對某一列中的唯一值求和,您可以應用以下公式:
1. 在空白單元格(例如 E2)中輸入此公式:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))
在確認公式之前,請仔細檢查:
- A2:A12: 包含條件的區域(在本例中為姓名)。
- D2: 目標條件(例如某個特定姓名)所在的單元格。
- B2:B12: 您希望唯一求和的值範圍。
您可以根據實際數據結構的需要調整這些範圍。確保所有範圍的長度相等,以避免公式錯誤。
2. 輸入完畢後,同時按下 Ctrl + Shift + Enter 以激活此陣列公式。公式周圍會出現大括號,表示這是一個陣列公式。然後,拖動填充柄向下複製公式,以便為您的摘要列中的每個對應值自動獲得正確的唯一值求和。
實用提示:如果您使用的是 Excel 365 或 Excel 2021,新的動態陣列函數如 UNIQUE 和 SUMIFS 可能會進一步簡化部分這些計算,但上述公式在多個 Excel 版本中都能可靠運行。
=SUMIF(A2:B12, UNIQUE(D2), B2:B12)
處理更多條件: =SUM(SUMIFS(求和範圍, 條件範圍1, UNIQUE(條件範圍1), [條件範圍2, 條件2], ...)
注意事項:
- 如果您使用的是 Excel 2019 或更早版本,請確保使用陣列公式輸入(Ctrl + Shift + Enter)。對於 Excel 365/2021,動態公式可能只需按 Enter 即可。
- 如果您的數據範圍特別大,陣列方法可能會變慢,因此請考慮先篩選數據或針對非常大的數據集使用其他方法。
- 仔細檢查是否有額外的空格或數據類型一致性——不一致的文字或數字格式可能會導致不匹配的錯誤。
提示:如果您需要根據兩個條件對所有唯一值求和,可以使用以下擴展的陣列公式:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))
該公式原理相似,但支持來自 B 列的額外篩選條件(現在與 F2 作為次要條件進行比較),並對 C 列中的唯一值求和。將此公式輸入到選定的摘要單元格後,使用 Ctrl + Shift + Enter 確認,然後根據需要應用到其他摘要行。
總結建議:雖然陣列公式在大多數場景中提供準確的結果,但始終要雙重檢查隱藏的重複值(例如那些帶有多餘空格或文字格式差異的值),並確保您的摘要區域從準確篩選的列表中提取數據。

使用 Kutools AI 解鎖 Excel 的魔法
- 智能執行:執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
- 自訂公式:生成量身定制的公式,簡化您的工作流程。
- VBA 編碼:輕鬆編寫和實現 VBA 代碼。
- 公式解釋:輕鬆理解複雜的公式。
- 文本翻譯:打破電子表格中的語言障礙。
使用 Kutools for Excel 的高級合併行功能根據條件對唯一值求和
Kutools for Excel 的高級合併行功能讓基於特定條件僅對唯一值求和變得輕鬆無比!只需點擊幾下,它就能智能地分組數據並應用自定義匯總統計邏輯——無需公式,無需麻煩,只有準確的結果。
步驟 1:選擇您的數據表
突出顯示整個表格,包括標題。
步驟 2:進入 Kutools > 內容 > 高級合併行。
步驟 3:設置分組列
在彈出的對話框中,選擇您要分組的列(例如水果),在操作部分將其設為主鍵。
步驟 4:設置唯一值求和欄位
選擇銷售列,在操作部分設置您希望的計算方式(例如求和)。
提示:您可以在對話框中預覽合併結果。
步驟 5:點擊確定。表格現在按客戶分組,每個客戶都有唯一的產品金額總和。
??Kutools for Excel 提供 300 多個強大的功能來提升您的生產力——而且您可以免費試用 30 天!
其他內建的 Excel 方法:使用樞紐分析表進行唯一值求和分析
Excel 的樞紐分析表功能提供了另一種強大的內建方法來根據條件匯總數據。儘管樞紐分析表預設情況下不能直接對唯一值求和,但在 Excel 2013 之後的版本中,它們支持 Distinct Count 計算,這有助於您分析指定字段的唯一條目數量。雖然這不能直接計算唯一值的總和,但您可以將 Distinct Count 與手動調整或計算字段結合使用,以促進類似的匯總。
優勢:樞紐分析表不需要記憶公式或編寫 VBA 代碼,並且它們提供了高度靈活的拖放界面。適合定期報告、群組分析、快速概覽或跨團隊協作時使用。然而,它們最適合用於匯總和分析,而不是用於構建進一步計算或自動化的公式。
以下是使用樞紐分析表進行唯一值求和分析的方法:
- 選擇您的數據範圍(例如 A1:B12 包括標題),然後進入插入 > 樞紐分析表。在對話框中,選擇是否將樞紐分析表放置在新工作表或現有工作表中。
- 在樞紐分析表字段列表中,將姓名拖到行區域,並將訂單拖到值區域。
- 對於值區域中的訂單條目,點擊下拉箭頭 > 值字段設置 > 設置為求和(顯示包括重複值在內的總訂單求和)。
限制:
- Distinct Count 功能僅在 Excel 2013 或更高版本中可用;較早版本需要更多的手動操作。
總之,雖然樞紐分析表在互動式匯總和分析方面表現出色,但如果需要真正的唯一值求和計算,請考慮將其與基於公式或 VBA 的方法搭配使用。
更多相關文章:
- 根據單一條件在 Excel 中對多列求和
- 在 Excel 中,您可能經常需要根據一個條件對多列求和。例如,我有一組如下截圖所示的數據,現在我想獲取三個月(1月、2月和3月)中 KTE 的總值。
- 在 Excel 中查找並對行或列中的匹配項求和
- 使用 vlookup 和 sum 函數可以幫助您快速找到指定的條件並同時對相應的值求和。在本文中,我們將向您介紹兩種方法來 vlookup 並對行或列中的第一個或所有匹配值求和。
- 根據月份和年份在 Excel 中求和
- 如果您有一組數據,A 列包含一些日期,B 列包含訂單數量,現在您需要根據另一列中的月份和年份來求和。在這種情況下,我想計算 2016 年 1 月的總訂單數量以得到以下結果。本文將討論一些技巧來解決此問題。
- 根據文本條件在 Excel 中求和
- 在 Excel 中,您是否嘗試過根據另一列中的文本條件求和?例如,我在工作表中有一組數據如下截圖所示,現在我希望將 B 列中與 A 列中的文本值相符的數字相加,例如,如果 A 列中的單元格包含 KTE,則求和這些數字。
- 根據下拉列表的選擇在 Excel 中求和
- 如下截圖所示,您有一個包含類別列和金額列的表格,並且您已創建了一個包含所有類別的數據驗證下拉列表。當您從下拉列表中選擇任何類別時,您希望對 B 列中所有對應的單元格值求和並在指定單元格中填入結果。例如,當從下拉列表中選擇類別 CC 時,您需要對 B5 和 B8 的值求和並得到總數 40+70=110。如何實現這一目標?本文中的方法可以幫助您。
最佳 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% 的工作效率,每天為你大量減少滑鼠點擊次數!