SUBTOTAL一個頂11個函數,不過今天的主角並不是它,比它更強大。
來,跟我一起來看看。
1.忽略錯誤值求和、計數
匯總行的妙用,選擇區域,插入表格,點設計,勾選匯總行。
匯總行,除了可以求和,還可以下拉選擇計數,最大值等。
當然,插入表格和勾選匯總行這些步驟也可以省略,直接寫公式也行。
求和就用:
=SUBTOTAL(9,D2:D11)
計數就用:
=SUBTOTAL(3,D2:D11)
不過,一旦金額出現錯誤值,上面的公式就用不了。
AGGREGATE函數多了一個參數,第二參數設置為7,可以忽略錯誤值。
=AGGREGATE(9,7,D2:D11)
第一參數為9是求和,3是計數,跟SUBTOTAL一樣。
=AGGREGATE(3,7,D2:D11)
2.篩選的時獲取連續序號
正常情況下,用ROW、COUNTIF之類獲取的序號,只要進行篩選就亂了。而AGGREGATE剛好能解決這個問題。
=AGGREGATE(3,7,B$2:B2)*1
區域採用混合引用,下拉的時候就逐漸變大,從而起到累計的作用。後面*1的作用,是防止最後一行當成匯總,導致篩選的時候出錯。不加不一定會錯,加了肯定沒錯。
現在篩選的時候,序號就是連續的,最後一行的匯總也跟著改變數據。
3.一個公式解決多種統計效果
比如分別統計最大、最小、平均值。
選擇C15:C17這3個單元格,輸入公式,按Ctrl+Shift+Enter三鍵結束。
=AGGREGATE({4;5;1},7,D2:D11)
4.按條件統計最大、小值
最大:
=AGGREGATE(14,7,$D$2:$D$11/($B$2:$B$11=F2),1)
最小:
=AGGREGATE(15,7,$D$2:$D$11/($B$2:$B$11=F2),1)
當第一參數為14(LARGE)、15(SMALL)的時候,可以使用第四參數,這個用法支持數組用法,可以忽略錯誤值進行統計。
$D$2:$D$11/($B$2:$B$11=F2)這部分的作用就是讓滿足條件的返回本身的價格,其他的返回錯誤值。
昨天的文章才提到F9鍵解讀公式,如果不理解可以在編輯欄選中查看運算結果。Excel2021版從昨天開始,連F9鍵都不用按,選中就會有運算結果,更加智能。
用心研究,時不時就能找到更好用的函數,提升工作效率。