本帖最后由 edwin11891 于 2024-12-6 20:00 编辑
1. 首先将原始数据的E、F两列的时间,加上日期,在单元格格式中设置只显示时间(不显示日期),避免第7、8行的凌晨时间出现日期错误。否则需要明确凌晨几点之前,左侧日期为前一日,这样在公式中可以自己加上A列日期进行计算。下列公式中已经手工将日期添加到E、F列时间上。
2. 查询公式为:
- =LET(data,FILTER(产线停线明细!E$3:F$11,(产线停线明细!B$3:B$11=B4)*(产线停线明细!A$3:A$11=C$1)),sortedData,SORT(data,1),minData,INDEX(sortedData,1,1),sortedInterval,BYROW(sortedData,LAMBDA(r,TEXTJOIN(",",,r))),interval,REDUCE(TEXTJOIN(",",,minData,minData),sortedInterval,LAMBDA(acc,a,LET(theInterval,TEXTAFTER(acc,";",-1,,1),theIntervalEnd,--TEXTAFTER(theInterval,","),curBegin,--TEXTBEFORE(a,","),curEnd,--TEXTAFTER(a,","),IF(theIntervalEnd>=curBegin,TEXTBEFORE(acc,",",-1)&","&MAX(theIntervalEnd,curEnd),acc&";"&a)))),Result,TEXTSPLIT(interval,",",";"),Ra,HSTACK(VALUE(Result)),Rn,SUM(BYROW(Ra,LAMBDA(x,SUM(x*{-1,1})))),IFERROR(Rn*24,""))
复制代码 |