标签：概率

Excel 应用ZTEST函数计算z检验的单尾概率值

ZTEST函数用于计算z检验的单尾概率值。对于给定的假设总体平均值μ0，ZTEST返回样本平均值大于数据集（数组）中观察平均值的概率，即观察样本平均值。ZTEST函数的语法如下。

其中参数array为用来检验μ0的数组或数据区域。μ0为被检验的值。sigma为样本总体（已知）的标准偏差，如果省略，则使用样本标准偏差。

【典型案例】已知一组数据，计算z检验的单尾概率值。基础数据如图16-45所示。

步骤1：打开例子工作簿“ZTEST.xlsx”。

步骤2：在单元格A13中输入公式“=ZTEST（A2：A11，4）”，用于计算总体平均值为4时数据集的z检验单尾概率值。

步骤3：在单元格A14中输入公式“=2*MIN（ZTEST（A2：A11，4），1-ZTEST（A2：A11，4））”，用于计算总体平均值为4时数据集的z检验双尾概率值。

步骤4：在单元格A15中输入公式“=ZTEST（A2：A11，6）”，用于计算总体平均值为6时数据集的z检验单尾概率值。

步骤5：在单元格A16中输入公式“=2*MIN（ZTEST（A2：A11，6），1-ZTEST（A2：A11，6））”，用于计算假设总体平均值为6，以上数据集的z检验双尾概率值。计算结果如图16-46所示。

图16-45　基础数据

图16-46　计算结果

【使用指南】如果array为空，函数ZTEST返回错误值“#N/A”。不省略sigma时，函数ZTEST的计算公式如下：

省略sigma时，函数ZTEST的计算公式如下：

其中，x为样本平均值AVERAGE（array）；s为样本标准偏差STDEV（array）；n为样本中的观察值个数COUNT（array）。

ZTEST表示当基础总体平均值为μ0时，样本平均值大于观察值AVERAGE（array）的概率。由于正态分布是对称的，如果AVERAGE（array）＜μ0，则ZTEST的返回值将大于0.5。

当基础总体平均值为μ0，样本平均值从μ0（沿任一方向）变化到AVERAGE（array）时，下面的Excel公式可用于计算双尾概率。

Excel 应用TTEST函数计算与学生的t检验相关的概率

TTEST函数用于返回与学生t检验相关的概率。可以使用函数TTEST判断两个样本是否可能来自两个具有相同平均值的总体。TTEST函数的语法如下。

TTEST(array1,array2,tails,type)

其中参数array1为第一个数据集，array2为第二个数据集。tails指示分布曲线的尾数。如果tails=1，函数TTEST使用单尾分布；如果tails=2，函数TTEST使用双尾分布。type为t检验的类型。如果type等于1，则检验类型为成对；如果type等于2，则检验类型为等方差双样本检验；如果type等于3，则检验类型为异方差双样本检验。

典型案例

已知两个数据集，计算与学生t检验相关的概率。基础数据如图16-43所示。

步骤1：打开例子工作簿“TTEST.xlsx”。

步骤2：在单元格A12中输入公式“=TTEST（A2：A10，B2：B10，2，1）”，用于计算对应于学生的成对t检验的概率。计算结果如图16-44所示。

图16-43　基础数据

图16-44　计算结果

使用指南

如果array1和array2的数据点个数不同，且type=1（成对），函数TTEST返回错误值“#N/A”。如果参数tails和type不是整数，将被截尾取整。如果tails或type为非数值型，函数TTEST返回错误值“#VALUE！”。如果tails不为1或2，函数TTEST返回错误值“#NUM！”。

TTEST使用array1和array2中的数据计算非负值t统计。如果tails=1，假设array1和array2为来自具有相同平均值的总体的样本，则TTEST返回t统计的较高值的概率。假设“总体平均值相同”，则当tails=2时返回的值是当tails=1时返回的值的两倍，且符合t统计的较高绝对值的概率。

Excel 应用PROB函数计算区域中的数值落在指定区间内的概率

PROB函数用于返回区域中的数值落在指定区间内的概率。如果没有给出上限（upper_limit），则该函数返回区间x_range内的值等于下限lower_limit的概率。PROB函数的语法如下。

PROB(x_range,prob_range,lower_limit,upper_limit)

其中参数x_range为具有各自相应概率值的x数值区域，prob_range为与x_range中的值相对应的一组概率值。lower_limit为用于计算概率的数值下界，upper_limit为用于计算概率的可选数值上界。

【典型案例】已知具备各自相应概率值的x数值区域，计算区域中的数值落在指定区间中的概率。基础数据如图16-41所示。

步骤1：打开例子工作簿“PROB.xlsx”。

步骤2：在单元格A7中输入公式“=PROB（A2：A5，B2：B5，2）”，用于得到x为2的概率。

步骤3：在单元格A8中输入公式“=PROB（A2：A5，B2：B5，1，3）”，用于得到x在1到3之间的概率。计算结果如图16-42所示。

图16-41　基础数据

图16-42　计算结果

【使用指南】如果prob_range中的任意值小于等于0或大于1，函数PROB返回错误值“#NUM！”。如果prob_range中所有值之和不等于1，函数PROB返回错误值“#NUM！”。如果省略upper_limit，函数PROB返回值等于lower_limit时的概率。如果x_range和prob_range中的数据点个数不同，函数PROB返回错误值“#N/A”。

Excel 应用FDIST函数计算F概率分布

FDIST函数用于返回F概率分布。使用此函数可以确定两个数据集是否存在变化程度上的不同。例如，分析进入高中的男生、女生的考试分数，确定女生分数的变化程度是否与男生不同。FDIST函数的语法如下。

FDIST(x,degrees_freedom1,degrees_freedom2)

其中参数x为参数值，degrees_freedom1为分子的自由度，degrees_freedom2为分母的自由度。

【典型案例】已知给定的参数值、分子自由度、分母自由度，计算F概率分布。基础数据如图16-31所示。

步骤1：打开例子工作簿“FDIST.xlsx”。

步骤2：在单元格A6中输入公式“=FDIST（A2，A3，A4）”，用于计算F概率分布。计算结果如图16-32所示。

【使用指南】如果任何参数都为非数值型，函数FDIST返回错误值“#VALUE！”。如果x为负数，函数FDIST返回错误值“#NUM！”。如果degrees_freedom1或degrees_freedom2不是整数，将被截尾取整。如果degrees_freedom1＜1或degrees_freedom1≥1010，函数FDIST返回错误值“#NUM！”。如果degrees_freedom2＜1或degrees_freedom2≥1010，函数FDIST返回错误值“#NUM！”。函数FDIST的计算公式为FDIST=P（F＜x），其中F为呈F分布且带有degrees_freedom1和degrees_freedom2自由度的随机变量。

图16-31　基础数据

图16-32　计算结果

Excel 应用BINOMDIST函数计算一元二项式分布的概率值

BINOMDIST函数可以返回一元二项式分布的概率值。函数BINOMDIST适用于固定次数的独立试验，当试验的结果只包含成功或失败两种情况，且当成功的概率在实验期间固定不变。例如，函数BINOMDIST可以计算三个婴儿中两个是男孩的概率。BINOMDIST函数的语法如下。

BINOMDIST(number_s,trials,probability_s,cumulative)

其中参数number_s为试验成功的次数。trials为独立试验的次数。probability_s为每次试验中成功的概率。cumulative为一逻辑值，决定函数的形式。如果cumulative为TRUE，函数BINOMDIST返回累积分布函数，即至多number_s次成功的概率；如果为FALSE，返回概率密度函数，即number_s次成功的概率。

【典型案例】已知工厂中某次产品试验的成功次数为8，独立试验次数为12，每次试验的成功概率为0.6，要计算12次试验中成功6次的概率。基础数据如图16-29所示。

步骤1：打开例子工作簿“BINOMDIST.xlsx”。

步骤2：在单元格A6中输入公式“=BINOMDIST（A2，A3，A4，FALSE）”，用于计算12次试验成功6次的概率。计算结果如图16-30所示。

【使用指南】number_s和trials将被截尾取整。如果number_s、trials或probability_s为非数值型，函数BINOMDIST返回错误值“#VALUE！”。如果number_s＜0或number_s＞trials，函数BINOMDIST返回错误值“#NUM！”。如果probability_s＜0或probability_s＞1，函数BINOMDIST返回错误值“#NUM！”。一元二项式概率密度函数的计算公式为：

式中：

等于COMBIN（n，x）。

图16-29　基础数据

图16-30　计算结果

一元二项式累积分布函数的计算公式为：

Excel 应用CHIINV函数计算χ2分布的单尾概率的反函数

CHIINV函数用于返回χ2分布单尾概率的反函数值。如果probability=CHIDIST（x，…），则CHIINV（probability，…）=x。使用此函数可比较观测结果和期望值，可确定初始假设是否有效。CHIINV函数的语法如下。

CHIINV(probability,degrees_freedom)

其中参数probability为与χ2分布相关的概率，degrees_freedom为自由度的数值。

【典型案例】给定用来计算分布的数值和自由度，计算χ2分布的单尾概率的反函数。基础数据如图16-25所示。

步骤1：打开例子工作簿“CHIINV.xlsx”。

步骤2：在单元格A5中输入公式“=CHIINV（A2，A3）”，用于计算χ2分布的单尾概率的反函数。计算结果如图16-26所示。

图16-25　基础数据

图16-26　计算结果

【使用指南】如果任一参数为非数字型，则函数CHIINV返回错误值“#VALUE！”。如果probability＜0或probability＞1，则函数CHIINV返回错误值“#NUM！”。如果degrees_freedom不是整数，将被截尾取整。如果degrees_freedom＜1或degrees_freedom≥1010，函数CHIINV返回错误值“#NUM！”。如果已给定概率值，则CHIINV使用CHIDIST（x，degrees_freedom）=probability求解数值x。因此，CHIINV的精度取决于CHIDIST的精度。CHIINV使用迭代搜索技术，如果搜索在100次迭代之后没有收敛，则函数返回错误值“#N/A”。

Excel 应用CHIDIST函数计算χ2分布的单尾概率

CHIDIST函数用于返回χ2分布的单尾概率。χ2分布与χ2检验相关。使用χ2检验可以比较观察值和期望值。例如，某项遗传学实验假设下一代植物将呈现出某一组颜色。使用此函数比较观测结果和期望值，可以确定初始假设是否有效。CHIDIST函数的语法如下。

CHIDIST(x,degrees_freedom)

其中参数x为用来计算分布的数值。degrees_freedom为自由度的数值。

【典型案例】给定用来计算分布的数值和自由度，计算χ2分布的单尾概率。基础数据如图16-23所示。

步骤1：打开例子工作簿“CHIDIST.xlsx”。

步骤2：在单元格A5中输入公式“=CHIDIST（A2，A3）”，用于计算χ2分布的单尾概率。计算结果如图16-24所示。

图16-23　基础数据

图16-24　计算结果

【使用指南】如果任一参数为非数值型，函数CHIDIST返回错误值“#VALUE！”。如果x为负数，函数CHIDIST返回错误值“#NUM！”。如果degrees_freedom不是整数，将被截尾取整。如果degrees_freedom＜1或degrees_freedom＞1010，则函数CHIDIST返回错误值“#NUM！”。函数CHIDIST按CHIDIST=P（X＞x）计算，式中X为χ2随机变量。

Excel 计算z检验的单尾概率值：ZTEST函数

ZTEST函数用于计算z检验的单尾概率值。对于给定的假设总体平均值μ0，ZTEST返回样本平均值大于数据集（数组）中观察平均值的概率，即观察样本平均值。ZTEST函数的语法如下：

ZTEST(array,μ0,sigma)

其中，array参数为用来检验μ0的数组或数据区域，μ0参数为被检验的值，sigma参数为样本总体（已知）的标准偏差，如果省略，则使用样本标准偏差。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ZTEST.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-25所示。工作表中记录了一组数据，要求根据工作表中的数据计算出z检验的概率值。具体的操作步骤如下。

图17-25　原始数据

STEP01：选中A13单元格，在编辑栏中输入公式“=ZTEST(A2:A11,4)”，用于计算总体平均值为4时数据集的z检验单尾概率值，输入完成后按“Enter”键返回计算结果，如图17-26所示。

STEP02：选中A14单元格，在编辑栏中输入公式“=2*MIN(ZTEST(A2:A11,4),1-ZTEST(A2:A11,4))”，用于计算总体平均值为4时数据集的z检验双尾概率值，输入完成后按“Enter”键返回计算结果，如图17-27所示。

STEP03：选中A15单元格，在编辑栏中输入公式“=ZTEST(A2:A11,6)”，用于计算总体平均值为6时数据集的z检验单尾概率值，输入完成后按“Enter”键返回计算结果，如图17-28所示。

图17-26　计算z检验单尾概率值（一）

图17-27　计算z检验双尾概率值（一）

STEP04：选中A16单元格，在编辑栏中输入公式“=2*MIN(ZTEST(A2:A11,6),1-ZTEST(A2:A11,6))”，用于计算总体平均值为6时数据集的z检验双尾概率值，输入完成后按“Enter”键返回计算结果，如图17-29所示。

图17-28　计算z检验单尾概率值（二）

图17-29　计算z检验双尾概率值（二）

如果array为空，函数ZTEST返回错误值“#N/A”。不省略sigma时，函数ZTEST的计算公式如下：

省略sigma时，函数ZTEST的计算公式如下：

其中，x为样本平均值AVERAGE(array)；s为样本标准偏差STDEV(array)；n为样本中的观察值个数COUNT(array)。

ZTEST表示当基础总体平均值为μ0时，样本平均值大于观察值AVERAGE(array)的概率。由于正态分布是对称的，如果AVERAGE(array)<μ0，则ZTEST的返回值将大于0.5。

当基础总体平均值为μ0，样本平均值从μ0（沿任一方向）变化到AVERAGE(array)时，下面的Excel公式可用于计算双尾概率：

=2*MIN(ZTEST(array,μ0,sigma),1-ZTEST(array,μ0,sigma))

Excel 计算t检验相关概率：TTEST函数

TTEST函数用于返回与学生t检验相关的概率。可以使用函数TTEST判断两个样本是否可能来自两个具有相同平均值的总体。TTEST函数的语法如下：

TTEST(array1,array2,tails,type)

其中，array1参数为第1个数据集，array2参数为第2个数据集，tails参数指示分布曲线的尾数，如果tails=1，函数TTEST使用单尾分布；如果tails=2，函数TTEST使用双尾分布。type参数为t检验的类型，如果type=1，则检验类型为成对；如果type=2，则检验类型为等方差双样本检验；如果type=3，则检验类型为异方差双样本检验。下面通过实例详细讲解该函数的使用方法与技巧。

打开“TTEST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-23所示。工作表中记录了两个数据集，要求根据工作表中的数据计算与学生t检验相关的概率。具体的操作步骤如下。

选中A12单元格，在编辑栏中输入公式“=TTEST(A2:A10,B2:B10,2,1)”，用于计算对应于学生的成对t检验的概率，输入完成后按“Enter”键返回计算结果，如图17-24所示。

如果array1参数和array2参数的数据点个数不同，且type=1（成对），函数TTEST返回错误值“#N/A”。参数tails和type将被截尾取整。如果tails参数或type参数为非数值型，函数TTEST返回错误值“#VALUE!”。如果tails参数不为1或2，函数TTEST返回错误值“#NUM!”。TTEST使用array1和array2中的数据计算非负值t统计。如果tails=1，假设array1参数和array2参数为来自具有相同平均值的总体的样本，则TTEST返回t统计的较高值的概率；假设“总体平均值相同”，则当tails=2时返回的值是当tails=1时返回的值的两倍且符合t统计的较高绝对值的概率。

图17-23　原始数据

图17-24　计算t检验相关概率

Excel 计算数值在指定区间内的概率：PROB函数

PROB函数用于返回区域中的数值落在指定区间内的概率。如果没有给出上限（upper_limit），则返回区间x_range内的值等于下限lower_limit的概率。PROB函数的语法如下：

PROB(x_range,prob_range,lower_limit,upper_limit)

其中，x_range参数为具有各自相应概率值的x数值区域，prob_range参数为与x_range中的值相对应的一组概率值，lower_limit参数为用于计算概率的数值下界，upper_limit参数为用于计算概率的可选数值上界。下面通过实例详细讲解该函数的使用方法与技巧。

打开“PROB函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-13所示。已知具备各自相应概率值的x数值区域，要求计算区域中的数值落在指定区间中的概率。具体的操作步骤如下。

图17-13　原始数据

STEP01：选中A7单元格，在编辑栏中输入公式“=PROB(A2:A5,B2:B5,2)”，用于计算x为2的概率，输入完成后按“Enter”键返回计算结果，如图17-14所示。

STEP02：选中A8单元格，在编辑栏中输入公式“=PROB(A2:A5,B2:B5,1,3)”，用于计算x在1到3之间的概率，输入完成后按“Enter”键返回计算结果，如图17-15所示。

图17-14　计算x为2的概率

图17-15　计算x在1到3之间的概率

如果prob_range参数中的任意值≤0或>1，函数PROB返回错误值“#NUM!”。如果prob_range参数中所有值之和不等于1，函数PROB返回错误值“#NUM!”。如果省略upper_limit参数，函数PROB返回值等于lower_limit时的概率。如果x_range参数和prob_range参数中的数据点个数不同，函数PROB返回错误值“#N/A”。