主页 / computer / 问题 / 1690205
Accepted
user1553584
Asked: 2021-11-30 07:13:10 +0800 CST

Excel VBA - 处理重叠时间

  • 772

我在 Excel 中有一个非常大的表，其中包含多行，其中每一行包含两个同时工作的员工之间的比较。请参阅下图中的一些示例行示例：

样本数据

我的目标是找出哪些员工在白天的收入最高，但我不希望员工时间重叠。例如，在上面的样本数据中，员工 145 与其他 3 名员工在 17:00 到 17:30 重叠，在这种情况下，员工 147 的收入最高。因此，我只想要员工 145 的一条记录，他们的开始时间更改为 17:30，结束时间未更改为 18:00，因为他们不是 17:00-17:30 期间收入最高的员工。

在员工 150 的情况下，它有点复杂，因为它们在不同的时间有 3 个重叠。我想要的结果是员工 150 的开始时间不变，但他们的结束时间应该更改为 15:15，因为在 15:15 到 16:00 期间，员工 152 的收入最高。

对于员工 160，我想首先在 12:15 截断他们的条目，因为员工 161 从 12:15-12:45 的收入要高得多，但是我想在表中插入一个额外的行员工 160 的开始和结束时间分别为 12:45 和 13:00，因为他们在此期间没有重叠，并且是当时收入最高的员工。

我很欣赏这可能有更多的情况，但我的目标是找出一天中每个时间收入最高的员工，而没有任何重叠。我已经设法创建了一些代码来处理单个重叠（可以根据上述标准截断、删除或添加条目），但是我很难处理一个员工 ID 有多个重叠或一个重叠的情况还有另一个重叠，所以如果有人知道如何在 VBA 中处理这个问题，我将不胜感激？伪代码也可以..

提前谢谢了！

microsoft-excel vba

1 个回答

  1. Best Answer

    我已从您之前的帖子中将收入添加到表 A 中。

    在此处输入图像描述

    然后我制作了查询的副本，并像这样编辑它：

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    times = List.Times(Time.FromText("00:00:00"),1440,Duration.FromText("0:01")),
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Start time", type time}, {"End time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "time_of_day", each times),
    #"Expanded time_of_day" = Table.ExpandListColumn(#"Added Custom", "time_of_day"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded time_of_day",{{"time_of_day", type time}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "shift", each [time_of_day] >= [Start time] and [time_of_day] <= [End time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"shift", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([shift] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"shift"})
in
    #"Removed Columns"

    在这里，我们创建了一天中每一分钟的列表（参见“时间”行），然后将其添加为查询 A 中展开的列。在步骤 #'Expanded time_of_day' 中，您将看到：

    在此处输入图像描述

    然后只需通过一个标志过滤表，该标志只保留每个班次的开始日期和结束日期之间的分钟行：

    在此处输入图像描述

    过滤为 TRUE 并删除过滤列，然后加载回电子表格后，您可以按分钟创建一个数据透视表，并让 Max of Income 以分钟显示从第一个班次开始到最后一个班次结束的最大收入转移。

    在此处输入图像描述

    如果您愿意，按小时分组：

    在此处输入图像描述

    • 1

相关问题