第14章 其他函数及统计功能举例

• • •
• •
• • •
14.2.3 指数回归分ຫໍສະໝຸດ Baidu的预测型函数
• • • • • • • • 在进行指数回归分析时，可以利用GROWTH函数对一元或多元指数回归分析 分析的模型进行预测值求解，其具体使用语法如下： GROWTH(known_y's,known_x's,new_x's,const) 参数known_y's满足指数回归拟合关系的一组已知的y值。 如果参数known_y's在单独一列中，则参数known_y's的每一列被视为一个 独立的变量。 如果参数known_y's在单独一行中，则参数known_y's的每一行被视为一个 独立的变量。 如果参数known_y's中的任何数为零或为负数，GROWTH函数将返回错误值 #NUM!。 参数known_x's满足指数回归拟合关系的一组已知的x值，为可选参数。 参数known_x's可以包含一组或多组变量。如果仅使用一个变量，那么只 要参数known_x's和数组known_y's具有相同的维数，则它们可以是任何形 状的区域。如果用到多个变量，则参数known_x's必须为向量（即必须为 一行或一列）。 如果省略参数known_x's，则假设该数组为{1,2,3,…}，其大小与参数 known_y's相同。 参数new_x's为需要通过GROWTH函数返回的对应y值的一组新x值。
14.3 多元统计分析举例——判别分析
• 样品分类完毕以后，如果有新的样品加入，就需 要判断此样品的归属问题，判别该样品具体归属 于哪一类，此时就需要运用判别分析的方法，本 节就介绍判别分析的基本知识和判别分析在Excel 中的实现方法。
14.3.1 判别分析的基本知识
• 同聚类分析一样，判别分析内容很丰富，判别的 原则也多种多样，判别方法也很多，本小节就对 判别分析进行最基本的介绍。 • 1、判别分析的种类 • 2．判别分析的准则 • 3．常用的判别方法
• • • • •
14.1.3 多元指数回归模型估计参数的函数
• • • • • • • 多元指数回归模型主要针对的是如下模型样式： 在Excel中，可以利用函数LOGEST来求解上述模型中b0，b1，…,bn，其使用语法与函数LINEST类似， 具体内容如下： 参数known_y's是模型表达式中已知的y值集合。 如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变量。 如果数组known_y's在单独一行中，则known_x's的每一行被视为一个独立的变量。 参数known_x's是模型表达式中已知的可选X值集合。 数组known_x's可以包含一组或多组变量。如果仅使用一个变量，那么只要known_x's和known_y's 具有相同的维数，则它们可以是任何形状的区域。如果用到多个变量，则known_y's必须为为一行 或一列。 如果省略known_x's，则假设该数组为{1，2，3，…}，其大小与known_y's相同。 参数const为一逻辑值，用于指定是否将常数项b0强制设为1。 如果const为TRUE或省略，b0将按正常值计算。 如果const为FALSE，b0将被设为1，并同时调整模型关系式的设置。 参数stats为一逻辑值，指定是否返回附加回归统计值。 如果参数stats设为TRUE，则函数LOGEST将返回附加回归统计值，此时函数返回的数组形式为如表 14.1所示。
• •
14.3 多元统计分析举例——聚类分析
• 在统计分析中，很多情况下不仅仅是分析变量之间的相关 关系，在多元统计分析中，还包括判别分析、聚类分析、 主成分分析、对应分析、因子分析等内容。在Excel中虽然 不能进行非常复杂的多元统计分析，但一些比较常用的如 聚类分析、判别分析等内容还是能够解决的，本节就以聚 类分析为例，介绍Excel的多元统计分析方法。 • 在实际应用的各个领域中都存在着大量分类问题，因此聚 类分析这个有用的数学工具越来越受到人们的重视，它在 许多领域中都得到了广泛的应用。聚类分析又称为群分析， 它是研究样品或指标分类问题的一种多元统计方法。聚类 分析种类和方法很多，根据不同的原理，可以分为系统聚 类法、有序样品聚类法、动态聚类法、模糊聚类法、图论 聚类法、聚类预报法等，本节主要介绍常用的并且能够在 Excel中实现的系统聚类法。
14.2.2 多元线性回归分析中的预测型函数
• • • • • • • 要求解多元线性回归模型的预测值，在Excel中，可以使用TREND函数，其使用语法同样比 较复杂，具体内容如下： TREND(known_y's,known_x's,new_x's,const) 参数known_y's是关系表达式Y=XB中已知的Y值集合。 如果参数known_y's在单独一列中，则参数known_x's的每一列被视为一个独立的变量。 如果参数known_y's在单独一行中，则参数known_x's的每一行被视为一个独立的变量。 参数known_x's是关系表达式Y=XB中已知的可选X值集合。 参数known_x's可以包含一组或多组变量。如果仅使用一个变量，那么只要参数known_x's 和参数known_y's具有相同的维数，则它们可以是任何形状的区域。如果用到多个变量，则 参数known_y's必须为向量（即必须为一行或一列）。 如果省略known_x's，则假设该数组为{1,2,3,…}，其大小与参数known_y's相同。 参数new_x's为需要函数TREND返回对应y值的新x值。 参数new_x's与参数known_x's一样，对每个自变量必须包括单独的一列（或一行）。因此， 如果参数known_y's是单列的，参数new_x's与参数known_x's应该有同样的列数。如果参数 known_y's是单行的，参数new_x's与参数known_x's应该有同样的行数。 如果省略参数new_x's，将假设它和参数known_x's一样。 如果参数new_x's与参数known_x's都省略，将假设它们为数组{1,2,3,…}，其大小与参数 known_y's相同。 参数const为一逻辑值，用于指定是否将常量b0强制设为0。 如果参数const为TRUE或省略，b0将按正常计算。 如果参数const为FALSE，b0将被设为0。
14.4 本章小结
• 本章介绍了Excel中一些回归分析中的函数用法， 并详细介绍了聚类分析和判别分析在Excel中的实 现过程，是对之前统计分析知识的补充和扩充， 同时引领读者在Excel探索更多求解问题的方法， 以提高其实用性。
14.3.2 判别分析应用举例
• 根据上述基本原理，可知利用距离判别法进行判 别分析，最终是要求出判别函数，以将检测的污 染物根据三种指标进行判别为例，具体方法可以 分为原始数据处理、估算求解的参数、求解判别 函数、对待判定样品进行分类和结果检验等5步， 本小节就详细介绍这些步骤在Excel中的实现。 • 1．原始数据处理 • 2．估算求解的参数 • 3．求解判别函数 • 4．对待判定样品进行分类 • 5．结果检验
• • • • • •
14.2 Excel中自带的回归分析中预测型函数
• 同估计参数的函数一样，预测型的函数同样可以 分为3类，分别是针对一元线性回归分析、多元线 性回归分析和指数回归分析的预测型函数，在 Excel中，在需要进行预测使，可以省略建立模型 一步，直接利用预测型函数进行相关预测，本节 将进行详细介绍，
14.3.2 基本聚类方法简介
• 正如样品之间的距离可以有不同的定义方法一样，类与类 之间的距离也有各种定义。例如可以定义类与类之间的距 离为两类之间最近样品的距离，或者定义为两类之间最远 样品的距离，也可以定义为两类重心之间的距离等等。类 与类之间用不同的方法定义距离，就产生了不同的系统聚 类方法。常用的有八种系统聚类方法分别为：最短距离法、 最长距离法、中间距离法、重心法、类平均法、可变类平 均法、可变法、离差平方和法。在本小节中，分别用dij表 示样品Xi与Xj之间距离，用Dij表示类Gi和类Gj之间的距离， 介绍其中几种比较常用的聚类方法 • （1）最短距离法。 • （2）最大距离法。
14.2.1 一元线性回归分析中的预测型函数
• 要求解一元线性回归模型的预测值，在Excel中， 可以使用FORECAST函数，其具体使用语法如下： • FORECAST(x,known_y's,known_x's) • 参数x是需要进行预测的数据点。 • 参数known_y's为因变量数组或数据区域。 • 参数known_x's为自变量数组或数据区域。
14.3.1 聚类分析的依据
• 在聚类分析中，对数据进行聚类，需要将关系密切的数据聚为一 类，也需要将关系不密切的数据分到不同的类中，要衡量数据间 的关系密切程度，一般的方法是利用不同的距离进行表示，本节 重点介绍距离的定义和分类。 1．聚类依据概述
•
• • • •
2．不同距离的定义 距离的定义很多，这里只介绍比较常用的几种： （1）针对样品分类，这种分类也称为Q-型聚类分析。 （2）对指标分类，也称为R-型聚类分析。
14.2.3 聚类分析应用举例
• 针对相同的数据，可以进行不同方法的聚类分析， 本节以对7个公司的规模和效益样本进行聚类为例， 计算两者标准偏差和平均值，具体内容如图14.33 所示，为了消除量纲影响，先将数据标准化，结 果如图14.34所示。下面采用上述3种不同的聚类 方法进行聚类，对不同方法的特点进行介绍。
第14章 其他函数及统计功能举例
• 除了之前介绍的各种函数和统计分析功能，Excel 中还有众多的函数统计分析功能可以使用，本章 就选取了几个有代表性的内容加以介绍，以完善 之前所述的统计分析内容。
14.1 Excel中自带的回归分析估计参数的函数 • 由于回归分析的应用较为广泛，因此，在Excel自 带的各种统计函数中，本节选取与回归分析有关 的几个函数进行介绍，以使对回归分析的介绍更 加完善。 • 回归分析的主要任务之一就是要对参数进行估计。 不论是一元回归分析还是多元回归分析，线性回 归分析还是非线性回归分析，都需要对参数进行 估计，本小节就详细介绍能够对不同回归分析模 型进行预测的不同函数。
14.1.1 一元线性回归分析中的参数估计函数
• 这里所说的参数估计包括三个部分，分为回归分析中的截 距、斜率和依据一元线性回归模型得到的预测值的标准误 差。举例说明，如要分析体重与身高简单的一元线性回归 关系，利用函数求解其参数的具体方法如下：
14.1.2 多元线性回归中估计参数的函数
• • • • • • • 多元线性回归中的参数估计实际上是对多元回归系数的求解，在Excel中，可以 使用函数LINEST直接进行求解，其使用语法比较复杂，具体内容如下： LINEST(known_y's,known_x's,const,stats) 参数known_y's是关系表达式Y=XB中已知的Y值集合。 如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变 量。 如果数组known_y's在单独一行中，则known_x's的每一行被视为一个独立的变 量。 参数known_x's是关系表达式Y=XB中已知的可选X值集合。 数组known_x's可以包含一组或多组变量。如果仅使用一个变量，那么只要 known_x's和known_y's具有相同的维数，则它们可以是任何形状的区域。如果 用到多个变量，则known_y's必须为一行或一列。 如果省略known_x's，则假设该数组为{1,2,3,…}，其大小与known_y's相同。 参数const为一逻辑值，用于指定是否将常数项b0强制设为0。 如果const为TRUE或省略，b0将按正常值计算。 如果const为FALSE，b0将被设为0，并同时调整Y=XB的设置。 参数stats为一逻辑值，指定是否返回附加回归统计值。