Excel2019函数公式大全 – 第19页

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　返回数组常量中的行数

Excel 计算行号：ROW函数

ROW函数用于返回引用的行号。其语法如下：

ROW(reference)

其中，reference参数为需要得到其行号的单元格或单元格区域。如果省略reference参数，则假定是对函数ROW所在单元格的引用。如果reference参数为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference参数的行号以垂直数组的形式返回。reference参数不能引用多个区域。下面通过实例详细讲解该函数的使用方法与技巧。

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

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

图16-59　原始数据

图16-60　返回公式所在行的行号

STEP02：选中A3单元格，在编辑栏中输入公式“=ROW(D19)”，用于返回引用所在行的行号，输入完成后按“Enter”键返回计算结果，如图16-61所示。

图16-61　返回引用所在行的行号

Excel 计算指定的引用：INDIRECT函数

INDIRECT函数用于返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，则须使用函数INDIRECT。其语法如下：

INDIRECT(ref_text,a1)

其中，ref_text参数为对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。a1为一逻辑值，指明包含在单元格ref_text中的引用的类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDIRECT函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-54所示。要求根据工作表中的数据内容，返回由文本字符串指定的引用。具体操作步骤如下。

图16-54　原始数据

STEP01：选中A7单元格，在编辑栏中输入公式“=INDIRECT($A$2)”，用于返回A2单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-55所示。

STEP02：选中A8单元格，在编辑栏中输入公式“=INDIRECT($A$3)”，用于返回A3单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-56所示。

图16-55　返回单元格A2的引用值

图16-56　返回A8单元格中的引用值

STEP03：选中A9单元格，在编辑栏中输入公式“=INDIRECT($A$4)”，用于返回A4单元格中的引用值，如果B4单元格有定义名“Beijing”，则返回定义名的值（2008），输入完成后按“Enter”键返回计算结果，如图16-57所示。

STEP04：选中A10单元格，在编辑栏中输入公式“=INDIRECT(“B”&$A$5)”，用于返回A5单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-58所示。

图16-57　返回A4单元格中的引用值

图16-58　返回A5单元格中的引用值

Excel 返回区域值或值引用：INDEX函数

INDEX函数用于返回表或区域中的值或值的引用，它两种形式：数组形式和引用形式。

数组形式

返回表格或数组中的元素值，此元素由行序号和列序号的索引值给定。当函数INDEX的第1个参数为数组常量时，使用数组形式。其语法如下：

INDEX(array,row_num,column_num)

其中，array参数为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数row_num参数或column_num参数为可选参数。如果数组有多行和多列，但只使用row_num参数或column_num参数，函数INDEX返回数组中的整行或整列，且返回值也为数组。row_num参数为数组中某行的行号，函数从该行返回数值。如果省略row_num参数，则必须有column_num参数。column_num参数为数组中某列的列标，函数从该列返回数值。如果省略column_num参数，则必须有row_num参数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDEX.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-46所示。要求根据工作表中的数据内容，返回表格或数组中的元素值。具体操作步骤如下。

STEP01：选中A5单元格，在编辑栏中输入公式“=INDEX(A2:B3,2,2)”，用于返回位于区域中第2行和第2列交叉处的数值（沙和尚），输入完成后按“Enter”键返回计算结果，如图16-47所示。

图16-46　原始数据

图16-47　A5单元格返回结果

STEP02：选中A6单元格，在编辑栏中输入公式“=INDEX(A2:B3,2,1)”，用于返回位于区域中第2行和第1列交叉处的数值（唐三藏），输入完成后按“Enter”键返回计算结果，如图16-48所示。

引用形式

返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。其语法如下：

INDEX(reference,row_num,column_num,area_num)

其中，reference参数为对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num参数或column_num参数分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,,column_num)。

row_num参数为引用中某行的行号，函数从该行返回一个引用。column_num参数为引用中某列的列标，函数从该列返回一个引用。area_num参数为选择引用中的一个区域，返回该区域中row_num参数和column_num参数的交叉区域。选中或输入的第1个区域序号为1，第2个为2，依此类推。如果省略area_num参数，则函数INDEX使用区域1。

例如，如果引用描述的单元格为(A1:C4,D1:E4,F1:H4)，则area_num 1为区域A1:C4，area_num 2为区域D1:E4，而area_num 3为区域F1:H4。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDEX函数.xlsx”工作簿，切换至“Sheet2”工作表，本例中的原始数据如图16-49所示。要求根据工作表中的数据内容，返回指定的行与列交叉处的单元格引用。具体操作步骤如下。

图16-48　A6单元格返回结果

图16-49　原始数据

STEP01：选中A12单元格，在编辑栏中输入公式“=INDEX(A2:C6,2,3)”，用于返回区域A2:C6中第2行和第3列的交叉处，即C3单元格的内容，输入完成后按“Enter”键返回计算结果，如图16-50所示。

STEP02：选中A13单元格，在编辑栏中输入公式“=INDEX((A1:C6,A7:C10),2,2,2)”，用于返回第2个区域A8:C11中第2行和第2列的交叉处，即B8单元格的内容，输入完成后按“Enter”键返回计算结果，如图16-51所示。

图16-50　A12单元格返回结果

图16-51　A13单元格返回结果

STEP03：选中A14单元格，在编辑栏中输入公式“=SUM(INDEX(A1:C10,0,3,1))”，用于对第1个区域A1:C10中的第3列求和，即对C1:C10求和，输入完成后按“Enter”键返回计算结果，如图16-52所示。

STEP04：选中A15单元格，在编辑栏中输入公式“=SUM(B2:INDEX(A2:C6,5,2))”，用于返回以B2单元格开始到A2:C6单元格区域中第5行和第2列交叉处结束的单元格区域的和，即B2:B6单元格区域的和，输入完成后按“Enter”键返回计算结果，如图16-53所示。

图16-52　A14单元格返回结果

图16-53　A15单元格返回结果