Excel自动填充如何用函数规避特殊数字（如4和7）

Excel自动填充利用函数规避特殊数字的方法：基础操作

Excel的自动填充功能很高效，但有时我们不希望看到某些数字，例如在生成楼层、会员卡等序号的过程中，许多人不希望看到4和7这样的数字。其实，通过巧妙地利用SUBSTITUTE函数的嵌套使用，可以很好地解决这个问题。

首先打开一张空白Excel表，在A1单元格中填入数字1。接下来定位光标到A2单元格，在公式栏内输入如下公式：

=SUBSTITUTE(SUBSTITUTE(A1+1,4,5),7,8)

随后向下拖动A2单元格右下角的黑色方块填充柄，生成的所有数据中就不会出现4和7这两个数。

Excel自动填充利用函数规避特殊数字的方法：原理详解

在“=SUBSTITUTE(SUBSTITUTE(A1+1,4,5),7,8)”这个由两个SUBSTITUTE函数嵌套的公式中，里边一层函数“SUBSTITUTE(A1+1,4,5)”跳过出现数字4的单元格填充为5，外面一层函数在第一个函数过滤之后，再对7进行过滤并替换为8。经过两重过滤之后，无论是单元格中出现4还是7，都会被函数中指定的后续数字5或8代替，于是4和7就不会再出现了。

如果再解释得明白一点，“SUBSTITUTE(A1+1,4,5)”函数的作用就是判断A1+1（也就是A2）单元格中自动填充的数据有没有4，若有就用5代替。

扩展应用：规避更多数字及生成幸运会员卡号

如果你不希望出现另一个数字（比如2），可以在外面再包一层SUBSTITUTE函数，例如：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+1,4,5),7,8),2,3)。

如果你开店希望给会员发放不带4和7的“幸运会员卡”号，只需修改起始会员卡号（例如设置2016123890为起始号），然后进行后续数据的填充即可。

SUBSTITUTE函数语法与第4参数

SUBSTITUTE函数语法描述：SUBSTITUTE(text,old_text,new_text,instance_num)

参数意义如下：

Text：为需要替换其中字符的文本，或对含有文本的单元格的引用。

Old_text：为需要替换的旧文本。

New_text：用于替换old_text的文本。

Instance_num：为一数值，用来指定以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则将用new_text替换Text中出现的所有old_text。

在上面的例子中只用了前三个参数。如果规定号码中第几次出现的4或7不能有，其余可以有，只需在函数最后再加一个参数（用半角逗号隔开）。

总结

上述只是SUBSTITUTE函数应用的冰山一角，更多的使用方法需要你在实际应用中灵活地与其他函数或公式组合使用。