假设您有一个列表，其中写入了不同程度的“直截了当”的初始数据——例如，地址或公司名称：
 |  |
可以清楚地看到，同一个城市或公司在这里以各种不同的形式出现，这显然会在将来使用这些表格时产生很多问题。 如果你稍微思考一下，你可以从其他领域找到很多类似任务的例子。
现在想象一下，这种不正确的数据会定期出现在你面前，也就是说，这不是一次性的“手动修复它，忘记它”的故事，而是一个定期出现的问题，并且存在于大量单元格中。
该怎么办？ 不要通过“查找和替换”框或单击手动将弯曲的文本替换为正确的文本 100500 次 按Ctrl+H?
在这种情况下，首先想到的是根据预编译的参考书匹配不正确和正确的选项进行批量替换 - 如下所示：
不幸的是，随着此类任务的普遍存在，Microsoft Excel 没有简单的内置方法来解决它。 首先，让我们弄清楚如何使用公式来执行此操作，而不涉及 VBA 或 Power Query 中宏形式的“重型火炮”。
案例一、批量全换
让我们从一个相对简单的案例开始——您需要用新的文本替换旧的歪曲文本。 充分.
假设我们有两个表：
在第一个 - 公司的原始杂色名称。 在第二个 - 通信参考书。 如果我们在第一个表中的公司名称中找到该列中的任何单词 找到，那么你需要用正确的名字完全替换这个歪曲的名字——从列 替代 第二个查找表。
为了方便：
- 两个表都使用键盘快捷键转换为动态（“智能”） 按Ctrl+T 或团队 插入 - 表格 （插入 - 表格）.
- 在出现的选项卡上 构造函数 （设计） 第一个表名为 时间，以及第二个参考表—— 替换.
为了解释公式的逻辑，让我们从远处走一点。
以 A2 单元格中的第一家公司为例，暂时忘记了其余的公司，让我们尝试从列中确定哪个选项 找到 在那里见面。 为此，请选择工作表空闲部分中的任何空单元格并在此处输入函数 寻找 （寻找）:
该函数判断是否包含给定的子字符串（第一个参数是列中的所有值 找到) 到源文本（数据表中的第一个公司）中，并且应该输出找到文本的字符的序号，或者如果未找到子字符串则输出错误。
这里的技巧是，由于我们指定的不是一个，而是几个值作为第一个参数，所以这个函数也将返回结果不是一个值，而是一个包含 3 个元素的数组。 如果你没有支持动态数组的最新版 Office 365，那么在输入这个公式后点击 输入 你会在工作表上看到这个数组：
如果您有以前版本的 Excel，然后单击 输入 我们只会看到结果数组中的第一个值，即错误#VALUE！ （＃价值！）.
你不应该害怕🙂事实上，我们的公式有效，如果您在公式栏中选择输入的函数并按下键，您仍然可以看到整个结果数组 F9（只是不要忘记按 ESC回到公式）：
得到的结果数组意味着在原来的歪曲公司名称中 (GK 莫罗兹科 OAO) 一列中所有值的 找到 只找到第二个 （莫罗兹科）, 从连续的第 4 个字符开始。
现在让我们在公式中添加一个函数 查看（抬头）:
这个函数有三个参数：
- 期望值 – 您可以使用任何足够大的数字（主要是它超过了源数据中任何文本的长度）
- 查看_向量 – 我们正在寻找所需值的范围或数组。 这里是之前介绍的功能 寻找，它返回一个数组 {#VALUE!:4:#VALUE!}
- 向量_结果 – 如果在相应的单元格中找到所需的值，我们想要返回值的范围。 以下是列中的正确名称 替代 我们的参考表。
这里主要和不明显的特征是函数 查看 如果没有完全匹配，总是寻找最近的最小（前一个）值. 因此，通过指定任何大数字（例如，9999）作为所需值，我们将强制 查看 在数组 {#VALUE!:4:#VALUE!} 中找到最近的最小数字 (4) 的单元格，并从结果向量中返回相应的值，即从列中返回正确的公司名称 替代.
第二个细微差别是，从技术上讲，我们的公式是一个数组公式，因为函数 寻找 返回的结果不是一个，而是一个包含三个值的数组。 但由于函数 查看 支持开箱即用的数组，那么我们不必将这个公式作为经典的数组公式输入 - 使用键盘快捷键 按Ctrl+转移+输入. 一个简单的就足够了 输入.
就这样。 希望你能明白其中的逻辑。
剩下的是将完成的公式转移到该列的第一个单元格 B2 固定 ——我们的任务就解决了！
当然，对于普通（不是智能）表，这个公式也很有效（只是不要忘记关键 F4 并修复相关链接）：
案例二、批量部分替换
这个案子有点棘手。 同样，我们有两个“智能”表：
第一个写错地址需要更正的表（我称之为 Data2）。 第二个表是参考书，根据它你需要对地址内的子字符串进行部分替换（我称这个表 换人2).
这里的根本区别是你只需要替换原始数据的一部分——例如，第一个地址有一个不正确的 “英石。 圣彼得堡” 在右边 “英石。 圣彼得堡”，保留地址的其余部分（邮政编码、街道、房屋）。
完成的公式会是这个样子（为了便于理解，我把它分成了多少行使用 其他+输入):
这里的主要工作是由标准的 Excel 文本函数完成的 替代 （代替），它有 3 个参数：
- 源文本 – 地址列中的第一个歪曲地址
- 我们在寻找什么——在这里我们使用函数的技巧 查看 （抬头）从以前的方式从列中提取值 找到，它作为一个片段包含在弯曲地址中。
- 用什么替换——同理我们从列中找到对应的正确值 替代.
输入这个公式 按Ctrl+转移+输入 这里也不需要，尽管它实际上是一个数组公式。
可以清楚地看到（参见上图中的 #N/A 错误），尽管如此优雅，但这样的公式有几个缺点：
- 功能 SUBSTITUTE 区分大小写，所以在替换表中没有找到倒数第二行的“Spb”。 要解决此问题，您可以使用该功能 扎门尼特 （代替），或初步将两个表放在同一个寄存器中。
- 如果文本最初是正确的或在其中 没有要替换的片段 （最后一行），那么我们的公式会抛出错误。 这个时刻可以通过使用函数拦截和替换错误来中和 IFERROR (如果错误):
- 如果原文包含 一次从目录中提取几个片段，那么我们的公式只替换最后一个（在第 8 行，Ligovsky «大街XNUMX号« 变成 “公关”, 但是 “S-铅” on “英石。 圣彼得堡” 不再，因为 “S-Pb”在目录中较高）。 这个问题可以通过重新运行我们自己的公式来解决，但是已经沿着列 固定:
在某些地方并不完美和繁琐，但比相同的手动更换要好得多，对吧？ 🙂
PS
在下一篇文章中，我们将弄清楚如何使用宏和 Power Query 实现这种批量替换。
- SUBSTITUTE 函数如何替换文本
- 使用 EXACT 函数查找精确的文本匹配
- 区分大小写的搜索和替换（区分大小写的 VLOOKUP）