内容
如何用公式快速批量替换参考列表中的文字——我们已经整理好了。 现在让我们尝试在 Power Query 中执行此操作。
经常发生 演出 这个任务比解释要容易得多 为什么 它有效，但让我们尝试两者都做🙂
因此，我们有两个使用键盘快捷键从普通范围创建的“智能”动态表 按Ctrl+T 或团队 主页 – 格式为表格 （首页——表格格式）:
我打电话给第一张桌子 时间，第二张表—— 目录使用领域 表名 （表名） 标签 构造函数 （设计）.
任务：替换表中的地址 时间 列中的所有出现 找到 手册 到列中相应的正确对应项 替代. 单元格中的其余文本应保持不变。
步骤 1. 将目录加载到 Power Query 中并将其转换为列表
将活动单元格设置到参考表中的任何位置后，单击选项卡 时间 （日期）或在选项卡上 电源查询 （如果您有旧版本的 Excel 并且您将 Power Query 作为加载项安装在单独的选项卡上）在按钮上 从表/范围 （从表/范围）.
引用表将加载到 Power Query 查询编辑器中：
为了不干扰，自动添加了一个步骤 修改型 （更改类型） 在右侧面板中，可以安全地删除应用的步骤，只留下步骤 来源 （资源）:
现在，为了进行进一步的转换和替换，我们需要把这个表变成一个列表（list）。
抒情题外话
- 表 是由若干行和列组成的二维数组。
- 记录（记录） – 一维数组字符串，由几个带名称的字段元素组成，例如 [姓名 = “玛莎”，性别 = “f”，年龄 = 25]
- 清单 – 一维数组列，由多个元素组成，例如 {1，2，3，10，42} or {“信望爱”}
为了解决我们的问题，我们将主要对类型感兴趣 清单.
这里的技巧是 Power Query 中的列表项不仅可以是平庸的数字或文本，还可以是其他列表或记录。 正是在这样一个棘手的列表（list）中，由我们需要翻我们的目录的记录（records）组成。 在 Power Query 语法符号（方括号中的条目，大括号中的列表）中，这看起来像：
{
[查找=“圣。 圣彼得堡”，替换 = “圣。 圣彼得堡”] ,
[查找=“圣。 圣彼得堡”，替换 = “圣。 圣彼得堡”] ,
[查找=“彼得”，替换=“圣。 圣彼得堡”] ,
等等
}
这种转换是使用 Power Query 中内置的 M 语言的特殊功能执行的 - 表.ToRecords. 要直接在公式栏中应用它，请将此函数添加到那里的步骤代码中 来源.
它是：
后：
添加 Table.ToRecords 函数后，我们的表的外观会发生变化——它会变成一个记录列表。 通过单击任何单词旁边的单元格背景，可以在视图窗格的底部查看各个记录的内容 Record （但不是一个字！）
除了上述之外，再添加一个笔划是有意义的——缓存（缓冲）我们创建的列表。 这将强制 Power Query 将我们的查找列表一次加载到内存中，并且当我们稍后访问它以替换它时不再重新计算它。 为此，将我们的公式包装在另一个函数中—— 列表缓冲区:
这种缓存将显着提高速度（数倍！），并清除大量初始数据。
这样就完成了手册的准备工作。
它仍然可以点击 主页 – 关闭并加载 – 关闭并加载到… （首页—关闭&加载—关闭&加载到..）， 选择一个选项 只需创建一个连接 （仅创建连接） 并返回 Excel。
步骤 2. 加载数据表
这里的一切都是陈词滥调。 和以前的参考书一样，我们起身到表格中的任何地方，点击标签 时间 按键 从表/范围 和我们的桌子 时间 进入 Power Query。 自动添加步骤 修改型 （更改类型） 您还可以删除：
不需要做任何特别的准备工作，我们继续做最重要的事情。
步骤 3. 使用 List.Accumulate 函数执行替换
让我们使用命令将计算列添加到我们的数据表中 添加列 - 自定义列 （添加列 - 自定义列）: 并在打开的窗口中输入添加列的名称（例如， 更正地址) 和我们的神奇功能 列表.累积:
它仍然可以点击 OK – 我们得到一个包含替换的列：
注意：
- 由于 Power Query 区分大小写，因此倒数第二行没有替换，因为在目录中我们有“SPb”，而不是“SPb”。
- 如果源数据中有多个子字符串要同时替换（例如，在第 7 行中，您需要同时替换“S-Pb”和“Prospectus”），那么这不会产生任何问题（与替换来自以前的方法）。
- 如果源文本（第 9 行）中没有可替换的内容，则不会发生错误（再次与公式替换不同）。
这样一个请求的速度非常非常不错。 例如，对于大小为 5000 行的初始数据表，此查询在不到一秒的时间内更新（顺便说一下，没有缓冲大约 3 秒！）
List.Accumulate 函数的工作原理
原则上，这可能是本文的结尾（我写，你读）。 如果您不仅希望能够，而且还想了解它是如何“在引擎盖下”工作的，那么您将不得不更深入地研究兔子洞并处理 List.Accumulate 函数，它完成了所有的批量替换为我们工作。
这个函数的语法是：
=列表.累积（名单, 种子, 累加器)
哪里
- 名单 是我们正在迭代其元素的列表。
- 种子 – 初始状态
- 累加器 – 对列表的下一个元素执行某些操作（数学、文本等）并将处理结果累积到特殊变量中的函数。
通常，在 Power Query 中编写函数的语法如下所示：
(argument1, argument2, ... argumentN) => 一些带有参数的动作
例如，求和函数可以表示为：
(a, b) => a + b
对于 List.Accumulate ，这个累加器函数有两个必需的参数（它们可以命名任何东西，但通常的名称是 州 и 当前，如该函数的官方帮助中所示，其中：
- 州 – 结果被累加的变量（它的初始值是上面提到的那个 种子)
- 当前 – 列表中的下一个迭代值 名单
比如我们看一下下面的构造逻辑的步骤：
=列表.累积（{3，2，5}, 10, （状态，当前）=> 状态 + 当前)
- 可变值 州 设置为等于初始参数 种子Ie 状态 = 10
- 我们取列表的第一个元素 (当前 = 3) 并将其添加到变量中 州 （十）。 我们得到 状态 = 13.
- 我们取列表的第二个元素 (当前 = 2) 并将其与变量中的当前累积值相加 州 （十）。 我们得到 状态 = 15.
- 我们取列表的第三个元素 (当前 = 5) 并将其与变量中的当前累积值相加 州 （十）。 我们得到 状态 = 20.
这是最新积累的 州 该值是我们的 List.Accumulate 函数并作为结果输出：
如果您有一点幻想，那么使用 List.Accumulate 函数，您可以模拟，例如 Excel 函数 CONCATENATE（在 Power Query 中，它的类似物称为 文本合并) 使用表达式：
甚至搜索最大值（模仿 Excel 的 MAX 函数，在 Power Query 中称为 列表.Max):
但是，List.Accumulate 的主要功能是不仅可以处理简单的文本或数字列表作为参数，还可以处理更复杂的对象——例如，lists-from-lists 或 lists-from-records（你好，Directory！）
让我们再看一下在我们的问题中执行替换的构造：
列表.累积（目录, [地址], (state,current) => Text.Replace(state, current[Find], current[Replace]) )
这里到底发生了什么？
- 作为初始值 (种子) 我们从列中取出第一个笨拙的文本 [地址] 我们的桌子： 199034，圣彼得堡，海峡。 贝林加，D。 1
- 然后 List.Accumulate 逐一迭代列表的元素—— 手册. 该列表的每个元素都是一个记录，由一对字段“要查找的内容 - 替换的内容”组成，换句话说，就是目录中的下一行。
- 累加器函数放入一个变量 州 初始值（第一个地址 199034，圣彼得堡，海峡。 贝林加，D。 1) 并对其执行累加器功能 - 使用标准 M 功能的替换操作 文本替换 （类似于 Excel 的 SUBSTITUTE 函数）。 它的语法是：
Text.Replace( 原文，我们要找的，我们要替换的)
在这里我们有：
- 州 是我们的脏地址，位于 州 （从那里到达 种子)
- 当前[搜索] – 字段值 找到 从列表的下一个迭代条目 目录，它位于变量中 当前
- 当前[替换] – 字段值 替代 从列表的下一个迭代条目 目录躺在 当前
因此，对于每个地址，每次都会运行目录中所有行的完整枚举循环，将 [Find] 字段中的文本替换为 [Replace] 字段中的值。
希望你明白了🙂
- 使用公式批量替换列表中的文本
- Power Query 中的正则表达式 (RegExp)