在线计算公式用Excel创建经验公式并进行预测

用Excel2007创建经验公式并进行预测
据1997-12-16 00:00 作者： 江苏 陈岁松杨守礼 修改
用公式表示事物与事物之间的数量关系，有两种情况：一 种是确定关系，例如圆面积S和半径r具有
如下关系，S=πr2，知道了圆半径，圆面积立即可以算出 。这样的公式可以通过几何关系进行推导。另一
种称为非确定关系，例如居民收入与商品零售额的关系， 气温与冷饮销售量的关系，吸烟量与肺癌发病率
的关系……等等，虽然我们知道它们有一定的关系，但无 法直接用数学进行推导。如果要用公式表示它们
之间的关系，只有用回归分析方法，用回归分析建立的公 式称做经验公式。
用传统的回归分析建立经验公式是很麻烦的，计算过程也极易出错，用Exce l的专用工具和专用函数
建立经验公式，就使这一繁锁的工作变得轻而易举。
具体应用， 我们将通过下面的两个例题(四种方法)来说明。这几种方法各有优点，但方法一最直观，
方法四最简单 。
例题一
某企业根据记录，企业的产量和生产费用如表1，现在需要分析产量(X)与生产 费用(Y)间的关系，并建立经
验公式。根据此公式，如果1999年的产量定为9千吨，预计生产费用 是多少?
方法一 用图形功能建立经验公式。
优点：能清晰地观察XY的几何图形 ，并直接获得经验公式，这对XY的关系毫不了解的情况特别有
利。用图形功能建立经验公式，不仅可以 处理简单的直线公式，还可以处理对数、指数、多项式、幂级数
等非线性公式，既直观又简便。
具体操作如下：
1.在Excel的一个工作表中将以上数据录入Excel。
2.选取该数据区域，点击菜单“插入”／“散点图（曲线）”／“，完成XY图形曲线。
3.用右键点击XY图形上的XY曲线，出现对话框，选“加上趋势线…”，出现“趋势线”对话框。
4.“趋势线”对话框中的曲线“类型”，有“线性”、“对数”、“多项式”、“升幂”、“指数”、“移动平 均”等6种可
供选择。根据观察，选取一种适合的线型，点“选项”，选中“显示公式”和“显示R平方 值”，然后点“确定”，
XY图形上就会自动加上你选定的曲线公式(经验公式)和R2值(参见Exc el图1)。其中“多项式”公式有“项”
数的设置，一般取2项即可满足要求。
5.R 称为相关系数，表示经验公式对原始数据拟合的程度，一般要求R2>0.8，观察经验公式和原曲线
的 拟合效果，并看R值的大小，如果曲线拟合不够理想，或R值偏低，不妨按第6步重新选择拟合公式再
作 一次比较，直到满意为止。
6.重新选择拟合公式的方法是，用右键点击XY图形的拟合曲线(曲 线稍粗)，注意不是点击原始的XY
曲线，再点“趋势线格式”。在“趋势线格式”对话框中重新选取“ 类型”和“选项”，另外选择一种曲线，以下的
工作和第4步完全一样。
本例采用“线性 ”公式较好，获得的经验公式为y=12.896x＋51.323(见Excel图1)，相关系数R2=0. 9403，
拟合情况良好。当x=9时，y=12.896×9＋51.323=167.39(万元) ，这就是该企业1999年预计的生产费用。
方法二用LINEST或LOGEST函数建立经验公式。
适用范围：在已经了解y=f(x)性 质的情况下，直接获取经验公式的有关参数。LINEST用于线性公式，
LOGEST用于指数曲线公 式，使用这两个函数可以解决多元回归的复杂问题。
LINEST和LOGEST的语法相同：L INEST(变量Y，自变量X，Const，Stats)。“变量Y”是Y数据区
域；“自变量X” 是X数据区域，X可以是一个变量X1，也可以是多个变量(多元回归)X1、X2、……Xn；“Const”
是截距处置，设置为1或省略时，截距值正常计算，设置为0时，截距按0计算；“Stats”是统计 参数处置，
设置为1时，返回全部统计参数，设置为0或省略时，只返回公式y=mnxn＋…m2x2 ＋m1x1＋b的有关系
数mn、…m2、m1、b等。返回的有关参数在Excel图格上的排列规则 如表2。
有关统计参数的详细解释，涉及较多的数理统计理论，这里从略。我们可以根据例1的计 算，Excel
图1的K2：L4区域就是LINEST的返回值，从相应的位置，看到b=51.32 32、m1=12.8960、R2=0.9403，
所得参数和方法一的结果完全一致，获得的经验公 式也是y=12.896x＋51.323。
需要指出的是，在使用LINEST函数时，因为返 回参数不只一个，所以显示这些参数是一个区域，故
操作要按Excel的“数组”处理，即同时按Sh ift、Ctrl和Enter三个键，完成一次操作。LINEST的操作步骤
是：选定返回值的区域 ，例1只有b、m1两个参数，我们只要求显示R2，故选取2列3行即可(Excel图
1的K2：L 4区域)，具体设置为LINEST(B2：I2，B1：I1，1，1)，同时按Shift、Ctrl和En ter三个键，操
作完成。
例题二
某地货运公司货运周转量Y(亿吨公里)与当地 工农业总产值X1(亿元)、基本建设总投资X2(亿元)和人口总
数X3(十万人)有关(见表3)， 可以建立一个3元经验公式。根据此公式，如果1999年该地工农业总产值预
计为4.5亿元，基本建 设总投资为2.3亿元，人口总数为5.34(十万人)，预测1999年货运周转量是多少?
操作步骤如下：
1.在Excel表格中录入例2数据(Excel图2)。需要说明的是，利用 数据分析工具获取经验公式的有关
参数，原始数据只能竖直(列)排列。
2.点击菜单“ 工具”／“数据分析”／“回归”，在“回归”对话框中，“输入Y区域”为B2：B7，“输入X区域”
为C2：E7，在“输出选项”中，选择“输出区域”的左上角单元格(本例取B13)，点击“确定”，从B 13单元格
开始立即增加许多新内容(Excel图2的整个下半部分)，包括根据经验公式计算的预测 值、残差等。在这些
新内容中我们还看到大量的统计参数，就一般应用来说，只需“系数”栏的“截距” (即系数b)、“X变量1”(即系
数m1)、“X变量2”(即系数m2)、“X变量3”(即系数m 3)和“R的平方”几个数据也就够了。相应的经验公式
为y=2.8942X3＋1.2835X2－ 1.3005X1－7.4251，相关系数R2=0.9990。
为了核对，我们也用LINE ST函数作了一下验算，所得经验公式的参数完全相同(见Excel图2的F3：
I5区域)。
将1999年X1、X2、X3的预计值代入经验公式，y=2.8942×5.34＋1.283 5×2.3－1.3005×4.5－
7.4251=5.13(亿吨公里)，这就是该公司新一年的货 运周转量。
方法三 利用SLOPE函数、INTERCEPT函数和RSQ函数分别计算线性 公式的斜率m、截距b和相
关系数R2，从而获得经验公式。
SLOPE和INTERC EPT的语法一样，只有2项，每项仍用“，”点隔开。第1项是Y的数据区域，第2
项是X的数据区域 。对于例1，SLOPE(B2：I2，B1：I1)，返回m=12.8960;INTERCEPT(B2: I2,B1：I1)，
返回b=51.3232;RSQ(B2:I2,B1:I1),返回R2=0. 9403。所得结果均列入Excel图1的K7：L9区域，返回数
值和方法一的结果相同。
方法四用TREND函数、FORECAST(直线)或GROWTH(指数曲线)函数，直接获得 经验公式的拟合
值和预测值，但不显示经验公式。
TREND的语法包含4项，TREN D(变量Y，自变量X，预测X，截距处置)。“自变量X”可以是一个变
量X1，也可以是多元变量X 1、X2……Xn；“预测X”设置预测Y对应的X，当然对于多元情况，“预测X”
也是一组变量；“ 截距处置”一般予以省略。对于例1，在B15单元格设置如下(Excel图1)，TREND(SB2：SI2，SB1：SI1，B1)，将第一项、第二项设置成“绝对地址”，是为了在复制公式时不改变这些 设置。B15
设置完成后，只需将光标指住单元格右下角，待光标变成十字，向右拖动，即可完成所有的 拟合和预测计
算。
FORECAST只用于线性回归，其语法为FORECAST(预测 X，变量Y，自变量X)，由于是一元方程，
所以“预测X”只有一个。对于例1，在B16单元格设置 如下(Excel图1)，FORECAST(B1，SB2：SI2，SB1：
SI1)。将光标指住 B16单元格右下角(出现十字光标)，向右拖动，同样完成所有的拟合和预测计算。用TREND
和F ORECAST拟合和预测的结果完全相同。