標(biāo)簽： itsumproduct條件求和計數(shù) | 分類： Excel函數(shù)公式 |
在Excel里，除了VLOOKUP，另一個必學(xué)的應(yīng)該是SUMPRODUCT函數(shù)了，她稱得上是函數(shù)中的“萬金油”！
首先，名字雖然長一點(diǎn)，但也因此齊集了SUM()和PRODUCT()的名字及繼承了部分功能，已可見一斑！
能稱得上“萬金油”，重要的是她能做很多COUNTIF、SUMIF的工作，在還沒有COUNTIFS、SUMIFS的年代里，甚至還兼負(fù)她們的功能，還沒完哦，某些時候甚至能完成VLOOKUP或者INDEX+MATCH組合才能完成的單條件或多條件查找任務(wù)……另外，她還可以輕易完成透視表行、列結(jié)構(gòu)的數(shù)值匯總結(jié)果，是不是有點(diǎn)“不明覺厲”了？
下面，我們用實(shí)際的數(shù)據(jù)和統(tǒng)計要求，來進(jìn)行一些條件性的計數(shù)或者求和，以此弄清SUMPRODUCT的玩法：
源表數(shù)據(jù)如上，需要統(tǒng)計的問題如下，菜鳥可以先自我測試一下，看看自己有沒有辦法完成下面的統(tǒng)計要求，讓你會用什么函數(shù)呢？
-----------------------------------------------
1.計算表中的采購總額；
=SUMPRODUCT(E2:E16,F2:F16)
[公式一]
這是最簡潔的計算公式，因為在Excel里，乘積+求和的功能正是SUMPRODUCT所專職扮演的，由此你也應(yīng)該可以看到其原生態(tài)的功能，就是乘積+求和，如果不知道這個函數(shù)又不會使用數(shù)組公式，那這題就沒法快速求解。
SUMPRODUCT的計算過程是，各個參數(shù)的逐個元素依次相乘，最后將各個乘積的結(jié)果求和。
在這里我們只有兩個參數(shù)，所以是E列和F列的值依次相乘后求和，也就是
認(rèn)清楚這個函數(shù)特性，才有利于后面求解公式的理解哦……所以，請認(rèn)真再回顧一下上面的計算過程。
-----------------------------------------------
2.統(tǒng)計水果的采購總數(shù)量；
=SUMPRODUCT((C2:C16="水果")*F2:F16)
[公式二] =SUMPRODUCT(N(C2:C16="水果"),F2:F16)
[公式三]
衍生用法之一：條件求和。
上面兩個公式求出來的結(jié)果是一樣的，也都是正確的，只是寫法不同，第一個公式只有一個參數(shù)，所以功能類似于SUM，第二個公式有兩個參數(shù)，用的是SUMPRODUCT的自身計算功能。
先說說[公式二]的計算原理或者說計算過程，首先是(C2:C16="水果")，這就是一個“條件”，比較C2:C16區(qū)域中，是否等于“水果”，這個邏輯表達(dá)式返回的結(jié)果就是TRUE或者FASLE，在這里，這個條件公式得到的結(jié)果為：
{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
（Tips: 你可以在編輯欄中，抹黑這個邏輯表達(dá)式，然后按F9，就可以看到計算結(jié)果）
這樣公式2第1步運(yùn)算后，得到的是：
=SUMPRODUCT({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}*F2:F16)
即一個邏輯數(shù)組與一個區(qū)域數(shù)組相乘，也就是：
{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}*{65;15;68;18;16;18;35;14;73;17;25;45;20;18;25}
那么這兩個數(shù)組相乘會怎么計算呢？
前面我們曾科普過一個小知識，就是說在Excel表格中，TRUE直接參與數(shù)值計算時，能轉(zhuǎn)化為1，F(xiàn)ALSE能轉(zhuǎn)化為0，在這個理論基礎(chǔ)上，上面的計算式就相當(dāng)于：
{1;0;1;0;0;0;1;0;1;0;0;1;1;0;0}*{65;15;68;18;16;18;35;14;73;17;25;45;20;18;25}
現(xiàn)在，你有點(diǎn)理解了嗎？為什么用(C2:C16="水果")就可以只計算滿足這個條件的值？
因為當(dāng)條件成立時，會返回TRUE，而TRUE直接參與計算時為1，而不滿足條件的為0，0乘以任何數(shù)都是0，因此不會計入結(jié)果中……
如果還沒理解，建議你再花點(diǎn)時間弄清楚，因為后面的求解，全都是在這個理論基礎(chǔ)上進(jìn)行的！
那[公式三]呢？為什么外面還有一個N？是什么意思呢？
這里的N不是一個字母，而是一個函數(shù)，一個能將TRUE、FALSE數(shù)值化的函數(shù)，也就是能將TRUE轉(zhuǎn)化為1；FALSE轉(zhuǎn)化為0。為什么要加一個這樣的函數(shù)呢？因為當(dāng)TRUE、FALSE作為獨(dú)立的參數(shù)參與乘積時，是會被直接以0對待，比如說=SUMPRODUCT({TRUE;TRUE},{5;5})，這個式子的計算結(jié)果就等于0，所以你的邏輯判斷式如果未經(jīng)過運(yùn)算就直接作為整體array參數(shù)，那得到的結(jié)果肯定是0。
上面不是說了TRUE和FALSE能直接參與計算的嗎？為什么這里又要用N()函數(shù)先轉(zhuǎn)化呢？
沒錯，邏輯值能直接參與計算，但問題是這里的邏輯值獨(dú)立為一個參數(shù)，并沒有直接參與任何運(yùn)算符的運(yùn)算，而是通過SUMPRODUCT的內(nèi)部機(jī)制進(jìn)行乘積，但在SUMPRODUCT的語法規(guī)則里，就明確表明“函數(shù)SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0處理”……所以才有了我們用N( )或者某些人用雙負(fù)號的這一過程，當(dāng)然更多人為追求公式簡潔，往往使用單個參數(shù)的書寫方式，也就是[公式二]的寫法。
-----------------------------------------------
3.計算雪梨的采購次數(shù)；
=SUMPRODUCT(N(B2:B16="雪梨"))
[公式四]
這個的理解就跟上面[公式三]是類似的，由于單參數(shù)SUMPRODUCT里的邏輯值，就被忽略計算，所以我們需要借助N()函數(shù)先轉(zhuǎn)化出1、0，然后滿足條件的1求和，就能得到次數(shù)或者個數(shù)。
-----------------------------------------------
4.統(tǒng)計采購數(shù)量在50斤以上的“水果”的采購總額；
=SUMPRODUCT((C2:C16="水果")*(F2:F16>50)*F2:F16*E2:E16)
[公式五] =SUMPRODUCT((C2:C16="水果")*(F2:F16>50)*F2:F16,E2:E16)
[公式六]
這個統(tǒng)計要求里，有兩條條件，而不管條件有多少，我們只管在SUMPRODUCT里，用括號把條件括起來，再用*號把各個條件連接就行了。
回過頭來，我們說說*號，為什么幾個條件之間的連接，不使用+、-、/號而選用*號呢？
我們看一下這個：
1 * 1 =1
當(dāng)然可能你看不出什么，我們換AND()來描述一下：
TRUE AND TRUE =TRUE
這個是不是就很清楚了，上面的*號和AND邏輯運(yùn)算是一致的，所以我們盡管用*號，把各個條件都連接起來，最后就是AND的集合，錯不了，因此也成就了SUMPRODUCT多條件統(tǒng)計的神話！
當(dāng)然，我們不能說*就等價于AND計算，而是只有在數(shù)組運(yùn)算中，才有這個特性而已。
那么，數(shù)組中的OR運(yùn)算，能用哪個運(yùn)算符來表示呢？這個就暫且作為思考題吧……
-----------------------------------------------
5.計算10月份的“水果”和“肉類”采購總數(shù)量。
=SUMPRODUCT((MONTH(D2:D16)=10)*((C2:C16="水果")+(C2:C16="肉類"))*F2:F16) [公式七]
這公式就厲害了，集數(shù)據(jù)列預(yù)處理和AND/OR運(yùn)算于一身啊，需要慢慢研究才能消化得了哦。
首先是MONTH()函數(shù)的運(yùn)用，我們知道SUMIF/SUMIFS可以條件或多條件求和，但她們的求和區(qū)域及條件區(qū)域參數(shù)，參數(shù)指定的類型是固定的，因此沒辦法對數(shù)據(jù)列進(jìn)行預(yù)處理，比如說這個題目，她們就需要增加輔助列才能完成統(tǒng)計，但SUMPRODUCT就不一樣，參數(shù)沒有類型上的限制，尤其是能靈活支持內(nèi)存上的數(shù)組，因此我們可以用MONTH()函數(shù)先生成一列只表示采購月份的內(nèi)存數(shù)組，然后再與要求的“10月份”進(jìn)行比較。
后面括號里的 + 號，就是OR運(yùn)算的體現(xiàn)了，1+ 0 = 1， 尤如 TRUE OR FALSE =TRUE，
理解了這一點(diǎn)，這公式也很容易解讀，只是新學(xué)者可能要花點(diǎn)時間搞清楚各個括號與運(yùn)算符號之間的聯(lián)系與差異。
--------------------
常見錯誤處理：函數(shù)用的機(jī)率越多，能遇到錯誤的機(jī)率也就越高，就像VLOOKUP一樣，天天有人問為什么……
1. #VALUE! - 值錯誤
比如說：=SUMPRODUCT((C1:C16="水果")*F1:F16)
2. #N/A - 值缺失錯誤
比如說：=SUMPRODUCT((C1:C16="水果")*F2:F16)前面邏輯運(yùn)算結(jié)果里有C1~C16共16個元素，而后面要乘積的元素則只有F2~F16共15個元素，這樣運(yùn)算后將產(chǎn)生16個結(jié)果，但由于第16個值與NULL相乘，因此最后一個元素會出現(xiàn)#N/A錯誤，公式因此也返回#N/A。
因此當(dāng)出現(xiàn)#N/A錯誤時，確認(rèn)各個參數(shù)選擇的數(shù)據(jù)區(qū)域的單元格個數(shù)是否一致即可。
----------------------
初學(xué)者，首先弄清楚各個*的計算過程，以及各種括號組合的意義所在，然后就可以橫行無忌了，畢竟其單參數(shù)的計算通式就是：
=SUMPRODUCT((條件1)*(條件2)*(...)*計算區(qū)域1*計算區(qū)域2))
聯(lián)系客服
