excel图表制作技巧:多系列数据,如何制作组合图表?
【前言】
【正文】
学习EXCEL其实和与人交往的过程很像,同学们和EXCEL接触的时间长短,接触的内容广泛度,对它的用心程度,这些就能把EXCELER们区分为认识熟悉知心的等级。
前些日子有一位学生,找我问一个图表的问题,下面是她的原图:
这是一个发刊阅读量的计划/实际的数量对比图,这个图表中有几个问题:
1.堆积柱形图系列中,计划数量和实际数量是混着的,不能快速从图表中找到相应的数据;
2.代表各月累计数量的走势折线,没有分布到相对应的各个月份上,造成观感上的错觉;
3.主标题不够明显,对于各个系列的数据对比不能一目了然。
【设计制作新图表】
根据上面的这些内容,E图表述也给出了一些建议,同时做了一份组合图,如下所示:
【制图思路】
看到这样的一个数据源,按照观看者比较容易能够接受的图表习惯来说,对比类的最好是柱形图,走势类的最好是折线图。excel如何把数据做成图形
所以,我们采用了一组组合图表来表达每一个系列的值。
【制作过程】
步骤1:清洗数据
在B3单元格输入函数:
=ROUND(INDEX(发刊数据!$B$3:$Y$6,MATCH(B$1,发刊数据!$A$3:$A$6,0),MATCH($A3&B$2,发刊数据!$B$2:$Y$2,0))%%,2)
在C3单元格输入函数:
=ROUND(INDEX(发刊数据!$B$3:$Y$6,MATCH(B$1,发刊数据!$A$3:$A$6,0),MATCH($A3&C$2,发刊数据!$B$2:$Y$2,0))%%,2)
在B3:I14单元格区域填充函数,得到我们的图表数据源。
函数解析:通过两个MATCH函数,在源数据中,索引出对应的系列,再用ROUND函数,将源数据,转化成以万为单位。
小知识
做图表,别总是拿过来就做。多看看,如果数值超过10000,在图表中就会显得很乱,也不好读数,所以用%%(除以10000)的方式减少数值位数的显示,可以增加可读性,提高图表整体的观感。excel如何把数据做成图形
在J3单元格输入函数:
=SUM(B3,D3,F3,H3)…月度计划合计量
在K3单元格输入函数:
=SUM(C3,E3,G3,I3)…月度实际和计量
在L3单元格输入函数:
=SUM(B3,D3,F3,H3)+N(J2)…累计计划量
在M3单元格输入函数:
=SUM(C3,E3,G3,I3)+N(K2)…累计实际量
步骤2:制作图表
选中A2:C14单元格区域,插入图表——簇状柱形图。
选中系列图形,鼠标右键——设置系列格式,调整系列重叠为(0%),调整间隙宽度(50%)。
去掉无用元素,更改文本字体(微软雅黑),更改数值字体(ARIAL),更改标题内容,添加表元素,最后再进行一波配色:黄色(254、248、136),红色(229、70、70),蓝色(16、54、103)。下表的亮点在于,标题部分将数据中重要的类型内容体现了出来,方便阅读者更直观地进行数据的对比,如下图所示。
给它起个好吃的名字——西红柿鸡蛋图。
分别再以对应的数据源,制作AFS、AW、GMMC三个图表,用按住ALT键,再移动图表的锚定方式,将4个小图组合起来。excel如何把数据做成图形
用同样的方法制作最后的合计图,注意的是,同一个图表中制作累计折线选择为次坐标轴即可。
到这里,设计制作的过程完成了,但是有时候事情的发展总是不会按照我们设想的那样进行,这位学生又来和我诉苦了,领导不同意用这个图,还是希望用原来的图来做。
【反转,再设计】
【第二次的制作过程】
步骤1:清洗数据
在B3单元格输入函数:
=ROUND(INDEX(发刊数据!$B$3:$Y$6,MATCH(LEFT($A3,LEN($A3)-2),发刊数据!$A$3:$A$6,0),MATCH(B$2,发刊数据!$B$2:$Y$2,0))%%,2)excel如何把数据做成图形
函数思路和上面讲到的一样,大家自己理解一下。关键看下面说的操作手法:
选择B3:B6单元格区域,按CTRL+D,填充函数;再选择B3:C6单元格区域,向右拉动填充柄,填充到Y列。此时就形成了,隔一列空列的数据形式。
在C7单元格输入函数:
=ROUND(INDEX(发刊数据!$B$3:$Y$6,MATCH(LEFT($A7,LEN($A7)-2),发刊数据!$A$3:$A$6,0),MATCH(I$2,发刊数据!$B$2:$Y$2,0))%%,2)
用同样的方式填充到Y列。
在B11单元格输入函数:
=IF(B3="",A11+(C11-A11)/2,SUM($B$3:B6))向右拉填充柄,填充函数到X11单元格。
函数解析:对于IF函数的使用,相信同学们在理解上不会有难度的。但是为什么要这样做,其目的就在于,B11:X11单元格区域是代表累计计划数量走势折线图的源数据,为了让这条折线可以和柱形图的月份对应上,我们在空置列的地方,加了一个连接两端数据的点,这样可以使折线平滑上升。
在B12单元格输入函数:
=IF(SUM(C7:Y10)=0,NA(),IF(B7="",SUM($C$7:C10),A12+(C12-A12)/2))向右拉填充柄,填充函数到X11单元格excel如何把数据做成图形
函数解析:和B11单元格的作用是一样的,代表了实际数量折线图的数据源。这里的NA(),是指如果实际数量等于0的时候,不显示数据点。
步骤2:制作主图表
选中A2:Y10单元格区域,插入图表——堆积柱形图。
从这个图中的系列图形的颜色,大家不难发现,柱形系列已经有了区分,之前的计划柱形和实际柱形只能同时编辑,而现在就可以分开调整了。
去掉无用的图表元素,排版,配色,一波操作我们就不详细的说了,给大家一组配色方案吧。
因为在开始的时候,发现源数据上有売上、计画、実績这样的文本,猜想是一个日企,那么就给他们配了一组日本风格的色系。主颜色就是上面的配色,可以通过透明度、阴影、三维效果让颜色有更多的变化。
步骤3:加入累计走势折线
选中图表绘图区,单击鼠标右键,点选选择数据,将累计数据系列添加进图表。
然后更改图表类型,改成带平滑线和数据标记的散点图。
再将折线改成次坐标显示,并通过改变线条颜色、粗细等,美化折线图。
及此也就完成了我们的图表制作。
最后,教大家一个简单拖拽创建表单和生成报表的方法。使用简道云表单在线创建“云表格”,通过简单拖拽,即可制作出一个个柱形图、折线图、饼图等图表,1分钟实现数据可视化分析!非常方便~