第6章　统计函数

6.1　平均值计算函数

函数1：AVERAGE函数（求平均值）

函数功能

AVERAGE函数用于计算所有参数的算术平均值。

函数语法

AVERAGE(number1,number2,...)

参数解释

number1,number2,...：表示要计算平均值的1～255个参数。

用法剖析

实例解析

实例209　快速自动求平均值

表格统计了学生的语文成绩，要求计算出平均分，利用Excel中的“自动求和”功能可以快速自动求平均值。

选中目标单元格，在“公式”选项卡的“函数库”组中单击“自动求和”按钮，在下拉菜单中单击“平均值”命令，如图6-1所示。

此时函数根据当前选中单元格左右的数据默认参与运算的单元格区域（如果默认参数区域不是我们想要的，则重新选取），如图6-2所示。

按Enter键即可完成操作，如图6-3所示。

实例210　在成绩表中忽略0值求平均分

表格中统计了学生各门功课的成绩，要求计算各门功课的平均分（0值要忽略），即得到第10行中的数据。

选中B10单元格，在公式编辑栏中输入公式：

按Ctrl+Shift+Enter组合键得出“语文”平均分（忽略0值），如图6-4所示。

选中B10单元格，拖动右下角的填充柄向右复制公式，即可批量得出其他科目的平均分（忽略0值）。

公式解析

①　依次判断B2∶B9单元格区域值是否大于0，如果是，返回TRUE，如果不是，返回FALSE，返回的是一个数组。

②　将①步数组中TRUE值的对应在B2∶B9单元格区域中取值，最后求出平均值。

实例211　计算指定学校学生的平均成绩

如图6-5所示的表格中统计了参加某项考试的学生的成绩，“班级”列中是全称，其中一个学校有多个班，要求统计出指定某个学校的平均分数。

选中E2单元格，在公式编辑栏中输入公式：

按Ctrl+Shift+Enter组合键得出“桃州一小”的平均分，如图6-6所示。

嵌套函数

• 　FIND函数属于文本函数类型，用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值。
• 　ISNUMBER函数属于信息函数类型。可以判断引用的参数或指定单元格中的值是否为数字。如果检验的内容为数字，将返回TRUE，否则将返回FALSE。

公式解析

①　在B2∶B13单元格区域中寻找“桃州一小”，找到返回“1”，找不到返回“#VALUE!”。

②　判断第①步返回值中是否为数字，是数字，返回TRUE，不是数字，返回FALSE。因此排除了第①步中结果为“#VALUE!”的单元格。

③　将第②步返回值中为TRUE的对应在C2∶C13单元格区域上的值取出并进行求平均值。

函数2：AVERAGEA函数（求包括文本和逻辑值的平均值）

函数功能

AVERAGEA函数返回给定参数（包括数字、文本和逻辑值）的平均值。

函数语法

AVERAGEA(value1,value2,...)

参数解释

value1,value2,...：表示为需要计算平均值的1～30个单元格、单元格区域或数值。

用法剖析

实例解析

实例212　计算平均分时将“缺考”的也计算在内

表格中统计了学生的成绩（包括缺考的），要求计算每位学生的平均成绩（缺考的也计算在内）。

选中G2单元格，在公式编辑栏中输入公式：

按Enter键得出第一位学生的平均分，如图6-7所示。

选中G2单元格，拖动右下角的填充柄向下复制公式，即可批量得出其他学生的平均分。

公式解析

=AVERAGEA(B2∶F2)

求B2∶F2单元格区域的所有成绩的平均分。

提示

如果直接使用AVERAGE函数计算平均分，将自动忽略“缺考”项。例如第5行有一项缺考的，用AVERAGE函数为“SUM(B5∶F6)/4”；而AVERAGEA函数则为“SUM(B5∶F6)/5”。

实例213　统计各月份的平均销售额（计算区域含文本值）

下面的表格中要求计算出各个月份中的平均销售额，其中有一个销售部在3月中处于调整状态，但在计算平均销售额时，也要求将其计算在内。

选中E2单元格，在公式编辑栏中输入公式：

按Enter键即可计算出1月份平均销售额。

选中E2单元格，拖动右下角的填充柄向下复制公式，即可求解出其他各个月份的平均销售额，如图6-8所示（注意3月份的平均销售额）。

公式解析

=AVERAGEA(B2∶D2)

求B2∶D2单元格区域的所有分数的平均值（去除文本）。

函数3：AVERAGEIF函数（按条件求平均值）

函数功能

AVERAGEIF函数用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。

函数语法

AVERAGEIF(range,criteria,average_range)

参数解释

• 　range：表示要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。
• 　criteria：表示数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为“32”“>32”“apples”或“B4”。
• 　average_range：表示要计算平均值的实际单元格集。如果忽略，则使用range。

用法剖析

实例解析

实例214　统计各班级平均分

表格中统计了学生成绩（分属于不同的班级），要求计算出各个班级的平均分，即得到F2∶F4单元格区域中的值。

选中F2单元格，在公式编辑栏中输入公式：

按Enter键得出“1班”的平均分数，如图6-9所示。

选中F2单元格，拖动右下角的填充柄至F4单元格中，即可快速计算出“2班”与“3班”的平均分数，如图6-10所示。

提示

E2∶E4单元格区域的数据需要被公式引用，因此必须事先建立好，并确保正确。

公式解析

=AVERAGEIF($A$2∶$A$13,E2,$C$2∶$C$13)

在A2∶A13单元格区域中寻找与E2单元格中数据相同的记录，并返回对应在C2∶C13单元格区域中的分数，最后对返回的所有满足条件的数据求平均值。

实例215　计算月平均出库数量

表格中按月份分别统计了商品的出入库数量，要求统计出月平均出库数量（入库不统计）。

选中E2单元格，在公式编辑栏中输入公式：

按Enter键得出月平均出库数量，如图6-11所示。

公式解析

=AVERAGEIF(B2∶B13,"出库",C2∶C13)

在B2∶B13单元格区域中寻找所有“出库”记录，并返回对应在C2∶C13单元格区域中的数量，最后对返回的所有满足条件的数据求平均值。

提示

如果想统计月平均入库数量，只需要将公式更改为“=AVERAGEIF(B2∶ B13,"入库",C2∶C13)”即可。

实例216　排除新店计算平均利润

表格中统计了各个分店的利润金额，要求排除新店计算平均利润。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键得出结果，如图6-12所示。

公式解析

①　注意这个条件的设置，它使用了通配符，表示以“（新店）”结尾，前面再使用“<>”符号，表示所有不以“（新店）”结尾的即为满足的条件。

②　在A2∶A11单元格区域中寻找所有不以“（新店）”结尾的记录，并返回对应在B2∶B11单元格区域中的利润值，最后对返回的所有满足条件的值求平均值。

函数4：AVERAGEIFS函数（按多条件求平均值）

函数功能

AVERAGEIFS函数用于返回满足多重条件的所有单元格的平均值（算术平均值）。

函数语法

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

参数解释

• 　average_range：表示要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。
• 　criteria_range1, criteria_range2, …：表示计算关联条件的1～127个区域。
• 　criteria1, criteria2, …：表示数字、表达式、单元格引用或文本形式的1～127个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为“32”“>32”“apples”或“B4”。

用法剖析

实例解析

实例217　计算一车间女职工平均工资

表格中统计了各职工的工资（分属于不同的车间，并且性别不同），现在要求统计出指定车间、指定性别的平均工资，即需要同时满足两个条件。

选中D14单元格，在公式编辑栏中输入公式：

按Enter键即可统计出一车间女性职工的平均工资，如图6-13所示。

公式解析

①　第一个条件判断区域与第一个条件。

②　第二个条件判断区域与第二个条件。

③　同时满足①与②条件时，将对应在D2∶D12单元格区域上的值取出并进行求平均计算。

实例218　求介于某一区间内的平均值

表格中规定了某仪器测试的有效值范围与8次测试的结果（其中包括无效的测试）。要求排除无效测试计算出有效测试的平均值。

选中B12单元格，在公式编辑栏中输入公式：

按Enter键得出介于有效范围内的平均值，如图6-14所示。

公式解析

①　第一个条件判断区域与第一个条件。

②　第二个条件判断区域与第二个条件。

③　同时满足①与②条件时，将对应在B3∶B10单元格区域上的值取出并进行求平均计算。

实例219　统计指定店面所有男装品牌的平均利润

表格中统计了不同店面不同品牌（分男女品牌）商品的利润。要求统计出指定店面中所有男装品牌的平均利润。

选中C15单元格，在公式编辑栏中输入公式：

按Enter键即可统计出1店面男装的平均利润，如图6-15所示。

公式解析

①　第一个条件判断区域与第一个条件。

②　第二个条件判断区域与第二个条件。注意第二个条件中使用了通配符，表示只要以“男”结尾则为满足条件。

③　同时满足①与②条件时，将对应在C2∶C13单元格区域上的值求平均值。

实例220　忽略0值求指定班级的平均分

表格中统计了各个班级学生成绩（其中包含0值），现在要求计算指定班级的平均成绩并且要求忽略0值。

选中F4单元格，在公式编辑栏中输入公式：

按Enter键即可计算出班级为“1”的平均成绩且忽略0值。

选中F4单元格，向下复制公式到F5单元格，即可计算出班级为“2”的平均成绩，如图6-16所示。

公式解析

①　第一个条件判断区域与第一个条件。

②　第二个条件判断区域与第二个条件。

③　同时满足①与②条件时，将对应在C2∶C11单元格区域上的值求平均值。注意因为建立第一个公式后要向下复制求取班级2的平均分，所以除了条件1除外，其他单元格区域都要使用绝对引用。

函数5：GEOMEAN函数（返回几何平均值）

函数功能

GEOMEAN函数用于返回正数数组或数据区域的几何平均值。

函数语法

GEOMEAN(number1,number2,...)

参数解释

number1,number2,...：表示需要计算其平均值的1～30个参数。

实例解析

实例221　判断两组数据的稳定性

例如表格是对某两人6个月中工资的统计。利用求几何平均值的方法可以判断出谁的收入比较稳定。

选中E2单元格，在公式编辑栏中输入公式：

按Enter键即可得到“小张”的月工资几何平均值，如图6-17所示。

选中F2单元格，在公式编辑栏中输入公式：

按Enter键即可得到“小李”的月工资几何平均值，如图6-18所示。从统计结果可以看到，虽然小张的合计工资大于小李的合计工资，但小张的月工资几何平均值却小于小李的月工资几何平均值。几何平均值越大表示其值越稳定，因此小李的收入更加稳定。

函数6：HARMEAN函数（返回数据集的调和平均值）

函数功能

HARMEAN函数用于返回数据集合的调和平均值（调和平均值与倒数的算术平均值互为倒数）。

函数语法

HARMEAN(number1,number2,...)

参数解释

number1,number2,...：表示需要计算其平均值的1～30个参数。

实例解析

实例222　计算固定时间内几位学生平均解题数

在实际应用中，往往由于缺乏总体单位数的资料而不能直接计算算术平均数，这时需要用调和平均法来求得平均数。例如5名学生分别在一个小时内解题4、4、5、7、6，要求计算出平均解题速度。我们可以使用公式“=5/(1/4+1/4+1/5+1/7+1/6) ”计算出结果等于4.95。但如果数据众多，使用这种公式显然是不方便的，因此可以使用HARMEAN函数快速求解。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可计算出平均解题数，如图6-19所示。

函数7：TRIMMEAN函数（截头尾返回数据集的平均值）

函数功能

TRIMMEAN函数用于从数据集的头部和尾部除去一定百分比的数据点后，再求该数据集的平均值。

函数语法

TRIMMEAN(array,percent)

参数解释

• 　array：表示需要进行筛选并求平均值的数组或数据区域。
• 　percent：表示计算时所要除去的数据点的比例。当percent=0.2时，在10个数据中去除2个数据点（10×0.2=2），在20个数据中去除4个数据点（20×0.2=4）。

实例解析

实例223　通过10位评委打分计算选手的最后得分

在进行技能比赛时，10位评委分别为进入决赛的3名选手打分，通过10个打分结果计算出3名选手的最后得分。

选中B13单元格，在公式编辑栏中输入公式：

按Enter键即可在10个数据中去除2个数据点后再进行求平均值计算。

选中B13单元格，向右复制公式，即可计算出其他选手的最后得分，如图6-20所示。