分列提取你还在用老套路吗？该尝试个新套路了。 / 开普饭

你好，我是刘卓。欢迎来到我的公号，excel函数解析。分列这个基础命令，想必各位小伙伴都很熟悉。但是用函数实现分列的效果，可能只有部分小伙伴才知道，它的常用套路是trim+mid+substitute+rept。

相比于这个套路，我个人更喜欢另一个新套路，那就是令人“望而生畏”的filterxml。其实这个函数的入门用法，比如分列，还是很简单的。今天就来分享下分列提取的新套路。

-01-

具体应用

1.分列—提取出分数

下图A2:A3是数据源，现在要把A3单元格中的分数分别提取出来，结果如C列所示。老套路是在C3单元格中输入下面的公式，下拉填充。这个公式得到的结果是文本型数字，如果后续参与运算的话要注意数据类型的转换。这个套路之前也讲过，就不细讲了。

=TRIM(MID(SUBSTITUTE(A$3,"、",REPT(" ",99)),ROW(A1)*99-98,99))

下面来看新套路，选中D3:D6，在公式编辑栏输入下面的公式，按ctrl+shift+enter结束。这个公式得到的结果是一个数组，在编辑栏选中公式，按F9可以看到结果为{86;29;90;100}。

=FILTERXML("<a>"&SUBSTITUTE(A3,"、","")&"</a>","a/b")

下面来解释下这个公式，SUBSTITUTE(A3,"、","")这部分将A3单元格中的顿号"、"全部替换为""，结果为"862990100"，如下图所示。

"<a>"&SUBSTITUTE(A3,"、","")&"</a>"这部分是在上一步结果的前后两端连接开始和结束标签，结果为"<a>862990100</a>"，如下图所示。

其实上面的结果就是我们构建出的xml，我们之前说过，xml是由树状结构的标签组成的。其中，a标签是根元素，必须有且仅有1个。b标签是a元素的子元素，子元素可以有多个。

每个元素要有开始标签和结束标签。比如a元素的开始标签是<a>，结束标签是</a>。开始标签和结束标签之间的内容叫做这个元素的文本内容。比如86中的86就是b元素的文本内容。

从上图中可以看出，我们把要提取的分数分别放入b元素的文本内容中。下一步就要用filterxml提取分数了。

filterxml是用xpath来提取xml的数据，xpath是一种路径表达式，和我们电脑系统中的路径很相似。比如我们公式中的"a/b"就是一个xpath，代表从xml中提取a元素下的所有b元素的文本内容，这样就把我们要的分数提取出来了。

对于filterxml这个函数，我只会用一些基础的用法。所以讲起来还是比较吃力的（用比讲简单多了）。想要系统学习的话，可以去论坛搜海鲜老师的帖子。

分列只是最基础的用法，filterxml还可以对提取出的数字进行筛选，比如筛选出大于60分的，结果如E列所示，公式如下。如果用分列的老套路来做，那就有点难了。

=FILTERXML("<a>"&SUBSTITUTE(A3,"、","")&"</a>","a/b[.>60]")

这个筛选的公式比之前分列的公式多了[.>60]这部分，中括号[]是谓词，代表筛选的条件，小点.是一个上下文的变量，遍历每个b元素的文本内容。

"a/b[.>60]"这个xpath的意思是筛选出a元素下的所有分数大于60的b元素的文本内容。

2.文本提取及数字求和

本来一开始想分享这个案例的，但是我就很犹豫。一方面，我担心没有铺垫的话，直接讲可能很多小伙伴也看不懂。另一方面，如果细讲的话，我自己又感觉很吃力。所以，先分享了一个最基础的分列套路。这个案例下次再分享吧。

今天的分享就到这里，看不懂的请到留言讨论区扣1。

链接：

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

提取码：q3vd

分列提取你还在用老套路吗？该尝试个新套路了。