本篇的主题是将单元格内一串文本,找出所有数字并求和,如下图。
难度较高,新手建议仅了解下,先学会数组运用,再研究此知识点。
废话少说,步入正题。
重点说明:本篇只针对文本内整数的数字进行提取并求和,如果含小数点,此方式无效。
首先:解这道题,需要构思方法,本篇以作者的方法进行描述,思路是用LEFT函数逐步提取内容,我们以第一个文本作为例子,如下图:
对于有一定水平的朋友解决上图应该不难,无非就是利用LEFT+ROW函数进行逐步提取。
原文本中明明只有14个字符,为什么要做15个呢?因为每个单元格不知道有多少个字符,宁可多不可少,也可以设置99位,只要比所有单元格中文本个数最多的那个更多即可。
有朋友会说,第14行与第15行是一样的,不会影响计算结果吗?其实不会影响,上图中我们最终的目的是将15个单元格内容从右向左进行提取,只要能对准第8行和第13行,将这两行向左提取数字的位数,数字就能被提取出来,但上图中最右侧可不止这两行是纯数字,那么我们就要一一对应,怎么对应呢?我们再看一个图。
公式:
LEFT(A3,ROW($A$1:$A$15))
当LEFT函数第一参数为文本,第二参数为右侧的提取位数时,0值返回的是错误值,第8行的数字3,和第13行的数字2,将是我们真正要提取的内容,错误值很好处理,最终形成数组去掉错误值,再用SUM函数进行求和就能得到我们最终的计算结果。
看到这里,我们能想到,核心方式就是右侧的提取位数,只要位数出来,问题就迎刃而解,我们需要利用FREQUENCY函数的频率分布,确认每个文本(非数字)之间的频率。
上图是频率的思路,只有8行,无法和15行进行对应,怎么才能对应呢?
我们先来拆解FREQUENCY函数每个参数得到的结果是什么。
说明:上图中描述为了更清晰,均未加绝对引用,在实际操作过程中,公式内所有区域都必须加绝对引用,否则下拉将改变区域。
公式:
ISERR(--MID(B3,ROW($A$1:$A$15),1))*ROW($A$1:$A$15)
思路:左侧得到1~15的行号,右侧A项拆分每个字符,B项使用 两个减号 转换成数字格式(文本为乱码)后,用ISERR函数判断文本逻辑值(乱码的逻辑值是TRUE),C项继续得到1~15行号,D项将B和C两项相乘,即行号顺序不变,纯数字变成0了。这样就能和拆分的文本进行一一对应,如果是文本,则按照1~15数字的顺序排列,遇到数字,则直接显示0,然后利用FREQUENCY函数,用第二参数在第一参数内逐个找频率。
我们将得到的频率的结果,与我们理想中的结果进行对比,并用RIGHT函数对频率结果进行测试。
公式:
FREQUENCY(ROW($A$1:$A$15),ISERR(--MID(A3,ROW($A$1:$A$15),1))*ROW($A$1:$A$15))
完全乱套了,但我们发现,1~5行频率结果有数字1,而不是0,但是我们就算是1,用RIGHT函数提取出来的也是文本字符,不影响,随便是几,只要提取出来的不是纯数字,就能满足我们的要求,6~8行是0,不管它。
整体来看,能满足我们要求的是数字4和数字3,不仅和理想结果错了一位,还比理想结果大一位,都是有规律的,错一位,我们可以将文本拆解前组合任意单字符,再来看效果。
前面加一个符号,这样就对称了。
(我们例题中最多是15位数,当加了一个字符后正好是15位,大家在实际操作过程中千万不能卡的那么准,尽量大一点,也不能大太多,因为会严重影响速度,比如预测最多有50个字符,我们可以设置成100即可)
公式:
LEFT("@"&A3,ROW($A$1:$A$15))
虽然是对称了,但是频率结果都比理想结果大1位,这样也好办,我们再到右侧组合一个空白字符即可,这里有个细节,如果直接在原文本右侧加空格,是没用的,因为第一位还是“@”,第二位还是字符“@E”,因此我们在用LEFT函数逐步提取完成后,再来组合空白字符。
(为什么是空白字符呢?因为RIGHT函数提取后,文本无法转换成数字,空白可在转换时自动去掉,数字+空白经过转换等于数值)
每一个提取的文本后面加上一个空白字符后,频率为1的提取的都是空白,对应的数字4和数字3则提取的我们最终的数字(后面带空格)
公式:
LEFT(" "&A3,ROW($A$1:$A$15))&" "
为了方便大家复制,我们将上述中最终版公式列举出来。
左侧公式:
LEFT(" "&A3,ROW($A$1:$A$15))&" "
右侧公式:
FREQUENCY(ROW($A$1:$A$15),ISERR(--MID(A3,ROW($A$1:$A$15),1))*ROW($A$1:$A$15))
然后使用RIGHT函数进行组合组合:
RIGHT(左侧公式,右侧公式)*1
公式右侧乘1的目的是将文本数字转换成真正数字
(空白不影响,直接自动去掉了)
得到的一组数中有乱码和纯数字,再嵌套一层IFERROR函数去掉乱码,最后嵌套一层SUM函数进行求和。
最终完整版公式:
=SUM(IFERROR(RIGHT(LEFT(" "&A3,ROW($A$1:$A$15))&" ",FREQUENCY(ROW($A$1:$A$15),ISERR(MID(A3,ROW($A$1:$A$15),1)*1)*ROW($A$1:$A$15)))*1,""))
结语:
◆有的人会觉得,我可以用辅助,或者16版本的CTRL+E,又或者VBA正则去处理,我想说,函数玩的就是思路,当你水平上升到一定程度,当年看似非常难的问题,现在你可能几分钟就能解决,先苦后甜,是一样的道理;
你可以用16版快捷键,但遇到快捷键无法处理的怎么办?
你可以用辅助,如果不需要继续深入学下去,辅助是最合适的,有个段子,没有什么是IF解决不了的,辅助也同样适用,但只能局限于当前水平;
你也可以用VBA正则去处理,首先你得有VBA的水平,没有水平等于白说。
函数学到一定程度去学VBA最合适(至少VBA会调用工作表函数),如果函数基础差,甚至常用函数都不会,学VBA只会举步维艰,这是作者总结的经验,希望大家受益!
◆学而不用则殆,希望大家学习后利用到自己的工作中,哪怕是点滴的应用,也是一种进步。给自己的工作带来非常大的方便。
免费微信交流群:liuou514