excel求和公式技巧:遍地開花的SUBTOTAL函數

2020-12-11 部落窩教育H

編按：剛入門的小夥伴總喜歡抱怨「Excel的函數太多了，老是記不住，有沒有一個能匯總很多函數功能的函數呢？」不瞞你說，還真有！今天咱們要說的這個函數，其最大的功能就是可以替代11種函數使用。除此之外，它還可以根據不同的篩選結果，變更最後的計算結果！怎麼樣？是不是光聽起來就很厲害呢？廢話不多說，趕緊和小編一起來看看吧！

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

一、忽略篩選行求和

「苗老師，我碰到了個問題」，小白一上班就來找我，她說：「我有一張合計的表，列印的時候不想列印某些人的內容，就把它們用篩選隱藏了，但是每次求和都要更改求和區域，老麻煩了。」

我說：「那好辦，換個求和函數就行。別用SUM了，試試SUBTOTAL。」

小白：「這是什麼函數，沒用過。」

我說：「這函數可比SUM函數厲害多了，能應對好幾種求和場景呢！」

小白：「這麼厲害，那你可得教教我。」

「那就聽我細細給你道來~首先，來解決你表格的問題。」說著，我就打開了她的表格，如下圖所示。

「現在，你的表格使用的是SUM函數求和，我們把它換成SUBTOTAL函數，你再看看」。說完，我在單元格A7中輸入了公式。

=SUBTOTAL(9,A2:A6)

「真的變了！」接著小白又篩選了一些別的行，發現都可以得到她想要的結果，十分高興。不過隨後她又發現了新大陸，「那這個9是什麼意思呢？」

我：「這個9呀，表示忽略未篩選出的數據，僅對篩選後的結果進行求和」。

小白：「聽你這說法，還有其他數字代表其他的含義咯？」

我：「當然，那我就再跟你說說其他數字的含義吧！」

二、忽略隱藏行求和

我們有時候會碰到這種情況，有一列數字，需要隱藏幾個不進行運算的數據。如果是直接使用SUM，是無法得到正確結果的，如下圖所示。

即使用上剛學的SUBTOTAL函數的參數「9」，也是無法實現的，如下圖所示。

這時候我們就要考慮換一個參數了。

下面有請參數「109」，登場！

公式：=SUBTOTAL(109,A1:A5)

如下圖所示，將SUBTOTAL函數第一參數變為「109」後，就能輕鬆得到忽略隱藏行後的求和結果！如下圖所示。

參數「109」的作用是對可見數值進行求和，它既可以對隱藏後的數據求和，也可以對篩選後的數據求和。而參數「9」只能使用在篩選行，對隱藏行則無效。

三、SUBTOTAL其他參數的應用

SUBTOTAL不僅僅局限在求和領域，平均值、最大值、標準差、方差，都能求，只需改變它的第一參數即可。例如，現在我們要統計忽略隱藏行的最大值，如下圖所示。

公式：=SUBTOTAL(104,A1:A5)

隱藏了最大值「8」後，直接在單元格A6中得到了當前可見的最大值「7」。

那為什麼是104呢？其實SUBTOTAL函數裡面有一套數字代表規則，今天咱們就把其他的參數都說一說，包括求平均值、最大值、最小值、標準差、方差等11種功能。有的常用，有的不常用，大家結合自己的需求來選擇。下面是11種參數的對照表。

四、拓展部分1：只統計分類匯總

我們在制表的時候，經常會碰到這樣一種匯總情況，在同表內進行分項匯總，如下圖所示。

如果使用SUM進行匯總，則會統計出所有的數據，如下圖所示。

可是我們只想合計各個小計的內容呀！別慌，只需把SUM換成SUBTOTAL就可以得到我們想要的答案。如下圖所示。

這是為什麼呢？其實SUBTOTAL除了能忽略掉被隱藏、篩選的行外，還會忽略掉包含SUBTOTAL，以及AGGREGATE函數的單元格。單元格B3、B6、B10都是用SUBTOTAL函數計算的小計，自然在最後用SUBTOTAL函數求和時，會被忽略掉。如下圖所示。

五、拓展部分2：不間斷序號

「我們了解了SUBTOTAL函數的特性之後，就可以用它來做一些什麼，比如給列表編號。」

「什麼，列表編號不是用滑鼠拉一下就好了嗎？」

「不一樣~我的編號，可是自動的哦！無論是刪除行還是隱藏行，編號都能自動重新排列!」

「這麼神奇，那我可要好好學學。」

其實它非常簡單，假設我有一張列表，目前序號列是空的，如下圖所示。

在A2單元格輸入公式：=SUBTOTAL(103,B$2:B2)，然後下拉填充，就能得到我們想要的序號。如下圖所示。

我們試著來隱藏一行，就會發現，序號仍然是按照順序排列的，並沒有中斷，如下圖所示。

現在我們來逐步解釋一下公式=SUBTOTAL(103,B$2:B2)

103：查看上述參數對照表可以得知，103的作用是忽略隱藏行和被篩選值，統計非空單元格數。

B$2:B2：A2單元格內的區域是B$2:B2，目的是，統計出B2:B2區域中非空單元格數，結果為1。在公式下拉後，A3單元格內的區域變成了B$2:B3，那麼統計的非空單元格數就變成了兩個，得到的結果為 2。如下圖所示。

以此類推，隨著公式的下拉，我們就可以得到一組連續的序號。再結合SUBTOTAL函數第一參數只計算可見數值的特性，就可以得到一組不間斷的序號！

你還知道哪些關於SUBTOTAL函數的妙用呢？一起來交流學習吧！

＊＊＊＊部落窩教育－excel不一樣的求和技巧＊＊＊＊

原創：苗旭/部落窩教育（未經同意，請勿轉載）

相關焦點

  • excel求和公式技巧：遍地開花的SUBTOTAL函數
    我說：「那好辦，換個求和函數就行。別用SUM了，試試SUBTOTAL。」 小白：「這是什麼函數，沒用過。」 我說：「這函數可比SUM函數厲害多了，能應對好幾種求和場景呢！」 小白：「這麼厲害，那你可得教教我。」
  • Excel分類求和,SUBTOTAL函數以一敵百
    Excel分類求和，SUBTOTAL函數以一敵百大家好，今天和大家分享的內容是《Excel分類求和，SUBTOTAL函數以一敵百》。提起匯總，我想這應該是每一個辦公人必修本領，相信大家對於SUM函數的使用應當是操縱自如了。
  • excel函數公式大全之利用SUBTOTAL函數實現匯總篩選數據顯示求和
    excel函數公式大全之利用SUBTOTAL函數實現匯總篩選數據顯示求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數SUBTOTAL函數。
  • Excel中的subtotal函數——一個可以替代多個的統計函數!
    在下圖的Excel表格中，對相關區域進行分類匯總求和、求平均值以後，我們發現統計結果的函數公式是=SUBTOTAL(1,E2:E4)、=SUBTOTAL(9,E2:E4)這種形式，而不是我們通常使用的sum或者average函數，雖然兩者的計算結果是一樣的
  • Excel中的subtotal函數用法介紹
    Excel裡函數成百上千，學也學不完，其實，對於日常工作來說，學會其中幾種，如vlookup，count、sum等就可以解決百分之八十的問題了。剩下的百分之二十則是我們可以不斷精進的部分。比如subtotal函數，這個函數是一個比較少見，但是非常強大的一個函數，這一個函數，包含了11種函數功能。
  • Excel求和的5種技巧,除了Sum之外,還有Subtotal、Aggregate等函數
    ，大家都已經非常的熟悉了，大部分情況下是用「命令」或Sum函數求和，其實還有很多種求和的方式，如大家比較熟悉的Sumif、Sumifs，還有大家比較陌生的Sumproduct函數法、Subtotoal函數法、Aggregate函數法。
  • Excel中的subtotal函數用法介紹(一)
    比如subtotal函數，這個函數是一個比較少見，但是非常強大的一個函數，這一個函數，包含了11種函數功能。函數用法如下：語法： SUBTOTAL(function_num,範圍)其中function_num中的不同數字分別代表不同的函數乍一看，1-11數字代表的函數和101-111代表的函數是一樣的，但是仔細觀察我們就能發現，兩類數字，一類算的是包含隱藏值的，另一類是忽略隱藏值，只對可見單元格進行計算。
  • WPS2019辦公技巧——WPS表格的多用途函數SUBTOTAL函數
    WPS2019辦公技巧——WPS表格的多用途函數SUBTOTAL函數SubTotal函數是 WPS表格 中的分類匯總函數，它共支持 11 個函數，分別為 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp，
  • Excel如何在表格中快速創建超級表並打開subtotal函數？
    excel如何在表格中快速創建超級表，並且打開subtotal函數？下面小編就通過實踐案例給大家操作一下。1.我們先將表格新建為超級表。6.在匯總行的下拉菜單，有一些數據的計算公式，上方顯示的就是subtotal函數。
  • excel函數公式大全之利用SUBTOTAL函數在篩選狀態實現連續序號
    excel函數公式大全之利用SUBTOTAL函數實現匯總篩選數據顯示求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數SUBTOTAL函數。
  • 萬能函數Subtotal實用技巧解讀!
    在Excel中，有一個函數既能求和，求平均值、還能計數、求最值等多種功能，想知道這個函數嗎？其實就是Subtotal函數，其功能真的是「以一敵十」，今天，小編帶大家來了解，學習Subtotal函數。一、Subtotal函數的作用及語法結構。
  • 配合篩選功能使用的極品函數SUBTOTAL
    點擊上方「藍字」關注我們在Excel中有這麼一個函數可以求和，可以求平均值、可以能計數、可以最大最小值，可以求標準差等多種功能，這就是Subtotal函數，其功能真的是「以一敵十一」。這麼牛氣衝衝的函數，今天就讓我們解開它的面紗一、Subtotal函數介紹。作用：返回一個數據列表或數據的分類匯總。
  • Excel中的subtotal函數用法介紹(二)
    今天繼續挖掘subtotal函數蘊含的寶藏。subtotal函數除了可以計算可見單元格，對於非空單元格的計算也非常好用。舉個慄子。這個時候，subtotal函數又來大顯神通了，上一期我們介紹過subtotal函數裡不同的數字代表不同的統計功能其中數字2和數字3，分別代表著統計數值個數和統計非空單元格數量。因此，當我們需要統計非空單元格時，只需要將數字3代入函數subtotal中。下圖，對比了統計數值個數和統計非空單元格數量的結果。
  • 能求和、計數、還能求最值等功能的統計函數Subtotal實用技巧解讀
    統計函數，我們學過的已經很多，有求和類、計數類、最值類等，但是，有一個函數既可以求和，還可以計數，統計最值……可以說是以一敵十，這個函數就是Subtotal函數。一、作用及語法結構。方法：在目標單元格中輸入公式：=SUBTOTAL(103,$B$3:B3)。
  • Excel –用subtotal函數給區域編號,且忽略隱藏行保持順序連續
    subtotal 函數是 Excel 中一個功能非常全面的函數，它不僅僅可以求和，還可以計算乘積、平均值、最大最小值、計數、計算方差等。可以說，這是一個包羅萬象的函數，只要通過調整參數設置，就能以一代多實現不同函數的效果。不僅如此，subtotal 函數還允許設置是否要忽略隱藏值。今天我們就以一個案例來學習一下：如何用 subtotal 函數忽略隱藏值自動順序編號。
  • 萬能函數Subtotal和Aggregate應用技巧全集解讀!
    在Excel中，經常要對數據進行分類統計，如果用普通的函數、公式去完成不僅費時費力，而且部分功能根本無法實現，今天，小編給大家介紹兩個分類統計的萬能函數：Subtotal和Aggregate。一、Subtotal函數。
  • 分類匯總函數Subtotal和Aggregate應用技巧解讀
    2、其它代碼所對應的功能和求和的用法相同。（二）分類匯總函數Subtotal：篩選匯總。目的：按部門統計「年薪」。2、其它代碼所對應的功能和求和的用法相同。（三）分類匯總函數Subtotal：經典用法之保持序號的連續性。
  • 經典統計函數Subtotal,能求和、計數、還能求最大(小)值等
    在Excel中，常用到的統計函數有Sum系列、Count系列、Average系列、Max系列、Min系列等，其功能非常的強大，使用起來也非常的簡單，但函數較多，語法結構也不完全相同，如果不經常使用，對於部分函數的使用可能存在一定的困難，造成工作效率低下等……其實在Excel中，有這麼一個函數，集成了了求和、計數、平均值、最值、成績等多種功能，它既是Subtotal
  • Excel教程:Excel求和函數匯總!有SUM、SUMIF、SUBTOTAL函數等等……
    但你知道嗎，Excel中為我們提供了很多求和函數，可不止SUM函數一個？今天，就讓我們來逐一認識下Excel中的求和函數吧！作為求和的正統血脈，SUM函數是資歷最老的求和函數。這個以求和的英文單詞直接冠名的函數，相信很多小花瓣都不會陌生。
  • excel數據計算的高效技能,求和函數的使用技巧
    我們在實際工作中，當我們需要處理大量數據的時候，我們通常會選擇excel表格來處理，因為excel表格處理數據自帶很多實用的公式和函數，今天我們要分享的是數據求和，這次我們不使用公式求和，這次我們分享一個更加高效的技巧，使用求和函數對數據進行快速求和。