☰

Excel 趋势线准确性

tag-icon • tag-icon microsoft-excel charts trendlines
Excel 趋势线准确性

这是我时不时会遇到的一个问题，它让我非常烦恼，因为我总是必须重新检查我得到的每条趋势线。

一个例子：

r       L
(mm)    
30,00   97,0  
60,00   103,2  
90,00   106,0  
110,00  101,0  
125,00  88,0  
140,00  62,0  
148,00  36,7  
152,50  17,0

绘制趋势线（使用三阶多项式回归类型）r在 x 轴上，以及大号在 y 上，Excel 将给出公式

y = -0,0002x³ + 0,0341x² - 1,8979x + 128,73

R² = 0,994。如果我使用该公式对相同的值进行插值r作为公式的推导来源，我得到了

r   y  
(mm)      
30,00   97,083  
60,00   94,416  
90,00   88,329  
110,00  66,371  
125,00  33,68  
140,00  -17,416  
148,00  -53,5912  
152,50  -76,97725

哪些是完全不同的？

为什么会出现这种情况？原因何在？

答案1

看起来 Excel 给出的公式具有四舍五入系数。使用 OpenOffice 计算例程进行回归，我得到了这个公式，它与数据的拟合度更高：

y=-0.00017257x³+0.034107417x²-1.89794239x+128.7325785

由于 x³ 项非常大，系数的微小差异都会对预测结果产生很大影响。

答案2

作为W_沃利讨论过这是因为 Excel 会对数值进行四舍五入显示在公式中，解决方法是简单地更改标签的显示格式，方法如下：

  1. 创建图形，添加趋势线，使方程标签可见。

  2. 右键单击公式标签并选择格式化数据标签...

  3. 在数字选择类型数字并输入您想要的小数位数。

  4. 关闭格式窗口。

以下是将给定示例数据的小数位数设置为 20（例如）的结果，并添加了换行符以避免滚动条：

y = -0.00017256831201215700x³ + 0.03410741673273060000x²
                  - 1.89794238802443000000x + 128.73257845634200000000

答案3

如果您无法自己得出回归系数，您可以简单地用 r 值除以 10 来代替 r 值。即：30 变为 3，60 变为 6，依此类推。您会发现 Excel 重新计算系数更精确，因为它将使用更多有效数字。

相关内容