沒想到要學SUBTOTAL函數的粉絲挺多的,那就來個全面的講解吧。當了那么多年的配角,今天終于當回主角。 1.匯總行的妙用 選擇區域,插入表格,點設計,勾選匯總行。 匯總行,除了可以求和,還可以下拉選擇計數,最大值等。 當然,插入表格和勾選匯總行這些步驟也可以省略,直接寫公式也行。 求和就用:
計數就用:
SUBTOTAL一共可以代表11個函數,不過平常用得最多的是計數COUNTA和求和SUM。 2.篩選的時獲取連續序號 正常情況下,用ROW、COUNTIF之類獲取的序號,只要進行篩選就亂了。而SUBTOTAL剛好能解決這個問題。
區域采用混合引用,下拉的時候就逐漸變大,從而起到累計的作用。后面*1的作用,是防止最后一行當成匯總,導致篩選的時候出錯。不加不一定會錯,加了肯定沒錯。 現在篩選的時候,序號就是連續的,最后一行的匯總也跟著改變數據。 3.對篩選的結果進行條件求和、計數 正常情況下,SUMIFS、COUNTIFS不管有沒篩選結果都一樣,不能直接對篩選的結果進行判斷。 不過可以利用SUBTOTAL可以對可見單元格生成序號。
現在要求大于200元的班級個數,就可以用這樣的公式:
對價格進行篩選,統計結果會自動改變。 同理,統計金額大于200元的班級的總金額,就可以這樣設置公式。
核心點就是利用SUBTOTAL作為輔助列,生成數字1,再根據輔助列作為新的條件進行判斷。 4.將篩選的結果合并在一個單元格內 這個跟案例3一樣,也是用輔助列生成數字1,再借助這個判斷。 輸入公式,按Ctrl+Shift+Enter結束。IF部分的作用是讓符合F列為1,D列大于200的,返回C列的值,否則返回空,再用TEXTJOIN將內容合并起來。
要將SUBTOTAL用好,還得學會很多函數才行。 還想知道什么用法? 作者:盧子,清華暢銷書作者,《Excel效率手冊 早做完,不加班》系列叢書創始人,個人公眾號:Excel不加班(ID:Excelbujiaban) |
|