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