google-sheets - 查找许多重叠时间的总持续时间

Question

我有一份员工考勤表的日期和时间列表。时间从 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     |
+---+------------+------------+----------+------------+

我尝试过编写查询，但总是发现自己又回到了起点。如果有人有建议，我很想知道!预先感谢您。

尼尔

Answer 1

我的解决方案

为了解决这个问题，我需要 2 个额外的列:

第 1 步:返回“overlap”或“ok”

当一端位于另一端内部时，两条线重叠:

我做了一个query检查这一点的公式:

=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由于 query 中的不精确而使用

第 2 步:获取时间链

这部分很棘手。仅当数据像示例中那样排序时，我的解决方案才有效。

在单元格 I1 中输入 1 。在单元格中I2输入公式:

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

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

1
2
2
2
2
2
3
4
4
4
4
4

第三步。获取持续时间

在 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),"")

查询按组获取最大持续时间，在步骤2中找到。

• round由于 query 中的不精确而使用