序号不對号,工作就亂套!
好不容易排好的序号,隻要一篩選或者一隐藏就又亂了。
特别是面對成百上千條數據時...
真讓人頭大!
今天我們就來解決這個問題~
繼續往下看吧~
01
篩選或隐藏數據之後,序号不連續
相信大家都有過這種經曆,工作中,我們經常需要對數據,進行篩選。
但是,篩選之後,序号并不會連續編号。
就比如下面的案例,當篩選出華東地區的數據之後,左側的序号是不連續的。
而對比下圖,篩選之後,編号都是連續的。
甚至,當有數據隐藏時,編号也都是連續的。
面對這樣的問題,我們要怎樣去做呢?
02
SUBTOTAL 函數的語法講解
這裡,需要引入 SUBTOTAL 函數,來解決上面這個問題。
SUBTOTAL 函數的語法如下:
=SUBTOTAL(function_num,ref1,[ref2],...)
可以理解為:
=SUBTOTAL(彙總方式,彙總區域)
SUBTOTAL 的第一參數,對應的函數如下圖:
如果使用 1-11,會忽略已篩選掉的單元格,但包括手動隐藏的行。
如果使用 101-111,不僅會忽略已篩選掉的單元格,還會忽略手動隐藏的行。
03
SUBTOTAL 函數的實例講解
上一部分,我們隻是講解了 SUBTOTAL 的語法,但是還不知道如何使用 SUBTOTAL 。
下面,就通過幾個例子講解一下。
下圖中,正常情況下,對 B 列計數,三個公式的結果一樣,都是 10。
對于為什麼 SUBTOTAL 第一參數分别是 3 和 103,請看 SUBTOTAL 第一參數對應的函數表。
另外,這個對應表無需記憶,因為在寫公式的時候,會有函數屏幕提示。
對 B 列,篩選出華東地區的數據時,COUNTA 還是 10,但是 SUBTOTAL 會忽略已經篩選掉的數據。
當隻手動隐藏第 5 行數據時,隻有 SUBTOTAL 的第一參數是「103」的時候,計數結果是 9 。
對比上面三個例子,應該就能理解了 SUBTOTAL 第一參數的意思:
如果使用 1-11,會忽略已篩選掉的單元格,但包括手動隐藏的行。
如果使用 101-111,不僅會忽略已篩選掉的單元格,還會忽略手動隐藏的行。
04
篩選或隐藏數據之後,序号保持連續
到這裡,很多小夥伴肯定會舉手:我知道了如何實現序号保持連續了。
然後,在 A2 單元格輸入以下公式,并雙擊填充得到序号列。
=SUBTOTAL(103,$B$2:B2)
好的,下面,我們來驗證一下,這個公式是否正确。
當隐藏第 4 行數據時,編号是連續的。
當篩選出「華東地區」的時候,序号也是連續的。
但仔細一看,奇怪,怎麼多了一個「華中地區」?
我明明隻篩選了「華東地區」。
為什麼會這樣呢?
因為,此函數相當于分類彙總,在篩選時,最後一行是不參與篩選的。
知道了原因,我們終于可以放大招,給出最後的解決方法了。
方法一:
在最後一行數據下的第一行(此例中是 A12:D12 )中任意單元格輸入一個空格,擴充篩選區域,就可以解決。
方法二:
在 A2 單元格輸入以下公式,雙擊填充可以解決。
=SUBTOTAL(103,$B$2:B2)*1
方法三:
在 A2 單元格輸入以下公式,雙擊填充也可以解決。
=SUBTOTAL(103,$B$2:B2)
好了,問題終于解決了!
從這個問題中,你是否能一探 SUBTOTAL 的奧秘呢?
那就留個小問題考考你吧。
下圖中,如何在篩選或隐藏狀态下,得到華東地區的銷售額?
歡迎下方「留言」,分享你的答案哦~
别忘了還有練習,練習文件獲取方式就在評論裡哦~
,