朱珏钰

(湖南第一师范学院信息科学与工程系,湖南长沙 410205)

利用 EXCEL实现计酬分配预测的回归分析＊

朱珏钰

(湖南第一师范学院信息科学与工程系,湖南长沙 410205)

在经济管理理论和应用研究中,回归分析的应用日益广泛.通过运用一元、多元回归分析的理论及建模方法,搜集某大型公司的数据,利用分析工具库提供的回归工具进行回归分析,拟合出回归模型.同时检验模型中自变量对因变量的影响程度,利用增减法,通过不断尝试增加自变量来建立和确定最优回归模型.

回归分析;回归模型;预测;计酬分配

在现实世界中,许多现象之间客观地存在着各种各样的有机联系,这种联系经常表现为数量上的相互依存关系.例如在生产活动中,粮食的产量要受肥量、降雨量、气温等因素的影响.又如在市场经济环境下,商品的销售量与商品的价格、商品的质量以及消费者的收入水平等因素有关.回归分析预测就是从各种因素之间的因果关系出发,通过分析与预测对象相关联因素的变动趋势,推算预测对象的未来数量状态.

1 回归分析预测的原理

无论是哪一种回归模型,在建立模型时都需要计算各个变量的均值、离差、平方和等多项指标.而对回归模型进行统计检验更需要进一步将总离差平方和分解成回归平方和与残差平方和,并进行拟合优度检验、回归系数的显著性检验、自相关检验等.其运算复杂、计算工作量大.应用 EXCEL分析工具库提供的回归工具就可以快捷地完成回归分析预测所需的几乎所有的计算.采用回归分析工具的分析结果包括 SUMMRAY OUTPUT(回归汇总输出)、RESI DUAL OUTPUT(残差输出 )和 PROBAB I LITY OUTPUT(正态概率输出)三部分,其中回归汇总输出是回归结果中最重要的部分,主要包括回归统计信息 (如可决系数)、调整可决系数、标准误差和观测值数、方差分析表、回归参数信息 (如回归参数值)、回归参数 t统计量值、P-value值等.下面以某快递公司按工作时间计酬分配的回归分析预测为例来说明 EXCEL在计酬分配预测回归分析中的应用[1].

2 操作方法与步骤

设某快递公司准备制定按工作时间计酬的分配方案,人事部门随机抽取了 10名员工一个月的业务记录,计算出他们每天工作的平均行驶距离和工作时间数据.现需要通过分析行驶距离与工作时间的因果关系,建立相应的回归模型.该问题只有一个自变量——行驶距离,是典型的一元线性回归问题.应用 EXCEL“回归”分析工具建立预测模型的方法如下:

(1)将需要分析的统计数据输入到工作表中;

(2)单击“工具”菜单中的“数据分析”命令弹出“数据分析”对话框;

(3)在“分析工具”列表框中选择“回归”工具,然后单击“确定”按钮弹出“回归”对话框;

(4)Y值输入区域:在此输入对因变量数据区域的引用,该区域必须由单列数据组成.指定工作时间数据所在的单元格区域 B1:B11.X值输入区域:EXCEL将对此区域中的自变量从左到右进行升序排列.指定行驶距离数据所在的单元格区域 C1:C11.标志:如果输入区域的第一行或第一列包含标志,选此复选框.EXCEL将在输出表中生成适宜的数据标志.置信度:如果需要在汇总输出表中包含附加的置信度信息,选此复选框.在右侧的框中,输入所要使用的置信度,默认值为 95%.输出区域:指定分析结果输出到本工作表,并键入输出区域的左上角单元格地址 E1.回归分析结果中回归汇总输出(SUMMRAYOUTPUT)(如图 1所示).

在“回归统计”中给出了相关系数 R=0.8187、系数 R2=0.6702、调整后的 R2=0.6290以及标准误差 =1.0472等回归统计结果,说明该回归模型拟合优度较好.在“方差分析”结果中给出了 F检验值16.2564,说明回归效果显著.在最下方的回归模型区域给出了回归系数的检验结果,自变量 P-value=0.003<0.05,通过了显著性检验.最后建立的一元回归模型为:y=1.9578+0.0434x.

图1

应用上述回归模型,即可根据给定的行驶距离计算出大致的工作时间.在 B18单元格输入上述模型的计算公式“=F17+F18＊C18”.其中 F17和F18单元格中分别为回归分析计算出的一元线性回归模型的截距和斜率.C18为指定自变量数值的单元格,只要在 C18单元格输入指定的行驶距离即可得到模型预测的工作时间.例如指定行驶距离为 90公里,相应的预测结果 B18=5.86631.

进一步分析上述“回归统计”结果可知,该回归模型的判定系数只有 0.6702,并不十分理想,这说明行驶距离这一因素对工作时间的变动影响大约只占 67%.还需要考虑是否有其他的因素被忽略了.经过分析发现,员工每天承担的业务次数对工作时间也有重要的影响,所以增加了一个自变量业务次数,然后重新建立回归模型.因为有两个自变量,所以这属于多元回归模型,具体来说是二元回归模型.多元回归分析的操作步骤与一元回归类似.有关的统计数据及多元回归分析结果如图 2所示.

图2

对于多元回归来说,R为复相关系数,判定系数R2也应该使用调整后的 R2.因为在多元回归模型中,R2会随着模型自变量个数的增加而增加,出现虚高的现象.另外,多元回归模型还应进行回归方程的显著性检验,即自变量和因变量总体之间的线性关系是否显著,这主要是通过“方差分析”结果中的 F检验值来判断.比较两个模型,可以看出二元回归模型的各项指标比一元回归模型有了很大的改善,说明模型更加科学、合理,预测也更为准确.最后建立的二元回归模型为:y=-0.0572+0.0357x1+0.9268x2.

应用该回归模型,即可根据给定的行驶距离和业务次数计算出大致的工作时间.在 B19单元格输入上述模型的计算公式“=G17+G18＊C19+G19＊D19”.其中 G17、G18和 G19单元格中分别为回归分析计算出的二元线性回归模型的 3个系数.C19为指定第一个变量行驶距离的单元格,D19为指定第二个自变量业务次数的单元格.只要在 C19和D19单元格输入指定的行驶距离和业务次数,即可得到模型预测的工作时间.例如指定行驶距离为90公里,业务次数为 3次,相应的预测结果 B19=5.93911[2].

回归分析是统计学中的精华,也是最为实用之处,被广泛地应用于自然科学和社会科学的研究及商业统计分析中,而 EXCEL提供的各类函数及回归工具能帮助用户快速地实现各种类型的回归分析,并在此基础上进行预测.回归分析最为常用且普遍的方法是采用回归分析工具方法,熟练地掌握回归分析工具的应用,并能够正确利用分析的输出结果,将会给人们的工作提供巨大的帮助.

[1]EXCEL Home.EXCEL数据处理与分析[M].北京:人民邮电出版社,2008.

[2]钟晓鸣,等.EXCEL在统计分析中的应用 [M].北京:科学出版社,2009.

TP317.3

A

1008-4681(2010)02-0071-02

2010-03-24

朱珏钰(1980-),女,湖南长沙人,湖南第一师范学院信息科学与工程系讲师.研究方向:计算机科学与技术.

(责任编校:小蒙)