英文:
Excel formula that conditionally sums up durations
问题
我有一个包含两列的Excel表格。
第一列包含带有日期和时间的时间戳，第二列包含报告机器状态的值，当机器开启时为1，关闭时为0。
我需要一个Excel公式，用于报告两个时间戳之间机器开启的总时长。
Excel表格的格式如下：
22/12/2022 11:13 1
22/12/2022 11:58 0
23/12/2022 13:16 1
23/12/2022 13:58 1
24/12/2022 23:03 1
24/12/2022 23:42 0
例如，对于上面的数据，总时长将为11:10:00。
英文:
I have an Excel sheet with two columns.
First column has a timestamp with date and time and the second contains the status of a machinery that is reported with 1 when it was on and 0 when it was off.
I need an Excel formula that reports the total amount of time that the machinery was on between two timestamps.
The Excel has the following form:
22/12/2022 11:13 1
22/12/2022 11:58 0
23/12/2022 13:16 1
23/12/2022 13:58 1
24/12/2022 23:03 1
24/12/2022 23:42 0
For example the data above the total time duration would be 11:10:00
答案1
得分: 3
=SUM((A2:A6-A1:A5)*B1:B5)
或者对于Excel 365中的更一般情况：
=LET(a, TOCOL(A:A, 1), b, TOCOL(B:B, 1), SUM((DROP(a, 1) - DROP(a, -1)) * DROP(b, -1)))
英文:
Or just
=SUM((A2:A6-A1:A5)*B1:B5)
Or for a more general one in Excel 365:
=LET(a,TOCOL(A:A,1),b,TOCOL(B:B,1),SUM((DROP(a,1)-DROP(a,-1))*DROP(b,-1)))
答案2
得分: 2
看起来很简单：
从C2填充到C6的公式。
或者，您可以在任何单个单元格中使用以下公式：
=REDUCE(0,B1:B5,LAMBDA(a,b,a+(OFFSET(b,1,-1)-OFFSET(b,0,-1))*b))
英文:
It looks easy:
Fill down the formula from C2 to C6.
Alternatively you can use the next formula in any single cell:
=REDUCE(0,B1:B5,LAMBDA(a,b,a+(OFFSET(b,1,-1)-OFFSET(b,0,-1))*b))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论