[已解决]函数求解

w1098

=LOOKUP(OFFSET(原始刷卡记录!$D$1,SMALL(IF(DAY(原始刷卡记录!$C$2:$C$19)=-LOOKUP(,-LEFT(B$1,ROW($1:$5)))*(原始刷卡记录!$B$2:$B$19=$A2),ROW($1:$18),20),MOD(COLUMN(),2)+1),),{0;0.354166666666667;0.5;0.534722222222222;0.722222222222222}-0.1,{"未打卡";"正常";"下午请假";"上午请假";"正常"})

zxy999

数组公式,有点长:

=IF(SUM((原始刷卡记录!$B$2:$B$19=$A2)*(DAY(原始刷卡记录!$C$2:$C$19)=--LEFT(B$1,LEN(B$1)-1))),VLOOKUP(LARGE((原始刷卡记录!$B$2:$B$19=$A2)*(DAY(原始刷卡记录!$C$2:$C$19)=--LEFT(B$1,LEN(B$1)-1))*原始刷卡记录!$D$2:$D$19,IF(RIGHT(B$1)="上",2,1)),{0.333333333333333,"正常";0.354166666666667,"迟到";0.479166666666667,"上午请假";0.5,"下午请假";0.722222222222222,"正常";0.743055555555555,"加班"},2),"未打卡")

GFPb3AgY.rar (5.66 KB, 下载次数: 4)

2010-9-26 16:11 上传

点击文件名下载附件
函数求解