今天是常用函數(shù)的第四篇，我們來學(xué)一下Excel中的“萬能函數(shù)”——。很多人Excel高手都對它愛不釋手求和、計數(shù)、數(shù)據(jù)查詢它都能輕松搞定，下面我們就來學(xué)習(xí)下這個強(qiáng)大的Excel函數(shù)SUMPRODUCT：返回對應(yīng)的數(shù)組乘積之和語法：=SUMPRODUCT (array1， [array2]， [array3]， ...)第一參數(shù)：第1個數(shù)據(jù)區(qū)域第二參數(shù)：第2個數(shù)據(jù)區(qū)域第三參數(shù)：第3個數(shù)據(jù)區(qū)域以此類推，可以設(shè)置255個數(shù)據(jù)區(qū)域
使用這個函數(shù)我們需要注意一點(diǎn)就是：每一個參數(shù)中數(shù)據(jù)的個數(shù)與方向必須相等，否則的話函數(shù)就會返回#VALUE!這個錯誤值。隨后我們來看下它的使用方法如下圖，我們想要根據(jù)【單價】與【銷量】來計算下總的銷售金額
公式：=SUMPRODUCT(B2:B6,C2:C6)函數(shù)會將對應(yīng)的數(shù)據(jù)相乘，相乘后會得到一列結(jié)果，最后再對這一列數(shù)據(jù)求和，這個就是SUMPRODUCT函數(shù)的作用，返回對應(yīng)數(shù)據(jù)的乘積之和，是先相乘，然后再求和在Excel中我們可以將邏輯值TRUE看做是1，邏輯值FALSE看做是0，這句話翻譯過來就是這點(diǎn)是SUMPRODUCT條件計數(shù)與條件求和的基礎(chǔ)，只需要記得就可以了，這點(diǎn)總是有很多人問為什么，我只能說：王八的屁股——龜腚（規(guī)定），就好比1+1=2一樣現(xiàn)在我們想要計算下【行政部】的人數(shù)公式：=SUMPRODUCT((B2:B18=G3)*1)在這里我們僅僅使用了1個參數(shù)，B2:B18=G3是條件，如果部門等于【行政部】就會返回TRUE這個邏輯值，最后還需要將這個乘以1，將邏輯值轉(zhuǎn)換為數(shù)字，這樣的話SUMPRODUCT才會對齊求和現(xiàn)在我們想要計算下【成型車間】且【級別為2級】的員工人數(shù)公式：=SUMPRODUCT((B2:B20=I2)*(C2:C20=2))在這里我們也是僅僅使用SUMPRODUCT函數(shù)的1個參數(shù)，B2:B20=I2表示【部門等于成型車間】，C2:C20=2表示【員工級別等于2級】最后讓這2個條相乘，就能將邏輯值轉(zhuǎn)換為數(shù)值，SUMPRODUCT就能求和計算了單條件求和與單條件計數(shù)的原理幾乎是一樣的，我們只需要將后面的乘1，換成想要求和的列即可，比如現(xiàn)在，我們想要求一下【行政部的薪資總額】公式：=SUMPRODUCT((B2:B18=H3)*D2:D18)多條件求和與多條件計數(shù)是一樣的，就是在后面再多乘一個求和列即可比如現(xiàn)在，我們想要計算【成型車間等級為2級薪資總額】公式：=SUMPRODUCT((B2:B20=I2)*(C2:C20=2)*D2:D20)公式：=SUMPRODUCT(($C$2:$C$8>C2)*1)+1這個公式的本質(zhì)就是一個單條件計數(shù)，($C$2:$C$8>C2)*1會計算出比自己大的數(shù)值的個數(shù)，因為不包含本身，所以結(jié)果還需要加1公式：=SUMPRODUCT(($A$3:$A$23=$F4)*($B$3:$B$23=G$3)*$C$3:$C$23)如下圖，我們將左側(cè)的1維表格，轉(zhuǎn)換為了右側(cè)的2維表格，這個公式的本質(zhì)其實就是一個多條件求和，我們通過更改單元格的引用方式，巧妙的將其轉(zhuǎn)換為了2維表格所謂雙向求和，就是我們需要根據(jù)橫向與豎向兩個方向進(jìn)行判斷求和，現(xiàn)階段使用SUMPRODUCT是最簡單的方法。如下圖，我們想要計算項目【D】的【人工費(fèi)】公式：=SUMPRODUCT((A2:A13=H6)*(B1:F1=I6)*B2:F13)A2:A13=H6在豎向進(jìn)行判斷項目是否等于【D】B1:F1=I6在橫向判斷費(fèi)用類別是否等于【人工費(fèi)】B2:F13就是求和的數(shù)據(jù)數(shù)據(jù)區(qū)域這兩個條件也正好等于B2:F13行列方向的數(shù)據(jù)個數(shù)如下圖，我們想要計算橙色區(qū)域的數(shù)據(jù)之和，就是每隔一列求和公式：=SUMPRODUCT((MOD(COLUMN(A1:F10),2)=0)*A1:F10)這個公式的本質(zhì)是一個SUMPRODUCT單條件求和，MOD(COLUMN(A1:F10),2)=0的作用是判斷數(shù)字所在的列號是否為偶數(shù)列，如果是就求和，如果不是則不求和還是計算橙色區(qū)域的數(shù)值之和，現(xiàn)在是每隔一行求和公式：=SUMPRODUCT((MOD(ROW(A1:F10),2)=0)*A1:F10)在這里將COLUMN換成了ROW，來獲取數(shù)據(jù)對應(yīng)的行號，因為計算的還是偶數(shù)行。如果想要計算奇數(shù)行只需要將等于0，改為等于1即可以上就是今天分享的全部內(nèi)容，怎么樣SUMPRODUCT是不是非常的強(qiáng)大呢？當(dāng)然了，它也缺點(diǎn)，公式大多數(shù)都是數(shù)組計算，計算的效率就比較差，反應(yīng)可能會比較慢。我是Excel從零到一，關(guān)注我，持續(xù)分享更多Excel技巧
|
