分类：统计学函数应用

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

Excel 计算线性回归线斜率：SLOPE函数

SLOPE函数用于计算根据known_y’s和known_x’s中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数的语法如下：

SLOPE(known_y's,known_x's)

其中，known_y’s参数为数字型因变量数据点数组或单元格区域，known_x’s参数为自变量数据点集合。下面通过实例详细讲解该函数的使用方法与技巧。

打开“SLOPE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-66所示。该工作表中记录了一组x、y值，要求根据这些数据点计算拟合的线性回归直线的斜率。具体的操作步骤如下。

选中A10单元格，在编辑栏中输入公式“=SLOPE(A2:A8,B2:B8)”，用于计算线性回归直线的斜率，输入完成后按“Enter”键返回计算结果，如图18-67所示。

图18-66　原始数据

图18-67　计算斜率

注意：

1）参数可以是数字，或者是包含数字的名称、数组或引用。

2）如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

3）如果known_y’s参数和known_x’s参数为空或其数据点个数不同，函数SLOPE返回错误值“#N/A”。回归直线的斜率计算公式如下：

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s),n是样本大小。

Excel 计算正态化数值：STANDARDIZE函数

STANDARDIZE函数用于计算以mean为平均值，以standard_dev为标准偏差的分布的正态化数值。STANDARDIZE函数的语法如下：

STANDARDIZE(x,mean,standard_dev)

其中，x参数为需要进行正态化的数值，mean参数为分布的算术平均值，standard_dev参数为分布的标准偏差。下面通过实例详细讲解该函数的使用方法与技巧。

打开“STANDARDIZE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-64所示。已知要正态化的数值、分布的算术平均值和分布的标准偏差，要求计算符合上述条件的58的正态化数值。具体的操作步骤如下。

图18-64　原始数据

选中A6单元格，在编辑栏中输入公式“=STANDARDIZE(A2,A3,A4)”，用于返回符合上述条件的58的正态化数值，输入完成后按“Enter”键返回计算结果，如图18-65所示。

如果参数standard_dev≤0，函数STANDARDIZE返回错误值“#NUM!”。正态化数值的计算公式如下：

图18-65　计算正态化数值

Excel 计算正态累积分布：NORMSINV函数

NORMSINV函数用于计算标准正态累积分布函数的反函数。该分布的平均值为0，标准偏差为1。NORMSINV函数的语法如下：

NORMSINV(probability)

其中，probability参数为正态分布的概率值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“NORMSINV函数.xlsx”工作簿，会自动切换至如图18-62所示的工作表页面。本例中需要计算概率为0.95221时标准正态累积分布函数的反函数值。计算的方法如下。

选中A2单元格，在编辑栏中输入公式“=NORMSINV(0.95221)”，用于计算概率为0.95221时标准正态累积分布函数的反函数值，输入完成后按“Enter”键返回计算结果，如图18-63所示。

图18-62　需要计算的值

图18-63　计算反函数值

如果参数probability为非数值型，函数NORMSINV返回错误值“#VALUE!”。如果参数probability<0或参数probability>1，函数NORMSINV返回错误值“#NUM!”。如果已给定概率值，则NORMSINV使用NORMSDIST(z)=probability求解数值z。因此，NORMSINV的精度取决于NORMSDIST的精度。NORMSINV使用迭代搜索技术。如果搜索在100次迭代之后没有收敛，则函数返回错误值“#N/A”。

Excel 计算正态累积分布：NORMSDIST函数

NORMSDIST函数用于计算标准正态累积分布函数，该分布的平均值为0，标准偏差为1。可以使用该函数代替标准正态曲线面积表。NORMSDIST函数的语法如下：

NORMSDIST(z)

其中，z参数为需要计算其分布的数值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“NORMSDIST函数.xlsx”工作簿，会自动切换至如图18-60所示的工作表页面。本例中需要计算1.66667的正态累积分布函数值。计算的方法如下。

选中A2单元格，在编辑栏中输入公式“=NORMSDIST(1.66667)”，用于计算1.66667的正态累积分布函数值，输入完成后按“Enter”键返回计算结果，如图18-61所示。

图18-60　需要计算的值

图18-61　计算结果

如果z参数为非数值型，函数NORMSDIST返回错误值“#VALUE!”。标准正态分布密度函数计算公式如下：

Excel 计算正态累积分布：NORMINV函数

NORMINV函数用于计算指定平均值和标准偏差的正态累积分布函数的反函数。NORMINV函数的语法如下：

NORMINV(probability,mean,standard_dev)

其中，probability参数为正态分布的概率值，mean参数为分布的算术平均值，standard_dev参数为分布的标准偏差。下面通过实例详细讲解该函数的使用方法与技巧。

打开“NORMINV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-58所示。已知需要计算其分布的数值、分布的算术平均值和分布的标准偏差，要求计算在这些条件下正态累积分布函数的反函数值。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=NORMINV(A2,A3,A4)”，用于计算正态累积分布函数的反函数值，输入完成后按“Enter”键返回计算结果，如图18-59所示。

如果任一参数为非数值型，函数NORMINV返回错误值“#VALUE!”。如果参数probability<0或参数probability>1，函数NORMINV返回错误值“#NUM!”。如果参数standard_dev≤0，函数NORMINV返回错误值“#NUM!”。如果参数mean=0且参数standard_dev=1，函数NORMINV使用标准正态分布。如果已给定概率值，则NORMINV使用NORMDIST(x,mean,standard_dev,TRUE)=probability求解数值x。因此，NORMINV的精度取决于NORMDIST的精度。NORMINV使用迭代搜索技术。如果搜索在100次迭代之后没有收敛，则函数返回错误值“#N/A”。

图18-58　原始数据

图18-59　计算正态累积分布函数的反函数

Excel 计算正态累积分布：NORMDIST函数

NORMDIST函数用于计算指定平均值和标准偏差的正态分布函数。此函数在统计方面应用范围广泛（包括假设检验）。NORMDIST函数的语法如下：

NORMDIST(x,mean,standard_dev,cumulative)

其中，x参数为需要计算其分布的数值，mean参数为分布的算术平均值，standard_dev参数为分布的标准偏差，cumulative参数为一逻辑值，决定函数的形式，如果cumulative参数为TRUE，函数NORMDIST返回累积分布函数；如果为FALSE，返回概率密度函数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“NORMDIST函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-55所示。已知需要计算其分布的数值、分布的算术平均值和分布的标准偏差，要求计算出累积分布函数值和概率密度函数值。具体的操作步骤如下。

图18-55　原始数据

STEP01：选中A6单元格，在编辑栏中输入公式“=NORMDIST(A2,A3,A4,TRUE)”，用于计算在上述条件下的累积分布函数值，输入完成后按“Enter”键返回计算结果，如图18-56所示。

STEP02：选中A7单元格，在编辑栏中输入公式“=NORMDIST(A2,A3,A4,FALSE)”，用于计算在上述条件下的概率密度函数值，输入完成后按“Enter”键返回计算结果，如图18-57所示。

图18-56　计算累积分布函数值

图18-57　计算概率密度函数值

如果mean参数或standard_dev参数为非数值型，函数NORMDIST返回错误值“#VALUE!”。如果参数standard_dev≤0，函数NORMDIST返回错误值“#NUM!”。如果参数mean=0，参数standard_dev=1，且参数cumulative=TRUE，则函数NORMDIST返回标准正态分布，即函数NORMSDIST。正态分布密度函数（cumulative=FALSE）的计算公式如下：

如果cumulative=TRUE，则公式为从负无穷大到公式中给定的X的积分。

Excel 计算基于给定样本估算方差：VAR函数

VAR函数用于计算基于给定样本的方差。VAR函数的语法如下：

VAR(number1,number2,...)

其中，参数number1、number2……为对应于总体样本的1～255个参数。下面通过实例详细讲解该函数的使用方法与技巧。

假定某工厂仅生产了10种产品，取样为随机样本进行抗断强度方差的检验。打开“VAR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-53所示。具体的检验步骤如下。

选中A13单元格，在编辑栏中输入公式“=VAR(A2:A11)”，用于计算工具抗断强度的方差，输入完成后按“Enter”键返回计算结果，如图18-54所示。

图18-53　原始数据

图18-54　计算方差结果

函数VAR假设其参数是样本总体中的一个样本。如果数据为整个样本总体，则应使用函数VARP来计算方差。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算包含引用中的逻辑值和代表数字的文本，则需要使用VARA函数。函数VAR的计算公式如下：

其中，x为样本平均值AVERAGE(number1,number2,…)，n为样本大小。

Excel 计算基于整个样本总体方差：VARPA函数

VARPA函数用于计算基于整个样本总体的方差。VARPA函数的语法如下：

VARPA(value1,value2,...)

其中，参数value1、value2……为对应于样本总体的1～255个参数。下面通过实例详细讲解该函数的使用方法与技巧。

假定某工厂仅生产了10种产品，取样为随机样本进行抗断强度方差的检验。打开“VARPA函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-51所示。具体的检验步骤如下。

选中A13单元格，在编辑栏中输入公式“=STDEV(A2:A11)”，用于计算全部工具抗断强度的方差（假定仅生产了10件工具），输入完成后按“Enter”键返回计算结果，如图18-52所示。

图18-51　原始数据

函数VARPA假设其参数为样本总体。如果数据代表的是总体的一个样本，则必须使用函数VARA来计算方差。参数可以是下列形式：数值；包含数值的名称、数组或引用；数字的文本表示；或者引用中的逻辑值，例如TRUE和FALSE。逻辑值和直接键入参数列表中代表数字的文本被计算在内。包含TRUE的参数作为1来计算；包含文本或FALSE的参数作为0（零）来计算。如果参数为数组或引用，则只使用其中的数值。数组或引用中的空白单元格和文本值将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算不包括引用中的逻辑值和代表数字的文本，则需要使用VAR函数。函数VARPA的计算公式如下：

图18-52　计算总体标准偏差

其中，x是样本平均值AVERAGE(value1,value2,…)且n是样本大小。

Excel 估算基于样本标准偏差：STDEV函数

STDEV函数用于估算基于样本的标准偏差。标准偏差反映数值相对于平均值（mean）的离散程度。STDEV函数的语法如下：

STDEV(number1,number2,...)

其中，参数number1、number2……为对应于总体样本的1～255个参数。也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用的形式。下面通过实例详细讲解该函数的使用方法与技巧。

某工厂有10种产品在制造过程中是由同一台机器制造出来的，并取样为随机样本进行抗断强度的标准偏差检验。打开“STDEV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-49所示。具体的检验步骤如下。

选中A13单元格，在编辑栏中输入公式“=STDEV(A2:A11)”，用于计算抗断强度的标准偏差，输入完成后按“Enter”键返回计算结果，如图18-50所示。

图18-49　原始数据

图18-50　计算标准偏差

函数STDEV假设其参数是总体中的样本。如果数据代表全部样本总体，则应该使用函数STDEVP来计算标准偏差。如果要使计算包含引用中的逻辑值和代表数字的文本，则需要使用STDEVA函数。此处标准偏差的计算使用“n-1”方法。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。函数STDEV的计算公式如下：

其中x为样本平均值AVERAGE(number1,number2,…)，n为样本大小。

Excel 计算偏差平方和：DEVSQ函数

DEVSQ函数用于计算数据点与各自样本平均值偏差的平方和。DEVSQ函数的语法如下：

DEVSQ(number1,number2,...)

其中，参数number1、number2……为1～255个需要计算偏差平方和的参数，也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用的形式。下面通过实例详细讲解该函数的使用方法与技巧。

打开“DEVSQ函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-47所示。工作表中已经给出了一组数据，要求计算出数据点与各自样本平均值偏差的平方和。具体的操作步骤如下。

选中A10单元格，在编辑栏中输入公式“=DEVSQ(A2:A8)”，用于计算上面数据点与各自样本平均值偏差的平方和，输入完成后按“Enter”键返回计算结果，如图18-48所示。

图18-47　原始数据

图18-48　计算偏差平方和

参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。偏差平方和的计算公式为：