有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:0行,採用鼠標拖動填充將很耗時,故不推薦使用。
2.函數解釋
公式中共運用了4個函數。我們先來看看這四個函數的各自作用。
l ROW()函數。ROW()函數返回該行任意一個單元格所在的行號,如:ROW(A13)=13、 ROW(B13)=13。
l INT()函數。取整函數,如:INT (0.1)=0、INT (2)=2、INT (3.7)=3、INT(-1.1)=-2。即:當x≥0時,INT (x)= x值的整數部分(非四捨五入);
當x<0時,INT (x)=(x值整數部分的絕對值+1)乘以-1。
l MOD()函數。求兩個數相除的餘數,如:MOD(1,16)=1、MOD(16,16)=0。當MOD(x,y)=0時,x即為y的整數倍。(注:第一個參數為被除數,第二個參數為除數)
l 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)在“插入”菜單中選擇“模塊”命令,然後在右側窗口中輸入以下代碼:
ub rep()
Dim i%
For i = 1 To 1000
Sheet2.Range("A" & (16 * i - 14) & ":A" & (16 * i+1)) = i
Next i
End Sub
(3)按F5鍵(或者單擊快速工具欄中的運行按鈕
)運行以上程序之後,即可在A1:A16000:0單元格中快速生成連續重複編號,運算過程不到一秒鐘,如下圖所示。
2.代碼解釋
For i = 1 To 1000:用於指定編號的取值範圍。如果編號取值是2到25,則寫成For i = 2 To 25。
Sheet2:用於指定需要編號的工作表。sheet2並非指的是工作表的名稱,而是指Excel工作簿的第2張表(從左向右),若需要在第1張表中生成編號,只需將代碼改成sheet1即可,其他情況類推。
Range("A" & (16 * i - 14) & ":A" & (16 * i+1)):用於指定編號的單元格範圍和規則,意思是從A1單元格開始到A(16 * i)單元格止每16個單元格編1個號。
"A"指的是需要生產編號的列號,若需要在B列或C列生成編號,則寫成“B”或者“C”;
若需在某一列第m個單元格開始生成編號,則只需將16 * i – 14替換成16 * i+m-16; 16 * i+1替換成16 * i+m-1即可。
若需要每5個單元格編1個號,並從B1開始編號,則可以寫成Range("B" & (5 * i - 4) & ":B" & (5 * i))