Excel公式和函数计算平均值.docx

《Excel公式和函数计算平均值.docx》由会员分享，可在线阅读，更多相关《Excel公式和函数计算平均值.docx（10页珍藏版）》请在三一办公上搜索。

1、Excel公式和函数 计算平均值Excel公式和函数 计算平均值 在分析实际问题时，为了反映整个过程或整体的概貌，经常会引入平均值的概念。平均值有多种类型，常见的有几何平均值、算术平均值、加权平均值、方均根等等。平均值的引入能够使计算结果显得更加直观、简易。在Excel中，提供了一系列专门用于各种平均值统计的函数。 1AVEDEV函数 AVEDEV函数用于返回一组数据与其均值的绝对偏差的平均值，该函数通常用于评测数据的离散度，如学生的某科考试成绩。离散度是描述数值变量资料频数分布的主要特征。 语法：AVEDEV (number1, number2,.) 其中，参数Number1, number

2、2, .用于计算绝对偏差平均值的一组参数，参数的个数可以有1到255个。输入数据时，所使用的计量单位将会影响AVEDEV函数的计算结果。 例如，如图7-25所示为某个学生参加演讲比赛的得分情况，求该生得分的绝对偏差平均值。 选择D14单元格，在对话框中，选择AVEDEV函数，在对话框中，设置参数Number1为D4:D13，单击按钮，即可得出计算结果为0.26，如图7-26所示。 设置 计算结果 得分情况 图7-25 得分情况表 图7-26 绝对偏差平均值 用户也可以选择D14单元格，在中输入“=AVEDEV(D4:D13)”公式，计算 绝对偏差的平均值。 技 巧 2AVERAGE和AVERA

3、GEA函数 在进行数据统计时，如统计学生考试情况或者销售情况，经常需要计算学生各科目的平均成绩或销售业绩，此时，就可以利用Excel统计函数中的AVERAGE和AVERAGEA函数进行计算。这两种函数都可以返回参数的算术平均值，两者区别在于如何对待非数值的单元格。 算术平均值它是将一组数据相加后，除以数据的个数得出的。例如，2、3、3、5、7和10的平均数是30除以6，结果是5。 语法：AVERAGE (number1, number2,.) AVERAGEA (value1, value2,.) 其中的参数均表示需要计算平均值的1到255个参数，如果计算中不包括引用的逻辑值和代表数字的文本，

4、可以使用AVERAGE函数；若包括引用中的逻辑值和代表数字的文本，则应该使用AVERAGEA函数。 例 参数作为0计算；包含文本的数组、引用参数或者空文本也作为0计算。 如，在注 意 “评分情况表”中，选择D15单元格，在中，输入“=AVERAGE(D4:D13)”公式，按Enter键即可计算出数值区域的平均值，如图7-27所示。 然后，选择D16单元格，输入“=AVERAGEA(D4:D13)”公式，即可计算所有非空单元格区域的平均值，如图7-28所示。 在使用AVERAGEA函数进行计算时，包含TRUE的参数作为1计算，包含FALSE的输入公式 输入公式 计算结果 计算结果 图7-27 数

5、值区域的平均值 图7-28 包含文本的平均值 在使用AVERAGE函数计算“数字平均值”时，含有文本的单元格将 不被计算在内；而使用AVERAGEA函数进行计算时，D10单元格将被作为0值参与注 意 计算。 数 3TRIMMENA函如果在统计数据时，希望在分析中剔除一部分数据的计算，可以使用TRIMMENA函数来实现。该函数可以先从数据集的最大值和最小值除去一定百分比的数据点，然后再计算平均值。 例如，在计算选手平均分数时常去掉一个最高分，去掉一个最低分，该选手的最后得分就是利用TRIMMENA函数进行计算的。 语法：TRIMMEAN (array, percent) 其中，参数Array表示

6、需要进行筛选并计算平均值的数组或数据区域；参数Percent表示计算时所要除去数据点的比例。例如，如果参数Percent等于0.2，则在20个数据点的集合中，就要除去4个数据点，其中开始位置除去2个，结束位置除去2个。 如果参数Percent的值小于0或者大于1，则TRIMMEAN函数将返回错误值#NUM!；如果参数Percent等于0.1，30个数据点的10%等于3个数据点，TRIMMEAN函数将对称地在数据集的头部和尾部各除去一个数据。 例如，在“得分情况表”中，要求去掉一个最高分，去掉一个最低分后，计算该选手的最后得分。 选择D17单元格，插入TRIMMEAN函数，在对话框中，设置参数A

7、rray为D4:D13；参数Percent为0.2，即可得出该选手的最后得分为9.38分，如图7-29所示。 设置 计算结果 图7-29 计算最后得分 4AVERAGEIF函数 使用AVERAGEIF函数，可以在一定范围内根据条件进行平均值的自动计算。该函数可以返回某个区域内满足给定条件的所有单元格的平均值。 语法：AVERAGEIF (range, criteria, average_range) 其中，各参数的意义如下所示： l 用； l Criteria 参数Criteria表示数字、表达式、单元格引用或者文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为30、30、

8、30、apples或者B4等。 l Average_range 该参数表示要计算平均值的实际单元格集。如果忽略该参数，则使用Range进行计算。 Range 参数Range表示要计算平均值的一个或者多个单元格，可以包括数字、包含数字的名称、数组或者引例如，飓风电脑公司对一月至四月的销售情况进行了统计，得到如图7-30所示的销售统计表，要求计算各月份销售量在700以上的平均值。 选择“一月份销售量平均值”所对应的单元格，即C19单元格，插入AVERAGEIF函数，在对话框中，设置参数Range为C4:C17；参数Criteria为“700”，并忽略参数Average_range的值，即可计算出一

9、月份销售量在700以上的平均值，如图7-31所示。 设置 计算结果 图7-30 电脑配件销售统计表 图7-31 一月份700以上的销量平均值 在设置函数参数时，还需要注意以下几点： l l l l l l 忽略单元格区域中包含TRUE或者FALSE的单元格； 如果参数Average_range中的单元格为空单元格，那么AVERAGEIF将忽略该单元格； 如果参数Range为控制或者文本值，AVERAGEIF函数将返回错误值#DIV0!； 如果条件中的单元格为空单元格，则AVERAGEIF函数将其视为0值； 如果所选区域中没有满足条件的单元格，函数将返回错误值#DIV/0!； 用户可以在条件中使

10、用通配符，即问号(?)和星号(*)。问号匹配任意一个单个字符；星号匹配任意一个字符序列。 然后，向右拖动C19单元格右下角的填充柄，将公式填充至F19单元格，即可计算各月份销量在700以上的平均值，如图7-32所示。 填充公式 图7-32 各月700以上的销量平均值 5AVERAGEIFS函数 该函数与AVERAGEIF函数类似，其区别在于AVERAGEIF函数用于返回满足单个条件的所有单元格的平均值；而AVERAGEIFS函数用于返回满足多重条件的所有单元格的平均值。 语法：AVERAGEIFS (average_range, criteria_range1, criteria1, crit

11、eria_range2, criteria2) 其中，参数Average_range表示要计算的一个或多个单元格，包括数字，包含数字的名称、数组或者引用；参数Criteria_range1, Criteria_range2, 表示计算关联条件的1至127个区域；参数Criteria1, criteria2, 表示数字、表达式、单元格引用或者文本形式的1至127个条件，用于定义要对哪些单元格求平均值。 例如，某学校高三年级学生进行了一次模拟考试，校方对学生的各科成绩、总分和班级情况进行了整理，得出如图7-33所示的成绩表。如何计算2班学生各科成绩的平均分数？ 选择C18单元格，并插入AVERAG

12、EIFS函数，在对话框中，设置参数Average_range为C3:C16；参数Criteria_range1为G3:G16；参数Criteria1为2，即可计算出2班学生语文成绩的平均分数为109，如图7-34所示。 设置 计算结果 图7-33 高三年级考试成绩表 图7-34 2班语文成绩平均分数 在设置函数参数时，参数Average_range被设置为C3:C16，表示要计算的是语文分数；而设置参数Criteria_range1为G3:G16和Criteria1位2，则表示要计算的班级是2班。 使用AVERAGEIFS函数计算平均值，同样需要注意几点： l l l l 所选单元格区域中，包

13、含TRUE的单元格计算为1，包含FALSE的单元格计算为0； 仅当Average_range中的每个单元格满足为其指定的所有相应条件时，才对这些单元格进行平均值计算； 如果Average_range中的单元格无法转换为数字，则AVERAGEIFS函数将返回错误值#DIV0!； 同样，用户可以在条件中使用通配符。 然后，再使用相同的方法，分别计算数学和英语成绩的平均分数，其计算结果如图7-35所示。 计算结果 图7-35 2班数学和英语平均分数 此时，由于该单元格公式的条件中不是绝对引用，因此，不能利用拖动填充柄的 用户也可以选择方式复制公式。 C18单元格，输入“=AVERAGEIFS(C3:

14、C16,$G$3:$G$16,2)”公式，计算提 示 语文的平均分数后，拖动其填充柄，将公式填充至E18单元格。 技 巧 数 6GEOMEAN函该函数可以返回正数数组或者区域的几何平均值，如计算可变复利的平均增长率。几何平均值是指n个正实数乘积的n次算术根，给定n个正实数a1，a2，an，其几何平均数为：(a1a2an)(1/n)。 语法：GEOMEAN (number1, number2,.) 其中，参数Number1, number2, .是用于计算平均值的1到255个参数，用户既可以使用逗号分隔参数的形式，也可以使用单个数组或对数组的引用。 例如，已知一组可变复利的增长率数据，如图7-3

15、6所示，要求计算该组数据的平均增长率。 选择C5单元格，并插入GEOMEAN函数，在对话框中，设置参数Number1为A3:G3，即可计算出可变复利的平均增长率为2.06982，如图7-37所示。 平均增长率 可变复利的增长率 设置 图7-36 可变复利的增长率 图7-37 平均增长率 7HARMEAN函在计算几何平均值时，如果任何一个数据点的值小于0，GEOMEAN函数将返回错 误值用户也可以选择#NUM!。 C5单元格，直接在中输入“=GEOMEAN(A3:G3)”公式，提 示 计算平均增长率。 技 巧 数 该函数可以返回数据集合的调和平均值。调和平均值是平均数的一种，但统计调和平均数与数

16、学调和平均数不同。 在数学中，调和平均数与算术平均数都是独立自成体系的，两者的计算结果不相同且后者恒小于后者，因而数学调和平均数定义为：数值倒数平均数的倒数。统计调和平均数则与之不同，它是加权算术平均数的变形，附属于算术平均数，不能单独成立体系，且计算结果与加权算术平均数完全相等。它主要是用于解决在无法掌握总体单位数的情况下，只有每组的变量值和相应的标志总量，而需要求得平均数的情况下使用的一种数据方法。调和平均值与倒数的算术平均值互为倒数。 语法：HARMEAN (number1, number2,.) 该函数中的参数意义与GEOMEAN函数中参数的意义相同。如果任何一个数据点的值小于或者等于0，HARMEAN函数将返回错误值#NUM!，并且调和平均值总是小于几何平均值，而几何平均值总是小于算术平均值。 例如，某人要从A地到B地，已知去时的速度为60KM/H，回来时的速度为40KM/H，求该人的平均速度为多少？ 将已知条件输入到工作表中之后，选择“平均速度”所对应的单元格，即C6单元格，并插入HARMEAN函数。然后，在对话框中，设置参数Number1为C3:C4，即可计算出其平均速度为48KM/H，如图7-38所示。 设置 计算结果 图7-38 平均速度