如何在Microsoft Excel中对相似项目进行分组

您是否需要在 Excel 中对相似的项目进行分组？

Excel 有很多方法可以对相同的项目进行分组，但这些方法依赖于值的完全匹配。

即使数据不完全匹配，也可能被视为相同。将这些相似的项目分组可能是一项困难的任务，而且通常需要手动完成。

Excel 确实有一个功能可以把这些相近但不完全相同的值分组在一起！只是有点隐蔽。

Power Query 有一个模糊分组功能，专门用于此类情况。这将允许您根据相似度阈值对项目进行分组，并为含义相同的不同术语设置翻译表。

本篇文章将向您展示如何使用 Power Query 对所有相关值进行分组。获取本篇文章中使用的示例工作簿的副本以继续学习！

向表中添加数据

此示例显示了问题列表。您可以看到，虽然列表中的所有项目都是不同的，但其中一些项目相似，需要分组在一起。

你需要做的第一件事是将要分组的数据放入表中。

这将使将数据导入 Power Query 并使用模糊分组功能变得容易。

选择数据内的任意单元格。
前往插入功能区选项卡。
点击桌子命令。

这将打开 创建表 菜单并选择了数据范围。

检查 我的表格有标题 选项。
按好的按钮。

数据位于表对象内，可以导入到 Power Query 中。

确保你将表重命名为简短且描述性的名称例如问题。

创建翻译表

在将数据导入 Power Query 并对相似项目进行分组之前，值得设置一个翻译表。

您可以使用此表构建自定义映射来帮助模糊匹配过程。

这可用于告诉 Excel 某些文本具有相同的含义。例如，在数据上下文中，单词 电子邮件 和信息可能是同一件事，应该归为一组。

翻译表需要有一个从和到列标题。从列将被视为与到行内的列。

这也应该在 Excel 表中。

将表导入 Power Query

现在您已将数据分组并在表中映射翻译，可以将这些数据导入到 Power Query 中。

以下是将表导入 PowerQuery 的方法。

选择表格。
前往数据标签。
点击 来自表/范围 选项。

将两个表导入到 PowerQuery 中，您将能够对数据进行分组。

从“转换”选项卡对数据进行分组

类似项目的分组最终将使用 Table.FuzzyGroup() M 代码中的功能。

查询编辑器用户界面无法提供此功能。

您可以从 UI 中设置常规分组，它将自动为您构建一个 Table.Group() 公式。

这将具有与以下相同的参数签名 Table.FuzzyGroup() 公式除了最后一个处理模糊分组选项的参数之外。

从 UI 构建常规分组公式，然后将其编辑为所需的模糊分组公式将更加容易。

选择数据集中要分组的列。在此示例中，只有一列。
前往转换标签。
点击 通过…分组 选项。

这将打开 通过…分组 菜单 基本的 选项和要分组的列已被选中。

添加 新列名 到输入框。
选择 所有行 选择作为手术。这将允许您稍后扩展分组数据。
按好的按钮。

= Table.Group(#"Changed Type", {"Item"}, {{"Data", each _, type table [Item=nullable text]}})

这将导致上述 M代码公式。

这将执行精确匹配分组。根据您的数据集，这可能不会改变任何内容，除了添加一列，其中每行都有一个桌子。

💡 提示：你可以了解一下更多关于 表.组 功能这里。

将组步骤编辑为模糊组

= Table.FuzzyGroup(#"Changed Type", {"Item"}, {{"Data", each _, type table [Item=nullable text]}},[IgnoreCase=true, IgnoreSpace=true, Threshold=0.8, TransformationTable=Translation])

点击公式栏，通过添加上述部分来编辑结果公式红色的。

在这种情况下， Translation 是包含您的翻译表的查询的名称从和到列。

这会将使用的函数更改为 Table.FuzzyGroup() 函数。前三个参数相同，最后添加了第四个参数以指定模糊分组选项。

最后一个选项参数是包含这些字段的记录。

忽略大小写：这可以是真的或者 错误的。将其设置为 true 将允许不区分大小写的分组。例如，电子邮件和电子邮件被分组在一起。
忽略空间：这可以是真的或者 错误的。将其设置为 true 将在分组时忽略任何空格字符。例如， 电子邮件 和 电子邮件 被组合在一起。
临界点：这是一个介于 0 和 1 之间的数字，用于指定两个值分组的相似度分数。阈值 0 会将所有内容分组在一起，而阈值 1 则需要完全匹配才能分组。
转换表：这是一张映射表从价值等同于到分组结果中的值。

有关 表.模糊组 功能您可以查看 Microsoft 文档。

按 进入 键或单击查看接受更新的公式。

这将执行模糊分组并对相似的项目进行分组，同时考虑到翻译表中的映射！

再次，您将看到一个新列，其中桌子在每一行中。每个表都包含分组数据，单击单元格的空白部分时可以看到预览。

现在您可以扩展这些表。

点击扩张在分组列标题中切换。
选择扩张选项。
按好的按钮。

您现在将看到所有数据，其值根据项目列进行分组。

💡 提示：查看模糊分组结果，然后调整 忽略大小写， 忽略空间，和 临界点 改进分组的选项。

将数据加载到 Excel

现在可以将这些分组数据加载到 Excel 中。

前往家 Power Query 编辑器的选项卡。
点击 关闭并加载 按钮。
选择 关闭并加载到 选项。

选择将数据加载到桌子。
选择加载数据的位置。
按好的按钮。

现在，您的类似项目已分组到一个新的 Excel 表中！

结论

对相似但不完全相同的项目进行分组是一项困难的任务，大多数用户都会手动完成。

Power Query 确实提供了模糊分组功能，但无法从查询编辑器用户界面访问该功能。

执行常规分组，然后将生成的 M 代码调整为模糊分组是一种相当简单的实现该功能的方法，因为它们的参数签名相似。

您是否知道 Excel 的 Power Query 中可用的模糊分组功能？请在评论中告诉我！

如何在Microsoft Excel中对相似项目进行分组