excel函数大全统计部分一.docx
《excel函数大全统计部分一.docx》由会员分享,可在线阅读,更多相关《excel函数大全统计部分一.docx(53页珍藏版)》请在冰豆网上搜索。
excel函数大全统计部分一
一、AVEDEV
请参阅
返回一组数据与其均值的绝对偏差的平均值,ADEDEV用于评测这组数据的离散度。
语法
AVEDEV(number1,number2,...)
Number1,number2,... 用于计算绝对偏差平均值的一组参数,参数的个数可以有1到30个,可以用单一数组(即对数组区域的引用)代替用逗号分隔的参数。
说明
∙参数必须是数字,或者是包含数字的名称、数组或引用。
∙如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。
∙平均偏差的公式为:
输入数据所使用的计量单位将会影响函数AVEDEV的计算结果。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
7
8
A
数据
4
5
6
7
5
4
3
公式
说明(结果)
=AVEDEV(A2:
A8)
上面一组数据与其均值的绝对偏差的平均值(1.020408)
二、AVERAGE
请参阅
返回参数的平均值(算术平均值)。
语法
AVERAGE(number1,number2,...)
Number1,number2,... 为需要计算平均值的1到30个参数。
说明
∙参数可以是数字,或者是包含数字的名称、数组或引用。
∙如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。
提示
当对单元格中的数值求平均值时,应牢记空白单元格与含零值单元格的区别,尤其在“选项”对话框中的“视图”选项卡上已经清除了“零值”复选框的条件下,空白单元格不计算在内,但计算零值。
若要查看“选项”对话框,单击“工具”菜单中的“选项”。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
A
数据
10
7
9
27
2
公式
说明(结果)
=AVERAGE(A2:
A6)
上面数字的平均值(11)
=AVERAGE(A2:
A6,5)
上面数字与5的平均值(10)
三、AVERAGEA
请参阅
计算参数列表中数值的平均值(算数平均值)。
不仅数字,而且文本和逻辑值(如TRUE和FALSE)也将计算在内。
语法
AVERAGEA(value1,value2,...)
Value1,value2,... 为需要计算平均值的1到30个单元格、单元格区域或数值。
说明
∙参数必须为数值、名称、数组或引用。
∙包含文本的数组或引用参数将作为0(零)计算。
空文本("")也作为0(零)计算。
如果在平均值的计算中不能包含文本值,请使用函数AVERAGE。
∙包含TRUE的参数作为1计算;包含FALSE的参数作为0计算。
提示
当对单元格中的数值求平均值时,应牢记空白单元格与含零值单元格的区别,尤其在“选项”对话框中的“视图”选项卡上已经清除了“零值”复选框的条件下,空白单元格不计算在内,但计算零值。
若要查看“选项”对话框,请单击“工具”菜单中的“选项”。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
7
A
数据
10
7
9
2
不可用
公式
说明(结果)
=AVERAGEA(A2:
A6)
上面的数字与文本“不可用”的平均值。
在计算中使用了包含文本“不可用”的单元格。
(5.6)
=AVERAGEA(A2:
A5,A7)
上面的数据和空白单元格的平均值。
(7)
四、BETADIST
请参阅
返回Beta累积分布函数。
Beta累积分布函数通常用于研究样本中一定部分的变化情况。
例如,人们一天中看电视的时间比率。
语法
BETADIST(x,alpha,beta,A,B)
X 用来进行函数计算的值,居于可选性上下界(A和B)之间。
Alpha 分布参数。
Beta 分布参数。
A 数值x所属区间的可选下界。
B 数值x所属区间的可选上界。
说明
∙如果任意参数为非数值型,函数BETADIST返回错误值#VALVE!
。
∙如果alpha≤0或beta≤0,函数BETADIST返回错误值#NUM!
。
∙如果xB或A=B,函数BETADIST返回错误值#NUM!
。
∙如果省略A或B值,函数BETADIST使用标准beta分布的累积函数,即A=0,B=1。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
A
B
Data
说明
2
进行函数计算的参数
8
分布参数
10
分布参数
1
下界
3
上界
公式
说明(结果)
=BETADIST(A2,A3,A4,A5,A6)
上述参数的Beta分布累积函数的函数值(0.685470581)
五、BETAINV
请参阅
返回指定的beta分布累积分布函数的反函数值。
即,如果probability=BETADIST(x,...),则BETAINV(probability,...)=x。
beta分布函数可用于项目设计,在给定期望的完成时间和变化参数后,模拟可能的完成时间。
语法
BETAINV(probability,alpha,beta,A,B)
Probability Beta分布的概率值。
Alpha 分布参数。
Beta 分布参数。
A 数值x所属区间的可选下界。
B 数值x所属区间的可选上界。
说明
∙如果任意参数为非数值型,函数BETAINV返回错误值#VALVE!
。
∙如果alpha≤0或beta≤0,函数BETAINV返回错误值#NUM!
。
∙如果probability≤0或probability>1,函数BETAINV返回错误值#NUM!
。
∙如果省略A或B值,函数BETAINV使用标准的累积beta分布,即A=0,B=1。
如果已给定概率值,则BETAINV使用BETADIST(x,alpha,beta,A,B)=probability求解数值x。
因此,BETAINV的精度取决于BETADIST的精度。
BETAINV使用迭代搜索技术。
如果搜索在100次迭代之后没有收敛,则函数返回错误值#N/A。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
A
B
数据
说明
0.685470581
beta分布的概率值
8
分布参数
10
分布参数
1
下界
3
上界
公式
说明(结果)
=BETAINV(A2,A3,A4,A5,A6)
上述参数的beta分布累积函数的反函数值
(2)
六、BINOMDIST
请参阅
返回一元二项式分布的概率值。
函数BINOMDIST适用于固定次数的独立试验,当试验的结果只包含成功或失败二种情况,且当成功的概率在实验期间固定不变。
例如,函数BINOMDIST可以计算三个婴儿中两个是男孩的概率。
语法
BINOMDIST(number_s,trials,probability_s,cumulative)
Number_s 为试验成功的次数。
Trials 为独立试验的次数。
Probability_s 为每次试验中成功的概率。
Cumulative 为一逻辑值,用于确定函数的形式。
如果cumulative为TRUE,函数BINOMDIST返回累积分布函数,即至多number_s次成功的概率;如果为FALSE,返回概率密度函数,即number_s次成功的概率。
说明
∙Number_s和trials将被截尾取整。
∙如果number_s、trials或probability_s为非数值型,函数BINOMDIST返回错误值#VALVE!
。
∙如果number_s<0或number_s>trials,函数BINOMDIST返回错误值#NUM!
。
∙如果probability_s<0或probability_s>1,函数BINOMDIST返回错误值#NUM!
。
∙一元二项式概率密度函数的计算公式为:
式中:
等于COMBIN(n,x)。
一元二项式累积分布函数的计算公式为:
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
A
B
数据
说明
6
试验成功次数
10
独立试验次数
0.5
每次试验的成功概率
公式
说明(结果)
=BINOMDIST(A2,A3,A4,FALSE)
10次试验成功6次的概率(0.205078)
七、CHIDIST
请参阅
返回γ2分布的单尾概率。
c2分布与c2检验相关。
使用c2检验可以比较观察值和期望值。
例如,某项遗传学实验假设下一代植物将呈现出某一组颜色。
使用此函数比较观测结果和期望值,可以确定初始假设是否有效。
语法
CHIDIST(x,degrees_freedom)
X 为用来计算分布的数值。
Degrees_freedom 自由度。
说明
∙如果任一参数为非数值型,函数CHIDIST返回错误值#VALUE!
。
∙如果x为负数,函数CHIDIST返回错误值#NUM!
。
∙如果degrees_freedom不是整数,将被截尾取整。
∙如果degrees_freedom<1或degrees_freedom≥10^10,函数CHIDIST返回错误值#NUM!
。
∙函数CHIDIST按CHIDTST=P(X>x)计算,式中X为c2随机变量。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
A
B
数据
说明
18.307
用来计算分布的数值
10
自由度
公式
说明(结果)
=CHIDIST(A2,A3)
满足上述条件的γ2分布的单尾概率(0.050001)
八、CHIINV
请参阅
返回γ2分布单尾概率的反函数值。
如果probability=CHIDIST(x,...),则CHIINV(probability,...)=x。
使用此函数可比较观测结果和期望值,可确定初始假设是否有效。
语法
CHIINV(probability,degrees_freedom)
Probability 为γ2分布的单尾概率。
Degrees_freedom 自由度。
说明
∙如果任一参数为非数字型,则函数CHIINV返回错误值#VALUE!
。
∙如果probability<0或probability>1,则函数CHIINV返回错误值#NUM!
。
∙如果degrees_freedom不是整数,将被截尾取整。
∙如果degrees_freedom<1或degrees_freedom≥10^10,函数CHIINV返回错误值#NUM!
。
如果已给定概率值,则CHIINV使用CHIDIST(x,degrees_freedom)=probability求解数值x。
因此,CHIINV的精度取决于CHIDIST的精度。
CHIINV使用迭代搜索技术。
如果搜索在100次迭代之后没有收敛,则函数返回错误值#N/A。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
A
B
数据
说明
0.05
γ2分布的单尾概率
10
自由度
公式
说明(结果)
=CHIINV(A2,A3)
γ2分布的单尾概率的反函数值(18.30703)
九、CHITEST
请参阅
返回独立性检验值。
函数CHITEST返回(c2)分布的统计值及相应的自由度。
可以使用(c2)检验确定假设值是否被实验所证实。
语法
CHITEST(actual_range,expected_range)
Actual_range 为包含观察值的数据区域,将和期望值作比较。
Expected_range 为包含行列汇总的乘积与总计值之比率的数据区域。
说明
∙如果actual_range和expected_range数据点的个数不同,则函数CHITEST返回错误值#N/A。
∙c2检验首先使用下面的公式计算c2统计:
其中:
Aij=第i行、第j列的实际频率
Eij=第i行、第j列的期望频率
r=行数
c=列数
∙c2的低值是独立的指示器。
从公式中可看出,c2总是正数或0,且为0的条件是:
对于每个i和j,如果Aij=Eij。
∙CHITEST返回在独立的假设条件下意外获得特定情况的概率,即c2统计值至少和由上面的公式计算出的值一样大的情况。
在计算此概率时,CHITEST使用具有相应自由度df的个数的c2分布。
如果r>1且c>1,那么df=(r-1)(c-1)。
如果r=1且c>1,那么df=c-1。
或者如果r>1且c=1,则df=r-1。
不允许出现r=c=1并且返回#N/A。
∙当Eij's不太小时,使用CHITEST最合适。
某些统计人员建议每个Eij应该大于等于5。
示例
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
5
6
7
8
A
B
C
男士(实际数)
女士(实际数)
说明
58
35
同意
11
25
中立
10
23
不同意
男士(期望数)
女士(期望数)
说明
45.35
47.65
同意
17.56
18.44
中立
16.09
16.91
不同意
公式
说明(结果)
=CHITEST(A2:
B4,A6:
B8)
上述数据的c2统计值为16.16957,自由度为2(0.000308)
十、CONFIDENCE
请参阅
返回一个值,您可使用该值构建总体平均值的置信区间。
置信区间为一个值区域。
样本平均值x位于该区域的中间,区域范围为x±CONFIDENCE。
例如,如果通过邮购的方式定购产品,其交付时间的样本平均值为x,则总体平均值的区域范围为x±CONFIDENCE。
对于任何包含在本区域中的总体平均值μ0,从μ0到x,获取样本平均值的概率大于alpha;对于任何未包含在本区域中的总体平均值μ0,从μ0到x,获取样本平均值的概率小于alpha。
换句话说,假设使用x、standard_dev和size构建一个双尾检验,假设的显著性水平为alpha,总体平均值为μ0。
如果μ0包含在置信区间中,则不能拒绝该假设;如果μ0未包含在置信区间中,则将拒绝该假设。
置信区间不允许进行概率为1–alpha的推断,此时下一份包裹的交付时间将肯定位于置信区间内。
语法
CONFIDENCE(alpha,standard_dev,size)
Alpha 是用于计算置信度的显著水平参数。
置信度等于100*(1-alpha)%,亦即,如果alpha为0.05,则置信度为95%。
Standard_dev 数据区域的总体标准偏差,假设为已知。
Size 样本容量。
说明
∙如果任意参数为非数值型,函数CONFIDENCE返回错误值#VALUE!
。
∙如果alpha≤0或alpha≥1,函数CONFIDENCE返回错误值#NUM!
。
∙如果standard_dev≤0,函数CONFIDENCE返回错误值#NUM!
。
∙如果size不是整数,将被截尾取整。
∙如果size<1,函数CONFIDENCE返回错误值#NUM!
。
∙如果假设alpha等于0.05,则需要计算等于(1-alpha)或95%的标准正态分布曲线之下的面积。
其面积值为±1.96。
因此置信区间为:
示例
假设样本取自50名乘车上班的旅客,他们花在路上的平均时间为30分钟,总体标准偏差为2.5分钟。
假设alpha=.05,计算CONFIDENCE(.05,2.5,50)的返回值为0.69291。
那么,相应的置信区间为30±0.69291=约为[29.3,30.7]。
对于任何包含在本区间中的总体平均值μ0,从μ0到30,获取样本平均值的概率大于0.05。
同样地,对于任何未包含在本区间中的总体平均值μ0,从μ0到30,获取样本平均值的概率小于0.05。
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
从帮助中选取示例。
3.按Ctrl+C。
4.在工作表中,选中单元格A1,再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换,请按Ctrl+`(重音符),或在“工具”菜单上,指向“公式审核”,再单击“公式审核模式”。
1
2
3
4
A
B
数据
说明
.05
显著水平参数
2.5
总体标准偏差
50
样本容量
公式
说明(结果)
=CONFIDENCE(A2,A3,A4)
总体平均值的置信区间。
换句话说,花在上班路上的基础总体平均值的置信区间为30±0.692951分钟,或29.3到30.7分钟。
(0.692951)
十一、CORREL
请参阅
返回单元格区域array1和array2之间的相关系数。
使用相关系数可以确定两种属性之间的关系。
例如,可以检测某地的平均温度和空调使用情况之间的关系。
语法
CORREL(array1,array2)
Array1 第一组数值单元格区域。