张文迪的博客

数据分析及数据化运营-BI仪表盘及数据运营项目咨询与实施

用Excel制作一个漂亮的分类散点图

       前段时间在朋友圈发了一个散点图,好多朋友说很漂亮,想要学习制作方法,今天小编就为大家解析这个散点图的做法。我们先来看看这个散点图:

用Excel制作一个漂亮的分类散点图

      

       这个散点图一共分为四类,每一类按照顺序进行散点图的绘制,不同类别使用不同的颜色区分,并在图中标出平均值进行连线。


数据准备


将分组数据准备成如下形式:

用Excel制作一个漂亮的分类散点图

此处仅仅截取两个Group,大家可以根据需要选择各个group,此处不用进行排序,分组展示即可,下面我们来构造辅助列:

1、第一个辅助列,作用是将每个分组按照1,2,3。。。的顺序标个序号,具体公式是:


用Excel制作一个漂亮的分类散点图


2、第二个辅助列,是用来规定散点图每个分组横坐标的起点,由于图中各个散点图之间要有一定的间隔,所以起点以1,3,5等奇数开始。


要构造这个数,此处首先构造一个第几组的辅助列,这一列有两种方式构造,可以提取B列中的1,2,3等字符构造,使用RIGHT函数。当然,也不排除分组名称中没有1,2,3,因而本文使用第二种方式构造:


用Excel制作一个漂亮的分类散点图


然后再使用2n-1的公式构造奇数,当然高手可以两个公式嵌套,一步到位。用Excel制作一个漂亮的分类散点图用Excel制作一个漂亮的分类散点图用Excel制作一个漂亮的分类散点图用Excel制作一个漂亮的分类散点图用Excel制作一个漂亮的分类散点图用Excel制作一个漂亮的分类散点图


用Excel制作一个漂亮的分类散点图


3、第三个就是规定每个分组之间的每个散点的横向间隔,此处间隔则是将1按照每个分组点的个数进行分割,本文样例中的每个分组,散点个数是不同的,因而使用如下公式:


用Excel制作一个漂亮的分类散点图


此处使用countif公式,是分组计数的公式,当然本文是以1作为一个散点图的长度,如果点非常多,想要以其他的长度作为分组,则乘以其他值。当然,要记得在前面设置间隔的过程中,相应的修改。


4、最后就是对每个坐标点的横坐标进行定义,使用辅助3(F列)加上辅助4(G列)的累计相加即可,使用公式如下:


用Excel制作一个漂亮的分类散点图


自此,所有的辅助列便已经构造完毕,当然这里面的这些辅助列只是为了讲解构造思路,可以将公式进行叠加,减少辅助列的数量。


画图


首先选择Group1的数据画散点图:


用Excel制作一个漂亮的分类散点图


分别选择横坐标和纵坐标,并对散点图的样式进行设置,选择散点,更改系列样式:


用Excel制作一个漂亮的分类散点图


根据自己的需求逐个选择分组,增加数据并进行描点画图即可。

用Excel制作一个漂亮的分类散点图

这样,我们就形成了一个初步的分类散点图了。

用Excel制作一个漂亮的分类散点图

画完散点,我们来计算中间的均值数据,均值数据我们用透视表透视一下即可得出:

用Excel制作一个漂亮的分类散点图


然后将均值数据也增加到图中,这里注意X轴选择每个分组的中间值,Y轴是选择刚刚透视的均值:

用Excel制作一个漂亮的分类散点图


再将数据点样式改为中间白色,边框黑色即可,这样一个分类散点图就大功告成了,其他图表元素可以根据需要再增添即可。


发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

«   2020年10月   »
1234
567891011
12131415161718
19202122232425
262728293031
搜索
网站分类
文章归档
最新留言
控制面板
您好,欢迎到访网站!
  查看权限
友情链接

Powered By Z-BlogPHP 1.5.2 Zero

Copyright 张文迪. 备案号:辽ICP备15006660号-2

展开