Excel中有一個你不得不會的分類彙總函式SUBTOTAL
2023-01-15由 Excel教案 發表于 農業
excel公式怎麼分類
我們對於這個函式的最常使用的是在分類彙總中, 返回列表或資料庫中的分類彙總。一旦你建立了分類彙總,就可以使用SUBTOTAL 函式對該列表進行相關的計算。
這個函式包含11個功能,有求平均值,求最大,最小值,計數等等。所有你學好這個函式是十分有必要的。
SUBTOTAL可以達到你認為其他函式不能實現的效果。他的引數不僅非常多,而且功能十分強大。是你不可或缺資料處理的幫手。
首先先了解一下官方對他的解釋:
語法: SUBTOTAL(function_num,ref1,ref2, 。。。)
function_num可選擇值的範圍在1到11之間,和101到111之間。ref1代表需要處理資料的區域。
另外包含隱藏的值和不包含隱藏的值,分類彙總時候,他是對於是否你手動隱藏的來區分,並不是在篩選狀態隱藏的,這個千萬要理解好。
在篩選狀態,自動輸入序號
我們先看看動畫的操作,如下圖:
公式:=SUBTOTAL(3,B$2:B2)
其實這個函式公式利用的就是計算在篩選狀態的非空單元格個數,從而達到計算連續序號的作用。
分類彙總情況下,求平均值
我們先看看動畫的操作,如下圖:
公式 =SUBTOTAL(1,F:F)
其實這個就是求和在分類彙總的時候,我們選擇引數的是求平均值。這個函式強大之處在於他在帥選狀態也可以完成計算。
計算平均工資
公式{=SUM(SUBTOTAL({9,4,5},F:F)*{1,-1,-1})/(SUBTOTAL(3,F:F)-3)}
一眼看到這個公式可能你有點難理解,我們把這個公式拆開來看。
第一層,SUBTOTAL({9,4,5},F:F)),對於這一部分的意思是9,4,5,分別代表的是求和,最大值,最小值,然後他的作用區域為F列。
第二層,SUBTOTAL({9,4,5},F:F)*{1,-1,-1})這個就是組建了一個數組,效果等價於sum(f:f)-min(f:f)-max(f:f)。
第三層,(SUBTOTAL(3,F:F)-3),即就是去掉最大值,最小值,標題欄。從而達到計算人數作用。
最後輸完公式以後,記得要按三鍵完成陣列公式的必備輸入。
PS:附上Function_num的解釋
Function_num (包含隱藏值)
為1到11之間的自然數,用來指定分類彙總計算使用的函式
1 、1 AVERAGE(算術平均值)
2、 2 COUNT(數值個數)
3、 3
COUNTA
(非空單元格數量)
4 、4 MAX(最大值)
5 、5 MIN(最小值)
6 、6 PRODUCT(括號內所有資料的乘積)
7 、7 STDEV(估算樣本的標準偏差)
8 、8 STDEVP(返回整個樣本總體的標準偏差)
9、 9 SUM(求和)
10 、10 VAR(計算基於給定樣本的方差)
11 、11 VARP(計算基於整個樣本總體的方差)
Function_num (忽略隱藏值) 函式
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 SUM 求和
10 、110 VAR 方差
11 、111 VARP 方差
寫在結尾:
我們學習在於點滴積累之間,循序慢進,選對了方式,水滴石穿,相反如果沒有好的方式,會事半功倍。
以上就是今天要和大家分享的技巧,希望對大家有所幫助,祝各位一天好心情!
唯有不斷學習,才能不被淘汰!
Excel中每一個方法都有特定的用途,不是他們沒有用處,只是你不瞭解或者暫時用不著,建議你收藏起來,萬一哪天用著呢?