Excel分類求和,SUBTOTAL函數以一敵百

2020-12-14 半點功夫體育

Excel分類求和，SUBTOTAL函數以一敵百

大家好，今天和大家分享的內容是《Excel分類求和，SUBTOTAL函數以一敵百》。提起匯總，我想這應該是每一個辦公人必修本領，相信大家對於SUM函數的使用應當是操縱自如了。但是在實際工作當中，很多時候數據不不停的變化，需要統計不同的範圍，根據篩選的結果等等方式進行合計，如果不能很好掌握方法，單靠SUM函數來支持，顯然是力不從心的。下面，我們一起通過案例學習一下如何解決這些問題。

一、忽略篩選行匯總求和

案例：現在有一張匯總統計表，因為工作需要，部分項目不需要通過篩選進行了隱藏，於是每次篩選後用SUM求和都要重新選擇求和區域，很是麻煩。有沒有好的辦法，可以實現根據自己選擇的範圍進行動態的匯總呢？

個人分析：如何想達到動態求和的目的，使用SUM已經不能勝任工作了，現在需要SUBTOTAL函數來解決。subtotal函數的語法是：SUBTOTAL(function_num,ref1,ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數字，指定使用何種函數在列表中進行分類匯總計算。我們可以先不用管這些含義，用實例操作你就會明白。

具體操作：首先將表格中使用的SUM函數更換為SUBTOTAL函數，在單元格A7中輸入了公式。=SUBTOTAL(9,A2:A6)

案例小結：通過篩選後的數據匯總，可以使用SUBTOTAL函數完成任務。這裡的「9」表示的意思是忽略未篩選出的數據，僅對篩選後的結果進行求和」。

二、忽略隱藏行求和

案例：現在有一個統計表，由於工作需要將其中一列數字進行隱藏不參與運算。如果我們直接使用SUM函數，是無法得到正確結果的，如下圖所示。

即使是使用剛學的SUBTOTAL函數並且設置參數「9」，也是無法實現想要的結果的，如下圖所示。

個人分析：是不是沒有辦法了？其實，這時候我們只需要更換SUBTOTAL函數一個參數即可完成任務。將一個參數修改為「109」，修改後的公式：=SUBTOTAL(109,A1:A5)，如下圖所示，通過函數參數的修改，就能輕鬆得到忽略隱藏行後的求和結果！

參數「109」的含義是對可見數值進行求和，它既可以對隱藏後的數據進行求和，還可以對篩選後的數據進行求和。而上一個案例中的參數「9」卻只能使用顯示行的的運算，對隱藏行則是無效的。

三、篩選結果求和

有的時候，我們需要對數據中相同的單位或類型進行分別求和，達到通過篩選可以自動計算我想要的匯總值。如下圖，我想要知道三個不同單位的匯總數量。如下圖

個人小結：通過SUBTOTAL函數的不同參數設置達到了篩選求和的結果，比每次都使用sum函數簡直方便多了。

四、SUBTOTAL多功能的應用

可不要以為SUBTOTAL函數隻對求和的運算很強大，其實對於平均值、最大值、標準差、方差等運算，SUBTOTAL都能有求必應，僅僅改變它的第一參數即可實現需要的結果。

小案例：我們需要統計下表中忽略隱藏行的最大值。在A6單元格輸入公式：=SUBTOTAL(104,A1:A5)，結果如下圖

現在我們再來實驗一下，隱藏第三行的最大值「8」後，在A6單元格中得到了當前可見的最大值「7」。

五、關鍵參數詳細了解

上一個案例中的參數104到底是什麼意思呢？其實SUBTOTAL函數裡面有一套數字代表規則，包括求平均值、最大值、最小值、標準差、方差等11種功能。下面我們結合11種參數的對照表對其他的參數都了解了解，

總結一下今天的分享，SUBTOTAL其實是一個功能很強大的函數，我們只要了解它的基本運算技巧和運算方法，就可以實現辦公中很多想要實現的結果。

還是那句話，在我們日常辦公過程中，很多的小技巧都需要平時的收集、學習和積累，相信自己，每天進步一點點，受益自己一生中。大家如果有什麼問題或是好的建議，歡迎在下方留言交流。

每天半點工夫，玩轉辦公軟體，我還是你們的工夫哥，我們下次再見！

相關焦點

  • Excel中的subtotal函數用法介紹(一)
    比如subtotal函數，這個函數是一個比較少見，但是非常強大的一個函數，這一個函數，包含了11種函數功能。函數用法如下：語法： SUBTOTAL(function_num,範圍)其中function_num中的不同數字分別代表不同的函數乍一看，1-11數字代表的函數和101-111代表的函數是一樣的，但是仔細觀察我們就能發現，兩類數字，一類算的是包含隱藏值的，另一類是忽略隱藏值，只對可見單元格進行計算。
  • Excel中的subtotal函數——一個可以替代多個的統計函數!
    在下圖的Excel表格中，對相關區域進行分類匯總求和、求平均值以後，我們發現統計結果的函數公式是=SUBTOTAL(1,E2:E4)、=SUBTOTAL(9,E2:E4)這種形式，而不是我們通常使用的sum或者average函數，雖然兩者的計算結果是一樣的
  • Excel中的subtotal函數用法介紹
    Excel裡函數成百上千，學也學不完，其實，對於日常工作來說，學會其中幾種，如vlookup，count、sum等就可以解決百分之八十的問題了。剩下的百分之二十則是我們可以不斷精進的部分。比如subtotal函數，這個函數是一個比較少見，但是非常強大的一個函數，這一個函數，包含了11種函數功能。
  • WPS2019辦公技巧——WPS表格的多用途函數SUBTOTAL函數
    WPS2019辦公技巧——WPS表格的多用途函數SUBTOTAL函數SubTotal函數是 WPS表格 中的分類匯總函數，它共支持 11 個函數，分別為 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp，
  • 配合篩選功能使用的極品函數SUBTOTAL
    點擊上方「藍字」關注我們在Excel中有這麼一個函數可以求和，可以求平均值、可以能計數、可以最大最小值，可以求標準差等多種功能，這就是Subtotal函數，其功能真的是「以一敵十一」。這麼牛氣衝衝的函數，今天就讓我們解開它的面紗一、Subtotal函數介紹。作用：返回一個數據列表或數據的分類匯總。
  • excel求和公式技巧:遍地開花的SUBTOTAL函數
    ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊一、忽略篩選行求和「苗老師，我碰到了個問題」，小白一上班就來找我，她說：「我有一張合計的表，列印的時候不想列印某些人的內容我說：「那好辦，換個求和函數就行。別用SUM了，試試SUBTOTAL。」小白：「這是什麼函數，沒用過。」我說：「這函數可比SUM函數厲害多了，能應對好幾種求和場景呢！」
  • Excel –用subtotal函數給區域編號,且忽略隱藏行保持順序連續
    subtotal 函數是 Excel 中一個功能非常全面的函數，它不僅僅可以求和，還可以計算乘積、平均值、最大最小值、計數、計算方差等。可以說，這是一個包羅萬象的函數，只要通過調整參數設置，就能以一代多實現不同函數的效果。不僅如此，subtotal 函數還允許設置是否要忽略隱藏值。今天我們就以一個案例來學習一下：如何用 subtotal 函數忽略隱藏值自動順序編號。
  • Excel求和的5種技巧,除了Sum之外,還有Subtotal、Aggregate等函數
    ，大家都已經非常的熟悉了，大部分情況下是用「命令」或Sum函數求和，其實還有很多種求和的方式，如大家比較熟悉的Sumif、Sumifs，還有大家比較陌生的Sumproduct函數法、Subtotoal函數法、Aggregate函數法。
  • Excel如何在表格中快速創建超級表並打開subtotal函數？
    excel如何在表格中快速創建超級表，並且打開subtotal函數？下面小編就通過實踐案例給大家操作一下。1.我們先將表格新建為超級表。6.在匯總行的下拉菜單，有一些數據的計算公式，上方顯示的就是subtotal函數。
  • 分類匯總函數Subtotal和Aggregate應用技巧解讀
    此時，如果要一個只對「可見」單元格或區域、忽略錯誤等類型進行統計分析的函數，將會是「雪中送炭」……今天，小編帶大家了解一下萬能的分類統計匯總函數Subtotal和Aggregate。一、分類匯總函數：Subtotal，返回指定區域的分類匯總結果。
  • excel函數公式大全之利用SUBTOTAL函數實現匯總篩選數據顯示求和
    excel函數公式大全之利用SUBTOTAL函數實現匯總篩選數據顯示求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數SUBTOTAL函數。
  • excel求和公式技巧：遍地開花的SUBTOTAL函數
    ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊一、忽略篩選行求和「苗老師，我碰到了個問題」，小白一上班就來找我，她說：「我有一張合計的表，列印的時候不想列印某些人的內容，就把它們用篩選隱藏了，但是每次求和都要更改求和區域
  • Excel中的subtotal函數用法介紹(二)
    今天繼續挖掘subtotal函數蘊含的寶藏。subtotal函數除了可以計算可見單元格，對於非空單元格的計算也非常好用。舉個慄子。這個時候，subtotal函數又來大顯神通了，上一期我們介紹過subtotal函數裡不同的數字代表不同的統計功能其中數字2和數字3，分別代表著統計數值個數和統計非空單元格數量。因此，當我們需要統計非空單元格時，只需要將數字3代入函數subtotal中。下圖，對比了統計數值個數和統計非空單元格數量的結果。
  • 以一敵十的Subtotal函數經典用法解讀!
    在Excel函數中，有一個函數既能求和、求平均值、還能計數、求最值等多種功能，其實這個函數就是Subtotal，其功能真是「以一敵十」，今天我們討論其最經典的5種用法。一、Subtotal函數的作用及語法結構。作用：返回一個數據列表或資料庫的分類匯總。語法：=Subtotal(功能代碼，數值區域)。
  • Excel SubTotal函數的使用方法,含隱藏篩選和分類匯總實例
    SubTotal函數是 Excel 中的分類匯總函數，它共支持 11 個函數，分別為 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp，這些函數有兩組編號，一組為 1 到 11，另一組為 101 到 111，其中前一組包含隱藏值，後一組不包含隱藏值。
  • SUBTOTAL函數的用法介紹
    內容提要：excel中SUBTOTAL函數是一個匯總函數，它可以包括求和、匯總、平均等等多個函數的使用。SUBTOTAL函數的優點在於可以忽略隱藏的單元格、支持三維運算和區域數組引用。　　SUBTOTAL函數就是返回一個列表或資料庫中的分類匯總情況。
  • 能求和、計數、還能求最值等功能的統計函數Subtotal實用技巧解讀
    統計函數，我們學過的已經很多，有求和類、計數類、最值類等，但是，有一個函數既可以求和，還可以計數，統計最值……可以說是以一敵十，這個函數就是Subtotal函數。一、作用及語法結構。作用：返回指定的數據列表或資料庫的分類匯總。語法結構：=Subtotal(功能代碼，數據列表或資料庫)。
  • 經典統計函數Subtotal,能求和、計數、還能求最大(小)值等
    在Excel中，常用到的統計函數有Sum系列、Count系列、Average系列、Max系列、Min系列等，其功能非常的強大，使用起來也非常的簡單，但函數較多，語法結構也不完全相同，如果不經常使用，對於部分函數的使用可能存在一定的困難，造成工作效率低下等……其實在Excel中，有這麼一個函數，集成了了求和、計數、平均值、最值、成績等多種功能，它既是Subtotal
  • 萬能函數Subtotal實用技巧解讀!
    在Excel中，有一個函數既能求和，求平均值、還能計數、求最值等多種功能，想知道這個函數嗎？其實就是Subtotal函數，其功能真的是「以一敵十」，今天，小編帶大家來了解，學習Subtotal函數。一、Subtotal函數的作用及語法結構。
  • Excel明星函數系列5:以一敵十的SubTotal函數,不會用就太可惜了
    SubTotal函數簡介：SubTotal函數是目前財務、行政用的最多的一個函數，也是重要函數之一。SubTotal函數是 Excel 中的分類匯總函數，它共支持 11 個函數，分別為 Average、Count、CountA、Max、Min、Product、Stdev、Stdevp、Sum、Var、Varp，這些函數有兩組編號，一組為 1 到 11，另一組為 101 到 111，其中前一組包含隱藏值，後一組不包含隱藏值。