以可用于 Excel 数据透视表的方式对非日期数据进行分组

Question

我有一个包含各种部门标签和相关数据的大列表，想知道如何最好地将其合并为更简单的报告内容。

下表摘录了部分数据。这里我们为金融使用了一系列不同的名称，但它们都可以简单地表示为“金融”。

+-----------------------------------+
| Finance - Accounts Payable        |
| Finance - Chief Financial Officer |
| Finance - Corporate               |
| Finance - Customs                 |
| Finance - Data Management         |
| Finance - Finance - Retail        |
| Finance - Finance Administration  |
| Finance - Finance Corporate       |
| Finance - Finance Retail          |
| Finance - Finance SS              |
| Finance - Finance, Corporate      |
| Finance - Finance, Retail         |
| Finance - Finance, SS             |
| Finance - Finance, Wholesale      |
| Finance - Planning and Reporting  |
| Finance - Retail                  |
| Finance - Shared Services         |
| Finance - Wholesale               |
| Finance ? Finance, Retail         |
| Finance Corporate                 |
| Finance SS                        |
| Finance, Corporate                |
| Finance, Shared Services          |
| Finance, Wholesale                |
+-----------------------------------+

我似乎找不到一种好的方法来对这些数据进行分组（数据透视表分组对于非日期数据似乎没有用），所以我想知道是否最好操作数据以用一个统一的名称替换所有字段，然后从清理后的数据创建一个数据透视表。

任何建议，将不胜感激。

Answer 1

使用 XLOOKUP 对您的唯一部门列表进行近似匹配应该可以得到您想要的结果。

我创建了两个命名表：

1. 角色，我们想要分组的角色列表
2. depts，要分组的唯一部门列表

此函数中的第五个参数告诉 XLOOKUP 查找精确匹配或下一个最小项。因此，此公式适用于以部门名称开头的职位。

=XLOOKUP([@Name],depts[Departments],depts[Departments],"Dept not found",-1,1)

编辑：适用于 Excel 365 之前的 PowerQuery 方法

碰巧的是，我今天早些时候制作了一个有关此过程的视频，所以它还记忆犹新。

您可以使用 PowerQuery 进行部分文本匹配，而无需复杂的 Excel 公式。

首先，在部门列表上创建仅连接查询。

1. 将光标放在列表中
2. 使用数据>获取和转换数据>从表/范围
3. 在 Power Query 编辑器中，单击“主页”>“关闭并加载”下拉菜单，使用“关闭并加载到”，然后选择“仅创建连接”

接下来，使用上面列出的第一步和第二步在数据列表上创建查询。

我已将 XLOOKUP 方法中的列表扩展为包含您的所有数据以及我添加的额外数据。完整列表位于我的工作簿中名为 allroles 的表中。在 Power Query 中，我现在有以下内容：

现在使用添加列>常规>自定义列并使用以下公式：

Table.First(Table.SelectRows(
  depts,
  (lookup) => Text.Contains([Role],lookup[Departments])
))

请注意，我们在此处使用的原因Table.First是为了确保Table.SelectRows为每个角色仅返回一个匹配项。因此，按匹配优先级顺序对部门列表进行排序非常重要。如果您有 Finance - Corporate，那么在您的部门列表中，您在一行中有 Finance，在下一行中有 Corporate，则上述公式将返回该角色的 Finance。如果 Corporate 在 Finance 之前，它将返回 Corporate。如果您删除对的调用Table.First，它将返回一行 Finance 和一行归类为 Corporate 的重复行。这实际上非常强大，但我怀疑这不是您想要的。

要理解我们为何使用 SelectRows，重要的是要记住 SelectRows 是 PowerQuery 筛选数据的方式。因此，在这里，我们说“筛选部门表并返回当前角色包含部门表的 Departments 列中的值的行”。该(lookup)部分只是一个引用部门表的命名参数，它允许我们从即将添加到完全不同的表 (allroles) 的列中引用该表中的列。

单击“确定”后，您将看到一个新列，每行都显示“记录”字样。如果您已删除 Table.First，则每行都会显示“表”。

单击列顶部的双箭头，如下所示：

这将展开记录（或表），并显示自定义函数返回的值：

现在您可以使用“主页”>“关闭并加载”将结果放回工作簿。