基于 Excel 的预测方法和公式
首页 » 知识库 » 此处
作者：Guillaume Saint-Jacques, 2008 年 6 月 18日（上次修订时间：2010 年 2 月 22 日）
预测的好处
预测有助于您做出正确的决策以及挣钱/省钱。这里有一个例子。- 最佳化库存量
时间就是金钱。空间也是金钱。所以您要做的就是利用一切途径来减少您的库存，当然，同时又要不至于缺货。
怎么做到这一点？当然是通过预测！
如何让一切变得简单：利用标签、备注、文件名
随着时间的流逝，您的数据会越积越多，您混淆和犯错的可能性也越来越大。如何解决这一问题？合理利用标签、备注和为文件正确命名可以为您免去许多麻烦。- 务必为列添加标签。使用每列的第一行来描述所包含的数据。
- 不同的数据放到不同的列。不要将不同的数据（例如成本和销售量）放到同一列。否则极有可能会混淆，并且这样也会导致计算和处理数据的难度加大。
- 为每个文件指定清晰易懂的名称。这样做只需要花一点点精力和时间，但却易于辨别，也便于使用 windows 搜索功能进行查找。
- 运用备注。
即便您较少处理大量数据，但依然非常容易混淆，特别是在查看很久前创建的数据时。对此 Excel 提供了一个很出色的解决方法：备注。
 实用的备注 |
您可以使用备注：
- 解释单元格的内容（例如Doe 先生估计的单价）
- 警示后续的表格用户（例如我对该项计算存疑... ）
运用我们的库存预测 Web 应用可获取高级销售预测。Lokad 精通于通过需求预测来优化库存。我们的预测引擎工具涵盖本教程中所涉及的功能及其他许多原生功能。
开始：一个使用趋势线的简单预测示例
查看数据
数据：第一列为相似产品单价数据（单价反映产品的质量）。第二列为产品销售量数据。
想了解的信息：如果我们销售另一款产品，且这款产品的质量与 150 美元的单价相称，那么预计可以销售多少件？
实现方法： 非常简单。我们只需要找到单价和销售量之间数学关系，然后利用这种关系来执行预测。
首先，在 Excel 中创建图表总是很有用的，这样便于查看数据。您可以通过目测在短短数秒之内就辨别出趋势。
要执行这项操作，先要选择数据，然后选择“插入”>“图表”，之后再选择“XY（散点图）”选项。我们将把销售量作为质量的函数来估计，因此用横轴表示单价，竖轴表示销售量。
现在稍微看一下，可以发现二者的关系似乎呈线性不断增加。
要了解这种关系的确切形式，右键单击图表，然后选择"趋势线"选项。
 创建趋势线 |
现在我们要选择"拟合"（即最能说明）这些数据的关系。再次目测一下：本例中的点几乎呈直线，因此要采用"线性"设置。稍后我们将使用其它较复杂但也往往也更切合实际的设置，例如"指数"。 趋势线现在显示在图表上。再次右键单击可以显示这种关系的准确形式：y = 102.4x - 191.64。
理解：销售件数 = 102.4 乘以单价 - 191.64。
因此，如果决定以 150 美元的单价生产，预计将售出 102.4*150 - 191.64 = 15168 件。
 线性趋势线 |
至此我们已成功进行了第一次预测。
但是务必谨慎：软件能够查找两列之间的关系（即便在现实中这种关系非常薄弱）！因此务必检验关系的稳定性。快速执行此操作的过程如下：
- 首先务必查看图表。如果发现这些点非常接近趋势线，例如上例中所示，则表示关系可能非常稳定。但是，如果点的分布非常随机，并且通常状况下远离趋势线，那么要小心了：相关性较弱，不应盲目相信估计出来的关系。
 点的分布很随意：无关联性，预测不可靠 |
这些是 "有意义"的点，可实现更可靠的预测 |
- 查看图表后，可以使用 CORREL 函数。在示例中，这个函数为 CORREL(A2:A83,B2:B83)。如果结果接近 0，则关联性较低，结论为：不存在实际趋势。如果接近于 1，则关联性较强。后者是有用的，因为它增大了所考量的关系的幂指数。
另外还有许多种确保关联性高的方式；稍后会对此进行介绍。
当然，这些步骤也可以自动执行：您不必去留意关系，也不必用袖珍式计算器来执行计算。您只需使用 Analysis Toolpak！
使用 Analysis Toolpak 预测
请先检查是否安装了 Excel ATP (Analysis Toolpak)，然后再继续操作。有关更多信息，请参考“安装 Analysis Toolpak”一节。但不巧的是，这种具有如此简单线性关系的理想销售数据在现实中很罕见。我们来看一下 Excel 为处理数据更复杂的情形提供了什么功能。
深入了解：指数拟合示例
您可能会想到，这样一种线性数据模式并非始终都有可能。实际上，我们有足够的理由认为应遵循指数模式。经济领域的许多行为受指数方程驱动（利润复合计算便是一个典型例子）。执行指数拟合的过程如下：
1) 查看数据。绘制一个简单的图表并查看图表。如果这些数据遵循指数变化，则应类似于：
 理想的指数形式 |
使用趋势线 |
2) 幸运的是，使用 Analysis Toolpak 可以直接执行所有这些操作：将所有数据放入空白 excel 表，然后转至工具 => 数据分析
安装 Analysis Toolpak (ATP)
ATP 是 Microsoft Excel 附带的一个插件，但在默认情况下并非总是会安装该插件。如要进行安装，请执行以下操作：- 确保您备有 Office 光盘。Excel 可能会要求您插入光盘来安装 ATP 文件。
- 打开一个 excel 表，然后转至“工具”菜单，选择“插件”。选中窗口中标记 « Analysis Toolpak » 的第一个框。
- 根据软件提示，插入 office 光盘。
- 完成了！您会发现« 工具 »菜单现在将包含更多项功能，其中包括« 数据分析 »选项。这个选项将是我们最常用的一个选项。
使用 Analysis Toolpak (ATP)
... 使用线性设置
现在我们来回顾一下线性示例。如果您的数据« 看起来 »很好（参见上面的说明），则可以使用 ATP 来直接估计函数形式，无需执行« 趋势线 »过程。打开您的数据表，然后打开« 工具 »菜单并选择« 数据分析s »。随后将弹出一个窗口，询问您要执行何种分析类型。对于线性设置，请选择« 回归 »。
现在需要为 Excel 提供两个参数：« Y 范围 »和 « X 范围 »。Y 范围表示要评估的对象（例如销售量），X 范围包含您认为可以解释销售量的数据（本例中为单价）。在我们的示例（参见 example1.xls）中，销售量数据位于 B 列的第 3 行至第 90 行，因此 Y 范围应输入 « $B$3:$B$90 »，X 范围应输入 «$A$3:$A$90 »。完成后，单击 « 确定 »。
随即将显示一个包含« 回归结果 »的新表。
 普通最小二乘回归情形下的 Analysis Toolpak 输出 |
最重要的结果包含在表格底部的« 系数 »列。截距为常量，« X 变量 »系数就是 X（此处为单价）的系数。因此，我们将找到与使用« trendline » 函数时相同的方程式：销售量 = 截距 + X系数 * 单价，即销售量 = -126 + 100 *单价
该表还包含一个有用的数值 - « R 方 »，这个数值有助于您了解估计的合理程度。如果它接近 1，表示估计很合理，也意味着您找到的方程式很好地表示了您的数据。如果接近于 0，则表示估计不合理，您可能还要尝试其他类型的拟合（参见下文的指数拟合）。
这种方法可能速度比« 趋势线 »快，但它更具技术性，而且可视化程度低很多。如果您不想经历绘制和查看数据的麻烦，那么务必至少要检验 « R 方 »值。
... 使用指数拟合
如果线性估计表现不佳（例如 R 方值较低时（如 0.1）），可以尝试指数拟合。照例启动 Analysis Toolpak：打开您的数据表，然后打开« 工具 »菜单，并选择« 数据分析 »。随即将弹出一个窗口，询问您要执行何种类型的分析。
在指数设置中，需要选择« 指数 »。
请注意，Excel 仅要求您提供一个输入范围。请选择包含要预测数据（例如单价）的列，然后选择“平滑系数”。