Excel中有不少萬能函數(shù)，一個(gè)函數(shù)能頂多個(gè)函數(shù)，例如VLOOKUP、OFFSET、SUBTOTAL、AGGREGATE、SUMPRODUCT等。它們各有專長(zhǎng)，功能都非常強(qiáng)大，且受人追捧，今天Excel辦公小課堂來給大家介紹其中的SUMPRODUCT函數(shù)，樸實(shí)低調(diào)，不為大多數(shù)人所知，但卻同樣擁有超強(qiáng)的能力！一共整理了10種經(jīng)典案例，非常全面，建議先收藏，不然到時(shí)候需要就找不到了哦~SUMPRODUCT函數(shù)：返回相應(yīng)的數(shù)據(jù)或區(qū)域乘積之和功能：SUMPRODUCT(數(shù)組1,數(shù)組2,...)一、SUMPRODUCT函數(shù)第1種用法：基礎(chǔ)用法，乘積求和目的：根據(jù)每個(gè)商品的數(shù)量和單價(jià)，計(jì)算所有商品的總計(jì)公式：=SUMPRODUCT(C4:C12,D4:D12)二、SUMPRODUCT函數(shù)第2種用法：單條件求和目的：根據(jù)每個(gè)商品的數(shù)量和單價(jià)，計(jì)算行政部使用各商品總計(jì)公式：=SUMPRODUCT((C4:C12="行政部")*(E4:E12)*(F4:F12))說明：SUMPRODUCT函數(shù)(C4:C12="行政部")表示在C4:C12區(qū)域查詢“行政部”，如果此區(qū)域有包含行政部，則通過行政部中數(shù)量列和單價(jià)列的數(shù)據(jù)進(jìn)行相乘，也就是(E4:E12)*(F4:F12)，最后進(jìn)行求和匯總。三、SUMPRODUCT函數(shù)第3種用法：多條件求和目的：根據(jù)每個(gè)商品的數(shù)量和單價(jià)，計(jì)算行政部6月份使用各商品總計(jì)公式：=SUMPRODUCT((C4:C12="行政部")*(D4:D12="6月")*(E4:E12)*(F4:F12))說明：SUMPRODUCT函數(shù)(C4:C12="行政部")*(D4:D12="6月")表示在C4:C12區(qū)域查詢“行政部”，D4:D12區(qū)域查詢“6月”如果兩個(gè)區(qū)域同時(shí)包含行政部和6月，則通過行政部和6月的數(shù)量列和單價(jià)列數(shù)據(jù)進(jìn)行相乘，也就是(E4:E12)*(F4:F12)，最后進(jìn)行求和匯總。四、SUMPRODUCT函數(shù)第4種用法：單條件計(jì)數(shù)目的：根據(jù)兩個(gè)班級(jí)總?cè)藬?shù)，統(tǒng)計(jì)男同學(xué)人數(shù)公式：=SUMPRODUCT(N(D4:D13="男"))說明：SUMPRODUCT函數(shù)N(D4:D13="男")表示在D4:D13區(qū)域計(jì)算性別為“男”的個(gè)數(shù)，其中“性別”列是文本形式，則需嵌套N函數(shù)，表示返回轉(zhuǎn)化為數(shù)值后的值，從而統(tǒng)計(jì)男同學(xué)的人數(shù)。五、SUMPRODUCT函數(shù)第5種用法：多條件計(jì)數(shù)目的：根據(jù)兩個(gè)班級(jí)總?cè)藬?shù)，統(tǒng)計(jì)80分以上女同學(xué)人數(shù)公式：=SUMPRODUCT((D4:D13="女")*(E4:E13>80))說明：SUMPRODUCT函數(shù)(D4:D13="女")表示在D4:D13區(qū)域查詢性別為女的數(shù)據(jù)，(E4:E13>80)表示在E4:E13查詢成績(jī)大于80的數(shù)據(jù)，然后將兩則數(shù)據(jù)進(jìn)行相乘，從而統(tǒng)計(jì)80分以上女同學(xué)的人數(shù)。六、SUMPRODUCT函數(shù)第6種用法：不間斷排名目的：根據(jù)兩個(gè)班級(jí)學(xué)生成績(jī)進(jìn)行排名公式：=SUMPRODUCT(($E$4:$E$13>E4)/COUNTIF($E$4:$E$13,$E$4:$E$13))+1
說明：對(duì)于排名可能大家都會(huì)想到使用RANK函數(shù)來進(jìn)行計(jì)算排名，但是RANK是國(guó)際的一種排名方式，當(dāng)遇到相同名次時(shí)，下一個(gè)名次數(shù)字順序就會(huì)間斷，例如案例中有三位學(xué)生都是第5名，順延下一位學(xué)生的名次就變成了第8名了，此時(shí)就間斷了6和7名的名次，解決不間斷名次（中國(guó)式排名方式）需要使用SUMPRODUCT函數(shù)。SUMPRODUCT函數(shù)($E$4:$E$13>E4)表示在E4:E13成績(jī)區(qū)域列是否大于E4成績(jī),按F9則返回一組數(shù)組{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}，這里的TRUE表示的是1，F(xiàn)ALSE表示的是0。而COUNTIF($E$4:$E$13,$E$4:$E$13)表示數(shù)組中的元素就是對(duì)應(yīng)成績(jī)重復(fù)出現(xiàn)的次數(shù)，按F9可以看出{1;1;1;3;3;3;1;1;1;1}，這里的3表示的是成績(jī)78分出現(xiàn)3次，1表示沒有重復(fù)的分?jǐn)?shù)，也就是各分?jǐn)?shù)出現(xiàn)1次。然后將兩組數(shù)組進(jìn)行相除，再通過SUMPRODUCT函數(shù)進(jìn)行求和得到數(shù)字5，后面再加上1表示的是當(dāng)前數(shù)字的本身排名情況。如果對(duì)以上說明不理解，可以通過這種公式套路進(jìn)行理解套路：=SUMPRODUCT((條件區(qū)域>條件）/COUNTIF（要進(jìn)行排名的區(qū)域))+1七、SUMPRODUCT函數(shù)第7種用法：按條件排名目的：根據(jù)兩個(gè)班級(jí)學(xué)生成績(jī)，分別進(jìn)行按班級(jí)排名公式：=SUMPRODUCT(($B$4:$B$13=B4)*($E$4:$E$13>E4))+1說明：SUMPRODUCT函數(shù)($B$4:$B$13=B4)表示在B4:B13班級(jí)區(qū)域列判斷是否等于B4,按F9則返回一組數(shù)組{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}，($E$4:$E$13>E4)表示在E4:E13成績(jī)區(qū)域列是否大于E4，按F9則返回一組數(shù)組{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}。這里的TRUE表示的是1，F(xiàn)ALSE表示的是0，通過SUMPRODUCT函數(shù)將兩組數(shù)組乘積求和匯總，再加上1本身成績(jī)排名，從而計(jì)算出各班級(jí)學(xué)生排名情況。如果對(duì)以上說明不理解，可以通過這種公式套路進(jìn)行理解套路：=SUMPRODUCT((條件區(qū)域1=條件1）*(要進(jìn)行排名的區(qū)域))+1八、SUMPRODUCT函數(shù)第8種用法：模糊條件求和目的：根據(jù)每個(gè)商品的數(shù)量和單價(jià)，計(jì)算筆銷量總和公式：=SUMPRODUCT(ISNUMBER(FIND("筆",B4:B12))*C4:C12*D4:D12)說明：由于SUMPRODUCT函數(shù)的參數(shù)中不支持使用通配符，所以在計(jì)算帶有關(guān)鍵字的問題，在這里需要截取ISNUMBER解決關(guān)鍵字的問題，首先使用FIND函數(shù)在B4:B12數(shù)據(jù)區(qū)域中查找關(guān)鍵字“筆”，如果包含關(guān)鍵字“筆”，就返回表示關(guān)鍵字位置的數(shù)值，否則就返回錯(cuò)誤值。然后使用ISNUMBER函數(shù)判斷FIND函數(shù)的數(shù)組結(jié)果是不是數(shù)值，如果是數(shù)值，說明是包含關(guān)鍵字的，如果不是數(shù)值，那就是不包含關(guān)鍵字了。ISNUMBER最終得到由邏輯值TRUE或FALSE構(gòu)成的內(nèi)存數(shù)組，然后再與后面的區(qū)域C4:C12和區(qū)域D4:D12依次相乘，最后由SUMPRODUCT返回乘積之和九、SUMPRODUCT函數(shù)第9種用法：忽略文本求和目的：統(tǒng)計(jì)所有商品的總數(shù)量說明：公式SUMPRODUCT(C4:C12)直接對(duì)數(shù)量列中包含數(shù)字進(jìn)行求和，忽略文本。當(dāng)然在這里也可以直接用SUM函數(shù)進(jìn)行匯總。十、SUMPRODUCT函數(shù)第10種用法：數(shù)值和文本混合求和目的：根據(jù)每個(gè)商品的數(shù)量和單價(jià)，計(jì)算筆銷量總和公式：=SUMPRODUCT(--SUBSTITUTE(D4:D12,"元","")*C4:C12)說明：由于單價(jià)列包含有單位“元”，此時(shí)先使用SUBSTITUTE函數(shù)將單位元進(jìn)行替換掉，也就是--SUBSTITUTE(D4:D12,"元","")，這里兩個(gè)減號(hào)--，表示的是負(fù)負(fù)得正，也就是強(qiáng)制地把文本型的數(shù)字轉(zhuǎn)換成數(shù)值型的，最后通過SUMPRODUCT函數(shù)將數(shù)量列和單價(jià)列的數(shù)據(jù)進(jìn)行乘積求和。
|
