Excel萬(wàn)能函數(shù)SUMPRODUCT
Excel中有一個(gè)函數(shù)幾乎萬(wàn)能，無(wú)論是條件計(jì)數(shù)統(tǒng)計(jì)，還是條件求和匯總，都可以利用它來(lái)輕松搞定，它就是SUMPRODUCT，如果你還不了解它，好好往下看。
本文學(xué)習(xí)要點(diǎn)（強(qiáng)烈推薦收藏本教程）
1、SUMPRODUCT函數(shù)語(yǔ)法解析及基礎(chǔ)用法
2、SUMPRODUCT函數(shù)單條件計(jì)數(shù)
3、SUMPRODUCT函數(shù)多條件計(jì)數(shù)
4、SUMPRODUCT函數(shù)多條件求和統(tǒng)計(jì)
5、SUMPRODUCT函數(shù)多條件統(tǒng)計(jì)示例
6、SUMPRODUCT函數(shù)模糊條件求和
7、SUMPRODUCT函數(shù)跨列條件求和
8、SUMPRODUCT函數(shù)實(shí)現(xiàn)多權(quán)重綜合評(píng)價(jià)
9、SUMPRODUCT函數(shù)二維區(qū)域條件求和
想系統(tǒng)學(xué)習(xí)的同學(xué)，推薦你參加我親自授課的特訓(xùn)營(yíng)↓，系統(tǒng)提升自己。
01 SUMPRODUCT函數(shù)語(yǔ)法解析及基礎(chǔ)用法
SUMPRODUCT函數(shù)是Excel中的數(shù)學(xué)函數(shù)，用于在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。
其基本語(yǔ)法為：
SUMPRODUCT(array1,[array2], [array3], ...)
SUMPRODUCT 函數(shù)語(yǔ)法具有下列參數(shù)：
Array1：必需。其相應(yīng)元素需要進(jìn)行相乘并求和的第一個(gè)數(shù)組參數(shù)。
Array2, array3,...：可選。 2 到 255 個(gè)數(shù)組參數(shù)，其相應(yīng)元素需要進(jìn)行相乘并求和。
備注
數(shù)組參數(shù)必須具有相同的維數(shù)。 否則，函數(shù) SUMPRODUCT 將返回 #VALUE! 錯(cuò)誤值 #REF!。
函數(shù) SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0 處理。
為了大家更好的理解，下面結(jié)合一個(gè)實(shí)際案例來(lái)介紹。
上表中左側(cè)是數(shù)據(jù)源區(qū)域，包含員工的姓名、基礎(chǔ)津貼和工種強(qiáng)度系數(shù)。
需要統(tǒng)計(jì)的是所有員工的應(yīng)發(fā)津貼之和。
應(yīng)發(fā)津貼=基礎(chǔ)津貼*工種強(qiáng)度系數(shù)
這個(gè)問(wèn)題用SUMPRODUCT函數(shù)處理，會(huì)變得非常簡(jiǎn)單。
這里給出兩種方法。
方法1：
=SUMPRODUCT(B2:B12,C2:C12)
方法2：
=SUMPRODUCT(B2:B12*C2:C12)
兩種方法的區(qū)別在于，SUMPRODUCT函數(shù)的兩個(gè)參數(shù)之間的連接符號(hào)不同，方法1用逗號(hào),連接，方法2用乘號(hào)*連接。
這個(gè)案例的數(shù)據(jù)源中全部是數(shù)值，所以?xún)煞N方法返回的結(jié)果一致。
如果當(dāng)數(shù)據(jù)源中包含文本數(shù)據(jù)時(shí)，使用方法1依然可以返回正確結(jié)果，但使用方法2會(huì)導(dǎo)致文本和數(shù)值相乘，返回錯(cuò)誤值#VALUE!
這只是SUMPRODUCT函數(shù)的基礎(chǔ)用法，下面咱們?cè)俣鄟?lái)看幾個(gè)案例，介紹這個(gè)函數(shù)豐富的應(yīng)用方法。
02 SUMPRODUCT函數(shù)單條件計(jì)數(shù)
SUMPRODUCT函數(shù)處理?xiàng)l件計(jì)數(shù)問(wèn)題也是順手拈來(lái)。
表格左側(cè)是數(shù)據(jù)源區(qū)域，要在右側(cè)的黃色單元格，用公式統(tǒng)計(jì)女生數(shù)量。
D2單元格輸入以下公式：
=SUMPRODUCT(N(B2:B12="女"))
03 SUMPRODUCT函數(shù)多條件計(jì)數(shù)
SUMPRODUCT函數(shù)不單能搞定單條件計(jì)數(shù)統(tǒng)計(jì)，多條件計(jì)數(shù)也沒(méi)問(wèn)題。
表格中左側(cè)是數(shù)據(jù)源區(qū)域，右側(cè)黃色單元格輸入公式。
要統(tǒng)計(jì)高于80分的女生人數(shù)，E2單元格輸入以下公式：
=SUMPRODUCT((B2:B12="女")*(C2:C12>80))
04 SUMPRODUCT函數(shù)多條件求和統(tǒng)計(jì)
SUMPRODUCT函數(shù)不但能搞定條件計(jì)數(shù)功能，還可以處理?xiàng)l件求和統(tǒng)計(jì)。
今天咱們就來(lái)結(jié)合一個(gè)實(shí)際案例，介紹SUMPRODUCT函數(shù)多條件求和的用法。
表格中左側(cè)是數(shù)據(jù)源區(qū)域，右側(cè)黃色單元格輸入公式。
要統(tǒng)計(jì)高于80分的女生總分，E2單元格輸入以下公式：
=SUMPRODUCT((B2:B12="女")*(C2:C12>80)*C2:C12)
05 SUMPRODUCT函數(shù)多條件統(tǒng)計(jì)示例
為了大家更好地理解SUMPRODUCT函數(shù)多條件統(tǒng)計(jì)的用法，咱們?cè)賮?lái)看一個(gè)案例。
表格左側(cè)是員工業(yè)績(jī)表，右側(cè)的黃色區(qū)域需要輸入公式，統(tǒng)計(jì)3月份指定員工的業(yè)績(jī)之和。
G3單元格輸入以下公式，并向下填充
=SUMPRODUCT((MONTH($A$2:$A$14)=3)*($B$2:$B$14=F3),$C$2:$C$14)
06 SUMPRODUCT函數(shù)模糊條件求和
SUMPRODUCT函數(shù)不但能夠搞定精確條件查詢(xún)，模糊條件求和也不在話(huà)下。
由于SUMPRODUCT函數(shù)不支持通配符*和?的使用，所以遇到模糊條件求和時(shí)，需要配合其他函數(shù)嵌套完成。
下面，咱們就來(lái)介紹一下SUMPRODUCT函數(shù)模糊條件求和的用法。
表格左側(cè)是數(shù)據(jù)源區(qū)域，我們要統(tǒng)計(jì)的是銷(xiāo)售部門(mén)女性員工的獎(jiǎng)金之和。
這里的銷(xiāo)售部門(mén)就是一個(gè)模糊條件，包括銷(xiāo)售1部、銷(xiāo)售2部……等。
在F2單元格輸入以下公式：
=SUMPRODUCT(ISNUMBER(FIND("銷(xiāo)售",C2:C12))*(B2:B12="女"),D2:D12)
07 SUMPRODUCT函數(shù)跨列條件求和
今天來(lái)結(jié)合一個(gè)實(shí)際工作中經(jīng)常會(huì)遇到的問(wèn)題，介紹SUMPRODUCT函數(shù)跨列條件求和的方法。
數(shù)據(jù)源中包含每個(gè)分公司各個(gè)季度的計(jì)劃數(shù)據(jù)和實(shí)際數(shù)據(jù)，要在黃色區(qū)域分別對(duì)計(jì)劃、實(shí)際數(shù)據(jù)進(jìn)行匯總。
看了這么多干貨，記得收藏哦~
在J3單元格輸入以下公式，再填充至K6單元格區(qū)域。
=SUMPRODUCT(($B$2:$I$2=J$2)*$B3:$I3)
08 SUMPRODUCT函數(shù)實(shí)現(xiàn)多權(quán)重綜合評(píng)價(jià)
工作中遇到的KPI績(jī)效多權(quán)重計(jì)算問(wèn)題，也可以通過(guò)SUMPRODUCT函數(shù)解決。
這個(gè)表格中展示了參與KPI考核的四項(xiàng)占比，以及每個(gè)員工這四項(xiàng)分別的得分。
需要根據(jù)每個(gè)考核項(xiàng)各自占比不同，計(jì)算員工的KPI綜合得分。
黃色區(qū)域輸入公式，進(jìn)行計(jì)算。
在F3單元格輸入以下公式，向下填充。
=SUMPRODUCT(B$2:E$2,B3:E3)
09 SUMPRODUCT函數(shù)二維區(qū)域條件求和
工作中經(jīng)常遇到二維區(qū)域的條件求和，比如這種：
表格左側(cè)是數(shù)據(jù)源，右側(cè)黃色區(qū)域要輸入公式，根據(jù)二維區(qū)域條件求和。
在F2單元格輸入以下公式，填充至F2:K4單元格區(qū)域
=SUMPRODUCT(($A$2:$A$18=F$1)*($B$2:$B$18=$E2),$C$2:$C$18)
如果你覺(jué)得有用，就分享給朋友們看看吧~
聯(lián)系客服
