[已解决]怎么用考勤机的excel数据生成考勤统计表

zylg_com

现有2个表格
A是“考勤机”上的数据

考勤 号码	自定义 编号	姓名	出勤时间	出勤 状态	更正 状态	异常 情况	操作
2001		于蓉	2014-5-4 8:27	上班 签到	加班 签到	自由 加班
2001		于蓉	2014-5-4 17:33	上班 签到	加班 签退	自由 加班

B是要生成的“考勤统计表”

序号

姓名

部门

日期

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

星期

四

五

六

日

一

二

三

四

五

六

日

一

二

三

四

1

于蓉

财务     中心

上

无

无

无

是

下

无

无

无

现在想在B"考勤统计表"中根据A“考勤机”的【出勤时间】自动统计签到情况
每天早上9：00之前，每天下午17：30之后

在早上9：00之前签到的显示【是】
在早上9：01-11：00签到的显示【迟】
在下午15：30-17：29签到的显示【早】
在下午17：30之后签到的显示【否】
当天没有记录的显示【无】

表格中：姓名：于蓉 的员工在2014-5-4 8：27签到
在B"考勤统计表"中的上4就显示【是】

姓名：于蓉 的员工在2014-5-1到3日都没有记录
在B"考勤统计表"中的上1，上2，上3，下1，下2，下3就显示【无】

请问下这个效果要怎么实现

最佳答案

月排行榜 / 总排行榜

baksy

2014-5-26 08:58

考勤统计表的E6单元格和E7单元格各复制以下公式，
三键回车( 公式复制后,点一下公式编辑栏的任意位置,先按住 shift、ctrl 两个键,然后敲enter键。)
E6=IFERROR(IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24<=9),ROW($C$2:$C$61))),"是",IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>9)*(MOD(考勤机!$D$2:$D$61,1)*24<11),ROW($C$2:$C$61))),"迟","无")),"")
E7=IFERROR(IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>17.5),ROW($C$2:$C$61))),"否",IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>=15.5)*(MOD(考勤机!$D$2:$D$61,1)*24<17.5),ROW($C$2:$C$61))),"早","无")),"")
E6、E7的公式右托，
复制E6、E7单元格往下粘贴、横拉，完成。

跳转到最佳答案楼层

baksy

考勤统计表的E6单元格和E7单元格各复制以下公式，
三键回车( 公式复制后,点一下公式编辑栏的任意位置,先按住 shift、ctrl 两个键,然后敲enter键。)
E6=IFERROR(IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24<=9),ROW($C$2:$C$61))),"是",IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>9)*(MOD(考勤机!$D$2:$D$61,1)*24<11),ROW($C$2:$C$61))),"迟","无")),"")
E7=IFERROR(IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>17.5),ROW($C$2:$C$61))),"否",IF(MIN(IF((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>=15.5)*(MOD(考勤机!$D$2:$D$61,1)*24<17.5),ROW($C$2:$C$61))),"早","无")),"")
E6、E7的公式右托，
复制E6、E7单元格往下粘贴、横拉，完成。

zylg_com

baksy 发表于 2014-5-26 08:58
考勤统计表的E6单元格和E7单元格各复制以下公式，
三键回车( 公式复制后,点一下公式编辑栏的任意位置,先按 ...

谢谢高手大哥的指导，我在学习这个公式的过程又遇到2个问题想请教，

现在只能正常获得【有】和【无】的结果
1、E6和E7中的条件【迟】【早】无法显示
在早上9：01-11：00签到的显示【迟】
在下午15：30-17：29签到的显示【早】
没有办法正常显示
是公式中什么地方还要调整么？
比如早上9：06的应该是【迟】但是显示的是【无】。

2、尝试删除公式中的一段后原来显示“无”的，显示为“0”
删除E6中的下面公式：
IF(SUMPRODUCT((考勤机!$C$2:$C$61=$B6)*(DAY(考勤机!$D$2:$D$61)=E$4)*(MOD(考勤机!$D$2:$D$61,1)*24>9)*(MOD($D$2:$D$61,1)*24<11))>0,"迟")

能正常获得结果但是原来显示“无”的，显示为“0”
想问一下 这个是为什么呢？

zylg_com

不好意思，回复重复了删除一个

baksy

zylg_com 发表于 2014-5-26 21:37
谢谢高手大哥的指导，我在学习这个公式的过程又遇到2个问题想请教，

现在只能正常获得【有】和【无】的 ...

2楼公式已更新。