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

精品文档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-1m2m1b2sensen-1se2se1seb3r2sey4Fdf5ssregssresid表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】某企业19月份的总成本与人工小时及机器工时的数据如图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多元线性回归分析仍以例4-1的数据为例，首先选取单元格区域A17:D21，再以数组公式方式输入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”，即得该二元线性回归的有关参数如图4-2所示，从而得到：图4-2二元线性回归分析回归方程：Y = 471.4366+3.6165X1+3.4323X2相关系数：R2=0.9990标准差：Sey=11.7792。4.3.2 LOGEST函数LOGEST函数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGEST函数的公式为=LOGEST(known_ys,known_xs,const,stats)【例4-2】某企业12个月某产品的生产量（X）与生产成本（Y）的有关资料如图4-3所示，假设它们之间有如下关系：。选取单元格区域B15:C18，输入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)”（数组公式输入），即得回归参数，如图4-3所示，参数m=0.8887，参数b=1891.7729，生产成本与生产量的回归曲线为：Y=1791.77290.8887X，相关系数R2=0.95885。GROWTH函数的功能是返回给定的数据预测的指数增长值。根据已知的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用GROWTH工作表函数来拟合满足给定x值和y值的指数曲线。GROWTH函数的公式为=GROWTH(known_ys,known_xs,new_xs,const)式中，各参数的含义同TREND函数。但需注意的是，如果known_ys中的任何数为零或为负，函数GROWTH将返回错误值#NUM!。【例4-4】以例4-3的资料为例，利用GROWTH函数预测来年的1、2、3月的销售量。预测步骤为：选中单元格区域B1:B3，输入公式“=GROWTH(A1:A12,13;14;15)”（数组公式输入），即得来年的1、2、3月份的销售量分别为756、811和870。这个公式同样默认1;2;3;4;5;6;7;8;9;10;11;12作为known_xs的参数，故数组13;14;15就对应后面的3个月份。4.3.5 FORECAST函数FORECAST函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。FORECAST函数的计算公式为a+bx式中，；。FORECAST函数的公式为= FORECAST(x,known_ys,known_xs)式中x需要进行预测的数据点。需要说明的是：如果x为非数值型，函数FORECAST返回错误值#VALUE!。如果known_ys和known_xs为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。如果known_xs的方差为零，函数FORECAST返回错误值#DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40)= 10.60725。4.3.6 SLOPE函数SLOPE函数的功能是返回根据known_ys和known_xs中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数的公式为= SLOPE(known_ys,known_xs)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果known_ys和known_xs为空或其数据点数目不同，函数SLOPE返回错误值#N/A。例如：SLOPE(2,3,9,1,8,7,5,6,5,11,7,5,4,4)= 0.305556。4.3.7 INTERCEPT函数INTERCEPT函数的功能是利用已知的x值与y值计算直线与y轴的截距。截距为穿过known_xs和known_ys数据点的线性回归线与y轴的交点。公式为= INTERCEPT (known_ys,known_xs)例如：INTERCEPT(2, 3, 9, 1, 8, 6, 5, 11, 7, 5)= 0.0483871。图4-3指数回归回归方程的系数及相关系数也可以利用下面的公式直接计算参数m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887参数b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729相关系数R2：=INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.958854.3.3 TREND函数TREND函数的功能是返回一条线性回归拟合线的一组纵坐标值（y值），即找到适合给定的数组known_ys和known_xs的直线（用最小二乘法），并返回指定数组new_xs值在直线上对应的y值。TREND函数的公式为=TREND(known_ys,known_xs,new_xs,const)式中 new_xs需要函数TREND返回对应y值的新x值。new_xs与known_xs一样，每个独立变量必须为单独的一行（或一列）。因此，如果known_ys是单列的，known_xs和new_xs应该有同样的列数，如果known_ys是单行的，known_xs和new_xs应该有同样的行数。如果省略new_xs，将假设它和known_xs一样。【例4-3】某企业过去一年的销售量为下列数据：300，356，374，410，453，487，501，534，572，621，650，670，将它们保存在单元格A1:A12中，则下一年的1、2、3月的销售量预测步骤为：选中单元格区域B1:B3，输入公式“=TREND(A1:A12,13;14;15)”（数组公式输入），即得来年的1、2、3月份的销售量分别为710、743和777。这个公式默认1;2;3;4;5;6;7;8;9;10;11;12作为known_xs的参数，故数组13;14;15就对应其后的3个月份。GROWTH函数的功能是返回给定的数据预测的指数增长值。根据已知的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用GROWTH工作表函数来拟合满足给定x值和y值的指数曲线。GROWTH函数的公式为=GROWTH(known_ys,known_xs,new_xs,const)式中，各参数的含义同TREND函数。但需注意的是，如果known_ys中的任何数为零或为负，函数GROWTH将返回错误值#NUM!。【例4-4】以例4-3的资料为例，利用GROWTH函数预测来年的1、2、3月的销售量。预测步骤为：选中单元格区域B1:B3，输入公式“=GROWTH(A1:A12,13;14;15)”（数组公式输入），即得来年的1、2、3月份的销售量分别为756、811和870。这个公式同样默认1;2;3;4;5;6;7;8;9;10;11;12作为known_xs的参数，故数组13;14;15就对应后面的3个月份。4.3.5 FORECAST函数FORECAST函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。FORECAST函数的计算公式为a+bx式中，；。FORECAST函数的公式为= FORECAST(x,known_ys,known_xs)式中x需要进行预测的数据点。需要说明的是：如果x为非数值型，函数FORECAST返回错误值#VALUE!。如果known_ys和known_xs为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。如果known_xs的方差为零，函数FORECAST返回错误值#DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40)= 10.60725。4.3.6 SLOPE函数SLOPE函数的功能是返回根据known_ys和known_xs中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数的公式为= SLOPE(known_ys,known_xs)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果known_ys和known_xs为空或其数据点数目不同，函数SLOPE返回错误值#N