1.10 常用函数进阶应用

在1.6节中我们学习了求和、平均值、计数、最大值、最小值几个常用函数的基础应用，接下来学习稍微有点深度的应用，加强巩固对这几个常用函数的理解。

1.10.1 SUM函数

SUM是一个数学函数，可将值相加。你可以将单个值、单元格引用、单元格区域、数组相加。如果求和的值是文本则忽略不计；如果是错误值，则求和结果也是错误值。

实例6 求家庭收支明细表中的累计余额

问题描述： 图1-33所示的“家庭收支明细表”中列出了期初金额500元，以及2017年8月每天的家庭收入与支出费用。为了方便排版只显示了部分日期的数据，现需要求出每天的累计余额，也就是加上当天的收入，再减去当天的支出之后剩余的金额。

解决思路： 将上月月末余下的金额，也就是本月的期初金额，加上从1日到当前日每天的累计收入，减去从1日到当前日每天的累计支出。

操作步骤： 如图1-34所示，在D4单元格录入公式=SUM(B$4:B4)-SUM(C$4:C4)+$B$2，向下填充公式，完成计算。下面讲解详细运算过程。

步骤1： SUM(B$4:B4)中B$4:B4区域的起点单元格和终点单元格是一样的，起点的B$4要固定行号，终点的B4不固定。这样B$4:B4区域向下填充时B$4始终不变，B4则不断向下扩展区域，外层再嵌套一个SUM函数就不断求和出累计收入金额。为了便于理解，请参考图1-35中公式的变化规则。

步骤2： SUM(C$4:C4)中C$4:C4区域的起点单元格和终点单元格是一样的，起点的C$4要固定行号，终点的C4不固定。这样C$4:C4区域向下填充时C$4始终不变，C4则不断向下扩展区域，外层再嵌套一个SUM函数就不断求和出累计支出金额。

步骤3： 将步骤2的累计收入金额减去步骤1的累计支出金额，再加上期初金额，公式为=SUM(B$4:B4)-SUM(C$4:C4)+$B$2，将公式向下填充，即计算出了每天的累计金额。

1.10.2 AVERAGE函数

AVERAGE函数计算平均值（算术平均值），支持常量数字、单元格引用的数字等。接下来讲解一下AVERAGE函数的参数特性。

直接将逻辑值和文本型数字写在公式中是可以被计算的，比如公式：=AVERAGE（TRUE，〝2〝），返回结果为1.5。但要引起注意的是如果逻辑值和文本型数字出现在单元格区域中，情况就有些变化了，图1-36所示为使用AVERAGE和AVERAGEA两个函数对不同数据类型做测试的效果。如果不支持，则返回逻辑值＃DIV/0！，支持则返回对应的平均数字。可以看出AVERAGE函数只支持数字，而AVERAGEA函数则会将逻辑值识别成对应的1或0，而将文本（包括文本型数字）识别为0。

实例7 计算每位客服人员的奖金是多少

问题描述 ：图1-37所示的表中有4个月份，每个月占有总奖金的1/4份，奖金总额为200元。如果其中1个月等级为优，则相当于1/4*200。那么求每位客服人员应得的奖金是多少？

解决思路： 先分别判断这4个月的等级是否等于“优”，再将运算出来的逻辑值TRUE或FALSE进行平均计算，得出的平均值乘以200的结果就是每位客服人员的所得奖金。

操作步骤： 如图1-38所示，在F3单元格输入公式=AVERAGE（B3=〝优〝，C3=〝优〝，D3=〝优〝，E3=〝优〝）*200，向下填充公式，完成计算。下面讲解详细运算过程。

步骤1： AVERAGE函数中使用了4个参数，判断每个月的等级是否为“优”。那么所得出的结果自然是逻辑值TRUE或FALSE，TRUE相当于1，FALSE相当于0。

步骤2： 看一下AVERAGE函数中的平均运算原理。

● 判断结果有1个成立，比如AVERAGE（1，0，0，0），占1/4，平均结果为0.25；

● 判断结果有2个成立，比如AVERAGE（1，1，0，0），占2/4，平均结果为0.5；

● 判断结果有3个成立，比如AVERAGE（1，1，1，0），占3/4，平均结果为0.75；

● 判断结果有4个成立，比如AVERAGE（1，1，1，1），占4/4，平均结果为1。

步骤3： 将步骤2的平均结果与200相乘，得到最后的奖金金额。

1.10.3 COUNT函数

COUNT 函数计算单元格、数组等区域中的数字个数。例如，在公式=COUNT(A1:A20)中，如果A1:A20区域中有 5 个单元格包含数字，则答案就为 5。

直接写在COUNT函数参数中的逻辑值和文本型数字将被计算。例如，=COUNT（〝100〝，TRUE，〝Excel〝，FALSE，＃DIV/0！）计算结果为3，其中〝100〝、TRUE、FALSE转换成功，〝Excel〝、＃DIV/0！转换失败，文本和错误值不能被转换，所以忽略不计，将不计算在内。但如果逻辑值、文本型数字在单元格区域中，则不会被计算。

实例8 计算员工一周的出勤率

问题描述： 图1-39所示的表格中，1表示正常上班；0表示上班迟到，虽然迟到但仍记为正常出勤；请假、旷工、单元格空白等记为缺勤。

解决思路： 先统计每个人从周一到周日1和0的个数，再除以7，结果就是出勤率。

操作步骤： 如图1-40所示，在I3单元格输入公式=COUNT(B3:H3)/7，向下填充公式完成计算。下面讲解详细运算过程。

步骤1： COUNT(B3:H3)计算出勤天数，单元格区域中正常出勤的标记为1和0，是数字。其他缺勤都是以非数字的方式来标记的。而COUNT函数的特性是：只计算单元格中数字的个数，所以计算结果为6。

步骤2： 将步骤1的结果除以7，公式为=6/7，7表示一周的天数。计算结果为86%。

1.10.4 MAX函数

MAX函数求参数中的最大数字。参数支持数字、文本型数字、单元格引用，遇到错误值则会返回错误值。直接将逻辑值、文本型数字写在参数中将被计算；如果是写在单元格中再引用求最大值，则不会被计算。如果引用区域中没有数字，则会返回0。

实例9 计算员工上班迟到的分钟数

问题描述： 图1-41所示的表中登记了每个员工的上班时间，规定的标准上班时间为8:00，现需要在C列求出每个员工的迟到分钟数。

解决思路： 将员工迟到上班时间，减去标准上班时间8:00的结果就是该员工迟到分钟数。关键是用什么方法去提取员工迟到上班的时间，而若员工没有迟到，则不减或减出来也为0。

操作步骤： 如图1-42所示，在C3单元格输入公式=MAX（〝8：00〝，B3）-〝8：00〝，向下填充公式，完成计算。下面讲解详细运算过程。

步骤1： 计算关键公式MAX（〝8：00〝，B3），将规定上班时间8：00与B3单元格中的实际上班时间进行比较取最大值，如果B3单元格的时间比8：00大，则取B3的时间，反之，若比8：00小，则取8：00。本公式取值为8：02。

步骤2： 将步骤1获取的迟到上班时间8:02减去标准上班时间8:00，最后迟到时间为0:02（2分钟）。

1.10.5 MIN函数

MIN函数求参数中的最小数字。函数的相关属性与1.10.4节的MAX函数是一样的。只不过MAX函数求最大值，而MIN函数求最小值。

实例10 计算两科成绩同时大于或等于 90 分的学生的平均分

问题描述： 图1-43中列出了每个学生的语文和数学分数，如果两科成绩同时大于或等于90分，那么才将这两科成绩进行平均计算。否则不计算，显示为空就可以了。

解决思路： 将语文、数学、最低标准90三个分数求最小值，如果返回结果为90，则证明两科成绩均大于或等于90分；如果返回结果小于90，则证明两科成绩至少有一科是小于90分的。无论怎样都要将求得的最小值与90作等于比较，再乘以两科成绩的平均分。

操作步骤： 如图1-44所示，在D3单元格输入公式=(MIN(B3:C3,90)=90)*AVERAGE(B3:C3)，向下填充公式完成计算。下面讲解详细运算过程。

步骤1： MIN(B3:C3,90)是将B3:C3单元格区域中的94、95与90进行比较，取最小值。如果B3:C3的值都大于或等于90，则返回90。如果B3:C3中至少有一个值小于90，则返回B3:C3中的最小值，这个值肯定是小于90的。当前公式返回值为90。

步骤2： 将步骤1获取的值与最低标准90进行等于比较，若等于90，则证明两科成绩大于或等于90，返回结果TRUE；若不等于90，则证明至少有一科是小于90的，返回结果FALSE。当前公式为90=90，结果为TRUE。

步骤3： 将步骤2判断出来的逻辑值与平均分相乘。TRUE乘以平均分，等于平均分；FALSE乘以平均分，等于0。当前公式为TRUE*AVERAGE(B3:C3)，结果为94.5。

注意：如果不想显示单元格中的零值也可以隐藏。选择【文件】菜单→【选项】命令，弹出【Excel选项】对话框，再单击【高级】选项，取消勾选【在具有零值的单元格中显示零】复选框。

1.10.6 综合应用

实例11 计算每个参赛选手的最后得分

问题描述： 图1-45列出了每个评委给选手的评分。现在需要求出每个参赛选手的最后得分，计算最后得分的公式为：(总分-1个最高分-1个最低分)/(评委人数-2)。

解决思路：首 先用SUM函数求出每个参赛选手的总分，然后减去用MAX函数求出的最大值，再减去用MIN函数求出的最小值，最后除以总评委数减去2。

操作步骤： 如图1-46所示，将鼠标光标定位在I3单元格中，输入公式=(SUM(B3:H3)-MAX (B3:H3)-MIN(B3:H3))/(COUNT(B3:H3)-2)，向下填充公式完成计算。下面讲解详细运算过程。

步骤1： 求总分的公式为SUM(B3:H3)，将所有评委的打分进行求和，结果为61.77。

步骤2： 将步骤1中的总分，减去所有评分中的最高分，公式为61.77-MAX(B3:H3)，计算结果为52.2。

步骤3： 将步骤2中的分数，减去所有评分中的最低分，公式为52.2-MIN(B3:H3)，计算结果为44.06。

步骤4： 将步骤3中的分数，除以COUNT(B3:H3)-2计算的评委人数，因为有评委弃权，所以每个参赛选手的打分评委人数可能不一致。减2是因为在总分基础上，减掉了一个最高分和一个最低分，相当于要减掉两个人。最后公式为44.06/(COUNT(B3:H3)-2)，得出平均分为8.812。

我们再次对每个常用函数给出举例应用，一是为了加深对这些函数的理解，为后面更好地应用这些函数做铺垫；二是通过这些实例告诉大家，函数是死的，人是活的，要学好函数，逻辑思维才是王道。 uGnnmJAGAo67eB/+hNnSOXOy6uyk8f2RiX2ukstLJOG/Oj8k2+50lC7RZfs69QzE