我们在上一篇文章《好麻烦!Excel序号输入怎样才能批量搞定?》中,介绍了 5 个简单实用的序号生成技巧,但在面对更加复杂的需求时,那5个套路可能就不管用了。

今天,就再分享 5 个高级技巧,能够让你一下子搞定很多关于表格序号的问题。例如:

排序前后,如何让序号始终不变?

筛选前后,如何让序号始终连续?

分类内部,如何自动给每一行编号?

超长的编号,如何自动批量生成?

如何生成循环的序号?

……

下面为你一一揭晓!

第一则:排序稳如狗

原本表格中的序号是从小到大按顺序排列的,但是按照其他列的数据排序以后,序号就会被打乱。就像下面标红的序号一样:

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(1)

有时候,我们会有些特殊需求,比如,让序号始终保持「1-n」的状态,方便打印。怎么办?

只需要借助一个 Row 函数就可以实现:

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(2)

图中的函数公式是:=Row(A1)

Row 函数可以返回指定单元格的行号,借助行号来生成序号是 Excel 中最常用的高级套路之一。Row,从英文单词字面上理解,就是行的意思。你记住了吗?

第二则:筛选不间断

按条件筛选数据后,不符合条件的行会被整行隐藏掉,原本连续的序号,会变得断断续续。

有些表格,需要反复筛选出某些数据出来的打印。这样就会好麻烦好麻烦呀。有没有办法设置一批动态的序号,自动忽略隐藏的行,保证序号始终连续呢?

当然可以,依然要用到函数公式。不过为了满足这么高级的需求,当然得用更加高级的函数。

这个函数,就是万能的 SUBTOTAL,看效果:

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(3)

图中的函数公式是:=SUBTOTAL(103,$B$2:B2) 。

Subtotal 就是函数界的孙猴子,想变就变!

它可以代替 11 个函数,还有 2 种计算模式(包含隐藏行、忽略隐藏行),1 个函数就能实现 2×11=22 种功能,简直要逆天。

让序号不受筛选印象,始终保持连续,是 Subtotal 最常见的一种用法。其他用法暂时不展开,如果你感兴趣,以后我们再慢慢细说。

第三则:组内编号

你有没有碰到过这样的表格呢?按类别分组,各个组中给每一行添加连续编号。

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(4)

怎么办?手工一个个输入吗?NO,NO,NO。聪明人会用这一招。

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(5)

图中的函数公式是:=IF(A2="",B1 1,1)

作为最常用函数 TOP 3 成员,IF函数几乎无表不在。如果高考也考 Excel 的话,IF 函数肯定是必考题。要读懂上面的 公式,你至少需要了解:

❶ 合并单元格中只有第一个单元格有数,其他都为空单元格;

❷ 单元格空值可以用连续的双引号 “ ” 表示什么都木有;

❸ 当左边不是空值时,说明是第一个单元格,结果等于 1,其他单元格等于上一个单元格的值加 1,依此类推,就能得到各组内部的连续序号;

❹ 数字格式 00,可以让 1 自动变成 01 。

第四则:超长编号

5553875987800001

5553875987800002

5553875987800003

5553875987800004

……

有15位以上的超长编号,直接输入后向下填充,会变成科学计数法。

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(6)

没办法,超长文本通常都要以文本格式写入才行。那就先设为文本格式,再输入吧。

可是……文本格式的数字编号,自动填充时只是复制,不会自动递增……

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(7)

难道就没有办法了吗?别忘了,我们还有表格界的超级消防队长,基础功能搞不定时,就请出函数公式,分成两部分输入,再拼合得到一起:

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(8)

图中的函数公式是:=A2&B2

别说100个,就算是10,000个,两三秒种就全部生成了!就问你爽!不!爽!?

第五则:循环序号

1234、1234 像首歌 ~ 怎么批量生成固定数量的循环序号呢?其实,不用函数公式,利用自动填充也可以做到。

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(9)

可是如果有大批量的循环序号,拖拽填充柄生成循环序号还是很麻烦。有两个万能的函数公式可以派上用场:

excel公式与函数的正确计算方法(一下子搞定Excel序号的5个函数公式)(10)

图中的两个函数公式分别是:

=MOD(ROW(A1) 2,3) 1

=INT((ROW(A1) 3)/4)

其中 Mod 函数为求余函数,常用来生成循环序数;INT 为取整函数,常用来指定数量递增的序数。

光说不练假把式,马上打开你的 Excel 表动手试一试吧!

,