1、实例实例11 11 制作员工考勤情况统计表制作员工考勤情况统计表 使用模板创建工作表使用模板创建工作表 设置错误提示消息设置错误提示消息 引用其他工作表的数据引用其他工作表的数据 设置设置DATEDIF函数及其参数函数及其参数 应用渐变数据条格式应用渐变数据条格式学习情境学习情境员工考勤制度是否严谨，直接关系公司的长期运营。为了稳定发展，更好地为小区服务，祥和小区物业公司制定了员工考勤制度，并为每位员工制作考勤卡，将正常工作时数、加班时数、病假、带薪假都统计在内，作为工资发放和年终业绩评估时的参考。编排效果编排效果掌握技能掌握技能通过本实例，将学会以下技能：使用模板创建工作表。设置错误提示消息
2、。引用其他工作表的数据。设置DATEDIF函数及其参数。应用渐变数据条格式。从模板中创建考勤卡从模板中创建考勤卡工作簿模板包含了已经设计好的工作表，用户可根据需要迅速生成。Excel 2010包含了本地模板和网络模板，本地模板不需要连接互联网，通过新建即可生成具有专业特色的工作表。1.新建Excel文档。2.单击“文件”选项卡“新建”命令。3.在“可用模板”列表中，单击“样本模板”项，进入“样本模板”列表。3.单击1.新建2.单击4.在“样本模板”列表中，单击“考勤卡”项。5.在右侧的预览区中，单击“创建”按钮完成“考勤卡”的创建。4.单击5.单击设置单元格格式设置单元格格式根据模板完成创建后
3、，Excel自动新建了名为“考勤卡1”的工作簿，其中包含一个名为“考勤卡”的工作表。1.单击C7单元格，输入“0111张小雨”；单击C16单元格，输入“1-7”。在表格中所输入的日期时间格式是新建工作表时模板中自带的格式，根据需要，可重新设置。1.输入2.选中C16、C21:C27单元格。3.右键单击选中的单元格的任意位置，打开快捷菜单。4.单击“设置单元格格式”命令，打开“设置单元格格式”对话框。5.单击“数字”选项卡“日期”分类。6.在“类型”下拉列表中，选中“3月14日”项。7.单击“确定”按钮返回工作表。3.右击4.单击2.选中设置数据有效性设置数据有效性公司实行计时工资制度，正常工作
4、时间的工资为50元，加班工资为原工资的1.5倍，病假工资为原工资的0.8倍，带薪假工资与原工资相同。另外，员工请假不得超过2个小时，否则不予发放病假工资。模板生成时内含公式，因此在单元格中输入数据，可立刻计算出该员工的计时工资。1.输入如图所示的数据。1.输入2.选中F21:F27单元格。3.单击“数据”选项卡“数据工具”组“数据有效性”按钮，打开“数据有效性”对话框。4.在“设置”选项卡的“允许”栏中，选择“整 数”项。5.数据介于1、2之间，输入最小值“1”，最大值“2”。6.单击“确定”按钮完成设置。2.选中4.选择3.单击设置错误提示消息设置错误提示消息1.打开“数据有效性”对话框，单
5、击“出错警告”选项卡。2.单击“样式”栏“停止”项。3.在“错误信息”中输入“病假不符合规定”。4.单击“确定”按钮完成设置，返回工作表。1.单击3.输入2.选中4.单击5.在F27单元格中，输入“3”，按“Enter”键。5.输入6.当输入的数字不在设置范围时，系统弹出“错误消息提示”对话框，单击“取消”按钮，返回工作表。6.单击建立副本建立副本1.重命名“考勤卡”工作表标签为“考勤卡0111”。2.右键单击“考勤卡0111”工作表标签。3.在打开的快捷菜单中，单击“移动或复制”命令，打开“移动或复制工作表”对话框。3.单击2.右击1.重命名4.选中“建立副本”复选框。5.单击“确定”按钮完
6、成副本的建立。4.选中5.单击6.重命名工作表标签为“考勤卡0110”。7.将鼠标放置于“考勤卡0111”工作表标签，按下“Ctrl”键的同时向右拖动鼠标。当黑色箭头位于“考勤卡0111”工作表标签右侧位置时，释放鼠标，并重命名副本工作表为“考勤卡0120”。6.重命名7.拖动修改数据修改数据1.单击工作表“考勤卡0110”，输入图中所示内容。1.输入2.单击工作表“考勤卡0120”，输入图中所示内容。2.输入3.单击工作表标签栏的“插入工作表”按钮，插入新工作表Sheet1。4.在A1:H5单元格中分别输入图中所示内容，并设置格式为自动调整列宽、居中。3.插入4.输入引用其他工作表的数据引用
7、其他工作表的数据1.单击A3单元格，输入“=”号。2.单击“考勤卡0110”工作表。3.单击C7单元格，编辑栏中出现“=考勤卡0110!C7”，表示引用了“考勤卡0110”工作表C7单元格中的数据。按“Enter”键完成输入。3.单击2.单击4.返回工作表Sheet1，单击A3单元格，选中公式中的“C7”，按“F4”键切换引用方式为绝对引用。复制绝对引用单元格复制绝对引用单元格1.将鼠标置于A3单元格的右侧，填充公式至A4和A5单元格。1.填充2.修改A4单元格的内容为“考勤卡0111!$C$7”，A5单元格的内容为“考勤卡0120!$C$7”。3.单击编辑栏的“输入”按钮完成公式修改。2.修
8、改3.单击4.将表格中的“工时总计”、“病假”、“带薪假”字段也进行13步骤的操作，引用并填充至相应的单元格中。其中，工时总计为“正常工作时数”与“加班时数”的总和。4.引用插入插入DATEDIF函数函数工龄计算经常出现在Excel数据的统计与计算问题中，而DATEDIF函数能够返回两个日期之间的间隔数，返回值可自行设定为年、月或日。1.单击F3单元格，输入“DATEDIF()”函数。1.输入2.在DATEDIF函数的括号中，单击B3单元格引用数据。3.继续输入逗号，再输入“TO”，此时在公式的下方会自动出现函数提示框。4.双击提示框中的“TOD-AY”函数。2.单击3.输入4.双击5.继续输
9、入逗号，再输入“Y”，表示以年份返回B3和目前之间的差值。按“Enter”键完成输入。6.将F3单元格的公式填充至F4和F5单元格。5.输入6.填充插入插入LOOKUP函数函数当需要对两组一一对应的数据进行判断时，可使用LOOKUP函数。如员工累计工作已满1年不满10年的，年休假5天；已满10年不满20年的，年休假10天；已满20年的，年休假15天；未满1年的，无带薪假。1.单击G3单元格，再单击编辑栏的“插入公式”按钮，打开“插入函数”对话框。单击“或选择类别”列表框的下拉箭头，在下拉列表中选择“查找与引用”类函数。2.在“选项函数”栏中，选中“LOOKUP”函数。3.单击“确定”按钮，打开
10、“选定参数”对话框。1.单击2.选中3.单击4.选中第一项。5.单击“确定”按钮完成参数设置。4.选中5.单击6.单击“Lookup_value”参数后面的编辑框，在工作表中单击单元格F3，引用该单元格的值。7.单击“Loopup_vector”参数后面的编辑框，输入数组“0,1,10,20”。8.单击“Result_vector”参数后面的编辑框，输入数组“0,5,10,15”。9.单击“确定”按钮完成输入。6.引用9.单击7.输入8.输入增加小数位增加小数位1.在G3单元格中，继续输入公式“-E3/8”，按“Enter”键返回工作表。2.将G3单元格的公式填充至G4和G5单元格。1.输入2
11、.填充3.选中G3:G5单元格。4.单击“开始”选项卡“数字”组“增加小数位”按钮。4.单击3.选中插入插入IF函数函数1.单击H3单元格，输入“=”号，进入公式编辑状态。2.继续输入“if”，根据公式提示栏的提示内容，双击“IF”项，此时公式中显示“=IF(”。1.单击2.双击3.继续输入“（C3-D3-E3）56，”，表示达到优秀的基础值为每周7天，每天工作8小时，即56个工时。3.输入4.继续输入“优秀,良好)”，表示大于优秀基础值的为优秀，小于等于基础值的为良好。5.单击编辑栏中的“输入”按钮退出公式编辑状态。6.将H3单元格的公式填充至H4和H5单元格。4.输入5.单击6.填充应用单元格样式应用单元格样式1.单击A1单元格。2.单击“开始”选项卡“样式”组“单元格样式”按钮“标题1”项，应用标题样式至“员工考勤情况统计”。1.单击2.单击3.选中A2:H5单元格。4.单击“开始”选项卡“样式”组“套用表格格式”按钮“表样式中等深浅9”项，应用表格样式。5.选中G3:G5单元格。6.单击“开始”选项卡“样式”组“条件格式”按钮“数据条”项“橙色数据条”，应用条件格式。3.选中4.单击5.选中6.单击
