考勤公式求助

edwin11891

huanxiangboy 发表于 2025-4-10 21:59
稍微有点瑕疵，比如张逸尘1月6号8点至8点半，他还有个1月6号12点至1月7号17点，这种情况1月6号就是0.5+4= ...

终于达到目标，但是公式实在有点长。为了测试，第37、38行数据稍有修改。

1. =LET(sName,A2:A38,sS,B2:B38,sE,C2:C38,Ref,DROP(REDUCE("",UNIQUE(sName),LAMBDA(Zx,Zy,VSTACK(Zx,LET(sa,FILTER($B$2:$C$38,$A$2:$A$38=Zy),s,DROP(REDUCE("",SEQUENCE(ROWS(sa)),LAMBDA(x,y,VSTACK(x,LET(Ta,INDEX(sa,y,1),Tb,INDEX(sa,y,2),IF(INT(Ta)=INT(Tb),HSTACK(Zy,INDEX(sa,y,{1,2})),DROP(REDUCE("",SEQUENCE((INT(Tb)-INT(Ta))+1,,0),LAMBDA(XX,YY,VSTACK(XX,IFS(YY=0,HSTACK(Zy,Ta,INT(Ta)+1),YY=(INT(Tb)-INT(Ta)),HSTACK(Zy,INT(Tb),Tb),TRUE,HSTACK(Zy,INT(Ta)+YY,INT(Ta)+YY+1))))),1)))))),1),s)))),1),sT,MAP(CHOOSECOLS(Ref,2),TAKE(Ref,,-1),LAMBDA(x,y,LET(t,SEQUENCE(INT(y)-INT(x)+1,,INT(x))+1/24*{8,12,13,17},SUM(TEXT(x-t,"[<]!0")*{-1,1,-1,1},TEXT(y-t,"[<]!0")*{1,-1,1,-1})))),NN,TAKE(Ref,,1),Re,PIVOTBY(HSTACK(MATCH(NN,UNIQUE(sName),),NN),TEXT(CHOOSECOLS(Ref,2),"yyyy/mm/dd"),sT,LAMBDA(x,ROUND(SUM(x)*24,1)),0,0,,0),DROP(Re,,1))

复制代码

edwin11891

送佛送到西，开个国际火车，把全列日期的也贴上来吧，只是完成目标，没有考虑优化。

1. =LET(sName,A2:A38,sS,B2:B38,sE,C2:C38,Ref,DROP(REDUCE("",UNIQUE(sName),LAMBDA(Zx,Zy,VSTACK(Zx,LET(sa,FILTER($B$2:$C$38,$A$2:$A$38=Zy),s,DROP(REDUCE("",SEQUENCE(ROWS(sa)),LAMBDA(x,y,VSTACK(x,LET(Ta,INDEX(sa,y,1),Tb,INDEX(sa,y,2),IF(INT(Ta)=INT(Tb),HSTACK(Zy,INDEX(sa,y,{1,2})),DROP(REDUCE("",SEQUENCE((INT(Tb)-INT(Ta))+1,,0),LAMBDA(XX,YY,VSTACK(XX,IFS(YY=0,HSTACK(Zy,Ta,INT(Ta)+1),YY=(INT(Tb)-INT(Ta)),HSTACK(Zy,INT(Tb),Tb),TRUE,HSTACK(Zy,INT(Ta)+YY,INT(Ta)+YY+1))))),1)))))),1),s)))),1),sT,MAP(CHOOSECOLS(Ref,2),TAKE(Ref,,-1),LAMBDA(x,y,LET(t,SEQUENCE(INT(y)-INT(x)+1,,INT(x))+1/24*{8,12,13,17},SUM(TEXT(x-t,"[<]!0")*{-1,1,-1,1},TEXT(y-t,"[<]!0")*{1,-1,1,-1})))),NN,TAKE(Ref,,1),Re,PIVOTBY(HSTACK(MATCH(NN,UNIQUE(sName),),NN),TEXT(CHOOSECOLS(Ref,2),"yyyy/mm/dd"),sT,LAMBDA(x,ROUND(SUM(x)*24,1)),0,0,,0),Ta,DROP(Re,,1),Fn,DROP(TAKE(Ta,,1),1),Tb,DATE(YEAR(--B2),MONTH(--B2),1),Tc,TEXT(SEQUENCE(,EOMONTH(B2,0)-Tb+1,Tb),"yyyy/mm/dd"),Td,DROP(REDUCE("",Tc,LAMBDA(XX,YY,HSTACK(XX,IFNA(CHOOSECOLS(Ta,MATCH(YY,TEXT(TAKE(Ta,1),"yyyy/mm/dd"),)),"")))),,1),Te,IFNA(VSTACK(Tc,DROP(Td,1)),""),Tf,HSTACK(VSTACK("姓名",Fn),Te),Tf)

复制代码

huanxiangboy

edwin11891 发表于 2025-4-11 09:39
送佛送到西，开个国际火车，把全列日期的也贴上来吧，只是完成目标，没有考虑优化。

大佬，向右拖，向下拖的公式怎么编写

edwin11891

huanxiangboy 发表于 2025-4-11 14:29
大佬，向右拖，向下拖的公式怎么编写

不考虑，不然公式长度直接过台湾海峡了！

erdgh12

=LET(arr,A2:C38,brr,DROP(REDUCE(0,SEQUENCE(ROWS(arr)),LAMBDA(x,y,LET(n,ROUNDUP((INDEX(arr,y,3)-INDEX(arr,y,2))*24,),VSTACK(x,HSTACK(REPTARRAY(INDEX(arr,y,1),n),TOCOL(TEXT(SEQUENCE(,n,INDEX(arr,y,2),1/24),"yyyy-mm-dd"))))))),1),PIVOTBY(INDEX(brr,,1),INDEX(brr,,2),INDEX(brr,,2),COUNTA,0,0,,0))

mao12306

向楼上各位老师学习，重新写了一个应该能满足楼主要求了。黄色标记为了测试与原数据不同

1. =LET(data,LET(d,IFERROR(--A2:C38,A2:C38),DROP(REDUCE(0,SEQUENCE(ROWS(d)),LAMBDA(x,y,LET(n,INDEX(d,y,1),st,INDEX(d,y,2),en,INDEX(d,y,3),m_en,INT(en),m_st,INT(st),k,INT(en)-INT(st),VSTACK(x,IF(k=0,INDEX(d,y,),LET(m_1,HSTACK(n,st,m_st+0.99931),m_2,HSTACK(n,m_en+0.00069,en),IF(k=1,VSTACK(m_1,m_2),VSTACK(m_1,IFNA(HSTACK(n,DROP(SEQUENCE(k,,m_st),1)+{0.00069,0.99931}),n),m_2)))))))),1)),c_lab,VSTACK(INT(CHOOSECOLS(data,2)),SEQUENCE(31,,45658)),fill,EXPAND("",31,,""),r_lab,VSTACK(TAKE(data,,1),fill),v_arr,VSTACK(ROUND(MAP(CHOOSECOLS(data,2),TAKE(data,,-1),LAMBDA(x,y,(MAX(0,MIN(MOD(y,1),TIME(12,0,0))-MAX(MOD(x,1),TIME(8,0,0))))+MAX(0,MIN(MOD(y,1),TIME(17,0,0))-MAX(MOD(x,1),TIME(13,0,0))))),6),fill),g,VSTACK(MATCH(TAKE(data,,1),TAKE(data,,1),0),fill),DROP(PIVOTBY(HSTACK(g,r_lab),c_lab,v_arr,SUM,0,0,,0),-1,1))

复制代码

一杯绿茶来了

F2下拉右拉
=LET(s,FILTER($B$2:$C$38,($A$2:$A$38=$E2)*(INT($B$2:$B$38)=F$1),""),IFERROR(SUM(INDEX(FREQUENCY(MOD(SEQUENCE(NPV(-2,s*1440),,@s,1/1441),1),({8;12;13;17}-0.001)/24),{2,4}))/60,))