Excel统计函数.docx

1、Excel统计函数第 4 章 统计函数在 Excel 中，统计函数包含众多的函数。从实际应用角度，统计函数可以分为描述统 计函数、概率分布函数、假设检验函数和回归函数等。这些函数分别对应着统计应用的各 方面。在本章中，将对每个函数结合具体的例子进行详细分析。4.1 描述统计函数分析本小节中介绍的函数主要用来分析整体的数值特征，包括实际生活中最常见的最大 值、最小值等参数。计算并理解这些参数，用户就可以掌握一组数据的整体属性。本小节 将详细介绍各函数的功能和使用方法。4.1.1 AVEDEV 函数：计算平均绝对偏差【功能说明】计算数值的平均绝对偏差。偏差表示每个数值与平均值之间的差，平均 偏差表

2、示每个偏差绝对值的平均值。函数主要用来衡量数据的离散程度。【语法表达式】AVEDEV(number1，number2，)【参数说明】number1，number2，：表示需要计算平均偏差的数值。参数可以指定 单元格区域，参数个数最多是 255。【使用说明】 函数的参数可以是数字、数字的数组、名称或引用。 在计算过程中函数将忽略空白单元格、包含逻辑值和文本的单元格，但包含 0 值 的单元格不会忽略。【实际应用】某公司对一批货物进行称重，公司选择其中的 10 件进行测试。记录各货物的重量， 现在需要计算平均绝对偏差。在单元格 F11 中输入函数表达式“=AVEDEV(B2:B11)”，求解的结果如

3、图 4.1 所示。图 4.1 计算重量的平均绝对偏差【应用说明】在实际应用中，AVEDEV 函数常用来检测产品的平均偏差，来衡量实际 和标准规格之间的离散程度。 AVERAGE 和 AVERAGEA 函数：计算平均值【功能说明】计算数值的平均值。【语法表达式】AVERAGE（number1，number2，）；AVERAGEA(value1，value2，)【参数说明】 number1，number2，：表示数值。使用函数时，可以是数值、指定单元格或单 元格区域，个数最多为 255 个。 value1，value2，：表示列表中各种类型的数据。使用函数时，参数的值可以是 具体的数值或者指定单元

4、格，个数最多可达 30 个。【使用说明】 在函数 AVERAGEA 中，如果参数是数组，函数在计算时则忽略空单元格；如果 数组中包括文本，则将其看作 0。 在函数 AVERAGEA 中，如果参数是逻辑值，函数把 TRUE 看作 1，FALSE 看作0。 AVERAGEA 和 AVERAGE 函数的差别是：AVERAGEA 函数将文本单元格包括 在内，而 AVERAGE 函数则忽略文本单元格。【实际应用】某学校统计了 10 名同学的考试成绩，由于有同学却考，因此成绩为“无效”。现在学校需要根据不同的标准统计平均分，基础数据如图 4.2 所示。图 4.2 基础数据根据上面的基础数据，具体求解步骤如

5、下：（1）在单元格 E10 中输入函数表达式“=AVERAGEA(B2:B11)”，可以计算平均分。 对于缺考的两位学生，函数 AVERAGEA 将认为其成绩是 0，如图 4.3 所示。图 4.3 计算整体的平均分（2）在单元格 E11 中输入函数表达式“=AVERAGE(B2:B11)”，可以参考考试学生 的平均分。对于没有成绩的学生，AVERAGE 将自动忽略，计算结果如图 4.4 所示。图 4.4 计算参加考试学生的平均分【应用说明】在实际应用中，函数 AVERAGE 除了可以统计单个工作表中数值的平均 值，还可以计算多个工作表数据的平均值。 COUNT 和 COUNTA 函数：统计单元

6、格个数【功能说明】COUNT 函数的功能是统计数值单元格的个数。COUNTA 函数的功能是 统计列表中非空单元格的个数。【语法表达式】COUNT(value1，value2，)；COUNTA(value1，value2，)【参数说明】 在 COUNT 函数中，参数 value1，value2，：表示包含各种数据类型的单元格。 函数只统计包含数字的单元格的个数。 在 COUNTA 函数中，value1，value2，：表示包含各种数据类型的单元格。【使用说明】 在函数 COUNTA 中，参数的值可以是任何类型的数据。例如，数字、文本、逻 辑值、错误值等等。 在函数 COUNTA 中，如果参数是数

7、组或引用，则只计算其中包含数字的单元格 的个数。【实际应用】 某学习小组统计了小组的文化课和体育课的成绩情况。由于文化课和体育课的成绩信息统计不同，需要用不同的统计口径，基础信息如图 4.5 所示。图 4.5 基础信息根据上面的数据表，需要通过不同的口径来统计信息，具体步骤如下：（1）使用 COUNT 函数统计信息。在单元格 B7 中输入表达式“=COUNT(B2:B5)”， 统计有文化课考试成绩同学的个数，得到的结果如图 4.6 所示。图 4.6 使用 COUNT 函数统计（2）使用 COUNTA 函数统计信息。在单元格 C7 中输入表达式“=COUNTA(C2:C5)”， 统计体育达标同学

8、的个数，如图 4.7 所示。图 4.7 使用 COUNTA 函数统计【应用说明】在上面的例子中，在 B 列中，用户需要知道的是有考试成绩的同学个数， 因此用 COUNT 统计有数值的单元格个数，而由于体育成绩不是按照数值来表达的，因此 使用 COUNTA 统计的数值是有效的。 COUNTBLANK 函数：统计空白单元格【功能说明】统计单元格区域中空白单元格的个数。【语法表达式】COUNTBLANK(range)【参数说明】参数 range 表示需要统计空白单元格的区域。【使用说明】单元格是否为空，并不是判断单元格中是否包含数据。如果单元格中包 含空格，也是非空的。【实际应用】某学校为高中三个年

9、级的各班统计分数，每个年级包括 5 个班级。统计分数的班级都 以“”标记，没有登记的班级表示没有统计分数，现在需要统计没有统计分数的班级个 数。在单元格 D8 中输入函数表达式“=COUNTBLANK(B2:D6)”，结果如图 4.8 所示。图 4.8 统计未统计分数的班级【应用说明】如果单元格中含有返回值为空文本()的公式，COUNTBLANK 函数会 将该单元格包括在内，但包含零值的单元格不包括在内。4.1.5 COUNTIF 函数：按条件统计【功能说明】统计符合条件的单元格个数。【语法表达式】COUNTIF(range，criteria)【参数说明】 range：表示需要计算单元格个数的

10、单元格区域。 criteria：表示判断条件，根据这个条件判断哪些单元格将被计算在内。【使用说明】 函数忽略空白和包含文本的单元格，但包含 0 的单元格不会忽略。 在函数 COUNTIF 中，输入判断条件时可以使用通配符：问号（?）和星号（*）。 问号（?）代表多个字符，例如：陈?，表示第一个字符是“陈”的字符串；星号（*）代表单个字符，例如：*王*，表示带“王”的字符串；表示引用星号和问 号本身，例如：*农，表示类似“*农”的字符串。【实际应用】 某公司统计了各部门的销量数据，同时公司设定了标准销量。现在需要分析各部门销量是否达标。为了统计上面不同条件的个数，具体步骤如下：（1）在单元格 A

11、13 中输入标准销量：52；（2）在单元格 B15 中输入函数“=COUNTIF(A2:B10,&$A$13)”，计算数据区域中 大于 52 的个数；（3）在单元格 B16 中输入函数“=COUNTIF(A2:B10,$A$13)”，计算数据区域中等于52 的个数；（4）在单元格 B17 中输入函数“=COUNTIF(A2:B10,=&$A$13)”，计算数据区域 大于或等于 52 的个数；（6）在单元格 B19 中输入函数“=COUNTIF(A2:B10,=&$A$13)”，计算数据区域 小于或等于 52 的个数。得到的最后结果如图 4.9 所示。图 4.9 计算结果【应用说明】COUNTI

12、F 函数可以和其他函数嵌套使用，实现复杂的数据筛选功能。4.1.6 FREQUENCY 函数：计算频率分布【功能说明】计算某个区间的数值在数据单元格列表中出现的次数。【语法表达式】FREQUENCY(data_ array，bins_ array)【参数说明】 data_ array：表示数据列表。 bins_ array：表示数值区间段。函数要统计 bins_ array 中的数值在 data_ array 中 出现的次数。如果参数 bins_ array 不包含任何值，函数返回的值与 data_ array 中 的数值个数相等。【使用说明】 函数忽略空白单元格和包含文本的单元格。 在结果区

13、域中，要多选择一个单元格，用来放多出来的数值。 如果希望以数组的形式得到结果，则需要以数组公式的形式输入。【实际应用】 某调查公司统计了不同地域的人数数据（单位是：万），现在调查公司需要统计各区间的人数频率。选择单元格 E2E7，在编辑栏中输入函数“=FREQUENCY(A2:A12,B2:B6)”，然后按 下“Ctrl+Shift+Enter”组合键，就可以得到频率分布结果，如图 4.10 所示。图 4.10 计算若数的频率分布【应用说明】FREQUENCY 函数得到的结果是数组形式，因此，该函数可以和其他数 组函数综合使用。4.1.7 HARMEAN 函数：计算调和平均值【功能说明】计算一

14、组数值的调和平均值。调和平均值与倒数的算术平均值互为倒数。【语法表达式】HARMEAN(number1，number2，)【参数说明】number1，number2，：表示要计算几何平均值的数值。使用函数时， 参数值可以是具体的数字，也可以指定单元格区域，参数个数最多可达 30 个。【使用说明】 各参数的值必须大于 0。如果有一个参数为负数，函数将返回错误值#NUM!。 各参数的值可以是数字，也可以是包含数字的名称、数组或引用。 函数在计算过程中，会忽略包括文本、逻辑值的单元格和空单元格。如果单元格 的值为 0，函数将返回错误值#NUM!。【实际应用】已知原始数据，计算调和平均值。在单元格 A

15、12 中，输入函数“=HARMEAN(A2:A9)”， 得到数据的调和平均值，结果如图 4.12 所示。图 4.12 计算结果【应用说明】根据基本的数学知识，调和平均值总小于几何平均值，而几何平均值总 小于算术平均值4.1.8 KURT 函数：计算数据集的峰值【功能说明】返回数值的峰值。峰值反映的是与正态分布相比，分布的尖锐度和平坦 度。如果峰值为正数，表示比正态分布稍尖的分布状态，此时峰值分布在平均值周围；如 果峰值为 0，表示标准的正态分布；如果峰值为负数，表示比正态分布稍平坦的分布状态， 此时峰值分散的分布在平均值周围。【语法表达式】KURT(number1，number2，)【参数说明

16、】number1，number2，：表示需要计算峰值的数值。使用函数时，参数 可以是直接输入到参数框中的数字，也可以指定单元格区域。参数个数最多可达 30 个。【使用说明】 参数可以是数值，也可以是逻辑值。 如果参数是数组或应用，则只计算其中的数值数据。 在计算过程中，忽略空单元格和文本单元格，但包含 0 值的单元格将计算在内。 如果参数个数少于 4 个，或者样本标准方差等于 0，函数将返回错误值#DIV/0！。【实际应用】 某厂商统计了近几天的人数，为了比较人数分布和正态分布的分散情况，需要计算人数的峰值。在单元格 C12 中输入公式“=KURT(A2:A12)”，求解结果如图 4.13 所

17、示。图 4.13 计算人数的峰值【应用说明】KURT 函数的正峰值表示相对尖锐的分布。负峰值表示相对平坦的分布； 如果数据少于 4 个，或样本标准偏差等于 0，函数 KURT 返回错误值 #DIV/0!。4.1.9 LARGE 函数：计算数据集中第 k 个最大值【功能说明】计算这组数值中的第 N 个最大值。例如，指定返回位置 7 的数值，也就 是按从大到小的顺序排列，返回第 7 个值。【语法表达式】LARGE(array，k)【参数说明】 array：表示给定的数值。使用函数时，参数值可以是直接输入的数值，也可以指 定单元格区域。 k：一个数值，表示需要返回的数值的位置。例如，参数 k 设为

18、6，则表示返回第 6 个最大值。【使用说明】 参数 array 必须是数值。 参数 k 必须是正整数，其值不能超过 array 中数值的个数。 函数在统计过程中，将忽略空单元格、逻辑值和包含文本的单元格。【实际应用】 某厂商对一组商品进行称重，根据中数据，需要求解该组数据重最大、第五大和最小销的重量，其中基础数据如图 4.14 所示。图 4.14 基础数据表格根据上面的计算表格，求解的具体步骤如下：（1）在单元格 D10 中输入函数表达式“=LARGE(A2:A12,1)”，计算最大销售量，如 图 4.15 所示。图 4.15 求解最大重量的数值（2）在单元格 D11 中输入函数表达式“=LA

19、RGE(A2:A12,5)”，计算第五大重量，如图 4.16 所示。图 4.16 计算第五大重量（3）在单元格 D12 中输入函数表达式“=LARGE(A2:A12,11)”，计算最小重量，如 图 4.17 所示。图 4.17 计算最小重量【应用说明】在实际应用中，LARGE 函数可以和 LOOKUP 函数嵌套使用，返回最值 数据的信息。4.1.10 MAX 和 MAXA 函数：获取最大值【功能说明】返回数值中的最大值。【语法表达式】MAX(number1，number2，)【参数说明】number1，number2，：表示数值。使用函数时，参数可以是直接输入的数值，也可以指定单元格。【使用说

20、明】 在计算过程中，忽略空白单元格和包含文本的单元格，但包含 0 的单元格被计算 在内。 如果参数的值是数组或引用，则只计算其中包含数值的单元格。 如果所有的参数值中都不包含数值，则函数返回 0。【实际应用】某公司统计了 7 个地区销量的变化。由于经济危机，各地区的销量变化都是负值，现在需要统计最小的变化数，原始数据如图 4.18 所示。图 4.18 基础数据根据上面的基础数据，具体的求解步骤如下：（1）在单元格 C10 中输入函数表达式“=MAX(B2:B8)”，计算有统计的最小变化值， 如图 4.19 所示。图 4.19 计算有统计的最小变化值（2）在单元格 C11 中输入函数表达式“=M

21、AXA(B2:B10)”，计算最小变化值，得到的结果如图 4.20 所示。图 4.20 计算最小变化值【应用说明】根据本例的数据，合适的统计口径应该是 MAX 函数，如果使用 MAXA函数，将会将没有统计的数值设置为 0。4.1.11 MEDIAN 函数：获取给定数据的中值【功能说明】返回数值的中间值。【语法表达式】MEDIAN(number1，number2，)【参数说明】number1，number2，：表示一组数值。使用函数时，参数的值可以是 一组具体的数值，也可是指定单元格区域。参数个数最多可达 255 个。【使用说明】 中值是数据按顺序排列时位于中间位置的值，不用专门对数据进行排序。

22、 数据个数为奇数时，函数返回中间位置的数值；个数为偶数时，函数返回位于中 间两个数值的平均值。【实际应用】 某厂商统计了近几天的人数，现在需要计算人数的中值。在单元格 D12 中输入函数表达式“=MEDIAN(A2:A12)”，得到人数的中值，得到的结 果如图 4.21 所示。图 4.21 计算人数的中值【应用说明】MEDIAN 函数在实际生活中应用广泛，例如，用来统计体重、销售等的 中值。4.1.12 MIN 和 MINA 函数：获取最小值【功能说明】返回数值中的最小值。【语法表达式】MIN(number1，number2，)【参数说明】number1，number2，：表示数值。使用函数时

23、，参数值可以是直接输 入到参数框的数值，也可以指定单元格。【使用说明】 函数在计算过程中，忽略空白单元格和包含文本的单元格，但包含 0 的单元格被 计算在内。 如果参数的值是数组或引用，则只计算其中包含数值的单元格。 如果所有的参数值中都不包含数值，则函数返回 0。【实际应用】 某厂商统计了各地区的厂商数量的变化数值。对于没有统计的地区，数值为“无统计”，原始数据如图 4.22 所示。图 4.22 基础数据和表格根据上面的基础数据，具体的求解步骤如下：（1）在单元格 C10 中输入函数表达式“=MIN(B2:B8)”，计算有统计的最小变化值， 如图 4.23 所示。图 4.23 计算有统计的最

24、小变化数（2）在单元格 C11 中输入函数表达式“=MINA(B2:B10)”，计算最小变化数，得到 的结果如图 4.24 所示。图 4.24 计算最小变化数【应用说明】在实际应用中，如果参数都是数值，MINA 函数 MIN 返回的结果一样。4.1.13 MODE 函数：获取众数【功能说明】返回数值中出现次数最多的数值。【语法表达式】MODE(number1，number2，)【参数说明】number1，number2，：表示数值。使用函数时，参数的值可以是直接 输入到参数框的数值，也可以指定单元格区域。【使用说明】 函数在计算过程中，忽略空白单元格和包含文本的单元格，但包含 0 值的单元格

25、计算在内。 如果数值中没有重复的数值，函数将返回错误值#N/A!。【实际应用】某小组统计了小组成员的年龄，现在需要了解年龄的众数。在单元格 D11 中输入函数 “=MODE(A2:A11)”，得到的结果如图 4.25 所示。图 4.25 计算年龄的众数【应用说明】根据函数的定义，MODE 函数用于统计数值出现的频率。4.1.14 PERCENTILE 函数：获取数据中的对应百分位数值【功能说明】返回数值中第 N 个百分点所对应的数值。百分点指把升序排列的数值看 作 100 等分，每个分割点就是所谓的百分点。第 50 个百分点的值和中值相等。【语法表达式】PERCENTILE(array，k)【

26、参数说明】 array：表示数值。 k：表示指定的百分比。使用函数时，参数可以是 0 到 1 之间的任意数值。【使用说明】 参数 array 包含的数据个数在 0 到 8191 之间，不能等于 0，否则函数将返回错误 值#NUM!。 如果参数 k 不是 1/(n-1)的倍数，函数则会插入中间值，确定百分比所对应的值。【实际应用】某公司统计了员工的销量数值，根据员工的表现，公司需要给前 35%的员工设定为“优 秀”。现在需要确定优秀的销量数值。在单元格 B15 中输入表达式“=PERCENTILE(B2:B11,B14)”，结果如图 4.26 所示。图 4.26 计算结果【应用说明】PERCEN

27、TILE 函数和 Rank 函数的概念结合在一起，可以返回各种排名 或者排序的数值结果。4.1.15 PERCENTRANK 函数：获取数据集中值的百分比排位【功能说明】返回指定数值在数值中的百分比排位。【语法表达式】PERCENTRANK(array，x，significance)【参数说明】 array：表示数值。使用函数时，参数可以指定单元格区域。 x：表示数值，计算参数 x 在 array 中的百分比排位。使用函数时，参数可以是直 接输入的数值，也可以指定单元格。 significance：表示希望保留的小数点后的位数。如果省略，则系统默认为小数点 后保留 3 位有效数值。【使用说明】

28、 参数 array 不能为空。 significance 的值不能小于 1，一般采用默认值。 如果 array 中没有数值 x，则函数会自动插入数值，返回插入数值的百分比排位。【实际应用】 某公司统计了该公司某部门的员工年龄，为了统计数据，公司需要了解年龄小于 29的员工比例。在单元格 B14 中输入函数“=PERCENTRANK(B2:B11,A14)”，结果如图 4.27 所示。图 4.27 计算百分比排位【应用说明】根据实际的数据情况，PERCENTRANK 函数还可计算分数的集中程度。4.1.16 PERMUT 函数：计算排列数【功能说明】从给定数目的对象集合中，抽取一定数目的对象，计

29、算这些抽取对象的 排列数。排列有内部次序，例如，2，3和3，2是两种不同的排列。【语法表达式】PERMUT(number，number_ chosen)【参数说明】 number：表示集合中对象的总数目。参数可以直接输入，也可以指定单元格。 number_ chosen：表示从集合中抽取的对象数。【使用说明】 使用函数时，两参数的值必须是正整数。如果参数是小数，函数会自动截尾取整。 如果是负数，函数将返回错误值#NUM!。 number_ chosen 的值不能超过 number 的值。 函数计算的结果是有次序的排列数，不是组合数。【实际应用】某班共有 10 人，现在需要从中选择 2 人，分别

30、担任班长和副班长，共有多少种情况？ 在单元格 B4 中输入函数“=PERMUT(B1,B2)”，得到排列总数 90，如图 4.28 所示。图 4.28 计算排列总数【应用说明】根据参数的定义，只要了解数值总数和选取的对象数，就可以使用PERMUT 函数返回有序的组合数。4.1.17 QUARTILE 函数：获取数据集的四分位数【功能说明】返回一组数值的四分位数。也就是把一组数值分为 100 等份，分别返回数值的最小值、第 25 个百分点的值、中值、第 75 个百分点的值和最大值。【语法表达式】QUARTILE(array，quart)【参数说明】 array：表示一组数值。使用函数时，参数可以指定单元格区域。 quart：一个数值，代表需要返回哪一个四分位数。值为 0 时，表示返回最小值； 值为 1 时，表示返回第一个四分位数，也就是第 25 个百分点的数值；值为 2 时，表示返回第二个四分位数，也就是中值；值为 3 时，表示返回第三个四分位数，也就是第 75 个百分点的值；值为 4 时，表示返回最大值。【使用说明】 不用对数组数值进行排序。函数自动按从小到大的顺序返回各四分位数。 参数 quart