Excel 怎么给图表加趋势线并看懂 R²
折线图加了趋势线才知道数据往哪走,可 R² 到底说明什么很多人没弄清。讲清怎么加、选哪种、怎么读拟合优度和外推的边界。
一串按月的数据画成折线,看得出在涨,但涨得快还是慢、能不能照着往下推,光看线条只能靠猜。趋势线就是给这串点配一条参考线,顺手把公式和 R 平方值标出来,让「大概在往上走」变成一个带系数的判断。
入口在哪
趋势线挂在「数据系列」上,不在图表本身。先做好一张折线图或散点图,点一下图里的线让整条系列被选中,右键就能看到「添加趋势线」;也可以点图表右上角的加号,在列表里勾选趋势线。不同版本的按钮位置略有差别,找不到就在图表上右键试一次,只有选中状态对了,菜单里才会出现这一项。
要留意的是,饼图、雷达图、圆环图这类没有横纵坐标的图表不支持趋势线,右键菜单里根本不会出现。想给这类数据看趋势,先换成柱形图或折线图再说。
具体步骤
- 选中图表里的数据系列,右键选「添加趋势线」。
- 在右侧面板里选类型:基本匀速变化用线性;变化率本身在按比例增长用指数;早期涨得快后期变平用对数;有明显拐弯用多项式;只想抹平波动用移动平均。
- 勾选「显示公式」和「显示 R 平方值」,两者会以文本框形式贴到图上,可以拖到空白处。
- 需要往未来推几期,就在「前推」里填期数;想固定起点,勾选「设置截距」再填数值。
- 线条颜色和虚实不满意,可以在同一面板里改,别为了好看反复换类型。
- 多项式要填阶数,先试二阶,不要一上来就四阶五阶。
几个坑
- R 平方值高只说明这条线贴合这批点,不说明两个量有因果关系,更不说明未来会照这个斜率走。
- 点数太少时趋势线几乎没有意义。三五个点能拟合出很漂亮的 R 平方,换一批数据就全变。
- 多项式阶数越高越容易把噪声也拟合进去,曲线穿过每个点,往两头一延伸就飞出去。
- 横轴是文本日期时,Excel 会把它当类别处理,趋势线算出的斜率对应的是第几个,不是第几天。
- 前推后推是数学外推,不是预测。业务有季节性时,线性外推一定偏。
- 移动平均会跟着数据尾巴走,看不出拐点,也不给公式和 R 平方值。
怎么验证
- 把显示出来的公式抄进空白单元格,代入其中一个横坐标,算出的值应当落在图上那条线的位置。
- 看系数符号:指标随月份上升,斜率应当为正;算出负斜率就说明系列选错了,或者数据顺序反了。
- 临时把图表换成散点图再看一次,散点图能暴露被折线连起来的异常点。
- 删掉首尾各一个点重做一次趋势线,如果 R 平方值大幅跳动,说明结论不稳,别拿去汇报。
常见问题
为什么右键没有添加趋势线
多数是没选中系列,只选中了图表区域。先单击图里那条折线本身,四周出现小圆点后再右键;如果图表类型是饼图或雷达图,那本来就不支持。
R 平方值多少算好
没有统一门槛,要看场景。只有在同一批数据里横向比较不同模型时,这个数字才有可比性,跨行业比大小容易得出错误结论。
趋势线能不能用来预测下个月
只能当参考基线。要把预测当真,还得看季节性和外部因素,用专门的预测方法,趋势线外推得越远越不可靠。
一句话总结:趋势线是给已有数据配一条参考线,公式和 R 平方值只说明贴合程度,别当成因果证明或者预测结论。