Excel统计函数.docx
《Excel统计函数.docx》由会员分享，可在线阅读，更多相关《Excel统计函数.docx（97页珍藏版）》请在冰豆网上搜索。
Excel统计函数
第4章统计函数
在Excel中，统计函数包含众多的函数。
从实际应用角度，统计函数可以分为描述统计函数、概率分布函数、假设检验函数和回归函数等。
这些函数分别对应着统计应用的各方面。
在本章中，将对每个函数结合具体的例子进行详细分析。
4.1描述统计函数分析
本小节中介绍的函数主要用来分析整体的数值特征，包括实际生活中最常见的最大值、最小值等参数。
计算并理解这些参数，用户就可以掌握一组数据的整体属性。
本小节将详细介绍各函数的功能和使用方法。
4.1.1AVEDEV函数：
计算平均绝对偏差
【功能说明】计算数值的平均绝对偏差。
偏差表示每个数值与平均值之间的差，平均偏差表示每个偏差绝对值的平均值。
函数主要用来衡量数据的离散程度。
【语法表达式】AVEDEV（number1，number2，…）
【参数说明】number1，number2，…：
表示需要计算平均偏差的数值。
参数可以指定单元格区域，参数个数最多是255。
【使用说明】
•函数的参数可以是数字、数字的数组、名称或引用。
•在计算过程中函数将忽略空白单元格、包含逻辑值和文本的单元格，但包含0值的单元格不会忽略。
【实际应用】
某公司对一批货物进行称重，公司选择其中的10件进行测试。
记录各货物的重量，现在需要计算平均绝对偏差。
在单元格F11中输入函数表达式“=AVEDEV（B2:
B11）”，求解的结果如图4.1所示。
图4.1计算重量的平均绝对偏差
【应用说明】在实际应用中，AVEDEV函数常用来检测产品的平均偏差，来衡量实际和标准规格之间的离散程度。
AVERAGE和AVERAGEA函数：
计算平均值
【功能说明】计算数值的平均值。
【语法表达式】AVERAGE（number1，number2，…）；AVERAGEA（value1，value2，…）
【参数说明】
•number1，number2，…：
表示数值。
使用函数时，可以是数值、指定单元格或单元格区域，个数最多为255个。
•value1，value2，…：
表示列表中各种类型的数据。
使用函数时，参数的值可以是具体的数值或者指定单元格，个数最多可达30个。
【使用说明】
•在函数AVERAGEA中，如果参数是数组，函数在计算时则忽略空单元格；如果数组中包括文本，则将其看作0。
•在函数AVERAGEA中，如果参数是逻辑值，函数把TRUE看作1，FALSE看作
0。
•AVERAGEA和AVERAGE函数的差别是：
AVERAGEA函数将文本单元格包括在内，而AVERAGE函数则忽略文本单元格。
【实际应用】
某学校统计了10名同学的考试成绩，由于有同学却考，因此成绩为“无效”。
现在学
校需要根据不同的标准统计平均分，基础数据如图4.2所示。
图4.2基础数据
根据上面的基础数据，具体求解步骤如下：
（1）在单元格E10中输入函数表达式“=AVERAGEA（B2:
B11）”，可以计算平均分。
对于缺考的两位学生，函数AVERAGEA将认为其成绩是0，如图4.3所示。
图4.3计算整体的平均分
（2）在单元格E11中输入函数表达式“=AVERAGE（B2:
B11）”，可以参考考试学生的平均分。
对于没有成绩的学生，AVERAGE将自动忽略，计算结果如图4.4所示。
图4.4计算参加考试学生的平均分
【应用说明】在实际应用中，函数AVERAGE除了可以统计单个工作表中数值的平均值，还可以计算多个工作表数据的平均值。
COUNT和COUNTA函数：
统计单元格个数
【功能说明】COUNT函数的功能是统计数值单元格的个数。
COUNTA函数的功能是统计列表中非空单元格的个数。
【语法表达式】COUNT（value1，value2，…）；COUNTA（value1，value2，…）
【参数说明】
•在COUNT函数中，参数value1，value2，…：
表示包含各种数据类型的单元格。
函数只统计包含数字的单元格的个数。
•在COUNTA函数中，value1，value2，…：
表示包含各种数据类型的单元格。
【使用说明】
•在函数COUNTA中，参数的值可以是任何类型的数据。
例如，数字、文本、逻辑值、错误值等等。
•在函数COUNTA中，如果参数是数组或引用，则只计算其中包含数字的单元格的个数。
【实际应用】某学习小组统计了小组的文化课和体育课的成绩情况。
由于文化课和体育课的成绩信
息统计不同，需要用不同的统计口径，基础信息如图4.5所示。
图4.5基础信息
根据上面的数据表，需要通过不同的口径来统计信息，具体步骤如下：
（1）使用COUNT函数统计信息。
在单元格B7中输入表达式“=COUNT（B2:
B5）”，统计有文化课考试成绩同学的个数，得到的结果如图4.6所示。
图4.6使用COUNT函数统计
（2）使用COUNTA函数统计信息。
在单元格C7中输入表达式“=COUNTA（C2:
C5）”，统计体育达标同学的个数，如图4.7所示。
图4.7使用COUNTA函数统计
【应用说明】在上面的例子中，在B列中，用户需要知道的是有考试成绩的同学个数，因此用COUNT统计有数值的单元格个数，而由于体育成绩不是按照数值来表达的，因此使用COUNTA统计的数值是有效的。
COUNTBLANK函数：
统计空白单元格
【功能说明】统计单元格区域中空白单元格的个数。
【语法表达式】COUNTBLANK（range）
【参数说明】参数range表示需要统计空白单元格的区域。
【使用说明】单元格是否为空，并不是判断单元格中是否包含数据。
如果单元格中包含空格，也是非空的。
【实际应用】
某学校为高中三个年级的各班统计分数，每个年级包括5个班级。
统计分数的班级都以“√”标记，没有登记的班级表示没有统计分数，现在需要统计没有统计分数的班级个数。
在单元格D8中输入函数表达式“=COUNTBLANK（B2:
D6）”，结果如图4.8所示。
图4.8统计未统计分数的班级
【应用说明】如果单元格中含有返回值为空文本（""）的公式，COUNTBLANK函数会将该单元格包括在内，但包含零值的单元格不包括在内。
4.1.5COUNTIF函数：
按条件统计
【功能说明】统计符合条件的单元格个数。
【语法表达式】COUNTIF（range，criteria）
【参数说明】
•range：
表示需要计算单元格个数的单元格区域。
•criteria：
表示判断条件，根据这个条件判断哪些单元格将被计算在内。
【使用说明】
•函数忽略空白和包含文本的单元格，但包含0的单元格不会忽略。
•在函数COUNTIF中，输入判断条件时可以使用通配符：
问号（?
）和星号（*）。
问号（?
）代表多个字符，例如：
陈?
?
，表示第一个字符是“陈”的字符串；星号
（*）代表单个字符，例如：
*王*，表示带“王”的字符串；~表示引用星号和问号本身，例如：
~*农，表示类似“*农”的字符串。
【实际应用】某公司统计了各部门的销量数据，同时公司设定了标准销量。
现在需要分析各部门销
量是否达标。
为了统计上面不同条件的个数，具体步骤如下：
（1）在单元格A13中输入标准销量：
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的个数；
（5）在单元格B18中输入函数“=COUNTIF（A2:
B10,">="&$A$13）”，计算数据区域大于或等于52的个数；
（6）在单元格B19中输入函数“=COUNTIF（A2:
B10,"<="&$A$13）”，计算数据区域小于或等于52的个数。
得到的最后结果如图4.9所示。
图4.9计算结果
【应用说明】COUNTIF函数可以和其他函数嵌套使用，实现复杂的数据筛选功能。
4.1.6FREQUENCY函数：
计算频率分布
【功能说明】计算某个区间的数值在数据单元格列表中出现的次数。
【语法表达式】FREQUENCY（data_array，bins_array）
【参数说明】
•data_array：
表示数据列表。
•bins_array：
表示数值区间段。
函数要统计bins_array中的数值在data_array中出现的次数。
如果参数bins_array不包含任何值，函数返回的值与data_array中的数值个数相等。
【使用说明】
•函数忽略空白单元格和包含文本的单元格。
•在结果区域中，要多选择一个单元格，用来放多出来的数值。
•如果希望以数组的形式得到结果，则需要以数组公式的形式输入。
【实际应用】某调查公司统计了不同地域的人数数据（单位是：
万），现在调查公司需要统计各区
间的人数频率。
选择单元格E2～E7，在编辑栏中输入函数“=FREQUENCY（A2:
A12,B2:
B6）”，然后按下“Ctrl+Shift+Enter”组合键，就可以得到频率分布结果，如图4.10所示。
图4.10计算若数的频率分布
【应用说明】FREQUENCY函数得到的结果是数组形式，因此，该函数可以和其他数组函数综合使用。
4.1.7HARMEAN函数：
计算调和平均值
【功能说明】计算一组数值的调和平均值。
调和平均值与倒数的算术平均值互为倒数。
【语法表达式】HARMEAN（number1，number2，…）
【参数说明】number1，number2，…：
表示要计算几何平均值的数值。
使用函数时，参数值可以是具体的数字，也可以指定单元格区域，参数个数最多可达30个。
【使用说明】
•各参数的值必须大于0。
如果有一个参数为负数，函数将返回错误值#NUM!
。
•各参数的值可以是数字，也可以是包含数字的名称、数组或引用。
•函数在计算过程中，会忽略包括文本、逻辑值的单元格和空单元格。
如果单元格的值为0，函数将返回错误值#NUM!
。
【实际应用】
已知原始数据，计算调和平均值。
在单元格A12中，输入函数“=HARMEAN（A2:
A9）”，得到数据的调和平均值，结果如图4.12所示。
图4.12计算结果
【应用说明】根据基本的数学知识，调和平均值总小于几何平均值，而几何平均值总小于算术平均值
4.1.8KURT函数：
计算数据集的峰值
【功能说明】返回数值的峰值。
峰值反映的是与正态分布相比，分布的尖锐度和平坦度。
如果峰值为正数，表示比正态分布稍尖的分布状态，此时峰值分布在平均值周围；如果峰值为0，表示标准的正态分布；如果峰值为负数，表示比正态分布稍平坦的分布状态，此时峰值分散的分布在平均值周围。
【语法表达式】KURT（number1，number2，…）
【参数说明】number1，number2，…：
表示需要计算峰值的数值。
使用函数时，参数可以是直接输入到参数框中的数字，也可以指定单元格区域。
参数个数最多可达30个。
【使用说明】
•参数可以是数值，也可以是逻辑值。
•如果参数是数组或应用，则只计算其中的数值数据。
•在计算过程中，忽略空单元格和文本单元格，但包含0值的单元格将计算在内。
•如果参数个数少于4个，或者样本标准方差等于0，函数将返回错误值#DIV/0！
。
【实际应用】某厂商统计了近几天的人数，为了比较人数分布和正态分布的分散情况，需要计算人
数的峰值。
在单元格C12中输入公式“=KURT（A2:
A12）”，求解结果如图4.13所示。
图4.13计算人数的峰值
【应用说明】KURT函数的正峰值表示相对尖锐的分布。
负峰值表示相对平坦的分布；如果数据少于4个，或样本标准偏差等于0，函数KURT返回错误值#DIV/0!
。
4.1.9LARGE函数：
计算数据集中第k个最大值
【功能说明】计算这组数值中的第N个最大值。
例如，指定返回位置7的数值，也就是按从大到小的顺序排列，返回第7个值。
【语法表达式】LARGE（array，k）
【参数说明】
•array：
表示给定的数值。
使用函数时，参数值可以是直接输入的数值，也可以指定单元格区域。
•k：
一个数值，表示需要返回的数值的位置。
例如，参数k设为6，则表示返回第6个最大值。
【使用说明】
•参数array必须是数值。
•参数k必须是正整数，其值不能超过array中数值的个数。
•函数在统计过程中，将忽略空单元格、逻辑值和包含文本的单元格。
【实际应用】某厂商对一组商品进行称重，根据中数据，需要求解该组数据重最大、第五大和最小
销的重量，其中基础数据如图4.14所示。
图4.14基础数据表格
根据上面的计算表格，求解的具体步骤如下：
（1）在单元格D10中输入函数表达式“=LARGE（A2:
A12,1）”，计算最大销售量，如图4.15所示。
图4.15求解最大重量的数值
（2）在单元格D11中输入函数表达式“=LARGE（A2:
A12,5）”，计算第五大重量，如
图4.16所示。
图4.16计算第五大重量
（3）在单元格D12中输入函数表达式“=LARGE（A2:
A12,11）”，计算最小重量，如图4.17所示。
图4.17计算最小重量
【应用说明】在实际应用中，LARGE函数可以和LOOKUP函数嵌套使用，返回最值数据的信息。
4.1.10MAX和MAXA函数：
获取最大值
【功能说明】返回数值中的最大值。
【语法表达式】MAX（number1，number2，…）
【参数说明】number1，number2，…：
表示数值。
使用函数时，参数可以是直接输入
的数值，也可以指定单元格。
【使用说明】
•在计算过程中，忽略空白单元格和包含文本的单元格，但包含0的单元格被计算在内。
•如果参数的值是数组或引用，则只计算其中包含数值的单元格。
•如果所有的参数值中都不包含数值，则函数返回0。
【实际应用】
某公司统计了7个地区销量的变化。
由于经济危机，各地区的销量变化都是负值，现
在需要统计最小的变化数，原始数据如图4.18所示。
图4.18基础数据
根据上面的基础数据，具体的求解步骤如下：
（1）在单元格C10中输入函数表达式“=MAX（B2:
B8）”，计算有统计的最小变化值，如图4.19所示。
图4.19计算有统计的最小变化值
（2）在单元格C11中输入函数表达式“=MAXA（B2:
B10）”，计算最小变化值，得到
的结果如图4.20所示。
图4.20计算最小变化值
【应用说明】根据本例的数据，合适的统计口径应该是MAX函数，如果使用MAXA
函数，将会将没有统计的数值设置为0。
4.1.11MEDIAN函数：
获取给定数据的中值
【功能说明】返回数值的中间值。
【语法表达式】MEDIAN（number1，number2，…）
【参数说明】number1，number2，…：
表示一组数值。
使用函数时，参数的值可以是一组具体的数值，也可是指定单元格区域。
参数个数最多可达255个。
【使用说明】
•中值是数据按顺序排列时位于中间位置的值，不用专门对数据进行排序。
•数据个数为奇数时，函数返回中间位置的数值；个数为偶数时，函数返回位于中间两个数值的平均值。
【实际应用】某厂商统计了近几天的人数，现在需要计算人数的中值。
在单元格D12中输入函数表达式“=MEDIAN（A2:
A12）”，得到人数的中值，得到的结果如图4.21所示。
图4.21计算人数的中值
【应用说明】MEDIAN函数在实际生活中应用广泛，例如，用来统计体重、销售等的中值。
4.1.12MIN和MINA函数：
获取最小值
【功能说明】返回数值中的最小值。
【语法表达式】MIN（number1，number2，…）
【参数说明】number1，number2，…：
表示数值。
使用函数时，参数值可以是直接输入到参数框的数值，也可以指定单元格。
【使用说明】
•函数在计算过程中，忽略空白单元格和包含文本的单元格，但包含0的单元格被计算在内。
•如果参数的值是数组或引用，则只计算其中包含数值的单元格。
•如果所有的参数值中都不包含数值，则函数返回0。
【实际应用】某厂商统计了各地区的厂商数量的变化数值。
对于没有统计的地区，数值为“无统计”，
原始数据如图4.22所示。
图4.22基础数据和表格
根据上面的基础数据，具体的求解步骤如下：
（1）在单元格C10中输入函数表达式“=MIN（B2:
B8）”，计算有统计的最小变化值，如图4.23所示。
图4.23计算有统计的最小变化数
（2）在单元格C11中输入函数表达式“=MINA（B2:
B10）”，计算最小变化数，得到的结果如图4.24所示。
图4.24计算最小变化数
【应用说明】在实际应用中，如果参数都是数值，MINA函数MIN返回的结果一样。
4.1.13MODE函数：
获取众数
【功能说明】返回数值中出现次数最多的数值。
【语法表达式】MODE（number1，number2，…）
【参数说明】number1，number2，…：
表示数值。
使用函数时，参数的值可以是直接输入到参数框的数值，也可以指定单元格区域。
【使用说明】
•函数在计算过程中，忽略空白单元格和包含文本的单元格，但包含0值的单元格计算在内。
•如果数值中没有重复的数值，函数将返回错误值#N/A!
。
【实际应用】
某小组统计了小组成员的年龄，现在需要了解年龄的众数。
在单元格D11中输入函数“=MODE（A2:
A11）”，得到的结果如图4.25所示。
图4.25计算年龄的众数
【应用说明】根据函数的定义，MODE函数用于统计数值出现的频率。
4.1.14PERCENTILE函数：
获取数据中的对应百分位数值
【功能说明】返回数值中第N个百分点所对应的数值。
百分点指把升序排列的数值看作100等分，每个分割点就是所谓的百分点。
第50个百分点的值和中值相等。
【语法表达式】PERCENTILE（array，k）
【参数说明】
•array：
表示数值。
•k：
表示指定的百分比。
使用函数时，参数可以是0到1之间的任意数值。
【使用说明】
•参数array包含的数据个数在0到8191之间，不能等于0，否则函数将返回错误值#NUM!
。
•如果参数k不是1/（n-1）的倍数，函数则会插入中间值，确定百分比所对应的值。
【实际应用】
某公司统计了员工的销量数值，根据员工的表现，公司需要给前35%的员工设定为“优秀”。
现在需要确定优秀的销量数值。
在单元格B15中输入表达式“=PERCENTILE（B2:
B11,B14）”，结果如图4.26所示。
图4.26计算结果
【应用说明】PERCENTILE函数和Rank函数的概念结合在一起，可以返回各种排名或者排序的数值结果。
4.1.15PERCENTRANK函数：
获取数据集中值的百分比排位
【功能说明】返回指定数值在数值中的百分比排位。
【语法表达式】PERCENTRANK（array，x，significance）
【参数说明】
•array：
表示数值。
使用函数时，参数可以指定单元格区域。
•x：
表示数值，计算参数x在array中的百分比排位。
使用函数时，参数可以是直接输入的数值，也可以指定单元格。
•significance：
表示希望保留的小数点后的位数。
如果省略，则系统默认为小数点后保留3位有效数值。
【使用说明】
•参数array不能为空。
•significance的值不能小于1，一般采用默认值。
•如果array中没有数值x，则函数会自动插入数值，返回插入数值的百分比排位。
【实际应用】某公司统计了该公司某部门的员工年龄，为了统计数据，公司需要了解年龄小于29
的员工比例。
在单元格B14中输入函数“=PERCENTRANK（B2:
B11,A14）”，结果如图4.27所示。
图4.27计算百分比排位
【应用说明】根据实际的数据情况，PERCENTRANK函数还可计算分数的集中程度。
4.1.16PERMUT函数：
计算排列数
【功能说明】从给定数目的对象集合中，抽取一定数目的对象，计算这些抽取对象的排列数。
排列有内部次序，例如，{2，3}和{3，2}是两种不同的排列。
【语法表达式】PERMUT（number，number_chosen）
【参数说明】
•number：
表示集合中对象的总数目。
参数可以直接输入，也可以指定单元格。
•number_chosen：
表示从集合中抽取的对象数。
【使用说明】
•使用函数时，两参数的值必须是正整数。
如果参数是小数，函数会自动截尾取整。
如果是负数，函数将返回错误值#NUM!
。
•number_chosen的值不能超过number的值。
•函数计算的结果是有次序的排列数，不是组合数。
【实际应用】
某班共有10人，现在需要从中选择2人，分别担任班长和副班长，共有多少种情况？
在单元格B4中输入函数“=PERMUT（B1,B2）”，得到排列总数90，如图4.28所示。
图4.28计算排列总数
【应用说明】根据参数的定义，只要了解数值总数和选取的对象数，就可以使用
PERMUT函数返回有序的组合数。
4.1.17QUARTILE函数：
获取数据集的四分位数
【功能说明】返回一组数值的四分位数。
也就是把一组数值分为100等份，分别返回
数值的最小值、第25个百分点的值、中值、第75个百分点的值和最大值。
【语法表达式】QUARTILE（array，quart）
【参数说明】
•array：
表示一组数值。
使用函数时，参数可以指定单元格区域。
•quart：
一个数值，代表需要返回哪一个四分位数。
值为0时，表示返回最小值；值为1时，表示返回第一个四分位数，也就是第25个百分点的数值；值为2时，
表示返回第二个四分位数，也就是中值；值为3时，表示返回第三个四分位数，
也就是第75个百分点的值；值为4时，表示返回最大值。
【使用说明】
•不用对数组数值进行排序。
函数自动按从小到大的顺序返回各四分位数。
•参数quart
升级会员 