實(shí)用Excel技巧分享：16000行數(shù)據(jù)自動(dòng)分組編號(hào)

手機(jī)如何做表格：點(diǎn)擊查看

有16000人參與“社保代扣代繳協(xié)議”，需要將每16人設(shè)定為一組，第1-16行重復(fù)編號(hào)為1、第17-32行重復(fù)編號(hào)為2、…、第15985-16000行重復(fù)編號(hào)為1000。怎么弄？

上面是前段時(shí)間我?guī)椭笥呀鉀Q的一個(gè)真實(shí)問(wèn)題，我把它稱(chēng)之為數(shù)據(jù)分組重復(fù)編號(hào)。16000行數(shù)據(jù)，編號(hào)值從1到1000。如果采用輸入數(shù)字+粘貼的方式處理，工作量較大，且容易出錯(cuò)。基于此，我分享兩種方法給各位，3秒鐘實(shí)現(xiàn)16000行數(shù)據(jù)自動(dòng)分組編號(hào)。

方法1：函數(shù)法

1、操作步驟

（1）編輯“連續(xù)重復(fù)編號(hào)”公式。在A1單元格中輸入公式：=IF(MOD(ROW(A1),16)=0,ROW(A1)/16,INT(ROW(A1)/16)+1)。如下圖所示：

注：公式中所有數(shù)字、符號(hào)、標(biāo)點(diǎn)，都必須在“英文輸入法”狀態(tài)下輸入

（2）快速選定“連續(xù)重復(fù)編號(hào)”區(qū)域。用鼠標(biāo)選點(diǎn)擊A1單元格；在Excel地址欄輸入A16000; 按住“Shift” 鍵不松手，再按“Enter”鍵。完成以上三個(gè)步驟之后就可以快速選定需要連續(xù)重復(fù)編號(hào)的區(qū)域。如下圖所示：

（3）快速填充公式。選定“連續(xù)重復(fù)編號(hào)”區(qū)域之后，在“開(kāi)始”選項(xiàng)卡中，點(diǎn)擊“填充”選項(xiàng)卡，選擇“向下”選項(xiàng)，即可完成公式自動(dòng)填充。“連續(xù)重復(fù)編號(hào)”結(jié)果如下圖所示：

注：很多朋友習(xí)慣用鼠標(biāo)拖動(dòng)填充公式，此處由于編號(hào)多達(dá)16000行，采用鼠標(biāo)拖動(dòng)填充將很耗時(shí)，故不推薦使用。

2、函數(shù)解釋

公式中共運(yùn)用了4個(gè)函數(shù)。我們先來(lái)看看這四個(gè)函數(shù)的各自作用。

ROW()函數(shù)。ROW()函數(shù)返回該行任意一個(gè)單元格所在的行號(hào)，如：ROW(A13)=13、 ROW(B13)=13。

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

當(dāng)x<0時(shí)，INT (x)=（x值整數(shù)部分的絕對(duì)值+1）乘以-1。

MOD()函數(shù)。求兩個(gè)數(shù)相除的余數(shù)，如：MOD(1,16)=1、MOD(16,16)=0。當(dāng)MOD(x,y)=0時(shí)，x即為y的整數(shù)倍。（注：第一個(gè)參數(shù)為被除數(shù)，第二個(gè)參數(shù)為除數(shù)）

IF()函數(shù)。IF()函數(shù)有三個(gè)參數(shù)，即：IF(邏輯判斷表達(dá)式,結(jié)果1,結(jié)果2)，當(dāng)邏輯判斷表達(dá)式成立（即為真：TRUE），IF()函數(shù)返回結(jié)果1；當(dāng)邏輯判斷表達(dá)式不成立（即為假：FALSE），IF()函數(shù)返回結(jié)果2。

然后我們來(lái)理解整個(gè)公式的含義。

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

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

IF第二參數(shù)ROW(A1)/16：當(dāng)?shù)谝粎?shù)條件成立時(shí)，編號(hào)等于行號(hào)除以16的商。例如：

A16，編號(hào)= ROW(A16)/16=16/16=1

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

……

IF 第三參數(shù)INT(ROW(A1)/16)+1：當(dāng)?shù)谝粎?shù)不成立時(shí)，編號(hào)等于行號(hào)除以16的商取整后再加1。例如：

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

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

……

方法2：VBA法

1、操作步驟

（1）進(jìn)入VBA編輯窗口。按組合鍵Alt+F11（或者單擊“開(kāi)發(fā)工具”選項(xiàng)卡“Visual Basic”按鈕）進(jìn)入Excel中的Visual Basic。

（2）在“插入”菜單中選擇“模塊”命令，然后在右側(cè)窗口中輸入以下代碼：

（3）按F5鍵（或者單擊快速工具欄中的運(yùn)行按鈕）運(yùn)行以上程序之后，即可在A1：A16000單元格中快速生成連續(xù)重復(fù)編號(hào)，運(yùn)算過(guò)程不到一秒鐘，如下圖所示。

2、代碼解釋

For i = 1 To 1000：用于指定編號(hào)的取值范圍。如果編號(hào)取值是2到25，則寫(xiě)成For i = 2 To 25。

Sheet2：用于指定需要編號(hào)的工作表。sheet2并非指的是工作表的名稱(chēng)，而是指Excel工作簿的第2張表（從左向右），若需要在第1張表中生成編號(hào)，只需將代碼改成sheet1即可，其他情況類(lèi)推。

Range("A" & (16 * i - 15) & ":A" & (16 * i))：用于指定編號(hào)的單元格范圍和規(guī)則，意思是從A1單元格開(kāi)始到A（16 * i）單元格止每16個(gè)單元格編1個(gè)號(hào)。

"A"指的是需要生產(chǎn)編號(hào)的列號(hào)，若需要在B列或C列生成編號(hào)，則寫(xiě)成“B”或者“C”；

若需在某一列第m個(gè)單元格開(kāi)始生成編號(hào)，則只需將16 * i – 15替換成16 * i+m-16、16 * i替換成16 * i+m-1即可。

若需要每5個(gè)單元格編1個(gè)號(hào)，并從B1開(kāi)始編號(hào)，則可以寫(xiě)成Range("B" & (5 * i - 4) & ":B" & (5 * i))

重點(diǎn)回顧

快速選定某區(qū)域。用鼠標(biāo)選定備選區(qū)域左上角的單元格（如：A1）；在Excel地址欄輸入備選區(qū)域右下角的單元格（如：B16）; 按住“Shift” 鍵不松手，再按“Enter”鍵。完成以上三個(gè)步驟之后即可快速選定某區(qū)域。

巧用Excel函數(shù)是關(guān)鍵。有很多初學(xué)者已經(jīng)掌握了大量的Excel基本函數(shù)，但是就是不知道怎么樣、何時(shí)用、用哪種。我建議大家把Excel基本函數(shù)當(dāng)作我們炒菜的“素材”，把數(shù)學(xué)規(guī)律、事件內(nèi)隱藏的邏輯關(guān)系當(dāng)作炒菜的“工具”，多思考、勤實(shí)踐，那么再遇到問(wèn)題，就會(huì)“信手捏來(lái)”了。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。