考勤数据判断

dylanleelee

需要在的校核表的C列判断A列的人员，在B列的日期是否在出差。出差记录见记录表。

感谢各位大神！！！！！

良良

C3=IF(OR((B3>=[记录表.xlsx]Sheet1!C$2:C$80)*(B3<=[记录表.xlsx]Sheet1!D$2:D$80)*MMULT(N([记录表.xlsx]Sheet1!A$2:B$80=A3),{1;1})),"出差","")，三键。

part98

=IF(SUM((([记录表.xlsx]Sheet1!$A$2:$A$80=考勤记录!A3)+([记录表.xlsx]Sheet1!$B$2:$B$80=考勤记录!A3))*ISNUMBER(VDB(1,,[记录表.xlsx]Sheet1!$D$2:$D$80,[记录表.xlsx]Sheet1!$C$2:$C$80,B3))),"出差","")

dylanleelee

良良 发表于 2024-6-5 20:22
C3=IF(OR((B3>=[记录表.xlsx]Sheet1!C$2:C$80)*(B3

大神牛啊！谢谢

dylanleelee

part98 发表于 2024-6-5 20:23
=IF(SUM((([记录表.xlsx]Sheet1!$A$2:$A$80=考勤记录!A3)+([记录表.xlsx]Sheet1!$B$2:$B$80=考勤记录!A3) ...

太给力了，牛！谢谢！

你是那块小饼干

1. =IF(TEXTJOIN(",",TRUE,LET(_T,FILTER($H$2:$I$80,MMULT(N($F$2:$G$80=A2),SEQUENCE(2,,1,0)),""),
   
2. _X,CHOOSECOLS(_T,1),
   
3. _Y,CHOOSECOLS(_T,2),
   
4. FILTER(_T,MMULT(N(HSTACK(_X<=B2,_Y>=B2)),SEQUENCE(2,,1,0))=2,"")))="","","出差")
   

复制代码

dylanleelee

你是那块小饼干 发表于 2024-6-6 14:17

感谢！另一种思路

秃头发师

我擦，你们的那么简洁，看来是时候学习下MMULT了
=LET(x,CHOOSECOLS(FILTER([记录表.xlsx]Sheet1!$C:$D,([记录表.xlsx]Sheet1!$A:$A=A4)+([记录表.xlsx]Sheet1!$B:$B=A4)),1),y,CHOOSECOLS(FILTER([记录表.xlsx]Sheet1!$C:$D,([记录表.xlsx]Sheet1!$A:$A=A4)+([记录表.xlsx]Sheet1!$B:$B=A4)),2),XLOOKUP(B4,
TOCOL(MAKEARRAY(ROWS(x),MAX(y-x)+1,LAMBDA(a,b,IF(b<=INDEX(y,a)-INDEX(x,a)+1,INDEX(x,a)+b-1,p)))),MAKEARRAY(ROWS(x)*(MAX(y-x)+1),1,LAMBDA(r,s,"是")),""))

你是那块小饼干

1. =LET(_T,FILTER([记录表.xlsx]Sheet1!$C$2:$D$80,ISNUMBER(FIND(A3,[记录表.xlsx]Sheet1!$A$2:$A$80&[记录表.xlsx]Sheet1!$B$2:$B$80)),""),IF(SUM((CHOOSECOLS(_T,2)>=B3)*(CHOOSECOLS(_T,1)<=B3))>0,"是",""))

复制代码