Excel统计函数.docx
- 文档编号:23299240
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:97
- 大小:4.17MB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 统计 函数