學(xué)數(shù)組也有段時間，但盧子始終在數(shù)組的門外徘徊。聽到Ctrl+Shift+Enter三鍵猶如老鼠看到貓，但心中又存在幻想，希望有朝一日神人出現(xiàn)或者天降寶典，那時盧子就能夠輕松學(xué)好數(shù)組。但始終沒等到這一天，后來盧子借用玩游戲的時間來學(xué)習(xí)數(shù)組公式，學(xué)了很久，忽然有一天發(fā)覺會了好多，真是意外的驚喜。
SUM函數(shù)看似簡單，實則蘊含高深用法。什么是高手？高手就是能將最平常的函數(shù)變幻出神奇莫測的功能，解決掉你想都不敢想的問題。
Q：如果我們要求1-10的和怎么辦？
A：可以通過輔助列，在A1-A10分別輸入1-10，然后用SUM函數(shù)求和，話說這個99%的人都會，如果你屬于那1%的群體，那恭喜你，你可以關(guān)閉這篇文章，該干嘛干嘛去。
=SUM(A1:A10)
不過如果數(shù)據(jù)龐大的話，如輸入1-10000，用這辦法恐怕行不通，你光輸入就得輸入到哭。那這時就可以通過ROW函數(shù)來生成1-N這樣的序號。
=SUM(ROW(1:10000))
這時習(xí)慣了普通公式的人，一回車就會發(fā)現(xiàn)問題，怎么才1呢？
剛開始學(xué)習(xí)數(shù)組公式的時候，盧子就經(jīng)常犯這種低級錯誤。記住了，數(shù)組公式需要按Ctrl+Shift+Enter三鍵結(jié)束才可以，否則出錯。三鍵一按，立馬返回50005000，可以用數(shù)學(xué)速算法驗證下，答案是正確的。
=(10000+1)*10000/2
這個公式雖好，但假如有某個人誤操作你的表格，將其中兩行刪除，這時你就傻眼了，公式自己會變。
看來這個公式并不完美，仍需改善，這時INDIRECT函數(shù)就派上用場
=SUM(ROW(INDIRECT("1:10000")))
不管你怎么刪除行，效果始終不變。
加上INDIRECT函數(shù)就相當(dāng)于引用一個固定的1:10000，而不用借助單元格引用，直接用ROW(1:10000)其實還是要借助單元格才行實現(xiàn)。
現(xiàn)在不討論這種萬一的情況，繼續(xù)聊新的話題。
Q：如果要求1-10奇數(shù)跟偶數(shù)又該如何？
A：其實我們可以利用MOD（數(shù)據(jù),2），如果是奇數(shù)就是1，偶數(shù)就是0這個特點來處理。
奇數(shù)：
=SUM(MOD(ROW(1:10),2)*ROW(1:10))
偶數(shù)：
=SUM((MOD(ROW(1:10),2)=0)*ROW(1:10))
=SUM(MOD(ROW(1:10)-1,2)*ROW(1:10))
Q：如果要求1-10的最大，小5個數(shù)的和要怎么做呢？
A：前幾大可以用LARGE函數(shù)，前幾小可以用SMALL函數(shù)。
前5大的和：
=SUM(LARGE(ROW(1:10),ROW(1:5)))
前5小的和：
=SUM(SMALL(ROW(1:10),ROW(1:5)))
LARGE跟SMALL函數(shù)的語法其實一樣，學(xué)一個就行。一起來看看LARGE函數(shù)的語法：
=LAGER（數(shù)字,N）
求A列最大跟第2大的數(shù)字，最大就是N是1，第2大就是N是2，依次類推。
SUM函數(shù)誰都知道可以求和，但他還可以取代SUMIF、SUMIFS函數(shù)實現(xiàn)條件求和，你知道嗎？當(dāng)初就是被這一用法深深吸引，覺得SUM函數(shù)神奇，盧子才不知不覺喜歡上數(shù)組。閑話少說，進入主題。
某學(xué)校各人員成績，現(xiàn)在需要統(tǒng)計各學(xué)部的總成績？
正常的話用SUMIF函數(shù)就可以搞定，SUMIF就是條件求和的意思。
=SUMIF(E:E,G2,D:D)
其實我們可以將SUMIF拆分成兩個函數(shù)SUM+IF，先進行條件判斷，然后再求和。
以G2的學(xué)部財經(jīng)為例，需要判斷的條件就是E列學(xué)部是不是等于財經(jīng)，如果是就返回成績，不是就顯示0。選擇區(qū)域在K2:K16輸入公式，然后按Ctrl+Shift+Enter結(jié)束。可以清楚得看到所有符合財經(jīng)的都返回本身的成績，不是財經(jīng)的都返回0。
=IF(E2:E16=G2,D2:D16,0)
IF函數(shù)的區(qū)域判斷跟單元格單獨判斷是一樣的，其實這個公式等同于下面公式下拉的結(jié)果。唯一的差別在于，一個是選擇區(qū)域按三鍵結(jié)束，一個是下拉獲取結(jié)果而已。
=IF(E2=$G$2,D2,0)
到這一步已經(jīng)完成了90%的工作，只需在最外面嵌套個SUM函數(shù)進行求和即可。
=SUM(IF($E$2:$E$16=G2,$D$2:$D$16))
后面的所有數(shù)組公式都是需要按三鍵結(jié)束，在這里強調(diào)一下，為了方便說明，不再重復(fù)。
照著這個思路，看看SUM函數(shù)如何取代SUMIFS函數(shù)。現(xiàn)在增加了一個條件性別，變成多條件求和。
我們都知道在普通公式里面AND就表示并且的意思，按常規(guī)想法已經(jīng)是這樣設(shè)置公式
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
輸入公式后的結(jié)果讓人大跌眼鏡，居然全部都是0。
這時用常規(guī)公式，發(fā)現(xiàn)并沒有異常，怎么回事呢？
=IF(AND(E2=$G$2,C2=$H$2),D2,0)
這時有“獨孤九劍”之稱的F9鍵就派上用場。
在編輯欄選擇AND部分，這時就會出現(xiàn)抹黑現(xiàn)象，然后F9鍵，得到FALSE，只有一個結(jié)果，也就是說只要有任何一個條件不滿足就是FALSE，這樣一來肯定有問題。
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
必須得到15個結(jié)果才行。那有沒有其他函數(shù)可以取代AND函數(shù)，答案是肯定的，那就是*。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
現(xiàn)在用F9鍵同樣對條件進行測試。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
得到這樣的結(jié)果：
=IF({1;0;0;1;0;0;0;1;0;0;0;0;0;0;0},D2:D16,0)
也就是說同時滿足兩個條件的返回1，否則返回0。1就是TRUE所以返回D列對應(yīng)值，0就是FALSE所以返回0。如果對F9鍵還不熟練，沒關(guān)系，繼續(xù)回到單元格內(nèi)進行測試，跟我們的判斷一致。
現(xiàn)在只需在最外面嵌套SUM函數(shù)即可。
=SUM(IF(($E$2:$E$16=G2)*($C$2:$C$16=H2),$D$2:$D$16))
通過*知道可以將各個條件連接起來，其實求和區(qū)域也可以看成一個條件，將公式變成：
=SUM(($E$2:$E$16=G2)*($C$2:$C$16=H2)*$D$2:$D$16)
這樣SUM函數(shù)條件求和的通用公式就出來了。
=SUM((條件1)*(條件2)*(條件3)*…*求和區(qū)域)
SUM函數(shù)不僅僅可以實現(xiàn)條件求和，還可以進行各種各樣的求和，下面通過一些例子來見識下。
數(shù)量是用VLOOKUP函數(shù)查詢引用過來，找不到對應(yīng)值就顯示#N/A，如果直接求和的話出錯，該如何處理？
=SUM(A2:A11)
解決方案：
01 加一個條件來判斷數(shù)量是不是數(shù)字，就可以搞定。
=SUM(IF(ISNUMBER(A2:A11),A2:A11))
ISNUMBER函數(shù)只有一個參數(shù)，作用就是判定單元格是不是數(shù)字，如果是返回TRUE，否則返回FALSE。
IS類函數(shù)還有好多個，語法都差不多，有興趣的可以了解下。將鼠標(biāo)放在函數(shù)上面就有出現(xiàn)一些說明。
02 借助高版本函數(shù)進行容錯，再求和會更簡單。
=SUM(IFERROR(A2:A11,0))
=SUM(IFNA(A2:A11,0))
這兩個函數(shù)的用法前面說過，這里就不再重復(fù)解釋。
統(tǒng)計各季度的銷售額。
=SUM(IF(ISNUMBER(FIND("季度",A2:A17)),B2:B17))
SUM函數(shù)不能直接用*，但FIND函數(shù)也可以實現(xiàn)查找季度這2個字符是不是存在，如果存在就返回字符所在位置，否則返回錯誤值。
ISNUMBER函數(shù)判斷是否是數(shù)字，也就是包含季度，如果是返回B列的銷售額。
當(dāng)然這里只是為了說明用法，因為數(shù)據(jù)源本身就很有規(guī)律，每個季度都等于3個月的合計，所以也可以采取取巧法。
=SUM(B2:B17)/2
截止到目前SUM函數(shù)僅僅是取代SUMIF跟SUMIFS，還不能真正體現(xiàn)出價值，后面看看他如何實現(xiàn)別人做不到的事兒。
含有單位的人員銷售清單，直接求和得不出正確答案，怎么才能讓含單位的銷售額可以求和呢？
幫助提到，SUM函數(shù)會自動忽略文本，600元這種就是文本，不屬于數(shù)字。最簡單的做法就是將元替換成空，然后自定義單元格格式G/通用格式"元"。很多人就是搞不明白一格一屬性的道理，才會造成匯總數(shù)據(jù)困難重重。正確的做法應(yīng)該將元寫在字段名那里變成銷售額（元），這樣別人一看便知道。廢話了這么多，進入正題，別見怪，只是有感而發(fā)而已。
剛才提到了替換這個詞，函數(shù)中也有屬于自己的替換函數(shù)，SUBSTITUTE函數(shù)。
SUBSTITUTE第四參數(shù)為可選，那就先別管他，其他參數(shù)可以理解為：
=SUBSTITUTE(文本,需要替換的舊字符,替換成新的字符)
單元格的元是多余的，需要替換成空，空可以用""表示，替換成空后直接求和，可以嗎，不驗證猜一下？
=SUM(SUBSTITUTE(B2:B7,"元",""))
這個是數(shù)組公式，用法也跟前面差不多，目測應(yīng)該可以匯總。
但實際SUBSTITUTE這個函數(shù)屬于文本函數(shù)，所以替換得到的數(shù)字，也屬于文本，在這里叫做文本數(shù)字。數(shù)字有兩種類型，一種是文本數(shù)字，一種是真正的數(shù)字，即數(shù)值。數(shù)值是可以求和，而文本不能求和。如：賬簿上的數(shù)字跟墻上的數(shù)字是不同的，前者我們可以用這些數(shù)字進行各種分析，后者只能當(dāng)欣賞用。那有什么辦法還原數(shù)字的本質(zhì)呢？
利用VALUE函數(shù)可以將文本型轉(zhuǎn)換成數(shù)值型。
=VALUE("600")
但一般情況下不會用這一種，而是通過運算轉(zhuǎn)換。
一起來了解“減負”運算。
在函數(shù)或公式中，運算過程會自動把文本轉(zhuǎn)換為數(shù)值(一個隱含過程)，再與數(shù)值進行運算，負值運算(-)也是一種運算，能把文本轉(zhuǎn)換成數(shù)值：
-"600"=-600
還記得負負得正吧？
-(-"600")=-(-600)=600
簡寫為：
--"600"=600
--可以把文本轉(zhuǎn)換為數(shù)值，但它不是標(biāo)準(zhǔn)的轉(zhuǎn)換方式，是借用負運算的隱含功能。
其實負負運算稱為減負運算更好，減去數(shù)字的負擔(dān)，還原數(shù)字的本質(zhì)。
=SUM(--SUBSTITUTE(B2:B7,"元",""))
將這一部分用F9鍵抹黑，得到：
=SUM({600;120;1000;210;129;123})
這樣就能夠求和了。
綜上，最終的數(shù)組公式為：
=SUM(--SUBSTITUTE(B2:B7,"元",""))
如果對字符提取三兄弟有印象的話，還可以這樣設(shè)置公式，因為都是包含元，也可以利用總字符數(shù)-1這個特點來做
=SUM(--LEFT(B2:B7,LEN(B2:B7)-1))
最后再來一個高段黑的，錄入金額的時候，居然把姓名也寫在一起。
看到這個，盧子都頭大，奉勸大家一句，別把Excel當(dāng)Word使用，否則后果很嚴(yán)重。
不過即使困難重重，SUM函數(shù)也能一一拿下，沒有求不了和的數(shù)據(jù)，只有求不了和的人。
我們知道LEN函數(shù)可以統(tǒng)計字符數(shù)，其實他還有一個兄弟叫LENB函數(shù)，他是統(tǒng)計字節(jié)數(shù)。漢字2字節(jié)，數(shù)字1字節(jié)。利用這個特點我們可以得出：
漢字的個數(shù)：
=LENB-LEN
數(shù)字的個數(shù)：
=LEN-漢字的個數(shù)=LEN-(LENB-LEN)=2*LEN-LENB
最終公式：
=SUM(--RIGHT(A2:A5,2*LEN(A2:A5)-LENB(A2:A5)))
SUM函數(shù)能實現(xiàn)各種各樣求和那也是情理之中，如果說SUM函數(shù)能取代COUNTIF、COUNTIFS函數(shù)實現(xiàn)條件計數(shù)，也許很多人會覺得天方夜譚。SUM函數(shù)強大得難以想象，很多人掌握的知識只是冰山一角。
提取姓名中的第一個字符也就是姓氏，可以用LEFT函數(shù)。
=LEFT(A2,1)
第二參數(shù)省略不寫的話，默認(rèn)就是提取1位，所以可簡化為：
=LEFT(A2)
結(jié)合前面SUM+IF的用法可用：
=SUM(IF(LEFT(A$2:A$12)=D2,1))
讓符合條件的顯示1，然后嵌套SUM進行求和。
統(tǒng)計各學(xué)部男女的人數(shù)。
利用SUM+IF的做法，依樣畫葫蘆也能做到，但這里不做說明。大家是否還記得SUM函數(shù)求和的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*求和區(qū)域)
其實省略掉求和區(qū)域就是條件計數(shù)的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*(條件n))
有了這個通用公式，條件計數(shù)so easy！
=SUM(($E$2:$E$16=$G2)*($C$2:$C$16=H$1))
計數(shù)還有一個經(jīng)典的問題，就是不重復(fù)計數(shù)。統(tǒng)計不重復(fù)學(xué)部的個數(shù)。
計算區(qū)域不重復(fù)個數(shù)的經(jīng)典公式，需要好好理解。
=SUM(1/COUNTIF(區(qū)域,區(qū)域))
=SUM(1/COUNTIF(E2:E16,E2:E16))
如果區(qū)域很多的話，可以改小，這樣便于理解。
觀察
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM(1/{4;4;3;4;4;4;4;4;4;4;4;4;3;4;3})
Esc鍵返回
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM({0.25;0.25;0.333333333333333;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.333333333333333;0.25;0.333333333333333})
Esc鍵返回，在單元格按三建結(jié)束看到結(jié)果：4。
分析
F9鍵觀察有時并不太直觀，回到工作表中繼續(xù)看看。
=COUNTIF(E2:E16,E2:E16)是多單元格數(shù)組，等同于=COUNTIF(E$2:E$16,E2)下拉的結(jié)果，也就是統(tǒng)計每個單元格本身出現(xiàn)的次數(shù)，如1。
=1/COUNTIF(E2:E16,E2:E16)是多單元格數(shù)組，等同于=1/ COUNTIF(E$2:E$16,E2)下拉的結(jié)果，也就是1/每個單元格本身出現(xiàn)的次數(shù)，為了讓數(shù)據(jù)更直觀轉(zhuǎn)換成分?jǐn)?shù)形式，如2。
出現(xiàn)4次就變成1/4，出現(xiàn)3次就變成1/3。1/3+1/3+1/3=3*(1/3)=1，1/N+…+1/N=N*(1/N)=1，不管出現(xiàn)幾次，相加都等于1。
最后將這些相加就是得到不重復(fù)的數(shù)量，如3。
解讀公式的一些習(xí)慣：
1. 把區(qū)域改小，這樣便于查看，如A1:A1000改成A1:A9。
2. F9鍵配合Ctrl+Z或者Esc鍵不斷地看運算過程再返回，重復(fù)到理解為止。
3. 輸入公式回到單元格查看運算過程，這種相對比較直觀。
4. 分析。
第2，3點可選，看你對公式的熟練程度，如果不熟練選擇3，熟練的話選擇2。
學(xué)好SUM函數(shù)不過是為了打開數(shù)組之門，知道數(shù)組的一些基礎(chǔ)用法，要學(xué)好數(shù)組還需要更多的知識支撐才可以。
推薦：每個Excel學(xué)得好的人都有一段不為人知的往事，我有故事，你想聽嗎
上篇：驚訝！簡單的Excel圖標(biāo)集，居然難倒50%的人
每個人都是從不懂到懂，多花點時間和精力，用心學(xué)習(xí)，你也可以學(xué)得很好。嘗試從學(xué)習(xí)中找到樂趣，培養(yǎng)自己的興趣愛好，這樣會學(xué)得更快。
在學(xué)習(xí)Excel的過程中，你有沒發(fā)現(xiàn)什么有趣的事兒？
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創(chuàng)始人，個人公眾號：Excel不加班（ID：Excelbujiaban）
