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