excel - Excel中使用 'addtrendline'函数计算R平方值？

Question

我已经为此苦苦挣扎了一段时间；简而言之，我找不到 Excel 用于 R² 的方程。

• 这是我的数据:
  x: 1 2 3 4 5 6 7 8 9 10
  y: 4 9 1 2 1 1 8 5 5 1
  

• 我绘制数据，拟合幂律函数(“添加趋势线”)并使用“添加趋势线 > 选项 > 在图表上显示 R 平方值”

显示的值:
R² = 0.03008。

问题 1

如果我在 Excel 中使用“RSQ()”函数计算它(采用 Excel 为拟合函数找到的参数值)，或者使用定义(维基百科)手动计算...
R² = 0.0272

问题 2

在Matlab中，使用“fit”函数，拟合函数的参数(当然还有R²)并不是EXCEL找到的参数。

问题:

这是我的主要问题:
Excel 如何计算“添加趋势线”函数中的 R²，因为它显然不是定义(维基百科)中的值？

还有额外的问题:
为什么 Excel 和 Matlab 最终没有得到相同的拟合参数？

非常感谢!

%%%%%% 编辑如下! %%%%
作为评论的答复；这是我使用的 Matlab 代码:

%% R-squared with the fit function
% use the fit function in Matlab, yobs being the data
[param, results] = fit(x,yobs,'power1');   

% R-squared from the fit function :
r_sq_from_fit = results.rsquare;

%% here I calculate "by hand" the R-squared, from the general definition (wikipedia!)
% calculates the fitting data yfit
yfit = (p_powerlaw.a).*x.^p_powerlaw.b;

% mean of the yobs, total sum of squares, and residual sum of squares
yobs_mean = mean(yobs);
SStot = sum((yobs-yobs_mean).^2);
SSres = sum((yobs-yfit).^2);

r_sq_hand = 1-SSres/SStot;

无论是从 Matlab 中的 fit 函数获得 R 平方还是“手动”计算，我都发现相同的值。 Matlab似乎是一致的，并且显然在其函数中使用了R平方的严格定义......

但是；当我比较时:

• Excel 通过 RSQ() 函数给出的 R 方值
• 以及我根据定义手动计算 R 方获得的值(当然采用 Excel 返回给我的 yfit 值，而不是 Matlab 返回的值，因为 Excel 和 Matlab 不同意关于拟合参数!)

...我获得了不同的值! Excel:0.027，正如我之前所说，手工计算:-0.1109(!)

Answer 1

这个问题(Excel中的“添加趋势线”到底是如何工作的？)也让我困惑了很长时间，因为在研究中我需要确定我的数字的来源。因为我在网上没有找到太多关于这方面的信息，所以我尝试了几种手动R^2(确定系数)评估以获得与Excel相同的结果。

我做出了与问题中提到的相同的观察。当使用“添加趋势线”进行线性(以及对数)函数拟合时，生成的 R^2 和回归参数与手动计算的参数相同。但是，当使用“添加趋势线”进行其他非线性函数拟合(例如指数函数)时，生成的 R^2 和回归参数与手动计算的参数不同。

这个问题的解决方案已经在此处的讨论中部分提及。看来，为了使所提供的数据符合非线性趋势，Excel 主要将问题线性化。例如，为了拟合指数函数 y=a* exp(b * x)，它主要将其转换为函数 ln(y)=ln(a)+b*x。那么 ln(y) 和 x 之间的关系是线性的。然后，使用残差平方和最小化的常用策略将线性化函数拟合到转换后的数据中。由此得到回归参数ln(a)和b。 R^2 也是根据线性化形式计算的。由于是线性相关性，Excel 可以使用 RSQ() 函数来计算 R^2。

当手动执行此过程时，得到的回归参数和 R^2 值与 Excel 的“添加趋势线”提供的值相同。

因此，一般来说，在非线性回归的情况下，Excel 的“添加趋势线”提供的回归参数和 R^2 值并不是真正的非线性，而是很可能是在问题线性化后获得的。因此，这些参数与未经任何转换直接计算的参数略有不同。

关于 R^2 的说明:据我现在了解，线性情况下的 R^2(最好用小写字母表示:r^2)计算为相关系数的平方。 (RSQ()=CORREL()^2=PEARSON()^2)。由于这种关系，r^2 的值只能在 0 - 1 之间变化。可以通过截距和/或斜率更改一组输入已知值，而无需更改 r^2 值。另一方面，非线性情况下的 R^2(最好用大写字母表示)以不同的方式定义(参见维基百科)。它的值并不局限于值 0，但最大值 1 仍然表示最佳拟合。通过截距和/或斜率改变一组已知输入会改变 R^2 值。 R^2 在线性情况下也同样有效。