FILTERXML函数用过没？

今天给大家聊一个比较陌生的函数：FILTERXML

语法格式：FILTERXML(xml, xpath)

这个函数有两个必需的参数，第1参数是有效的xml格式的字符串，第2参数是指定的xpath。

正常而言，该函数常用于解析xml结构的网页数据。但不正常的是……咱们可以人为搭建xml格式的字符串，进而实现原本很复杂的文本处理问题。

举个例子。

以上图所示的数据为例，需要按分隔符拆分。

参考函数如下：

C2单元格输入公式后横/纵向填充 ▼

=IFERROR(INDEX(FILTERXML('<a><b>'&SUBSTITUTE($A2,'-','</b><b>')&'</b></a>','a/b'),COLUMN(A1)),'')

有朋友可能会觉得这个问题更适合使用基础技巧：分列。

基础技巧和函数的优劣区别咱们说过很多次了，函数的优势有两个，一个是可以搭建结果和数据源之间的关联，当数据源发生了变动，计算结果可以自动更新，另外一个是函数的计算结果可以嵌套在另外一个函数中继续使用，比如说……

现在需要获取A列中的数值，并按从小到大排序。

参考函数如下：

C2单元格输入公式后横/纵向填充 ▼

=IFERROR(SMALL(FILTERXML('<a><b>'&SUBSTITUTE($A2,'-','</b><b>')&'</b></a>','a/b'),COLUMN(A1)),'')

再比如说，统计下图所示A列数据中不重复的人名数：

参考函数如下：

B2单元格输入公式后向下复制填充 ▼

=COUNTA(FILTERXML('<a><b>'&SUBSTITUTE($A2,'-','</b><b>')&'</b></a>','a/b[not(following::*=.)]'))

又或者，将A列不重复的人名拆分：

参考函数如下：

C2单元格输入公式后横/纵向填充 ▼

=IFERROR(INDEX(FILTERXML('<a><b>'&SUBSTITUTE($A2,'-','</b><b>')&'</b></a>','a/b[not(following::*=.)]'),COLUMN(A1)),'')

……

以上函数解法中都使用到了FILTERXML函数，下面稍微解释一下它，大家就多少了解一下。坦白的说，这些都是套路，理不理解并不重要，重要的是遇到同类问题会套用。

以下部分是FILTERXML函数的第1参数：

'<a><b>'&SUBSTITUTE($A2,'-','</b><b>')&'</b></a>'

SUBSTITUTE函数将A2单元格的分隔符，替换为</b><b>，最后返回一个xml结构的字符串。

<a><b>看见星光</b><b>随风小妞</b><b>看见星光</b></a>

至于啥是xml……感兴趣的朋友可以搜狗。

简单而言，它是一种可扩展标记语言，有标签组成节点，标签必须按合适的顺序进行嵌套，任何的起始标签都必须有一个结束标签。

以上文为例，<a>是开始标签，</a>是结束标签。b是嵌套在a节点内的子节点。

如果我们需要获取a节点下所有b节点的内容，可以将FILTERXML函数第2参数的xpath设置为:

a/b

此时FILTERXML函数返回一个纵向一维内存数组。

{'看见星光';'随风小妞';'看见星光'}

如果在获取a节点下所有b节点文本内容的同时去重复，可以将FILTERXML函数第2参数的xpath设置为:

a/b[not(following::*=.)]

最后，再在FILTERXML函数外嵌套其它查询/聚合/容错函数，即可返回指定结果。

……

挥挥手，下期再见。

图文制作：看见星光

原载：Excel星球