如何为带日期时间值的多项式趋势线生成斜率方程及计算指数趋势线R平方值

带日期时间值的多项式趋势线方程生成方法 #

所有涉及日期时间作为自变量的趋势线计算，第一步都要先把日期时间转换为Excel对应的序列值：Excel默认将1900-01-01记为序列值1，每增加1天序列值加1，当日的时间折算为0到1之间的小数（例如下午6点对应0.75），不要直接用Unix时间戳等其他数值格式，否则拟合结果会和Excel不一致。

你提供的参考图为2阶多项式趋势线，标准方程格式为 y = ax² + bx + c，其中a、b、c为拟合系数，对应任意时间点的斜率为对x求导的结果：斜率 = 2ax + b，代入该时间点对应的Excel序列值即可得到该点的斜率。

你可以直接用Excel自带函数生成和趋势线完全一致的系数：

• 单独新增一列存储所有日期时间对应的Excel序列值，记为X列，对应指标值为Y列
• 选中3行1列的空白单元格区域，输入公式 =LINEST(Y列数据范围, X列数据范围^{1,2}, TRUE, FALSE)，按Ctrl+Shift+Enter执行数组公式，返回的三个值从上到下依次为a、b、c，代入即可得到完整的多项式方程。

指数趋势线R平方和Excel结果不一致排查 #

该问题的核心原因是拟合逻辑和Excel的默认规则不匹配：
Excel计算指数趋势线时，会先对所有Y值取自然对数，转换为线性拟合逻辑 ln(Y) = ln(a) + bX，拟合完成后再还原为 Y = a*e^(bX) 的指数形式，对应的R平方是取对数后的线性拟合结果的R平方，而非直接用原始Y值和指数拟合预测值计算的决定系数，这是绝大多数人计算结果不一致的原因。

你可以按以下规则校准计算结果：

• 确认X轴的日期时间已转换为和Excel完全一致的序列值，排除起始日期、时区带来的数值偏差
• 计算R平方前先对所有Y值取自然对数，再用转换后的值和线性拟合结果计算R值后平方，即可得到和Excel完全一致的结果。

内容的提问来源于stack exchange，提问作者YuVaRaJ