送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天要和大家分享一道烧脑的题目,同时借着这道题目向大家介绍一则新的函数—FILTERXML函数的一则用法。
题目是这样子的。
朋友们,有什么特别的好方法吗?似乎没有什么特别好的办法来解决这个问题。这道题目的难点在于,星号前面的数字长度不定,这个给我们提取数字带来了极大的困难。
难道除了使用VBA外,我们就没有其他的的方法了吗?
有一个函数就是专门为解决这类问题而生的!FILTERXML函数。它的的语法结构如下:
FILTERXML(xml, xpath)
FILTERXML 函数语法具有下列参数。
Xml:有效 XML 格式的字符串
Xpath:标准 XPath 格式的字符串
大家习惯性的将这个函数称之为“海鲜大法”。当然,这只是这个函数的最基本用法之一,我只是列出了本题下需要用到的功能。其它更多更难的功能也请小伙伴们自行研究学习!
这个“海鲜大法”,是论坛上一位大神总结并发布的。回到这个题目中,要提取“*”之前的数字相加,只要按照上面提到的结构,将所有“*”前的数据放在"b"和"/b"之间,就可以提取到它们。下面所有的步骤都是围绕这个目标而展开的。
思路:
使用海鲜大法,首先要对源数据的格式做一个调整,使其能够满足公式的要求。
SUBSTITUTE(A2&"s","+","+*")部分,将源数据中的加号(+)替换为“+*”,这样,源数据中在中间部分的需要求和的数字就在两个星号之间了。为什么源数据后要加一个“s”?稍后我们解释
接下来利用FILTERXML函数返回计算结果{123;"56+";656;"5-966s"}
减负运算后结果是{123;#VALUE!;656;#VALUE!}
屏蔽错误之后SUM函数求和得到最终结果
之所以在源数据后添加一个“s”是因为,在源数据的最右侧有纯数值出现,这部分会在函数运算过程中被加入计算。添加一个“s”让其变为文本,不参与计算。
总结:在字符串很长、分列的字符串段长度不一、数量不定时,TRIM+MID+SUBSTITUTE+REPT这个套路不能胜任。而FILTERXML+SUBSTITUTE却能很好的适用。
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服