多个时间重叠时的总持续时间是多少？

Question

多个时间重叠时的总持续时间是多少？

4

我有一个员工时间表的日期和时间列表。时间从F列开始，到G列结束。有时项目的时间会重叠。员工不会因为重叠的项目而获得报酬，但我们需要单独跟踪每个项目。我想能够查看E、F和G列，找到任何重叠的项目，并返回一个单独的时间条目。在下面的示例中，请注意第1行不与其他行重叠，但第2-6行存在一系列重叠的条目。它们不一定都重叠，但更像是“链式”的。我想编写一个公式（而不是脚本）来解决这个问题。

+---+------------+------------+----------+
|   |     E      |       F    |    G     |
+---+------------+------------+----------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |
+---+------------+------------+----------+

我想要评估这些列，并在重叠序列的最后一行返回每个“链”的总持续时间。在下面的示例中，我们将放在第H列。它为从第2行开始到第6行结束（下午1点到下午6:45）的系列找到了5.75小时。

+---+------------+------------+----------+------------+
|   |     E      |       F    |    G     |      H     |
+---+------------+------------+----------+------------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |    0.5     |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |  overlap   |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |  overlap   |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |   5.75     |
+---+------------+------------+----------+------------+

我尝试编写查询语句，但发现自己总是卡在起点。如果有人有建议，我会很乐意知道！提前感谢。

Neill

- Neill

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Max Makhrov · Accepted Answer

我的解决方案

为了解决这个问题，我需要额外添加两列:

步骤1. 返回“overlap”或“ok”

当一条直线的一端在另一条直线内时，两条直线重叠：

我制作了一个查询公式来检查这个问题：

=if(QUERY(ArrayFormula({value(E1:E+F1:F),VALUE(E1:E+G1:G)}), "select count(Col1) where Col1 < "&value(G1+E1-1/10^4)&" and Col2 > "&value(F1+E1+1/10^4)&" label Count(Col1) ''",0)>1,"overlap","ok")

将该公式向下拖动。结果显示在列中：

ok
overlap
overlap
overlap
overlap
ok
ok
overlap
overlap
overlap
overlap
ok

在公式中：

value 用于比较数字。必须比较每个对：日期 + 时间。
-1 / 10 ^ 4 和 +1 / 10 ^ 4 由于查询的不精确性而使用。

步骤2. 获取时间链

这部分有些棘手。我的解决方案只适用于按示例排序的数据。

在单元格I1中输入1。在单元格I2中输入以下公式：

=if(or(and(H1=H2,H2="overlap"),and(H2="ok",H1="overlap")),I1,I1+1)

拖动公式向下。结果是列：

第三步. 获取持续时间

在J4单元格中粘贴并复制以下公式：

=if(H1="ok", round(QUERY(ArrayFormula({value(E:E+F:F),VALUE(E:E+G:G),I:I}), "select max(Col2) - min(Col1) where Col3 = "&I1 &" label max(Col2) - min(Col1) ''")*24,2),"")

该查询根据第二步找到的组获取最长持续时间。

round 用于解决 query 中不精确的问题