Ecel 财务应用 多元线性回归预测.docxVIP

Excel 财务应用 多元线性回归预测 在市场的经济活动中，经常会遇到某一市场现象的发展和变化取决于几个影响因素的情况，也就是一个因变量和几个自变量有依存关系的情况。而且有时几个影响因素主次难以区分，或者有的因素虽属次要，但也不能略去其作用。 例如，某一商品的销售量既与人口的增长变化有关，也与商品价格变化有关。这时采用一元回归分析预测法进行预测是难以奏效的，需要采用多元回归分析预测法。 1．多元线性回归预测法简介 多元回归分析预测法，是指通过对两上或两个以上的自变量与一个因变量的相关分析， 建立预测模型进行预测的方法。当自变量与因变量之间存在线性关系时，称为多元线性回归 分析。 多元线性回归模型与一元线性回归模型一样，在计算出回归模型之后，要对模型进行各种检验。 多元线性回归模型的检验方法有：判定系数检验（ R 检验），回归系数显著性检验（ T检验），回归方程显著性检验（F 检验）。 判定系数检验 多元线性回归模型判定系数的定义与一元线性回归分析类似。判定系数R 的计算公式为： R=R 接近于 1 表明Y 与 X1，X2，…，Xk 之间的线性关系程度密切；R 接近于 0 表明Y 与 X1， X2，…，Xk 之间的线性关系程度不密切。 回归系数显著性检验 在多元回归分析中，回归系数显著性检验是检验模型中每个自变量与因变量之间的线性 关系是否显著。显著性检验是通过计算各回归系数的t 检验值进行的。在多元回归模型中， 某个变量回归系数的t 检验没有通过，说明该变量与因变量之间不存在显著的线性相关关系， 在回归分析时就可以将该变量删去，或者根据情况作适当的调整，而后用剩下的自变量再进 行回归分析。 回归方程的显著性检验 回归方程的显著性检验是检验所有自变量作为一个整体与因变量之间是否有显著的线 性相关关系。显著性检验是通过 F 检验进行的。回归方程的显著性检验未通过可能是选择自变量时漏掉了重要的影响因素，或者是自变量与因变量间的关系是非线性的，应重新建立预测模型。 建立预测模型 输入数据某厂的产品在 10 个地区销售，各地区人口数平均每户总收入等于有关资料不同。试求销售量关于人数及每户总收入的回归方程。例如，在工作表中，建立预测模型，如图 8-49 输入 数据 图 8-49 线性预测模型 回归分析数据 单击【分析】组中的【数据分析】按钮，在弹出的对话框中，选择【回归】选项，单击 【确定】按钮后，弹出【回归】对话框。 然后设置【Y 值输入区域】为“$B$3:$B$12”；【X 值输入区域】为“$C$3:$D$12”；选择 回归分设置析结果设置启用【输出区域】单选按钮，并设置该区域为“$A$15”；分别启用【残值 回归分 设置 析结果 设置 启用 图 8-50 设置回归参数 图 8-51 回归分析结果单击【确定】按钮，得到如图 8-51 所示的回归分析结果。 在执行上述操作后，同时也得到了线性拟合图，如图8-52 和图 8-53 所示。 图 8-52 变量 X1 线性拟合图 图 8-53 变量 X2 线性拟合图 预测数据 根据以上得到的回归分析数据，可以认为该回归模型为一个较优良的回归模型，可以用来预测。 计算结果将人口数为 450000 人，每户总收入 4800 元，代入到回归式子中即可，在单元格“销售量 Y（箱）”所对应的单元格中，输入“=B32*C36+B33*C37+B31”公式，即可得出预测值为 272 箱，如图 8-54 计算 结果 图 8-54 计算预测值 计算出的“销售量 计算出的“销售量 Y（箱）”值不是整数，在实际工作中，销售箱数不可能为半箱，此时，用户可以选择该单元格，单击【数字】组中的【对话框启动器】按钮。然后在弹出的【设置单元格格式】对话框中，选择【数值】选项卡，并设置【小数位数】为0。