趁著空閑，盧子又仔細(xì)看了一下這個功能。發(fā)覺小數(shù)點的位數(shù)可以調(diào)節(jié)，允許為負(fù)數(shù)。如果將小數(shù)點位置設(shè)
每天都有無數(shù)人在討論她的用法?；?/div>
上每天都有一些精妙的公式被發(fā)掘出
來。學(xué)公式靠的是邏輯思維和思考問
題的方法(角度)，不像技巧那樣需死記硬
背。不經(jīng)過認(rèn)真思考、舉一反三是永遠(yuǎn)
學(xué)不好公式的。學(xué)好公式，絕大部分的工
作都可以輕松搞定。曾經(jīng)有人說過，只要
學(xué)好公式再配合一些技巧，你就可以成為
高手。讓我們一起向高手邁進(jìn)一小步！
04
Excel效率手冊——早做完，不加班
4.2.1 閑聊SUM
盧子：你會SUM函數(shù)嗎？
還沒等盧子繼續(xù)說下去，網(wǎng)友就耐不住
性子了。
網(wǎng)友：別開玩笑啦，這個誰不會。如圖4-44所示，
選擇單元格A7，在“公式”選項卡里，單
擊“自動求和”按鈕，就自動對區(qū)域進(jìn)行
求和，簡單得要死。
網(wǎng)友：幫助也不過如此，就是對所有數(shù)字求和，
我以為有什么稀奇。
盧子：實際幫助說到的用法，僅僅是最基礎(chǔ)的用
法，連SUM的冰山一角還沒有見著。
網(wǎng)友：有這么夸張嗎，你倒是說說看？
盧子：這里通過兩部分來說明 SUM的用法——基
礎(chǔ)用法與知識擴展。
基礎(chǔ)用法
案例 1：如圖 4-46所示，這是一份每
月銷售清單?，F(xiàn)在想按月份累計銷售
額，該怎么辦？
圖4-44
自動求和
盧子：你還知道SUM的其他用法嗎？
網(wǎng)友：這個不就自動求和而已，還有什么用法？
盧子：按F1鍵調(diào)出幫助，輸入sum搜索，會出現(xiàn)這
個函數(shù)的用法，如圖4-45所示，
圖4-46
每月銷售清單
選擇C2單元格，輸入公式，并向下填
充公式。
=SUM(B$2:B2)
給第一個B2的行塞點美元(B$2)，讓行站
住不動，下拉的時候不會有任何變化，
依然是B$2。第二個B2因為沒給美元，
圖4-45 SUM的用法
78
第 4 章 最受歡迎的函數(shù)與公式
下拉就變成B3、B4……B13了。在C3區(qū)域就變成了B$2:B3，也就是對B2:B3區(qū)域求和。在
C13區(qū)域變成了B$2:B13，也就是對B2:B13區(qū)域進(jìn)行求和。適當(dāng)給點美元，會起到意想不
到的效果。
案例2：如圖4-47所示，這是一份人員
銷售清單，需要匯總銷售額，如果是
你會怎么匯總呢？
網(wǎng)友：銷售額分成三列，每一列就用“,”隔開，
公式如下：
=SUM(F2:F7,H2:H7,J2:J7)
盧子：看來你對幫助還理解不夠透徹，一起來看
看函數(shù)說明。
圖4-47
人員銷售清單
函數(shù)說明
如果參數(shù)是一個數(shù)組或引用，則只計算其中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯
值或文本將被忽略。
如果任意參數(shù)為錯誤值或為不能轉(zhuǎn)換為數(shù)字的文本，Excel 將會顯示錯誤。
也就是說，如果區(qū)域中有文本，將被忽略，所以只要寫一個區(qū)域就行了。
=SUM(E2:J7)
網(wǎng)友：這些小細(xì)節(jié)還真沒注意看，多謝提醒。
盧子：前面都是基礎(chǔ)的，再來看看難度大點的。
知識擴展
案例3：如圖4-48所示，這是一份沒經(jīng)
過任何處理的不良明細(xì)，存在錯誤值，
直接求和出錯。這個有辦法解決嗎？
圖4-48
含錯誤值不良明細(xì)
79
Excel效率手冊——早做完，不加班
網(wǎng)友：這回我仔細(xì)研究了幫助，如果任意參數(shù)為錯誤值或為不能轉(zhuǎn)換為數(shù)字的文本，那么Excel 將會顯
示錯誤。這種問題只有刪除錯誤值才可以統(tǒng)計，不然會出錯。呵呵，這回我沒說錯吧，我也挺
用功的。
盧子：幫助僅供參考，我們還需要學(xué)會思考問題。“定位”錯誤值，然后刪除也是一種辦法。其實也可
以直接求和，在這之前先了解一下IFERROR函數(shù)，如圖4-49所示。
圖4-49 IFERROR函數(shù)說明
說白了，就是可以將錯誤值顯示成你
想顯示的任何形式。
=IFERROR(錯誤值,顯示值)
如圖4-50 所示，既然這樣，可以先通過
將錯誤值顯示成0，然后再匯總。
雖然這樣可以匯總，但會產(chǎn)生一個輔助
列?；氐交A(chǔ)知識，再看看數(shù)組公式的概念。
圖4-50
輔助列匯總
數(shù)組公式可以執(zhí)行多項計算并返回一個或多個結(jié)果。數(shù)組公式必須按組合鍵Ctrl Shift Enter
結(jié)束，在輸入數(shù)組公式時，Excel 會自動在大括號 { } 之間插入該公式。
利用數(shù)組可以省略輔助列，直接得到結(jié)果。
=SUM(IFERROR(B2:B8,0))
先將錯誤值全部轉(zhuǎn)換成 0，然后再匯總，因為轉(zhuǎn)換過程需要重新運算，所以需要按組合鍵
Ctrl Shift Enter結(jié)束。借助數(shù)組可以省略很多中間步驟，如果你想成為別人眼中的高手，數(shù)組
必須熟練掌握。下面再通過兩個例子來鞏固對數(shù)組公式的理解。
80
第 4 章 最受歡迎的函數(shù)與公式
案例 4：如圖 4-51所示，這是一份每
月銷售清單，現(xiàn)在要統(tǒng)計銷售額大于
500的次數(shù)？
剛好F2是600，顯示1，下拉試試，F(xiàn)3是
120，顯示0。跟我們的預(yù)想一樣。如果是整個區(qū)
域判斷！
=IF(F2:F13>500,1,0)
在編輯欄按F9鍵將公式抹黑，得到
{1;0;1;0;0;0;0;1;0;0;0;0}
跟我們在單元格下拉公式得到的結(jié)果是
一樣的，只是顯示在數(shù)組中。這樣只是起到判
斷而已，還需要求和。只需要在IF外面再嵌套
SUM函數(shù)就行了，因為要執(zhí)行多重計算，所以
是數(shù)組公式。
圖4-51
每月銷售清單
先來了解一下IF函數(shù)的用法，如圖4-52
所示。
=SUM(IF(F2:F13>500,1,0))
網(wǎng)友：貌似看懂了點，對了這個 F9鍵是干嘛用？
盧子：F9鍵，人稱“獨孤九劍”，看過《笑傲江
湖》的人應(yīng)該知道令狐沖的獨孤九劍很厲
害。既然 F9鍵有這個雅稱，一定有過人之
處。F9鍵是解讀公式的利器，公式如果太
長了看不懂，將看不懂的地方抹黑就知道運
算結(jié)果了?？赐旰笤侔唇M合鍵Ctrl Z返回，
否則公式就變了。步步高點讀機有一句廣告
詞：哪里不會點(抹)哪里，so easy！媽媽再
也不用擔(dān)心我的學(xué)習(xí)了。用在這里再適合不
過，如公式：
圖4-52 IF函數(shù)幫助
通俗點就是：
=IF(條件,滿足條件的返回值 ,不滿足條件的返
回值)
=SUM(SMALL(IF(B$1:B$10=5,ROW($1:$10)),
ROW(1:2))*{-1;1})-1
可以通過 IF來判斷銷售額是否滿足大于
500，讓大于500的顯示1，小于或等于500的顯
示0?？梢栽趩卧裰休斎牍?，看看是否跟
我們想的一樣。
這個ROW($1:$10)看不懂，你就抹黑它再按
F9鍵。
=SUM(SMALL(IF(B$1:B$10=5,{1;2;3;4;5;6;7;8;
9;10}),ROW(1:2))*{-1;1})-1
=IF(F2>500,1,0)
原來相當(dāng)于 1~10，記得按組合鍵 Ctrl Z返
81
Excel效率手冊——早做完，不加班
回哦，Excel是允許你后悔的。
網(wǎng)友：原來是協(xié)助解讀公式的一個工具。
盧子：這個很好用，我經(jīng)常用。還有一個叫公式求值的功能，效果跟這個差不多，有空你也可以了解一下。不過
公式求值讓人覺得自己就是一個機械操作工，而F9鍵讓人覺得自己是一個劍客，凡事隨心所欲。
再舉一個例子來說明數(shù)組的用法
案例5：還是以每月銷售清單那個附件來說明，求銷售額大于 500的人的總銷售額。
有了上面的基礎(chǔ)，再來了解這個就很容易了。
=SUM(IF(F2:F13>500,F2:F13,0))
用IF進(jìn)行判斷，讓大于500的顯示原來的銷售額，其他顯示0，進(jìn)行求和剛好得到銷售額
大于500的人的總銷售額。
網(wǎng)友：這回懂了，謝謝盧子。
盧子：今天這個SUM函數(shù)就先告一段落，自己有空再去熟練一下，欲知SUM更多用法，且聽下回分解。
網(wǎng)友：謝謝，期待下回更精彩的講解！
這邊剛結(jié)束，就收到簡單、Simple的私聊消息。
簡單：辛苦了。
盧子：累死人，沒想到一講就是兩個小時，比1000米賽跑還累。
簡單：明天把這些整理一下，分享到群里。
盧子：好的。
Simple：講得不錯，挺有邏輯性的。
盧子：已盡力了，但愿這次能收到好的評價?；仡^你看看大家的評價怎樣？
Simple：明后天應(yīng)該就能知道大家的想法，到時跟你匯報。
盧子：那我先睡了，腦力活原來比體力活更累。
Simple：那早點休息吧。
…………
第二天晚上，收到Simple的私聊信息。
Simple：大家評價蠻高的，都在打聽什么時候再組織講座，到時得提前通知。群里不斷有
陌生人加入，說要聽課。看你太辛苦，昨晚的講座內(nèi)容已經(jīng)幫你整理好了。
盧子：謝了，回頭我直接把你整理的分享出來就行。這回的辛苦總算沒白費，我回頭再準(zhǔn)
備下，爭取這兩天舉行第二回講座。
82
第 4 章 最受歡迎的函數(shù)與公式
經(jīng)過兩天的準(zhǔn)備，盧子把SUM函數(shù)的其
他資料整理好了。這回只是對上回知識的補
充，并不會涉及太多的知識點。
盧子：今晚繼續(xù)學(xué)習(xí) SUM函數(shù)，通過三個小例子
來對上一回的知識進(jìn)行補充說明，大約 1小
時就可以講完了，呵呵。
晚的正題，別見怪，剛才只是有感而
發(fā)而已。
剛才提到了替換這個詞，函數(shù)中也有
屬于自己的替換函數(shù)，SUBSTITUTE
函數(shù)，用法如圖4-54所示。
網(wǎng)友：老師辛苦了，沒想到小小 SUM函數(shù)居然這
么神奇，這回要用功學(xué)習(xí)才是。
盧子：很好，那一起開始學(xué)習(xí)吧。
案例1：如圖4-53所示，這是一份含有
單位的人員銷售清單，直接求和得不
出正確答案，怎么才能讓含單位的銷
售額可以求和呢？
圖4-54 SUBSTITUTE函數(shù)幫助
SUBSTITUTE 的第四參數(shù)為可選，那
就先別管它，其他參數(shù)可以理解為：
=SUBSTITUTE(文本,需要替換的舊字符,
替換成新的字符)
單元格中的“元”是多余的，需要替
換成空，空可以用＂＂表示，替換成
空后直接求和，可以嗎？
圖4-53
含單位的人員銷售清單
幫助提到，SUM函數(shù)會自動忽略文
本，600元就是文本，不屬于數(shù)字。最
簡單的做法就是將“元”替換成空，
然后自定義單元格格式G/通用格式
“元”。很多人就是搞不明白一格一屬
性的道理，才會造成匯總數(shù)據(jù)困難重
重。正確的做法應(yīng)該將“元”寫在字
段名那里變成銷售額 (元)，這樣別人一
看便知。廢話了這么多，還沒扯到今
= SUM(SUBSTITUTE(B2:B7,＂元＂,
＂＂))
網(wǎng)友：這個是數(shù)組公式，用法也跟前面說得差不
多，應(yīng)該可以匯總。
盧子：SUBSTITUTE函數(shù)屬于文本函數(shù)，所以替
換得到的數(shù)字也屬于文本，在這里叫作文
本數(shù)字。數(shù)字有兩種類型，一種是文本數(shù)
字，一種是真正的數(shù)字，即數(shù)值。數(shù)值可
83
Excel效率手冊——早做完，不加班
以求和，而文本不能求和。如賬簿上的數(shù)
綜上，最終的數(shù)組公式為：
字跟墻上的數(shù)字是不同的，前者我們可以
用這些數(shù)字進(jìn)行各種分析，后者只能當(dāng)欣
賞用。那有什么辦法還原數(shù)字的本質(zhì)呢？
=SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂))
網(wǎng)友：沒想到數(shù)字還有這些學(xué)問，長見識了。
盧子：再來看另一種不規(guī)范輸入的案例。
利用VALUE函數(shù)可以將文本型轉(zhuǎn)換成
數(shù)值型。
案例2：如圖4-55所示，這是一份含附
加分的成績明細(xì)表，分?jǐn)?shù)分為基本分
（左），附加分（右），怎么匯總分?jǐn)?shù)呢？
=VALUE(＂600＂)
但一般情況下不會這么做，而是通過
運算轉(zhuǎn)換。
一起來了解“減負(fù)”運算
在函數(shù)或公式中，運算過程會自動把文
本轉(zhuǎn)換為數(shù)值(一個隱含過程)，再與數(shù)值進(jìn)行
運算，負(fù)值運算(-)也是一種運算，能把文本
轉(zhuǎn)換成數(shù)值。
圖4-55
含附加分成績明細(xì)
仔細(xì)觀察發(fā)現(xiàn)：
-＂600＂=-600
有附加分的中間都隔著空格，如圖4-56
所示。這跟分?jǐn)?shù)寫法的前半部分一
樣，只是少了斜杠 (/) 分母。既然這
樣，我們就可以構(gòu)造后半部分。分
數(shù)&“/1”，E2就得到“90 5/1”，
通過&函數(shù)得到的是文本數(shù)值，前面加
“－－”讓他變成數(shù)值，E3本身就是
數(shù)字，不必轉(zhuǎn)換。
還記得負(fù)負(fù)得正吧？例如：
-(-＂600＂)=-(-600)=600
可簡寫為：
--＂600＂=600
--可以把文本轉(zhuǎn)換為數(shù)值，但它不是標(biāo)準(zhǔn)
的轉(zhuǎn)換方式，而是借用負(fù)運算的隱含功能。
其實負(fù)負(fù)運算稱為減負(fù)運算更好，即減
去數(shù)字的負(fù)擔(dān)，還原數(shù)字的本質(zhì)。
=SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂))，
將這一部分用F9鍵抹黑，得到：
=SUM({600;120;1000;210;129;123})
這樣就能夠求和了。
圖4-56
分?jǐn)?shù)的組成部分
84
第 4 章 最受歡迎的函數(shù)與公式
通過上面的分析，問題已經(jīng)解決一大半
了，只需判斷分?jǐn)?shù)是不是數(shù)值，是的
話就顯示本身的值，不是就用--(分?jǐn)?shù)
&“/1”)。怎么判斷單元格的值是不是
數(shù)值呢？先來了解一下 ISNUMBER 函
數(shù)，它只有一個參數(shù)。
案例3：如圖4-57所示，這是一份人員銷
售清單，經(jīng)常要在總銷售額處插入新的
人員。用SUM直接統(tǒng)計不會對新增加人
員的銷售額進(jìn)行匯總，該怎么處理呢？
=ISNUMBER(值)
如果是數(shù)字就顯示 TRUE，否則顯示
FALSE。
到這里思路都出來了，只需輸入數(shù)組公
式到單元格里來驗證我們的想法是否正
確就可以了。
圖4-57
人員銷售清單
按組合鍵Ctrl F3定義一個叫“上一
行”的名稱，引用位置為B20。
=SUM(IF(ISNUMBER(E2:E7),E2:E7,--
(E2:E7&＂/1＂)))
這個引用為相對引用，每插入一行引
用位置就會動態(tài)變化，所以用下面的
公式就可以搞定，以后插入行也會自
動匯總進(jìn)去，如圖4-58所示。
網(wǎng)友：測試通過，原來寫公式跟斷案一樣，需要
嚴(yán)謹(jǐn)?shù)姆治?，才能不判錯案子。
盧子：公式主要是靠邏輯，大腦要經(jīng)常動，這樣
才不會生銹。
=SUM(B15:上一行)
溫馨提示
分子不能超過5位數(shù)，否則出錯。
如：1 100000/1 用=ISNUMBER(＂1
100000/1＂)測試顯示為 FALSE，證明這個
是文本。
圖4-58
定義名稱
正常的話，不會出現(xiàn)這樣的分?jǐn)?shù)，稍
微了解一下就行。
一個過來人的忠告
有人說直接用 SUM( 區(qū)域)就行，插入行
后區(qū)域會自動擴展。但我說這是Excel的BUG
85
Excel效率手冊——早做完，不加班
你信嗎？曾經(jīng)我就因為直接用SUM(區(qū)域)導(dǎo)致開錯兩張單，最后核對金額的時候才發(fā)現(xiàn)異常，
還好金額不大，如果是大金額，這將給公司帶來多大的損失呀。小心駛得萬年船，如果你堅
持你的想法，用SUM(區(qū)域)插入行后請選擇區(qū)域，查看狀態(tài)欄的總計跟公式匯總是否一致。圖
4-59為直接用SUM(區(qū)域)插入行后的結(jié)果，數(shù)量添加的行會自動匯總進(jìn)去，金額卻沒有自動增
加。為了保險起見還是定義名稱，當(dāng)然還有其他方法，例如：
=SUM(B15:INDEX(B:B,ROW()-1))
圖4-59 在“合計”上面插入行后的結(jié)果
關(guān)于SUM的用法到這里已經(jīng)結(jié)束了，如果還有什么疑問可以向Simple提出來，到時我再統(tǒng)
一解答。
網(wǎng)友：又學(xué)到了幾招，謝謝老師。
盧子：對了，以后叫我盧子就行，這樣我還習(xí)慣點，下節(jié)課根據(jù)大家的反饋再決定講什么函數(shù)。
4.2.2 求和之王SUMPRODUCT
盧子：經(jīng)過這幾天的反饋，有兩個問題提的人比較多。
其1：實例是用公式 =SUM(IF(F2:F13>500,F2:F13,0))，但現(xiàn)實有很多公式是這種形式
=SUM((F2:F13>500)*F2:F13)，后者是怎么得出來的？
其 2： SUM的數(shù)組公式要按組合鍵
Ctrl Shift Enter 結(jié)束才能得到正確答
案，很麻煩，經(jīng)常會忘記按組合鍵
Ctrl Shift Enter，有沒有其他函數(shù)可以取
代SUM的數(shù)組形式呢？
一起來看看問題 1，如圖4-60所示，以
銷售額大于500的人的總銷售額為例進(jìn)
行說明。
圖4-60 銷售額大于500的人的總銷售額
86
第 4 章 最受歡迎的函數(shù)與公式
=SUM((F2:F13>500)*F2:F13) 可能很多人
想知道抹黑地方的意思，有句話叫心急吃不
了熱豆腐，凡事得一步步慢慢來，急不得。
開始先不要直接用整個區(qū)域解讀，先分成一
個單元格的判斷解讀，單元格理解后再轉(zhuǎn)換
成區(qū)域理解。這樣更有助于理解，適當(dāng)?shù)臅r
候配合F9鍵，效果會更好。
網(wǎng)友：原來公式是這么解讀的，老想一步就到
位，反而理解不好。先拆開，再合并，先
記住這個方法。
=B2>500如果成立就顯示 TRUE，否則
顯示FALSE。在這里B2>500 成立，顯
示TRUE。
=(B2>500)*B2 等同于 =TRUE*B2，
得到600，也就是說在這里 TRUE相當(dāng)
于1。
盧子：問題2用SUMPRODUCT可以取代SUM的數(shù)
組公式。還是老方法，先看SUMPRODUCT
函數(shù)的幫助，如圖 4-61所示，對多個區(qū)域
先相乘，后匯總。
=(B3>500)*B3等同于=FALSE*B3，得到
0，也就是說在這里FALSE相當(dāng)于0。
*可以讓符合條件的值顯示其本身，不
符合條件的值顯示 0，但它不是標(biāo)準(zhǔn)的
轉(zhuǎn)換方式，而是借用乘法運算的隱含
功能。再回到公式上
=SUM((F2:F13>500)*F2:F13)
按F9鍵抹黑得到：
=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;F
ALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FA
LSE})*B2:B13)
圖4-61 SUMPRODUCT幫助
得到一組由TRUE跟FALSE組成的數(shù)組：
基礎(chǔ)用法
=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;F
ALSE;FALSE;TRUE;FALSE;FALSE;FALSE;
FALSE})*B2:B13)
畢竟是出自一家人，幫助都差不多，非
常好理解，如圖4-62所示，統(tǒng)計總金額。
數(shù)組*B2:B13，讓符合條件的都顯示其
本身，其他顯示0。
=SUM({600;0;1000;0;0;0;0;1000;0;0;0;0})
到這步應(yīng)該可以理解了吧。解讀公式一
圖4-62
統(tǒng)計總金額
87
Excel效率手冊——早做完，不加班
=SUMPRODUCT(B2:B4,C2:C4)
只強調(diào)一句，SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0處理，如B4現(xiàn)在的值是文本
=SUMPRODUCT({2;5;＂無＂},C2:C4)，“無”在這里等同于0。在此不再對這個函數(shù)的基礎(chǔ)用
法進(jìn)行說明，否則會讓大家誤認(rèn)為侮辱了你們的智商。
知識擴展
通過對SUM的學(xué)習(xí)知道了它可以求和、計數(shù)，SUM能做到的SUMPRODUCT 都能做到，
而且做得更好。SUMPRODUCT函數(shù)本身就支持?jǐn)?shù)組，所以條件計數(shù)、求和的時候不需要按組
合鍵Ctrl Shift Enter，正因為這樣它才受到大多數(shù)人的喜歡。有人把它比喻成璀璨的明珠，光芒
四射，魅力無窮，稱為求和之王也不為過。
通用公式
計數(shù)：
=SUMPRODUCT((條件1)*(條件2)*(條件3)*…*(
條件N))
求和：
=SUMPRODUCT((條件1)*(條件2)*(條件3)*…*
求和區(qū)域)
如圖4-63 所示，這是IT部落窩隨機抽查
的人員資料表，下面通過10個小例子來說明
條件計數(shù)、求和的用法。
圖4-63 IT部落窩隨機抽查的人員資料表
計數(shù)
例子1：女性有幾個人？
=SUMPRODUCT(--(C4:C22=＂女＂))
例子2：潛水時間大于15天的男人有多少？
=SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂))
88
第 4 章 最受歡迎的函數(shù)與公式
例子3：2月份發(fā)言的男人有多少？
=SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂))
這里涉及一個新函數(shù)MONTH，其作用就是將日期轉(zhuǎn)換成月份。相關(guān)聯(lián)的函數(shù)還有
YEAR，其作用是將日期轉(zhuǎn)換成年，DAY函數(shù)可將日期轉(zhuǎn)換成日。
例子4：不包括“笑看今朝”的男人有幾個？
=SUMPRODUCT((A4:A22<>＂笑看今朝＂)*(C4:C22=＂男＂))
< >（不等于）屬于比較運算符，還有=（等于），>（大于），<（小于），>=（大 于等于）
和 < =（小于等于），跟數(shù)學(xué)的表示方法略有差別，但作用一樣。
求和
例子5：女性潛水總天數(shù)。
=SUMPRODUCT((C4:C22=＂女＂)*E4:E22)
例子6：潛水時間大于15天的男性的潛水天數(shù)。
=SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂)*E4:E22)
例子7：2月份發(fā)言的男性的潛水天數(shù)。
=SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂)*E4:E22)
例子8：QQ號首位是8的人的潛水天數(shù)。
=SUMPRODUCT((LEFT(B4:B22)=＂8＂)*E4:E22)
LEFT的語法：LEFT(文本,N)，提取左邊的N位文本，省略第二參數(shù)，就是提取1位。
例子9：姓名字符數(shù)為2，不包括“月亮”的人的潛水天數(shù)。
=SUMPRODUCT((LEN(A4:A22)=2)*(A4:A22<>＂月亮＂)*E4:E22)
LEN的語法：LEN(字符)，統(tǒng)計字符個數(shù)，漢字、字母、數(shù)字都是一個字符；LENB(字符
)，統(tǒng)計字節(jié)個數(shù)，漢字兩個字節(jié)，字母、數(shù)字為一個字節(jié)。
例子10：“笑看今朝”和“冷逸”的潛水天數(shù)。
=SUMPRODUCT(((A4:A22=＂笑看今朝＂) (A4:A22=＂冷逸＂))*E4:E22)
在這里是“或”的意思，只要滿足其中一個條件就行，它有時可以替代 OR的功能，如
=IF(OR(A4=＂笑看今朝＂,A4=＂冷逸＂),1,0)等同于=IF((A4=＂笑看今朝＂) (A4=＂冷逸＂),1,0)，
但OR不能替代 在數(shù)組中的用法，切記！
89
Excel效率手冊——早做完，不加班
簡化
=SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22)
公式剖析，老辦法，先轉(zhuǎn)換成單元格比較。
A5={＂笑看今朝＂,＂冷逸＂}，一個單元格跟兩個值同時比較，滿足就顯示TRUE，否則顯
示FALSE。
A5={＂笑看今朝＂,＂冷逸＂}，按F9鍵得到{TRUE,FALSE}。
({TRUE,FALSE})*E5，按F9鍵得到{6,0}。也就是說，只要單元格滿足其中一個值，就一
定會得到由0跟單元格本身組成的常量數(shù)組，完全不滿足就顯示{0,0}。因為單元格不可能同時
滿足兩個條件，所以不會出現(xiàn){6,6}這種情況。
=SUMPRODUCT((A5={＂笑看今朝＂,＂冷逸＂})*E5)
抹黑得到：
=SUMPRODUCT({6,0})
同理：
=SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22)
抹黑得到：
=SUMPRODUCT({0,0;6,0;0,5;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0})
這里就不再進(jìn)行解釋了，留點空間給大家思考。
有SUM作為鋪墊，理解SUMPRODUCT會異常簡單。今天就到此結(jié)束，有疑問可以反饋出來。
網(wǎng)友：謝謝，回去好好消化一下。
4.2.3 既生SUMIF（COUNTIF），何生SUMPRODUCT
網(wǎng)友：SUMPRODUCT函數(shù)太好用了，導(dǎo)致現(xiàn)在Excel專門的條件求和、計數(shù)函數(shù)SUMIF(COUNTIF)都不
去使用了。
盧子：一部《三國演義》讓大家把諸葛亮當(dāng)神看待——神機妙算，無所不能。以至于周瑜感嘆“既生
瑜，何生亮”。其實周瑜也是一個很有才華的人，只是被掩蓋了。
扯遠(yuǎn)了，回到正題。
SUMIF(COUNTIF) 其實也很好用，有好事者測試了SUMIFS跟SUMPRODUCT多條件求和
統(tǒng)計速度，前者是后者的三倍。那SUMIF單條件統(tǒng)計速度比SUMPRODUCT快一點還是可以肯
定的。不過對你我來說，可以忽略這個速度的問題。
90
第 4 章 最受歡迎的函數(shù)與公式
先來了解一下 COUNTIF 函數(shù)。怎么
學(xué)函數(shù)，還是老話按 F1鍵調(diào)出幫助。不
要對每次的重復(fù)操作厭倦，幫助可以給
我們提供很多有用的信息。如圖4-64 所
示，這是COUNTIF 函數(shù)的幫助。
通俗點的語法：
=COUNTIF(條件區(qū)域,條件)
統(tǒng)計區(qū)域滿足條件的個數(shù)。
圖4-64 COUNTIF幫助
下面通過幾個小例子來說明COUNTIF
的用法。如圖4-65所示，這是2006年電腦
配件銷售一覽表。
例子1：數(shù)量大于30的有幾個？
=COUNTIF(D4:D22,＂>30＂)
例子2：營業(yè)部中含“河”字的有幾個？
=COUNTIF(A4:A22,＂*河*＂)
通配符的說明：*代表所有字符，？
代表一個字符。如果需要統(tǒng)計營業(yè)部中的
兩個字符，且“河”字在最后面，可以這
么寫公式：
=COUNTIF(A4:A22,＂河＂)
圖4-65 2006年電腦配件銷售一覽表
91
Excel效率手冊——早做完，不加班
例子3：在商品列中是否有鍵盤？
=IF(COUNTIF(B4:B22,＂鍵盤＂)>0,＂存在＂,＂不存在＂)
如果存在鍵盤COUNTIF統(tǒng)計出來的次數(shù)大于0；否則等于0；公式可以稍做簡化。
=IF(COUNTIF(B4:B22,＂鍵盤＂),＂存在＂,＂不存在＂)
網(wǎng)友：>0這部分怎么可以省略？這是什么原理？
盧子：一起來看看下面幾個判斷。
=IF(3,＂存在＂,＂不存在＂)，返回存在；
=IF(-3,＂存在＂,＂不存在＂)，返回存在；
=IF(0,＂存在＂,＂不存在＂)，返回不存在。
也就是說任何不等于0的數(shù)字在這里都等同于TRUE，0等
同于FALSE。如果不相信，可以自己多試幾個看看。不過建議
初學(xué)者不要用簡寫，用標(biāo)準(zhǔn)寫法更有助于理解。前面幾個例子
的條件都是手寫的，其實條件可以直接引用單元格。
圖4-66
引用單元格
例子4：如圖4-66所示，統(tǒng)計每個營業(yè)部出現(xiàn)的次數(shù)。
=COUNTIF($A$4:$A$22,L4)
因為公式要下拉，為防止區(qū)域改變，所以加絕對引用。絕對引用、相對引用和混合引用，
可以通過用F4鍵切換得到。
例子5：統(tǒng)計共有幾個不重復(fù)營業(yè)部？
=SUMPRODUCT(1/COUNTIF(A4:A22,A4:A22))
=SUMPRODUCT(1/COUNTIF(區(qū)域,區(qū)域))是計算區(qū)域不重復(fù)個數(shù)的經(jīng)典公式，需要好好理
解。為了便于解讀公式，應(yīng)把區(qū)域改小，公式變成：
=SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9))
觀察
=SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9))
按F9鍵抹黑：
=SUMPRODUCT(1/{1;3;3;2;3;2})
按Esc鍵返回：
=SUMPRODUCT(1/COUNTIF(A4:A9,A4:A9))
92
第 4 章 最受歡迎的函數(shù)與公式
按F9鍵抹黑：
=SUMPRODUCT({1;0.333333333333333;0.333333333333333;0.5;0.333333333333333;0.5})
按Esc鍵返回，在單元格處按回車看到結(jié)果：3。
分析
按F9鍵觀察有時不太直觀，回到工作表中繼續(xù)看看。
=COUNTIF(A4:A9,A4:A9) 是多單元格數(shù)組，等同于
=COUNTIF($A$4:$A$9,A4)下拉的結(jié)果，也就是統(tǒng)計每個單元
格本身出現(xiàn)的次數(shù)，如1。
=1/COUNTIF(A4:A9,A4:A9) 是多單元格數(shù)組，等同于
=1/COUNTIF($A$4:$A$9,A4) 下拉的結(jié)果，也就是1/每個單元
格本身出現(xiàn)的次數(shù)。為了讓數(shù)據(jù)更直觀地轉(zhuǎn)換成分?jǐn)?shù)形式，
如2，出現(xiàn)3次就變成1/3，出現(xiàn)2次就變成1/2，出現(xiàn)1次就1。
1/3 1/3 1/3=3*(1/3)=1，1/N … 1/N=N*(1/N)=1，不管出現(xiàn)幾
次，相加都等于1，如圖4-67所示。
圖4-67
輔助理解
最后將這些分?jǐn)?shù)相加就得到不重復(fù)的數(shù)量，如3。
解讀公式的一些習(xí)慣
把 區(qū) 域 改 小， 這 樣 便 于 查 看， 如 將
A1:A1000改成A1:A3。
F9鍵配合組合鍵Ctrl Z或者Esc鍵不斷地看
運算過程再返回，重復(fù)到理解為止。
輸入公式后回到單元格查看運算過程，
這相對比較直觀。
分析
第2、第3點可選，看你對公式的熟練
程度而言，如果不熟練選擇 3，熟練的話選
擇2。
關(guān)于計數(shù)就說到這里，回頭再聊聊求和。
先來了解一下SUMIF函數(shù)的幫助，如
圖4-68所示。
圖4-68
SUMIF幫助
93
Excel效率手冊——早做完，不加班
通俗語法：
=SUMIF(條件區(qū)域,條件,求和區(qū)域)
實例以圖4-65所示的2006年電腦配件銷售一覽表為例進(jìn)行說明。
例子1：求匯總顯示器的數(shù)量。
=SUMIF(B4:B22,＂顯示器＂,D4:D22)
例子2：求數(shù)量大于30的總數(shù)量。
=SUMIF(D4:D22,＂>30＂)
第3個參數(shù)省略了，求和區(qū)域相當(dāng)于D4:D22，公式的作用等同于：
=SUMIF(D4:D22,＂>30＂,D4:D22)
例子3：求匯總數(shù)量在30至40之間的數(shù)量總和。
=SUMIF(D4:D22,＂>=30＂)-SUMIF(D4:D22,＂>40＂)
[30,無窮大)跟[40,無窮大)的交集是[40,無
窮大)，[30,無窮大)減去交集[40,無窮大)剛好
是[30,40]這個區(qū)間，所以>=30的總和減去>40
的總和就是30~40之間的總和。
網(wǎng)友：這個沒有數(shù)學(xué)基礎(chǔ)真的不好理解，看來數(shù)
圖4-69 求[30,40]區(qū)間的總和
學(xué)還是挺重要的。
盧子：很多東西都是相互借鑒的，學(xué)好數(shù)學(xué)有助于學(xué)好函數(shù)。
例子4：求最后字符為“河”且總字符為3個的營業(yè)部的總金額。
=SUMIF(A4:A22,＂河＂,F4:F22)
代表1個字符，“河”就是最后字符為“河”且是3個字符。
例子5：如圖4-70所示，數(shù)字包含錯誤值，怎么避開錯誤值求和
=SUMIF(A:A,＂<9E 307＂)
9E 307是9乘以10的307次方，相當(dāng)于Excel最大的數(shù)字，任何數(shù)字都比它小。數(shù)據(jù)的排序依
據(jù)為數(shù)字<文本<邏輯值<錯誤值，再大的數(shù)字都比不上錯誤值，所以可以避開錯誤值求和。
94
第 4 章 最受歡迎的函數(shù)與公式
例子6：如圖4-71所示，多個區(qū)域求型號等于A03的總數(shù)量。
圖4-70
數(shù)字包含錯誤值
圖4-71 多個區(qū)域求型號等于A03的總數(shù)量
=SUMIF(A2:G10,＂A03＂,B2)
=SUMIF(A2:G10,＂A03＂,B2:H10)
兩個公式的效果是一致的，SUMIF的第3個參數(shù)會自動擴展區(qū)域，但不建議簡寫，那樣會
導(dǎo)致運算速度變慢。
溫馨提示
sum_range參數(shù)與 range參數(shù)的大小和形狀可以不同。求和的實際單元格可使用 sum_range參數(shù)中左上角的單
元格作為起始單元格，然后包括與 range參數(shù)大小和形狀相對應(yīng)的單元格。但是，當(dāng) SUMIF函數(shù)中的range和 sum_
range參數(shù)不包含相同的單元格個數(shù)時，工作表重新計算需要的時間可能比預(yù)期的長。
網(wǎng)友：原來簡單不一定好，簡單是以付出效率作為代價的。
盧子：在使用函數(shù)的過程中還是使用標(biāo)準(zhǔn)用法為好，而在學(xué)習(xí)過程中多了解其他用法也好。
SUMIF跟COUNTIF函數(shù)有點相似，只要理解一個，要了解另一個就簡單了。多條件求和
SUMIFS跟COUNTIFS也比較常見，有興趣的話可以了解一下。
4.2.4 無處不在的IF
盧子：只要留心觀察，生活中到處充滿IF，我們每天都在跟IF打交道。
如果明天下暴雨，我就不去上班。
如果有你陪在我身邊，我會很開心。
如果有網(wǎng)絡(luò)，我就上網(wǎng)，否則睡覺。
如果你來了，請你喝功夫茶，否則我自己喝。
