分类：函数篇

Excel 应用FTEST函数计算F检验的结果

FTEST函数用于计算F检验的结果。F检验返回的是当数组1和数组2的方差无明显差异时的单尾概率。可以使用FTEST函数来判断两个样本的方差是否不同。例如，给定几个不同学校的测试成绩，可以检验学校间测试成绩的差别程度。FTEST函数的语法如下。

FTEST(array1,array2)

其中参数array1为第一个数组或数据区域，array2为第二个数组或数据区域。

【典型案例】已知在两个数据区域中，给定了两个不同学校不同科目在某一测试中成绩达到优秀分数线的学生数目，计算学校间测试成绩的差别程度。基础数据如图16-37所示。

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

步骤2：在单元格A8中输入公式“=FTEST（A2：A6，B2：B6）”，用于计算总体平均值的置信区间。计算结果如图16-38所示。

图16-37　基础数据

图16-38　计算结果

【使用指南】参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果数组1或数组2中数据点的个数小于2个，或者数组1或数组2的方差为零，函数FTEST返回错误值“#DIV/0！”。

Excel 应用FREQUENCY函数计算以垂直数组的形式返回频率分布

FREQUENCY函数用于计算一个值，可以使用该值构建总体平均值的置信区间。FREQUENCY函数的语法如下。

FREQUENCY(data_array,bins_array)

其中参数data_array是一个数组或对一组数值的引用，要为它计算频率。如果data_array中不包含任何数值，函数FREQUENCY将返回一个零数组。bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回的值与data_array中的元素个数相等。

【典型案例】已知在单元格区域中的若干分数，以及区间分割点，计算各区间内的分数个数。基础数据如图16-35所示。

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

步骤2：在单元格A12中输入公式“=FREQUENCY（A2：A10，B2：B4）”，然后选中“A12：A15”单元格，按F2键，再按“Ctrl+Shift+Enter”组合键，用于确定各区间的数值个数。计算结果如图16-36所示。

图16-35　基础数据

图16-36　计算结果

【使用指南】在选择了用于显示返回的分布结果的相邻单元格区域后，函数FREQUENCY应以数组公式的形式输入。返回的数组中的元素个数比bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。例如，如果要为三个单元格中输入的三个数值区间计数，请务必在四个单元格中输入FREQUENCY函数获得计算结果。多出来的单元格将返回data_array中第三个区间值以上的数值个数。函数FREQUENCY将忽略空白单元格和文本。对于返回结果为数组的公式，必须以数组公式的形式输入。

Excel 应用FINV函数计算F概率分布的反函数值

FINV函数用于计算F概率分布的反函数值。如果p=FDIST（x，…），则FINV（p，…）=x。在F检验中，可以使用F分布比较两个数据集的变化程度。例如，可以分析前半年和后半年的收入分布，判断两段时间是否有相似的收入变化程度。FINV函数的语法如下。

FINV(probability,degrees_freedom1,degrees_freedom2)

其中参数alpha是用于计算置信度的显著水平参数。置信度等于100×（1-alpha）%，也就是说，如果alpha为0.05，则置信度为95%。standard_dev为数据区域的总体标准偏差，假设为已知。size为样本容量。

【典型案例】已知与F累积分布相关的概率值、分子自由度和分母自由度，计算这些条件下F概率分布的反函数值。基础数据如图16-33所示。

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

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

图16-33　基础数据

图16-34　计算结果

【使用指南】如果任何参数都为非数值型，则函数FINV返回错误值“#VALUE！”。如果probability＜0或probability>1，函数FINV返回错误值“#NUM！”。如果degrees_freedom1或degrees_freedom2不是整数，将被截尾取整。如果degrees_freedom1＜1或degrees_freedom1≥1010，函数FINV返回错误值“#NUM！”；如果degrees_freedom2＜1或degrees_freedom2≥1010，函数FINV返回错误值“#NUM！”。函数FINV可用于返回F分布的临界值。例如，ANOVA计算的结果常常包括F统计值、F概率和显著水平参数为0.05的F临界值等数据。若要返回F的临界值，可用显著水平参数作为函数FINV的probability参数。

如果已给定概率值，则FINV使用FDIST（x，degrees_freedom1，degrees_freedom2）=probability求解数值x。因此，FINV的精度取决于FDIST的精度。FINV使用迭代搜索技术，如果搜索在100次迭代之后没有收敛，则函数返回错误值“#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 应用CHITEST函数计算独立性检验值

CHITEST函数用于计算独立性检验值。函数CHITEST返回（χ2）分布的统计值及相应的自由度。可以使用（χ2）检验值确定假设值是否被实验所证实。CHITEST函数的语法如下。

CHITEST(actual_range,expected_range)

其中参数actual_range为包含观察值的数据区域，将对期望值作检验。expected_range为包含行列汇总的乘积与总计值之比率的数据区域。

【典型案例】某班统计本班中男生与女生去某地旅游的意向，已知统计的实际数值与期望数值，计算相关性检验值。基础数据如图16-27所示。

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

步骤2：在单元格A10中输入公式“=CHITEST（A2：B4，A6：B8）”，用于计算总体平均值的置信区间。计算结果如图16-28所示。

【使用指南】1）如果actual_range和expected_range数据点的个数不同，则函数CHITEST返回错误值“#N/A”。χ2检验首先使用下面的公式计算χ2统计：

检验

式中：

Aij为第i行、第j列的实际频率。

Eij为第i行、第j列的期望频率。

图16-27　基础数据

图16-28　计算结果

2）从公式中可看出，χ2总是正数或0，且为0的条件是：对于每个i和j，如果Aij=Eij。

3）CHITEST返回在独立的假设条件下意外获得特定情况的概率，即χ2统计值至少与由上面的公式计算出的值一样大的情况。在计算此概率时，CHITEST使用具有相应自由度df的个数的χ2分布。如果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”。

4）当Eij的值不太小时，使用CHITEST最合适。某些统计人员建议每个Eij应该大于等于5。

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 应用BETAINV函数计算指定Beta分布的累积分布函数的反函数

BETAINV函数用于返回指定的Beta分布累积Beta分布的概率密度函数的反函数值。即，如果probability=BETADIST（x，…），则BETAINV（probability，…）=x。Beta分布函数可用于项目设计，在给定期望的完成时间和变化参数后，模拟可能的完成时间。BETAINV函数的语法如下。

BETAINV(probability,alpha,beta,A,B)

其中参数probability为Beta分布的概率值。alpha与beta为分布参数。A为数值x所属区间的可选下界，B为数值x所属区间的可选上界。

【典型案例】已知Beta分布的相关参数，返回指定Beta分布的累积分布函数的反函数。基础数据如图16-21所示。

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

步骤2：在单元格A8中输入公式“=BETAINV（A2，A3，A4，A5，A6）”，用于计算指定Beta分布的累积分布函数的反函数。计算结果如图16-22所示。

图16-21　基础数据

图16-22　计算结果

【使用指南】如果任意参数为非数值型，函数BETAINV返回错误值“#VALUE！”；如果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”。

Excel 应用BETADIST函数计算Beta累积分布函数

BETADIST函数返回累积Beta分布的概率密度函数。累积Beta分布函数通常用于研究样本中一定部分的变化情况。例如，人们一天中看电视的时间比率。BETADIST函数的语法如下。

BETADIST(x,alpha,beta,A,B)

其中参数x为用来进行函数计算的值，居于可选性上下界（A和B）之间。alpha与beta为分布参数。A为数值x所属区间的可选下界，B为数值x所属区间的可选上界。

【典型案例】已知Beta分布的相关参数，计算Beta累积分布的函数。基础数据如图16-19所示。

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

步骤2：在单元格A8中输入公式“=BETADIST（A2，A3，A4，A5，A6）”，用于计算Beta累积分布的函数。计算结果如图16-20所示。

图16-19　基础数据

图16-20　计算结果

【使用指南】如果任意参数为非数值型，函数BETADIST返回错误值“#VALUE！”。如果alpha≤0或beta≤0，函数BETADIST返回错误值“#NUM！”。如果x＜A、x＞B或A=B，函数BETADIST返回错误值“#NUM！”。如果省略A或B值，函数BETADIST使用标准Beta分布的累积函数，即A=0，B=1。