小试循环引用:提取数字、字母、汉字!

你好,我是刘卓。欢迎来到我的公号,excel函数解析。昨天分享了循环引用的概念、运算原理(迭代运算)以及2个案例,今天再来分享几个案例,展示一下循环引用的作用。
-01-

提取数字、字母和汉字

1).提取数字

如下图所示,A列的单元格中是一些字符串,现在要将数字提取出来,放在B列相应的单元格中。只需勾选开关,即可得到结果。

首先在excel选项中启用迭代计算,并设置最多迭代次数为100(这里主要看字符串的长度)。

然后从【开发工具】中插入【复选框】,在B1单元格拖出一个复选框,改名为“开关”,并设置控件格式,单元格链接为A1,这样启用开关就做好了。

再在C1单元格输入公式=IF(A1,C1+1,0),作为计数器。

最后,在B4单元格输入公式=IF(A$1*(A4<>""),B4&TEXT(MID(A4,C$1,1),"0;;0;"),""),下拉填充。

当勾选开关时,excel会自动执行迭代计算,得到我们要的结果。

这里简单说一下公式的思路:

第一次迭代时,从字符串中提取出第1个字符,判断它是否是数字,如果是数字,那么在它前面连接空单元格,作为下一次迭代的初始值;如果不是数字,那么返回空,然后在它前面连接空单元格,结果还是空,作为下一次迭代的初始值。

第二次迭代时,从字符串中提取出第2个字符,同样判断它是否是数字,如果是数字,那么和在它前面连接第一次迭代的结果,作为下一次的初始值,如果不是数字,那么返回空,然后在它前面连接第一次迭代的结果,这样就相当于这一次迭代没有改变结果,继续把这个结果作为下一次的初始值。……,以此循环,不断迭代,直到迭代了100次。

2).提取字母

如下图所示,要在B列中提取字母,勾选一下开关就可以了。迭代次数还是100,开关和计数器的操作方法和上面是一样的,我就不重复写了,主要看下迭代计算的公式。

在B17单元格输入公式=IF(A$14*(A17<>""),B17&IF(OR(MID(A17,C$14,1)=CHAR(ROW($65:$90))),MID(A17,C$14,1),""),""),按ctrl+shift+enter三键结束,向下填充。

3).提取汉字

在B列提取汉字。迭代次数还是100,开关和计数器的设置方法同上。B30单元格的迭代公式为=IF(A$27*(A30<>""),B30&IF(MID(A30,C$27,1)<"吖","",MID(A30,C$27,1)),""),下拉填充。


-02-

数字去重、排序、连接

下图A4:H10是数据源,题目要求是将每行所有数字的个位数提取出来,然后去重,最后再按照从小到大的顺序连接起来,结果如I列所示。

以第1行的数字为例说明,也就是A4:H4。把它们的个位数提取出来,分别是5,1,3,9,2,6,9,4;然后对其去重复,结果就是5,1,3,9,2,6,4;最后按从小到大的顺序连接起来就是1234569。就是这么个意思。

这个题目我们可以用循环引用的迭代计算来完成。首先开启迭代计算,最多迭代次数为10。然后在【开发工具】中插入【复选框】,在E2单元格拖出复选框,改名“开关”,设置链接单元格为C2。

然后在G2单元格输入公式=IF(C2,G2+1,0),作为计数器。

在I4单元格输入公式=IF(C$2,I4&IF(OR(G$2-1=--RIGHT(A4:H4)),G$2-1,""),""),按ctrl+shift+enter三键结束,向下填充。

最后勾选开关,即可获得结果。
链接:

https://pan.baidu.com/s/1jfFQlmMoXX7ZBBUE1G4lNg

提取码:gxyn
(0)

相关推荐