鲜为人知但很有用的函数——filterxml / 四六文摘

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享一个冷门函数filterxml的用法，此函数的用法是由很多人(包括我)敬仰的海鲜老师所创。在我的心中，海鲜老师就是宝藏级别的存在。

filterxml这个函数在某些情况下是很有用的，我也是花了大半个月的时间才学到了一些基础用法，就来简单分享一下。

-01-

函数说明

FILTERXML函数使用指定的xpath从XML内容返回特定数据。有2个参数，语法如下：

FILTERXML(xml, xpath)

第1参数xml：是有效的xml格式的字符串。

第2参数xpath：是标准的xpath格式的字符串。

这么说，相信各位小伙伴还是一头雾水，这很正常，我刚学的时候也是这样的感觉。因为这里又涉及两个新的概念：一个是xml，一个是xpath。

什么是xml？xml是一种可扩展标记语言，用来传输和存储数据。简单来理解就是一种树状结构的标签语言。

什么是xpath？xpath是一种在xml文档中查找信息的语言，它使用路径表达式来选取xml中的一些信息。这些路径表达式和我们电脑系统中使用的路径很相似，比如"c盘/某某文件夹/某某文件"。

-02-

示例解释

下面通过一个简单的示例让大家了解一下xml和xpath。

<a>excel 太难了</a>

上图就是一个简单的xml，有a标签和b标签。标签用尖括号表示，有开始标签<a>和结束标签</a>。并且b标签包含在a标签中。

xpath是路径表达式，比如"a/b"就是一个xpath，表示a标签下的b标签的文本内容。如下图所示，filterxml用"a/b"这个xpath从A1单元格的xml中提取出b标签的内容“太难了”。

因xml和xpath各自包含的内容非常多，所以今天我不想也不能讲很多，毕竟还是要看大家喜不喜欢，要不然白写了。

今天主要是想通过实例，让大家了解下filterxml可以干什么。如果你看完它的用法，觉得有用，想要继续学习，可以给文章点赞。我将根据点赞数决定是否更新它的详细用法。

-03-

具体应用

1.像vlookup一样查找

下图左表是数据源，现要根据E3单元格的姓名查找对应的性别，结果如F3所示。在F3单元格输入下面的公式：

=FILTERXML("<a "&TEXTJOIN({"='","' "},,A3:B11)&"'/>","//@"&E3)

需要注意公式中的单引号和空格，还需要有textjoin函数，filterxml常与textjoin、substitute组合使用。

2.一对多查找

下图左表是数据源，要根据D16单元格的商店查找出它都有哪些水果，结果如E16:E22所示。选中E16:E22，输入下面的公式，按ctrl+shift+enter三键结束。此公式为区域数组公式。

=IFNA(IF(ROW(),FILTERXML("<a></a>","//@"&D16)),"")

3.提取不重复值

下图A列是一些部门，有重复的数据。现在要提取不重复的部门，结果如D列和E列所示。D列是区域数组公式，选中D32:D37，输入下面的公式，按ctrl+shift+enter结束。

=IFNA(IF(ROW(),FILTERXML("<a>"&TEXTJOIN("",,A32:A43)&"</a>","a/b[not(preceding::*=.)]")),"")

4.分列

下图A、B两列是数据源，现要将B列的成员分列出来，结果如C、D、E列所示。在C48单元格输入下面的公式，右拉下拉。

=FILTERXML("<a>"&SUBSTITUTE($B48,"、","")&"</a>","a/b["&COLUMN(A1)&"]")

5.提取成员姓名及最高分

下图A、B两列是数据源，其中B列是不规范数据。现在要提取各部门成员姓名和最高分，结果如C列和D列所示。

提取成员姓名的公式为：在C56单元格输入下面的公式，下拉填充。

=FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(B56,"：",""),"、","、")&"</a>","a")

提取最高分的公式为：在D56单元格输入公式，下拉填充。

=FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(B56,"：","、"),"、","")&"</a>","a/b[not(//b>.)][.*0=0]")

鲜为人知但很有用的函数——filterxml