Google 試算表、 Excel 自動編號的 4 種函數教學,與案例範本下載



最近因為要做一些試算表上的資料整理、工作切分,所以有了「連續遞增編號」的需求,例如把大量的名單加上特定編號,或是把影片腳本的分鏡加上編號等等。雖然試算表左方都有「列」的預設編號,但那通常不是我們需要的項目編號,因為還要去掉像是開頭的欄位名稱、說明格等等。而且最好能自動化,即使我改動列的順序也都能自動重新編號。

於是就研究了一下, Google 試算表、 Excel 上有沒有什麼「連續自動編號」的函數公式,可以自動幫名單項目加上 2101、 2102、 2103 這樣的編碼,或是幫腳本加上像是分鏡1、分鏡2、分鏡3,這樣的連續遞增代號。

而且當我拖曳移動列的順序,或是插入、刪減某一列時,連續編號可以自動處理。

最後我找到了四種可用的函數公式,今天這篇文章一起整理分享給需要的朋友,除了分享操作教學外,也分析 4 種函式應用上的優缺點。




當然,也還是有一個簡單的方法,就是利用「自動填滿」的功能。例如我先打上分鏡1、分鏡2,圈選這兩格,在圈選處右下方的藍色小點處往下拉,就能把後面一整排的數值變成分鏡3、分鏡4、分鏡5等等。

這個方法雖然簡單好用,但是遇到項目很多時,或是遇到常常要移動、插入、刪除項目時,就變成要常常手動重新編號,還是麻煩。





函式一:ROW 簡單易用統計連續編號

最常用來做連續自動編號的函數公式,應該就是「 ROW 」, ROW 的功能是把指定的「列號」(最左方預設的列編號)輸入在儲存格中。例如下圖這樣。

=ROW() 

那麼這個儲存格就會自動輸入左邊的列號 3 ,當然,只是這樣就沒有意義了。




因為這邊雖然是第 3 列,但實際要開始的編號是 1  ,所以我可以用下面的函數公式來算出想要的編號:

=ROW()-2

第 3 列減去 2 變成編號 1 ,這樣一來,這邊就會是從 1  開始編號,後面只要自動填滿同樣的公式,就會變成自動連續編號了。




利用 ROW 的這個函數公式來編號,優缺點如下:

  • 優點:
    • 可以往上、往下拖曳移動列,都可以自動重新編號。
    • 可以隨時插入、刪減某一列,同樣可以自動重新編號,只是增加某一列時,要手動複製函數公式過去。
  • 缺點
    • 但是中間不能插入空白列(有時候想要做為區隔),因為每一列都會繼續遞增數字,這樣數字就有錯。
      • 除非去改數值,例如後面的數值開始要多減 1 列。





函式二:TEXT 搭配 ROW 可以加上文字數字規則

如果說你需要的不只是自動數字編號,還想要加上一些文字規則,例如變成 2001、 2002 ,或是變成分鏡01、分鏡 02 。那麼可以結合下面的函數公式:

=TEXT(ROW()-2 ,"分鏡00")

利用 TEXT 這個函數,確定文字、數字的規則,就可以自動帶入。





函式三:ARRAYFORMULA 搭配 ROW 可以一次產生整排編號

前面的 ROW 公式有個麻煩處,就是要手動複製公式到新增加上的列上面。如果說資料量很大,或是常常要新增列的時候,就會多了很多手動的步驟。

這時候可以搭配 ARRAYFORMULA 這個函數公式,跟 ROW 結合在一起使用:

=arrayformula(ROW(A3:A18)-2)

這個函式可以一次自動產生一整排數字編號,以上述公式來說的意思就是:「根據 A3 到 A18 的每個列號 -2 後,自動產生所有編號。」




這個公式對於一次生成大量自動連續編號來說,可以節省很多時間,他的優缺點為:

  • 優點:
    • 讓下方自動展開所有數字,不用複製或填滿
    • 而且插入新的一行、刪減一行時,都會自動重新編號
    • 往上、往下移動都可以自動重新編號,省去麻煩動作
  • 缺點
    • 只是完全沒辦法插入空白列(想要區隔用時),因為也會自動編號





函式四:COUNTA 可以跳過空白標題列繼續編號

於是可以發現,前面幾種方法的一個共同缺點是,如果想要在自動編號的列中間,插入一些「空白列」,例如當做標題區隔用途。

那麼如果插入空白列、小標題列是必須的時候,除了上述公式再手動修改,還可以試試看另外一個函式:

=COUNTA($B$3:B3) 

先在第一個要開始自動編號的儲存格輸入上述公式,然後複製填滿到下面其他儲存格即可。

這個函數的用途是:「計算指定範圍內有幾個儲存格有數值,也可以說是統計指定欄位有幾個項目,並算出這一項是第幾個項目。」




例如下面這個公式的意思就會是,從 B3 開始到 B8 這 6 個儲存格中,有五格有資料(B欄位只有五個有內容文字),所以 A8 這一格就自動統計出 5 這個自動編號。

後面也就依此類推。




於是, COUNTA 這個函式就可以幫助我們「跳過故意留空的儲存格,不要計算編號」。

但是他也依然有優缺點:

  • 優點
    • 中間可以插入空白列,不會累計空白數值的編號
    • 可以在修改過程隨意插入、刪減任何一列,但是增加某一列時,要手動複製函數公式過去
  • 缺點
    • 拖曳移動列時,往下移動會自動重新統計,但往上移動需要修改數值才會正確



以上,就是我整理的四種可以在 Google 試算表、 Excel 中建立自動連續編號的函數公式教學,提供大家參考。

我也提供一份 Google 試算表範例:「電腦玩物:試算表自動編號教學範例」,想參考的朋友點擊連結,可以直接複製一份到自己的 Google 雲端硬碟去練習操作。

我的電子郵件是 esorhjy@gmail.com ,如果你有任何關於時間管理、提升工作效率的問題,歡迎寫信跟我討論。或是訂閱「電腦玩物電子報」。

也跟大家介紹我開設的一系列時間管理、筆記術、數位工具相關課程相關書籍,歡迎參考。

有興趣的朋友還可以延伸參考:
(歡迎社群分享。但全文轉載請來信詢問,禁止修改上述內文,禁止商業使用,並且必須註明來自電腦玩物原創作者 esor huang 異塵行者,及附上原文連結:Google 試算表、 Excel 自動編號的 4 種函數教學,與案例範本下載

留言

張貼留言

相關文章:

這個網誌中的熱門文章

內建 Windows 10 螢幕錄影功能:用遊戲列錄製軟體教學影片

只需準備一本空白筆記本,讓年度計畫有效實現

全家沖印、 7-11 列印照片誰好用?立可得與 ibon教學+比較表

AnyDesk 最快的遠端桌面軟體,免費免安裝中文教學

Draw.io 中文線上製作流程圖首選!專業水準完全免費

pyTranscriber 影片自動上字幕免費軟體!10分鐘搞定1小時影片

ImTranslator 最好用中英對照翻譯利器,也能翻譯 Evernote 外文工作筆記