用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制作一个漂亮的分类散点图

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

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

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

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

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

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

画图

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

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

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

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

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

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

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

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

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

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

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

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

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

本文为专栏文章,来自:张文迪,内容观点不代表本站立场,如若转载请联系专栏作者,本文链接:https://www.afenxi.com/80000.html 。