表格设计课 第5期.jpg
首先，我对这个栏目做个介绍。
- 什么是「表格设计课」。我们在做Excel时遇到的问题，多半是表格没有设计好，那些各种复杂的公式，都是在弥补表格设计上的错误。「表格设计课」就是教你设计好表格，从源头上避免问题发生。
- 「表格设计课」的思想：
好的表格是设计出来的。 - 每期一个表格问题。原始文稿，来自于「读者投稿」或者「网络」，投稿邮箱是laxiaodeng2017@163.com，如果你也有问题，可以给我投稿。
- 每周三更新
以下是本期内容
问题描述
今天的话题，来自于小G同学的投稿，也是在工作中，处理大数据表格的时候，经常遇到的一个统计问题。
如果大家在表格统计上有问题，或者表格更新繁琐的问题，欢迎投稿给我，邮箱是laxiaodeng2017@163.com。
首先我们来看一下问题的表格。
image
表格中包含了110个经销商，连续7个月的销售数据，你能猜到小G同学，要问什么问题吗？
-----思考时间-----
-----思考时间-----
-----思考时间-----
我相信部分同学已经猜出来了。小G同学的问题是：如何在30秒内，一眼看出哪个经销商，销量上升的最快，哪个下降的最快。
image
时间只有30秒，还要一眼看出，小G同学给我出了一道难题啊！。
我也在小密圈里，分发了这个问题，测试了一下“偷懒学员们”，这几期的学习成果。
临时对策
有几个学员，给我提出了下面几个对策。
1. 折线图
A同学看我问题还没说完，就抢先回答：“这个简单，使用折线图啊，折线图是专门用来看上升或者下降趋势的。”
没问题，我们来针对这110个厂商的销售数据，做一个折线图看看。
image
折线图把趋势用线条表现出来了，确实可以在30秒内完成，但是一眼看过去，我们什么也看不出来。
折线图方法，失败！
2. 数据条
B同学，刚在网易云课堂，学习了《和秋叶一起学Excel》，知道了条件格式，他胸有成竹的回答：“数字不方便阅读，不能“一眼”看出大小。可以使用【条件格式】的【数据条】，把数据用条形显示出来。”
我们来试一下数据条。
image
不错，数字用【数据条】的长短表示后，确实可以“一眼”看出大小了。但是是上升还是下降？你能一眼看出来了吗？
而且，各个经销商的销售量差距比较大，有的一个月能卖上万台，有的才买100多台，这样就会出现下面的问题。
image
100多台的销售记录，根本看不到数据条，还比什么大小啊。
数据条方法，失败！
3. 迷你图
C同学，在公司里是个Excel高手，她提了一个，一般人都想不到的方案：“使用迷你图啊，在一个单元格里显示趋势，而且不会像折线图那样重叠。肯定能达到小G同学的要求！”
好，我们再试一下【迷你图】。鉴于很多同学，不知道什么是【迷你图】，我用动态图片来演示一下。
[图片上传失败...(image-1094b4-1529994688128)]
可是，迷你图就真的实现了我们的需求了吗？
image
虽然在每个迷你图，都能看到对应经销商的销售趋势。但是在经销商之间，无法形成对比。还是无法快速找出，上升最快，和下降最快的经销商。
迷你图方法，失败！
问题分析
我们回过头来，再分析一下小G同学的需求：在30秒内，一眼看出哪个经销商，销量上升的最快，哪个下降的最快？
问题是从阅读的角度出发的，但不是从表格数据出发的。我们从数据的角度，可以把这个问题，分为两个部分：
- 同一经销商，销售趋势对比。也就是对比不同月份的销量
- 不同经销商，销量趋势的对比。
1. 同一经销商，销售趋势对比
第1个需求是很容易，就可以实现的，我们可以使用C同学建议的【迷你图】，或者创建一个动态图表，都是可以查看，指定经销商的销售趋势的。
image
2. 不同经销商，销售趋势对比
这个实现起来就比较难了。因为销售趋势本身，我们是通过折线图实现的，而折线图之间，除了人为的目视，很难进行大小的对比。
所以，我们要解决的重点是
把经销商7个月的销售趋势，用一个可以量化的数值，展现出来。
把经销商7个月的销售趋势，用一个可以量化的数值，展现出来。
把经销商7个月的销售趋势，用一个可以量化的数值，展现出来。
解决方案
针对前面分析的第2点，我的解决思路是这样的。
-
计算前后两个月的销量增长值。增长值=后一个月的销量 - 前一个月的销量。如果增长值是负数，就表示销量下降。我把这个增长值叫做a。
image
-
把这个7个月的销量差值，累加起来，计算出一个累计的增长值。我把它叫做A。
image
- 计算累计涨幅。用累计增长值A除以7个月的平均销量，计算出涨幅B。
为每个经销商，增加了累计增长值A，和涨幅B，这两个代表趋势的数值，我们就可以轻松的实现，不同经销商的趋势对比了。
下面，我们先来看一下具体实现的公式吧。
1. 累计增长值A
这个累计增长值A，为了简化公式，使用了数组公式计算得出，用27月的数据，减去16月的数据。公式如下：
image
2. 涨幅B
这个计算起来就简单了。使用累计增长值A，除以1~7月的平均销量。公式如下：
image
3. 经销商分类
有了累计增长值A和涨幅B，就可以输出图表了，我们尝试着使用柱形图和折线图。结果如下：
image
但是问题还是很明显，110个柱子和110个拐点的折线图，让表格变的非常的丑。丑的让我们连续看10秒钟，都会觉得是一种煎熬。
而且几个销量近1000的数据，把图表拉的很高，让销量低的经销商，无法形成对比。
所以接下来，我根据经销商的平均销量，按照01000，10002000的规律，进行了分段统计。同时取前10项，这样就快速可以找到关注的重点了。
image
这个图的实现呢，使用了几个知识点：
- 使用数据透视表，创建数据透视图。
- 对平均销量【创建组】，形成分段统计。
- 对涨幅进行筛选，去前10项。
- 针对平均销量，创建【切片器】。
改善输出
最后改善输出的图表，使用数据透视表，创建了数据透视图，图中：
- 使用柱形图表示涨幅B
- 使用折线图代表累计增长值A
[图片上传失败...(image-650d50-1529994688128)]
在图表中，点击右边的切片器，可以切换不同销售端的增长趋势排名。
同时，在0~1000销量这个阶段，我们可以轻松的发现：
- 涨幅最高的（即增长最快的）是，卓尔03。
- 累计增长量最大的是，卓尔02。
然后，我们在动态图表里，相应的输入经销商的名字，就可以快速查看对应的趋势了。
[图片上传失败...(image-11a4d3-1529994688128)]
案例下载
案例下载包含两个部分
1- 问题表格
你可以在公众号，回复【0517】获取本节练习文件，然后自己动手，尝试着去修改表格。
2- 答案表格
如果你改不出来，可以通过下面两种方式，获取答案表格。
- 转发文章，截图发到我个人微信，可以获取答案表格。
- 加入我的小密圈，每一期「表格设计课」的答案，都会公布在小密圈里。
- 另外，你还可以和小G同学一样，得到表格设计上的帮助
image
来吧，我在圈儿里等你来
footer
我是拉小登，如果你喜欢我的文章，请转发或者打赏，有你的支持，我才能继续写出更多，更好的教程，咱们明天见。
网友评论