上面這個圖中的A列,就是咱們經常看到的序號形式之一。
有朋友會說,這很簡單啊,就是一些數字的羅列而已。
但在實際工作當中,遠不止數字羅列這麼簡單。咱們經常會遇到不同樣式的序號排列要求,比如說:按部門填寫序號、篩選後填寫序號、不連續的數據填寫序號等等。
1、生成常規序號
一般咱們填寫序號,只要在第一個單元格內輸入數字1,然後按住單元格右下角的填充柄向下拖動,然後在【自動填充選項】選擇【序列填充】就可以了:
如果相鄰列已經輸入了內容,可以分別輸入1、2,然後選中兩個單元格,雙擊單元格右下角的填充柄,就可以快速完成序號的填充。
但是如果相鄰列的單元格內容不連續,序號只能填充到最近一個空單元格以上的位置:
如果相鄰列沒有輸入內容,而我們需要生成的序號又非常多,是不是就要一拖到底了呢?
當然不是的。
假如說要在A列生成1至10000的連續序號,可以在A1單元格輸入數字1,在名稱框內輸入“A1:A10000”,按Enter鍵。
然後依次單擊【開始】,【填充】,在下拉列表中選擇【系列】,在【序列】對話框中,終止值輸入10000,單擊【確定】,就可以快速的生成1至10000的連續序號了。
2、按部門添加序號
在日常工作中,有時咱們需要按部門填寫序號。
以下圖為例,要求按部門單獨填寫序號,不同部門都要從1開始編號,按順序遞增。
怎麼快速生成這樣的序號呢?在A2單元格輸入以下公式,向下複製:
=COUNTIF(B$2:B2,B2)
COUNTIF函數對區域中滿足單個指定條件的單元格進行計數。
第一參數B$2:B2中的B$2是行絕對引用,在公式向下複製時,就會變成:
COUNTIF(B$2:B3,B3)
COUNTIF(B$2:B4,B4)
COUNTIF(B$2:B5,B5)……
這樣的不斷擴大的區域引用。
公式的意思是:計算自B2單元格開始,至公式所在行的B列這個區域內,有多少個與同一行中B列值相同的單元格。
3、篩選後保持連續的序號
除了上面這種情況,我們還會遇到一些需要篩選後打印的數據表,如果按常規方法輸入序號後,一旦數據經過篩選,序號就會發生錯亂。
如何處理才能使序號在篩選後也能保持連續呢?
A2單元格輸入以下公式向下複製:
=SUBTOTAL(3,B$1:B2)-1
這時候再對C列的工資額進行篩選,或是對B列的姓名進行篩選,序號就始終保持連續了。
SUBTOTAL函數只統計可見單元格的內容,通過給定不同的第一參數,可以完成計數、求和、平均值、乘積等等多種彙總方式。
在本例中,第一參數是3,就是告訴SUBTOTAL函數要執行的彙總方式是COUNTA。
COUNTA函數用於計算區域中非空單元格的個數。
SUBTOTAL(3,區域)就是計算區域中可見非空單元格的個數。
第二參數B$1:B2的B$1使用了行絕對引用,當公式向下複製時會變成
B$1:B3、B$1:B4、B$1:B5……
也就是引用自B1單元格自公式所在行的B列,這樣一個逐行遞增的引用區域,來判斷可見非空單元格的個數。
注意這裡有一個問題,A2單元格的公式如果使用
=SUBTOTAL(3,B$2:B2)
在篩選時雖然序號沒有問題了,但是篩選的結果會出現錯誤:
工作表中使用了SUBTOTAL函數,Excel會默認把最後一行作為彙總行,所以會始終顯示,因此影響到篩選操作了。
我們只要記得在處理序號時,需要將SUBTOTAL函數的第二參數引用起始位置寫成公式所在行的上一行,再將結果減1就可以了。
4、生成間斷的序號
在下示的數據表中,會不規律的出現一些空行,要求我們在生成序號的時候自小到大排列,但是空行不顯示內容。
A2單元格輸入以下公式,向下複製:
=IF(B2="","",MAX(A$1:A1)+1)
也可以使用:
=IF(B2="","",COUNTA(B$2:B2))
第一個公式是先判斷B2是否為空值,如果B2是空值則返回空,否則計算自A1單元格開始至當前單元格上一行的最大值。
第二個公式也是先判斷B2是否為空值,如果B2是空值則返回空,否則計算B列自B2單元格開始至當前單元格的非空單元格個數。
5、合併單元格添加序號
COUNTA函數的作用是統計數據區域內不為空的單元格個數。
使用COUNTA函數,能夠實現對帶有合併單元格的表格快速添加序號。
同時選中A2:A13單元格區域,編輯欄輸入公式,按Ctrl+回車:
=COUNTA(B$2:B2)
閱讀更多 數據分析之家 的文章