如何汇总工时（去除重叠时间段）

博爱风雨同舟

=LET(sa,TEXT(FILTER(产线停线明细!$E$3:$F$12,(产线停线明细!$A$3:$A$12=$C$1)*(产线停线明细!$B$3:$B$12=B4))*24,"0.00")*100,sb,SORT(--UNIQUE(TEXTSPLIT(TEXTJOIN("\",,MAP(TAKE(sa,,1),TAKE(sa,,-1),LAMBDA(X,Y,TEXTJOIN("\",,ROW(INDIRECT(X&":"&Y)))))),,"\"))),sc,TAKE(GROUPBY(sb-SEQUENCE(COUNT(sb)),sb,LAMBDA(X,@X&IF(ROWS(X)=1,,-MAX(X))),,0),,-1),SUM(BYROW(sc,LAMBDA(x,-IMREAL(IMDIV(x&"j","1+j"))*2))/100))

博爱风雨同舟

=LET(u,(产线停线明细!$A$3:$A$12=$C$1)*(产线停线明细!$B$3:$B$12=B4),sa,MAP(FILTER(产线停线明细!$E$3:$E$12,u),LAMBDA(x,TEXT(x*24,"0.00")))*100,sb,BYROW(FILTER(产线停线明细!$E$3:$F$12,u),LAMBDA(X,TEXT(MOD(NPV(-2,X),1)*24,"0.00")))*100-1,sc,SORT(--UNIQUE(TEXTSPLIT(TEXTJOIN("/",,MAP(sa,sb,LAMBDA(X,Y,TEXTJOIN("/",,IF(SEQUENCE(Y+1,,X)>2400,SEQUENCE(Y+1,,X)-2400,SEQUENCE(Y+1,,X)))))),,"/"))),sd,TAKE(GROUPBY(sc-SEQUENCE(COUNT(sc)),sc,LAMBDA(X,@X&IF(ROWS(X)=1,,-MAX(X))),,0),,-1),IFERROR(SUM(MAP(sd,LAMBDA(x,COUNT(ROW(INDIRECT(SUBSTITUTE(x,"-",":"))))))/100),""))