以前在處理?xiàng)l件求和的問(wèn)題時(shí)，SUMIF函數(shù)一向都是無(wú)往不利的，可是今天怎么犯渾了，統(tǒng)計(jì)了三個(gè)數(shù)據(jù)兩個(gè)都是錯(cuò)的：
復(fù)習(xí)一下SUMIF函數(shù)的用法：=SUMIF（條件區(qū)域，條件，求和區(qū)域）。
這個(gè)表格是按完成率對(duì)年終獎(jiǎng)進(jìn)行求和，因此條件區(qū)域使用了C列，具體條件是E2到E4這三個(gè)單元格，實(shí)際的求和區(qū)域是B列，都沒(méi)有問(wèn)題啊，可是為什么只有30%-70%這個(gè)檔的結(jié)果正確，而>70%和<30%這兩個(gè)檔的匯總結(jié)果都是0呢？
通常公式正確而結(jié)果錯(cuò)誤，都是因?yàn)閿?shù)據(jù)源不規(guī)范帶來(lái)的問(wèn)題。
在Excel中，如果在一個(gè)數(shù)字前面或者后面增加其他內(nèi)容后，數(shù)據(jù)就不是數(shù)字形式了而是文本形式了，例如：500g、0.3ml、>30、10-30這樣的內(nèi)容，都是文本形式。
但是負(fù)號(hào)和百分號(hào)比較特殊，-5、70%這種依然還是數(shù)字形式。
在本例中，條件區(qū)域是C列，全部是文本形式的內(nèi)容，作為條件來(lái)說(shuō)，引用了E列的內(nèi)容，其中的>70%和<30%兩個(gè)條件在SUMIF看來(lái)就變成了大于0.7和小于0.3了。要在全是文本形式的C列去找大于0.7或者小于0.3的數(shù)據(jù)，當(dāng)然是找不到了，所以結(jié)果為0。
此處需要解釋一個(gè)概念：>70%可以有兩種含義，第一種含義是一個(gè)比較，就是判斷數(shù)據(jù)是否大于0.7，此時(shí)的>表示大于號(hào)；另一層含義是僅僅表示“>70%”這個(gè)具體內(nèi)容。
在本例中要想得到正確的結(jié)果，需要將條件>70%和<30%變?yōu)閮?nèi)容本身，而不是大于0.7或者小于0.3這樣帶有比較的含義，只有這樣才能在條件列（C列）找到符合條件的數(shù)據(jù)，從而得到正確結(jié)果。
原因明白了以后，解決方法也就有了，以下分享三個(gè)思路，前兩個(gè)方法都是使用SUMIF函數(shù)。
根據(jù)上面的分析，是數(shù)據(jù)內(nèi)容中有大于號(hào)和小于號(hào)引發(fā)的誤會(huì)，因此可以從這個(gè)思路著手處理。
方法1：公式修改為=SUMIF(C:C,'='&E2,B:B)
注意條件的寫法，'='&E2，因?yàn)槭褂昧?amp;這個(gè)文本連接符，將原來(lái)E2單元格的內(nèi)容視為一個(gè)文本內(nèi)容，與C列數(shù)據(jù)去對(duì)應(yīng)，從而得到正確結(jié)果。
方法2：公式修改為=SUMIF(C:C,'*'&E2,B:B)
將公式1的'='&E2變成'*'&E2，兩個(gè)公式的原理是類似的，都是將>70%這種數(shù)據(jù)變成文本形式，來(lái)讓SUMIF行使條件求和的功能。
在處理?xiàng)l件求和類的問(wèn)題時(shí)，SUMIF無(wú)疑非常方便，不過(guò)還有一個(gè)更加強(qiáng)大的函數(shù)：SUMPRODUCT。
遇到SUMIF搞不定的問(wèn)題，SUMPRODUCT函數(shù)統(tǒng)統(tǒng)拿下，今天這個(gè)問(wèn)題當(dāng)然也不例外，第三個(gè)方法就是使用SUMPRODUCT函數(shù)來(lái)解決。
方法3：公式修改為=SUMPRODUCT(($C$2:$C$16=E2)*$B$2:$B$16)
這個(gè)公式?jīng)]什么多說(shuō)的，就是SUMPRODUCT條件求和的標(biāo)準(zhǔn)寫法，只需要注意一點(diǎn)，在使用這個(gè)函數(shù)的時(shí)候，一定要準(zhǔn)確選擇范圍，如果數(shù)據(jù)區(qū)域中有不可計(jì)算的數(shù)據(jù)，就會(huì)造成結(jié)果的錯(cuò)誤。
下面這種錯(cuò)誤是經(jīng)常會(huì)見(jiàn)到的：
在選擇計(jì)算區(qū)域時(shí)，是從第一行開(kāi)始的，這就造成了B1單元格的這個(gè)文字內(nèi)容要進(jìn)行乘法計(jì)算，從而得到一個(gè)錯(cuò)誤值，具體情況我們可以使用F9功能鍵看到：
第一個(gè)值就是錯(cuò)誤值，那么接下來(lái)的求和結(jié)果肯定就會(huì)錯(cuò)誤了。
關(guān)于SUMPRODUCT這個(gè)函數(shù)，之前有過(guò)很多篇文章專門講解，今后還會(huì)有文章講解，就不再啰嗦了。
小結(jié)：通過(guò)今天這個(gè)案例，我們又發(fā)現(xiàn)了SUMIF函數(shù)的一個(gè)特點(diǎn)，作為條件的第二參數(shù)，如果是單元格引用的時(shí)候，經(jīng)常不會(huì)去判斷數(shù)據(jù)屬性是文本還是數(shù)字，這在某些時(shí)候帶來(lái)了便利，但是當(dāng)數(shù)據(jù)比較特別的時(shí)候也會(huì)有一些莫名其妙的錯(cuò)誤，這里說(shuō)的比較特別是有別于那些人為因素造成的數(shù)據(jù)不規(guī)范，在本例中，完成率一列的書寫方式本身沒(méi)有問(wèn)題，只是遇上了SUMIF這個(gè)有時(shí)候有點(diǎn)不拘小節(jié)的函數(shù)，就鬧誤會(huì)了。
建議新手在遇到問(wèn)題時(shí)，首先檢查公式結(jié)構(gòu)是否有錯(cuò)，確定結(jié)構(gòu)正確時(shí)，再檢查數(shù)據(jù)源，有必要的話可以借助公式求值和F9鍵通過(guò)計(jì)算過(guò)程來(lái)檢查問(wèn)題出現(xiàn)的原因。其實(shí)這些也與自己的經(jīng)驗(yàn)有關(guān)，只要能夠堅(jiān)持關(guān)注，堅(jiān)持學(xué)習(xí)，一定會(huì)將Excel這個(gè)工具用的得心應(yīng)手。
掃碼加Excel交流群
交流心得 解決問(wèn)題
驗(yàn)證信息：Excel
聯(lián)系客服
