在文本中搜索关键字

在源文本中搜索关键字是处理数据时最常见的任务之一。 让我们使用以下示例以多种方式查看其解决方案：

在文本中搜索关键字

假设你和我有一个关键字列表——汽车品牌的名称——和一张包含各种备件的大表格，其中描述有时可以同时包含一个或多个这样的品牌，如果备件适合多个品牌的话汽车品牌。 我们的任务是通过给定的分隔符（例如，逗号）在相邻单元格中查找并显示所有检测到的关键字。

方法 1. 电源查询

当然，首先我们使用键盘快捷键将表格变为动态（“智能”） 按Ctrl+T 或命令 主页 – 格式为表格 （首页——表格格式），给他们起名字（例如 邮票и 备件) 并通过在选项卡上选择将一个一个加载到 Power Query 编辑器中 数据 – 来自表/范围 （数据——来自表/范围）. 如果您有旧版本的 Excel 2010-2013，其中 Power Query 作为单独的加载项安装，则所需的按钮将位于选项卡上 电源查询. 如果您有全新版本的 Excel 365，则按钮 从表/范围 现在打电话给那里 有叶子 （来自工作表）.

在 Power Query 中加载每个表后，我们使用命令返回到 Excel Home — 关闭并加载 — 关闭并加载到... — 仅创建连接 （主页 — 关闭并加载 — 关闭并加载至…… — 仅创建连接）.

现在让我们创建一个重复的请求 备件通过右键单击它并选择 重复请求 （重复查询），然后将生成的复制请求重命名为 结果 我们将继续与他合作。

动作逻辑如下：

  1. 在高级选项卡上 添加列 选择一个团队 自定义列 （添加列 - 自定义列） 并输入公式 = 品牌. 点击后 OK 我们将得到一个新列，其中每个单元格中都会有一个嵌套表格，其中包含我们的关键字列表 – 汽车制造商品牌：

    在文本中搜索关键字

  2. 使用添加列标题中的双箭头按钮展开所有嵌套表格。 同时，带有备件描述的行将乘以品牌数的倍数，我们将得到所有可能的“备件-品牌”对组合：

    在文本中搜索关键字

  3. 在高级选项卡上 添加列 选择一个团队 条件列 （条件栏） 并设置检查源文本（部分描述）中关键字（品牌）出现的条件：

    在文本中搜索关键字

  4. 要使搜索不区分大小写，请在公式栏中手动添加第三个参数 比较.OrdinalIgnoreCase 发生检查功能 文本.包含 （如果公式栏不可见，则可以在选项卡上启用它 评论):

    在文本中搜索关键字

  5. 我们过滤结果表，只留下最后一列的，即匹配并删除不需要的列 出现次数.
  6. 使用命令对相同的描述进行分组 通过...分组 标签 转型 （变换——分组）. 作为聚合操作，选择 所有线路 （所有行）. 在输出中，我们得到一个包含表格的列，其中包含每个备件的所有详细信息，包括我们需要的汽车制造商的品牌：

    在文本中搜索关键字

  7. 要提取每个部分的成绩，请在选项卡上添加另一个计算列 添加列 - 自定义列 （添加列 - 自定义列） 并使用由表格组成的公式（它们位于我们的列中 更多信息) 和提取列的名称：

    在文本中搜索关键字

  8. 我们单击结果列标题中带有双箭头的按钮，然后选择命令 提取值 （提取值）使用您想要的任何分隔符输出邮票：

    在文本中搜索关键字

  9. 删除不需要的列 更多信息.
  10. 要将从中消失的部分添加到结果表中，在描述中找不到品牌，我们执行合并查询的过程 结果 与原始请求 备件 按键 结合 标签 主页 （主页 — 合并查询）. 连接类型 - 外连接权 （右外连接）:

    在文本中搜索关键字

  11. 剩下的就是删除多余的列并重命名 - 移动剩余的 - 我们的任务就解决了：

    在文本中搜索关键字

方法 2. 公式

如果您有 Excel 2016 或更高版本，那么我们的问题可以使用新功能以非常紧凑和优雅的方式解决 结合 （文本连接）:

在文本中搜索关键字

这个公式背后的逻辑很简单：

  • 功能 搜索 （寻找） 在零件的当前描述中依次搜索每个品牌的出现，并返回符号的序列号（从找到品牌开始）或错误#VALUE！ 如果品牌不在描述中。
  • 然后使用函数 IF （如果） и 欧芝卡 （错误） 我们将错误替换为空文本字符串“”，并将字符的序号替换为品牌名称本身。
  • 使用函数通过给定的分隔符将空单元格和找到的品牌的结果数组组装成单个字符串 结合 （文本连接）.

性能比较和用于加速的 Power Query 查询缓冲

对于性能测试，让我们以包含 100 个备件描述的表格作为初始数据。 在上面我们得到以下结果：

  • 通过公式（方法 2）重新计算时间 - 9 秒。 当您第一次将公式复制到整个列和 2 秒时。 在重复（缓冲影响，可能）。
  • Power Query 查询（方法 1）的更新时间要差得多——110 秒。

当然，这在很大程度上取决于特定 PC 的硬件以及安装的 Office 版本和更新，但我认为总体情况是清楚的。

为了加速 Power Query 查询，让我们缓冲查找表 邮票，因为它在查询执行的过程中不会改变，也没有必要不断地重新计算它（就像 Power Query 事实上所做的那样）。 为此，我们使用函数 表.缓冲区 来自内置的 Power Query 语言 M。

为此，请打开一个查询 结果 并在选项卡上 评论 按下按钮 进阶编辑 （查看 - 高级编辑器）. 在打开的窗口中，添加带有新变量的行 马克2，这将是我们 automaker 目录的缓冲版本，稍后在以下查询命令中使用这个新变量：

在文本中搜索关键字

经过这样的细化，我们请求的更新速度提高了近 7 倍——最多 15 秒。 完全不同的事情🙂

  • Power Query 中的模糊文本搜索
  • 使用公式进行批量文本替换
  • 使用 List.Accumulate 函数在 Power Query 中进行批量文本替换

发表评论