1.4 使用公式填充序号、编号
不使用公式填充的序号或编号是固化不变的。如果要让序号或编号随着其他数据的产生而动态产生，且不因删除行而缺失，应该怎么办呢？要实现这种自动化的效果，可以通过使用函数公式来实现。至于想要筛选后得到连续编号，请参阅4.3.1节的内容。
1.4.1 填充无空行数据的连续编号
在文件“第1章 数据填充”中插入一个工作表，将该工作表命名为“使用公式填充序号”。在工作表中建立一个数据表，如图1-30所示。
图1-30 待编号的无空行的数据
表中，C列的数据没有空行，要在A列编写自动化的自然数序号，在B列编写形如“AK47-001”的编号，其中“AK47”为固定部分，应该如何实现呢？
在A2、B2单元格分别输入如下函数公式：
填充和验证公式的操作过程如下：
（1）选择A2:B2区域，将光标移到B2单元格右下角，当光标变成黑色十字时，拖动鼠标左键向下移动，当到达指定单元格时，比如B6单元格，松开鼠标，完成公式填充。
（2）为检验A列、B列的序号和编号是否自动生成、动态变化，删除第5行。“孙七”的序号自动变为“4”，编号自动变为“AK47-004”。
操作过程及效果如图1-31所示。
图1-31 删除行后序号和编号变化的效果
为便于介绍下一个例子，按“Ctrl+Z”组合键以撤消删除的操作。
【函数公式解析】
在本例第一个公式中，IF函数对值和期待值进行逻辑比较，根据C2单元格有无数据决定是否生成序号。具体语法为：
logical_test必需。表示计算结果为TRUE或FALSE的任意值或表达式。
value_if_true必需。为TRUE时返回的值。
value_if_false可选。为FALSE时返回的值。
IF函数最简单的形式为：
如果（内容为TRUE，则执行某些操作，否则就执行其他操作）
因此，IF语句可能有两个结果。第一个结果是比较结果为TRUE，第二个结果是比较结果为FALSE。Excel允许嵌套最多64个不同的IF函数。
1.4.2 填充有空行数据的连续编号
有时数据有空行，我们也想要实现序号随着其他数据的产生而产生且删除行不影响序号的目的。在“使用公式填充序号”工作表中建立一个数据表，如图1-32所示。
图1-32 待编号的有空行的数据
表中，第5行为空行，要在E列编写自动化的自然数序号，在F列编写形如“AK47001”的编号，其中“AK47”为固定部分，应该如何实现呢？
在E2、F2单元格分别输入如下公式：
填充和验证公式的过程为：
（1）选择E2:F2区域，将光标移到F2单元格右下角，当光标变成黑色十字时，拖动鼠标左键向下移动，到达指定单元格时，比如F6单元格，松开鼠标，完成公式填充。
（2）为检验E列、F列的序号和编号是否自动生成、动态变化，删除第4行。“孙权”的序号自动变为“3”，编号自动变为“AK47003”了。
操作过程及效果如图1-33所示。
图1-33 删除行后编号变化的效果
为便于介绍下一个例子，按“Ctrl+Z”组合键以撤消删除。
【函数公式解析】
在本例第一个公式中，COUNTA函数计算范围中不为空的单元格的个数。具体语法为：
value1必需。表示要计数的值的第一个参数。
value2，…可选。表示要计数的值的其他参数，最多可包含255个参数。
COUNTA函数只有一个参数，引用区域为$G$2:G2，起始单元格$G$2为绝对引用，不会因公式的填充而变化，结束单元格G2为相对引用，会因公式的填充而变化。
1.4.3 规避特殊数字填充编号
有些人避讳使用与“4”和“7”这样似乎不吉利的数字有关的编号、卡号。自动填充编号时，如何才能规避呢？在“使用公式填充序号”工作表中建立一个卡号表，如图1-34所示。
图1-34 待编号的卡号表
表中，如果在J2单元格填入卡号的起始号“2016123990”，其后的卡号则可以自动填充，而且能规避“4”和“7”这两个数字，如何才能实现这种想法呢？通过巧妙地嵌套SUBSTITUTE函数，结合Excel的自动填充功能，就可以很好地解决这个问题。
首先在J2单元格填入卡号的起始号“2016123990”。然后在J3单元格输入如下公式：
接着鼠标左键向下拖动J3单元格的填充柄到需要的地方。效果如图1-35所示。
图1-35 填充卡号后的效果
【函数公式解析】
在本例公式中，SUBSTITUTE函数用于在文本字符串中使用新文本替换老文本。具体语法为：
text（文本）必需。需要替换其中字符的文本，或对含有文本的单元格的引用。
old_text（老文本）必需。表示需要替换的文本。
new_text（新文本）必需。用于替换old_text的文本。
instance_num可选。指定要用new_text替换old_text的事件。如果指定了instance_num，则只有满足要求的old_text被替换。否则文本中出现的所有old_text都会更改为new_text。
本例，在内层函数式“SUBSTITUTE（J2+1，4，5）”中，将数字串中的数字“4”替换为数字“5”。而外层SUBSTITUTE函数则将内层函数计算值中的“7”替换为数字“8”。经过SUBSTITUTE函数的两重过滤之后，单元格中的编号就不再出现数字“4”或“7”。双减号“--”是将文本型数字强制转换为数值型数字。
如果还想规避其他数字，可以依样画葫芦，继续嵌套SUBSTITUTE函数。本例只使用到SUBSTITUTE函数前三个参数，而第4个参数省略没有用。假设在本例中，我们规定号码中数字“4”或“7”不能在第几次出现，其余次数则可以出现，就只须在函数的最后再加一个参数，注意用半角逗号隔开。
1.4.4 填充连续的英文字母
在“使用公式填充序号”工作表中建立一个数据表，如图1-36所示。
图1-36 待按英文字母编号的数据
表中，要对M列编写英文大写字母，该如何操作呢？
在M2单元格输入如下公式：
填充和验证公式的过程为：
（1）选择M2单元格，将光标移动到M2单元格右下角，当光标变成黑色十字时，拖动鼠标左键向下移动，到达指定单元格时，比如M5单元格，松开鼠标，完成公式填充。
（2）为检验M列编号是否自动生成、动态变化，删除第4行，“杨贵妃”的编号自动变为“C”。
操作过程及效果如图1-37所示。
图1-37 填充连续英文字母及删除行后编号变化的效果
【函数公式解析】
在本例公式中，CHAR函数将数字编码转换为相应的字符，如果要用小写字母填充，请将式中的“64”更改为“96”。在计算机字符编码中，特定的编码数字和特定的字符是对应的。
CHAR函数的语法为：
number必需。介于1到255的数字，指定所需的字符。使用的是当前计算机字符集中的字符。