Excel 财务应用多元线性回归预测

Excel财务应用多元线性回归预测在市场的经济活动中，经常会遇到某一市场现象的发展和变化取决于几个影响因素的情况，也就是一个因变量和几个自变量有依存关系的情况。而且有时几个影响因素主次难以区分，或者有的因素虽属次要，但也不能略去其作用。例如，某一商品的销售量既与人口的增长变化有关，也与商品价格变化有关。这时采用一元回归分析预测法进行预测是难以奏效的，需要采用多元回归分析预测法。1多元线性回归预测法简介多元回归分析预测法，是指通过对两上或两个以上的自变量与一个因变量的相关分析，建立预测模型进行预测的方法。当自变量与因变量之间存在线性关系时，称为多元线性回归分析。多元线性回归模型与一元线性回归模型一样，在计算出回归模型之后，要对模型进行各种检验。多元线性回归模型的检验方法有：判定系数检验（R检验），回归系数显著性检验（T检验），回归方程显著性检验（F检验）。 判定系数检验多元线性回归模型判定系数的定义与一元线性回归分析类似。判定系数R的计算公式为：R=R接近于1表明Y与XI,X2,，Xk之间的线性关系程度密切；R接近于0表明Y与XI，X2，Xk之间的线性关系程度不密切。 回归系数显著性检验在多元回归分析中，回归系数显著性检验是检验模型中每个自变量与因变量之间的线性关系是否显著。显著性检验是通过计算各回归系数的t检验值进行的。在多元回归模型中，某个变量回归系数的t检验没有通过，说明该变量与因变量之间不存在显著的线性相关关系,在回归分析时就可以将该变量删去，或者根据情况作适当的调整，而后用剩下的自变量再进行回归分析。 回归方程的显著性检验回归方程的显著性检验是检验所有自变量作为一个整体与因变量之间是否有显著的线性相关关系。显著性检验是通过F检验进行的。回归方程的显著性检验未通过可能是选择自变量时漏掉了重要的影响因素，或者是自变量与因变量间的关系是非线性的，应重新建立预测模型。2建立预测模型某厂的产品在10个地区销售，各地区人口数平均每户总收入等于有关资料不同。试求销售量关于人数及每户总收入的回归方程。例如，在工作表中，建立预测模型，如图8-49所示。A£DE.爭元雄性回归預测基本毀悒2地区）人口更i；丰人）母户.轻尿尻）+Bi狒130£2545C223SS皓6I131j：）52S3E：1123470F：LlliL'jSZL37j9G55532SflD二输入数据10H252拐Dt«32D|1I232舵t<27'12J：Lt423BM44H.hBil2弘“说r!_-_图8-49线性预测模型3回归分析数据单击【分析】组中的【数据分析】按钮，在弹出的对话框中，选择【回归】选项，单击【确定】按钮后，弹出【回归】对话框。然后设置【Y值输入区域】为“$B$3:$B$12”；【X值输入区域】为“$C$3:$D$12”；选择【输出区域】单选按钮，并设置该区域为'$A$15”；分别启用【残值】【标准残差】和【线Kf：iIlf怡Xft则IIII设置Office（Ei0斬工惟廿厲；！警saw1启用AECD19RSquirs0.99627djurtrdE旳遹1/ZL回归分'：jlL'l析结果124E3Ntd±別M2J回日轴f2却2錮.忧旳丄创鰹乳45S7499EL3|Z7璃赶T闽.的曲備4.旳Ei只i姐23293d息计940®T.(3CaeffldentE轩证滉產tStatF-ralil图8-51回归分析结果图8-50设置回归参数性拟合】复选框，如图8-50所示。单击【确定】按钮，得到如图8-51所示的回归分析结果。在执行上述操作后，同时也得到了线性拟合图，如图8-52和图8-53所示。图8-53变量X2线性拟合图图8-52变量X1线性拟合图4预测数据根据以上得到的回归分析数据，可以认为该回归模型为一个较优良的回归模型，可以用来预测。将人口数为450000人，每户总收入4800元，代入到回归式子中即可，在单元格“销售量Y（箱）”所对应的单元格中，输入“=B32*C36+B33*C37+B31”公式，即可得出预测值计算出的“销售量Y（箱）”值不是整数，在实际工作中，销售箱数不可能为半箱，此时，用户可以选择该单元格，单击【数字】组中的【对话框启动器】按钮。然后在弹出的【设置单元格格式】对话框中，选择【数值】选项卡，并设置【小数位数】为0。