Excel教程:excel竟能预测未来?3分钟,看懂数据未来走势!

全套Excel视频教程,扫码观看

编按:

哈喽,大家好!对于exceler而言,预测数据走势,如销量趋势预测、股指走势预测等是日常处理、分析数据工作中的一部分。面对这类问题,我们可以使用Excel图表中的趋势线对数据未来走势进行预测,帮助我们更加直观地了解数据变化的趋势。下面我们就和春风一起来学习excel中的趋势线吧!
趋势线是使用EXCEL进行数据预测时,非常重要的方法,它是图表中表示数据系列趋势的一种辅助线,在数据分析过程中,为了更加直观地了解数据变化的趋势,可以为图表中某个系列添加趋势线来辅助分析。当趋势线向上倾斜,表示数据有增加或上涨趋势,向下倾斜则表示数据有减少或下跌趋势。相信不少exceler都遇到过与预测数据走势相关的问题,下面就让我们在实践中学习趋势线的用法吧。
已知某销售公司1到6月的销售数据,需要预测出该公司下半年每月的销售数据走势。
原始表格:
生成图形:

1

预测下半年各月的销售金额

要预测下半年每月的销售数据,就需要进行定量数据分析,可以使用散点图和趋势线建立回归模型,获得拟合度最高的回归方程后,再利用回归方程计算出下半年每月的销售额,然后使用图表展示出上半年的实际值和下半年的预测值。春风推荐使用带直线的散点图,既可以表现数据大小,又可以表现变化趋势。具体步骤如下:
温馨提示:加入下面QQ群:1043683754,下载教程配套的课件练习操作。
① 选中“B3:B8”单元格,在功能区中单击“插入”菜单,选择图表功能区,在“插入散点图(X、Y)或气泡图”菜单下选择左上角的“散点图”,单击“确定”按钮并关闭对话框。
② 打开“图表元素”,勾选“趋势线”按钮。
③ 单击图表中的趋势线,打开“设置趋势线格式”对话框,选择“多项式”选项,“顺序”值使用默认的数值2,勾选“显示公式”和“显示R平方值”复选框,此时在趋势线上将获得回归方程和R2值。从图中可以看到,散点与趋势线结合紧密,判断系数R2=0.9995,回归方程显著,可以使用该方程进行趋势预测。
这里需要指出,趋势线虽然仅仅是表示趋势的线条,但线条有直线也有曲线,且弯曲的程度也各不相同,Excel中的趋势线有6种不同的类型:
① 线性趋势线:用于简单线性数据集的最佳拟合直线。

② 指数趋势线:用于表示数据变化越来越快,是一种斜度越来越大的曲线。

③ 对数趋势线:用于表示数据的增加或减小速度刚开始很快,随即又迅速趋于平稳。

④幂数趋势线:用于表示以一个相对恒定的速率变化的数据。

⑤多项式趋势线:用于表示数据之间存在较大偏差的数据,其阶数可由曲线的拐点数来预估,一般数据越复杂,阶数越高。

⑥移动平均趋势线:用于取邻近数据的平均值,将平均值作为趋势线中的一个点,然后取下一组邻近数据的平均值,以此类推,以平均值作为趋势线的点。
④ 在A9:A14单元格中输入数字“7-12”,代表7月-12月,在C9单元格中输入公式“=ROUND(4039.6*A9^2-8170.8*A9+91454,0)”,此公式为第③步中,带入值后的趋势线上显示的公式。拖动填充柄将公式复制到下面对应的单元格中,这样就获得了7-12月销售额的预测值,ROUND()函数用于对数据小数点位数进行取舍。在C1单元格中输入C列数据标题“下半年预测销售额”,在C8单元格中输入B8单元格的值“18709”,即把6月份销售额复制到预测销售额中,这样能保证后续曲线不会有断点。
⑤ 由于数据值较大,我们以“万元”作为金额单位,在“E3:E8”“F8:F14”单元格中输入10000,通过“选择性粘贴”的“除”运算把10000“数值”粘贴到原数据表中,设置小数位数为一位。

2

全年销售额图的绘制

选择“A3:C14”单元格区域,在功能区中单击“插入”菜单,选择图表功能区,在“插入散点图(X、Y)或气泡图”菜单下选择第二行的“带直线和数据标记的散点图”单击“确定”按钮关闭对话框。
设置横、纵坐标轴格式,单击横坐标轴,在右侧的“设置坐标轴格式”中选择“坐标轴选项”,设置“最小值”为1,“最大值”为12,“主要刻度”为1;按照同样的方法将纵坐标轴的“最大值”设置为60。
右击图表中蓝色曲线,点击“选择数据”按钮,选择“系列1”点击“编辑”,在“系列名称”中选择B1单元格;按照同样的方法,将“系列2”的名称改为C1单元格的值,单击“确定”按钮后,关闭对话框。这样就分别为两条曲线添加了系列名称“上半年实际销售额”和“下半年预测销售额”。
在图表中选择“下半年预测销售额”数据系列的第一个数据点,在“设置数据点格式”对话框中将其数据标记的填充颜色设置与“上半年实际销售额”数据系列相同。
下面我们将预测的数据用虚线连接。在图表中选择“下半年预测销售额”数据系列的数据点,设置连接折线的“短划线类型”为虚线。
接着为数据系列添加“数据标签”“横坐标轴标题”“纵坐标轴标题”“图表标题”。
最后美化图形。在图表工具中选择合适的样式,删除“主轴主要垂直网格线”,设置纵坐标轴线条为“无线条”,调整图例位置、图表以及图表区的大小。
OK,一张含趋势线的图表就完成了,掌握趋势线的用法,你将有种一切未来尽在掌握的感觉。聪明的你有什么别的想法,欢迎留言。
扫二维码免费学Excel等视频

Excel教程相关推荐

同事因操作表格慢,被领导骂哭,我怒总结出5个切换表格神技巧【Excel教程】
老板让985毕业的新同事给表格加个编号,第二天他就离职了
用了十几年Excel,这个函数我居然才知道,不要太好用!【Excel教程】

让工作提速百倍的「Excel极速贯通班」

(0)

相关推荐