Excel22 – 第139页

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”。

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,m=population_s

N=number_population

下面通过实例详细讲解该函数的使用方法与技巧。

打开“HYPGEOMDIST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-11所示。已知样本中成功的次数、样本容量、样本总体中成功的次数、样本总体的容量，要求计算样本和样本总体的超几何分布。具体的操作步骤如下。

选中A7单元格，在编辑栏中输入公式“=HYPGEOMDIST(A2,A3,A4,A5)”，用于返回上述样本和样本总体的超几何分布，输入完成后按“Enter”键返回计算结果，如图17-12所示。

如果任一参数为非数值型，函数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!”。

图17-11　原始数据

图17-12　返回上述样本和样本总体的超几何分布

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

Excel 计算F概率分布：FDIST函数

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

FDIST(x,degrees_freedom1,degrees_freedom2)

其中，x参数为参数值，degrees_freedom1参数为分子的自由度，degrees_freedom2参数为分母的自由度。下面通过实例详细讲解该函数的使用方法与技巧。

打开“FDIST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-9所示。要求根据工作表中已经给定的参数值、分子自由度、分母自由度，计算F概率分布。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=FDIST(A2,A3,A4)”，用于计算F概率分布，输入完成后按“Enter”键返回计算结果，如图17-10所示。

如果任何参数都为非数值型，函数FDIST返回错误值“#VALUE!”。如果x参数为负数，函数FDIST返回错误值“#NUM!”。如果degrees_freedom1参数或degrees_freedom2参数不是整数，将被截尾取整。如果参数degrees_freedom1<1或参数degrees_freedom1≥10^10，函数FDIST返回错误值“#NUM!”。如果参数degrees_freedom2<1或参数degrees_freedom2≥10^10，函数FDIST返回错误值“#NUM!”。函数FDIST的计算公式为FDIST=P(F>x)，其中F为呈F分布且带有degrees_freedom1和degrees_freedom2自由度的随机变量。

图17-9　原始数据

图17-10　计算F概率分布

Excel 计算χ2分布单尾概率：CHIDIST函数

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

CHIDIST(x,degrees_freedom)

其中，x参数为用来计算分布的数值，degrees_freedom参数为自由度的数值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“CHIDIST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-5所示。工作表中已经给定用来计算分布的数值和自由度，要求计算χ2分布的单尾概率。具体的操作步骤如下。

选中A5单元格，在编辑栏中输入公式“=CHIDIST(A2,A3)”，用于计算χ2分布的单尾概率，输入完成后按“Enter”键返回计算结果，如图17-6所示。

图17-5　原始数据

图17-6　计算χ2分布的单尾概率

如果任一参数为非数值型，函数CHIDIST返回错误值“#VALUE!”。如果x为负数，函数CHIDIST返回错误值“#NUM!”。如果degrees_freedom参数不是整数，将被截尾取整。如果参数degrees_freedom<1或参数degrees_freedom>10^10，则函数CHIDIST返回错误值“#NUM!”。函数CHIDIST按CHIDIST=P(X>x)计算，式中X为χ²随机变量。

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

CHIINV(probability,degrees_freedom)

其中，probability参数为与χ²分布相关的概率，degrees_freedom参数为自由度的数值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“CHIINV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-7所示。工作表中已经给定用来计算分布的数值和自由度，要求计算χ²分布的单尾概率的反函数值。具体的操作步骤如下。

选中A5单元格，在编辑栏中输入公式“=CHIINV(A2,A3)”，用于计算χ²分布的单尾概率的反函数值，输入完成后按“Enter”键返回计算结果，如图17-8所示。

图17-7　原始数据

图17-8　计算χ²分布的单尾概率的反函数值

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

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

BINOMDIST函数可以返回一元二项式分布的概率值。函数BINOMDIST适用于固定次数的独立试验，试验的结果只包含成功或失败两种情况，且成功的概率在实验期间固定不变。例如，函数BINOMDIST可以计算3个婴儿中两个是男孩的概率。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次的概率。打开“BINOMDIST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-3所示。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=BINOMDIST(A2,A3,A4,FALSE)”，用于计算12次试验成功6次的概率，输入完成后按“Enter”键返回计算结果，如图17-4所示。

图17-3　原始数据

图17-4　计算12次试验成功6次的概率

如果参数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)。

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

Excel 计算区域值出现频率：FREQUENCY函数

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

FREQUENCY(data_array,bins_array)

其中，data_array参数是一个数组或对一组数值的引用，要为它计算频率。如果data_array参数中不包含任何数值，函数FREQUENCY将返回一个零数组。bins_array参数是一个区间数组或对区间的引用，该区间用于对data_array参数中的数值进行分组。如果bins_array参数中不包含任何数值，则函数FREQUENCY返回的值与data_array参数中的元素个数相等。下面通过实例详细讲解该函数的使用方法与技巧。

打开“FREQUENCY函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图17-1所示。已知在单元格区域中的若干分数，以及区间分割点，要求计算各区间内的分数个数。具体的操作步骤如下。

选中A12:A15单元格，按“F2”键，在A12单元格中输入公式“=FREQUENCY(A2:A10,B2:B4)”，然后按“Ctrl+Shift+Enter”组合键返回数组公式，用于分别各区间的分数个数，计算结果如图17-2所示。

图17-1　原始数据

图17-2　计算结果

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

Excel实战：实现学生成绩查询

打开“学生成绩查询.xlsx”工作簿，某大学语言测试成绩表如图16-67所示。现在需要实现只输入学生姓名，就能够查询某一学生的成绩或其他信息。

下面介绍如何使用查找与引用函数中的INDEX函数和MATCH函数来实现这种查询功能。

图16-67　学生成绩表

STEP01：选中H3单元格，在编辑栏中输入公式“=INDEX(E:E,MATCH($G$3,$A:$A,0))”，输入完成后按“Enter”键返回计算结果，如图16-68所示。该公式使用INDEX函数返回成绩表中$G$3所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$3相同的值。

STEP02：选中G3单元格，在单元格中输入一个学生的姓名，这里输入“田珊”，然后按“Enter”键返回，可以看到在H3单元格中显示出其成绩，如图16-69所示。

图16-68　输入成绩公式

图16-69　输入学生姓名查询成绩

STEP03：选中H5单元格，在编辑栏中输入公式“=INDEX(E:E,MATCH($G$5,$B:$B,0))”，输入完成后按“Enter”键返回计算结果，如图16-70所示。该公式使用INDEX函数返回成绩表中$G$5所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$5相同的值。

STEP04：选中G5单元格，在单元格中输入一个学生的准考证号码，这里输入“1525509012510716”，然后按“Enter”键返回，可以看到在H5单元格中显示出其成绩，如图16-71所示。

图16-70　在H5单元格中输入公式

图16-71　使用准考证号码查询学生成绩

Excel 计算转置单元格区域：TRANSPOSE函数

TRANSPOSE函数用于返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，反之亦然。在行列数分别与数组的行列数相同的区域中，必须将TRANSPOSE输入为数组公式。使用TRANSPOSE可在工作表中转置数组的垂直和水平方向。其语法如下：

TRANSPOSE(array)

其中，array参数为需要进行转置的数组或工作表中的单元格区域。所谓数组的转置就是，将数组的第1行作为新数组的第1列，数组的第2行作为新数组的第2列，依此类推。下面通过实例详细讲解该函数的使用方法与技巧。

打开“TRANSPOSE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-65所示。要求根据工作表中的数据内容，返回转置单元格区域。具体操作步骤如下。

选中A4:A6单元格区域，在公式编辑框中输入公式“=TRANSPOSE($A$2:$C$2)”，然后按“Ctrl+Shift+Enter”组合键转换为数组公式，得到的计算结果如图16-66所示。

图16-65　原始数据

图16-66　返回转置单元格区域

Excel 检索实时数据：RTD函数

RTD函数用于从支持COM自动化的程序中检索实时数据。其语法如下：

RTD(ProgID,server,topic1,[topic2],...)

其中，ProgID参数为已安装在本地计算机上、经过注册的COM自动化加载宏的ProgID名称，该名称用引号引起来。Server为运行加载宏的服务器的名称。topic1、topic2……为1～253个参数，这些参数放在一起代表一个唯一的实时数据。

例如，以下公式将从LOREM_IPSUM服务器的MyComAddIn.Progid中检索“Price”：

=RTD("MyComAddIn.Progid",,"LOREM_IPSUM","Price")

Excel 计算行数：ROWS函数

ROWS函数用于返回引用或数组的行数。其语法如下：

ROWS(array)

其中，array参数为需要得到其行数的数组、数组公式或对单元格区域的引用。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ROWS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-62所示。要求根据工作表中的数据内容，返回引用的行数。具体操作步骤如下。

图16-62　原始数据

STEP01：选中A2单元格，在编辑栏中输入公式“=ROWS(A1:F7)”，用于返回引用中的行数，输入完成后按“Enter”键返回计算结果，如图16-63所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=ROWS({1,2,3,4;5,6,7,8;2,3,4,5})”，用于返回数组常量中的行数，输入完成后按“Enter”键返回计算结果，如图16-64所示。

图16-63　返回引用中的行数

图16-64　返回数组常量中的行数