实例}Excel中的有关预测函数及其应用..docxVIP

Excel提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：线性模型：y?=?mx?+?b?或?y?=?m1x1?+?m2x2?+ …+?b指数模型：或式中，y为因变量；x是自变量；m、m1、...、mn-1、mn、b分别为预测模型的待估计参数。Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、GROWTH函数、FORECAST函数、SLOPE函数和INTERCEPT函数，它们所使用的参数都基本相同，现列于表4-1中，以供参考。表4-1??预测函数的参数及含义参数含义known_ys?因变量y的观测值集合known_xs?自变量x的观测值集合。它可以是一个变量（即一元模型）或多个变量（即多元模型）的集合。如果只用到一个变量，只要?known-ys?和?known-xs?维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，known_ys?必须是向量（也就是说，必须是一行或一列的区域）。如果省略?known_xs，则假设该数组是?{1,2,3...}，其大小与?known_ys相同const逻辑值，指明是否强制使常数b为0（线性模型）或为1（指数模型）。?如果const?为?TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0（线性模型）或设为1（指数模型）stats逻辑值，指明是否返回附加回归统计值。?如果?stats?为?TRUE，则函数返回附加回归统计值，这时返回的数组为?{mn，mn-1，...，m1，b；sen，sen-1，...，se1，seb，r2，sey；F，df；ssreg，ssresid}。如果?stats为FALSE或省略，函数只返回系数预测模型的待估计参数m、mn、mn-1、...、m1和b。附加回归统计值返回的顺序见表4-2。表4-2中的各参数说明见表4-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出表4-2??附加回归统计值返回的顺序?1234561mnmn-1…m2m1b2sensen-1…se2se1se?b3r2sey????4Fdf????5ssregssresid????表4-3??各参数说明参数说明se1,se2,...,sen系数?m1,m2,?...,mn?的标准误差值Seb常数项?b?的标准误差值（当?const?为?FALSE时，seb?= #N/A）??参数说明r2相关系数，范围在?0?到?1?之间。如果为?1，则样本有很好的相关性，Y?的估计值与实际值之间没有差别。反之，如果相关系数为?0，则回归方程不能用来预测?Y?值seyY?估计值的标准误差FF?统计值或F?观察值。使用F?统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找?F?临界值。所查得的值和函数LINEST?返回的F统计值的比值可用来判断模型的置信度ssreg回归平方和ssresid残差平方和LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为= LINEST（known_ys，known_xs，const，stats）下面举例说明LINEST函数的应用。1．一元线性回归分析LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。当只有一个自变量?x?（即一元线性回归分析）时，可直接利用下面的公式得到斜率和?y?轴的截距值以及相关系数：斜率：INDEX（LINEST（known_ys，known_xs）,1,1）；或INDEX（LINEST（known_ys,known_xs）,1）截距：INDEX（LINEST（known_ys，known_xs）,1,2）；或INDEX（LINEST（known_ys,known_xs）,2）相关系数：INDEX（LINEST（known_ys,known_xs,true,true）,3,1）【例4-1】某企业1～9月份的总成本与人工小时及机器工时的数据如图4-1所示。假设总成本与人工小时之间存在着线性关系，则在单元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”，在单元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”，在单元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”，即得总成本与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X1，相关系数为R2=0.99801，如图4-1所示。图4-1??一元线性回归分析2．多