在 MS Excel 中对重叠时间进行分组

Question

我有一个电子表格，其中包含多个条目，每个条目都有开始和结束时间。我希望能够识别重叠的时间条目，然后能够通过为重叠条目对分配重叠组 ID 来对重叠条目进行分组。开始和结束时间可以是一天中的任何时间。我想要对它们进行分组的原因是为了能够识别大数据集中的重叠时间，对每个重叠时间进行“分组”，以便我可以从数据集中提取每个“组”并对每个重叠“组”进行进一步计算。

请在下图中查看一些示例条目以及我想要获得的内容：

我已经设法用以下函数解决了“重叠存在”问题

=IF(SUMPRODUCT((startTime<=endTime)*(endTime>=startTime)),TRUE,FALSE)

但是，我正在努力确定如何对两个重叠的条目进行分组，如上面的示例所示。

任何帮助将不胜感激！

Answer 1

这是使用 PowerQuery 的方法。

首先，将光标放在数据中的任意位置，然后按 Ctrl+T 创建表格。它看起来应该像这样：

使用数据>获取和转换数据>从表/范围。单击对话框中的“确定”后，您将在 PowerQuery 编辑器中看到您的数据。它应该如下所示：

为了对每一行和每一行进行比较，我们将把这个数据集连接到它自己。为此，我们首先要创建这个数据集的副本。展开窗口左侧的“查询”窗格，然后右键单击“Table1”查询并选择“引用”。

这将创建 Table1 查询的副本。为清楚起见，将第一个查询重命名为 A，将第二个查询重命名为 B。您可以通过右键单击查询并选择“重命名”来重命名查询。因此，现在您有两个查询：

选择查询 B，然后转到添加列>自定义列并像这样配置对话框：

单击“确定”后，您将看到以下内容：

单击QueryA列右上角的双箭头：

单击“确定”。这将创建新行。此查询中的每一行现在代表查询 B 中的每一行与查询 A 中的每一行的比较。

首先，我们可以过滤掉任何将员工 ID 与自身进行比较的行（如上面的第一行）。首先单击 QueryA.Employee ID 列上的过滤器，然后选择 Number>Does not equal。按如下方式配置，然后按 OK：

现在转到“主页”>“高级编辑器”并编辑以下行：

#"Filtered Rows" = Table.SelectRows(#"Expanded QueryA", each [QueryA.Employee ID] <> 1)

对此：

#"Filtered Rows" = Table.SelectRows(#"Expanded QueryA", each [QueryA.Employee ID] <> [Employee ID])

单击“确定”后，您将看到不再有将员工 ID 与其自身进行比较的行。

现在我们可以创建一个列来标记重叠的位置。使用添加列>自定义列并按如下方式配置：

您可以看到，对于存在重叠的行，该列正确显示 TRUE。

您现在可以选择重命名列，在 TRUE 上过滤重叠列，然后右键单击重叠列并选择“删除”以从结果中隐藏该列。

单击“主页”>“关闭并加载”时，数据将重新放入工作簿。现在您可以继续分析（或者使用 PowerQuery 编辑器向查询添加步骤）。确保将所有时间列的数据类型更改为“时间”（单击四列中每一列的列标题中的图标）

我知道这听起来有些冗长，但这种方法的好处是：

1. 没有笨重而复杂的公式，这些公式容易出错，并且会减慢工作簿的速度
2. 当源数据更新时，您只需刷新查询即可查看结果
3. 这种方法将每个员工与其他每个员工进行比较

编辑：要删除明显的“重复”（我不建议这样做，因为它会使以后的分析不太灵活），您可以执行以下操作：

首先，我们需要确定两行比较的是两边的相同两名员工。为此，我们将添加两个自定义列来计算最小员工 ID：

以及最大员工 ID：

我们想要做的是选择每对最小/最大员工 ID 的单个出现。为此，我们可以为每对分配一个组索引，然后根据 Index=1 过滤结果。

要分配组索引，请使用 Home>Group By 并进行如下配置：

你会看到这个：

转到主页>高级编辑器并将此行添加到查询中：

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

我从中获取了此代码和技术这一页

您需要确保在上一行末尾添加一个逗号，并将“in”部分更改为#“Indexed”而不是#“Grouped Rows”：

按“完成”后，单击“行”列顶部的双箭头，选择除“最小员工 ID”和“最大员工 ID”之外的所有列（您已经拥有这些列）。取消选中“使用原始列名作为前缀”：

结果如下：

现在，在 1 上过滤“GroupIndex”，然后删除最小员工 ID、最大员工 ID 和 GroupIndex（右键单击该列，然后选择删除）：

这是经过这些附加步骤后的完整查询：

let
    Source = A,
    #"Added Custom" = Table.AddColumn(Source, "QueryA", each A),
    #"Expanded QueryA" = Table.ExpandTableColumn(#"Added Custom", "QueryA", {"Employee ID", "Start time", "End time"}, {"QueryA.Employee ID", "QueryA.Start time", "QueryA.End time"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded QueryA", each [QueryA.Employee ID] <> [Employee ID]),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "overlaps", each [QueryA.Start time] < [End time] and [QueryA.End time] > [Start time]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"QueryA.Employee ID", "Compare with Employee ID"}, {"QueryA.Start time", "Compared Start Time"}, {"QueryA.End time", "Compared End Time"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([overlaps] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"overlaps"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Compared Start Time", type time}, {"Compared End Time", type time}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Min Employee ID", each if [Employee ID] < [Compare with Employee ID] then [Employee ID] else [Compare with Employee ID]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Max Employee ID", each if [Employee ID] > [Compare with Employee ID] then [Employee ID] else [Compare with Employee ID]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Min Employee ID", Int64.Type}, {"Max Employee ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Min Employee ID", "Max Employee ID"}, {{"Rows", each _, type table [Employee ID=nullable number, Start time=nullable time, End time=nullable time, Compare with Employee ID=number, Compared Start Time=nullable time, Compared End Time=nullable time, Min Employee ID=nullable number, Max Employee ID=nullable number]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Employee ID", "Start time", "End time", "Compare with Employee ID", "Compared Start Time", "Compared End Time", "GroupIndex"}, {"Employee ID", "Start time", "End time", "Compare with Employee ID", "Compared Start Time", "Compared End Time", "GroupIndex"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Rows", each ([GroupIndex] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"Min Employee ID", "Max Employee ID", "GroupIndex"})
in
    #"Removed Columns1"