《跟着名师学电脑Excel应用入门实例》课件-第11章

实例11制作员工考勤情况统计表—使用模板创建工作表—设置错误提示消息—引用其他工作表的数据—设置DATEDIF函数及其参数—应用渐变数据条格式

学习情境

员工考勤制度是否严谨，直接关系公司的长期运营。为了稳定发展，更好地为小区服务，祥和小区物业公司制定了员工考勤制度，并为每位员工制作考勤卡，将正常工作时数、加班时数、病假、带薪假都统计在内，作为工资发放和年终业绩评估时的参考。

编排效果

掌握技能

通过本实例，将学会以下技能：

使用模板创建工作表。

设置错误提示消息。

引用其他工作表的数据。

设置DATEDIF函数及其参数。

应用渐变数据条格式。

从模板中创建考勤卡

工作簿模板包含了已经设计好的工作表，用户可根据需要迅速生成。Excel2010包含了本地模板和网络模板，本地模板不需要连接互联网，通过新建即可生成具有专业特色的工作表。

1.新建Excel文档。

2.单击“文件”选项卡→“新建”命令。

3.在“可用模板”列表中，单击“样本模板”项，进入“样本模板”列表。3.单击1.新建2.单击

4.在“样本模板”列表中，单击“考勤卡”项。

5.在右侧的预览区中，单击“创建”按钮完成“考勤卡”的创建。4.单击5.单击

设置单元格格式

根据模板完成创建后，Excel自动新建了名为“考勤卡1”的工作簿，其中包含一个名为“考勤卡”的工作表。

1.单击C7单元格，输入“0111张小雨”；单击C16单元格，输入“1-7”。

在表格中所输入的日期时间格式是新建工作表时模板中自带的格式，根据需要，可重新设置。1.输入

2.选中C16、C21:C27单元格。

3.右键单击选中的单元格的任意位置，打开快捷菜单。

4.单击“设置单元格格式”命令，打开“设置单元格格式”对话框。

5.单击“数字”选项卡→“日期”分类。

6.在“类型”下拉列表中，选中“3月14日”项。

7.单击“确定”按钮返回工作表。3.右击4.单击2.选中

设置数据有效性

公司实行计时工资制度，正常工作时间的工资为50元，加班工资为原工资的1.5倍，病假工资为原工资的0.8倍，带薪假工资与原工资相同。另外，员工请假不得超过2个小时，否则不予发放病假工资。

模板生成时内含公式，因此在单元格中输入数据，可立刻计算出该员工的计时工资。

1.输入如图所示的数据。1.输入

2.选中F21:F27单元格。

3.单击“数据”选项卡→“数据工具”组→“数据有效性”按钮，打开“数据有效性”对话框。

4.在“设置”选项卡的“允许”栏中，选择“整

数”项。

5.数据介于1、2之间，输入最小值“1”，最大值“2”。

6.单击“确定”按钮完成设置。2.选中4.选择3.单击

设置错误提示消息

1.打开“数据有效性”对话框，单击“出错警告”选项卡。

2.单击“样式”栏→“停止”项。

3.在“错误信息”中输入“病假不符合规定”。

4.单击“确定”按钮完成设置，返回工作表。1.单击3.输入2.选中4.单击

5.在F27单元格中，输入“3”，按“Enter”键。5.输入

6.当输入的数字不在设置范围时，系统弹出“错误消息提示”对话框，单击“取消”按钮，返回工作表。6.单击

建立副本

1.重命名“考勤卡”工作表标签为“考勤卡0111”。

2.右键单击“考勤卡0111”工作表标签。

3.在打开的快捷菜单中，单击“移动或复制”命令，打开“移动或复制工作表”对话框。3.单击2.右击1.重命名

4.选中“建立副本”复选框。

5.单击“确定”按钮完成副本的建立。4.选中5.单击

6.重命名工作表标签为“考勤卡0110”。

7.将鼠标放置于“考勤卡0111”工作表标签，按下“Ctrl”键的同时向右拖动鼠标。当黑色箭头位于“考勤卡0111”工作表标签右侧位置时，释放鼠标，并重命名副本工作表为“考勤卡0120”。6.重命名7.拖动

修改数据

1. 单击工作表“考勤卡0110”，输入图中所示内容。1.输入

2. 单击工作表“考勤卡0120”，输入图中所示内容。2.输入

3.单击工作表标签栏的“插入工作表”按钮，插入新工作表Sheet1。

4.在A1:H5单元格中分别输入图中所示内容，并设置格式为自动调整列宽、居中。3.插入4.输入

引用其他工作表的数据

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.修改3.单击

4.将表格中的“工时总计”、“病假”、“带薪假”字段也进行1～3步骤的操作，引用并填充至相应的单元格中。其中，工时总计为“正常工作时数”与“加班时数”的总和。4.引用

插入DATEDIF函数

工龄计算经常出现在Excel数据的统计与计算问题中，而DATEDIF函数能够返回两个日期之间的间隔数，返回值可自行设定为年、月或日。

1.单击F3单元格，输入“DATEDIF()”函数。1.输入

2.在DATEDIF函数的括号中，单击B3单元格引用数据。

3.继续输入逗号，再输入“TO”，此时在公式的下方会自动出现函数提示框。

4.双击提示框中的“TOD-AY”函数。2.单击3.输入4.双击

5.继续输入逗号，再输入“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.单击“确定”按钮，打开“选定参数”对话框。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.填充

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