如何绘制两组不同的散点图和月份数并计算回归线？

Question

最初有几点值得注意。

Excel 提供了一个“预测表”选项（位于功能区上“数据”菜单的“预测”组中），它使用一种中等复杂的方法（基于指数平滑）来预测时间序列的未来值。虽然任何能够使用 Excel 的人都可以使用它，但在将其用于实际问题之前，最好先了解一下“幕后”发生的事情。
尽管 Excel 会在某些图表中添加所谓的“趋势线”，但要确定任何点的趋势线值，要么从图表本身“目测”，要么选择正确的趋势线显示选项，复制图表上显示的趋势线方程的数值，并在 Excel 公式中使用这些数值来计算特定点的趋势线值。

下面的方法不采用这两种方法中的任何一种，而是使用 Excel 公式明确地拟合模型，然后使用该模型的系数来计算模型估计的值。

原始问题似乎涉及两项任务：(i) 模型拟合和数值估计；(ii) 绘制数据、模型及其估计值的图表。下面将分别处理这些任务。

模型拟合与估计

当然，线性回归模型等统计模型适合数值数据，因此标签“2015 年 4 月”、“2015 年 5 月”......“2015 年 8 月”需要转换为数值。

虽然 1、2、3、4 和 5 似乎显而易见，但这需要了解数据所代表的内容。例如，1、2、3、4 和 5 的替代值可能是 2015 年第一个星期三之后到 5 个月中每个月的第四个星期三的总天数。这将产生 112、140、168、196 和 231 的值来代替月标签。

为简单起见，我将坚持使用 1、2、3、4、5。此外，为简单起见，我暂时将坚持使用简单的线性模型。此外，由于 Excel 的提示和统计建模术语都使用“x”和“y”并赋予它们特定含义，因此我将更改问题的“X”和“Y”，代之以“P”和“Q”。

简单线性模型的数学形式为

y=a+b×x

在哪里X是独立变量（在本例中是与月份相关的数字），是是因变量（与磷或者问）和A和b是模型的系数。在线性回归中，系数的值是使用特定的数学方法（最小二乘法）估算的。在 Excel 中，这是使用函数INTERCEPT和SLOPE来实现的，这些函数分别估算A和b。

每个函数都有两个参数，第一个参数是包含已知X值，第二个是包含已知是's。例如，如果 2015 年 4 月、2015 年 5 月和 2015 年 6 月对应的数字包含在中，A2:A4并且相应的磷值C2:C4在

A使用公式=INTERCEPT(A2:A4,C2:C4)计算 b使用公式计算=SLOPE(A2:A4,C2:C4)

然后可以使用简单的“公式”根据系数计算出模型估计的值

=a+b*month

因此，一旦A和b已经确定，估计值为磷（表示为害虫) 可计算出 2015 年 7 月和 2015 年 8 月的增长率，在这个简单的例子中，用month上述公式中的 4 和 5 代入。重复此操作可计算出模型系数和估计值问。

下面的屏幕截图显示了上述内容在 Excel 中的实现。

制图

通常，适用于具有一个独立变量的线性回归模型的图表类型是简单散点图。

尽管 Excel 允许在创建此类图表之前选择数据，但 Excel 会“猜测”数据的哪些位是独立变量（X值）并且是因变量（是值）并不总是很出色，特别是对于包含 4 个系列的图表（磷，问，害虫和奎斯特) 都使用相同的独立变量（月份值）。

最简单的方法是，在使用菜单中的“插入”命令之前，选择工作表的空白区域，创建一个完整的空白图表（从“图表”组中选择“插入散点图（X、Y）或气泡图”，然后从下拉选项中选择“带起始线和标记的散点图”）。这将打开“选择数据源”对话框，如下所示。

单击“添加”按钮将允许通过“编辑系列”对话框添加每个系列

对于第一个系列，磷数据添加如下图所示（基于上面的截图）

添加第一个系列后，问，害虫和奎斯特可以类似地添加。

然后可以对每个系列的格式和图表的其他元素进行一些编辑，以获得如下所示的图表。

Excel 不允许将月份标签插入到此图表中 - 对于散点图，“选择数据源”对话框右侧的“编辑”选项显示为灰色，从而阻止对“水平（类别）轴标签”进行更改。

然而，由于使用了相等间隔X值（1、2、3、4 和 5）时，图表类型可以更改为“线”。这不会扭曲图表上的线条形状，现在允许使用范围B2:B6来标记 x 轴。但请注意，更改这样的图表类型可能会导致一些精心应用的格式（例如数据系列的颜色和标记）丢失。

指数模型

而不是使用简单的线性模型来拟合模型磷，可以使用指数增长模型。其一般形式为

y = a×exp(b×x)

对两边取自然对数可得

ln(y) = ln(a) + b×x

这是一个线性模型ln(y)而不是是作为因变量。

现在可以使用上面针对简单线性模型列出的技术来估计该线性化模型的系数。这留作练习，但精确到小数点后 6 位，这些系数是

对数函数= -0.326943（截距）
b= 1.039721（斜率）

AEXP可以通过将函数应用于截距值来确定，从而得出（同样精确到小数点后 6 位）

A= 0.721125

应用这些新价值观A和b指数模型方程（y = a×exp(b×x)）与X值 1、2、3、4 和 5）可得出此新模型的 P 估计值

2.039649（2015 年 4 月）
5.768998（2015 年 5 月）
16.317191（2015 年 6 月）
46.151986（2015 年 7 月）
130.537530（2015 年 8 月）

这些和先前来自线性模型的估计值提供了如下图表：

讨论

显然，7 月和 8 月，这两个模型对磷7 月份，指数增长模型估计值是线性模型的两倍，8 月份，该模型估计值是线性模型的五倍。

从表面上看，比较 4 月、5 月和 6 月三个月的实际值和估计值，可以发现指数模型比线性模型更适合数据。线性模型的误差平方和 (ESS) 值（3 个月内实际值和估计值的平方差之和）为 6，而指数模型仅为 0.156。（在评估不同模型与数据的拟合程度方面，较低的 ESS 值通常表示拟合度更高。）

然而，如果没有大量的额外信息（例如对导致磷数据），对于哪种模型最合适，这确实是一种猜测。在没有这些额外信息的情况下，3 个数据点根本不足以对底层过程是线性、指数还是许多其他可能性之一做出合理的判断。在这种情况下，不建议仅基于 3 个数据点构建预测模型，尤其是因为预测值的广泛变化可能与选择错误模型的风险有关。

例如，如果数据代表销售额，那么选择错误的模型可能会对库存、人员和其他产能成本（错误地选择指数）或销售、利润和商业声誉的损失（错误地选择线性）产生严重后果。

Answer 1