有时候,我们需要如下图的簇状柱形图和折线图的组合,但Excel是无法直接生成这样的图表的(或许你以为可以,但真的是不行(づ。◕ᴗᴗ◕。)づ)。但是通过『黑科技』的方式,就让此任务变的可能了。
先放数据,我们的绘图基于以上数据。
整体思路先说下我们的整体思路:
- 先绘制簇状柱形图
- 自己计算坐标,绘制散点图
其中的关键就是坐标是如何计算的。我们先看下簇状柱形图的一些概念,下面会用到。
间隙宽度:两个不同的分类(Category)柱子之间的距离,单位是柱宽的倍数。假如距离是4个柱子的宽度,那么这个值就是400%。默认是219%。
系列重叠:两个柱子中间的距离,单位是柱宽的倍数。要想两个柱子隔开一定的距离,它必须是个负值。
主轴横坐标:从0.5开始,每个分类(Category)占据1个宽度,至于1个宽度具体多宽,Excel负责计算。因此主轴横坐标是0.5,1.5, 2.5…… 同时,我们有如下等式:
1个主轴宽度=该分类中所有有柱子宽度 1个间隙宽度-该分类中所有系列重叠 (系列重叠为负值需要减)
计算散点图的坐标根据我们前面所说,关键就是怎么计算散点图的坐标。
先看X坐标的计算如图所示,我们希望每个散点图的横坐标恰好位于柱子的中间。
我们观察到,每个分类中的柱子,相对于分类的开始,距离都是相同的。因此我们计算出每个柱子相对于开始的距离,再加上分类的开始坐标。就得到了柱中心X坐标。
柱中心X坐标=该分类开始X坐标 柱中心与分类开始的距离
柱子数量 | 7 |
间隙宽度(柱宽%) | 219% |
系列重叠(柱宽%) | 0% |
以上三个数字我们先列出来,下面计算需要用到。间隙宽度和系列重叠通过双击柱子就可以看到它们的值。
每个分类宽度(柱宽) | 9.19 |
每个分类宽度(柱宽)= 所有有柱子宽度 1个间隙宽度 - 1个分类中所有系列重叠(系列重叠是负值需要减)
= 7 2.19 - 0
= 9.19
下面我们计算每个柱中心与分类开始的距离(单位是主轴坐标)。注意,我们需要把柱宽转化为主轴的宽度。前面我们的计算都是基于柱宽的,而每个分类总的长度就是1,也就是100%,因此我们计算公式为 [0.5*间隙宽度 (编号-0.5)-(编号-1)*系列重叠]/ 每个分类的宽度 得到的就是柱中心与分类开始的距离(主轴坐标) 不懂的看下图。
分类内柱子编号 | 柱中心与分类开始的距离 | 计算过程 |
1 | 0.173558215 | =[0.5*2.19 (1-0.5)-(1-1)*0]/9.19=0.173558215 |
2 | 0.282372144 | |
3 | 0.391186072 | |
4 | 0.5 | |
5 | 0.608813928 | |
6 | 0.717627856 | |
7 | 0.826441785 |
有了这个表,柱中心X坐标就可以很容易的计算出来了。柱中心X坐标=分类开始X坐标 柱中心与分类开始的距离。还记得我们上面说过的吧,每个分类开始坐标是 0.5,1.5,2.5这样开始的。
分类 | 系列 | X坐标 | 计算过程 |
1 | 1 | 0.673558215 | =0.5 0.173558215 |
1 | 2 | 0.782372144 | |
1 | 3 | 0.891186072 | |
1 | 4 | 1 | |
1 | 5 | 1.108813928 | |
1 | 6 | 1.217627856 | |
1 | 7 | 1.326441785 | |
2 | 1 | 1.673558215 | =1.5 0.173558215 |
2 | 2 | 1.782372144 | |
2 | 3 | 1.891186072 | |
2 | 4 | 2 | |
2 | 5 | 2.108813928 | |
2 | 6 | 2.217627856 | |
2 | 7 | 2.326441785 |
这个就比较简单了,Y坐标基本不需要计算。直接用原始值就可以了。
组合在一起需要的知识一共就这么多了。我们看看如何组合在一起,形成我们想要的图表。
先绘制簇状柱形图选定GDP数据,绘制图表
把我们上面计算的坐标做成表格,类似如下表:
注意x坐标有一行故意的空白行。
绘制散点图1.复制数据
2.选中图表
3.选择性粘贴
粘贴之后是这个样子
不要慌,要镇定,我们走在正确的道路上。
4.修改图表类型
改为组合图,把人口数据改为带直线和数据标记的散点图,其他全部为簇状柱形图。
修改完之后长这个样子。是不是还是不对?这是因为人口数据绘制在次轴上,次轴的坐标和主轴不一致,调整成一致就成功了。先把次轴的横坐标显示出来。
5.显示次轴横坐标
6.调整次轴横坐标
把次轴横坐标的最小值改为0.5,和主轴保持一致。
7.隐藏次轴横坐标
就这样,完工。\\\ ('ω') ////
有问题可以留言。需要源Excel文件样例的也可以联系我。
,