分类：函数篇

Excel 应用EXPONDIST函数计算指数分布

EXPONDIST函数用于返回指数分布。使用函数EXPONDIST可以建立事件之间的时间间隔模型，例如，在计算银行自动提款机支付一次现金所花费的时间时，可通过函数EXPONDIST来确定这一过程最长持续一分钟的发生概率。EXPONDIST函数的语法如下。

EXPONDIST(x,lambda,cumulative)

其中参数x为函数的值。lambda为参数值。cumulative为一逻辑值，指定指数函数的形式。如果cumulative为TRUE，函数EXPONDIST返回累积分布函数；如果cumulative为FALSE，返回概率密度函数。

典型案例

已知函数的值与参数值，试返回累积指数分布函数和概率指数分布函数。基础数据如图16-57所示。

图16-57　基础数据

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

步骤2：在单元格A5中输入公式“=EXPONDIST（A2，A3，TRUE）”，用于返回累积指数分布函数。

步骤3：在单元格A6中输入公式“=EXPONDIST（0.2，10，FALSE）”，用于返回概率指数分布函数。计算结果如图16-58所示。

图16-58　计算结果

使用指南

如果x或lambda为非数值型，函数EXPONDIST返回错误值“#VALUE！”。如果x＜0，函数EXPONDIST返回错误值“#NUM！”。如果lambda≤0，函数EXPONDIST返回错误值“#NUM！”。概率密度函数的计算公式为：

累积分布函数的计算公式为：

Excel 应用COUNTIFS函数计算区域中满足多个条件的单元格的数量

COUNTIFS函数用于计算某个区域中满足多重条件的单元格数目。COUNTIFS函数的语法如下。

COUNTIFS(range1, criteria1,range2, criteria2…)

其中参数range1，range2，…是计算关联条件的1至127个区域。每个区域中的单元格必须是数字或包含数字的名称、数组或引用。空值和文本值会被忽略。criteria1，criteria2，…是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格进行计算。例如，条件可以表示为32、”32″、”>32″、”apples”或B4。

典型案例

已知某公司4名销售人员销售3种产品是否超出配额的情况，分别计算多种条件下超出销售配额的次数。基础数据如图16-55所示。

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

步骤2：在单元格A7中输入公式“=COUNTIFS（B2：D2，”=是”）”，用于计算第一列中王俊杰超出配额次数。

步骤3：在单元格A8中输入公式“=COUNTIFS（B2：B5，”=是”，C2：C5，”=是”）”，用于计算同时超出B-02和B-03配额的人员数。

步骤4：在单元格A9中输入公式“=COUNTIFS（B5：D5，”=是”，B3：D3，”=是”）”，用于计算超出相同配额的次数。计算结果如图16-56所示。

使用指南

仅当区域中的每一单元格满足为其指定的所有相应条件时才对其进行计算。如果条件为空单元格，COUNTIFS将其视为0值。可以在条件中使用通配符，即问号（？）和星号（*）。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，则需要在字符前键入波形符（~）。

图16-55　基础数据

图16-56　计算结果

Excel 应用COUNTIF函数计算区域中满足给定条件的单元格的数量

COUNTIF函数用于计算区域中满足给定条件的单元格的个数。COUNTIF函数的语法如下。

COUNTIF(range,criteria)

其中参数range是一个或多个要计数的单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为32、”32″、”>32″、”apples”或B4。

【典型案例】已知一组数据，计算区域中满足给定条件的单元格的个数。基础数据如图16-53所示。

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

步骤2：在单元格A7中输入公式“=COUNTIF（A2：A5，”cars”）”，用于计算第一列中cars所在单元格的个数。

步骤3：在单元格A8中输入公式“=COUNTIF（A2：A5，A4）”，用于计算第一列中buses所在单元格的个数。

步骤4：在单元格A9中输入公式“=COUNTIF（A2：A5，A3）+COUNTIF（A2：A5，A2）”，用于计算第一列中bikes和cars所在单元格的个数。

步骤5：在单元格A10中输入公式“=COUNTIF（B2：B5，”>49″）”，用于计算第2列中值大于49的单元格个数。

步骤6：在单元格A11中输入公式“=COUNTIF（B2：B5，”<>”&B4）”，用于计算第2列中值不等于56的单元格个数。

步骤7：在单元格A12中输入公式“=COUNTIF（B2：B5，“>=43”）-COUNTIF（B2：B5，”>92″）”，用于计算第二列中值大于或等于43且小于或等于92的单元格个数。计算结果如图16-54所示。

图16-53　基础数据

图16-54　计算结果

【使用指南】可以在条件中使用通配符，如问号（？）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

Excel 应用COUNTBLANK函数计算区域内空白单元格的数量

COUNTBLANK函数用于计算指定单元格区域中空白单元格的个数。COUNTBLANK函数的语法如下。

COUNTBLANK(range)

其中参数range为需要计算其中空白单元格个数的区域。

【典型案例】已知一个单元格区域，计算该区域中空白单元格的个数。基础数据如图16-51所示。

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

步骤2：在单元格A7中输入公式“=COUNTBLANK（A2：B5）”，用于计算“A2：B5”区域中空单元格的个数。计算结果如图16-52所示。

图16-51　基础数据

图16-52　计算结果

【使用指南】即使单元格中含有返回值为空文本（””）的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。

Excel 应用COUNTA函数计算参数列表中值的个数

COUNTA函数用于计算参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。COUNTA函数的语法如下。

COUNTA(value1,value2,...)

其中参数value1，value2，…代表要计数其值的1到255个参数。

典型案例

已知一组数据，计算参数列表中非空值的单元格个数。基础数据如图16-49所示。

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

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

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

步骤4：在单元格A12中输入公式“=COUNTA（A1：A7，2）”，用于计算上列数据中非空单元格以及包含数值“2”的单元格个数。

步骤5：在单元格A13中输入公式“=COUNTA（A1：A7，”Two”）”，用于计算上列数据中非空单元格以及包含值“Two”的单元格个数。计算结果如图16-50所示。

图16-49　基础数据

图16-50　计算结果

使用指南

数值是任何类型的信息，包括错误值和空文本（””）。数值不包括空单元格。如果参数为数组或引用，则只使用其中的数值。数组或引用中的空白单元格和文本值将被忽略。如果不需要对逻辑值、文本或错误值进行计数，则需要使用COUNT函数。

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检验的单尾概率值。对于给定的假设总体平均值μ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 应用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用于在有限样本总体中进行不退回抽样的概率计算。