「Excel最強(qiáng)大的函數(shù)之一Subtotal函數(shù)詳解」如果你經(jīng)常和表格打交道一定遇到過這樣的尷尬用SUM函數(shù)對(duì)一列數(shù)據(jù)求和結(jié)果篩選掉幾個(gè)部門之后合計(jì)數(shù)字紋絲不動(dòng)或者辛辛苦苦把某些行隱藏起來做臨時(shí)統(tǒng)計(jì)求和結(jié)果卻把隱藏?cái)?shù)據(jù)統(tǒng)統(tǒng)算進(jìn)去了。我第一次在月底報(bào)表里被這種問題坑的時(shí)候整個(gè)人是崩潰的——數(shù)字對(duì)不上領(lǐng)導(dǎo)在旁邊等又不知道哪個(gè)環(huán)節(jié)出了問題。后來我才意識(shí)到該用的根本不是SUM而是那個(gè)看起來默默無聞的Subtotal。Subtotal絕對(duì)是Excel里最被低估、也最強(qiáng)大的函數(shù)之一。它的本職工作是計(jì)算匯總值但遠(yuǎn)遠(yuǎn)不止求和這么簡(jiǎn)單。它能根據(jù)你的篩選或隱藏狀態(tài)動(dòng)態(tài)改變統(tǒng)計(jì)結(jié)果還能在套娃計(jì)算時(shí)自動(dòng)跳過其他Subtotal生成的匯總行避免重復(fù)計(jì)數(shù)。無論你是做財(cái)務(wù)、電商運(yùn)營(yíng)、HR數(shù)據(jù)分析還是日常做統(tǒng)計(jì)報(bào)表只要需要跟著篩選走的匯總Subtotal就是那個(gè)最靠譜的選擇。這篇文章我會(huì)把這套東西徹底講透從它的語法結(jié)構(gòu)、那兩組反直覺的數(shù)字參數(shù)1到11以及101到111到嵌套屏蔽機(jī)制和常見坑位?？赐昴憔湍茉陧?xiàng)目報(bào)表、數(shù)據(jù)看板里靈活使用不再被隱藏行和篩選狀態(tài)反復(fù)折磨。1. 一段讓人崩潰的篩選求和經(jīng)歷Subtotal到底解決了個(gè)什么問題先講個(gè)我早期做業(yè)績(jī)報(bào)表時(shí)的真實(shí)場(chǎng)景你大概率也遇到過。表格里有三個(gè)月各門店的銷售明細(xì)約2000多行。月底我需要給管理層出一份匯總先按區(qū)域篩選出華東區(qū)想看看華東區(qū)總量再篩選出華東區(qū)的門店A看看這個(gè)門店的量。當(dāng)時(shí)我用的是最經(jīng)典的SUM(C2:C1000)公式放在匯總單元格里結(jié)果呢?zé)o論我篩選哪一個(gè)區(qū)域、哪一家門店這個(gè)SUM的結(jié)果都是全公司的總銷售——因?yàn)镾UM不懂篩選它只認(rèn)引用范圍內(nèi)的全部單元格哪怕那一行在界面上看不到了它也會(huì)照常納入計(jì)算。當(dāng)時(shí)我不明白這回事兒還以為是Excel出了問題甚至懷疑是某個(gè)單元格格式損壞了。折騰了好一會(huì)兒才發(fā)現(xiàn)問題出在我選錯(cuò)了函數(shù)。翻到函數(shù)列表試著換了SUBTOTAL(9,C2:C1000)篩選結(jié)果瞬間對(duì)了。篩選華東區(qū)它就顯示華東區(qū)合計(jì)篩選門店A它就顯示門店A合計(jì)連篩選銷量前10這種條件都沒問題——匯總數(shù)會(huì)乖乖跟著可見行走。這正是Subtotal最核心的能力它會(huì)根據(jù)你當(dāng)前的篩選Filter狀態(tài)去重算可見單元格。這一下子就解決了報(bào)表匯總里最頻繁的一類需求——篩什么就合計(jì)什么。除了篩選手動(dòng)隱藏行它也管。參數(shù)不同它要么無視隱藏行要么老老實(shí)實(shí)拆分成兩種行為模式這個(gè)后面細(xì)說。這個(gè)經(jīng)歷給我最大的教訓(xùn)是日常辦公里的很多奇怪問題根子往往不是Excel壞了而是你沒有找到合適的那把鑰匙。SUM適合做總賬Subtotal才是動(dòng)態(tài)匯總的那把鑰匙。2. 單函數(shù)十二種用途Subtotal的瑞士軍刀式參數(shù)結(jié)構(gòu)我敢說很多人對(duì)Subtotal望而卻步是因?yàn)榈谝淮慰吹剿恼Z法時(shí)被那一串?dāng)?shù)字編號(hào)搞得有點(diǎn)懵。實(shí)際上它一點(diǎn)都不復(fù)雜。函數(shù)基本結(jié)構(gòu)是這樣的SUBTOTAL(功能編號(hào), 引用區(qū)域1, [引用區(qū)域2], ...)第一個(gè)參數(shù)是功能編號(hào)它決定你要做什么類型的匯總比如求和、計(jì)數(shù)、平均值、最大值等。第二個(gè)參數(shù)開始是你想引用的數(shù)據(jù)范圍可以寫一個(gè)區(qū)域也可以寫多個(gè)區(qū)域。注意它引用區(qū)域的方式和SUM很不一樣SUM你可以把一整片區(qū)域直接拖進(jìn)去Subtotal也支持這種操作但如果你的數(shù)據(jù)分散在多個(gè)不連續(xù)的區(qū)域比如SUBTOTAL(9,C2:C10,C20:C30)這種多區(qū)域引用也是允許的。不過需要提醒的是Subtotal在處理三維引用或者跨多個(gè)Sheet的同位置單元格時(shí)會(huì)受限這是它和SUM的另一個(gè)區(qū)別實(shí)操時(shí)不要混用。那功能編號(hào)那串?dāng)?shù)字怎么記別硬背先理解Excel把編號(hào)分成了兩組。功能編號(hào)含隱藏值功能功能編號(hào)忽略隱藏值功能1平均值 AVERAGE101平均值 AVERAGE忽略隱藏行2計(jì)數(shù) COUNT僅數(shù)字102計(jì)數(shù) COUNT忽略隱藏行3計(jì)數(shù) COUNTA非空103計(jì)數(shù) COUNTA忽略隱藏行4最大值 MAX104最大值 MAX忽略隱藏行5最小值 MIN105最小值 MIN忽略隱藏行6乘積 PRODUCT106乘積 PRODUCT忽略隱藏行7樣本標(biāo)準(zhǔn)差 STDEV107樣本標(biāo)準(zhǔn)差 STDEV忽略隱藏行8總體標(biāo)準(zhǔn)差 STDEVP108總體標(biāo)準(zhǔn)差 STDEVP忽略隱藏行9求和 SUM109求和 SUM忽略隱藏行10樣本方差 VAR110樣本方差 VAR忽略隱藏行11總體方差 VARP111總體方差 VARP忽略隱藏行我第一次看到這個(gè)表的時(shí)候也是有點(diǎn)打怵的但拆開看就清楚了編號(hào)從1到11對(duì)應(yīng)的功能恰好覆蓋了Excel最常用的十一類統(tǒng)計(jì)運(yùn)算最常用的就是1平均值、2計(jì)數(shù)、3非空計(jì)數(shù)、9求和。編號(hào)從101到111和前面一一對(duì)應(yīng)的功能完全相同唯一的區(qū)別就是——這幾組編號(hào)會(huì)主動(dòng)忽略手動(dòng)隱藏的行。換句話說你不用學(xué)一百個(gè)函數(shù)只要記住一個(gè)Subtotal然后通過換編號(hào)就能搞定從求和到方差的一整套統(tǒng)計(jì)需求。這里倒是有個(gè)很關(guān)鍵的小細(xì)節(jié)可能你一直沒注意編號(hào)1到11并非包含隱藏行的萬能版。當(dāng)你的表格處于篩選狀態(tài)時(shí)編號(hào)1到11同樣忽略篩選掉的不可見行。它和101到111的真正分歧只出在手動(dòng)隱藏行這個(gè)動(dòng)作上。打個(gè)比方篩選就像給數(shù)據(jù)蓋了一層簾子簾子后的行編號(hào)1到11和101到111都會(huì)忽略手動(dòng)隱藏行則更像把某幾行從房間移走1到11會(huì)當(dāng)作它們還在101到111則會(huì)當(dāng)作它們不存在。用哪個(gè)我的習(xí)慣是如果只是對(duì)篩選結(jié)果做匯總用9就夠了如果表格里有手動(dòng)隱藏行且不希望它們參與計(jì)算直接上109不要給自己留后患。這里的安全性判斷原則很簡(jiǎn)單——拿不準(zhǔn)就用101以上的編號(hào)因?yàn)楹雎允謩?dòng)隱藏行這個(gè)特性在絕大多數(shù)日常場(chǎng)景里都是我們想要的行為。3. 一組參數(shù)帶來的靈異事件隱藏行到底算不算數(shù)上一步已經(jīng)提到了1到11和101到111的核心差別但實(shí)際操作中這個(gè)差別經(jīng)常會(huì)制造出一些靈異事件我單獨(dú)拿出來講一下因?yàn)檫@是Subtotal最大的坑位也是最多人用錯(cuò)的地方。先看一個(gè)具體例子。還是那張銷售表假設(shè)C列是銷售金額一共10行數(shù)據(jù)C2:C11 {100, 200, 300, 400, 500, 600, 700, 800, 900, 1000}如果你在C12輸入SUBTOTAL(9, C2:C11)結(jié)果會(huì)是5500也就是全部數(shù)據(jù)之和?，F(xiàn)在我把第5行到第7行手動(dòng)隱藏也就是400、500、600這三個(gè)數(shù)據(jù)所在的行。注意我用的是隱藏行不是篩選。此時(shí)再看SUBTOTAL(9, C2:C11)的結(jié)果仍然是5500因?yàn)榫幪?hào)9不理會(huì)手動(dòng)隱藏的行400、500、600依然被算進(jìn)去了。SUBTOTAL(109, C2:C11)的結(jié)果會(huì)變成4900因?yàn)榫幪?hào)109會(huì)自動(dòng)跳過三個(gè)隱藏行只對(duì)可見的1002003007008009001000求和。這正是靈異事件的源頭表格上看到的可見數(shù)字加起來明明是4900為什么那個(gè)Subtotal(9)還是5500很多人在這一步會(huì)懷疑Excel瘋了其實(shí)函數(shù)沒瘋是你給它傳達(dá)了一個(gè)錯(cuò)誤的指令。這里還有個(gè)更隱蔽的細(xì)節(jié)我們經(jīng)常在做小計(jì)-總計(jì)結(jié)構(gòu)的表時(shí)用到隱藏行。假設(shè)我有這樣一張表區(qū)域銷售額華東1000小計(jì)1000華南2000小計(jì)2000總計(jì)3000如果我把小計(jì)行全部隱藏掉想單看各區(qū)域明細(xì)行分別的合計(jì)結(jié)果會(huì)怎樣SUBTOTAL(9, 銷售額區(qū)域)會(huì)把隱藏的小計(jì)行也包括進(jìn)去重復(fù)計(jì)算。SUBTOTAL(109, 銷售額區(qū)域)則會(huì)正確忽略隱藏的小計(jì)行得到明細(xì)行的真實(shí)合計(jì)。這個(gè)場(chǎng)景在財(cái)務(wù)和數(shù)據(jù)分析中極其常見。所以我一般在設(shè)計(jì)模板時(shí)凡是做了分部小計(jì)、最后總計(jì)結(jié)構(gòu)的表格匯總單元格一律用109開頭的編號(hào)寧可用不上也不能讓它悄悄把隱藏行重復(fù)算進(jìn)去。順帶說一個(gè)輔助技巧因?yàn)殡[藏行是Subtotal的最主要變量你可以順手在表格左側(cè)加一組分組按鈕Excel的組合功能快捷鍵AltShift→對(duì)部分行做分組折疊。這樣既能保留隱藏行的語義也能借助Subtotal的101系編號(hào)做到折疊起來時(shí)自動(dòng)換一套統(tǒng)計(jì)口徑。折疊時(shí)是明細(xì)匯總展開時(shí)是含小計(jì)的整體匯總一份表格兩套口徑這招在經(jīng)營(yíng)分析報(bào)表里非常實(shí)用。4. 為什么用Subtotal嵌套Subtotal不會(huì)重復(fù)計(jì)算Subtotal還有一個(gè)獨(dú)門技能它會(huì)自動(dòng)屏蔽其他Subtotal的結(jié)果。這個(gè)特性在做分類匯總時(shí)簡(jiǎn)直是救命級(jí)別的存在。我用一個(gè)實(shí)際例子解釋。假設(shè)你有個(gè)商品銷售流水表結(jié)構(gòu)是A列日期、B列區(qū)域、C列銷售額?，F(xiàn)在你想按區(qū)域做一次匯總?cè)缓笤賹?duì)整個(gè)表做一次總計(jì)你可能會(huì)先在表格下方用SUM函數(shù)寫好各區(qū)域的小計(jì)再用SUM對(duì)所有小計(jì)求和得出總計(jì)。這種做法的問題在于如果哪天你在表格里加了新行又或者你的小計(jì)本身就是用Subtotal生成的后續(xù)再用SUM去套邏輯會(huì)越繞越亂。而Subtotal的做法是各區(qū)域小計(jì)行里寫SUBTOTAL(9, C2:C11)也就是每個(gè)區(qū)域可見數(shù)據(jù)之和所有區(qū)域都加完后在最下面寫一個(gè)總計(jì)時(shí)也用SUBTOTAL(9, C2:C30)——注意這里不要用SUM。你覺得這跟SUM有什么區(qū)別區(qū)別大了。如果你對(duì)C2:C30用SUMSUM會(huì)傻乎乎地把那些已經(jīng)是計(jì)算結(jié)果的小計(jì)行再全班加一遍造成重復(fù)。但如果用Subtotal它內(nèi)部有自動(dòng)識(shí)別機(jī)制當(dāng)計(jì)算范圍里出現(xiàn)其他Subtotal時(shí)它會(huì)跳過這些Subtotal的單元格只統(tǒng)計(jì)原始數(shù)據(jù)單元格所以不會(huì)重復(fù)計(jì)算。這個(gè)機(jī)制用一句話概括就是Subtotal天生知道哪些數(shù)字是別的Subtotal算出來的它會(huì)把它們排除出自己的計(jì)算范圍。這個(gè)特性在Excel自帶的分類匯總Subtotal命令里體現(xiàn)得更為淋漓盡致。當(dāng)你選中數(shù)據(jù)區(qū)域菜單欄依次點(diǎn)擊數(shù)據(jù)——分類匯總時(shí)Excel會(huì)在插入?yún)R總行的同時(shí)替你填好Subtotal函數(shù)并且自動(dòng)勾選每組一個(gè)匯總以及匯總行顯示在明細(xì)下方。這才是Subtotal的兩個(gè)最常見的自動(dòng)化用法分類匯總命令可以按某個(gè)字段比如區(qū)域分組一次性在每個(gè)組下面插入Subtotal行。這樣組內(nèi)小計(jì)是Subtotal最后的總計(jì)也是Subtotal完全不會(huì)重復(fù)。更精細(xì)一點(diǎn)的做法是把Subtotal直接嵌入到套用表格格式后的數(shù)據(jù)里右鍵表格——表格——匯總行那行匯總默認(rèn)用的就是Subtotal一類點(diǎn)下拉箭頭還能直接切換求和、平均、計(jì)數(shù)等不需要手動(dòng)改公式。如果你自己手動(dòng)搭公式只要記住一個(gè)大原則就夠了凡是有小計(jì)的地方就統(tǒng)一用Subtotal凡是小計(jì)需要被二次匯總的地方也統(tǒng)一用Subtotal。不要在一套計(jì)算鏈里混入SUM、AVERAGE這類普通函數(shù)一旦混入Subtotal屏蔽其他Subtotal的能力就用不上了你又會(huì)回到雙重計(jì)算的苦海。5. 參數(shù)應(yīng)用里的冷知識(shí)把SUBTOTAL變成會(huì)響應(yīng)的活報(bào)表很多人知道Subtotal能隨篩選變化卻不知道它還能配合其他功能玩出更多花樣做成會(huì)響應(yīng)的活報(bào)表。這部分我挑幾個(gè)自己高頻使用、驗(yàn)證過穩(wěn)定的玩法。5.1 只統(tǒng)計(jì)可見狀態(tài)的錯(cuò)誤值——以及一個(gè)反直覺的陷阱Subtotal在計(jì)算時(shí)有一個(gè)看似很好、實(shí)則暗藏陷阱的特性它計(jì)算時(shí)忽略被篩選掉的數(shù)據(jù)同時(shí)也忽略公式計(jì)算得到的錯(cuò)誤值比如DIV/0!、N/A這些。平時(shí)這算是一種容錯(cuò)避免整個(gè)匯總直接報(bào)錯(cuò)。但如果你做數(shù)據(jù)質(zhì)檢希望統(tǒng)計(jì)某一列里到底有多少個(gè)錯(cuò)誤值那就不能直接用COUNTIF配合Subtotal了必須換個(gè)思路。我的做法是加一個(gè)輔助列用ISERROR(原數(shù)據(jù)單元格)判斷得到TRUE/FALSE序列再用SUBTOTAL(3, 輔助列區(qū)域)統(tǒng)計(jì)可見行里有多少個(gè)TRUE這樣就能精準(zhǔn)得到當(dāng)前可見錯(cuò)誤單元格數(shù)量。說白了Subtotal本身不做條件判斷但它可以和輔助列組合成一套按可見狀態(tài)統(tǒng)計(jì)滿足條件數(shù)量的機(jī)制。這個(gè)思路適用于所有類似需求——不僅錯(cuò)誤值包含特定關(guān)鍵詞、大于某個(gè)閾值、去重后的種類數(shù)都可以通過輔助列Subtotal實(shí)現(xiàn)。5.2 用01編號(hào)和101編號(hào)做隱藏vs不隱藏的雙口徑對(duì)比前面說了編號(hào)9和109的區(qū)別在工作中這個(gè)差異還能反過來利用。例如月度匯報(bào)的表我需要同時(shí)展示含隱藏行口徑的全量業(yè)績(jī)和排除隱藏行的有效業(yè)績(jī)那就可以兩列各放一個(gè)Subtotal一列用9一列用109。這樣我只需要控制行隱藏與否兩列結(jié)果就自動(dòng)分道揚(yáng)鑣不需要維護(hù)兩套手動(dòng)匯總的數(shù)字。一張動(dòng)態(tài)表同時(shí)展示賬面數(shù)和實(shí)際可見數(shù)在財(cái)務(wù)對(duì)賬、審計(jì)痕跡核對(duì)中非常好用。5.3 和條件格式一起用高亮匯總行Subtotal結(jié)果被用于條件格式也是一個(gè)很順手的花活。比如我想讓合計(jì)行在銷售額總和超過100萬時(shí)自動(dòng)變紅那么只需選中匯總單元格設(shè)置條件格式公式為SUBTOTAL(9, C2:C100)1000000然后設(shè)置格式填充色。這樣篩選不同區(qū)域時(shí)如果區(qū)域規(guī)模不同顏色會(huì)動(dòng)態(tài)變化。這種數(shù)字一出口報(bào)表自己會(huì)說話的效果客戶和領(lǐng)導(dǎo)都吃這一套。5.4 篩選狀態(tài)下的多列聯(lián)動(dòng)匯總不要把Subtotal局限在單列單行你完全可以引用一個(gè)多列區(qū)域得到可見區(qū)域里所有列分別求和的效果。比如說我有C列和D列兩個(gè)數(shù)值列我可以寫SUBTOTAL(9, C2:D100)這個(gè)公式會(huì)橫向擴(kuò)展在相鄰兩列里分別顯示可見C列總和、可見D列總和。這種一拉兩行的寫法比寫兩個(gè)公式省事而且區(qū)域變化時(shí)也更好維護(hù)。不過要注意這種多列引用對(duì)區(qū)域的形狀有要求如果區(qū)域中有空列或整列不適配結(jié)果可能不盡人意。所以實(shí)操建議是單獨(dú)區(qū)域、單獨(dú)公式除非你已經(jīng)很熟悉這種橫向輸出機(jī)制否則盡量別在正式報(bào)表里亂用。5.5 與透視表搭配使用透視表本身自帶匯總能力大多數(shù)情況下并不需要Subtotal。但如果你做的是透視表公式組合模板想在透視表外面做一個(gè)跟隨透視表篩選器動(dòng)態(tài)變化的單元格那Subtotal就派上用場(chǎng)了。典型場(chǎng)景透視表已經(jīng)按月份和區(qū)域統(tǒng)計(jì)了銷售額希望在透視表旁邊的單元格里匯總當(dāng)前篩選條件下的所有可見行銷售額。直接在透視表外面用SUM引用透視表的某個(gè)區(qū)域是不行的因?yàn)橥敢暠韰^(qū)域結(jié)構(gòu)會(huì)變。但如果透視表布局固定你用SUBTOTAL(9, 透視表可見數(shù)據(jù)區(qū)域)就能得到跟隨透視表篩選狀態(tài)更新的匯總值。注意這里對(duì)引用區(qū)域的邊界要求很高區(qū)域范圍必須準(zhǔn)確覆蓋透視表數(shù)值區(qū)而且透視表刷新后結(jié)構(gòu)不能改變否則公式會(huì)偏離。這個(gè)方法適合數(shù)據(jù)透視表學(xué)習(xí)者和高級(jí)模板設(shè)計(jì)者不推薦新手在重要報(bào)表里貿(mào)然使用。6. 分組求和、整體統(tǒng)計(jì)一份庫存臺(tái)賬里的Subtotal實(shí)戰(zhàn)前面講了一堆原理和特性這里我用一個(gè)完整的實(shí)戰(zhàn)案例把上面這些東西串起來。假設(shè)我手頭有一份門店庫存臺(tái)賬所有明細(xì)都是流水賬大概100行包含A列倉庫名稱、B列商品類別、C列庫存數(shù)量。需求如下平時(shí)要看整個(gè)倉庫的總庫存篩選某個(gè)倉庫時(shí)匯總數(shù)要跟著變表格底部還要放各倉庫的小計(jì)但小計(jì)不參與多層重復(fù)計(jì)算隱藏部分行時(shí)匯總要能自動(dòng)避開隱藏行總數(shù)不能等于各小計(jì)的行數(shù)之和否則會(huì)有重復(fù)。我的做法是在表格最下方預(yù)留若干匯總行每行固定寫一個(gè)倉庫名比如華東倉、華南倉、華北倉旁邊寫SUBTOTAL(109, C2:C101)這樣只有當(dāng)篩選條件或者隱藏狀態(tài)發(fā)生變化時(shí)這個(gè)數(shù)字才會(huì)自動(dòng)更新。在所有小計(jì)下面再寫一個(gè)總計(jì)SUBTOTAL(109, C2:C101)注意總計(jì)的引用區(qū)域和每個(gè)小計(jì)完全一樣都是全明細(xì)區(qū)域。由于Subtotal會(huì)跳過其他Subtotal這個(gè)總計(jì)實(shí)際上等于當(dāng)前所有可見的原始數(shù)據(jù)之和和小計(jì)之和天然一致不會(huì)重復(fù)。這里有人可能擔(dān)心既然總計(jì)和小計(jì)都引用同一個(gè)區(qū)域那會(huì)不會(huì)小計(jì)把自己也包含進(jìn)去了不會(huì)因?yàn)樾∮?jì)公式本身在C102這類匯總區(qū)而不是在明細(xì)區(qū)C2:C101中引用區(qū)域內(nèi)沒有Subtotal自然不會(huì)造成自引用。只有當(dāng)你在明細(xì)區(qū)內(nèi)的某個(gè)單元格寫Subtotal時(shí)才會(huì)出現(xiàn)循環(huán)引用警告這種錯(cuò)誤要盡量避免。我還給這個(gè)臺(tái)賬加了一個(gè)輔助列D輔助標(biāo)記里面填1代表該行歸屬有效。然后把C列的數(shù)字全部換算成D列的一個(gè)輔助序號(hào)。怎么說呢這種方式通常用在你確實(shí)需要對(duì)可見行里的帶有某種標(biāo)記的行數(shù)做統(tǒng)計(jì)時(shí)D列為1表示有效E列寫SUBTOTAL(3, D2:D101)就能統(tǒng)計(jì)出當(dāng)前篩選狀態(tài)下有效庫存行數(shù)。這招對(duì)運(yùn)營(yíng)同學(xué)非常有用比如統(tǒng)計(jì)當(dāng)前區(qū)域里有庫存記錄的SKU數(shù)。最后補(bǔ)充一句關(guān)于Excel狀態(tài)欄的技巧當(dāng)你在表格中選中一個(gè)連續(xù)區(qū)域時(shí)Excel狀態(tài)欄本來就顯示平均值、計(jì)數(shù)、求和但這個(gè)求和同樣是跟手走的會(huì)在你手動(dòng)隱藏行時(shí)自動(dòng)剔除隱藏行。所以如果你只是臨時(shí)瞄一眼不需要寫公式用狀態(tài)欄就可以了。Subtotal的價(jià)值在于它是一個(gè)可復(fù)用的、會(huì)隨篩選和隱藏更新的公式化匯總值你可以把它放到任何報(bào)表位置而不是每次都手動(dòng)選中區(qū)域看狀態(tài)欄。7. 為什么你還在手動(dòng)核對(duì)數(shù)字、被SUM坑得體無完膚坦率講我已經(jīng)好幾年不在正式報(bào)表里用SUM做數(shù)據(jù)匯總了。這倒不是說SUM沒用日常橫向加幾個(gè)數(shù)它確實(shí)方便但只要數(shù)據(jù)量一大、篩選條件一變、隱藏行一多SUM就四處漏風(fēng)。Subtotal用熟了以后基本就是一函數(shù)走天下的狀態(tài)——求和使用9或109計(jì)數(shù)用2或102非空計(jì)數(shù)用3或103平均值用1或101。一個(gè)函數(shù)包攬十一類統(tǒng)計(jì)需求更別提那個(gè)讓人放心的自動(dòng)跳過其他Subtotal機(jī)制。我后來把同一個(gè)模板分享給了團(tuán)隊(duì)里的新人他第一次看到108、109這類編號(hào)時(shí)也是一頭霧水。我告訴他不用全記第一組參數(shù)你可以先只記9和109分別代表求和和忽略隱藏行的求和。其他編號(hào)遇到具體需求再去查表用個(gè)幾次就會(huì)形成肌肉記憶。另外再提一句和Subtotal經(jīng)常被搞混的AGGREGATE函數(shù)。AGGREGATE功能上更強(qiáng)支持更多的統(tǒng)計(jì)函數(shù)編號(hào)還能忽略錯(cuò)誤值和嵌套的SUBTOTAL與AGGREGATE。但它的公式參數(shù)更復(fù)雜輸入門檻也更高日常使用我建議還是優(yōu)先Subtotal。只有當(dāng)你想讓公式自動(dòng)忽略錯(cuò)誤值時(shí)比如一列里有幾個(gè)DIV/0!你還想正常求平均AGGREGATE才更合適。普通場(chǎng)景用Subtotal特殊容錯(cuò)場(chǎng)景用AGGREGATE兩者互補(bǔ)但不要overuse。8. 你可能會(huì)遇到的報(bào)錯(cuò)和詭異行為排查Subtotal用多了也難免會(huì)碰到一些奇奇怪怪的情況。我整理幾個(gè)最常遇到的報(bào)錯(cuò)和詭異行為給你規(guī)避掉。1. 明明篩選了Subtotal結(jié)果卻不變首先檢查你的公式是否引用了被篩選掉的行仍在范圍內(nèi)的數(shù)據(jù)比如你引用的是整列C:C篩選后該列其他行的值依然會(huì)被包含在Subtotal的統(tǒng)計(jì)區(qū)域里。Subtotal忽略的是不可見行而不是其他行。這時(shí)應(yīng)該把引用范圍精準(zhǔn)到數(shù)據(jù)區(qū)域不要一個(gè)C:C從頭拉到尾除非你不怕行數(shù)變動(dòng)。此外Excel的篩選如果有部分行隱藏的特殊結(jié)構(gòu)Subtotal也可能出現(xiàn)判斷失效的情況這時(shí)建議把篩選清除后逐段排查。2. 手動(dòng)隱藏行后數(shù)字依然包含隱藏?cái)?shù)據(jù)這個(gè)就是典型的選了9而不是109的問題?；氐絽?shù)編號(hào)把9改成109即可。如果你還想讓某些行被折疊但依然計(jì)入那就刻意保留9系列這取決于你的統(tǒng)計(jì)口徑。3. 出現(xiàn)#VALUE!或#NAME?錯(cuò)誤#NAME?通常意味著你寫函數(shù)名時(shí)拼寫錯(cuò)誤或者輸入了中文符號(hào)。檢查一下是不是寫成了SUBTOTAL(9,C2:C100)但括號(hào)或逗號(hào)用了全角符號(hào)。Excel對(duì)全角逗號(hào)極其敏感一個(gè)全角逗號(hào)就能讓公式徹底罷工。4. 循環(huán)引用把小計(jì)行包含進(jìn)自己的引用區(qū)域這是新手最容易犯的錯(cuò)。比如明細(xì)數(shù)據(jù)在C2:C30你在C31寫小計(jì)結(jié)果你的公式卻寫成了SUBTOTAL(9,C2:C31)那你的小計(jì)就被包含進(jìn)自己的計(jì)算區(qū)域了形成循環(huán)引用。解決辦法是確認(rèn)引用區(qū)域的邊界永遠(yuǎn)停留在最后一個(gè)明細(xì)行匯總行要單獨(dú)放在區(qū)域之外。如果表格行數(shù)經(jīng)常會(huì)變建議把明細(xì)區(qū)域轉(zhuǎn)成Excel表格Table然后用結(jié)構(gòu)化引用這樣Subtotal會(huì)自動(dòng)擴(kuò)展不會(huì)因?yàn)樾略鲂卸┧慊蛘叨嗨?。?shí)操中這個(gè)邊界問題比參數(shù)選錯(cuò)還要致命一定要養(yǎng)成明細(xì)區(qū)與匯總區(qū)分層的習(xí)慣。5. 篩選狀態(tài)下看不全匯總結(jié)果篩選模式下若把合計(jì)行也篩掉了公式還在但你就是看不到匯總值。這時(shí)用數(shù)據(jù)——篩選——重新應(yīng)用/清除或者把匯總行固定放在表格最上方一行凍結(jié)窗格模式下可以解決看不到匯總的問題。更省心的是用表格匯總行功能那個(gè)匯總行即使篩選時(shí)也會(huì)保持在表格底部Excel會(huì)自動(dòng)保證總計(jì)行不被篩選掉。6. 對(duì)多區(qū)域引用時(shí)結(jié)果異常Subtotal雖然支持多區(qū)域引用但當(dāng)引用區(qū)域帶有隱藏行且分布在不同工作表時(shí)計(jì)算基準(zhǔn)可能發(fā)生微妙變化。我的經(jīng)驗(yàn)是能合并成一個(gè)區(qū)域就絕不要拆散合并不了的用輔助表把相關(guān)列先拼到一個(gè)連續(xù)區(qū)域內(nèi)再用Subtotal。這個(gè)方法雖然多占用幾行但穩(wěn)定性高很多。7. 透視表聯(lián)動(dòng)時(shí)數(shù)據(jù)對(duì)不上前面第5.5節(jié)說過透視表動(dòng)態(tài)匯總的問題。如果你發(fā)現(xiàn)在透視表區(qū)域加Subtotal后數(shù)字總差首先檢查透視表是否有行總計(jì)或者列總計(jì)Subtotal會(huì)和這些總計(jì)重復(fù)計(jì)算。解決辦法是使用GETPIVOTDATA函數(shù)或者干脆讓透視表行總計(jì)留在原位Subtotal只負(fù)責(zé)透視表以外的數(shù)據(jù)不要試圖完全替代透視表內(nèi)置匯總。這種功能和功能打架的問題在設(shè)計(jì)報(bào)表模板時(shí)就要提前規(guī)避。我在實(shí)際答疑過程中還經(jīng)常遇到一類情況數(shù)據(jù)區(qū)域中混有文本型數(shù)字導(dǎo)致COUNTA和COUNT結(jié)果對(duì)不上。比如說有一列看起來是數(shù)字但實(shí)際上是文本格式此時(shí)Subtotal的2COUNT會(huì)統(tǒng)計(jì)不到而3COUNTA會(huì)算進(jìn)去。解決思路很簡(jiǎn)單先選中該列數(shù)據(jù)用分列向?qū)?shù)據(jù)——分列——直接完成批量把文本型數(shù)字轉(zhuǎn)成真正的數(shù)字格式然后再用Subtotal統(tǒng)計(jì)就一致了。順帶一提Excel本身有太多隱藏坑都和數(shù)據(jù)格式有關(guān)Subtotal只是其中之一。做模板的時(shí)候我給所有需要統(tǒng)計(jì)的列都提前設(shè)置成數(shù)值格式并禁止用戶手動(dòng)粘貼格式寧可多花點(diǎn)時(shí)間規(guī)范也不要后續(xù)返工核對(duì)半天的數(shù)據(jù)真相。用Subtotal的最終目的不就是為了讓自己在數(shù)據(jù)核對(duì)上少花時(shí)間嗎最后說一點(diǎn)個(gè)人體會(huì)Subtotal真正優(yōu)秀的點(diǎn)并不在于它比SUM厲害而在于它讓表格具備了一種對(duì)用戶行為響應(yīng)的能力。篩選、隱藏、分組這些操作在Excel里太常見了如果每次操作完都要手動(dòng)改匯總公式你的報(bào)表就永遠(yuǎn)是死報(bào)表。用了Subtotal你的報(bào)表才算是活起來了。我自己在給團(tuán)隊(duì)做模板時(shí)一遍遍強(qiáng)調(diào)的就是這句話能寫成公式的絕對(duì)不要手算能用Subtotal的絕對(duì)不要用SUM去湊合。 把Subtotal當(dāng)成Excel計(jì)算的默認(rèn)武器省下來的時(shí)間和頭發(fā)都?jí)蚰愣嗝脦讉€(gè)月的魚了。