超高效!一下子搞定Excel序號的5個函數公式~學會3個,你已是大神!
我們在上一篇文章《好麻煩!Excel序號輸入怎樣才能批量搞定?》中,介紹了 5 個簡單實用的序號生成技巧,但在面對更加複雜的需求時,那5個套路可能就不管用了。
今天,就再分享 5 個高級技巧,
排序前後,如何讓序號始終不變?
篩選前後,如何讓序號始終連續?
分類內部,如何自動給每一行編號?
超長的編號,如何自動批量生成?
如何生成迴圈的序號?
……
下麵為你一一揭曉!
第一則:排序穩如狗原本表格中的序號是從小到大按順序排列的,但是按照其他列的資料排序以後,序號就會被打亂。就像下面標紅的序號一樣:
有時候,我們會有些特殊需求,比如,讓序號始終保持「1-n」的狀態,方便列印。怎麼辦?
只需要借助一個 Row 函數就可以實現:
圖中的函數公式是:=Row(A1)。
Row 函數可以返回指定儲存格的行號,借助行號來生成序號是 Excel 中最常用的高級套路之一。Row,從英文單詞字面上理解,
按條件篩選資料後,不符合條件的行會被整行隱藏掉,原本連續的序號,會變得斷斷續續。
有些表格,需要反復篩選出某些資料出來的列印。這樣就會好麻煩好麻煩呀。有沒有辦法設置一批動態的序號,自動忽略隱藏的行,保證序號始終連續呢?
當然可以,依然要用到函數公式。不過為了滿足這麼高級的需求,當然得用更加高級的函數。
這個函數,就是萬能的 Subtotal,看效果:
圖中的函數公式是:=SUBTOTAL(103,$B$2:B2) 。
Subtotal 就是函數界的孫猴子,想變就變!
它可以代替 11 個函數,還有 2 種計算模式(包含隱藏行、忽略隱藏行),1 個函數就能實現 2×11=22 種功能,簡直要逆天。
讓序號不受篩選印象,始終保持連續,是 Subtotal 最常見的一種用法。
你有沒有碰到過這樣的表格呢?按類別分組,各個組中給每一行添加連續編號。
怎麼辦?手工一個個輸入嗎?NO,NO,NO。聰明人會用這一招。
圖中的函數公式是:=IF(A2="",B1+1,1)。
作為最常用函數 TOP 3 成員,IF函數幾乎無表不在。如果高考也考 Excel 的話,IF 函數肯定是必考題。要讀懂上面的 公式,你至少需要瞭解:
❶ 合併儲存格中只有第一個儲存格有數,其他都為空儲存格;
❷ 儲存格空值可以用連續的雙引號 “ ” 表示什麼都木有;
❸ 當左邊不是空值時,說明是第一個儲存格,結果等於 1,其他儲存格等於上一個儲存格的值加 1,依此類推,就能得到各組內部的連續序號;
❹ 數位格式 00,可以讓 1 自動變成 01 。
第四則:超長編號5553875987800001
5553875987800002
5553875987800003
5553875987800004
……
有15位以上的超長編號,直接輸入後向下填充,會變成科學計數法。
沒辦法,超長文本通常都要以文本格式寫入才行。那就先設為文本格式,再輸入吧。
可是……文本格式的數位編號,自動填充時只是複製,不會自動遞增……
難道就沒有辦法了嗎?別忘了,我們還有表格界的超級消防隊長,基礎功能搞不定時,就請出函數公式,分成兩部分輸入,再拼合得到一起:
圖中的函數公式是:=A2&B2。
別說100個,就算是10,000個,兩三秒種就全部生成了!就問你爽!不!爽!?
第五則:迴圈序號1234、1234 像首歌 ~ 怎麼批量生成固定數量的迴圈序號呢?其實,不用函數公式,利用自動填充也可以做到。
可是如果有大批量的迴圈序號,拖拽填充柄生成迴圈序號還是很麻煩。有兩個萬能的函數公式可以派上用場:
圖中的兩個函數公式分別是:
=MOD(ROW(A1)+2,3)+1
=INT((ROW(A1)+3)/4)
其中 Mod 函數為求餘函數,常用來生成迴圈序數;INT 為取整函數,常用來指定數量遞增的序數。
光說不練假把式,馬上打開你的 Excel 表動手試一試吧!
結果等於 1,其他儲存格等於上一個儲存格的值加 1,依此類推,就能得到各組內部的連續序號;❹ 數位格式 00,可以讓 1 自動變成 01 。
第四則:超長編號5553875987800001
5553875987800002
5553875987800003
5553875987800004
……
有15位以上的超長編號,直接輸入後向下填充,會變成科學計數法。
沒辦法,超長文本通常都要以文本格式寫入才行。那就先設為文本格式,再輸入吧。
可是……文本格式的數位編號,自動填充時只是複製,不會自動遞增……
難道就沒有辦法了嗎?別忘了,我們還有表格界的超級消防隊長,基礎功能搞不定時,就請出函數公式,分成兩部分輸入,再拼合得到一起:
圖中的函數公式是:=A2&B2。
別說100個,就算是10,000個,兩三秒種就全部生成了!就問你爽!不!爽!?
第五則:迴圈序號1234、1234 像首歌 ~ 怎麼批量生成固定數量的迴圈序號呢?其實,不用函數公式,利用自動填充也可以做到。
可是如果有大批量的迴圈序號,拖拽填充柄生成迴圈序號還是很麻煩。有兩個萬能的函數公式可以派上用場:
圖中的兩個函數公式分別是:
=MOD(ROW(A1)+2,3)+1
=INT((ROW(A1)+3)/4)
其中 Mod 函數為求餘函數,常用來生成迴圈序數;INT 為取整函數,常用來指定數量遞增的序數。
光說不練假把式,馬上打開你的 Excel 表動手試一試吧!