一、對隐藏值的計算和忽略
計算銷量平均值
公式:F4=SUBTOTAL(1,C2:C8)
或者G4=SUBTOTAL(101,C2:C8)
(1)小夥伴們有沒有發現,圖1中如果沒有隐藏行時,兩個公式的計算結果是相同的。也就相當于=SUM(C2:C8)/7
(2)當圖2中把序号1、2行隐藏後,G4結果發生了變化。
公式F4=SUBTOTAL(1,C2:C8)中的第一個參數為1,是以它就包含隐藏的行;
相當于=SUM(C2:C8)/7
公式G4=SUBTOTAL(101,C2:C8)中的第一個參數為101,是以它就不包含隐藏的行。相當于=SUM(C4:C8)/5
二、對篩選值的忽略
統一目前值的平均值
公式 =SUBTOTAL(1,C2:C8)
公式=SUBTOTAL(101,C2:C8)
通過篩選資料,小夥伴有沒有發現無論是使用哪個公式,結果都在發生變化,而且隻對目前顯示的數值負責。這就得出:通過篩選資料,不管是何種類型的統計,其計算結果隻對目前篩選保留的資料複制
三、永遠保持連續的序号
公式A2=SUBTOTAL(103,$B$2:B5),按Ctrl Enter填充
隐藏或取消隐藏行,其行号都是連續的。
參數103所對應的函數為:Counta。統計非空單元格的個數。當參數為1**時,忽略隐藏的行。是以公式=SUBTOTAL(103,$B$2:B5)統計的就是從B2開始到目前單元格累計非空單元格個數。
四、按顔色求和
公式G10==SUBTOTAL(9,C:C)
當subtotal函數第一個參數為9,那麼就是篩選求和。選中C列中某一個顔色的單元格,然後右擊--篩選--按所選單元格的顔色進行篩選,原理:通過篩選和subtotal函數來實作按顔色求和。