项目10：制作动态考勤表

项目10：制作动态考勤表教师：电话：辅导时间：邮箱：第一部分Word2016高级应用案例知识技能点用WEEK和DAY函数自动显示星期用DAY和DATE函数自动计算每月的天数设置单元格下拉列表选项使用COUNTIF函数计数10.1项目背景小王在帮助人力资源经理录入数据的过程中，人力资源经理有意让小王针对员工考勤登记和统计。由于考勤系统暂时没有到位，希望在这段时间人工考勤过程中，能充分运用Excel强大的自动化功能，尽量减少录入工作量，能做到自动统计。10.2.项目简介本项目利用时间函数、计数函数、判断函数、序列填充等多种函数，自动显示星期、自动计算当月考勤的天数、根据设计的考勤标志自动计算考勤统计等功能。10.3.项目制作10.3.1.制作基本表格新建Excel文档，命名为“员工考勤表.xlsx”，基本样式如图10-1所示。由于后续部分要根据年份和月份计算星期，所以将“2017”和“10”分别放置在一个单元格内（或合并单元格内）。图10-1考勤表基本表格10.3.2.自动显示星期根据“年”、“月”和“日期”中的数据，自动计算“星期”行中的数据。步骤1：在D2单元格中输入公式“=IF((WEEKDAY(DATE($M$1,$Q$1,D3),2))=7,"日",(WEEKDAY(DATE($M$1,$Q$1,D3),2)))”（不包含引号）。公式首先通过DATE函数，将“$M$1”单元格数据即2017、“$Q$1”数据即10和D3单元格数据即1拼接为日期形式，再通过WEEKDAY(****,2)函数，计算选定时间是星期几，由于WEEKDAY(****,2)函数计算结果为1,2,3……7的形式，根据通常写法，当等于7的时候，一般写为“星期日”，因此通过IF函数判断，当WEEKDAY(****,2)函数计算结果等于7的时候，将替换为“日”，否则用数字显示当前的星期日期，如图10-2所示。【小贴士】由于下一步向右填充是，M1单元格和Q1单元格内容不应该自动变化，因此，在引用该单元格内容时，应选择绝对引用“$M$1,$Q$1”。WEEKDAY函数的用法：语法：WEEKDAY(serial_number,[return_type])WEEKDAY函数语法具有下列参数：Serial_number

必需。一个序列号，代表尝试查找的那一天的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用函数DATE(2008,5,23)输入2008年5月23日。如果日期以文本形式输入，则会出现问题。图10-2用公式确定星期Return_type

可选，用于确定返回值类型的数字，每个不同的返回值，代表返回不同的星期格式，具体如下表所示。Return_type返回的数字1或省略数字1（星期日）到7（星期六）。2数字1（星期一）到7（星期日）。3数字0（星期一）到6（星期日）。11数字1（星期一）到7（星期日）。12数字1（星期二）到数字7（星期一）。13数字1（星期三）到数字7（星期二）。14数字1（星期四）到数字7（星期三）。15数字1（星期五）到数字7（星期四）。16数字1（星期六）到数字7（星期五）。17数字1（星期日）到7（星期六）。步骤2：设置D2单元格格式。根据中国习惯，“星期1”一般应写为“星期一”、“星期2”一般应写为“星期二”，依次类推，需要设置D2单元格的数据格式，显示为中文的“一、二”等。右键点击D2单元格，选择【设置单元格格式】，在弹出的对话框选择【数字】选项卡，在【分类】中选择【特殊】，在类型列表选择【中文小写数字】，点击【确定】按钮即可，如图10-3所示图10-3设置单元格格式第三步：选中D2单元格，拖动右下角的“填充柄”向右填充，直到AH3单元格。效果如图10-4所示。图10-4设置星期后的效果当确定年和月之后，需要AH1单元格自动显示当月的天数。在AH1单元格中输入公式“=DAY(DATE(M1,Q1+1,1)-1)”，公式先获取M1单元格的年份，此处为“2017”，以及Q1单元格月份（此处为10）的下一个月“Q1+1”，即11月，结合DATE第三个参数“1”，获取2017年11月1日的日期，再通过“DATE(M1,Q1+1,1)-1”，获取11月1日之前一天的日期，本例为2017年10月31日，最后用DAY函数获取2017年10月31日的天数，即31，填入到AH1单元格。如图10-5所示。图10-5获取当前月的天数为统一考勤标志，需要设定统一的考勤符号，当然也可以设定用汉字或者其他自己认可方便的符号均可，也可以根据实际情况确定更多考勤类别。本例假设有如下的符号规定。为方便录入，采用下拉列表选择的方法来设置，每次录入考勤的时候，仅需从下拉列表中选择即可。符号考勤含义√正常×旷工△公假☆私假选择需要考勤的所有单元格，选择【数据】选项卡->【数据验证】->【数据验证】，弹出数据验证对话框，在【允许】下拉列表中选择【序列】，在【来源】部分选中单元格折叠按钮，然后选择$B$37:$B$40区域，点击【确定】按钮，如图10-6所示。其中$B$37:$B$40是考勤符号标志区域。图10-6用列表设置考勤符号一旦设置完毕，每个人的考勤，就可以从下拉列表中选择了。录入考勤效果如图10-7所示。图10-7录入考勤10.3.5.自动考勤统计在表格中每个人的最后，划出一块区域，用来统计每个人的出勤天数，如图10-8所示。使用COUNTIF函数统计各种考勤符号出现的次数，由于每天的上午和下午都需要考勤，但统计是以天为单位的，因此在用COUNTIF函数统计之后，需要将考勤符号出现的次数再除以2，得到最终的以天为单位的考勤统计。图10-8考勤统计区域在统计区域中相应单元格中，输入公式“=COUNTIF(D4:AH5,AM$2)/2”（不包含引号），表示在D4:AH5单元格范围内，统计AM$2（即符号√）出现的次数，并用该数据除以2，得到正常出勤的以天为单位的数据，在该单元格右下方，拖动“填充柄”，向下填充，即可得到所有人的正常出勤天数。注意在此处对单元格AM$2的引用方法。在“旷工”单元格的下一个单元格，输入公式“=COUNTIF(D4:AH5,AM$3)/2”，表示在D4:AH5单元格范围内，统计