首頁 > 專題 > excel > 主體

Excel函數學習之以一敵十的SUBTOTAL函數!

青灯夜游
發布: 2023-03-28 18:56:24
轉載
1678 人瀏覽過

Excel函數學習之以一敵十的SUBTOTAL函數!

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

忽略篩選行求和

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

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

小白:「這是什麼函數,沒用過。」

我說:「這函數可比SUM函數厲害多了,能應付好幾種求和場景呢!」

小白:「這麼厲害,那你可得教教我。」

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

=SUBTOTAL(9,A2:A6)

Excel函數學習之以一敵十的SUBTOTAL函數!

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

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

小白:「聽你這說法,還有其他數字代表其他的含義咯?」

我:「當然,那我就再跟你說說其他數字的意思吧!」

忽略隱藏行求和

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

Excel函數學習之以一敵十的SUBTOTAL函數!

即使用上剛學的SUBTOTAL函數的參數“9”,也是無法實現的,如圖所示。

Excel函數學習之以一敵十的SUBTOTAL函數!

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

下面有請參數“109”,登場!

Excel函數學習之以一敵十的SUBTOTAL函數!

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

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

SUBTOTAL其他參數的應用

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

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

Excel函數學習之以一敵十的SUBTOTAL函數!        Excel函數學習之以一敵十的SUBTOTAL函數!

(前隱藏前)                   (隱藏後)

隱藏了最大值「8」後,直接在儲存格A6中得到了目前可見的最大值「7」。

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

##SUM10#110計算樣本變異數VAR11111

計算時忽略被篩選值

#計算時忽略隱藏行和被篩選值

#作用

對應函數

#1

101

平均值

AVERAGE

##2

102

計算包含數字的單元格數

COUNT

3

103

#計算非空白單元格數

COUNTA

4

#104

最大值

MAX

#5

105

最小值

#

MIN

6

#106

乘法

PRODUCT

#7

##107

計算樣本標準差

STDEV

8

108

計算總體標準差

STDEVP

9

109

#求和

#

計算總體變異數

VARP

拓展部分1:只統計分類匯總

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

Excel函數學習之以一敵十的SUBTOTAL函數!

拓展部分2:不間斷序號

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

“什麼,列表編號不是用滑鼠拉一下就好了嗎?”

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

“這麼神奇,那我可要好好學學。”

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

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

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

Excel函數學習之以一敵十的SUBTOTAL函數!

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

你還知道哪些關於SUBTOTAL函數的妙用呢?歡迎留言分享給我們哦~喜歡文章的小夥伴不妨點下“在看”,支持我們哦!

相關學習推薦:excel教學

以上是Excel函數學習之以一敵十的SUBTOTAL函數!的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:itblw.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板