當前位置:首頁 > 科技 > 正文

年少不知SUMPRODUCT好,錯把SUMIFS當成寶

與 30萬 粉絲一起學Excel

剛學完了多條件統計的2個公式,學員就用在了實際工作中, 按項目、日期統計金額,結果發現最後一個單元格不一樣。因此得出結論,第一個公式靠譜,第二個沒啥用。
=SUMIFS(C:C,A:A,E2,B:B,F2)
=SUMPRODUCT(($E2=$A$2:$A$32)*($F2=$B$2:$B$32)*$C$2:$C$32)


事實真的如此嗎?

盧子打開了表格,仔細查看了一下,發現了左邊數據源最後一個單元格帶綠帽子,也就是文本格式。這就是導緻2個公式求和結果不一樣的原因。


在求和的時候,SUMIFS是直接忽略文本,這就導緻了算少了。而SUMPRODUCT不管什麼格式,都可以求和。正确的,應該是後者。

如何才能快速确認每個金額都是數字格式?

這個表格才30多行,一下子就找到了,而實際表格可能是幾千行,用眼睛看肯定不靠譜。在隔壁列用ISNUMBER判斷,數字格式的返回TRUE,文本格式的返回FALSE。


再将FALSE篩選出來就行。


當然,也可以不用刻意去找,直接選擇C列,點分列,完成,就全部轉換成數字格式,也就是全部都是TRUE。


在所有需要輔助函數,或者不規範的情況下,都是SUMPRODUCT占了絕對優勢。這裡,盧子再舉幾個案例說明。

1.按月份統計金額

用MONTH提取月份,每個參數都可以嵌套其他函數非常方便。
=SUMPRODUCT(( MONTH($A$2:$A$32)=D2)*$B$2:$B$32)


如果有跨年的,可以直接用TEXT提取年月再統計。如果月份是數字格式,記得加--轉換格式。
=SUMPRODUCT(( --TEXT($A$2:$A$32,"emm")=D2)*$B$2:$B$32)


SUMIFS可不支持這種,需要用輔助列先提取月份才行。

2. 按賬戶計算餘額

區域采用混合引用,這裡下拉的時候就可以逐漸變大,求和區域可以用2個區域直接相減。SUMIFS可不支持這種,隻能用2個SUMIFS相減才行。
=SUMPRODUCT((C$2:C2=C2)*(D$2:D2-E$2:E2))


3.按總成号ABC隔列求和工單号123對應的數據

不管一維還是二維,都是直接套上就行。而SUMIFS就不支持二維,除非特殊情況,比如ABC順序跟原來一模一樣。
=SUMPRODUCT(($A11=$A$3:$A$5)*(B$10=$B$2:$J$2)*$B$3:$J$5)

其實SUMPRODUCT挺好的,除了引用區域的時候不能引用整列,沒啥缺點。

你可能想看:

有話要說...

取消
掃碼支持 支付碼