3.2       公式及函数的使用

3.2.1          案例分析

【案例 3-2】

对“期末成绩表”实例示范通过自动计算来查看数据计算的结果，运用公式和函数对平均分、总分、总分排名列，以及增加的课程平均分、课程最高分和课程最低分进行计算。介绍常用函数SUM、AVERAGE、MAX、MIN、RANK 的使用方法。

 设计要求：

（1）查看自动计算的结果。

（2）自动求和按钮。

（3）设置单元格的数字格式。

（4）SUM 函数的使用。

（5）AVERAGE 函数的使用。

（6）MAX/MIN 函数的使用。

（7）RANK 函数的使用。

编辑完成的“期末成绩表”效果如图 3‑25所示。

          图3‑25  “期末成绩表”计算结果

【案例 3-3】

图3‑26所示的“员工工资表”是按车间、部门编制的某月工资表，本表包括职工姓名、基本工资、加班工作、奖金、应发工资、个人所得税扣款及实发工资等。需要运用公式和常用函数 SUM、AVERGAE、MAX、MIN、RANK 对应发工资、个人所得税扣款及实发工资等数据列进行计算。

设计要求：

（1）查看自动计算的结果。

（2）DATEDIF 函数的使用。

（3）公式的使用。

（4）自动求和按钮的使用。

（5）SUM 函数的使用。

（6）AVERAGE 函数的使用。

（7）MAX/MIN 函数的使用。

         图3‑26员工工资表

3.2.2     知识储备

公式是对工作表中的数据进行计算和操作的等式，它以“=”号开头，由常量、运算符、函数及单元格引用组成。

例如，公式=SUM（B1：B5）/10，是用来计算 B1～B5 单元格区域之和再除以 10 的商，其中，SUM 是函数引用，B1、B5 是单元格引用，/是除法运算符，10 是常量。

（1）常量

常量指直接输入公式中的值，如数值“54”、日期“2016-01-01”、字符“ABC”等。

（2）运算符

计算符指公式中将数据连起来的符号。

①算术运算符：+（加）、-（减）、*（乘）、/（除）、百分号（%）和乘方（^）

其优先级的顺序是：百分号和乘方乘、除加、减。

   例如，公式=5*2+3^3 的结果为 37。

②关系运算符：=、<、>、>=、<= 、<>（不等于）

关系运算符用于比较两个值，产生逻辑值 TRUE 或 FALSE。例如，公式“A5>=B5”，当 A5 单元格的值大于或等于 B5，结果为 TRUE，否则为 FALSE。

③文本运算符：&（文本连接符）

   例如，公式=“AB”&“BCD”的结果为“ABBCD”。

（3）函数

函数是预定义好的内置公式，通过使用参数进行计算结果，函数的一般结构为：

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

函数名说明函数的功能，参数是函数运算的对象。参数可以是常量、单元格、单元格区域、公式或其他函数。

例如，公式“=IF（A3>20,D4,D5）”表示表达式的结果是根据对 A3 单元格数据大小的判断而来，如果 A3>20 成立，则表达式的结果为 D4，否则为 D5；公式“=SUM（5,1+2,D4:E5,F3）”表示对 5、公式 1+2 的计算结果、D4 到 E5 单元格区域和 F3 单元格求和。

Excel 提供了财务、日期与时间、数学和三角函数、统计、查询和引用、数据库文本、逻辑、信息等函数。最常用的函数如下。

①求和函数 SUM（number1,number2,…）。

②求平均值 AVERAGE（number1,number2,…）。

③计数函数 COUNT（value1,value2,…）。

④最大值函数 MAX（number1,number2,…）。

⑤最小值函数 MIN（number1,number2,…）。

（4）单元格引用

单元格引用是在公式中通过单元格的名称来引用此单元格的数据。

当公式中所引用单元格的数据发生变化时，公式会自动更新计算结果。

①相对引用

相对引用是默认的引用方式，直接由单元格的列号和行号组成。当公式被复制到其他单元格时，引用单元格的地址会根据位置的变化自动调节。

例如，在 G1 单元格输入公式“=D1+E1”，将其复制到 G4 单元格时，变为“=D4+E4。”

②绝对引用

绝对引用是在单元格的列号和行号加上符号$。当公式被复制到其他单元格时，引用单元格的地址固定不变。

例如，在 G1 单元格输入公式“=$D$1+$E$1”，将其复制到 G4 单元格时，公式仍为“=$D$1+$E$1。”

③混合引用

混合引用是在单元格的列号或行号加上符号$。当公式被复制到其他单元格时，若行号为绝对引用，行地址不变；若列号为绝对引用，列地址不变。

例如，在 G1 单元格输入公式“=D$1+$E1”，将其复制到 G4 单元格时，为“=D$1+$E4。”

1.1.1          案例实现

【案例 3-2实现】

1．增加行并格式化

操作要求：在原工作表数据行后增加三行。

操作步骤：选中 A19～A21 单元格区域，分别输入“课程平均分”“课程最高分”和“课程最低分”，将A19 与 B19 两个单元格设置合并居中，将 A20 与 B20 两个单元格设置合并居中，将 A21 与 B21两个单元格设置合并居中，最后设置 A19～A21 的文字大小为 12 号，字体为宋体。

2．自动求和按钮

操作要求：利用自动求和按钮计算总分和平均分列。

操作步骤如下：

（1）选中 M3 单元格，单击【开始】|【编辑】|【求和】命令后的下拉按钮，在打开的下拉列表中选择【求和】命令。

（2）在 M3 单元格中出现函数“=SUM（C3:L3）”，默认的参数“C3:L3”不正确，需重新设置。将鼠标指向 C3 单元格，拖曳到 K3 单元格，显示虚线框表示被选中的作为参数的区域，此时函数参数已设为“C3:K3”，表示对 3 行 3 列到 3 行 11 列的单元格进行求和，按【Enter】键表示确认。在M3 单元格中显示根据公式计算的结果，在编辑栏中显示此单元格所引用的公式。

也可以直接在 M3 单元格中输入“=C3+D3+E3+F3+G3+H3+I3+J3+K3”或“=SUM（C3:K3）”。

（3）用户可以通过下列方式将公式复制到其余单元格。

①将鼠标指针指向 M3 单元格右下角的填充柄上，当鼠标指针变为黑色的十字 ，向下拖曳鼠标填充公式，直到 M18 单元格。

由于函数“SUM（C3:K3）”的参数为相对引用，当该公式被填充到其他位置时，Excel 能够根据公式所在单元格位置的改变自动调节所引用的单元格。如在 M4 单元格，公式自动变为“SUM（C4:K4）”。

在 M18 单元格右下角出现【自动填充选项】按钮，单击其右边的小三角形，出现图 3-27 所示的下拉菜单，在该菜单中选择“不带格式填充”。否则，默认为带格式填充。

                          图 3‑27  自动填充选项

②用户也可以将公式复制到其他单元格。在 M3 单元格上，单击鼠标右键，打开快捷菜单，选择【复制】命令。选中 M4～M18 单元格区域，单击鼠标右键，在快捷菜单中选择【选择性粘贴】命令，打开“选择性粘贴”对话框，如图 3‑28所示。选择“粘贴”下的“公式”，单击【确定】按钮，即可在 M4～M18 单元格区域中出现求和公式。

                     图3‑28  “选择性粘贴”对话框

（4）选中 L3 单元格，单击【开始】|【编辑】|【求和】命令后的下拉按钮，在打开的下拉列表中选择【平均值】命令，如图 3‑29所示。

                            图3‑29  选择“平均值”

（5）在 L3 单元格中出现函数“=AVERAGE（C3:K3）”，按【Enter】键表示确认。默认的参数如果不正确，需重新设置。也可以直接在 L3 单元格中将公式改为“=AVERAGE（C3:K3）”或输入公

式“=M3/9”，将求平均值函数填充或复制到其他单元格。

（6）用同样的方法，可以计算出每门课程平均分。

3．MAX/MIN函数的使用

操作要求：计算出每门课程的课程最高分和课程最高分。

操作步骤：

（1）选中 C20 单元格，单击【开始】|【编辑】|【求和】命令后的下拉按钮，在打开的下拉列表中选择【最大值】命令，在 C20 单元格中出现函数“=MAX（C3:C19）”。默认的参数不正确，将鼠标指向 C3 单元格，拖曳到 C18 单元格，显示虚线框表示被选中的作为参数的区域。此时函数参数已设为“C3:C18”，表示对 3 列 3 行到 3 列 18 行的单元格求最大值，按【Enter】键表示确认。

也可以直接在 C20 单元格中将公式改为“=MAX（C3:C18）”。将 C20 单元函数填充或复制到 D20～K20 单元格。

（2）选中 C21 单元格，单击【开始】|【编辑】|【求和】命令后的下拉按钮，在打开的下拉列表中选择【最小值】命令，在 C21 单元格中出现函数“=MIN（C3:C20）”。默认的参数不正确，将鼠标指向 C3 单元格，拖曳到 C18 单元格，显示虚线框表示被选中的作为参数的区域。此时函数参数已设为“C3:C18”，表示对 3 列 3 行到 3 列 18 行的单元格求最小值，按【Enter】键表示确认。

也可以直接在 C21 单元格中将公式改为“=MIN（C3:C18）”。将 C20 单元函数填充或复制到 D21～K21 单元格。

4．设置单元格的数字格式

操作要求：设置平均分列和每门课程的课程平均分显示为小数点后两位。

对单元格可设置数值、货币、百分比、科学计数等多种数字格式。设置格式后，单元格中显示的是格式化后的结果，编辑栏中显示的是原始数据。

                           图3‑30   “数字格式”列表

操作步骤：选中 L3～L18 单元格区域，单击【开始】丨【数字】选项组“数字格式”列表框右边的箭头，如图 3‑30所示，在下拉菜单中选择“数字”。或单击“数字”选项组的右下角的箭头，打开“设置单元格格式”对话框，如图 3‑31所示，在“数字”选项卡下“分类”列表框中选择“数值”，在“小数位数”数值框中输入“2。”

用同样的方法，可设置课程平均分显示为小数点后两位。

        图3‑31  “设置单元格格式”对话框的“数字”选项卡

5．RANK函数的作用

操作要求：根据课程总分，对每个人的成绩进行排名。

RANK 函数是排名函数，最常用的是求某一个数值在某一区域内的排名，其语法形式为： RANK (number,ref,[order])

函数名后面的参数中，number 为需要求排名的数值或者单元格名称（单元格内必须为数字）；ref 为排名的参照数值区域，是一组数对一个数据列的引用，非数字值将被忽略；order 是排序方式，为 0 或忽略、降序，其他非 0 值（常用值 1）升序。

操作步骤：选中 N3 单元格，输入“＝RANK(M3,$M$3:$M$18,0)”，此处为求 M3 单元格数据在M3:M18 单元格区域数据列中的降序排名。注意此处 number 参数要用相对引用，因为此公式被填充或复制到区域时，被排名数字要发生变化；但 order 参数要用绝对引用，因为此公式被填充或复制到区域时，排名数据列不能发生变化。order 参数缺省或为 0，此处要求降序排名。通过“填充”或“选择性粘贴”将公式复制到 N4～N18 单元格区域。

也可通过函数向导来插入函数，其步骤如下：

（1）选中 N3 单元格，单击编辑栏的【插入函数】 按钮，打开“插入函数”对话框，如图 3‑32所示，在“选择函数”列表框中选择“RANK”，打开“函数参数”对话框，如图 3‑33所示。

                              图3‑32  插入函数

（2）在“函数参数”对话框中，在“Number”文本框中输入“M3”，在“Ref”文本框中输入“$M$3:$M$18”,在“Order”文本框中输入“0”或忽略，单击【确定】按钮。

                   图3‑33  “函数参数”对话框

【案例 3-3实现】

1．DATEDIF函数的使用

操作要求：利用 DATEDIF 函数计算“员工工资表”中工龄工资列数据。

DATEDIF 函数用于计算两个日期之间的年\月\日间隔数，其基本语法格式为：

DATEDIF(start_date,end_date,unit）

函数名后面的参数中，start_date 表示起始日期；end_date 表示结束日期；unit 表示所需信息的返回类型，其中Y 表示年，M 表示月，D 表示天。

操作步骤：打开“员工工资表”工作薄后，选中 D5 单元格，在编辑框中输入公式“=DATEDIF(员工通讯录!H3,TODAY(),"Y")*200”。此处为求“张丰”的工龄工资，注意此处 start_date 参数为“员工通讯录!H3”，表示要引用“员工通讯录”工作表中 H3 单元格中数据；end_date 参数为“TODAY()”，表示要引用当前系统的日期；unit 参数为"Y"，表示返回类型要用年表示，工龄工资的增加是根据每个人的入职年份计算的。

通过“填充”或“选择性粘贴”将公式复制到 D6～D16 单元格区域。

2．公式的使用

操作要求：利用公式计算“员工工资表”中加班工资、应发工资、个人所得税扣款及实发工资 4列数据。

（1）利用公式计算加班工资

操作步骤：选中 G5 单元格，在编辑框中输入公式“=E5*F5”，按【Enter】键后加班工资小计列数据自动计算出来。此处公式表示为，加班工资=加班天数*加班系数。通过“填充”或“选择性粘贴”将公式复制到 G6～G16 单元格区域。

（2）利用公式计算应发工资

操作步骤：选中 M5 单元格，在编辑框中输入公式“=B5+C5+D5+G5+H5+I5+J5—K5—L5”，按【Enter】键后，自动计算出应发工资列数据。此处公式表示为，应发工资=基本工资（底薪+岗位技能工资+工龄工资）+加班工资（小计）+奖金（业绩奖金+全奖金+特殊贡献奖）—捐款（社会扣除+考勤扣除）。通过“填充”或“选择性粘贴”将公式复制到 M6～M16 单元格区域。

（3）利用公式计算个人所得税扣款

操作步骤：选中 N5 单元格，在编辑框中输入公式“=(M5—3500)*10%—105”，按【Enter】键后，自动计算出张丰的个人所得税扣款列数据。此处公式不能通过“填充”或“选择性粘贴”将公式复制到N6～N16 单元格区域，因为每个人的工资不一样，所对应的捐款税率是不一样的。张丰的应发工资为 7012.5，个税起征点为 3500 元，全月应纳税额超过1500 元至 4500 元，因此可以直接减去3500，再乘以税率，最后减去速算。如果不知道个人工资税率，所有人应纳税额都没超过 3500元 ，可 以 用 IF 的嵌 套 公 式 ：

=IF（M5—3500<0,0,IF(M5—3500<1500,(M5—3500)*0.03,IF(M5-3500<4500,(M5-3500)*0.1-105,IF(M5-3500<9000,(M5-3500)*0.2-555,IF(M5-3500<35000,(M5-3500)*0.25-1005))))）”，如图 3‑34所示。

                   图3‑34个人所得税扣款计算

选中 N6～N16 单元格区域，单击【开始】丨【数字】选项组“数字格式”列表框右边的箭头，在下拉菜单中选择“数字”。或单击“数字”选项组的右下角的箭头，打开“设置单元格格式”对话框，在“分类”列表框中选择“数值”，在“小数位数”数值框中输入“2。”

（4）利用公式计算实发工资

操作步骤：选中 O5 单元格，在编辑框中输入公式“=M5-N5”，按【Enter】键后，自动计算出实发工资列数据。此处公式表示，实发工资=应发工资-个人所得税扣款。通过“填充”或“选择性粘贴”将公式复制到 O6～O16 单元格区域。

3．自动求和按钮、SUM函数、AVERAGE函数、MAX函数和MIN函数的使用

操作要求：利用自动求和按钮或函数计算工资总额、平均工资、最高工资及最低工资。

（1）计算工资总额

操作步骤：

①选中 O17 单元格，单击【公式】|【库函数】|【自动求和】命令的下拉按钮，也可单击【开始】|【编辑】|【求和】命令右边的下拉按钮，在打开的下拉列表中选择【求和】命令。

②在 O17 单元格中出现函数“=SUM（O5:O16）”，如果默认的参数不正确，需重新设置。将鼠标指向 O5 单元格，拖曳到 O16 单元格，显示虚线框表示被选中的作为参数的区域，此时函数参数已设为“O5:O16”，表示对 O5～O16 单元格区域求和，按【Enter】键表示确认。在 O17 单元格中显示出根据公式计算的结果，在编辑栏中显示出此单元格所引用的公式。

也可以直接在 M3 单元格中输入“= O3+O4+ O5+……+ O16”或“=SUM（O5:O16）”。

（2）计算平均工资

操作步骤：

①选中 O18 单元格，单击【公式】|【库函数】|【自动求和】命令的下拉按钮，在下拉菜单中选择【平均值】命令。

②在 O18 单元格中出现函数“=AVERAGE（O5:O17）”，默认的参数如果不正确，需重新设置成“O5:O16”，按【Enter】键表示确认。也可以直接在O18单元格中将公式改为“=AVERAGE（O5:O16）”，或输入公式“= O17/12。”

（3）计算最高工资及最低工资

操作步骤：

①选中 O19 单元格，单击【公式】|【库函数】|【自动求和】命令的下拉按钮，在下拉菜单中选择【最大值】命令，在 O19 单元格中出现函数“=MAX（O5:O18）”。默认的参数不正确，将鼠标指向 O5 单元格，拖曳到 O16 单元格，显示虚线框表示被选中的作为参数的区域，此时函数参数已设为“O5:O16”，按【Enter】键表示确认。也可以直接在 O19 单元格中将公式改为“=MAX（O5:O16）”。

②选中 O20 单元格，单击【公式】|【库函数】|【自动求和】命令的下拉按钮，在下拉菜单中选择【最小值】命令，在 O20 单元格中出现函数“=MIN（O5:O19）”。默认的参数不正确，将鼠标指向 O5 单元格，拖曳到 O16 单元格，显示虚线框表示被选中的作为参数的区域，此时函数参数已设为“O5:O16”，按【Enter】键表示确认。也可以直接在 O19 单元格中将公式改为“=MIN（O5:O16）”。

操作完成后用原文件名保存，编辑完成的“员工工资表”效果如图 3‑35所示。

                图3‑35“员工工资表”计算结果

3.2.4         课堂练习

【练习一】

现有图 3‑36所示的原材料明细表，用公式及函数对数据进行相应处理，实现对原材料的计算管理。

具体操作要求如下。

（1）用 PRODUCT 函数计算购入初期余额。

（2）用 SUM 函数汇总结存的数量、金额。

（3）用 PRODUCT 函数计算发出材料单价、金额。

（4）用 TRUNC 函数对合计栏的单价进行截尾取整。

           图3‑36原始材料明细表

操作步骤：

（1）打开文件后，在 G6 单元格中输入公式“=PRODUCT(E6,F6)”，可计算出 3 月 5 日购入某甲材料花费的费用，并将公式填充至 G6:G15 单元格区域。

（2）在 I7 单元格中输入公式“=SUM(M5,G6)/SUM(K5,E6)”，可计算出 3 月 8 日发出甲材料的单价。

（3）在 J7 单元格中输入公式“=PRODUCT(I7,H7)”，可计算出 3 月 8 日发出甲材料的金额。

（4）在 K7 单元格中输入公式“=K5+E6-H7”，可计算出3 月 8 日结存甲材料的数量。

（5）在 L7 单元格中输入公式“=I7”，可计算出 3 月 8 日结存甲材料的单价。

（6）在 M7 单元格中输入公式“=PRODUCT(L7,K7)”，可计算出 3 月 8 日结存甲材料的金额。使用相同的方法计算甲材料其他日期的发出和结存数据。

（7）在 E16 单元格中输入公式“=SUMIF($D$6:$D$15,＂购入材料＂,E6:E15)”，计算出本月收入甲材料的数量。使用相同的方法计算本月收入甲材料的金额G16、本月发出甲材料的数量H16、金额 J16。

（8）在 F16 单元格中输入公式“=TRUNC(G16/E16,2)”，计算出本月收入甲材料的单价。使用相同的方法计算本月发出甲材料的单价I16、结存单价 L16。

（9）在 K16 单元格中输入公式“=K5+E16-H16”，计算出本月结存甲材料的数量，在 M16 单元格中输入公式“=M5+G16-J16”，计算出本月结存甲材料的金额，在 L16 单元格中输入公式“=ROUND(M16/K16,2)”，计算出本月结存甲材料的单价。

涉及的函数说明如下：

① PRODUCT()函数返回的是将所有参数的数字相乘的值，如 PRODUCT(A2:A4,2)=A2*A3* A4*2。

② TRUNC()函数返回处理后的数值，其工作机制与 ROUND 函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入处理，而是统统截去。TRUNC（89.985，2）=89.98，TRUNC（89.985）=89 (即取整)，TRUNC（89.985，—1）=80。

③ ROUND()函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。ROUND（89.985，2）=89.99，ROUND（89.985）=90 (即取整)，ROUND（89.985，—1）=90。

     

                   图3‑37“原始材料明细表”计算结果        

 操作完成后用原文件名保存，编辑完成的“原始材料明细表”效果如图 3‑37所示

【练习二】

现有工作表“员工培训成绩表.xlsx”，如图 3‑38所示，要利用公式及函数计算其中有关数据。

                  图3‑38员工培训成绩表

具体操作要求如下。

（1）使用 SUM、AVERAGE 函数计算总成绩、平均成绩，其中平均成绩保留 1 位小数。

（2）使用 RANK、IF 函数计算排名、及排名等级。

平均分>=90: 优

80<=平均分<90: 良

60<=平均分<80: 一般

平均分<60: 差

（3）使用 COUNTIF 函数、SUMIF 函数计算每个部门的人数、每个部门平均成绩，其中平均成绩保留至 1 位小数。

（4）使用 COUNTIF 函数计算有不及格课程的人数。

（5）使用 COUNTIF 函数计算各部门各等级人数情。

函数说明如下。

（1）COUNTIF()函数用来计算区域中满足给定条件的单元格的个数。其语法格式为: 

COUNTIF(range,criteria)

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

（2）SUMIF()函数是用来根据指定条件对若干单元格进行求和，即按条件求和。其语法格式为：

SUMIF (range，criteria，sum_range)

其中，range 为条件区域，criteria为求和条件，sum_range为实际求和区域。

（3）countif ()函数用于对某一区域内满足多重条件的单元格进行计数，即多条件计数。其语法格式为：

COUNTIF(criteria_range1,criteria1,criteria_range2,criteria2,…) 相当于 countif（第一个条件区，第一个对应的条件，第二个条件区，第二个对应的条件，第N个条件区，第N 个条件对应的条件）。

（4）计算有不及格课程的人数时，先在 N3 单元格中输入公式“=IF(COUNTIF(D3:I3,"<60")>0,1,0)”，如果其值为“1”就表示此人有不及格课程，否则就没有不及格课程。然后在 Q9 单元格中

输入公式“=SUM（N3:N18）”。

操作完成后用原文件名保存，员工培训成绩表统计计算结果如图 3‑39所示。

         图3‑39员工培训成绩表统计计算结果