各位大神，能帮忙求助员工的在线时间

hxj119

=LET(a,UNIQUE(A2:A10763),b,MAP(C2:C10793,LAMBDA(x,SUM(IFERROR(REGEXP(x,{"\d{1,2}(?=时)","\d{1,2}(?=分)","\d{1,2}(?=秒)"})&"",0)*{3600,60,1}))),REDUCE({"姓名","合计时长"},a,LAMBDA(x,y,VSTACK(x,HSTACK(y,TEXT(SUM(FILTER(b,A2:A10793=y))/86400,"[hh]:mm:ss"))))))

喂你吃粑粑

1. =LET(Z,SUM(LET(X,FILTER($C$2:$C$10793,($A$2:$A$10793=E2)*($C$2:$C$10793<>"")),MAP(X,LAMBDA(Y,SUM(IF(REGEXP(Y,"[一-龟]+")={"时";"分";"秒"},{3600;60;1},0)*REGEXP(Y,"\d+")))))),INT(Z/(24*3600))&"天"&INT(MOD(Z,24*3600)/3600)&"时"&INT(MOD(MOD(Z,24*3600),3600)/60)&"分"&MOD(MOD(MOD(Z,24*3600),3600),60)&"秒")

复制代码

zxf520

喂你吃粑粑 发表于 2024-7-14 10:08

非常感谢大神呀··这样让我简化了好多步骤~

安然祥和

=IF(COUNTIF($A$2:A2,A2)=1,SUM(($A$2:$A$10793=A2)*(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("时",$C$2:$C$10793)),"0时","")&$C$2:$C$10793,"时",IF(ISERROR(FIND("分",$C$2:$C$10793)),":0:",":")),"分",":"),"秒",)&IF(ISERROR(FIND("秒",$C$2:$C$10793)),0,""))),"")

数组公式