|
你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享一个冷门函数filterxml的用法，此函数的用法是由很多人(包括我)敬仰的海鲜老师所创。在我的心中，海鲜老师就是宝藏级别的存在。 filterxml这个函数在某些情况下是很有用的，我也是花了大半个月的时间才学到了一些基础用法，就来简单分享一下。
函数说明 FILTERXML函数使用指定的xpath从XML内容返回特定数据。有2个参数，语法如下： 第1参数xml：是有效的xml格式的字符串。
第2参数xpath：是标准的xpath格式的字符串。 这么说，相信各位小伙伴还是一头雾水，这很正常，我刚学的时候也是这样的感觉。因为这里又涉及两个新的概念：一个是xml，一个是xpath。什么是xml？xml是一种可扩展标记语言，用来传输和存储数据。简单来理解就是一种树状结构的标签语言。什么是xpath？xpath是一种在xml文档中查找信息的语言，它使用路径表达式来选取xml中的一些信息。这些路径表达式和我们电脑系统中使用的路径很相似，比如"c盘/某某文件夹/某某文件"。示例解释 下面通过一个简单的示例让大家了解一下xml和xpath。上图就是一个简单的xml，有a标签和b标签。标签用尖括号表示，有开始标签<a>和结束标签</a>。并且b标签包含在a标签中。
xpath是路径表达式，比如"a/b"就是一个xpath，表示a标签下的b标签的文本内容。如下图所示，filterxml用"a/b"这个xpath从A1单元格的xml中提取出b标签的内容“太难了”。因xml和xpath各自包含的内容非常多，所以今天我不想也不能讲很多，毕竟还是要看大家喜不喜欢，要不然白写了。 今天主要是想通过实例，让大家了解下filterxml可以干什么。如果你看完它的用法，觉得有用，想要继续学习，可以给文章点赞。我将根据点赞数决定是否更新它的详细用法。
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><b "&TEXTJOIN({"='","'/><b "},,A16:B27)&"'/></a>","//@"&D16)),"")
3.提取不重复值
下图A列是一些部门，有重复的数据。现在要提取不重复的部门，结果如D列和E列所示。D列是区域数组公式，选中D32:D37，输入下面的公式，按ctrl+shift+enter结束。=IFNA(IF(ROW(),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,A32:A43)&"</b></a>","a/b[not(preceding::*=.)]")),"")
4.分列
下图A、B两列是数据源，现要将B列的成员分列出来，结果如C、D、E列所示。在C48单元格输入下面的公式，右拉下拉。 =FILTERXML("<a><b>"&SUBSTITUTE($B48,"、","</b><b>")&"</b></a>","a/b["&COLUMN(A1)&"]")
5.提取成员姓名及最高分
下图A、B两列是数据源，其中B列是不规范数据。现在要提取各部门成员姓名和最高分，结果如C列和D列所示。提取成员姓名的公式为：在C56单元格输入下面的公式，下拉填充。 =FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(B56,"：","<b>"),"、","</b>、")&"</b></a>","a")
提取最高分的公式为：在D56单元格输入公式，下拉填充。=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(B56,"：","、"),"、","</b><b>")&"</b></a>","a/b[not(//b>.)][.*0=0]")
|
