Excel：一次查找并替换多个值-excel-PHP中文网

首页

专题

excel

Excel：一次查找并替换多个值

William Shakespeare

Apr 01, 2025 am 04:11 AM

在本教程中，我们将研究几种方法来查找和替换多个单词，字符串或单个角色，因此您可以选择最适合您需求的单词。

人们通常如何在Excel中搜索？通常，通过使用查找和替换功能，该功能适用于单个值。但是，如果您有数十个甚至数百个物品要替换怎么办？当然，没有人愿意一对一手动进行所有这些替换，然后在数据更改时再次进行。幸运的是，有一些更有效的方法可以在Excel中进行质量替换，我们将详细调查它们。

用嵌套替换查找并替换多个值

在Excel中查找和替换多个条目的最简单方法是使用替代功能。

公式的逻辑非常简单：您编写一些单独的函数将旧值替换为新值。然后，您将这些功能嵌套在另一个功能中，以便每个后续替代品都使用上一个替代的输出来寻找下一个值。

替代（替代（替代（ text ， old_text1 ， new_text1 ）， old_text2 ， new_text2 ）， old_text3 ， new_text3 ）

在A2：A10的位置列表中，假设您想用全名替换缩写的国家名称（例如FR ， UK和USA ）。

要完成它，请在D2：D4中输入旧值以及E2：E4中的新值，如下屏幕截图所示。然后，将以下公式放入B2中，然后按Enter：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

…您将立即完成所有替代品：

Excel：一次查找并替换多个值

请注意，上述方法仅在支持动态数组的Excel 365中起作用。

在Excel 2019，Excel 2016和更早的动态版本中，需要为最上方的细胞（B2）编写该公式，然后复制到以下细胞中：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

请注意，在这种情况下，我们将使用绝对单元格引用锁定替换值，因此它们在将公式放下时不会移动。

Excel：一次查找并替换多个值

笔记。替代函数对大小写敏感，这意味着您应该在与原始数据中显示的同一字母案例中键入旧值（ old_text ）。

尽管可能很容易，但此方法具有很大的缺点 - 当您有数十个要替换的项目时，嵌套功能变得很难管理。

优点：易于实现；在所有Excel版本中支持

缺点：最好用于有限数量的查找/替换值

用Xlookup搜索并替换多个条目

在您希望替换整个单元格内容而不是其部分时，Xlookup功能派上用场。

假设您有A列中的国家 /地区列表，目的是用相应的全名代替所有缩写。与上一个示例一样，您从在单独的列中输入“查找”和“替换”项目（分别为d和e），然后在B2中输入此公式：

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

从Excel语言翻译成人类语言，这是公式的作用：

在D2：D4（Lookup_array）中搜索A2值（Lookup_value），然后从E2：E4（return_array）返回匹配项。如果找不到，请从A2中摘取原始值。

双击填充手柄以将公式复制到以下单元格中，结果不会让您等待：

Excel：一次查找并替换多个值

由于Xlookup功能仅在Excel 365中可用，因此上述公式在较早版本中无法使用。但是，您可以通过IFERROR或IFNA和VLOOKUP的组合轻松模仿这种行为：

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

Excel：一次查找并替换多个值

笔记。与替代品不同，Xlookup和Vlookup功能不是对大小写的敏感性，这意味着他们搜索忽略字母情况的查找值。例如，我们的公式将用法国代替FR和FR 。

优点：不寻常的使用通常功能；在所有Excel版本中使用

缺点：在单元格级别上工作，无法替换部分单元内容

使用递归lambda功能多次替换

对于Microsoft 365订户，Excel提供了一个特殊的功能，该功能允许使用传统公式语言创建自定义功能。是的，我说的是Lambda。这种方法的优点在于，它可以将非常冗长且复杂的公式转换为非常紧凑且简单的公式。此外，它使您可以创建自己不存在Excel中的功能，这仅在VBA中才可能。

有关创建和使用自定义Lambda功能的详细信息，请查看本教程：如何在Excel中编写lambda函数。在这里，我们将讨论几个实际示例。

优点：结果是一种优雅且令人惊讶的功能，无论替换对数量多少

缺点：仅在Excel 365中可用；特定于工作簿，不能在不同的工作簿中重复使用

示例1。一次搜索并替换多个单词 /字符串

为了一次替换多个单词或文本，我们创建了一个自定义的lambda函数，名为MultirePlace ，可以采用以下形式之一：

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

或者

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

两者都是自称的递归功能。区别仅在于如何建立出口点。

在第一个公式中，IF函数检查旧列表是否不是空白（旧“”）。如果为true，则调用多隔板函数。如果false，该函数将其当前形式和退出返回文本。

第二公式使用反向逻辑：如果旧的是空白（old =“”），则返回文本和退出；否则请致电MultirePlace 。

最棘手的部分是完成的！您要做的就是在名称管理器中命名多隔板函数，如下面的屏幕截图所示。有关详细指南，请查看如何命名lambda功能。

Excel：一次查找并替换多个值

功能获得名称后，您可以像其他任何内置功能一样使用它。

从最终用户的角度来看，您选择的两个公式变化中的哪个，语法都一样简单：

Multireplace（文本，旧，新）

在哪里：

文本- 源数据
旧- 找到的价值观
新- 替换为

以先前的例子为例，让我们不仅取代国家的缩写，还要取代国家的缩写。为此，在d2开始键入D列中的缩写（旧值），在E2中开始E列E中的全名（新值）。

在B2中，输入多台词函数：

=MultiReplace(A2:A10, D2, E2)

点击Enter并享受结果:)

Excel：一次查找并替换多个值

该公式如何工作

理解该公式的线索是理解递归。这听起来可能很复杂，但是原理很简单。在每次迭代中，递归函数解决了一个更大问题的一个小实例。在我们的情况下，多隔板函数通过旧值和新值循环，并且每个循环都执行一个替代：

多动(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

与嵌套替代功能一样，先前替代的结果成为下一个替代的文本参数。换句话说，在每个随后的呼叫中，替代功能不是原始文本字符串，而是上一个呼叫的输出。

要处理旧列表中的所有项目，我们从最上方的单元格开始，并使用偏移函数在每次交互时向下移动1行：

OFFSET(old, 1, 0)

新列表也是如此：

OFFSET(new, 1, 0)

关键的事情是提供一个退出点，以防止递归电话永远进行。它是在IF函数的帮助下完成的 - 如果旧单元格为空，则该函数将其返回文本其当前形式并退出：

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

或者

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

示例2。在Excel中替换多个字符

原则上，前一个示例中讨论的多隔板函数也可以处理单个字符，前提是每个旧字符都在单独的单元格中输入，就像上面的屏幕截图中的缩写和完整名称一样。

如果您想在一个单元格中输入旧字符和另一个单元格中的新字符，或者将它们直接在公式中键入，则可以通过使用以下公式之一创建另一个名为替代的自定义函数：

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

或者

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

请记住，以往常为名将您的新lambda函数命名：

Excel：一次查找并替换多个值

替换函数“ title =”名称一个自定义替换式函数>

并且您的新自定义功能已准备就绪：

替换（文本，old_chars，new_chars）

在哪里：

文字- 原始字符串
旧- 要搜索的角色
新的- 字符要替换为

为了进行现场测试，让我们做一些经常在导入的数据上执行的事情 - 用直引号和直接撇号替换智能报价和智能撇号。

首先，我们在E2中输入智能引号和智能撇号，直接引号和直撇号，将字符的空间分开，以获得更好的可读性。（当我们在两个单元格中使用相同的定界符时，它不会对结果产生任何影响 - excel只会用一个空间代替一个空间。）

之后，我们在B2中输入此公式：

=ReplaceChars(A2:A4, D2, E2)

并准确获取我们正在寻找的结果：

Excel：一次查找并替换多个值

也可以直接在公式中键入字符。在我们的情况下，只记得“复制”这样的直言：

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

该公式如何工作

替代方案的功能通过旧_chars和new_chars字符串循环，并从左侧的第一个字符开始一次替换。该部分由替代功能完成：

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

在每次迭代中，右功能都从旧_chars和new_chars字符串的左侧剥离一个字符，因此左侧可以拿出下一个字符以替换：

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

在每个递归调用之前，IF函数评估old_chars字符串。如果不是空的，则该函数会自动调用。一旦取代了最后一个字符，迭代过程就完成了，该公式将返回文本其当前形式和退出。

笔记。由于我们的核心公式中使用的替代功能对病例敏感，因此lambdas（多部和替换）都将大写和小写字母视为不同的字符。

群众查找并用UDF替换

如果您的Excel中没有Lambda功能，则可以使用VBA以传统方式编写用户定义的功能。

为了将UDF与lambda定义的多隔板功能区分开，我们将以不同的命名，例如MassReplace 。该函数的代码如下：

函数subsreplace （ inputrng作为范围， findrng为范围，替换为范围）为变量（） iInputCurrow，iinputCurcol，cntinputrows，cntinputCols在源范围内当前行的索引，当前列的索引，源范围中的当前列的索引，行计数，行计数，列计数，列计数redim rarre（1至cntinputrows，1至cntinputcols） redim arsearchReplace（1至cntfindrows，1至2）'准备find/replease for fin/替换成对的ifindcurrow = 1 to cntfindrows arsearchreplace arsearchreplace（ifindcurrow，1）= findrng.cells（ifIndrng.celliue）（1） arSearchReplace(iFindCurRow, 2) = ReplaceRng.Cells(iFindCurRow, 1).Value Next 'Searching and replacing in the source range For iInputCurRow = 1 To cntInputRows For iInputCurCol = 1 To cntInputCols sTmp = InputRng.Cells(iInputCurRow, iInputCurCol).Value '替换每个单元格中的所有查找/替换对ifindcurrow = 1 = 1 to cntfindrows stmp = replace（stmp，arsearchReplace（ifindcurrow，1），arsearchReplace（arsearchReplace（ifindcurrow，2））下一个arres （iinputcurrow，iinputcurcol，iinputcurcol ， iinputcurcol ）

像Lambda定义的功能一样，UDF在范围内。这意味着只有在您插入代码的工作簿中， MassReplace功能才能使用。如果您不确定如何正确执行此操作，请按照如何在Excel中插入VBA代码中所述的步骤。

一旦将代码添加到您的工作簿中，该函数将出现在公式IntelliSense中 - 仅函数的名称，而不是参数！不过，我相信记住语法没什么大不了的：

massReplace（input_range，find_range，replace_range）

在哪里：

input_range-要替换值的源范围。
find_range-字符，字符串或单词要搜索。
replace_range-字符，字符串或单词要替换为。

在Excel 365中，由于支持动态阵列，这是正常公式的作用，仅需要输入顶部单元格（B2）：

=MassReplace(A2:A10, D2:D4, E2:E4)

Excel：一次查找并替换多个值

在预先动态的excel中，这是一个老式的CSE数组公式：您选择整个源范围（b2：b10），键入公式，然后按CTRL Shift同时输入键以完成它。

Excel：一次查找并替换多个值

优点：Excel 2019，Excel 2016和更早版本的自定义Lambda功能的不错的替代品

缺点：必须将工作簿保存为宏启用.xlsm文件

散装用VBA宏代替Excel

如果您喜欢使用宏自动化常见任务，则可以使用以下VBA代码在范围内查找和替换多个值。

sub bulkreplace（） dim rng作为范围，作为范围的sourcerng ，替换为错误恢复范围下一个set sec sourcerng = application.inputbox （ “源数据：” ， “ bulk替换” ，应用程序。替换是什么，然后应用。ScreenupDation = forse for for for for fors for for fors for for plactacerng.columns （1） .cells sourcerng.replace what what：= rng.value，替换：= rng.offset ： = rng.offset（0，1）。

要立即使用宏，您可以下载包含代码的示例工作簿。或者，您可以在自己的工作簿中插入代码。