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