第三单元 薪酬福利数据表格设计.

Presentation on theme: "第三单元 薪酬福利数据表格设计."— Presentation transcript:

1 第三单元 薪酬福利数据表格设计

2 单元目录 任务一 员工加班统计表设计 任务二 员工考勤统计表设计 任务三 员工奖金统计表设计 任务四 员工工资统计表设计

3 任务描述 人力资源部要按照企业管理规定按月核算每位员工的加班费，设计员工加班月统计表。 本任务要创建的员工加班月统计表，如图3-1所示。

4 任务实施 1．创建基本工作表

5 任务实施 2．输入员工基本信息 （1）输入信息并设置格式
“员工编号”列格式设置为自定义“000” ，“加班日期”列设置为日期“01/3/14” ，“加班用时”和“小时工资”列设置为数值“ ” ，“应付加班费”列设置为货币“￥-1,234.10” 。 （2）利用数据有效性快速输入“加班原因”列。

6 任务实施 ① 定义单元格区域名称。将工作表“Sheet2”重命名为“序列”，在其中输入加班原因，选中A1：A7单元格区域，如图3-4所示，单击“公式”→“定义的名称”→“定义名称”按钮，弹出“新建名称”对话框，在“名称”文本框中输入“yy”，如图3-4所示。

7 任务实施 ② 输入“加班原因”列数据。返回工作表“员工加班月统计表”，单击D4单元格，单击“数据”→“数据工具”→“数据有效性”按钮，在弹出的“数据有效性”对话框中设置有效性条件，如图3-5所示。。

8 任务实施 通过自动填充功能实现所有员工加班原因的输入，如图3-6所示。

9 任务实施 提个醒 公式“=HOUR(H4-G4)+ MINUTE(H4-G4)/60”计算加班时间小时数。
3.计算应付加班费 （1）计算“加班用时”数据列。选中I4单元格，输入公式： =HOUR(H4-G4)+ MINUTE(H4-G4)/60 （2）通过公式复制，计算所有人的“加班用时” 列数据。 提个醒 公式“=HOUR(H4-G4)+ MINUTE(H4-G4)/60”计算加班时间小时数。 输入公式单元格的数据格式要设置为数值类型。其中：

10 任务实施 “H4-G4”表示加班用时，为时间格式 提个醒 “HOUR(H4-G4)”表示提取“H4-G4”中的小时数；
“MINUTE(H4-G4)”表示提取“H4-G4”中的分钟数； “MINUTE(H4-G4)/60”表示将分钟数转换成小时数。

11 任务实施 小知识 HOUR函数的功能：返回时间值的小时数，即一个介于0-23之间的整数。
HOUR函数的表达式：HOUR（Serial_number）。 参数Serial_number表示一个时间值，其中包含要查找的小时，可以是带引号的字符串，如“6:30PM”，也可是十进制数如“0.5”（即为12:00PM，正好是一天的一半），还可以是其他公式或函数的计算结果。 MINUTE函数的功能：返回时间值的分钟数，即一个介于0-59之间的整数。 MINUTE函数表达式：MINUTE（Serial_number）。 参数Serial_number表示一个时间值，其中包含要查找的分钟，用法同HOUR函数Serial_number参数的用法。

12 任务实施 （3）计算“应付加班费”数据列。按照公司规定，加班费以员工小时工资为基数，法定假日加班按照3倍小时工资计算，周六日加班按照2倍小时工资计算，工作日加班按照1.5倍小时工资计算。选中K4单元格，输入公式： =IF(F4="H",J4*I4*3,IF(F4="R",J4*I4*2,IF(F4="W",J4*I4*1.5,""))) 公式的功能是：判断单元格F4为H，则按算式J4*I4*3计算出相应的加班费；判断单元格F4为R，则按算式J4*I4*2计算出相应的加班费；判断单元格F4为W，则按算式J4*I4*1.5计算出相应的加班费。 

13 任务实施 知识拓展 《劳动法》中关于加班费的规定：
第三十一条　用人单位应当严格执行劳动定额标准，不得强迫或者变相强迫劳动者加班。用人单位安排加班的，应当按照国家有关规定向劳动者支付加班费。 第四十四条 有下列情形之一的，用人单位应当按照下列标准支付高于劳动者正常工作时间工资的工资报酬： （一）安排劳动者延长工作时间的，支付不低于工资的百分之一百五十的工资报酬； （二）休息日安排劳动者工作又不能安排补休的，支付不低于工资的百分之二百的工资报酬； （三）法定休假日安排劳动者工作的，支付不低于工资的百分之三百的工资报酬。

14 任务实施 4．冻结窗格，浏览所有数据 单击A4单元格，单击“视图”→“窗口”→“冻结窗格”按钮，选择“冻结拆分窗格”按钮，此时滚动鼠标中间的滑轮，便可不隐藏标题的情况下，方便的查看下面单元格的内容。查看效果如图3-8所示。

15 任务实施 5．套用表格样式，美化表格 （1）套用表格样式。单击“开始”→“样式”→“套用表格样式”按钮，选则“表样式浅色12”样式，效果如图3-9所示。

16 任务实施 （2）取消筛选按钮。选中A3：L3单元格区域，单击“开始”→“编辑”→“排序和筛选” →“筛选”按钮即可。 小知识
Excel 2007 套用表格样式如何取消？ 1．右键选择“表格”→“转换为区域“快捷菜单项； 2．在“设计”→“工具”→“转换为区域“按钮

17 任务实施 6．设置打印效果 单击Office按钮，在弹出的菜单中选则“打印”→“打印预览”菜单项，在弹出的窗口中单击“打印预览”→“打印”→“页面设置”按钮，设置A4纸、横向，反复预览并调整行间距和列间距。 最终效果见图3-1所示。

18 任务小结 本任务通过使用数据有效性、HOUR函数、MINUTE函数、IF函数，完成了员工加班月统计表的设计；通过冻结窗格，套用表格样式，设置打印效果，美化了员工加班月统计表的输出效果。

19 举一反三 1．员工月加班统计表中，还有哪些列可以利用数据有效性完成输入，自己动手试试吧。 2．有的公司按照职务的不同计算加班费。请在任务一的数据基础上，自行设计一个按照不同职务计算加班费的表格。 3．参照任务一的表格，为销售部制作一个加班上报表。

20 任务描述 企业人力部门需要做好考勤统计工作，统计缺勤扣款等事宜，激励职工按照岗位要求履行职责。
企业也要让员工享有带薪休婚假、产假、年假等的权利，保障职工的切身利益。 本任务要完成员工考勤统计表和员工带薪年假天数统计表的创建，如图3-10和3-11所示。

21 任务描述 图3-11 员工带薪年假天数统计表 图3-10 员工月考勤统计表

22 任务实施 1．创建“员工月考勤”基本工作表 （1）输入表格标题、列标题及设置边框样式等。 （2）冻结窗格。选中B4单元格，单击“视图”→“窗口”→“冻结窗格”按钮，选择“冻结拆分窗格”按钮，效果如图所示。

23 任务实施 （1）输入信息并设置格式。 根据公司的考勤记录，在工作表相应的单元格中输入员工的基本信息，同时设置为水平居中、底端对齐显示。将“编号”列的格式设置为自定义“000”，“月工资”、“日工资”、“全勤奖”和“请假扣款”列设置为货币“￥-1，234.10，“请假日期”列设置为日期“01/3/14”，其他列采用默认格式设置。 （2）用数据有效性快速输入“请假种类”列数据。

24 任务实施 ① 选中G4：G23单元格区域，单击“数据”→“数据工具”→“数据有效性”按钮，在弹出的“数据有效性”对话框切换到“设置”选项卡，进行设置如图3-13所示。

25 任务实施 ② 切换到“输入信息”选项卡，进行设置如图3-14所示。

26 任务实施 ③ 切换到“出错警告”选项卡，进行设置如图3-15所示。

27 任务实施 ④单击“确定”完成“数据有效性”设置。 ⑤利用数据有效性快速输入请假种类。单击选中G4单元格，此时在单元格G4的右侧就会出现一个下拉列表按钮，并在其下方出现提示信息，如图3-16所示。 图3-16 利用“数据有效性”输入数据

28 任务实施 图3-17 利用“数据有效性”输入数据 ⑥ 完成效果，如图3-18所示。

29 任务实施 图3-18 输入员工基本信息表

30 任务实施 （1）计算“出勤天数”数据列。 出勤天数=本月实际应出勤天数（本月18天）-请假天数。选中J4单元格，输入公式： =18-I4 通过公式复制，计算所有人的“出勤天数”列数据。 （2）计算“全勤奖”数据列。 公司规定，所有有请假项的员工(除请公假外)都不能算全勤，全勤员工每人每月享有200元的奖励，也就是说，员工请假项要么是空的，要么是“公假”，这时他才能有全勤奖200元，否则没有。选中K4单元格，输入公式： =IF(OR(G7="",G7="公假◎"),200,"") 通过公式复制，计算所有人的“全勤奖”列数据。

31 任务实施 提个醒 IF函数和OR函数结合使用进行多条件判断。
IF函数是针对一个条件进行判断的函数，并不能完全满足日常工作的需求。在工作制表中，将IF函数和OR函数结合使用，可以进行多条件的选择性判断。 本例公式表示：如果G2单元格中的数值为空值，或者G2单元格中的数值为“公假◎”，则在K4列显示200元，如果两个条件中没有一个符合，则不作显示（""表示空白）。

32 任务实施 小知识 OR函数的功能：判断所有参数中只要有一个逻辑值为TRUE(真)就返回TRUE(真)，否则返回FALSE(假)。
OR函数的表达式： OR(logical1，logical2，...) 参数说明：Logical1，logical2，...是待测试的条件表达式或逻辑值，最多可有30个。与OR函数用法类似的还有AND函数，IF函数和AND函数也可结合使用进行多条件判断。 AND函数的功能：判断所有参数中任意一个逻辑值都为真才返回TRUE(真)，只要有一个FALSE(假)就返回FALSE(假)。 AND函数的表达式：AND(logical1，logical2，...) 参数说明：Logical1，logical2，...是待测试的条件表达式或逻辑值，最多可有30个。

33 任务实施 （3）计算“请假扣款”数据列。 按照公司规定，缺勤1天病假扣0.5天工资，事假扣1天工资，婚假、产假、丧假、公假、年休假、其他都不扣工资，旷工扣3天工资。选中K4单元格，输入公式： =IF(G4="病假◇",F4*0.5*I4,IF(G4="事假〇,F4*I4,IF(G4="旷工△",F4*3*I4,""))) 公式的功能是：判断单元格G4为“病假◇”，则按算式F4*0.5*I4计算出相应的请假扣款；判断单元格G4为“事假〇”，则按算式F4*I4计算出相应的请假扣款；判断单元格G4为“旷工△”，则按算式F4*3*I4计算出相应的请假扣款；单元格G4如果都不符合上述条件，则请假扣款显示为空白。 通过公式复制，计算所有人的“请假扣款”列数据。

34 任务实施 4．统计带薪年假天数 （1）重命名“Sheet2”工作表为“员工带薪年假天数表”。 （2）创建带薪年假天数基本工作表并输入信息。 为浏览数据方便，还可按照前面的操作方法，冻结窗格，效果如图3-19所示。

35 任务实施 （3）计算工龄。假设是2011年年底统计工龄，单击F4单元格，输入公式： =DATEDIF(E3,"2011/12/31","y“) 公式的功能是：返回起止日期E3单元格到终止日期“2011年12月31日”相差的年份。（“y”代表起止日期与终止日期相差的时间单位为年。） 通过公式复制，计算所有人的工龄。

36 任务实施 （4）计算年假天数。 按照国务院公布的《职工带薪年休假条例》规定，该公司带薪年休假天数计算标准为：1.职工累计工作已满1年不满10年的，年休假5天；2.已满10年不满20年的，年休假10天3.已满20年的，年休假15天。单击G3单元格，输入公式： =SUM(5*(F3>={1,10,20})) 公式的功能是：分别判断F3单元格里的数据（存放着员工“孙楠”的工龄）是否大于等于大括号里的1、10、20，若成立则返回1，否则返回0，计算结果存放在一个新的内存数组中(1，1，0)，接下来内存数组中的每一个数再分别乘以5，最后将3个乘积相加求和。之所以这样做，是因为按照带薪年假天数的计算标准，仅满1年

37 任务实施 休假5天，满10年再多休假5天，满20年再多休假5天，也就是说休假天数成倍增加，对于这样的问题可以用上面介绍的内存数组的方法解决。 本例也可以用if函数的嵌套实现，想想公式如何？ 通过公式复制，计算所有人的年假天数。 最终效果见图3-11所示。

38 任务实施 小知识 数组的知识 数组：是一组起作用的单元格或值的集合，包括文本、数值、日期、逻辑和错误值等形式。在Excel中数组有两种形式，即常量数组和单元格区域数组。 常量数组可以包含数字、文本、逻辑值和错误值等，它用{}将构成数组的常量括起来，各个元素之间分别用分号和逗号来间隔行和列，如：{1，2} {10；20；30} 单元格区域数组：通过对一组连续的单元格区域进行引用而得到的数组，如：{A9:M15}) 数组公式：是使用了数组的一种特殊的公式，对一组或多组值进行多重计算，并返回一个或多个结果。 内存数组：数组通过数组公式运算后所生产的新数组称为内存数组。

39 任务小结 本任务通过使用数据有效性、OR函数、IF函数，完成了员工月考勤统计表的设计；通过使用DATEDIF函数、数组公式，完成了带薪年假天数统计表的设计。

40 举一反三 1．请为任务二中XXX公司生产部设计一张考勤记录表，注意要方便记考勤。 2．已知某大型企业规定员工休带薪年假的标准是： （1）工作不满一年不能休带薪年假； （2）工作满一年带薪年假休5天； （3）工作满三年带薪年假休7天； （4）工作满十年带薪年假休10天，以后每多工作5年，休假天数多5天。 请写出该企业该带薪年假天数的计算公式。

41 任务描述 奖金的规范化管理，成为企业现代化管理中的重要 内容。 进行员工奖金统计会涉及到Excel2007的一些重 要功能和处理技巧。
本任务要创建的员工奖金统计表，如图3-20所示。

42 任务实施 表3-1 业绩奖金规范 1．了解奖金规范 企业奖金规范。XXX公司规定：销售部员工按销售 额的不同分配不同比例的奖金，具体奖金规范如表3-1 所示。公司还规定，对于销售额在50000元以上的（含 50000元）员工，公司在给予提成销售额5%的基础上 额外一次性再发放5000元的奖励。 表3-1 业绩奖金规则

43 任务实施 2．创建奖金规范表 （1）启动Excel2007建立一个新的工作簿，将其保存为“员工奖金统计表.xlsx”,并将“Sheet1”工作表重命名为“奖金规范表”。 （2）输入数据,并设置工作表数据格式。效果如图所示3-21所示。

44 任务实施 提个醒 在创建“奖金规范表”时要注意，“奖金参考数”数据列必不可少，而且其中的数字一定是对应行月销售额数据范围中最小的数字。这是因为后续建立“奖金统计表”时，需要利用VLOOKUP函数进行数据引用。 VLOOKUP函数的功能是用于搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。“奖金参考数”列的数据是VLOOKUP函数搜索的单元格区域的第一列。

45 任务实施 3. 创建“奖金统计表”基本工作表 效果如图所示3-22所示。

46 任务实施 4．统计奖金额 单击E4单元格，输入公式： =VLOOKUP(D4,奖金规范表!$A$3:$C$8,3) 公式的功能是：从“奖金规范表”A3:C8单元格区域的第一列 搜索小于等于D4单元格数值的最大值，然后返回该区域相同行上第 3列的数值。本例中，D4单元格的值是“ ”，使用 VLOOKUP函数查找到的“奖金规范表”A3:C8单元格区域第一列 中小于等于该值的最大值是“5000”，返回它对应行的第三列的值 是1.5%。 通过公式复制，计算所有人的“奖金比例”列数据。

47 任务实施 提个醒 导入“奖金比例”公式的输入过程：
1.单击E4单元格，输入“=vlookup（）”，将鼠标指针移入“（）”中，并单击D4单元格。 2. 用鼠标单击“奖金规范表”工作表标签，用鼠标圈选A3:C8单元格区域。 3.拖动鼠标在地址栏里选取A3:C8，按F4键，使A3:C8单元格区域由相对引用变成绝对引用。

48 任务实施 提个醒 4.在地址栏里输入“，”，输入“3”，按“Enter”键确认公式的输入。
注意：公式中，A3:C8单元格区域必须使用绝对引用，这是因为公式在复制过程查找区域始终保持不变。

49 任务实施 ： 提个醒 导入“奖金比例”公式的输入过程：

50 任务实施 提个醒 导入“奖金比例”公式的输入过程：

51 任务实施 小知识 VLOOKUP函数的功能：用于在给定区域的第一列里查找目标数值，然后返回该值所在行上指定列的值。
VLOOKUP函数的表达式：VLOOKUP(lookup_value， table_array， col_index_num， [range_lookup]) 参数说明：lookup_value为目标数值，表示要在给定区域的第一列中查找的值。lookup_value 参数可以是值或引用。如果为 lookup_value 参数提供的值小于 table_array 参数第一列中的最小值，则 VLOOKUP 将返回错误值 #N/A。 table_array是给定区域，表示要在其中查找数据的单元格区域。可以使用对区域的引用（例如，A2:C11）或区域名称的引用。table_array 第一列中的值是由

52 任务实施 小知识 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写
col_index_num 为指定列，表示给定区域中待返回值的列号。当col_index_num 参数为 1 时，返回给定区域第一列中的值；当col_index_num 为 2 时，返回 给定区域第二列中的值，依此类推。 range_lookup为查找类型，是一个逻辑值，表明是进行精确匹配查找还是近似匹配查找。 如果 range_lookup 为 TRUE 或被省略，VLOOKUP函数则进行近似匹配查找。即如果找不到精确匹配值，则返回小于 lookup_value 的最大值。此时，给定区域第一列中的值必须按升序排列；否则，VLOOKUP 函数可能无法返回正确的值。

53 任务实施 小知识 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。
例如，假设区域 A2:C11 中 包含员工列表，编号存储在该区 域的第一列，如图所示。如果知 道员工的编号，就可以使用 VLOOKUP 函数返回该员工的姓名 或其所在的部门。 如，要获取 38 号员工的姓名，可以使用公式 ： =VLOOKUP(38， A2:C11， 2，FALSE) 此公式将精确匹配查找区域 A2:C11 的第1列中的值 38，然后返回“38”所在行上第2列的值（“莫言”）。

54 任务实施 （2）计算“奖金额（元）”数据列 选中F4单元格，输入公式： =IF(D4>=50000,ROUND(D4*E4,0)+50 00,ROUND(D4*E4,0)) 公式的功能是：如果D4单元格的值（即“赵悦”的月销售额）大于等于50000元，那么计算“奖金额（元）”的公式为ROUND(D4*E4，0)+5000；如果D4单元格的值小于50000元，那么计算“奖金额（元）”的公式为ROUND(D4*E4，0)。 通过公式复制，计算所有人的“奖金额（元）”列数据。最终效果见图3-10所示。

55 任务实施 小知识 ROUND函数的用法 ROUND函数的功能：用于将数字四舍五入到指定位数。
ROUND函数的表达式：ROUND ( number， num_digits ) 参数说明：number 要进行四舍五入运算的数字。 num_digits 指定要进行四舍五入运算的位数。

56 任务实施 知识拓展 HLOOKUP函数的用法： HLOOKUP函数是与VLOOKUP函数在功能上非常类似的一个函数。
HLOOKUP函数的表达式：HLOOKUP(lookup_value， table_array， row_index_num， [range_lookup]) 参数说明：HLOOKUP函数中参数与VLOOKUP函数中的参数比较： lookup_value 都是目标数值，HLOOKUP函数中的表示要在给定区域的第一行中查找的值；而VLOOKUP函数中的表示要在给定区域的第一列中查找的值 。

57 任务实施 知识拓展 table_array 都是给定区域，表示要在其中查找数据的单元格区域。HLOOKUP函数中的表示其第一行中的值是由 lookup_value 搜索的值；而VLOOKUP函数中的表示其第一列的值是由 lookup_value 搜索的值。 row_index_num 是HLOOKUP函数中的第三个参数，表示指定行，用于指定给定区域中待返回值的行号；而在VLOOKUP函数中的第三个参数是col_index_num ，表示指定列，用于指定在给定区域中待返回值的列号 。

58 任务实施 知识拓展 range_lookup 为查找类型，如果 range_lookup 为 TRUE 或被省略，两者都进行近似匹配查找。如果 range_lookup 参数为 FALSE或0，两者都进行精确匹配查找。只是近似匹配查找，给定区域第一行（HlOOKUP函数）或第一列（VLOOKUP函数）中的值必须按升序排列，精确匹配则不必。 其他用法都相同。

59 任务小结 本任务通过使用VLOOKUP函数、ROUND函数和IF函数， 完成了员工奖金统计表的设计，在奖金统计表设计中主要学习了使 用VLOOKUP函数引用不同工作表数据的方法，这在薪酬福利管理 中是应用非常广泛的。

60 举一反三 1．在任务三中，有人创建了如下的奖金规范表， 请在奖金统计表中使用HLOOKUP函数导入奖金比例， 计算奖金额（元）。
图JYFS-3-1 奖金规范表 提示：图JYFS-3-1“奖金规范表”的创建，可通过 复制任务三中的“奖金规范表”A2:C8单元格区域，然后 通过执行“选择性粘贴”命令，在“选择性粘贴”对话框中 勾选“转置”复选框即可。

61 举一反三 2．计算个人所得税。企业负有每月为职工代扣代 缴工资、薪金所得部分个人所得税的义务。国家规定： 工资、薪金所得，以每月收入额减除费用3500元后的 余额为应纳税所得额，即： 月应纳税所得额=月工资收 入额-3500（基数）。月应纳税额=月应纳税所得额X 适用税率-速算扣除数。图JYFS-3-2是2011年调整后 的个人所得税税率表。 图JYFS-3-2 个人所得税税率表

62 举一反三 请根据该税率表，计算图JYFS-3-3工作表中的 “代缴个税”列数据。 图JYFS-3-3 个人所得税计算表

63 任务描述 人力资源部需要及时、准确处理大量的工资数据。 相关工资表格的制定，成为规范工资管理的重要手段。
Excel在制定各类工资表格方面有着不可替代的作用。 本任务要创建的员工资统计表，如图3-23所示。

64 任务实施 1．新建或添加已有工作表 其中， “加班表”、“考勤表”、“销售部奖金表” 、“奖金规范表”分别为前三个任务中完成的“员工加班月统计表” 、“员工月考勤表”、“员工奖金统计表”和“奖金规范表”，其他工作表均为新建。

65 任务实施 2．为“工资表”添加基本数据 单位性质不同，工资表中包含的工资项也不同。本任务中的工资表基本数据如图所示。

66 任务实施 3．输入“基本工资表”数据信息 按照公司员工工资实际情况，在“基本工资表”工作表中输入员工的“编号”、“姓名”、“部门”、“人员类别”和“基本工资”等项数据，效果如图3-26所示。

67 任务实施 4．输入“奖金表”数据信息 不同性质的单位，奖金的分配方案各不相同。假设公司规定， 销售部员工根据销售额多少分配奖金，即任务三实现的销售部奖金统 计结果（保存在“销售部奖金表”里），生产部的奖金为每人每月为 1000元，其他部门员工的奖金为800元。因此，计算“奖金表”里 “奖金”列数据的方法是：单击E4单元格，输入公式： =IF(C4="销售部",VLOOKUP(A4,销售部奖金表 !$A$4:$F$10,6,0),IF(C4="生产部",1000,800))

68 任务实施 公式的功能是：如果部门是“销售部”的员工，“奖金”列返回VLOOKUP函数查找到的 “001”编号的员工本月奖金；如果部门是“生产部”的员工，“奖金”列数据为1000元；如果是其他部门的员工，“奖金”列数据为800元。 通过公式复制，计算所有人的“奖金”列数据。结果如图3-27所示。

69 任务实施 5．输入“补贴表”数据信息 假设公司规定，补贴按照人员类别进行分配。“高级管理人员”补贴为“基本工资”的70%，“中级管理人员”为55%，“一般管理人员”为45%，“高级技工”68%，“中级技工”为58%，“普通工人”45%，因此，计算“补贴表”里“补贴”列数据的方法是：单击E3单元格，输入公式： =IF(D3="高级管理人员",基本工资表!E3*70%,IF(D3="中级管理人员",基本工资表!E3*55%,IF(D3="一般管理人员",基本工资表!E3*45%,IF(D3="高级技工",基本工资表!E3*68%,IF(D3="中级技工",基本工资表!E3*58%,IF(D3="普通工人",基本工资表!E3*45%))))))

70 任务实施 公式的功能是：实现了公司按照人员类别不同分配补贴的方案。 通过公式复制，计算所有人的“补贴”列数据。结果如图3-28所示。

71 任务实施 6．输入“税率表”数据信息 按照国家2011年调整后的个人所得税税率表，输入如图3-29所示的个人所得税税率信息，以用于计算个人所得税。

72 任务实施 7．定义各工作表数据区域的单元格名称 定义单元格区域名称，以方便VLOOKUP函数引用各工作表的数据数据区域。 分别将“基本工资”、“奖金表”、“补贴表”、“加班表”、“考勤表”和“补贴表”数据区域的名称定义为“基本工资”、“奖金”、“补贴”、“加班表”、“考勤”和“补贴”。 8．完成“工资表”各数据项信息 （1）导入“基本工资”列数据。单击到D4单元格，在其中输入公式： =VLOOKUP(A4,基本工资,5) 公式的功能是：查询并引用“基本工资表”工作表中编号“001”员工的基本工资。

73 任务实施 （3）导入“补贴”列数据。单击到F4单元格，在其中输入公式： =VLOOKUP(A4,补贴,5)
（2）导入“奖金”列数据。奖金=奖金表中的奖金+考勤表中的全勤奖。单击到E4单元格，在其中输入公式： =VLOOKUP(A4,奖金,5)+VLOOKUP(A4,考勤,10) 公式的功能是：计算查询到的奖金表中的奖金和查询到的考勤表中的全勤奖之和，作为工资表中的奖金项。 （3）导入“补贴”列数据。单击到F4单元格，在其中输入公式： =VLOOKUP(A4,补贴,5) 公式的功能是：查询并引用“补贴”工作表中编号“001”员工的补贴。

74 任务实施 （4）导入“加班费”列数据。单击到G4单元格，在其中输入公式： =VLOOKUP(A4,加班表,11) 公式的功能是：查询并引用“加班费”工作表中编号“001”员工的加班费。 （5）导入“缺勤扣款”列数据。单击到H4单元格，在其中输入公式： =VLOOKUP(A4,考勤,11) 公式的功能是：查询并引用“考勤表”工作表中编号“001”员工的缺勤扣费。

75 任务实施 （6）计算“应发工资”列数据。单击到I4单元格，在其中输入公式： =SUM(D4:F4)-H4 公式的功能是：计算应发工资，即基本工资+奖金+补贴+加班费-缺勤扣款。 （7）计算“代缴保险”列数据。单击到J4单元格，在其中输入公式： =D4*5% 公式的功能是：计算代缴保险，即基本工资的5%。

76 任务实施 （8）计算“代缴个税”列数据。 国家规定：工资、薪金所得，以每月收入额减除费用3500元后的余额为应纳税所得额，即： 月应纳税所得额=月工资收入额-3500（基数）。月应纳税额=月应纳税所得额X适用税率-速算扣除数。单击到K4单元格，在其中输入公式： =IF(I4-J4-3500<0,0,(I4-J4-3500)*VLOOKUP(I4-J4-3500,税率表!$C$3:$F$9,3)-VLOOKUP(I4-J4-3500,税率表!$C$3:$F$9,4))

77 任务实施 （9）计算“实发工资”列数据。 单击到L4单元格，在其中输入公式： =ROUND(I4-J4-K4,2)
公式的含义是：如果月应纳税所得额小于0（即月工资收入不足3500元）则不纳税；如果大于0（即月工资收入超过3500元）则按公式 “(I4-J4-3500)*VLOOKUP(I4-J4-3500，税率表!$C$3:$F$9，3)-VLOOKUP(I4-J4-3500，税率表!$C$3:$F$9，4)”计算纳税。 （9）计算“实发工资”列数据。 单击到L4单元格，在其中输入公式： =ROUND(I4-J4-K4,2) 公式的功能是:计算实发工资并将结果四舍五入保留两位小数，实发工资=应发工资-代缴保险-代缴个税。 框选D4:L4单元格，通过Excel2007的自动填充功能实现其他人以上数据列的添加。

78 任务实施 （10）输入“银行账号”数据列。 ①设置单元格M4:M23区域为文本格式。

79 任务实施 提个醒 图3-32中，输入无效数据时可设置三种样式：停止、警告和信息。用户要根据具体情况选用不同的样式。其中，“停止”样式绝对不允许无效的数据出现在单元格中；“警告”样式，只是警告某种操作可能出现错误，但允许无效数据出现；“信息”样式以特殊形式保留无效数据。 ③ 依次输入所有人的银行账号。

80 任务实施 提个醒 我们发现，所有银行账号所在单元格左上角都有一个绿三角，这是因为系统认为“此单元格中的公式与电子表格中该区域的公式不同。” 框选所有银行账号数据，单击第一个单元格左侧的黄色小信息图标，选择“忽略错误”选项即可清除绿三角，如图3-33所示。 至此，我们完成了“工资表”工作表的创建，最终效果如图3-23所示。

81 任务实施 9．制作工资条 打印工资条是工资管理中必不可少的任务，下面我们就学习如何方便快速地制作员工工资条。
（1）插入新工作表，将其重命名为“工资条”。 （2）复制“工资表”工作表中的表格列项目到“工资条”工作表中，并设置表格边框，如图3-34所示。 图3-34 创建“工资条”数据项

82 任务实施 （3）依次在A2、B2、C2、D2、E2、F2、G2、H2、I2、J2、K2、L2、M2中输入公式或数据。（注意设置A2单元格的数据类型为时间“2001年3月”格式。 =NOW() 001 =VLOOKUP(B2,工资表,2,0) =VLOOKUP(B2,工资表,3,0) =VLOOKUP(B2,工资表,4,0) …… =VLOOKUP(B2,工资表,11,0) =VLOOKUP(B2,工资表,12,0)

83 任务实施 框选A1:M3单元格区域，使用Excel2007的自动填充功能实现工资条制作，效果如图3-35所示。

84 任务小结 本任务通过学习员工工资统计表的设计，使读者熟悉了工资管理的流程，熟悉了工资表及工资条创建过程具体的技能、技巧。

85 举一反三 1．了解自己周边某个企业的工资规定，找一份别人的工资条，试着自己制作一份工资表。
2．试着用数据透视表和数据透视图分析任务四中各部门职工的奖金和实发工资状况，参照效果如图JYFS-3-4、图JYFS-3-5所示。 图JYFS-3-4 数据透视表 图JYFS-3-5 数据透视图

86 图JYFS-3-6 使用“记录单”添加、查询数据
举一反三 3．试着使用“记录单”按钮完成“基本工资表”数据的添加、删除及查询操作，如图JYFS-3-6所示。 （1）在“基本工资表”中添加一行数据：021、魏玉谋、销售部、中级技工、2000； （2）查询所有销售部员工姓名； （3）删除020号“员工信息”。 图JYFS-3-6 使用“记录单”添加、查询数据

87 谢谢 ！