《{实例}Excel中的有关预测函数及其应用.doc》由会员分享，可在线阅读，更多相关《{实例}Excel中的有关预测函数及其应用.doc（10页珍藏版）》请在三一文库上搜索。
1、线性模型和指数模型的数Excel提供了关于估计线性模型和指数模型参数的几个预测函数。 学表达式如下： 线性模型:y = mx+ b 或 y = mxi + mx2 + , + b指数模型:式中，y为因变量；x是自变量；m m、.、m-i、m、b分别为预测模型的待估计参数。Excel提供的预测函数主要有 LINEST函数、LOGES函数、TREND函数、GROWTH数、FORECAST 函数、SLOPE!数和INTERCEPT®数，它们所使用的参数都基本相同，现列于表4-1中，以供参考。表4-1预测函数的参数及含义参数含义known y's因变量y的观测值集合自变量x的观测值集
2、合。匕可以是一个变量（即一兀模 型）或多个变量（即多元模型）的集合。known _x's如果只用到一个变量，只要known-y's 和known -x's 维数相同，它们可以是任 何形状的选定区域。如果用到不只一个变量，known_y's必须是向量（也就是说，必须是一仃或一列 的区域）。如果省略known_x's，则假设该数组是1,2,3，其大小与known y's相同const逻辑值，指明是否强制使常数b为0 （线性模型）或为1 （指数模型）。 如果con st为TRUE或省略，b将被正常计算。如果const为FALSE b将被设为0 （线性模
3、型） 或设为1 （指数模型）stats逻辑值，指明是否返回附加回归统计值。如果stats 为TRUE则函数返回附加回归统计值，这时 返回的数组为mn，m-1，m，b； sen， sen-1， se1， seb，r2，sey； F，df; sseg，ssid。如果 stats 为 FALSE 或省略，函数只返回系数预测模型的待估计参数m m、m-1、m 和 bo附加回归统计值返回的顺序见表4-2。表4-2中的各参数说明见表4-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出表4-2 附加回归统计值返回的顺序1234561mnmn-iJm2mr b2sensen-iJse2sei
4、se b32 rsey4:F:df5SSregSSresid表4-3 各参数说明参数说明se1,se 2,se n系数m1,m2, ,mn的标准误差值S&常数项b的标准误差值（当con st为FALSE时， seb = #N/A）参数说明2 r相关系数，范围在0到1之间。如果为1，则样本 有很好的相关性，丫的估计值与实际值之间没有差别。反之，如果相关系数为0 ,则回归方程不能用来预 测丫值sey丫估计值的标准误差FF统计值或F观察值。使用F统计可以判断因变量和 自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找F临界值。所查得的值 和函数LINEST返回的F统计值的比值
5、可用来判断模 型的置信度SSreg回归平方和ss resid残差平方和LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返 回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为=LINEST (known_y's , known_x's , const , stats ) 下面举例说明LINEST函数的应用。1一元线性回归分析LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自 回归分析。当只有一个自变量 x (即一元线性回归分析) 时，可直接利用下面的公式得到斜率和y轴的截距值
6、以及相关系数: 斜率：INDEX( LINEST( known _y's，kn ow n_x's),1,1 );或 INDEX ( LINEST(known_y's,known_x's ) ,1)截距：INDEX( LINEST( known _y's，kn ow n_x's),1,2 );或 INDEX ( LINEST(known_y's,known_x's ) ,2)相关系数：INDEX ( LINEST (known_y's,known_x's,true,true) ,3,1 )【例4-1】某企业19月份的
7、总成本与人工小时及机器工时的数据如图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) ”，即得总成本与人工小时的一元线性2 .回归分析方程为：Y=562.72756+4.41444X 1，相关系数为 R=0.99801，如图4-1所示。图4-1一元线性回归分析2 多元线性回归分
8、析仍以例4-1的数据为例，首先选取单元格区域A17:D21，再以数组公式方式输入公式“=LINEST（B2: B10,C2: D10,TRUE,TRUE”，即得该二元线性回归的有关参数如图4-2所示,从而得到：A1BCE113 *頁制號1NZ!8t0. X77S&O010 3KB9BSTii minroZU2832的俶讪图4-2二元线性回归分析回归方程：Y = 471.4366+3.6165X 1+3.4323X2相关系数：R =0.9990标准差：Sey =11.7792。4.3.2 LOGEST 函数LOGESTi数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返
9、回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGESTi数的公式为=LOGEST（k nown _y's,k nown _x's,c on st,stats）【例4-2】某企业12个月某产品的生产量 （X）与生产成本（Y）的有关资料如图4-3所示,假设它们之间有如下关系：。选取单元格区域 B15: C18,输入公式“=LOGEST（C2C13,B2: B13,TRUE,TRUE”（数组公式输入），即得回归参数，如图4-3所示，参数m=0.8887,参数b=1891.7729，生产成本与生产量的回归曲线为：Y=1791.7729 X 0.8887 ,相
10、关系数R2=0.95885。GROWT函数的功能是返回给定的数据预测的指数增长值。根据已知的x值和y值，函数GROWTH回一组新的x值对应的y值。可以使用GROWTH作表函数来拟合满足给定x值和y值的指数曲线。GROWT函数的公式为 =GROWTH(k now n_y's,k nown _x's ,n ew_x's,co nst) 式中，各参数的含义同TREND函数。但需注意的是，如果known_y's中的任何数为零或为负， 函数GROWTH返回错误值#NUM!。【例4-4】以例4-3的资料为例，利用 GROWT函数预测来年的1、2、3月的销售量。预测 步骤为：
11、选中单元格区域B1: B3,输入公式“ =GROWTH(AA12,13;14;15)” (数组公式输入)，即得来年的1、2、3月份的销售量分别为 756、811和870。这个公式同样默认 1;2;3;4;5;6;7;8;9;10;11;12作为 known_x's 的参数，故数组13;14;15就对应后面的 3个月份。4.3.5 FORECAST 函数FORECAS函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。FORECAS函数的计算公式为a+bx式中，心卩-用；“吃汗-q&a
12、mp;w吃。FORECAS函数的公式为=FORECAST(x,k nown _y's,k nown_x's)式中x需要进行预测的数据点。需要说明的是：如果x为非数值型，函数 FORECAST返回错误值#VALUE!。如果kn own_y's 和kn own_x's 为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。如果known_x's 的方差为零，函数 FORECAST返回错误值#DIV/0!。例如：F0RECAST(30,6,7,9,15,21,20,28,31,38,40)= 10.60725 。4.3.6 SLOPE 函数SLO
13、PE函数的功能是返回根据kn own_y's 和kn own_x's 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE!数的公式为=SLOPE(k nown _y's,k now n_x's)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果known_y's和known_x's 为空或其数据点数目不同，函数SLOPE返回错误值#N/A。例如：SLOPE(2,3,9,1,8
14、,7,5,6,5,11,7,5,4,4)= 0.305556 。4.3.7 INTERCEPT 函数INTERCEPT函数的功能是利用已知的x值与y值计算直线与y轴的截距。截距为穿过known_x's和known_y's 数据点的线性回归线与 y轴的交点。公式为=INTERCEPT (known _y's,k now n_x's)例如：INTERCEPT, 3, 9, 1, 8, 6, 5, 11,7, 5)= 0.0483871。图4-3指数回归回归方程的系数及相关系数也可以利用下面的公式直接计算参数 m INDEX(LOGEST(C2C13,B2: B13)
15、,1)=0.8887参数 b： INDEX(LOGEST(C2C13,B2: B13),1,2)=1791.7729相关系数 R2： =INDEX(LOGEST(C2C13,B2: B13,TRUE,TRUE),3,1)= 0.958854.3.3 TREND 函数TREN函数的功能是返回一条线性回归拟合线的一组纵坐标值（y值），即找到适合给定的数组 known_y's 和 known_x's 的直线（用最小二乘法），并返回指定数组 new_x's 值在 直线上对应的 y 值。TREN函数的公式为= TREND（known_y's,known_x's,n
16、ew_x's,const）式中 new_x's 需要函数 TREN 返回对应 y 值的新 x 值。 new_x's 与 known_x's 一样，每个独立变量必须为单独的一行（或一列）。因 此，如果 known_y's 是单列的， known_x's 和 new_x's 应该有同样的列数，如 果 known_y's 是单行的， known_x's 和 new_x's 应该有同样的行数。如果省略 new_x's ， 将假设它和 known_x's 一样。【例 4-3】某企业过去一年的销售量为下列数据：
17、 300 ， 356， 374， 410， 453， 487， 501， 534， 572， 621， 650， 670 ，将它们保存在单元格A1: A12 中，则下一年的 1、2、3月的销售量预测步骤为：选中单元格区域 B1： B3,输入公式“ =TREND（A1A12,13;14;15）”（数组公式输入），即得来年的 1、2、3月份的销售量分别为 710、743和 777。这个公式 默认1;2;3;4;5;6;7;8;9;10;11;12 作为 known_x's 的参数，故数组13;14;15 就对应其后 的 3 个月份。GROWTH函数的功能是返回给定的数据预测的指数增长值。根
18、据已知的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用 GROWTH工作表函数来拟合满足给定 x 值和 y 值的指数曲线。GROWTH函数的公式为= GROWTH（known_y's,known_x's,new_x's,const）式中，各参数的含义同 TREN 函数。但需注意的是，如果 known_y's 中的任何数为零或为 负，函数GROWTH将返回错误值 #NUM!。【例4-4】以例4-3的资料为例，利用 GROWTH函数预测来年的1、2、3月的销售量。预测 步骤为：选中单元格区域 B1:B3,输入公式“ =GROWTH（A1:A12,1
19、3;14;15） ”（数组公式输入）， 即 得来年 的 1 、 2、 3 月 份的销 售量 分别为 756、 811 和 870。这个 公式 同样默 认 1;2;3;4;5;6;7;8;9;10;11;12 作为 known_x's 的参数，故数组13;14;15就对应后面的 3个月份。4.3.5 FORECAST 函数FORECAST函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。FORECAST!数的计算公式为 a+bx式中,gp-必；办明0-近耳曲側刀卫-疔。FORECAS
20、T!数的公式为=FORECAST(x,k nown _y's,k nown_x's)式中 x需要进行预测的数据点。需要说明的是：如果x为非数值型，函数 FORECAST!回错误值#VALUE。如果known_y's和known_x's为空或含有不同数目的数据点，函数FORECAST!回错误值#N/A。如果known_x's的方差为零，函数 FORECAST返回错误值#DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40)= 10.60725。4.3.6 SLOPE 函数SLOPE函数的功能是返回根据kno wn
21、_y's和kno wn_x's中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE1数的公式为=SLOPE(k nown _y's,k now n_x's)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果known_y's和known_x's为空或其数据点数目不同，函数SLOPE返回错误值#N/A。例如：SLOPE(2,3,9,1,8,7,5,6,5,11,7,5,4,4)= 0.305556。INTERCEPT 函数INTERCEPT函数的功能是利用已知的x值与y值计算直线与 y轴的截距。截距为穿过known_x's 和 known_y's 数据点的线性回归线与 y 轴的交点。公式为= 0.0483871 。= INTERCEPT (known_y's,known_x's)例如： INTERCEPT(2, 3, 9, 1, 8, 6, 5, 11, 7, 5)