电脑
操作系统:Windwos7
办公软件:Excel 2007
使用EXCEL2007新建一个空白文档——在sheet1表格制作员工考勤基本信息:日期、姓名、签到、请假
接着在sheet2表制作如下图所示的考勤内容:姓名、正常上班次数、迟到、旷工、请假。
统计正常上班次数:输入员工姓名——选择B2单元格——输入公式:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A11)*(Sheet1!$C$2:Sheet1!$C$1500>='上班')),并同时按下ctrl+shift+回车键。
统计迟到次数:在C2输入函数公式:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$C$2:Sheet1!$C$1500='迟到'))——同时按下ctrl+shift+回车键
统计旷工次数:在旷工列D2输入公式:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$C$2:Sheet1!$C$1500='旷工'))——ctrl+shift+回车键——向下填充公式。
统计请假次数:在F2输入公式:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$D$2:Sheet1!$D$1500='请假'))——ctrl+shift+回车键——向下填充公式。
如何用Excel自动统计多人考勤天数:(1)制作员工每天考勤信息表;(2)制作统计员工考勤表头;(3)统计正常上班次数函数 =SUM((Sheet1!$B$2:Sheet1!$B$1500=A11)*(Sheet1!$C$2:Sheet1!$C$1500>='上班'))(4)统计迟到次数函数:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$C$2:Sheet1!$C$1500='迟到'))(5)统计旷工次数函数=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$C$2:Sheet1!$C$1500='旷工'))(6)统计请假函数:=SUM((Sheet1!$B$2:Sheet1!$B$1500=A2)*(Sheet1!$D$2:Sheet1!$D$1500='请假'))Tips:数组函数需要同时按ctrl+shift+回车键
希望能帮助到你