Excel 三種特殊情況下的求和公式：SUBTOTAL、LOOKUP 以及 COUNTIF

本文作者：小花

本文編輯：竺蘭

Excel 中有很多可用于條件求和的函數，如 SUMIF、SUMIFS、SUMPRODUCT。

但在一些特殊的數據表中進行條件求和，我們或許還需要其他函數的幫助。

今天，小花就分享三種特殊情況下的求和公式。

1、篩選后求和

審計員小 K 發現某下屬公司部分報銷費用可能存在風險，于是逐筆初篩出來。

現需設置一個公式，按部門統計發生額。隨后，小 K 將進行二次篩選，當風險項被排除時，也能夠自動剔除該金額。

這一問題的難點在于，如何確定每一條記錄是否被篩選出來？雖然肉眼很容易分清，但如何讓計算機明白卻不那么容易。

好在，Excel 中有一個專門的篩選統計函數 ——SUBTOTAL 函數。

借助 SUBTOTAL 剔除隱藏單元格后進行統計的功能，我們可以設置如下公式完成篩選情況下的條件求和。

篩選下的條件求和公式：

=SUMPRODUCT(($C$8:$C$41)*SUBTOTAL(3,OFFSET($C$7,ROW($C$8:$C$41)-7,0))*($A$8:$A$41=A2))

公式說明 ??

? OFFSET($C$7,ROW($C$8:$C$41)-7)

ROW ($C$8:$C$41)-7 函數返回一組 1-34 的有序數組，OFFSET 函數根據該數組，逐一偏移返回 C8:C41 的每一個單元格。這與直接引用 C8:C41 是不同的，前者將每個單元格都視為一個單獨的區域，而后者則將 C8:C41 視為一個整體。

? SUBTOTAL(3,①)

3 是計數功能代碼，此處 SUBTOTAL 識別 C8:C41 的每個單一單元格區域是否隱藏，如果該單元格隱藏，則統計結果為 0，否則為 1。即，②的結果為 1 則表示該單元格在篩選結果中。

? SUMPRODUCT(($C$8:$C$41)*②*($A$8:$A$41=A2))

SUMPRODUCT 函數的經典用法，C8:C41 是求和區域，②和 ($A$8:$A$41=A2) 是條件區域，求和區域和條件區域一一對應相乘再求和，即為條件求和結果。

2、合并單元格求和

合并單元格對報表的視覺呈有其「獨到」之處，但其痛點也很「毒辣」—— 無法直接進行條件統計。

比如，在存在合并單元格的情況下，直接進行條件求和，計算結果多半都是錯誤的。

這是因為，合并單元格過程中，除首個單元格外，其余單元格的內容都會被清除。

要想能夠在這種情況下實現條件求和，我們需要用 LOOKUP 函數來「復原」空白值。

合并單元格下的條件求和公式：

{=SUM(($C$8$C$20)*(LOOKUP(ROW($A$8$A$20)IF($A$8$A$20<>"ROW($A$8$A$20)"")$A$8$A$20)=A2))}

公式說明 ??

? IF($A$8:$A$20<>"",ROW($A$8:$A$20),"")

對 A8:A20 進行判斷，如不為空白，返回行號，否則返回空白。也就是說，每一個合并單元格首行都返回其行號。

? LOOKUP(ROW($A$8:$A$20),①,$A$8:$A$20)

如果 A8:A20 中的某個單元格為所在合并單元格的首行，則其行號會在①中出現，LOOKUP 返回其本身；

如果 A8:A20 中的某個單元格不是所在合并單元格的首行，則它在①中與空白對應，查詢其行號時，LOOKUP 匹配到小于且接近去其行號的最大值，即其所在合并單元格首行的行號，最終返回所在合并單元格首個單元格的值。由此，所有因合并而被清除的單元格值都得到復原。

? {=SUM(($C$8:$C$20)*(②=A2))}

利用 SUM 的數組運算，返回乘積和，和 SUMPRODUCT 條件求和原理類似，此處必須使用 SUM 函數，按【Ctrl+Shift+Enter】執行數組運算。

3、去除重復值求和

條件求和的另外一種特殊情況是，求和區域存在部分重復值，需要去重后再求和才能得到準確結果。

比如下圖中，部分人員有重復的，無法直接進行條件求和。

這時候，我們需要使用 COUNTIF 函數來「稀釋」重復值，再求和。

去重后的條件求和公式：

=SUMPRODUCT(($A$7$A$18=$A2)*($C$7$C$18)/COUNTIF($B$7$B$18$B$7$B$18))

公式說明 ??

COUNTIF ($B$7:$B$18,$B$7:$B$18) 統計每個姓名出現的次數 n，將其每一行工資對應「稀釋」為其本身的 1 / n，再進行條件求和，每個人的 1 / n 工資都被計算了 n 次，這就實現了去重后的條件求和。

以上，就是小花分享的三種特殊情況下的條件去和公式，包括：

? 利用 SUBTOTAL 識別篩選再完成條件求和；

? 利用 LOOKUP 復原合并單元格缺失值完成包含合并單元格的條件求和；

? 利用 COUNTIF 稀釋原值完成去重后的條件求和。

你學會了嗎？

隨著 Excel 版本不斷更新，也涌現了 SCAN、LAMBDA、UNIQUE、FILTER 等函數，能夠簡化上述公式或者公式中的某個片段，有條件的小伙伴不妨一試。

學無止盡，與君共勉！

本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：小花