基于Excel的最优子集多元线性回归预测模型设计

/CHINA MANAGEMENT INFORMATIONIZATION 1引言 回归分析预测法有多种类型。 依据相关关系中自变量的个 数不同进行分类， 可分为一元回归分析预测法和多元回归分析 预测法。 在一元回归分析预测法中，自变量只有一个，而在多元 回归分析预测法中，自变量有两个以上。 依据自变量和因变量之 间的相关关系不同，可分为线性回归预测和非线性回归预测。 多元线性回归在诸多学科中有广泛应用。 在多元线性回归 的实际应用中，考虑的自变量Xi（i 1，2，m，m为自变量数目 或个数） 经常包括所有可能影响因变量Y的因素。 在众多的Xi 中，有的对Y有显著影响，有的影响很小甚至基本无影响。 如果 把对Y影响小的Xi保留在回归模型中， 不仅增加收集数据和分 析数据的负担，使得回归方程不稳定，而且会因Xi的数目过多而 不便于使用。 因此，自变量选择在理论上和应用上都十分重要。 自变量选择通常采用的方法是最优子集法，即选出全局“最优” 回归模型。 该法是对自变量各种不同的组合所建立的回归方程 进行比较，进而从全部组合中挑出一个“最优”回归方程。 挑选 “最优”回归模型的方法一般有R2法、校正R2法、残差均方和或 剩余标准差最小法、Cp统计量法、AIC、BIC及AICC信息量准则 等。 回归分析预测法的一般步骤为： 1.1根据预测目标，确定自变量和因变量 明确预测的具体目标，也就确定了因变量。 如预测具体目标 是下一年度的销售量，那么销售量Y就是因变量。 通过市场调查 和查阅资料，寻找与预测目标的相关影响因素，即自变量，并从 中选出主要的影响因素。 1.2建立回归预测模型 依据自变量和因变量的历史统计资料进行计算， 在此基础 上建立回归分析方程，即回归分析预测模型。 1.3进行相关分析 回归分析是对具有因果关系的影响因素（自变量）和预测对 象（因变量）所进行的数理统计分析处理。 只有当变量与因变量 确实存在某种关系时，建立的回归方程才有意义。 因此，作为自 变量的因素与作为因变量的预测对象是否有关，相关程度如何， 以及判断这种相关程度的把握性多大， 就成为进行回归分析必 须要解决的问题。 进行相关分析，一般要求出相关系数（R），以相 关系数的大小来判断自变量和因变量的相关程度。 相关系数R的绝对值的大小和相关程度如下： （1） 当R 0时，说明是零相关，所求回归系数无效。 （2） 当| R | = 1时，说明是完全相关，自变量X与因变量Y 之间的关系为函数关系。 （3） 当0 | R | 1时，说明是部分相关，绝对值越大相关程 度越高。 1.4检验回归预测模型，计算预测误差 回归预测模型是否可用于实际预测，取决于对回归预测模型 的检验和对预测误差的计算。 回归方程只有通过各种检验，且预 测误差较小，才能将回归方程作为预测模型进行预测。 1.5计算并确定预测值 利用回归预测模型计算预测值， 并对预测值进行综合分析， 确定最后的预测值。 2应用实例 某零售店将其连续18个月的流动资金、广告费、员工薪酬以 及零售额等方面的数据作了一个汇总， 这些数据显示在工作表 A2：E14单元格区域，如图1所示。该店的管理人员试图根据这些 数据找到零售额与其他3个变量之间的关系，以便进行零售额预 测并为未来的预算工作提供参考。 试根据这些数据建立回归模 型。 如果未来某月流动资金额为150万元，广告费预算为44万 元，员工薪酬总额为28万元，试根据建立的回归模型预测该月的 销售额。 图1模型原始资料 广告费 基于 Excel 的最优子集多元线性回归预测模型设计 杨 桦 （东北财经大学 职业技术学院，辽宁 大连116023） 摘要回归分析预测法是一种具体的、行之有效的、实用价值很高的常用市场预测方法。 统计学中的回归预测分析具有 普遍的实用意义，但变量之间关系分析及计算繁杂，而借助Excel可方便高效地研究其数量变动关系，完成其繁杂的计算 分析过程。 本文利用Excel的图表及数据分析工具，采用最优子集法，判断各变量间的相关程度，并在此基础上建立回归模 型进行预测，使回归预测分析的计算过程更加简单，统计预测方法更为实用。 关键词Excel； 最优子集； 多元线性回归； 预测 doi: 10. 3969/j. issn. 1673 - 0194. 2011. 18. 046 中图分类号F272.1； F224.7 文献标识码A 文章编号1673-0194（2011）18-0070-02 收稿日期 2011-07-27 作者简介杨桦（1963-），女，东北财经大学职业技术学院副教授， 硕士生导师，主要研究方向：信息管理。 中 国 管 理 信 息 化 China Management Informationization 2011年9月 第14卷第18期 Sep.，2011 Vol14，No18 70 CHINA MANAGEMENT INFORMATIONIZATION/ 3建立模型 3.1采用图表法判断各变量与零售额的线性相关关系 分别选中流动资金与零售额、广告费与零售额、薪酬与零售 额，利用图表向导建立X、Y散点图，观察是否是线性相关，在此 基础上右击散点图添加趋势线，在打开的“添加趋势线”。 对话框 中作如图2所示的设置。 结果如图3 、图 4和图5所示。 拟合系数R（相关系数）分别为：0890 5、0837 3、0709 2 ，从 中可以看出流动资金与广告费这两个自变量对零售额的影响要 大于员工薪酬收入。 3.2利用数据分析工具得出回归分析报告判断其线性关系 首先选择“工具”菜单 “加载宏”命令，在打开的“加载宏”对 话框中选中“分析工具库”；单击“工具”菜单 “数据分析”命令，在 打开的“数据分析”对话框中选中“回归”，作如图6所示的设置。 得到的流动资金回归分析报告如图7所示。 其中R为0890 521 9，截距为171398 35，斜率为11399 237， 与图表法相吻合。 按照同样的方法分别建立广告费回归分析报 告、员工薪酬回归分析报告、流动资金广告费回归分析报告、流 动资金薪酬回归分析报告、广告费薪酬回归分析报告、流动资 金广告费薪酬回归分析报告等。 3.3采用最优子集法作自变量筛选 将这些回归分析报告进行整理，如表1所示，找出调整后R2 的最大值是流动资金、广告费两个自变量。 3.4进行预测 根据流动资金、广告费回归分析报告，已知：回归方程截距 为8695，斜率1（对应流动资金）为711，斜率2（对应广告费）为 图7回归分析报告 图2添加趋势线 20.00 0.00 500.00 1 000.00 1 500.00 2 000.00 2 500.00y = 28.524x + 259.48 R2= 0.837 3 30.0040.0050.0060.0070.0080.00 图4零售额与广告费线性图 200.00150.00100.0050.00 0.00 y = 11.4x + 171.37 R2= 0.890 5 图3零售额与流动资金线性图 20.00 0.00 22.0024.0026.0028.0030.00 y = 150.48x + 2152.5 R2= 0.709 2 图5零售额与薪酬线性图 图6各参数设置 自变量集R2调整后R2 流动资金 广告费 薪酬 流动资金、广告费 广告费、薪酬 流动资金、薪酬 流动资金、广告费、薪酬 X1 X2 X3 X1、X2 X2、X3 X1、X3 X1、X2、X3 0.891 0.837 0.710 0.957 0.870 0.898 0.957 0.884 0.827 0.691 0.952 0.852 0.885 0.948 表1自变量筛选 企业管理信息化 500.00 1 000.00 1 500.00 2 000.00 2 500.00 500.00 1 000.00 1 500.00 2 000.00 2 500.00 71 /CHINA MANAGEMENT INFORMATIONIZATION 一、煤矿实现信息化的重要性 随着信息技术的飞速发展， 我国矿山信息化建设取得了很 大的进步。 信息化是实现煤矿现代化管理的重要标志。 煤矿在过去的经营中缺乏一定的规范管理盲目追求企业的 利润，忽视了现代管理技术的重要作用。 煤矿信息化建设主要包 括财务信息化管理、作业信息化管理和销售信息化管理等。 信息 化的建设是煤矿从粗放经营向集约经营过渡的重要改革， 可以 提升煤矿企业的经营管理水平，增强企业的竞争力。 煤矿信息化建设是实现煤矿安全生产和管理现代化的重要 手段。 在煤矿安全生产方面，当今许多煤矿规模小，缺乏管理经 验，煤矿安全生产问题比较突出。 加强煤矿信息化建设可以让井 下作业人员时刻与井上作业人员保持联系， 能够跟踪井下作业 人员的行踪，勘测到具体作业地点和作业情况，既加强了管理， 又对矿工的安全提供了保障。 在管理方面，通过信息化建设可以更好地进行作业管理、人 力资源管理和财务管理等，提高管理信息及时性、准确性和真实 性，为煤矿的正常经营提供保障。 二、煤矿信息化发展现状 目前，我国煤矿依然是劳动密集型产业，生产、管理的自动 化及信息化水平较低。 大中型矿井生产设备老化，小型矿井生产 技术装备水平低，信息化整体水平不高。 煤矿管理系统仍然采用 一体化的管理方式，属于封闭式的管理系统，煤矿内部各个系统 之间缺乏信息共享、信息传输和信息分布式处理的机制。 这种封 闭式的信息管理使煤矿产业忽视了市场的调控作用， 也减少了 参与市场竞争的主动性，制约了煤矿整体效益和管理水平的提高。 许多煤矿管理者不重视煤矿信息化建设， 当然也不会对信 息化建设进行投资与开发，造成煤矿信息化基础设施十分落后。 三、煤矿信息化建设的原则 安全性是煤矿信息化建设的生命线， 也是煤矿内部信息共 享的前提。很多企业因在信息化建设中对安全问题处理不当而出 现企业信息外露、客户信息丢失、内部系统遭破坏等现象。在信息 化建设中应该尽量采用具有自主知识产权的设备，支持多种数字 认证和加密方法，要能够满足电子商务网络安全性的要求。 信息化系统的可靠性在网络设计中主要体现在两个方面：一 方面是网络拓扑结构的设计， 局域网络设计采用双核心方案，网 络设计应该采用多层结构，即核心接入，在增加带宽的同时，分散 流量，减少风险。 另一方面，连接网络的设备关键部分冗余，例如 接口卡、电源、背板等部件；可实现软件的在线升级，同时不会影 响网络正常运行。 通信技术和计算机技术的发展日新月异，信息化的建设也应 该适应新技术发展的潮流， 保证煤矿管理信息系统的先进性，选 择适合煤矿特点的技术和设备， 让投资的设备能够长期发挥效 益。同时还要兼顾所采用技术的成熟性、标准性和实用性，降低由 于新技术和新设备不成熟等因素而带来的风险。 在煤矿信息化建设中还应该遵循扩展性和开放性的原则，在 网络设计时，首先要满足现有规模网络用户的需求，同时还应该 考虑煤矿企业未来业务的发展和规模的扩大。信息化建设的设备 应该采用开放技术，支持标准协议，具有良好的网络互通性，满足 网络规模扩充的需要，在公司内部采用统一的网络体系结构和网 络协议。 四、煤矿信息化人才的培养 煤矿信息化建设不仅需要良好的运营环境和支撑机制，信息 化专业人才的培养更是不可忽视的重要环节。 信息化人才的培养可以视为是煤矿在长期发展中的一项投 资，如果投资得当就会带来非常可观的效益。 信息化人才的培养包括对信息化理论知识的培养、信息化技 术的培养以及信息化安全制度教育。理论知识培养可以改变煤矿 人员对于信息化毫无概念的现状，提高思想认识；技术的培养对 于信息化现代设备的运用非常重要；安全制度教育有利于提高员 关于煤矿信息化建设的思考 杜国栋 （山西古县老母坡煤业有限公司，山西 古县042106） 摘要本文主要阐述煤矿信息化建设的重要性，分析我国煤矿信息化发展现状，提出煤矿信息化建设的原则，特别强调 了煤矿信息化建设中的人才培养问题和管理信息系统维护问题。 关键词煤矿； 信息化； 建设； 思考 doi: 10. 3969/j. issn. 1673 - 0194. 2011. 18. 047 中图分类号F270.7 文献标识码A 文章编号1673-0194（2011）18-0072-02 收稿日期 2011-07