很多人都會使用sumproduct函數。
在多行多列數據表格中,先将多列同一行的數據相乘,再把多行的相乘的結果相加求和。
比如下面的這個表:
如果使用SUMproduct函數,就是:
sumproduct(B2:B5,C2:C6),該函數得到的結果就是100
這個的應用案例很多,比如B列是數量,C列是單價,要求快速彙總出總金額。
這樣的話,就用這個公式很快就能得出來了。
如果用傳統的每一行先乘,再把多行的乘積相加,就比較繁瑣了。
但是很多人對于sumproduct函數僅限于這種用法了。一不小心就會踩雷區了。
二、知乎的案例昨天在知乎上看到一個問題:
上面兩個圖片就是一個完整的提問。
這裡面的提問者就踩了雷區了。
三、我的回答:看了他的描述,實際他的需求就是:
滿足表B的某個條件下,對表A進行求和
表B就是數組:A7:C9,利用公式計算的話,就可以看到,它是一個數組:
數組B={1,1,1;2,2,2;3,3,3}這個數組有9個元素
表A就是數組:A2:C4,利用公式計算的話,就可以看到,它是一個數組:
數組A={1,2,3;4,5,6;7,8,9}這個數組有9個元素
現在就是從數組B中滿足條件:等于2的情況下,對應數組A的同樣的位置進行求和
下面是我做的一個SUMPRODUCT公式:
=SUMPRODUCT((區域1=滿足條件1)*(區域2=滿足條件2)*(…………)*(區域一)*(區域二)*(……))
條件1、條件2、條件3……可以是一個條件也可以是2個條件也可以是三個以上條件
區域一、區域二……可以是一個區域也可以是多個區域,一個區域就是直接求和,多個區域就是先對應的相乘,然後再求和。
根據我的公式,他這個題目就是用下面的公式:
=SUMPRODUCT((A7:C9=G13)*(A2:C4))
我在G13這個單元格,輸入1或2或3,輸入不同的數字,剛才的公式會有不同的結果:
下面放上結果:
上圖,我把A7:C9以及A2:C4做了絕對引用。G13、G14、G15分别輸入1、2、3,H13、H14、H15出現三個結果。
四、與提問者的進一步探讨“我原來的做法為什麼錯我還是有點疑問”對此我回複了提問者:
“你的公式是使用錯誤,不是你這種用法。
1.如果要使用條件,就得用乘法,乘法前後都是用括号作為一個整體。
2.如果你用逗号分隔為兩個參數 那麼逗号兩邊的數組必須是一樣多,且必須為數值型的。你在這裡加了條件後就是布爾運算了,變成非數值型了,所以另一個答主加了兩個- 通過兩個短橫線把非數值型變成數值型。”
然後我再次把提問者的公式輸入到WPS表格裡,利用公式計算,跟蹤了下過程,得到:
得到下面的步驟:
第一步得到:
第二步得到:
第三步得到:
第四步得到:
這裡就是雷區了(存在非數值型的數據)
第五步得到:
關鍵點就是在第四步,這個時候做了布爾運算,結果不是數值,是真和假,真假是無法用于逗号分割的sumproduct函數的。因為要用兩個參數的話,兩個參數必須都是數組,且元素個數一樣多,且都是數值型。
要想改變為數值型,就在前面加上--,或者加上1*
結果如下:
或者:
1*任何數(布爾型)還是等于任何數(變成了數值型)
--得正,因為做了加減乘除運算後,變為數值型了。
,
更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!