☰

Excel——值分离和新行

tag-icon • tag-icon microsoft-excel
Excel——值分离和新行

我有一个包含多个工作表的电子表格，我想要实现的结果是根据邮政编码显示县的记录费。有些邮政编码包含多个县，因此显示为

Cortland|Onondaga

我试图实现 excel 将两个或多个值分开并将它们输入到新行中，然后根据县名和州获取记录费用，这一切都是在用户在 A2 上提交邮政编码后完成的

以下是电子表格的链接。

https://docs.google.com/file/d/1M0A7n4xrrlCT6q65qvNPXgYQtVzHWHxS/edit?usp=docslist_api&filetype=msexcel

答案1

因此，基本上，您需要一个县字符串中的县列表，放置在 B2:B11 范围内，使用在 A2 中输入的邮政编码作为获取县字符串的查找值，然后您可以VLOOKUP()在 D2:D11 中使用 B2:b11 值作为查找值输出每个列出的县的记录费用，同样，查找额外的页面费用也是如此。

所有这些都很容易，包括处理 B2:B11 中的空白单元格。我把这个留给你，因为它实际上是千篇一律的，而且你只询问列表。

按照您对事物最大程度的看法，将邮政编码添加到县表作为 H1:I1，以下公式将为您提供所需的列表：

=LET(LookupInput,  A2,
     LookupTable,  H1:I1,
     LookupColumn,  2,
     CountyDelimiter,  "|",
     ZipCodeCounties,  VLOOKUP(LookupInput,  LookupTable,  LookupColumn,  FALSE),
     Counties,  SEQUENCE(  LEN(ZipCodeCounties)-LEN(SUBSTITUTE(ZipCodeCounties,CountyDelimiter,"")  )+1,   1),
     XMLString,  "<Outer><Inner>"&  SUBSTITUTE(ZipCodeCounties,  "|",  "</Inner><Inner>")  &"</Inner></Outer>",

     FILTERXML(XMLString,  "/Outer/Inner["&  Counties  &"]")
     )

该组织为LET()：

  1. 首先，您的三个数据输入变量VLOOKUP()。以绝对开头，以便快速查找和编辑。第四个变量用于指定分隔符，以防您的源在某一天会更改它。
  2. 临时计算名称，VLOOKUP()使用邮政编码来获取需要解析的字符串，并计算出需要从中解析出多少个元素。
  3. 最后一个必需的临时名称，将县字符串格式化为 HTML 字符串以便FILTERXML()可以使用它。
  4. 实际的工作公式，使用已定义的名称（上文 1-3 中提到）。请注意，它易于阅读和理解，而不是紧凑、密集、混乱。

基本上，该公式会获取输入的邮政编码并查找县字符串。然后，它将该字符串呈现为 HTML，并通过查找开头的长度和删除分隔符后的长度来计算其中有多少个元素，减去以找出删除了多少个元素，然后加一（每个元素之间的一个分隔符意味着许多元素加上一个，所以......）。最后，它使用FILTERXML()正常方式使用该函数返回每个元素，但为每个元素发出一个请求，而不是一次请求一个或所有元素。它使用它所SEQUENCE()制造的元素数量来支持这一点。

执行最后一点，使用元素请求字符串传递它SEQUENCE()不是必需的，如果您愿意，您可以删除它以及它上面的支持临时计算。更简单，更快，如果对此感兴趣的话，尽管它几乎不再感兴趣了。我把它们放进去的原因是给你一个“钩子”来处理列表中县的数量超过你在 B2:B11 中可以容纳的数量。不过，同样，你可以删除它，它会以相同的方式工作。在现实生活中，你可能不得不调整该范围内的空单元格的记录费和额外的页面费查找，不用担心需要比你预留的更多的行，但我把它放进去以防万一。

（FILTERXML()从 2013 年起有效。如果您使用的是早期版本，则需要使用几种解析字符串的技术中的一种，据我所知，所有这些技术都使用 {CSE} 条目。如果使用没有的版本SEQUENCE()，您可以从公式中删除其元素，或者使用旧的 ROW(1:xxx) 方法生成可变长度数组。）

相关内容