考勤数据判断

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,"")))="","","出差")
   

复制代码