Excel的自动填充如何用函数规避特殊数字Excel自动填充利用函数规避特殊数字的方法
Excel的自动填充功能很高效,但有时我们不希望看到某些数字,例如我们在生成楼层、会员卡等序号的过程中,许多人不希望看到4和7这样的数字。那么如何避免呢?
其实,通过巧妙地利用SUBSTITUTE函数的嵌套使用,可以很好地解决这个问题。我们以实际操作来具体说明吧。我们先生成一个不带4和7的列表。
首先打开一张空白Excel表,在A1单元格中填入数字1。
接下来定位光标到A2单元格,在公式栏内输入如下公式:
=SUBSTITUTE(SUBSTITUTE(A1+1,4,5),7,8)
随后向下拖动A2单元格右下角的黑色方块填充柄,你看到什么了?是不是生成的所有数据中唯独没有4和7这两个数呢?
那么,这个神奇的SUBSTITUTE函数是如何做到排除4和7这两个数字的呢?其实只要掌握了该函数的基本参数的含义,就全明白了。
在“=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(A1+1,4,5),7,8)),2,3”就可以了。
学会了这个简单的例子,如果你开店当老板了,希望给你的会员发放会员卡,但知道大家都不喜欢带4和7的数字,那么如何自动生成不带4和7的“幸运会员卡”号呢?照猫画虎,只需在上述例子的基础上,修改起始会员卡号,例如设置一个2016123890的会员卡起始号,之后进行后续数据的填充,这样生成的会员卡就都是“幸运会员”卡了。
上述例子是利用了SUBSTITUTE函数对文本中字符的替换功能,作为替换功能的该函数,其实还具有一个更精细的第4选项,它可以指定替换第几次出现的字符,而不是一概替换。请看该函数的语法描述:
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个参数省略没有用。假设在上述的案例中,我们规定号码中第几次出现的4或7不能有,其余都可以有,那么,只需在函数的最后再加一个参数就可以了。注意用半角逗号隔开。
上述只是SUBSTITUTE函数应用的冰山一角,更多的使用方法还要靠你在实际应用中,灵活地与其他函数或公式组合应用了。
百度翻译是什么百度翻译是一项免费的在线翻译服务,支持中文和英文中文和日文之间的文本网页翻译功能,只需输入您想要翻译的文本或者网页地址,即可轻松获得翻译结果。公司简介百度,全球最大的中文搜索引擎最
百度搜藏与操作系统中的收藏夹有何不同1)操作系统中的ldquo收藏夹rdquo只能保存网页链接而百度搜藏能同时保存收藏网页的全文。即使您搜藏的网页在互联网上已不存在,你也能随时在百度搜藏通过搜藏快照查看它。2)操作系
百度使用搜藏有哪些限制百度使用搜藏有以下限制1)标题长度最少1字节,最长76字节2)描述长度最长240字节3)分类长度最长20字节4)分类名中不能含有下列符号,5)url长度最长255字节6)相同的ur
百度搜藏分类是什么百度搜藏分类是比传统的目录更灵活更方便的组织搜藏的方式。您只需在添加或编辑搜藏条目时为其填写一个或多个分类,条目就会在页面左侧对应进入对应的分类。将您的搜藏条们分类能让它们更为有序
如果发现百度法律信息内容有误怎么办如果您发现百度法律信息某些内容有误,您可以通过建议与反馈告知百度,百度会及时安排核实与更正。百度法律搜索是百度与北大英华公司合作推出的针对法律方面的专业搜索,提供了自建国以来中央和
百度搜藏如何享用他人的收藏如果有很多用户同时收藏同一个网页,系统会自动地将这些用户聚合在一起,在该网页后面产生一个ldquo共x人搜藏rdquo的链接。点击该链接,您可以看到与您兴趣相同的其他网友点击用户名
百度搜藏如何设置属性百度搜藏属性设置登陆后,在ldquo我的搜藏rdquo页面右上方点击ldquo搜藏设置rdquo,弹出一个搜藏的设置窗口。在基本设置中,可以按照您的喜好选择,主要内容有1登录后默认
关于百度法律搜索有否有什么特殊的技巧百度法律搜索的使用技巧与网页搜索一致。百度法律搜索是百度与北大英华公司合作推出的针对法律方面的专业搜索,提供了自建国以来中央和地方的各项法律法规条文法律词语解释等信息,为您查找相关
百度翻译怎么看翻译结果输入需要翻译的文本,点击翻译按钮后,翻译结果将为您展现在输入框右侧的输出部分。百度翻译是一项免费的在线翻译服务,支持中文和英文中文和日文之间的文本网页翻译功能,只需输入您想要翻译的
百度空间如何制作我的照片秀在您的空间主页上,将您空间的照片通过Flash效果进行动态轮播,同时还能添加各种样式的背景边框和动画特效。1最新照片模块自动播放您空间中最新上传的最多30张照片。添加ldquo设置
我的百度空间地址是什么登录百度空间后,打开主页页面,此时浏览器中显示的地址为您的百度空间地址。您可以把它记下来,复制给您的好友用于添加链接或者直接在浏览器中输入即可直接访问您的百度空间。百度空间是属于您