☰

Excel 自动子字符串替换

tag-icon • tag-icon microsoft-excel
Excel 自动子字符串替换

我希望创建一个 Excel 表，当在输入列中放置一个值时，输出列将自动将输入字符串中找到的子字符串列表替换为相应的值。我认为这需要 Vlookup，但我无法让该函数处理子字符串。所以我来这里寻求帮助。

下面是一个示例表，显示了我所寻找的内容。输出字符串列将返回完整的输入字符串，其中要替换的值列中的值将替换为替换值列中的值。

替换值 重置价值 输入字符串 输出字符串
萌 柯克 莫伊和科利瞧不起拉里 柯克和斯波克瞧不起皮卡德
卷曲 斯波克 Larry 在 Moe's Tavern 和 John 见面 皮卡德在柯克酒馆与莱克会面
拉里 皮卡德
约翰 赖克

答案1

您可以使用以下内容：

=LET(Lookups,  FILTERXML("<group><element>" & SUBSTITUTE(C2," ","</element><element>") & "</element></group>",  "//group/element"),

      TEXTJOIN(" ", TRUE, IFERROR( XLOOKUP(Lookups, A2:A5, B2:B5),Lookups) )
      )

它使用FILTERXML技巧将输入字符串分成单词（在本例中），然后使用XLOOKUP在“要替换的值”列中检查每个单词。如果找到，它会采用该“替换值”，如果没有找到，它会给出错误。由于某种原因，我今晚没有研究，使用函数的第四个参数“如果未找到”只会将错误从 NA 更改为值。因此它被包裹在一个 中，IFERROR以捕获那些非常重要的错误，并用 的单词列表中的正确单词替换它们FILTERXML。然后TEXTJOIN将结果单词集合重新合并为单个字符串，并在它们之间插入单个空格。

它可能会处理 35 个单词可能遇到的问题，但它并不是特别强大。人们可能直接输入的许多内容可能会使问题变得混乱。此外，如果有标点符号，它不会正确处理问题，因为它不会将标点符号与其后的单词分开（通常位于后面...有时，对人类来说，它可能位于单词之前...）。

但是随着您体验到输入的多样性，您可能会调整它。至少一段时间。在某些时候，它不会像写的那样运行。此外，FILTERXML单词列表只能包含这么多字符（我相信这个限制略大于 6,000，尽管也许 8,000+ 的限制适用，也许。32,767 的限制肯定不适用。）否则它会出错或在该限制处切断字符串，导致输出结尾有缺陷。如图所示，这不是问题，但对于较长的输入...

答案2

如果您有最新的文本函数，包括TEXTSPLIT，下面的函数更加强大，因为它可以处理 Excel 接受的任意长度的输入字符串（32,767 个字符），而不受较短字符串的限制FILTERXML：

=TEXTJOIN( " ", TRUE, IFERROR( XLOOKUP( TEXTSPLIT(C2, , " "), A2:A5, B2:B5), TEXTSPLIT(C2,," ")  ))

它只是用空格分割输入字符串，查找要替换的值列中的所有部分，然后使用替换值列条目替换它们（如果在该列中找到）或保留原始值（如果在该列中找不到）。

仍然不够强大：

  1. 不考虑任何标点符号的使用。
  2. 不考虑包含空格的短语。
  3. 不考虑长度超过 Excel 32,767 个字符限制的结果字符串。

最后一点非常重要。将几千个“Moe”输入替换为“Picard”输出，即使接近长度限制的字符串也可能超出输出限制。

因此，句子甚至精心制作的奇异内容都没问题。但如果输入可能是一本书的长度，甚至是一章的长度，则可能必须将其分解为可行的部分，例如 20-25,000 个字符，对其进行操作，然后像以前一样将各个部分组合起来复制回某个地方，直到章节/书的长度输入完全处理完毕。经验可能有助于决定输入的确切材料应该有多长，以尽量减少需要处理的片段数，但又不会因为最终得到截断的输出字符串而“浪费一个周期”。

此外，对于标点符号的使用，合理的初步估计可能是列出要替换的值列表，然后使用公式将一整套“裸”列表与合理可能的标点符号连接起来。因此，20 个项目的列表中可能会看到另外 20 个项目添加一个句点，另外 20 个项目添加一个逗号，依此类推。当然，每个替换值项目也需要做同样的事情。（或者可以在公式中虚拟地完成，尽管这会更难……）但是，与前面提到的字符串长度不同，列表长度根本不重要。要替换的值列表可能有 800,000 行，Excel 不会在意。花更多时间检查？是的。在意吗？不。

对于包含空格的短语，第一个近似值可能是用不常见的字符替换输入字符串中的短语空格，在要替换的值和替换值列中使用相同的字符，然后用包裹SUBSTITUTE上述公式的来替换它们XLOOKUP。（不是它的IFERROR结果……为什么要花时间执行SUBSTITUTE这个，嗯？）笨重，但这是一个初步的近似值。

相关内容