實用Excel技巧分享：16000行資料自動分組編號

在之前的文章《實用Excel技巧分享：怎麼忽略隱藏列進行求和？在》中，我們了解了忽略隱藏列進行求和的方法。而今天我們來聊聊資料分組編號，介紹一下3秒鐘快速實現16000行資料自動分組編號的方法，快來學習學習！

有16000人參與“社保代扣代繳協議”，需要將每16人設定為一組，第1-16行重複編號為1、第17-32行重複編號為2、…、第15985-16000行重複編號為1000。怎麼弄？

上面是前段時間我幫朋友解決的一個真實問題，我把它稱之為資料分組重複編號。 16000行數據，編號值從1到1000。若採用輸入數字 貼上的方式處理，工作量較大，且容易出錯。基於此，我分享兩種方法給各位，3秒鐘實現16000行資料自動分組編號。

方法1：函數法

1、操作步驟

#（1）編輯「連續重複編號」公式。在A1儲存格中輸入公式：=IF(MOD(ROW(A1),16)=0,ROW(A1)/16,INT(ROW(A1)/16) 1)。如下圖所示：

註：公式中所有數字、符號、標點，都必須在「英文輸入法」狀態下輸入

#（2 ）快速選取「連續重複編號」區域。用滑鼠選點選A1儲存格；在Excel網址列輸入A16000; 按住「Shift」 鍵不放手，再按「Enter」鍵。完成以上三個步驟之後就可以快速選取需要連續重複編號的區域。如下圖所示：

（3）快速填入公式。選定「連續重複編號」區域之後，在「開始」標籤中，點選「填滿」選項卡，選擇「向下」選項，即可完成公式自動填入。 「連續重複編號」結果如下圖所示：

註：許多朋友習慣用滑鼠拖曳填充公式，這裡由於編號多達16000行，採用滑鼠拖曳填充將很耗時，故不建議使用。

2、函數解釋

公式中共運用了4個函數。我們先來看看這四個函數的各自作用。

• ROW()函數。 ROW()函數傳回該行任一儲存格所在的行號，如：ROW(A13)=13、 ROW(B13)=13。

• INT()函數。取整函數，如：INT (0.1)=0、INT (2)=2、INT (3.7)=3、INT(-1.1)=-2。即：當x≥0時，INT (x)= x值的整數部分（非四捨五入）；

  當x

• MOD()函數。求兩個數相除的餘數，如：MOD(1,16)=1、MOD(16,16)=0。當MOD(x,y)=0時，x即為y的整數倍。 （註：第一個參數為被除數，第二個參數為除數）

• IF()函數。 IF()函數有三個參數，分別是：IF(邏輯判斷表達式,結果1,結果2)，當邏輯判斷表達式成立（即為真：TRUE），IF()函數傳回結果1；當邏輯判斷表達式式不成立（即為假：FALSE），IF()函數傳回結果2。

然後我們來理解整個公式的意義。

=IF(MOD(ROW(A1),16)=0,ROW(A1)/16,INT(ROW(A1)/16) 1)

#IF第一參數MOD(ROW(A1),16)=0：判斷單元格所在行號除以16後的餘數是否等於0，也就是行號是否能被16整除。很顯然，16、32等能被16整除，餘數=0，條件成立；15、17等不能被16整除，餘數≠0，條件不成立。

IF第二參數ROW(A1)/16：當第一參數條件成立時，編號等於行號除以16的商數。例如：

A16，編號= ROW(A16)/16=16/16=1

A32，編號= ROW(A32)/16=32/16=2

#……

IF 第三參數INT(ROW(A1)/16) 1：當第一參數不成立時，編號等於行號除以16的商取整後再加1。例如：

A15，編號= INT(ROW(A15)/16) 1= INT（15/16） 1=INT( 0.9375) 1=0 1=1

A17，編號= INT(ROW(A17)/16) 1= INT（17/16） 1=INT( 1.0625) 1=1 1=2

##……

#方法2：VBA法

1、操作步驟#

（1）進入VBA編輯視窗。按下組合鍵Alt F11（或按一下「開發工具」標籤「Visual Basic」按鈕）進入Excel中的Visual Basic。

（2）在「插入」選單中選擇「模組」指令，然後在右側視窗中輸入以下程式碼：

Sub rep()
Dim i%
For i = 1 To 1000
Sheet2.Range("A" & (16 * i - 15) & ":A" & (16 * i)) = i
Next i
End Sub

（3）按F5鍵（或按一下快速工具欄中的運作按鈕）執行以上程式之後，即可在A1：A16000儲存格中快速產生連續重複編號，運算過程不到一秒鐘，如下圖所示。

2、程式碼解釋

For i = 1 To 1000：用來指定編號的取值範圍。如果編號取值是2到25，則寫成For i = 2 To 25。

Sheet2：用於指定需要編號的工作表。 sheet2並非指的是工作表的名稱，而是指Excel工作簿的第2張表（由左向右），若需要在第1張表中產生編號，只需將代碼改成sheet1即可，其他情況類推。

Range("A" & (16 * i - 15) & ":A" & (16 * i))：用於指定編號的儲存格範圍和規則，意思是從A1單元格開始到A（16 * i）單元格止每16個單元格編1個號碼。

"A"指的是需要生產編號的列號，若需要在B列或C列產生編號，則寫成「B」或「C」；

若需在某一列第m個單元格開始產生編號，只需將16 * i – 15替換成16 * i m-16、16 * i替換成16 * i m-1即可。

若需要每5個單元格編1個號，並從B1開始編號，則可以寫成Range("B" & (5 * i - 4) & ":B" & ( 5 * i))

重點回顧

• 快速選取某一區域。 用滑鼠選取備選區域左上角的儲存格（如：A1）；在Excel位址列輸入備選區域右下角的儲存格（如：B16）; 按住「Shift」 鍵不放手，再按下“Enter”鍵。完成以上三個步驟之後即可快速選定某區域。

• 巧用Excel函數是關鍵。 有許多初學者已經掌握了大量的Excel基本函數，但就是不知道怎麼樣、何時用、用哪一種。我建議大家把Excel基本函數當作我們炒菜的“素材”，把數學規律、事件內隱藏的邏輯關係當作炒菜的“工具”，多思考、勤實踐，那麼再遇到問題，就會“信手捏來」了。

相關學習推薦：excel教學

以上是實用Excel技巧分享：16000行資料自動分組編號的詳細內容。更多資訊請關注PHP中文網其他相關文章！