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