一粪狼、SUM函數(shù)——最容易被小看的求和函數(shù)

1.函數(shù)技能

SUM函數(shù)的主要技能就是求和。這個函數(shù)在絕大多數(shù)讀者朋友心目中，都戴著 Excel最簡單的函數(shù)（沒有之一）的帽子绞呈。但是纱意，看似簡單的東西婶溯，我們往往會忽略其更深層次的功能。所以偷霉，簡單的求和我們就不介紹了迄委，直奔其拓展應(yīng)用。

2.拓展應(yīng)用

在第一章第二節(jié)里类少，我們知道了在對同一工作簿下多個工作表的同一單元格進行求和時叙身，可以使用類似“=SUM('1月:12月'!B2)”的公式。而同樣的道理也適用于對多個工作表的多個單元格進行求和硫狞。比如信轿，我們要對表Sheet1～表Sheet3中所有的A1:A3單元格、A6單元格以及B8:D11單元格（共計39個單元格）求和残吩，就可以通過以下公式實現(xiàn)：

=SUM(Sheet1:Sheet3!A1:A3,Sheet1:Sheet3!A6,Sheet1:Sheet3!B8:D11)

二财忽、SUMIF函數(shù)——專攻單條件求和

提問：在銷售匯總表（見圖4-8）中，如何統(tǒng)計“南區(qū)”和“北區(qū)”的合計金額泣侮？

圖4-8 銷售匯總表

1.函數(shù)技能

SUMIF函數(shù)是一個專門針對滿足某（單一）條件的數(shù)據(jù)求和而生的函數(shù)即彪。當(dāng)用戶依次指定條件區(qū)域、求和條件活尊、求和區(qū)域后隶校，即可計算滿足條件的數(shù)據(jù)之和漏益。

2.語法格式

SUMIF(條件區(qū)域,求和條件,求和區(qū)域)

（1）求和條件參數(shù)（以下簡稱“條件參數(shù)”）可以為文本、數(shù)值深胳、單元格地址或公式等绰疤。但是，當(dāng)條件參數(shù)為長度超過15位的文本型數(shù)字時稠屠，就會存在統(tǒng)計誤差風(fēng)險峦睡，相關(guān)原理將在下文的“注意事項”中舉例說明。

（2）條件參數(shù)默認為等于狀態(tài)权埠。例如榨了，當(dāng)需要滿足的條件為（等于）北區(qū)時，只需要在條件參數(shù)輸入"北區(qū)"攘蔽，而不能輸入"=北區(qū)"或="北區(qū)"龙屉。

（3）條件區(qū)域和求和區(qū)域可以是同為縱向（列），也可以同為橫向（行）满俗。

3.提問解答

現(xiàn)在我們來看圖4-8中南北分區(qū)匯總的匯總公式转捕，根據(jù)SUMIF的技能及語法格式，我們可知（見圖4-9）：

C13單元格的公式為：=SUMIF($B$4:$B$11,"北區(qū)",C4:C11)

C14單元格的公式為：=SUMIF($B$4:$B$11,"南區(qū)",C4:C11)

執(zhí)行行填充后唆垃，可完成2012年數(shù)據(jù)的計算五芝。

圖4-9 SUMIF函數(shù)統(tǒng)計南北區(qū)合計

正如前面提到的，SUMIF函數(shù)的條件參數(shù)是默認等于狀態(tài)辕万。那么當(dāng)我們需要滿足的條件為不等于枢步、大于、小于渐尿、大于等于以及小于等于的時候醉途，該怎么處理呢？

方法是用連接符號“&”將不等于（“<>”）砖茸、大于（“>”）隘擎、小于（“<”）、大于等于（“>=”）以及小于等于（“<=”）與相關(guān)的比較值連接起來進行反映凉夯。

例如货葬，我們以條件為“不是（不等于)南區(qū)”的邏輯來設(shè)置圖4-9中C13單元格的公式，就可以表達為（見圖4-10）：=SUMIF($B$4:$B$11,"< >"&"南區(qū)",C4:C11)

圖4-10 SUMIF函數(shù)統(tǒng)計非南區(qū)合計

4.拓展應(yīng)用

（1）條件參數(shù)使用通配符的應(yīng)用劲够。

SUMIF函數(shù)的條件參數(shù)可以使用通配符宝惰。即條件參數(shù)中，可以用“*”代表任意多個連續(xù)的字符再沧，或者用“?”代表任意一個字符。但是尊残，SUMIF函數(shù)對于數(shù)值無法使用通配符炒瘸。

這樣淤堵，當(dāng)我們需要對圖4-8中的所有重慶客戶求和時，就很方便了顷扩。

C15單元格的公式為（見圖4-11）：=SUMIF($A$4:$A$11,"重慶*",C4:C11)

除了SUMIF函數(shù)外拐邪，可以使用通配符的還有SUMIFS函數(shù)、VLOOKUP函數(shù)隘截、 COUNTIF函數(shù)等扎阶。

圖4-11 SUMIF與通配符組合

（2）使用常量數(shù)組函數(shù)進行多條件求和。

有人可能問：SUMIF函數(shù)不是專攻單條件求和的嗎婶芭，怎么又可以多條件求和了东臀？

首先說明的是，這里的多條件僅僅是條件區(qū)域內(nèi)的多條件犀农《韪常或者說，是條件區(qū)域內(nèi)對滿足多條件之一的單元格匹配的求和區(qū)域單元格進行求和呵哨。例如我們要統(tǒng)計圖4-8中天津D公司及貴陽F公司（即使有多條天津D公司及貴陽F公司的記錄）的銷售額合計赁濒，用SUMIF函數(shù)也是可以做到的。

當(dāng)然孟害，在這種情況下拒炎，單憑SUMIF函數(shù)是搞不定的，我們需要借助常量數(shù)組和SUM函數(shù)進行處理（見圖4-12）挨务。

C16單元格的公式為：

=SUM(SUMIF($A$4:$A$11,{"天津D公司","貴陽F公司"},C4:C11))

該函數(shù)實際上等價于：

=SUMIF($A$4:$A$11,"天津D公司",C4:C11)+SUMIF($A$4:$A$11,"貴陽F公司", C4:C11)

需要說明的是击你，此時常量數(shù)組中的“｛｝”是直接錄入的，而不是通過【Ctrl+Shift+Enter】組合鍵輸入耘子。

圖4-12 SUMIF函數(shù)的單區(qū)域多條件求和

5.注意事項

SUMIF函數(shù)還有一些容易忽略的特點果漾，需要提醒大家予以關(guān)注。

（1）條件參數(shù)如果是長度超過15位的文本型數(shù)字時谷誓，超過15位的部分將被默認為0绒障。例如，圖4-13中我們對物料代碼為“12345678901234512345”的數(shù)量進行求和捍歪，我們會發(fā)現(xiàn)其結(jié)果為25户辱，而并不是10。這就是因為物料代碼的長度超過15位的部分糙臼，被默認為0庐镐。此時，前兩條記錄的代碼都被定義為“12345678901234500000”变逃，所以B5單元格的公式實際上判定前兩條記錄均滿足條件必逆，故結(jié)果為25（=10+15）。

圖4-13 條件為長度超過15位的文本型數(shù)字時的誤讀

解決方案是：改用SUMPRODUCT函數(shù)（稍后將介紹）進行求和。

（2）條件區(qū)域與求和區(qū)域的對應(yīng)關(guān)系名眉。

前面所有的案例都有一個特點粟矿，即條件區(qū)域的第一個單元格與求和區(qū)域的第一個單元格都是在同一行。這也是SUMIF函數(shù)最常用的模式损拢。

其實陌粹，求和區(qū)域(第三參數(shù)單元格區(qū)域)真正起作用的就是其左上角那個單元格。例如福压，以下幾個公式其實是等效的掏秩。

C13單元格的公式1：=SUMIF(B4:B11,"北區(qū)",C4:C11)

C13單元格的公式2：=SUMIF(B4:B11,"北區(qū)",C4)

C13單元格的公式3：=SUMIF(B4:B11,"北區(qū)",C4:C11000)

從上面的公式我們可以看出，求和區(qū)域（第三參數(shù)單元格區(qū)域）起作用的就是左上角那個單元格荆姆。此單元格的作用是定位定點蒙幻，只要有此定位點，SUMIF會自動以此單元格為原點胞枕，按照第一參數(shù)區(qū)域符合條件（區(qū)域跨度一致）的單元格的坐標杆煞，找到同樣坐標位置的單元格，并對其數(shù)值求和腐泻。如前所述C13單元格公式：已知條件區(qū)域（B4:B11單元格）的（縱向）跨度為8决乎，當(dāng)求和區(qū)域的第一個單元格（C4單元格）確定后，就已經(jīng)注定了整個求和區(qū)域的終點是以C4單元格為起點派桩，（縱向）跨度為8的C11單元格构诚，且當(dāng)B4單元格滿足條件時，參與求和的為C4單元格铆惑。同理范嘱，如果我們將求和區(qū)域的第一個單元格設(shè)置為C5，那么员魏，與條件區(qū)域匹配的求和區(qū)域就應(yīng)該是C5:C12單元格丑蛤，且當(dāng)B4單元格滿足條件時，參與求和的為C5單元格（而不再是C4單元格）撕阎，以此類推受裹。

例如，新版銷售匯總表格式如圖4-14所示虏束，此時要統(tǒng)計2012年北區(qū)的銷售額棉饶，應(yīng)該如何處理？

乍一看镇匀，這貌似是個多條件求和的問題照藻。但是，我們也可以根據(jù)表中的規(guī)律汗侵，將其轉(zhuǎn)變?yōu)閱螚l件求和幸缕。這個規(guī)律就是群发，2012年的數(shù)據(jù)始終是在對應(yīng)的條件區(qū)域單元格的右下一位》⑶牵或者說也物，當(dāng)在條件區(qū)域中找到滿足條件的單元格時，需要參與求和的是求和區(qū)域中行號比其大1的單元格（見圖4-15中底色相同的單元格）列疗。這樣，與條件區(qū)域（B4:B18單元格）對應(yīng)的求和區(qū)域浪蹂，就不再是同行號的D4:D18單元格抵栈，而應(yīng)為D5:D19單元格。

D21單元格的公式為：=SUMIF(B4:B18,"北區(qū)",D5)

該公式的實質(zhì)可以表達為：=SUMIF(B4:B18,"北區(qū)",D5:D19)

圖4-14 新版銷售匯總表

圖4-15 SUMIF求和區(qū)域與條件區(qū)域的錯位應(yīng)用

三坤次、SUMIFS函數(shù)——SUMIF函數(shù)的加強版

提問：在費用明細表（見圖4-16）中古劲，如何統(tǒng)計營銷部的業(yè)務(wù)招待費總額？

圖4-16 費用明細表

1.函數(shù)技能

SUMIF函數(shù)解決了單條件求和的問題缰猴，但是當(dāng)遇到上面這種需要多條件求和的問題時产艾，就需要它的升級版SUMIFS函數(shù)了。在二維報表（例如第五章第二節(jié)案例中的各種費用報表）中滑绒，如果你不習(xí)慣使用數(shù)據(jù)透視表闷堡，那么SUMIFS函數(shù)就是公式搭建的最佳選擇。

2.語法格式

SUMIFS(求和區(qū)域,條件1區(qū)域,條件1,條件2區(qū)域,條件2,……)

雖然是SUMIF函數(shù)的升級版疑故，但是其格式卻和SUMIF函數(shù)有些不同杠览。它是把求和區(qū)域放在了第一個參數(shù)的位置，后面的各個條件區(qū)域和條件的設(shè)置規(guī)則則和SUMIF函數(shù)基本一致纵势。

此外踱阿，SUMIFS函數(shù)的條件參數(shù)也可以在除數(shù)值或文本型數(shù)值之外的情形下，使用通配符钦铁。

3.提問解答

現(xiàn)在我們來看圖4-16中需要計算的營銷部業(yè)務(wù)招待費软舌。

首先，求和區(qū)域肯定是C4:C11單元格區(qū)域牛曹。其次佛点，需要滿足的第一個條件是部門為營銷部，則條件1區(qū)域為部門字段所在的A4:A11單元格區(qū)域躏仇，條件1為“營銷部”恋脚；以此類推，條件2的區(qū)域為費用類別所在的B4:B11單元格區(qū)域焰手，條件2為“業(yè)務(wù)招待費”糟描。這樣，我們就可以得出計算公式了（見圖4-17）书妻。

C13單元格的公式為：=SUMIFS(C4:C11,A4:A11,"營銷部",B4:B11,"業(yè)務(wù)招待費")

圖4-17 SUMIFS函數(shù)統(tǒng)計營銷部的業(yè)務(wù)招待費

4.注意事項

（1）和SUMIF函數(shù)一樣船响，當(dāng)SUMIFS函數(shù)中的某個條件為長度超過15位的文本型數(shù)字時躬拢，同樣會導(dǎo)致函數(shù)存在錯誤風(fēng)險（參見本節(jié)SUMIF函數(shù)的“注意事項”）。

（2）SUMIFS函數(shù)只能在Excel2007及以后的版本中使用见间。如果讀者朋友使用的是Excel2007以前的版本聊闯，可以使用一個也能進行多條件求和的函數(shù)——SUMPRODUCT函數(shù)。

四米诉、SUMPRODUCT函數(shù)——業(yè)余求和高手

提問：在A產(chǎn)品標準成本計算單（見圖4-18）中菱蔬，如何直接計算該產(chǎn)品的成本合計？

圖4-18 A產(chǎn)品標準成本計算單

1.函數(shù)技能

關(guān)于該函數(shù)的技能史侣，我們引用Excel關(guān)于該函數(shù)幫助的說法是：在給定的幾組數(shù)組中拴泌，將數(shù)組間對應(yīng)的元素相乘，并返回各乘積之和惊橱。直白一點的解釋就是：例如數(shù)組1為A1:A3單元格蚪腐，數(shù)組2為B1:B3單元格，則該函數(shù)可以直接計算出A1*B1+A2*B2+A3*B3的值税朴。

幫助文件中并沒有提到其求和功能回季，實際上它還擅長求和與統(tǒng)計個數(shù)，幾乎囊括了常用求和系列函數(shù)的功能正林。但是泡一，求和不是它的專業(yè)，它只是臨時客串求和函數(shù)這個角色卓囚，不過其強大的功能足以讓SUMIFS和COUNTIFS下崗失業(yè)瘾杭。

2.語法格式

SUMPRODUCT(數(shù)組區(qū)域1,數(shù)組區(qū)域2,數(shù)組區(qū)域3,……)

（1）各數(shù)組區(qū)域的“戶型”必須保持一致。例如：數(shù)組區(qū)域1為A1:A10單元格哪亿，則數(shù)組區(qū)域2粥烁、數(shù)組區(qū)域3……也必須是行號為1～10區(qū)域的列（例如C1:C10單元格）。如果數(shù)組區(qū)域1為A1:B10單元格蝇棉，則數(shù)組區(qū)域2讨阻、數(shù)組區(qū)域3……也必須為行號從1～10區(qū)域的相鄰的兩列。

（2）各參數(shù)均應(yīng)獨立配置“( )”篡殷。

（3）如果只有一個參數(shù)（區(qū)域1）钝吮，則功能等同于SUM函數(shù)。

3.提問解答

現(xiàn)在我們來看圖4-18中需要計算的成本合計板辽。

很明顯奇瘦，合計金額其實就是B4:B8單元格與C4:C8單元格各同行號單元格對應(yīng)乘積的和。所以劲弦，套用到SUMPRODUCT函數(shù)的格式中耳标，數(shù)組區(qū)域1為B4:B8單元格，數(shù)組區(qū)域2為C4:C8單元格。這樣惦蚊，我們的公式就出來了（見圖4-19）。

C9單元格的公式為：=SUMPRODUCT(B4:B8,C4:C8)

圖4-19 SUMPRODUCT函數(shù)的對應(yīng)區(qū)域乘積求和應(yīng)用

4.拓展應(yīng)用

SUMPRODUCT函數(shù)可以作為SUMIFS的超級替身進行多條件求和怔锌。其語法格式為：

SUMPRODUCT((條件1區(qū)域="條件1")* (條件2區(qū)域="條件2")*（……）* 求和區(qū)域)

SUMPRODUCT((條件1區(qū)域="條件1")* (條件2區(qū)域="條件2")*（……）,求和區(qū)域)

其中：

（1）條件區(qū)域和條件之間需要邏輯判斷符號（例如“=”“<>”等）連接砸琅。

（2）屬于條件判斷的參數(shù)需要單獨配上“( )”,而求和區(qū)域可以不使用“( )”宋距。

據(jù)此，即使在使用Excel2003的情況下症脂，我們?nèi)匀荒芡瓿蓤D4-16中的營銷部業(yè)務(wù)招待費的統(tǒng)計（見圖4-20）谚赎。

C13單元格的公式為：

=SUMPRODUCT((A4:A11="營銷部")*(B4:B11="業(yè)務(wù)招待費")*C4:C11)

或=SUMPRODUCT((A4:A11="營銷部")*(B4:B11="業(yè)務(wù)招待費"),C4:C11)

圖4-20 SUMPRODUCT函數(shù)的多條件求和應(yīng)用

SUMPRODUCT函數(shù)多條件求和時，求和區(qū)域之前使用“诱篷，”和“*”的區(qū)別：當(dāng)擬求和的區(qū)域中無文本時兩者無區(qū)別沸版；當(dāng)有文本時，使用“*”時會出錯兴蒸，返回錯誤值#VALUE!，而使用“细办，”時SUMPRODUCT函數(shù)會將非數(shù)值型的數(shù)組元素作為0處理橙凳，不會報錯，故使用“笑撞，”容錯能力更高岛啸。

需要提醒大家注意的是，SUMPRODUCT函數(shù)不能使用通配符茴肥。

五坚踩、ROUND函數(shù)——從根源上控制小數(shù)位數(shù)

提問：在稅金統(tǒng)計表（見圖4-21）中，根據(jù)價稅合計除以1.17乘以0.17計算的增值稅（已經(jīng)通過設(shè)置單元格格式控制為2位小數(shù)）瓤狐，為什么與合計數(shù)存在誤差瞬铸？

注：0.24+0.07+0.34+0.17+0.14=0.96

圖4-21 稅金計算表

1.函數(shù)技能

ROUND函數(shù)可將某個數(shù)字四舍五入為指定的位數(shù)。因為通過單元格格式設(shè)定的小數(shù)位數(shù)只是按設(shè)定的位數(shù)四舍五入保留來顯示（見圖4-22）础锐。我們在D1:D3單元格分別錄入0.123 45嗓节，雖然通過單元格格式設(shè)置將其小數(shù)點控制在2位，但是從編輯欄我們可以看到其本質(zhì)仍然是0.123 45皆警。所以在D4單元格對D1:D3單元格進行求和時參與計算的是3個0.123 45拦宣，其和為0.370 355⌒判眨控制小數(shù)位數(shù)后鸵隧，顯示為0.37，而不是我們希望的0.36（=0.12×3）意推。如果要想讓數(shù)據(jù)徹底地放棄被舍掉的小數(shù)位數(shù)豆瘫，就得用ROUND函數(shù)了。

圖4-22 單元格設(shè)置控制小數(shù)位數(shù)背后的真相

2.語法格式

ROUND(待控制小數(shù)位數(shù)的數(shù)據(jù),控制的小數(shù)位數(shù))

3.提問解答

我們對計算稅額的公式稍加處理左痢，就可得到?jīng)]有誤差的合計數(shù)了（見圖4-23）靡羡。

C4單元格的公式為：=ROUND(B4/1.17*0.17,2)

執(zhí)行列填充后系洛，合計數(shù)就顯示為0.96了。

圖4-23 ROUND函數(shù)完美鎖定小數(shù)位數(shù)

六略步、MOD函數(shù)——余數(shù)計算器

1.函數(shù)技能

MOD函數(shù)是計算兩個數(shù)據(jù)相除的余數(shù)的工具描扯。一般用于判斷具有固定周期、間隔期等規(guī)律的信息趟薄。在第五章第四節(jié)的案例中绽诚，我們將看到該函數(shù)的具體應(yīng)用。

2.語法格式

MOD(被除數(shù),除數(shù))

其中杭煎，被除數(shù)和除數(shù)均可以為數(shù)值恩够、單元格地址或公式等。

示例：=MOD(12,5)羡铲，結(jié)果為2蜂桶。

=MOD(3*4,6)，結(jié)果為0也切。

七扑媚、INT函數(shù)——整數(shù)切割機

1.函數(shù)技能

INT函數(shù)是一個以只保留數(shù)據(jù)整數(shù)為己任的工具。該函數(shù)瞧不起優(yōu)柔寡斷雷恃、和稀泥的四舍五入思想疆股，而是采用彪悍的一刀切作風(fēng)——只要不夠整數(shù)，通通都會被切割掉倒槐。

2.語法格式

INT(待切割的數(shù)據(jù))

其中旬痹，待切割的數(shù)據(jù)可以為數(shù)值、公式或單元格地址等讨越。

示例：=INT(3.99)两残，結(jié)果為3。

=INT(20/3)把跨，結(jié)果為6磕昼。

八、ABS函數(shù)——絕對值轉(zhuǎn)換器

1.函數(shù)技能

ABS函數(shù)可以將任何數(shù)值轉(zhuǎn)換為其絕對值节猿。

2.語法格式

ABS(待轉(zhuǎn)換為絕對值的數(shù)據(jù))

其中票从，待轉(zhuǎn)換為絕對值的數(shù)據(jù)可以為數(shù)值、單元格地址或公式等滨嘱。

示例：=ABS(5.30)峰鄙，結(jié)果為5.30。

=ABS(-5.30)太雨，結(jié)果為5.30吟榴。

使用ABS函數(shù)后，本章第一節(jié)圖4-6囊扳、圖4-7中的公式可以更加簡化吩翻。其中圖4-6中的公式表達如下兜看。

E3單元格公式為：=IF(D3="預(yù)算外費用",D3,IF(ABS(D3)<=2%,"正常",""))

九、SUM(IF)數(shù)組公式——單條件區(qū)域求和數(shù)組函數(shù)

首先需要說明的是狭瞎，SUM(IF)并不是一個函數(shù)细移，而是一個數(shù)組公式。由于本書并不過多涉及數(shù)組公式熊锭，所以我們將其作為數(shù)組公式的代表弧轧，在數(shù)學(xué)函數(shù)章節(jié)做一個簡單的介紹。

提問：在車輛燃油統(tǒng)計表（見圖4-24）中碗殷，如何統(tǒng)計行政部一季度燃油費合計精绎？

圖4-24 車輛燃油統(tǒng)計表

如果我們需要對滿足條件的一個連續(xù)區(qū)域進行求和，機械的辦法是求和區(qū)域的各列（行）分別使用SUMIF函數(shù)求和锌妻，再匯總代乃。但是如果要求和的區(qū)域達到一定數(shù)量時，這個疊加的方案就很費事了仿粹。這個時候襟己，我們就需要用數(shù)組公式SUM（IF）了，其語法格式為：

｛=SUM(IF(條件區(qū)域=滿足的條件,求和區(qū)域))｝

此公式等價于：｛=SUM((條件區(qū)域=滿足的條件)*(求和區(qū)域))｝

當(dāng)然牍陌，判斷條件除了等于外，也可以是不等于员咽、大于等于或小于等于毒涧。

這個公式的核心部分語法和IF函數(shù)雷同。唯一需要強調(diào)的是贝室，數(shù)組公式最外面的“｛｝”不能手工錄入契讲，而是在設(shè)置完公式主體“=SUM(IF(條件區(qū)域=滿足的條件,求和區(qū)域))”后，用鼠標選定（涂黑）該公式滑频，再同時按下【Ctrl+Shift+Enter】鍵確定捡偏，“｛｝”外套就自動穿上了。這樣峡迷，我們就可以解決前面的提問了（見圖4-25）银伟。

B10單元格的公式為：{=SUM(IF(B4:B9="行政部",C4:E9))}

圖4-25 SUM（IF）數(shù)組公式的應(yīng)用

在第五章第三節(jié)的案例中，我們將會看到這個數(shù)組公式的具體應(yīng)用绘搞。