某科技公司员工工资管理报表制作

某科技公司员工工资管理报表如图4-3-1所示。

图4-3-1　员工工资管理报表

任务情景

某科技公司员工都盼着发工资，人事部小王应用Excel进行较复杂的工资计算，计算工资到底用到哪些知识？

任务分析

◆根据员工职务级别，确定奖金数目。其中，经理是200元/天，副经理是150元/天，职员是100元/天。

◆全勤奖的设置。出勤天数达到25天，则全勤奖为200元，否则无全勤奖。

◆差旅补助。王宇副经理出差一次，补助200元；普通职员郑海涛出差一次，补助100元。

◆计算员工的应发工资。应发工资=基本工资＋奖金/天*出勤天数＋全勤奖＋差旅补助

◆计算员工的实发工资、并对实发工资进行排序。实发工资=应发工资-扣除五险一金

◆统计平均工资、超过平均工资的人数、最高工资、最低工资。

知识准备

在Excel中，除了输入常量数据外，公式与函数（函数是预先编写的公式）是其重要的组成部分。正是因为公式和函数，赋予了Excel强大的计算能力和统计分析能力，为用户处理数据提供了极大的便利。

1.公式

1）公式的输入和使用

公式是对工作表中数值执行计算的等式。公式要以等号（=）或加号（＋）开始。公式也可以包括下列所有内容或其中某项内容：常量、运算符、单元格引用和函数。

运算符是一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。Excel的常用运算说明见表4-3-1。

表4-3-1　Excel常用运算符

（2）公式的复制和填充

输入单元格中的公式，可以采用拖动公式单元格右下角的填充柄的方式进行复制。或者选中公式单元格后，单击“开始”选项卡“编辑”组中“填充”按钮进行公式的复制填充。

进行公式的复制填充时，填充的实际上是公式而非数据本身，并且会随着公式单元格的地址改变而修改公式中的单元格引用。

2.单元格引用的方式

单元格的引用分3种：相对引用、绝对引用和混合引用。

1）相对引用——相对引用或称相对地址，如A1、B3、C2：F4等。

2）绝对引用——绝对引用或称绝对地址，是指某一单元格在工作表中的绝对位置。绝对引用要在行号和列号前加一个＄符号，如：＄A＄1、＄B＄3等。当公式在复制、移动时，绝对引用单元格将不随公式位置变化而改变。

3）混合引用——混合引用或称混合地址，是指单元格地址的行号或列号前加上“＄”符号，如＄A1、B＄3。当公式在复制、移动时，混合引用是上述两者的结合。

3.函数的使用方法

Excel函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。

1）函数的语法形式

函数名称（参数1，参数2，……）

其中参数可以是常量、单元格、区域、公式或其他函数

2）函数输入方式

*直接输入法。以“=”或“＋”开始直接在单元格内输入函数及所使用的参数。

*通过“函数库”组插入。在“公式”选项卡“函数库”组中的单击某一函数类别中，从弹出的函数列表中单击选择的函数，然后在打开“函数参数”对话框中输入或选择参数。

*粘贴函数法：在“公式”选项卡“函数库”组中的单击“fx”按钮或单击编辑栏上的“fx”按钮，打开“插入函数”对话框，在“选择类别”下拉列表中选择函数类别，在“选择函数”列表中选择函数，然后在打开的“函数参数”对话框中输入参数。

4.Excel中的常用函数

下面将介绍Excel中的常用函数，对于其余的函数，可通过查阅资料或借助Excel的帮助来掌握使用方法。

1）求和函数SUM

功能：返回参数中所有数值之和。

格式：SUM（Number1，Number2…）

参数：“Number1，Number2…”为1到30个需要求和的参数。

说明：直接键入参数表中的数值、逻辑值及数值的文本表达式将被计算。上述情况对于后续的函数AVERAGE、COUNT、MAX、MIN等同样适用。

使用示例：

=SUM（3，2）等于5。

=SUM（“3”，2，TRUE）等于6，因为文本值被转换成数值，而逻辑值“TRUE”被转换成数值1。

2）条件求和函数SUMIF

功能：根据指定条件对若干单元格求和。

格式：SUMIF（Range，Criteria，Sum＿Range）

参数：Range为用于条件判断的单元格区域；Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。条件可以表示为32、＂32＂＂＞32＂＂apples＂等；Sum＿Range为需要求和的实际单元格。只有当Range中的相应单元格满足条件时，才对Sum＿Range中的单元格求和。如果省略Sum＿Range，则直接对Range中的单元格求和。

示例：假设A1：A4的内容分别为10、20、30、40；B1：B4的内容为1、2、3、4，则：=SUMIF（A1：A4，＂＞10＂，B1：B4）等于9；=SUMIF（A1：A4，＂＞10＂）等于90

3）求平均值函数AVERAGE

功能：返回参数平均值（算术平均）

格式：AVERAGE（Number1，Number2，…）

参数：Number1，Number2，…是要计算平均值的1～30个参数。参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。

示例：如果A1：A5中的数值分别为1、2、3、4、5，则：=AVERAGE（A1：A5）等于3。

=AVERAGE（A1：A5）运算的结果与=SUM（A1：A5）/COUNT（A1：A5）运算的结果相等

4）条件平均值函数AVERAGEIF

功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值(www.xing528.com)

格式：AVERAGEIF（range，criteria，［average＿range］）

注意：当average＿range参数缺省时，最对range区域中满足criteria的单元格求平均值。用法同上面SUMIF（）函数类似。

5）计数函数COUNT

功能：统计指定区域中包含数值的个数。只对包含数值的单元格进行计数。

格式：COUNT（Value1，Value2，…）

参数：Value1，Value2，…是包含或引用各种类型数据的参数（1～30个），只有数值类型的数据才被计数。

示例：如果A1～A6单元格的内容分别为：“销售”、12/03/2004、19、22.34、TRUE、＃DIV/0！，则：=COUNT（A1：A6）等于2，=COUNT（A4：A6）等于1，=COUNT（A1：A6，2）等于3。

6）条件计数函数COUNTIF

功能：计算给定区域内满足特定条件的单元格的数目

格式：COUNTIF（Range，Criteria）

其中：Range为需要计算其中满足条件的单元格数目的单元格区域；Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

示例：假设B3：B6中的内容分别为10、20、30、40，则：=COUNTIF（B3：B6，＂＞20＂）等于2。

7）条件判断函数IF

功能：执行真假值判断，根据逻辑测试的真假值返回不同的结果。

格式：IF（Logical＿test，Value＿if＿true，Value＿if＿false）

参数：Logical＿test表示计算结果为TRUE或FALSE的任意值或表达式。Value＿if＿true是Logical＿test为TRUE时返回的值。Value＿if＿false是Logical＿test为FALSE时返回的值。

说明：函数IF可以嵌套七层，用Value＿if＿false及Value＿if＿true参数可以构造复杂的检测条件。

示例：如果A10中的数值小于等于100，则下面的函数将显示“预算内”，否则将显示“超出预算”。=IF（A10＜=100，“预算内”，“超出预算”）

可以使用下列嵌套IF函数：=IF（D8＞89，“A”，IF（D8＞79，“B”，IF（D8＞69，“C”，IF（D8＞59，“D”，“F”））））

8）排位函数RANK

功能：返回一个数值在一组数值中的排位，使用语法：

格式：RANK（Number，Ref，Order）

参数：Number为需要找到排位的数字；Ref为包含一组数字的数组或引用，引用内的非数值型参数将被忽略；如果Order为0或省略，Excel将Ref当做按降序排列的数据清单进行排位。如果Order为1，Excel将Ref当做按升序排列的数据清单进行排位。

说明：函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。

任务实施

1.打开配套素材“Excel2010”→“某科技公司员工工资管理报表.xlsx”。

2.根据员工的职务确定奖金金额/天，经理为200元/天，副经理为150元/天，职员为100元/天。要应用条件判断函数IF（Logical＿test，Value＿if＿true，Value＿if＿false）来计算。单击F4，输入函数为“=IF（C4=＂经理＂，200，IF（C4=＂副经理＂，150，100））”回车，见图4-3-2所示。拖动F4单元格的填充柄到F14单元格，求出每个员工的奖金/天。

图4-3-2　函数使用-计算奖金/天

3.计算“全勤奖”，应用条件判断函数IF（Logical＿test，Value＿if＿true，Value＿if＿false）来计算。出勤天数达到25天，则全勤奖为200元，否则无全勤奖。单击G4，输入函数内容为“=IF（E4=25，200，0）”。拖动G4单元格的填充柄到G14单元格，求出每个员工全勤奖。

4.计算“应发工资”，应发工资=基本工资＋出勤天数*奖金/天＋全勤奖＋差旅补助。单击I4单元格，输入公式为“=D4＋E4*F4＋G4＋H4”，见下图4-3-3所示。

图4-3-3　公式使用例表-应发工资

5.计算“实发工资”，实发工资=应发工资—扣除五险一金。单击K4单元格，输入公式“=I4-J4”。

6.按工资的降序排序。单击L4，输入函数内容为“=RANK（K4，K＄4：K＄14，0）”，见图4-3-4所示。函数RANK（Number，Ref，Order），表示是Number在Ref范围内的排序，其中Order为0或省略，将Ref当做按降序排列的数据清单进行排序；Order不为零，将Ref当做按升序排列的数据清单进行排序。

图4-3-4　公式使用例表-按工资排序

7.计算“平均实发工资”，利用函数AVERAGE（Number1，Number2，…）来计算。单击D16单元格，输入函数内容为“=AVERAGE（K4：K14）”回车即可。选中“开始”选项卡，单击“数字”组的“增加小数点位数”命令，设置2位小数。

8.计算“超过平均实发工资的人数”。单击D17单元格，输入内容为“=COUNTIF（K4：K14，＂＞6563.79＂）”回车，如图4-3-5所示。应用了函数COUNTIF（Range，Criteria）来计算，其中Range为需要计算其中满足条件的单元格数目的单元格区域；Criteria为指定条件，本例中的条件为＞6563.79，其中6563.79时上一步算出来的平均工资。

图4-3-5　公式使用例表-计算超过平均实发工资的人数

9.计算“最高实发工资”和“最低实发工资”，应用函数Max（Number1，Number2，…）和Min（Number1，Number2，…）来计算。单击D18单元格，输入内容为“=MAX（K4：K14）”，其中K4：K18是实发工资数据区域。单击D19单元格，输入内容为“=MIN（K4：K14）”，其中K4：K18是实发工资数据区域。

知识拓展

其他函数

1.（竖直）查找函数VLOOKUP函数

功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

格式：VLOOKUP（lookup＿value，table＿array，col＿index＿num，［range＿lookup］）

参数：lookup＿value为查找值。Lookup＿value可以为数值、引用或文本字符串。Table＿array为查找范围，可以使用对区域或区域名称的引用。col＿index＿num为查找返回的匹配值的列序号。range＿lookup为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果是False，则为精确查找，这也是最常用的形式。如果为TRUE或省略，则返回近似匹配值，一般返回小于lookup＿value的最大数值。

说明：Lookup＿value的值必须与table＿array第一行的内容相对应。

示例：现有如下手机的每日销售数据如图4-3-6所示，使用VLOOKUP函数获得分销商A需要提供五个型号的12月6日的销售数据。

图4-3-6　VLOOKUP函数使用示例

在需要填入数据的单元格B17填入：

=VLOOKUP（A17，＄A＄3：＄H＄13，7，FALSE）

A17为要查找的型号，即“三星Note4”。不直接写“三星Note4”，是为了方便公式进行拖曳填充，以及保证准确性。

＄A＄3：＄H＄13为查找范围，使用绝对引用的原因是剩余的查找在这个区域，使用绝对引用后，就可对公式进行直接的拖曳填充。

col＿index＿num参数填写的是：7。因为从数据源第一列起，要查询的12月6日的销量数据在第7列。注意这里的列数是从数据源即引用范围的第一列作为1，而不是以A列作为第一列。

False表示精确查找。日常生活中，大部分都使用的是精确查找。在此例中如果使用true，将出现意外的结果。