Excel - 使用 FILTERXML 从字符串中提取子字符串

Excel的用途，不幸的是，这意味着它并不像我们希望的那样多样化。此外，Excel 似乎不允许返回返工的节点值，并且只允许您按外观顺序选择节点。但是，我们仍然可以使用相当多的功能。可以在此处找到有关此内容的更多信息。FILTERXMLXPATH 1.0

该函数采用两个参数：=FILTERXML(<A string in valid XML format>,<A string in valid XPATH format>)

假设单元格保存字符串：.为了创建有效的 XML 字符串，我们使用分隔符将分隔符更改为有效的结束标记和开始标记构造。因此，为了获得给定示例的有效 XML 构造，我们可以执行以下操作：A1ABC|123|DEF|456|XY-1A|ZY-2F|XY-3F|XY-4f|xyz|123SUBSTITUTE

"<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>"

出于可读性的原因，我将使用单词 <XML> 作为占位符来引用上述构造。下面，您将在有效的构造中找到不同的 usefull 函数来过滤节点：XPATH

---

_{1） 所有元素：}

=FILTERXML(<XML>,"//s")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f、xyz 和 123（所有节点）}

---

_{2） 按位置划分的元素：}

=FILTERXML(<XML>,"//s[position()=4]")

艺术

=FILTERXML(<XML>,"//s[4]")

_{返回值：456（索引 4 上的节点）}^†

=FILTERXML(<XML>,"//s[position()<4]")

_{返回：ABC、123 和 DEF（索引< 4 上的节点）}

=FILTERXML(<XML>,"//s[position()=2 or position()>5]")

_{返回：123、ZY-2F、XY-3F、XY-4f、xyz 和 123（索引 2 或> 5 上的节点）}

=FILTERXML(<XML>,"//s[last()]")

_{返回值：123（最后一个索引上的节点）}

=FILTERXML(<XML>,"//s[position() mod 2 = 1]")

_{返回：ABC、DEF、XY-1A、XY-3F 和 xyz（奇数节点）}

=FILTERXML(<XML>,"//s[position() mod 2 = 0]")

_{返回：123、456、ZF-2F、XY-4f 和 123（偶数节点）}

---

_{3） （非）数值元素：}

=FILTERXML(<XML>,"//s[number()=.]")

艺术

=FILTERXML(<XML>,"//s[.*0=0]")

_{返回值：123、456 和 123（数值节点）}

=FILTERXML(<XML>,"//s[not(number()=.)]")

艺术

=FILTERXML(<XML>,"//s[.*0!=0)]")

_{返回：ABC、DEF、XY-1A、ZY-2F、XY-3F、XY-4f 和 xyz（非数字节点）}

---

_{4） （不）包含以下元素：}

=FILTERXML(<XML>,"//s[contains(., 'Y')]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（包含“Y”，注意 XPATH 区分大小写，不包括 xyz）}

=FILTERXML(<XML>,"//s[not(contains(., 'Y'))]")

_{返回值：ABC、123、DEF、456、xyz 和 123（不包含“Y”，请注意 XPATH 区分大小写，包括 xyz）}

---

_{5） 以（不）开头或/和结尾的元素：}

=FILTERXML(<XML>,"//s[starts-with(., 'XY')]")

_{返回：XY-1A、XY-3F 和 XY-4f（以“XY”开头）}

=FILTERXML(<XML>,"//s[not(starts-with(., 'XY'))]")

_{返回：ABC、123、DEF、456、ZY-2F、xyz 和 123（不要以“XY”开头）}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F']")

_{返回：DEF、ZY-2F 和 XY-3F（以“F”结尾，请注意 XPATH 1.0 不支持以结尾）}

=FILTERXML(<XML>,"//s[not(substring(., string-length(.) - string-length('F') +1) = 'F')]")

_{返回：ABC、123、456、XY-1A、XY-4f、xyz 和 123（不要以“F”结尾）}

=FILTERXML(<XML>,"//s[starts-with(., 'X') and substring(., string-length(.) - string-length('A') +1) = 'A']")

_{返回：XY-1A（以“X”开头，以“A”结尾）}

---

_{6） 大写或小写的元素：}

=FILTERXML(<XML>,"//s[translate(.,'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')=.]")

_{返回值：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F 和 123（大写节点）}

=FILTERXML(<XML>,"//s[translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')=.]")

_{返回值：123、456、xyz 和 123（小写节点）}

_{注意：不幸的是，XPATH 1.0 不支持 upper-case（） 或 lower-case（），因此以上是一种解决方法。如果需要，请添加特殊字符。}

---

_{7） （不）包含任何数字的元素：}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=.]")

_{返回：123、456、XY-1A、ZY-2F、XY-3F、XY-4f 和 123（包含任意数字）}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')=.]")

_{返回值：ABC、DEF 和 xyz（不包含任何数字）}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=. and .*0!=0]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（包含数字，但不是数字）}

---

_{8） 独特元素或重复元素：}

=FILTERXML(<XML>,"//s[preceding::*=.]")

_{返回值：123（重复节点）}

=FILTERXML(<XML>,"//s[not(preceding::*=.)]")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f 和 xyz（唯一节点）}

=FILTERXML(<XML>,"//s[not(following::*=. or preceding::*=.)]")

_{返回：ABC、DEF、456、XY-1A、ZY-2F、XY-3F 和 XY-4f（没有类似同级的节点）}

---

_{9）一定长度的元素：}

=FILTERXML(<XML>,"//s[string-length()=5]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（长度为 5 个字符）}

=FILTERXML(<XML>,"//s[string-length()<4]")

_{返回值：ABC、123、DEF、456、xyz 和 123（短于 4 个字符）}

---

_{10） 基于前/后要素：}

=FILTERXML(<XML>,"//s[preceding::*[1]='456']")

_{返回值：XY-1A（前一个节点等于“456”）}

=FILTERXML(<XML>,"//s[starts-with(preceding::*[1],'XY')]")

_{返回：ZY-2F、XY-4f 和 xyz（上一个节点以“XY”开头）}

=FILTERXML(<XML>,"//s[following::*[1]='123']")

_{返回：ABC 和 xyz（以下节点等于“123”）}

=FILTERXML(<XML>,"//s[contains(following::*[1],'1')]")

_{返回值：ABC、456 和 xyz（以下节点包含“1”）}

=FILTERXML(<XML>,"//s[preceding::*='ABC' and following::*='XY-3F']")

艺术

=FILTERXML(<XML>,"//s[.='ABC']/following::s[following::s='XY-3F']")    

_{返回：123、DEF、456、XY-1A 和 ZY-2F（介于“ABC”和“XY-3F”之间的所有内容）}

---

_{11） 基于子字符串的元素：}

=FILTERXML(<XML>,"//s[substring-after(., '-') = '3F']")

_{返回值：XY-3F（连字符后以“3F”结尾的节点）}

=FILTERXML(<XML>,"//s[contains(substring-after(., '-') , 'F')]")

_{返回值：ZY-2F 和 XY-3F（连字符后包含“F”的节点）}

=FILTERXML(<XML>,"//s[substring-before(., '-') = 'ZY']")

_{返回值：ZY-2F（连字符前以“ZY”开头的节点）}

=FILTERXML(<XML>,"//s[contains(substring-before(., '-'), 'Y')]")

_{返回：XY-1A、ZY-2F、XY-3F 和 XY-4f（连字符前包含“Y”的节点）}

---

_{12） 基于串联的元素：}

=FILTERXML(<XML>,"//s[concat(., '|', following::*[1])='ZY-2F|XY-3F']")

_{返回值：ZY-2F（与“|”连接时的节点，后面的同级节点等于“ZY-2F|XY-3F'）}

=FILTERXML(<XML>,"//s[contains(concat(., preceding::*[2]), 'FA')]")

_{返回值：DEF（当节点与左侧的同级两个索引连接时，包含“FA”）}

---

_{13） 空与非空：}

=FILTERXML(<XML>,"//s[count(node())>0]")

艺术

=FILTERXML(<XML>,"//s[node()]")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f、xyz 和 123（所有不为空的节点）}

=FILTERXML(<XML>,"//s[count(node())=0]")

艺术

=FILTERXML(<XML>,"//s[not(node())]")

_{返回值：None（所有为空的节点）}

---

_{14） 之前或之后：}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][last()]/following::*")

_{返回：XY-4f、xyz 和 123（最后一个节点右侧的所有节点，以大写字母“F”结尾）}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][1]/preceding::*")

_{返回值：ABC 和 123（第一个节点左侧以大写字母“F”结尾的所有节点）}

---

_{15）（前面或后面）和自己：}

=FILTERXML(<XML>,"(//s[.*0!=0][last()]|//s[.*0!=0][last()]/preceding::*)")

_{返回：ABC、123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f 和 xyz（从右侧修剪所有数字节点††}

=FILTERXML(<XML>,"(//s[.*0=0][1]|//s[.*0=0][1]/following::*)")

_{返回值：123、DEF、456、XY-1A、ZY-2F、XY-3F、XY-4f、xyz 和 123（从左侧修剪所有非数字节点）}

---

_{16） 最大值或最小值：}

=FILTERXML(<XML>,"(//s[.*0=0][not(.<//s[.*0=0])])[1]")

_{返回值：456（查看数值节点的最大值）}

=FILTERXML(<XML>,"(//s[.*0=0][not(.>//s[.*0=0])])[1]")

_{返回值：123（查看数值节点的最小值）}

_{注意：这相当于按照 #3 返回所有数字节点，并使用 Excel 的 MIN（） 和 MAX（） 函数对数组进行后处理。}

---

现在显然，以上是功能可能性的演示，您可以获得上述各种组合以及更多！我试图涵盖最常用的字符串函数。如果您遗漏任何内容，请随时发表评论。XPATH 1.0

虽然这个问题本身就很宽泛，但我希望就如何使用手头的查询给出一些总体指导。该公式返回要以任何其他方式使用的节点数组。很多时候我会在 or 中使用它。但我想其他选择将是新的 DA 函数来溢出结果。FILTERXMLTEXTJOIN()INDEX()

请注意，在解析字符串时，与号字符 （&） 和左尖括号 （<） 不得以文字形式出现。它们将分别需要替换为 或 。另一种选择是分别使用其数字 ISO/IEC 10646 字符代码 as 或。解析后，该函数会将这些字符以文字形式返回给您。毋庸置疑，用分号拆分字符串就变得很棘手了。FILTERXML()&<&<

---

^‡ _{每个谓词，左方括号和右方括号之间的结构，都是给定节点列表的过滤器。写出多个这样的结构实际上是这样的谓词。}

‡^‡ 没有_{一个简单的、跟随/先于兄弟姐妹和自我的构造。因此，我使用了联合运算符。然而，这需要多个表达式位于 paranthesis 内。就像捕获组中的交替一样，如果人们会考虑正则表达式。}

编辑 22-3-2022：

根据新功能，现在可以将字符串直接拆分为水平或垂直范围（或两者兼而有之）。它甚至可以拆分值数组。但是，它不能像“xpath”那样直接过滤。因此，我将保持以下答案不变，因为它仍然相关。TEXTSPLIT()

---

臭名昭著的缺失函数XSPLIT()

这篇文章旨在进一步介绍我们如何在不使用 VBA 的情况下使用自己的可重用函数。虽然目前处于测试阶段，但即将到来，使用此功能，我们可以创建自己的自定义函数。让我举个例子来解释一下：XSPLIT()FILTERXML()LAMBDA()

公式很简单，它按外观顺序溢出带分隔符的文本值。但是，我们在“名称管理器”中创建的函数的名称是：C1=XSPLIT(A1,B1:B3)SPLIT()LAMBDA()

=LAMBDA(txt,del,[xpath],FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TEXTSPLIT(txt,del))&"</s></t>","//s"&xpath))

如您所见，该函数有 4 个参数：

• txt- 对源值的引用。
• del- 我们想要的任何数量的分隔符。书面或参考。
• [xpath]- 如果需要，请放置 xpath 表达式以应用一些过滤器。例如：仅返回数字子字符串。这是一个可选参数"[.*0=0]"
• FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TEXTSPLIT(txt,del))&"</s></t>","//s"&xpath)

第 4 个参数是调用所有前 3 个参数以创建与主文章中介绍的相同的构造的地方。现在，由于 MS 不想给我们自己，我们用三个参数创建了自己的函数。XSPLIT()

主要帖子集中在特定分隔符上;在给定的示例中，pipe-symbol.但是，如果您有多个分隔符怎么办？你需要多个嵌套函数，对吧？如果我们也能在我们的函数中实现它，那不是很好吗？这是我个人感到兴奋的地方，因为我们可以为这个函数提供多个分隔符来将字符串拆分为元素。SUBSTITUTE()SUBSTITUTE()XSPLIT()TEXTSPLIT()

现在，我们已经使用三个参数创建了自己的函数：XSPLIT()

=XSPLIT(<StringToBeSplited>,<YourDelimiters>,<OptionalXpath>)

现在，我们可以将其用作整个工作簿中的函数。享受！