欢迎转发和点一下“在看”,文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好,今天要分享的是一道数值提取并求和的题目。通常遇到这样的题目,第一反应是要用到SUBSTITUTE函数的经典应用。但今天这道题目有点例外,使用SUBSTITUTE函数会有些小麻烦。
许多朋友都错误地把EXCEL当做账本来使用。所有的数据全部一股脑都记录在同一个单元格中。当需要计算时,手拿计算器敲啊敲,按啊按。今天的这道题目就是这个样子的。
原题目如下:
如题目要求,提取“吨”字前的数据并求和。如果你可以使用高版本函数,TEXTBEFORE函数就能很轻松地解决问题。否则,我们一起来看看下面吧!
由于只是提取特定字符前的数字并求和,我们以前的帖子华山论剑:数字提取技术谁更强,应用之家帮你忙中介绍的办法就不能使用了。这么有规律的源数据,最适合使用FILTERXML函数了。
仔细观察一下源数据,我们发现需要提取的数字之前全部都是“绞线”这个词组,之后全部都是“吨”这个字。这就是我们解决这道题的关键。
而FILTERXML函数可以提取我们指定节点间的所有文本。
SUBSTITUTE(A2,"绞线","<b>")
SUBSTITUTE(A2,"绞线","<b>")部分,将“绞线”二字替换为“<b>”。
SUBSTITUTE(SUBSTITUTE(A2,"绞线","<b>"),"吨","</b>")
SUBSTITUTE(SUBSTITUTE(A2,"绞线","<b>"),"吨","</b>")部分,将“吨”字替换为“</b>”。
"<a>"&SUBSTITUTE(SUBSTITUTE(A2,"绞线","<b>"),"吨","</b>")&"</a>"
在替换后的字符串的最左侧添加“<a>”,在最右侧添加“</a>”,以满足FILTERXML函数对格式的要求。
FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A2,"绞线","<b>"),"吨","</b>")&"</a>","a/b")
使用FILTERXML函数提取b节点下所有的数据。而b节点下的数值正好是需要求和的数值。它的第二参数“a/b”的含义是,提取a节点下b节点间的数据。“<b>”和“</b>”刚好是一组,可以理解为起点和终点,构成一个完成的闭合。
=SUM(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A2,"绞线","<b>"),"吨","</b>")&"</a>","a/b"))
最后用SUM函数来汇总求和,三键确认后向下拖曳即可。
这个函数也就是我们经常说的海鲜大法。
-END-
联系客服