![6cb38456554d28c64ac087a593e377d6.png](https://img-blog.csdnimg.cn/img_convert/6cb38456554d28c64ac087a593e377d6.png)
数据分析工具千千万,怎么又能少得了Excel~
预测工作表
预测工作表是Excel2016版添加的一个新功能,预测使用基于现有时间的数据和指数平滑(ETS)算法的AAA版本来预测未来值。
下图中,天猫双十一从10年至今,销售额从9亿狂奔至19年的2684亿(20年双十一官方发布11.1日至11.11日累计销售额,当天未公布)
![84974a2986138432180b435a01ce69ad.png](https://img-blog.csdnimg.cn/img_convert/84974a2986138432180b435a01ce69ad.png)
这里我们用10年至18年的销售数据来预测19年与20年双十一当天的销售额情况。
预测工作表位于【数据】功能区下部(Excel2016及其以上版本,没有安装包的小伙伴后台私我)
![22604f3f10b338ae905b8a6e51067209.png](https://img-blog.csdnimg.cn/img_convert/22604f3f10b338ae905b8a6e51067209.png)
选中表格,点击预测工作表,界面如下所示:
![8f5f00b1b19da98f621076a50dce0d14.png](https://img-blog.csdnimg.cn/img_convert/8f5f00b1b19da98f621076a50dce0d14.png)
预测开始从2018年以后,预测2年的数据(19&20年),置信区间95%(默认),代表实际值95%会落在预测区间内,其它为默认值。
预测结果如下图所示:
![d20797ccefd8a22f8d60f80bec87301d.png](https://img-blog.csdnimg.cn/img_convert/d20797ccefd8a22f8d60f80bec87301d.png)
最上面的橘色线:叫做置信上限,即未来趋势的上限不超过此线;
最下面的橘色线:叫做置信下限,即未来趋势的下限不超过此线;
上下两根橘色线之间:叫做置信区间,即未来趋势在此区间中波动;
中间加粗的橘色线:叫做趋势线,即未来趋势最有可能沿此线的趋势发展。
图中预测2019年双十一销售额区间在2379亿至2823亿,最有可能销售2602亿元,我们看下2019年实际销售额2684亿刚好在此区间内,与趋势预测值误差在3%左右,完全可以接受。
从图中预测可以看出,2020年双十一销售额大概在2611亿元至3527亿元之间。
规划求解
《孙子算经》中记载着一道有趣的题目:
今有雉兔同笼,上有三十五头,下有九十四足,问雉兔各几何?
意思是说:有若干只鸡兔同在一个笼子里,从上面数,有35个头,从下面数,有94只脚。问笼中各有多少只鸡和兔?
在现在来看,典型的一元二次方程组嘛!
一只鸡有一个头两只脚,一只兔有一个头四只脚,设鸡有x只,兔子有y只,可得以下方程组:
![425652e4ef5d0346d69c67a174388c0f.png](https://img-blog.csdnimg.cn/img_convert/425652e4ef5d0346d69c67a174388c0f.png)
今天我们用Excel中给的规划求解来解答这道题。
Step1 整理已知数据
题目的条件反应在Excel中如下图所示,通过更改B2&B3单元格的数字(鸡兔的量)来使B6&B7单元格成立(这两单元格皆有公式组成,与B2&B3进行关联)
![09789dfce2a304edb794cb3d808ad71b.png](https://img-blog.csdnimg.cn/img_convert/09789dfce2a304edb794cb3d808ad71b.png)
Step2 调出规划求解界面
“规划求解”功能在数据功能区,但一般默认未展示,需要我们手动调出。
右键功能区任意空白位置,选择【自定义功能区】—【加载项】—【Excel加载项】—勾选【规划求解加载项】—【确定】。
回到主界面,点击【数据】,选择【规划求解】。
![d02b24340fd66e58ce05d81b81f94027.png](https://img-blog.csdnimg.cn/img_convert/d02b24340fd66e58ce05d81b81f94027.png)
界面讲解
规划求解的界面如下图所示,首先需要设定一个目标,这个目标一定得是个带公式的单元格,这里选择B6或者B7都是可以的,目标值有三个选项,最大值、最小值与目标值,我们这里选择目标值。
如果设置目标中输入B6,那么目标值就是35,如果设置目标中输入B7,那么目标值就是94。
可变单元格就是B2&B3。
![0bf150cd255c9779e230626b3003cbc1.png](https://img-blog.csdnimg.cn/img_convert/0bf150cd255c9779e230626b3003cbc1.png)
Step3 输入参数
这里将B6单元格设置为目标值(35个头),另外一个已知条件设置到约束条件,同时鸡和兔的数量应该为整数,也设置为约束条件。
![dab4ad7dc5473ca06d720a9d11fcdbc9.png](https://img-blog.csdnimg.cn/img_convert/dab4ad7dc5473ca06d720a9d11fcdbc9.png)
点击确认按钮,可以发现,Excel规划求解已经求解完毕,正确的数字被输入到B2&B3单元格内,鸡23只,兔12只。
小结
趋势预测和规划求解是Excel当中比较实用的两个数据分析小工具,分享给大家,觉得有用的小伙伴记得关注我,每天分享数据小技巧。