如何在excel上做可移动的表示斜率的直线

计算机技术已经成为推动世界经济发展的因素之一，计算机的广泛应用，对财务会计理论与实务带来了深刻影响。笔者就 Microsoft Excel在财务预测中的一些问题提出解决方案和实践体会和大家共享。

本文是以Excel 2000版为例，Excel其他版本会略有不同。

Excel功能介绍

Microsoft公司推出的Excel电子化表格处理软件，是Office套件的核心程序之一。其集表格计算、图表显示、超强内置函数、数据分析等功能于一体，被广泛应用在商务办公、财务统计、工程计算等各个领域。拥有容量大、内置功能函数丰富、多种类型制图和数据库强大等多种特点。

Excel在财务预测中的应用

方案的提出：以一元线性回归分析为例

下表是某加工企业当年的机器加工工时及对应的间接费用，根据生产预算，要求财务人员测算次年加工工时分别安排为65、69、75、78、85、91等时相应发生的间接费用。

构建数学模型

第一步：创建工作表。

启动Excel，建立一张新工作表，命名并保存。将原始数据按顺序输入工作表中，如下表所示：

第二步：作散点图并添加趋势线。

首先，将机器工时当作自变量X，将间接费用当作因变量Y，通过图表向导工具来完成图表制作：选取“插入图表”以启动图表工具向导，在“图表类型”中选取XY散点图；在“数据源区域”中选取上表中的A2：M3部份；在“图表选项”中的图表标题栏写上“散点图分析技术”，在分类X轴上填上“机器工时”，在数据Y轴上填“间接费用”；点选“作为其中的对象插入”可选项；按确定后便嵌入一个图表到工作表中。在嵌入完基础图表后如果对图表不太满意还可以双击图表，以激活图表进行相关编辑修改。

创建完图表后，就可以开始添加趋势线了，操作步骤是：用鼠标选中图表，从菜单条中选取“图表&添加趋势线”，再从弹出的选项框中选取一种最接近于现有图表的线型(Excel提供了6种常用的趋势预测曲线)，创建完后的图表如图一所示。在此可以直观地看出，数据之间的变化趋势呈线性关系。

第三步，建立数学模型。

用制作图表的方法建立数据变量之间的趋势关系后，便可以近似地建立机器工时与间接费用之间的线性模型Y=A+BX了。其中：Y表示因变量间接费用，X表示自变量机器工时，A和B是线性模型中待求解的常数。

相关性分析

数学模型的建立，并不表示其客观表达了机器工时和间接费用之间的函数关系，还需要通过一定参数对模型进行判断。这就需要用到CORREL相关分析函数，该函数是返回两组数据之间的相关系数，语法CORREL(array1,array2)，其中：array1表示为第一组数值的单元格区域，array2表示为第二组数值的单元格区域。函数运算结果实质上就是统计学中相关系数的计算公式：ρｘ?Y＝。用文字表述就是：X、Y变量的相关系数等于其协方差除以变量Ｘ和变量Ｙ的标准差。

在B4单元格中输入公式“＝CORREL(B1:M1,B2:M2)”，按回车后即可解得变量X与变量Y的相关系数为0.90，该数值以定量的方式表示出机器工时X与间接费用Y呈现的正相关关系及相关程度(1表示直线关系)，通过相关系数检验后，便可确定构建的数学模型Y＝A＋BX成立。

回归分析

数学模型Y＝A+BX已经建立好，但A与B的值如何来确定？这时，需要借助于Excel统计分析函数来解决。Excel中用于线性回归分析的函数一共有3个，在此只用其中2个函数即：Intercept、Slope。

求解系数A的函数Intercept。本函数是利用已知的X值与Y值计算回归直线与Y轴的截距，即模型中的A值。该函数语法是 Intercept?known_y’s?known_x’s?，其中：known_y’s表示为因变量的数据值集合，known_x’s表示为自变量的数据值集合。在了解该函数语法结构后，便可以准确运用此函数了。在B5单元格中输入公式“＝Intercept(b1：m1，b2：m2)”，按回车后 Excel便计算出了Intercept函数求解出值是100.86，即系数A值为100.86。

求解系数B的函数Slope。本函数是返回known＿y’s和known＿x’s中的数据点拟合的线性回归直线的斜率，即模型中的B值。该函数语法是Slope?known＿y’s?known＿x’s?，其中：known＿y’s与known＿x’s表示的意义与Intercept相同。参照上述函数使用原理，在B6单元格中输入公式“＝Slope(b1：m1，b2：m2)”，按回车后Slope函数求解值为13.30，即系数B值为 13.30。

通过上述计算，预测模型表达式￥＝100.86＋13.3?Ｘ便完全建立了。

应用模型解决方案

在完全模型表达式后，便可以将机器加工工时计划数值65、69、75、78、85、91分别输入单元格C5：C10中，在D5中输入公式：“＝＄B＄5＋C5?＄B＄6”(其中的＄表示绝对引用关系)，然后将D5单元格中的公司直接向下复制到D6至D10中，即可求解出对应于机器工时的间接费用的数值：965，1018，1098，1138，1231，1311