Excel公式和函数相关概率计算Word下载.docx
- 文档编号:7234628
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:23
- 大小:469.14KB
Excel公式和函数相关概率计算Word下载.docx
《Excel公式和函数相关概率计算Word下载.docx》由会员分享,可在线阅读,更多相关《Excel公式和函数相关概率计算Word下载.docx(23页珍藏版)》请在冰点文库上搜索。
参数Alpha为B3;
参数Beta为B4;
参数A为B5;
参数B为B6,如图8-3所示。
图8-3设置函数参数
其中,在该对话框中,设置的参数,应该注意如下几点:
●如果任意参数为非数值型,函数BETADIST返回错误值#VALUE!
。
●如果alpha≤0或beta≥0,函数BETADIST返回错误值#NUM!
●如果x<A、x>B或A=B,函数BETADIST返回错误值#NUM!
●如果省略A或B值,函数BETADIST使用标准beta分布的累积函数,即A=0,B=1。
单击【函数参数】对话框中的【确定】按钮,即可得出“累积函数的函数值”相对应的计算结果,其值为0.68547058;
并在编辑栏中显示计算公式,效果如图8-4所示。
图8-4计算结果
计算出的“累积函数的函数值”越大,代表样本中数据的变化越大;
反之,则代表样本中数据的变化越小。
技巧
选择所需计算结果的单元格,输入“BETADIST(C2,C3,C4,C5,C6)”公式,也可以求出累积函数的函数值。
2.BETAINV函数
BETAINV函数返回beta累积分布函数的反函数值。
即如果probability=BETADIST(x,...),则BETAINV(probability,...)=x。
beta累积分布函数可用于项目设计,如在给出期望的完成时间和变化参数后,模拟可能的完成时间。
BETAINV(probability,alpha,beta,A,B)
其中,该函数的各参数功能如下:
Probability为Beta分布的概率值,Alpha和Beta分别为分布的参数,A为数值x所属区间的可选下界,B为数值x所属区间的可选上界。
例如,选择C8单元格,在【插入函数】对话框中,选择BETAINV函数。
然后在【函数参数】对话框中,分别设置参数Probability为C2;
参数Alpha为C3;
参数Beta为C4;
参数A为C5;
参数B为C6;
即可求出计算结果为1.932762623,效果如图8-5所示。
图8-5计算概率的反函数值
其中,在【函数参数】对话框中,设置各参数时应注意以下几点:
●如果任意参数为非数值型,函数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。
3.BINOMDIST函数
在统计领域中,有一些随机事件只具有两种互斥结果的离散型随机事件,称为二项分类变量(dichotomousvariable),如对病人治疗结果是有效还是无效,某种化验结果是阳性还是阴性等。
二项分布(binomialdistribution)就是对这类只具有两种互斥结果的离散型随机事件的规律性进行描述的一种概率分布。
在Excel中,需要计算二项分布的概率分布、累积概率时0,需要利用Excel工作表中的BINOMDIST函数。
BINOMDIST函数返回一元二项式分布的概率值。
该函数适用于固定次数的独立试验,当试验的结果只包含成功或失败二种情况,且当成功的概率在实验期间固定不变。
例如,函数BINOMDIST可以计算三个婴儿中两个是男孩的概率;
或者,已知次品概率的情况下,利用函数BINOMDIST可以计算10个产品中发现2个次品的概率。
BINOMDIST(number_s,trials,probability_s,cumulative)
其中,该函数包含4个参数,各参数功能如下:
●Number_s为试验成功的次数。
●Trials为独立试验的次数。
●Probability_s为每次试验中成功的概率。
●Cumulative
该值为一逻辑值,用于确定函数的形式。
如果参数cumulative为TRUE,函数BINOMDIST返回累积分布函数,即至多number_s次成功的概率;
如果为FALSE,返回概率密度函数,即number_s次成功的概率。
例如,抛硬币的结果不是正面就是反面,故每次抛硬币为正面的概率是0.5;
求掷硬币10次中成功6次的概率为多少?
在“10次试验6次为正面的概率”所对应的单元格中,输入“=BINOMDIST(6,10,0.5,FALSE)”公式,则可求出计算结果等于0.205078125,效果如图8-6所示。
图8-6计算结果
在计算的“10次试验6次为正面的概率”结果中,计算结果的值越大,表示为正面的概率越大;
反之,表示抛出的硬币为正面的概率越小。
用户也可以在【插入函数】对话框中,选择BINOMDIST函数。
然后在【函数参数】对话框中,设置各参数的具体值,也可求出分布概率值。
其中,在该公式中,需要对各参数进行如下几点说明:
●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!
4.CHIDIST函数
CHIDIST函数返回x2分布的单尾概率。
其中,双尾测验是测了0.05和0.01两个水平上的差异,单尾概率为单尾测验只测试了0.05水平上的差异,0.05上有差异说明这种差异是不同于误差的差异,而是真实存在的差异,而在0.01上有差异说差异显著,说明这种因子对实验有极大的影响。
x2分布与x2检验相关,使用x2检验可以比较观察值和期望值。
其中,使用x2分布可以对随机变量抽样的标准偏差进行差异显著性检验的方法,可以用来衡量实测值与理论值之间的符合程度。
标准偏差是一种量度数据分布的分散程度之标准,用以衡量数据值偏离算术平均值的程度。
标准偏差越小,这些值偏离平均值就越少,反之亦然。
标准偏差的大小可通过标准偏差与平均值的倍率关系来衡量。
标准偏差公式:
S=Sqr(∑(xn-x拨)^2/(n-1))
公式中∑代表总和,xn代表一组数字中的各个数据,x拨代表x的算术平均值,^2代表二次方,Sqr代表平方根。
例如,有一组数字分别是200、50、100、200,求它们的标准偏差。
首先可以计算出:
x拨=(200+50+100+200)/4=550/4=137.5
然后,求出二次方的值:
S^2=[(200-137.5)^2+(50-137.5)^2+(100-137.5)^2+(200-137.5)^2]/(4-1)=5625
最后得出标准偏差结果:
S=Sqr(5625)=75
下面来具体介绍在Excel中,CHIDIST函数的语法结构:
CHIDIST(x,degrees_freedom)
其中,X为用来计算分布的数值。
Degrees_freedom为自由度的数值。
例如,某工人厂生产某型号的电灯泡,其寿命服从方差为6000的正态分布。
现在随机选取36个电灯泡,测出其寿命的样本方差为10000,求出电灯泡的单尾概率。
正态分布一种概率分布,正态分布是具有两个参数μ和σ2的连续型随机变量的分布,第一参数μ是遵从正态分布的随机变量的均值,第二个参数σ2是此随机变量的方差,所以正态分布记作N(μ,σ2)。
首先,在工作表中的“自由度”所对应的单元格中,输入“=B2-1”公式,计算该分布的自由度。
在数理统计过程中,根据公式(n-1)*S2/σ2(其中,n表示样本个数,S2表示样本方差,σ2表示整体方差),可在单元格B5中输入“=B2*B3/B4”公式,计算需要分布的数值,如图8-7所示。
图8-7计算分布数值图8-8计算单尾概率
然后,选择“单尾概率”所对应的单元格,输入“CHIDIST(B5,B3)”公式,即可求出“单尾概率”的值,如图8-8所示。
用户可以选择“计算分布的数值”和“单尾概率”所对应的单元格,并在【数字】组中,单击【数字格式】下拉按钮,选择【数字】项,设置计算结果保留两位小数。
在该公式中,需要对其中两个参数进行如下几点说明:
●如果任一参数为非数值型,函数CHIDIST返回错误值#VALUE!
●如果x为负数,函数CHIDIST返回错误值#NUM!
●如果degrees_freedom不是整数,将被截尾取整。
●如果degrees_freedom<
1或degrees_freedom>
10^10,则函数CHIDIST返回错误值#NUM!
5.CHIINV函数
CHIINV函数返回x2分布的单尾概率的反函数值。
如果probability=CHIDIST(x,...),则CHIINV(probability,...)=x。
使用此函数可比较观察结果与理论值,以确定初始假设是否有效。
CHIINV(probability,degrees_freedom)
其中,Probability为与x2分布相关联的概率。
Degrees_freedom为自由度。
在介绍参数Probability的过程中,需要了解该参数与置信度的关系:
Probability(与x2分布相关联的概率)=置信度/2。
所谓置信度,也叫置信水平,即在抽样对总体参数作出估计时,由于样本的随机性,其结论总是不确定的。
例如,现在对某工厂需要检测的电灯泡总体分析是否符合正态分布,已知置信度为0.02,计算单尾检测的分布数值。
在【函数参数】对话框中,设置参数Probability为B9/2;
参数Deg_freedom为B2,即可在工作表中的B8单元格中,计算出结果为57.34,如图8-9所示。
图8-9计算反单尾概率
在该【函数参数】对话框中,需要对其进行以下几点说明:
●如果任一参数为非数字型,则函数CHIINV返回错误值#VALUE!
●如果probability<0或probability>1,则函数CHIINV返回错误值#NUM!
●如果degrees_freedom<1或degrees_freedom≥10^10,函数CHIINV返回错误值#NUM!
如果已给定概率值,则CHIINV使用CHIDIST(x,degrees_freedom)=probability求解数值x。
因此,CHIINV的精度取决于CHIDIST的精度。
CHIINV使用迭代搜索技术。
6.FDIST函数
该函数返回F概率分布,使用此函数可以确定两个数据集是否存在变化程度上的不同。
FDIST(x,degrees_freedom1,degrees_freedom2)
其中,各参数功能如下:
●x为求解概率时所需的参数值。
●Degrees_freedom1为分子的自由度。
其中,自由度是在数学中能够自由取值的变量个数,自由度通常用于抽样分布中。
●Degrees_freedom2为分母的自由度。
例如,分析进入高校的男生、女生的考试分数,确定女生分数的变化程度是否与男生不同。
在C10单元格中,输入“=VAR(B2:
B9)”公式,计算出“男生考试的样本方差”值;
在C11单元格中,输入“=VAR(C2:
C9)”公式,计算出“女生考试的样本方差”值;
并在C12单元格中,输入“=C10/C11”公式,计算“参数数值”的值,如图8-10所示。
图8-10计算“参数数值”
VAR函数为方差计算函数,在前面章节中已经进行过详细介绍,这里不再多加说明。
选择“F检验结果”所对应的单元格,在【函数参数】对话框中,分别设置参数x为C12;
Degrees_freedom1为3;
Degrees_freedom2为3,即可计算出“F检验结果”的值,如图8-11所示。
图8-11计算“函数参数”的值
由于自由度等于样本数减去1,因为男生和女生的人数分别为4个,所以可以得到Degrees_freedom1和Degrees_freedom2分别为3。
其中,在函数的计算结果中,“F检验结果”的值越大,表示男生与女生的分数变化程度越大;
反之,则代表男生与女生的分数变化程度越小。
在计算该函数的过程中,需要注意以下几点:
●如果任何参数都为非数值型,函数FDIST返回错误值#VALUE!
●如果x为负数,函数FDIST返回错误值#NUM!
●如果degrees_freedom1或degrees_freedom2不是整数,将被截尾取整。
●如果degrees_freedom1<1或degrees_freedom1≥10^10,函数FDIST返回错误值#NUM!
●如果degrees_freedom2<1或degrees_freedom2≥10^10,函数FDIST返回错误值#NUM!
●函数FDIST的计算公式为FDIST=P(F>x),其中F为呈F分布且带有degrees_freedom1和degrees_freedom2自由度的随机变量。
7.FINV函数
返回F概率分布的逆函数值,即F分布的临界值。
如果p=FDIST(x,.),则FINV(p,.)=x。
在F检验中,可以使用F分布比较两个数据集的变化程度。
例如,可以分析美国、加拿大的收入分布,判断两个国家/地区是否有相似的收入变化程度。
FINV(probability,degrees_freedom1,degrees_freedom2)
其中,该函数中的参数含义如下:
参数robability代表累积F分布的概率值,参数Degrees_freedom1代表分子自由度,参数Degrees_freedom2代表分母自由度。
例如,运用上述男生与女生考试分数分析的实例,并给出置信度为0.01,求F概率分布的反函数值“置信区间上限”和“置信区间下限”的值。
选择“置信区间下限”所对应的单元格,并在【函数参数】对话框中,分别设置参数probability为B15/2;
参数Degrees_freedom1为3;
参数Degrees_freedom2为3,即可计算出F概率分布的反函数值,如图8-12所示。
图8-12计算F概率分布的反函数
其中,FINV函数在计算过程中的参数注意事项与FDIST函数相同。
8.FREQUENCY函数
该函数为计算数值在某个区域内的出现的频率,然后返回一个垂直数组。
FREQUENCY(data_array,bins_array)
●Data_array为一数组或对一组数值的引用,用来计算频率。
如果data_array中不包含任何数值,函数FREQUENCY返回零数组。
●Bins_array为一数组或对数组区域的引用,设定对data_array进行频率计算的分段点。
如果bins_array中不包含任何数值,函数FREQUENCY返回data_array元素的数目。
注意
由于函数FREQUENCY返回一个数组,所以它必须以数组公式的形式输入。
该函数可以用于计算不同工资段的人员分布,公司员工的年龄分布,学生成绩的分布情况等。
这里以具体示例说明其基本的用法。
例如,以计算某公司的员工年龄分布情况为例说明。
在工作表里列出了员工的年龄。
这些年龄为20、36、38、28、49、25、45和32,并分别输入到C3至C10单元格区域中。
这一列年龄就是data_array。
Bins_array是另一列用来对年龄分组的区间值。
在本例中,bins_array是指C12至C15单元格区域,分别含有值25、30、35、和40。
以数组形式输入函数FREQUENCY,就可以计算出年龄在25岁以下、26~30岁、31~35岁、36~40岁和40岁以上各区间中的数目。
选择B12至B16单元格区域,输入“=FREQUENCY(C3:
C10)”公式,然后同时按下Ctrl+Shift+Enter键,即可计算出各个分段内的人数,效果如图8-13所示。
图8-13计算结果
在本例数组公式的计算过程中,用户需注意以下几点:
●在选择了用于显示返回的分布结果的相邻单元格区域后,函数FREQUENCY应以数组公式的形式输入。
●返回的数组中的元素个数比bins_array中的元素个数多1个。
多出来的元素表示最高区间之上的数值个数。
例如,如果要为三个单元格中输入的三个数值区间计数,请务必在4个单元格中输入FREQUENCY函数获得计算结果。
多出来的单元格将返回data_array中第三个区间值以上的数值个数。
●函数FREQUENCY将忽略空白单元格和文本。
●对于返回结果为数组的公式,必须以数组公式的形式输入。
9.FTEST函数
该函数返回F检验的结果。
F检验返回的是当数组1和数组2的方差无明显差异时的概率。
可以使用此函数来判断两组数据的方差是否不同。
FTEST(array1,array2)
其中,参数Array1为第一个数组或数据区域;
参数Array2为第二个数组或数据区域。
例如,给定公立和私立学校的测试成绩,可以检验各学校间测试成绩的差别程度。
在A1至B6单元格区域中,输入公立和私立学校的学生测试成绩数据,并选择“返回上述数据集F检验结果”所对应的单元格,在【函数参数】对话框中,设置参数Array1为A2:
A6,参数Array2为B2:
B6,即可在B7单元格中,得出计算结果,如图8-14所示。
图8-14计算概率
在该函数的计算结果中,“返回上述数据集F检验结果”的值越大,表示计算的两组数据有明显的差异;
反之,则代表计算的两组数据变化不大。
在该函数的计算过程中,应注意以下几点:
●参数可以是数字,或者是包含数字的名称、数组或引用。
●如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;
但包含零值的单元格将计算在内。
●如果数组1或数组2中数据点的个数小于2个,或者数组1或数组2的方差为零,函数FTEST返回错误值#DIV/0!
10.PROB函数
该函数为返回一概率事件组中,在指定区域内的事件所对应的概率之和。
如果没有给出上限(upper_limit),则返回区间x_range内的值等于下限lower_limit的概率。
PROB(x_range,prob_range,lower_limit,upper_limit)
其中,在该函数中,各参数的功能如下:
●X_range具有各自相应概率值的x数值区域。
●Prob_range与x_range中的值相对应的一组概率值。
●Lower_limit用于计算概率的数值下界。
●Upper_limit用于计算概率的可选数值上界。
例如,某公司门口的霓虹灯门面招牌的颜色变换为3种颜色,在A1至B5单元格区域中,输入霓虹灯变换颜色及概率的相应数据信息,并在“x为2的概率”和“x在1到3之间的概率”所对应的单元格中,分别输入“=PROB(A2:
A5,B2:
B5,2)”和“=PROB(A2:
B5,1,3)”公式,即可求出计算结果,如图8-15所示。
图8-15计算概率
在该公式的计算过程中,应注意以下几点内容:
●如果prob_rang中的任意值≤0或>1,函数PROB返回错误值#NUM!
●如果prob_range中所有值之和不为1,函数PROB返回错误值#NUM!
●如果省略upper_limit,函数PROB返回值等于lower_limit时的概率。
●如果x_range和prob_range中的数据点个数不同,函数PROB返回错误值#N/A。
11.TINV函数
该函数返回作为概率和自由度函数的学生t分布的t值。
其中,T分布是以0为中心,左右对称的单峰分布;
t分布是一簇曲线,其形态变化与n(确切地说与自由度ν)大小有关。
自由度ν越小,t分布曲线越低平;
自由度ν越大,t分布曲线越接近标准正态分布曲线。
TINV(probability,degrees_freedom)
其中,Probability是对应于双尾学生t分布的概率。
Degrees_freedom是分布的自由度。
例如,选择“返回t分布的t值”所对应的单元格,在【函数参数】对话框中,设置参数Probability为B2;
参数Deg_freedom为B3,则可计算出“返回t分布的t值”,效果如图8-16所示。
图8-16计算返回t分布的t值
●如果任一参数为非数字型,则TINV返回错误值#VALUE!
●如果probability<0或probability>1,则TINV返回错误值#NUM!
●如果degrees_freedom不是整数,将被截尾取整。
●如果degrees_f
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel公式和函数 相关概率计算 Excel 公式 函数 相关 概率 计算