excel函數太難了，先學一個來壓壓驚

06-01

SUMPRODUCT函數是excel07版本後新增的一個函數，功能多樣且強大，是excel中的一顆璀璨明星，今天我們來看一下這個函數的用法。

語法：=SUMPRODUCT（array1,array2,array3, ...），Array為數組。

意思為在給定的幾組數組中，然後把數組間對應的元素相乘，最後返回乘積之和。

SUM意思是求和，PRODUCT意思是求積，組合在一起的意思是乘積之和。

函數的幾個經典用法：

1、與SUM函數用法比較

例：下圖中求銷售數量總和，SUMPRODUCT(B2:B11)=SUM(B2:B11)

求銷售總金額：=SUMPRODUCT(B2:B11*C2:C11)回車

或者=SUM(B2:B11*C2:C11)按Ctrl Shife Enter三鍵結束

SUMPRODUCT函數支持數組運算，不需要按Ctrl Shife Enter三鍵結束，它的運算原理是

當SUMPRODUCT函數的參數為兩個數組時，中間可以用乘號也可以是逗號，也可以寫成=SUMRPODUCT(B2:B11,C2:C11)數組之間對應元素相乘，再求和，上圖中=SUMPRODUCT(B2:B11*C2:C11)=SUMPRODUCT(B2:B11,C2:C11)=B2*C2 B3*C3 B4*C4 ······ B11*C11

用乘號和用逗號的區別在與，當有一個數組中有文本時，中間用乘號得出錯誤值，因為文本是無法參與計算的，此時中間只能用逗號隔開，然後文本將會被當成0來處理；

求銷售總量，輸入=SUMPRODUCT(B2:B11,C2:C11)

2、隔列求和

例：求1、2、3、4月份的計劃數量之和，在N3輸入=SUMPUDUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3)，下拉填充。

釋義：COLUMN函數，返回所選擇的單元格的列數，如輸入=COLUMN(B5)，則得到結果為2，意思是B5所在的單元格是第二列，輸入=COLUMN(G17)，得到結果為7，意思是G17所在的單元格是第7列，COLUMN(B3:M3)，意思是B3到M3所在的列數，得到結果{2,3,4,5,6,7,8,9,10,11,12,13}

MOD函數，用來求餘數的函數，返回兩數相除的餘數，輸入=MOD(5,2)，得到結果為1，意思是5除以2得到的餘數為1，輸入=MOD(17,3)，得到結果為2，意思是17除以3得到的餘數是2，MOD(COLUMN(B3:M3),3)=2，意思是B3:M3所在的列數除以3，得到餘數為2的單元格，得到的結果是{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE},此中TRUE為真，符合結果，FALSE為假，不符合結果。在計算中TRUE當成1計算，FALSE按0計算

計算式：=SUMPRODUCT((MOD(COLUMN(B3:M3),3)=2)*B3:M3)

=SUMPRODUCT({1,0,0,1,0,0,1,0,0,1,0,0}*{68,81,13,70,83,13,107,71,-36,85,118,33})=330

兩個數組之間元素一一對應相乘再求和。

此例中還可以用SUMIF函數，N3中輸入=SUMIF($B$2:$M$2,$K$2,B3:M3)下拉填充，注意B2:M2的絕對引用；

如果沒有「計劃、實際、差異」所在的行（刪除第二行），則不能用SUMIF函數，用SUMPRDUCT函數較好

3、多條件求和

公式用法：=SUMPRODUCT(條件1*條件2*條件3*······條件N) ，公式中多個條件相乘。

例：下圖中求2017年3月2日宏基21吋電腦銷售金額，輸入=SUMPRODUCT((A2:A18=--"2017/03/02")*(B2:B18="電腦")*(C2:C18="宏基21吋")*F2:F18)日期前的雙負號「--」是對邏輯值進行轉換的

求2017年3月3日小米5.5吋手機銷售金額，輸入=SUMPRODUCT((A2:A18=--"2017/3/3")*(B2:B18="手機")*(C2:C18="小米5.5吋")*F2:F18)，日期前加雙負號

此時也可以用SUMIFS函數，輸入=SUMIFS(F2:F18,A2:A18,"2017/3/3",B2:B18,"手機",C2:C18,"小米5.5吋")

下圖求東北和西北地區銷售總數量，輸入=SUMPRODUCT(((A2:A10="東北") (A2:A10="西北")),B2:B10)

前兩個條件相加，=SUMPRODUCT(((A2:A10="東北") (A2:A10="西北")),B2:B10)

=SUMPRODUCT((A2:A10="東北"),B2:B10) SUMPRODUCT((A2:A10="西北"),B2:B10)

4、多條件計數

如圖輸入=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))

釋義：第一個條件B2:B16>=80,當B2:B16區域有符合條件>=80時，顯示1，否則顯示0，另一條件同理

=SUMPRODUCT((B2:B16>=80)*(C2:C16>=80))

=SUMPRODUCT({0;1;1;0;1;1;0;0;0;0;0;0;1;1;0}*{1;1;1;1;1;1;0;0;0;0;0;1;0;1;0})兩兩對應相乘再求和

此例還可以用COUNTIFS函數，輸入=COUNTIFS(B2:B16,">=80",C2:C16,">=80")

5、條件排名

下圖中，求排名，在C2輸入=SUMPRODUCT(($B$2:$B$16>B2)*1) 1向下填充

公式含義：在B2:B16區域中，乘以1，把它轉化成數組才能參與運算，加1（ 1）是看比B2(79)的成績大的數量有幾個，如果有6個，則B2的排名是7；