分类：统计函数应用技巧

Excel中的统计函数主要用于对数据区域进行各种分类统计与分析。统计函数包括了许多统计学领域的函数，具体包括平均值函数、Beta分布函数、概率函数、单元格数量计算函数、指数与对数函数、最大值与最小值函数、标准偏差函数、方差函数、正态累积分布函数、数据集相关函数、Pearson乘积矩函数、t分布函数等。本章将通过实例介绍统计函数的基本语法、参数用法以及在实际中的应用。

Excel 应用COUNT函数计算参数列表中数字的个数

COUNT函数用于计算返回包含数字的单元格的个数以及返回参数列表中的数字个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。COUNT函数的语法如下。

COUNT(value1,value2,...)

其中参数value1，value2，…是可以包含或引用各种类型数据的1到255个参数，但只有数字类型的数据才计算在内。

典型案例

已知一组数据，计算数据中包含数字的单元格的个数以及返回参数列表中的数字个数。基础数据如图16-47所示。

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

步骤2：在单元格A10中输入公式“=COUNT（A2：A8）”，用于计算上列数据中包含数字的单元格的个数。

步骤3：在单元格A11中输入公式“=COUNT（A5：A8）”，用于计算上列数据的最后4行中包含数字的单元格的个数。

步骤4：在单元格A12中输入公式“=COUNT（A2：A8，2）”，用于计算上列数据中包含数字的单元格以及包含数值2的单元格的个数。计算结果如图16-48所示。

图16-47　基础数据

图16-48　计算结果

使用指南

数字参数、日期参数或者代表数字的文本参数被计算在内。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为错误值或不能转换为数字的文本，将被忽略；如果参数是一个数组或引用，则只计算其中的数字。

数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果要统计逻辑值、文本或错误值，则需要使用COUNTA函数。

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

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

其中参数array为用来检验μ₀的数组或数据区域。μ₀为被检验的值。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表示当基础总体平均值为μ₀时，样本平均值大于观察值AVERAGE（array）的概率。由于正态分布是对称的，如果AVERAGE（array）＜μ₀，则ZTEST的返回值将大于0.5。

当基础总体平均值为μ₀，样本平均值从μ₀（沿任一方向）变化到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 应用HYPGEOMDIST函数计算超几何分布

HYPGEOMDIST函数用于计算超几何分布。给定样本容量、样本总体容量和样本总体中成功的次数，函数HYPGEOMDIST返回样本取得给定成功次数的概率。使用函数HYPGEOMDIST可以解决有限总体的问题，其中每个观察值或者为成功或者为失败，且给定样本容量的每一个子集有相等的发生概率。HYPGEOMDIST函数的语法如下。

HYPGEOMDIST(sample_s,number_sample,population_s,number_population)

其中参数sample_s为样本中成功的次数，number_sample为样本容量，population_s为样本总体中成功的次数，number_population为样本总体的容量。

【背景知识】超几何分布的计算公式如下。

式中：

x=sample_s

n=number_sample

M=population_s

N=number_population

【典型案例】已知样本中成功的次数、样本容量、样本总体中成功的次数、样本总体的容量，计算样本和样本总体的超几何分布。基础数据如图16-39所示。

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

步骤2：在单元格A7中输入公式“=HYPGEOMDIST（A2，A3，A4，A5）”，用于计算总体平均值的置信区间。计算结果如图16-40所示。

图16-39　基础数据

图16-40　计算结果

【使用指南】所有参数将被截尾取整。如果任一参数为非数值型，函数HYPGEOMDIST返回错误值“#VALUE！”。如果sample_s小于0或sample_s大于number_sample和population_s中的较小值，函数HYPGEOMDIST返回错误值“#NUM！”。如果sample_s小于0或（number_sample-number_population+population_s）中的较大值，函数HYPGEOMDIST返回错误值“#NUM！”。如果number_sample≤0或number_sample＞number_population，函数HYPGEOMDIST返回错误值“#NUM！”。如果population_s≤0或population_s＞number_population，函数HYPGEOMDIST返回错误值“#NUM！”。如果number_population≤0，函数HYPGEOMDIST返回错误值“#NUM！”。

函数HYPGEOMDIST用于在有限样本总体中进行不退回抽样的概率计算。

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≥10¹⁰，函数FINV返回错误值“#NUM！”；如果degrees_freedom2＜1或degrees_freedom2≥10¹⁰，函数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≥10¹⁰，函数FDIST返回错误值“#NUM！”。如果degrees_freedom2＜1或degrees_freedom2≥10¹⁰，函数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　计算结果

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