|
Excel中，我們經(jīng)常會(huì)求和，計(jì)數(shù)，權(quán)重計(jì)算，排名等，聽起來相當(dāng)?shù)膹?fù)雜，也要用到很多函數(shù)公式，其實(shí)，Excel中有一個(gè)功能非常強(qiáng)大的函數(shù)，它就是SUMPRODUCT函數(shù)，既能求和、計(jì)數(shù)、權(quán)重計(jì)算，還能排名等。 一 、SUMPRODUCT語法結(jié)構(gòu)。 功能：將數(shù)組之間對(duì)應(yīng)的元素相乘，并返回乘積之和。 語法結(jié)構(gòu)：SUMPRODUCT(array1,[array2],[array3]……）。 參數(shù)： 1、array1：必需。相應(yīng)元素需要進(jìn)行相乘并求和的第一個(gè)數(shù)組參數(shù)。 2、array2、array3：可選。其相應(yīng)元素需要進(jìn)行相乘并求和。 注意事項(xiàng)： 1、如果SUMPRODUCT函數(shù)具有多個(gè)參數(shù)數(shù)組，這些數(shù)組之間必需具有相同的維數(shù)，否則SUMPRODUCT將返回#VALUE!錯(cuò)誤值REF！ 2、函數(shù)SUMPODUCT將非數(shù)值型的數(shù)組元素作為0來處理。 示例： 目的：返回產(chǎn)品的銷量總額。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(C3:C9,D3:D9)。 解讀： 函數(shù)SUMPRODUCT的計(jì)算過程是：50*66+25*56+500*67+5*56+98*33+2500*57+1800*20。 二、SUMPRODUCT:單條件求和。 目的：計(jì)算出相應(yīng)的地區(qū)的銷量總和和銷售額總和。 方法： 在目標(biāo)單元格中輸入公式： 1、=SUMPRODUCT((E3:E9=H3)*D3:D9)、 2、=SUMPRODUCT((E3:E9=H3)*(C3:C9)*(D3:D9))。 解讀： 1、如果E3:E9區(qū)域中的單元格中的值和H3相等，則返回TRUE，暨1。如果不相等，則返回FALSE，暨0。 2、以“北京”地區(qū)為例：公式：=SUMPRODUCT((E3:E9=H3)*D3:D9)就是1*66+0*56+0*67+0*56+0*33+0*57+0*20=66。 3、以“北京”地區(qū)為例：公式：=SUMPRODUCT((E3:E9=H3)*(C3:C9)*(D3:D9))就是1*50*66+0*25*56+0*500*67+0*5*56+0*98*33+0*2500*57+0*1800*20=3300。 三、SUMPRODUCT：多條件求和。 目的：計(jì)算相應(yīng)地區(qū)銷量大于50的銷量總額。 方法： 在對(duì)應(yīng)的目標(biāo)單元格中輸入公式;=SUMPRODUCT((E3:E9=H3)*(D3:D9>50),(C3:C9)*(D3:D9))。 解讀： 1、首先進(jìn)行條件判斷，如果相等或大于50，則返回TRUE，暨1。如果不相等或小于等于50，則返回FALSE，暨0。 2、然后對(duì)應(yīng)的數(shù)組元素之間相乘，并返回成績之和。 四、SUMPRODUCT:單條件計(jì)數(shù)。 目的：計(jì)算銷往相應(yīng)地區(qū)的產(chǎn)品數(shù)。 方法： 在目標(biāo)單元格中輸入：=SUMPRODUCT(N(E3:E9=H3))。 解讀： 1、函數(shù)N的主要作用是將不是數(shù)值的值轉(zhuǎn)換為數(shù)值形式。 2、對(duì)應(yīng)的返回值請(qǐng)參閱下圖。 五、SUMPRODUCT：多條件計(jì)數(shù)。 目的：查詢銷售到相應(yīng)地區(qū)的相應(yīng)產(chǎn)品數(shù)。 方法： 在對(duì)應(yīng)的目標(biāo)單元格中輸入公式：=SUMPRODUCT(N(E3:E9=H3)*N(B3:B9=I3))。 解讀： 1、利用函數(shù)N將對(duì)判斷的結(jié)果轉(zhuǎn)換為數(shù)值，然后再求對(duì)應(yīng)的乘積之和。 六、SUMPRODUCT:跨列統(tǒng)計(jì)。 目的：統(tǒng)計(jì)出計(jì)劃和銷量之和。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(($C$3:$J$3=K$3)*($C4:$J4))。 解讀： 1、首先判斷$C$3:$J$3區(qū)域中的值和K$3是否相等，如果相等返回TRUE，如果不相等，則返回FALSE。 2、然后和C4:J4區(qū)域中的值進(jìn)行相乘并返回乘積之和。 3、特別注意的事：絕對(duì)引用和相對(duì)引用的實(shí)用，固定不變的范圍絕對(duì)引用，變化的相對(duì)引用。 七、SUMPRODUCT：多權(quán)重計(jì)算。 目的：根據(jù)分值比例算出最后得分。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(C$3:E$3,C4:E4)。 八、SUMPRODUCT：中國式排名。 目的：對(duì)“最后得分”進(jìn)行排名。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(($F$4:$F$10>F4)/COUNTIF($F$4:$F$10,$F$4:$F$10))+1。 |
|
|
