分类：函数篇

Excel 应用POISSON函数计算泊松分布

POISSON函数用于返回泊松分布。泊松分布通常用于预测一段时间内事件发生的次数，比如一分钟内通过收费站的轿车的数量。POISSON函数的语法如下。

POISSON(x,mean,cumulative)

其中参数x为事件数，mean为期望值。cumulative为一逻辑值，确定所返回的概率分布形式。如果cumulative为TRUE，函数POISSON返回泊松累积分布概率，即随机事件发生的次数在0到x之间（包含0和1）；如果为FALSE，则返回泊松概率密度函数，即随机事件发生的次数恰好为x。

典型案例

已知事件数和期望值，计算符合这些条件的泊松累积分布概率和泊松概率密度函数的结果。基础数据如图16-157所示。

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

步骤2：在单元格A5中输入公式“=POISSON（A2，A3，TRUE）”，用于计算符合上述条件的泊松累积分布概率。

步骤3：在单元格A6中输入公式“=POISSON（A2，A3，FALSE）”，用于计算符合上述条件的泊松概率密度函数的结果。计算结果如图16-158所示。

图16-157　基础数据

图16-158　计算结果

使用指南

如果x不为整数，将被截尾取整。如果x或mean为非数值型，函数POISSON返回错误值“#VALUE！”；如果x＜0，函数POISSON返回错误值“#NUM！”；如果mean＜0，函数POISSON返回错误值“#NUM！”。函数POISSON的计算公式如下。

假设cumulative=FALSE：

假设cumulative=TRUE：

Excel 应用PERMUT函数计算给定数目对象的排列数

PERMUT函数用于从给定数目的对象集合中选取的若干对象的排列数。排列为有内部顺序的对象或事件的任意集合或子集。排列与组合不同，组合的内部顺序无意义。此函数可用于彩票抽奖的概率计算。PERMUT函数的语法如下。

PERMUT(number,number_chosen)

其中参数number表示对象个数的整数，number_chosen表示每个排列中对象个数的整数。

典型案例

已知有30个数，从中选取7个数，计算所有可能的排列数量。基础数据如图16-155所示。

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

步骤2：在单元格A5中输入公式“=PERMUT（A2，A3）”，用于计算所有可能的排列数量。计算结果如图16-156所示。

图16-155　基础数据

图16-156　计算结果

使用指南

两个参数若非整数将被截尾取整。如果number或number_chosen为非数值型，函数PERMUT返回错误值“#VALUE！”；如果number≤0或number_chosen＜0，函数PERMUT返回错误值“#NUM！”；如果number<number_chosen，函数PERMUT返回错误值“#NUM！”。排列数的计算公式如下。

Excel 应用PERCENTILE函数返回区域中数值的第k个百分点的值

PERCENTILE函数用于计算区域中数值的第k个百分点的值。可以使用此函数来建立接受阈值。例如，可以确定得分排名在10之上的CEO候选人。PERCENTILE函数的语法如下。

PERCENTILE(array,k)

其中参数array为定义相对位置的数组或数据区域。k为0到1之间的百分点值，包含0和1。

典型案例

已知一组数据，计算数据在第30个百分点的值。基础数据如图16-153所示。

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

步骤2：在单元格A7中输入公式“=PERCENTILE（A2：A5，0.3）”，计算结果如图16-154所示。

图16-153　基础数据

图16-154　计算结果

使用指南

如果array为空或其数据点超过8191个，函数PERCENTILE返回错误值“#NUM！”；如果k为非数字型，函数PERCENTILE返回错误值“#VALUE！”；如果k＜0或k＞1，函数PERCENTILE返回错误值“#NUM！”；如果k不是1/（n-1）的倍数，函数PERCENTILE使用插值法来确定第k个百分点的值。

Excel 应用NEGBINOMDIST函数计算负二项式分布

NEGBINOMDIST函数用于计算负二项式分布。当成功概率为常量probability_s时，函数NEGBINOMDIST返回在到达number_s次成功之前，出现number_f次失败的概率。此函数与二项式分布相似，只是它的成功次数固定，试验总数为变量。与二项式分布类似的是，试验次数被假设为自变量。NEGBINOMDIST函数的语法如下。

NEGBINOMDIST(number_f,number_s,probability_s)

其中参数number_f为失败次数，number_s为成功的极限次数，probability_s为成功的概率。

典型案例

已知某种化学药剂试验的失败次数、成功的极限次数、成功的概率，计算在这些条件下的负二项式分布值。基础数据如图16-151所示。

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

步骤2：在单元格A6中输入公式“=NEGBINOMDIST（A2，A3，A4）”，用于计算在这些条件下的负二项式分布值。计算结果如图16-152所示。

图16-151　基础数据

图16-152　计算结果

使用指南

number_f和number_s若非整数将被截尾取整。如果任一参数为非数值型，函数NEGBINOMDIST返回错误值“#VALUE！”；如果probability_s＜0或probability＞1，函数NEGBINOMDIST返回错误值“#NUM！”；如果number_f＜0或number_s＜1，函数NEGBINOMDIST返回错误值“#NUM！”。负二项式分布的计算公式如下。

式中x是number_f，r是number_s，且p是probability_s。

Excel 应用GAMMAINV函数计算γ累积分布函数的反函数

GAMMAINV函数用于计算γ累积分布函数的反函数。如果P=GAMMADIST（x，…），则GAMMAINV（p，…）=x。使用此函数可研究可能出现偏态分布的变量。GAMMAINV函数的语法如下。

GAMMAINV(probability,alpha,beta)

其中参数probability为γ分布的概率值，alpha为分布参数，beta为分布参数。如果beta=1，函数GAMMAINV返回标准伽玛分布。

典型案例

已知用来计算分布的数值、Alpha分布参数、Beta分布参数，计算这些条件下的γ累积分布函数的反函数。基础数据如图16-149所示。

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

步骤2：在单元格A6中输入公式“=GAMMAINV（A2，A3，A4）”，用于计算在上述条件下γ累积分布函数的反函数。计算结果如图16-150所示。

图16-149　基础数据

图16-150　计算结果

使用指南

如果任一参数为文本型，函数GAMMAINV返回错误值“#VALUE！”；如果probability＜0或probability＞1，函数GAMMAINV返回错误值“#NUM！”；如果alpha≤0或beta≤0，函数GAMMAINV返回错误值“#NUM！”。

如果已给定概率值，则GAMMAINV使用GAMMADIST（x，alpha，beta，TRUE）=probability求解数值x。因此，GAMMAINV的精度取决于GAMMADIST的精度。GAMMAINV使用迭代搜索技术，如果搜索在100次迭代之后没有收敛，则函数返回错误值“#N/A”。

Excel 应用GAMMADIST函数计算γ分布

GAMMADIST函数用于计算γ分布。可以使用此函数来研究具有偏态分布的变量。γ分布通常用于排队分析。GAMMADIST函数的语法如下。

GAMMADIST(x,alpha,beta,cumulative)

其中参数x为用来计算分布的数值，alpha为分布参数，beta为分布参数。如果beta=1，函数GAMMADIST返回标准γ分布。cumulative为一逻辑值，决定函数的形式。如果cumulative为TRUE，函数GAMMADIST返回累积分布函数；如果为FALSE，则返回概率密度函数。

典型案例

已知用来计算分布的数值、Alpha分布参数、Beta分布参数，计算这些条件下的概率γ分布和累积γ分布。基础数据如图16-147所示。

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

步骤2：在单元格A6中输入公式“=GAMMADIST（A2，A3，A4，FALSE）”，用于计算在上述条件下的概率γ分布。

步骤3：在单元格A7中输入公式“=GAMMADIST（A2，A3，A4，TRUE）”，用于计算在上述条件下的累积γ分布。计算结果如图16-148所示。

图16-147　基础数据

图16-148　计算结果

使用指南

如果x、alpha或beta为非数值型，函数GAMMADIST返回错误值“#VALUE！”；如果x＜0，函数GAMMADIST返回错误值“#NUM！”；如果alpha≤0或beta≤0，函数GAMMADIST返回错误值“#NUM！”。γ概率密度函数的计算公式如下。

标准γ概率密度函数如下。

当alpha=1时，函数GAMMADIST返回如下的指数分布。

对于正整数n，当alpha=n/2，beta=2且cumulative=TRUE时，函数GAMMADIST以自由度n返回（1-CHIDIST（X））。当alpha为正整数时，函数GAMMADIST也称为爱尔朗（Erlang）分布。

Excel 应用FISHERINV函数计算Fisher变换的反函数值

FISHERINV函数用于计算Fisher变换的反函数值，使用此变换可以分析数据区域或数组之间的相关性。如果y=FISHER（x），则FISHERINV（y）=x。FISHERINV函数的语法如下。

FISHERINV(y)

其中参数y为要对其进行反变换的数值。

典型案例

计算Fisher变换的反函数在0.365463754上的值。基础数据如图16-145所示。

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

步骤2：在单元格A2中输入公式“=FISHERINV（0.365463754）”，用于计算Fisher变换的反函数在0.365463754上的值。计算结果如图16-146所示。

图16-145　基础数据

图16-146　计算结果

使用指南

如果y为非数值型，函数FISHERINV返回错误值“#VALUE！”。Fisher变换反函数的计算公式为：

Excel 应用FISHER函数计算Fisher变换值

FISHER函数用于计算点x的Fisher变换值。该变换生成一个正态分布而非偏斜的函数，使用此函数可以完成相关系数的假设检验。FISHER函数的语法如下。

FISHER(x)

其中参数x为要对其进行变换的数值。

典型案例

计算0.35的Fisher变换值。基础数据如图16-143所示。

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

步骤2：在单元格A2中输入公式“=FISHER（0.35）”，用于计算0.35的Fisher变换值。计算结果如图16-144所示。

图16-143　基础数据

图16-144　计算结果

使用指南

如果x为非数值型，函数FISHER返回错误值“#VALUE！”；如果x≤-1或x≥1，函数FISHER返回错误值“#NUM！”。Fisher变换的计算公式如下。

Excel 应用TINV函数计算学生的t分布的反函数

TINV函数用于计算作为概率和自由度函数的学生t分布的t值。TINV函数的语法如下。

TINV(probability,degrees_freedom)

其中参数probability为对应于双尾学生t分布的概率，degrees_freedom为分布的自由度数值。

典型案例

已知对应于双尾学生t分布的概率和自由度，计算学生t分布的t值。基础数据如图16-141所示。

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

步骤2：在单元格A5中输入公式“=TINV（A2，A3）”，用于计算学生t分布的t值。计算结果如图16-142所示。

图16-141　基础数据

图16-142　计算结果

使用指南

如果任一参数为非数值型，函数TINV返回错误值“#VALUE！”；如果probability＜0或probability＞1，函数TINV返回错误值“#NUM！”；如果degrees_freedom不是整数，将被截尾取整；如果degrees_freedom＜1，函数TINV返回错误值“#NUM！”。TINV返回t值，P（|X|＞t）=probability，其中X为服从t分布的随机变量，且P（|X|＞t）=P（X<-t or X＞t）。

单尾t值可通过用两倍概率替换概率而求得。如果概率为0.05而自由度为10，则双尾值由TINV（0.05，10）计算得到，它返回2.28139。而同样概率和自由度的单尾值可由TINV（2*0.05，10）计算得到，它返回1.812462。在某些表中，概率被描述为（1-p）。

如果已给定概率值，则TINV使用TDIST（x，degrees_freedom，2）=probability求解数值x。因此，TINV的精度取决于TDIST的精度。TINV使用迭代搜索技术，如果搜索在100次迭代之后没有收敛，则函数返回错误值“#N/A”。

Excel 应用TDIST函数计算学生的t分布

TDIST函数用于计算学生t分布的百分点（概率），其中数值（即x）是t的计算值（将计算其百分点）。t分布用于小样本数据集合的假设检验，使用此函数可以代替t分布的临界值表。TDIST函数的语法如下。

TDIST(x,degrees_freedom,tails)

其中参数x是需要计算分布的数值，degrees_freedom是一个表示自由度的整数，tails指定返回的分布函数是单尾分布还是双尾分布。如果tails=1，则TDIST返回单尾分布；如果tails=2，则TDIST返回双尾分布。

典型案例

已知需要计算分布的数值和自由度，计算双尾分布和单尾分布。基础数据如图16-139所示。

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

步骤2：在单元格A5中输入公式“=TDIST（A2，A3，2）”，用于计算双尾分布。

步骤3：在单元格A6中输入公式“=TDIST（A2，A3，1）”，用于计算单尾分布。计算结果如图16-140所示。

图16-139　基础数据

图16-140　计算结果

使用指南

如果任一参数为非数字型，则TDIST返回错误值“#VALUE！”。如果degrees_freedom＜1，则TDIST返回错误值“#NUM！”。参数degrees_freedom和tails若不是整数将被截尾取整。如果tails不为1或2，则TDIST返回错误值“#NUM！”。如果x＜0，TDIST返回错误值“#NUM！”。

如果tails=1，TDIST的计算公式为TDIST=P（X＞x），其中X为服从t分布的随机变量；如果tails=2，TDIST的计算公式为TDIST=P（|X|＞x）=P（X＞x or X＜-x）。因为不允许x＜0，所以当x＜0时要使用TDIST，注意用TDIST（-x，df，1）=1–TDIST（x，df，1）=P（X＞-x）和TDIST（-x，df，2）=TDIST（xdf，2）=P（|X|＞x）进行变形换算。