- ·上一篇:excel表格怎么左侧插入一列
- ·下一篇:excel表格怎么做大双面桌签
怎么用excel表格做统计数据预测
1.如何用excel做数据预测呢?
销售量是呈年度增长趋势,并随季度周期性变化。
1. 预测2010年总销售量
2007 1800
2008 2170
2009 2570
2010 年销售量 =forecast(预测年份, 销量区域,年份区域)=forecast(2010,b2:b4,a2:a4)=2950
2. 计算年度周期内变化
第一季度 第二季度 第三季度 第四季度
2007 200 300 500 800
2008 240 350 600 980
2009 300 420 700 1150
季度占权重 (每季度销量/全年销量)
11.11% 16.67% 27.78% 44.44%
11.06% 16.13% 27.65% 45.16%
11.67% 16.34% 27.24% 44.75%
三年平均季度权重:
11.28% 16.38% 27.55% 44.78%
3. 求出 2010 年各季度销量预测:年度销量*平均季度权重
第一季度 第二季度 第三季度 第四季度
333 483 813 1321
2.用excel做数据预测
以上图所示数据为例来简单加以说明。
1、选择源数据区,点“插入”——图表,在图标类型列表框中选择XY散点图,如下图所示
直接点完成即可。
2、选中图表中的曲线,右击,选择添加趋势线。
在对话框中有线性、对数、多项式、乘幂、指数、移动平均共6种,其中前5项可做数据预测分析。
先选择线性,之后再“选项”中勾选显示公式和显示R平方
单击确定,出现下图
R平方值在0至1之间,越接近1,说明曲线契合度越高。因此可多试试其他趋势线,选择R平方值最大的作为最终趋势线。(在上图中选中线性趋势线,即图中的黑粗线,右击,在下拉菜单中选择“趋势线格式”可更改趋势线类别,更改时需注意要勾选显示公式和R平方值)
3、本例最佳趋势线为乘幂,如下图
然后,根据图中所给出的公式利用excel的计算功能,将公式输入单元格后,通过改变x值即可得到预测出的y值。
3.如何在EXCEL中进行数据预测
介绍一个最简单的方法,请看图:
其中,1 月到8 月的数据是已知的,9 月的收入,也就是黄色背景的部分,是通过FORECAST
函数计算出来的这个是官方公式书写方法:FORECAST(x, known_y's,known_x's)我翻译一下:
FORECAST(要预测的Y 值所对应的X 值, 已知的Y 值序列, 已知的X 值序列)结合本例再翻译
一下:FORECAST(要预测哪个月的收入, 已知的每个月的收入序列, 与已知收入对应的月份序
列)这样解释明白了没?所以我写下了这样的公式:=FORECAST(J2,B1:I1,B2:I2)计算结果是
435,满意吗?其实这就是个很普通的线性回归。
我们用FORECAST,并非通过月份预测收入,而是通过许多个“(月份,收入)对”,找它们的规
律,预计未知月份的收入数据。回过头去看附图中的折线图表(实质上应该理解为连了线的
散点图):深蓝色的点,就是“(月份,收入)对”在坐标中的位置,黑色的是趋势线,9 月所
对应的点是预测出来的,恰好对应趋势线的末端。
只要能看明白这个图,不再钻“收入”、“月份”等字眼的牛角尖,而只看其实质(x,y),就不
所以不可对其太过依赖。
另外,如果决定认为线性回归是适合实际情况的算法,也就是说x 和y 确实具有比较强的线
性的相关性——也就是(x,y)对的那些点不至于排得太杂乱——那么,原则上(x,y)对的已知数
据越多,则预测结果越可靠。
最重要的一条: 还是人比较聪明,线性回归是人类智慧,FORECAST 也是人类智慧。EXCEL
的宏和VBA 功能,可以让人类的智慧得到更广阔和自由地发挥,这个以后找机会再来讨论。
4.如何用EXCEL做数据统计与分析
1、打开数据表格,每组需要统计的数据需要排列在同一行或列。选择“数据”-“数据分析”-“描述统计”后,出现属性设置框,依次选择。
2、输入设置。在输入区域中,选择原始数据区域,可以选中多个行或列,并在分组方式中对应的选择“行”或“列”;如果数据内容在第一行有文字标志标明,勾选“标志位于第一行”。
3、输出设置,在需要输出的描述统计表的位置,选择一个单元格作为统计表左上角的一格。勾选“汇总统计”,点击确定。详细地描述统计结果就生成了。
提示:中位数反映了数据排序后位于中间的值,众数代表具有最多个数的数值,峰度的大小代表数据的分布相比正态分布更为平缓或是突兀,偏度的正负表示数据分布的峰值在均值的左侧还是右侧。