《3秒钟实现16000行数据自动分组编号【Excel教程】》系傻大方资讯网小编转载，希望对您有所帮助。

关注

excel教程

，置顶公众号

有

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

个函数。我们先来看看这四个函数的各自作用。

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

）在

“

插入

”

菜单中选择

“

模块

”

命令，然后在右侧窗口中输入以下代码：

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

基本函数当作我们炒菜的

“

素材

”

，把数学规律、事件内隐藏的逻辑关系当作炒菜的

“

工具

”

，多思考、勤实践，那么再遇到问题，就会

“

信手捏来

”

了。

温馨提醒：本文配套的练习课件请扫下面二维码加入QQ群：316492581下载。

如果您因为工作所需要使用Excel，不妨关注部落窝教育的

《一周Excel直通车》

视频课或者

《Excel极速贯通班》

直播课系统学习。

主讲老师： 滴答

 

Excel技术大神，资深培训师；

课程粉丝100万+；

课程预览