二维码 购物车
部落窝在线教育欢迎您!

用excel做一张更“懂”领导心思的图表(三)

 

作者:E图表述来源:部落窝教育发布时间:2020-07-28 15:03:16点击:271

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!本篇教程是做懂领导心思图表的第三篇,分享多系列数据图表的制作经验。多系列数据图表的制作,在工作中,一直是个难题。由于数据系列较多,做出来的图表看着总显得混乱,各数据之间的对比性也不强,基本违背了我们作图的初衷。那有没有什么好的思路或者方法来制作多系列的图表呢?下面我们就来看看excel高手是如何进行制作的。

 

【前言】

 

很多同学都问过作者E图表述,如何才能快速的学到EXCEL。这个话题,好像从EXCEL2003的年代,就一直有人问。其实说简单也不简单,说难也不是很难,最主要的是,你每天能和EXCEL有多少的互动,并且这份互动的内容是不是总会有新的方式来呈现。

 

什么意思呢?换一种方法来说,现实生活中,你对周围的人简单的分个类:“认识的”和“不认识的”,那么你总结一下,什么样的人是你认识的呢?是不是那些和你有过接触的人就会是认识的,另外没有接触过的就有可能是不认识的了;再在“认识的”人中再分类:“熟悉的”和“不熟悉的”,那是不是那些经常在工作中或生活上频繁接触的人就会是熟络的人,另外的不太有交集的人,即便是认识也不会是很熟的人;再在“熟悉的”人中再分类:“知心的”和“好朋友”,不用再分析了吧,都是成年人了,这些就是同学们应该了解的“处世之道”。想融入一个“圈子”,就要多和圈子里的人用心地去接触去互动去交流。

 

【正文】

 

学习EXCEL其实和与人交往的过程很像,同学们和EXCEL接触的时间长短,接触的内容广泛度,对它的用心程度,这些就能把EXCELER们区分为“认识”“熟悉”“知心”的等级。

 

对于作者:E图表述来说,我应该算是EXCEL的好朋友吧,因为我们“见面的机会相当的多,平均每天我都会使用10个小时以上,涉及的内容也很全“操作、函数、数透、图表、VBA ”,所以……,希望你能GET到我的“学习方法”。

 

前些日子有一位学生,找我问一个图表的问题,下面是她的原图:



 

这是一个发刊阅读量的计划/实际的数量对比图,这个图表中有几个问题:

 

1.堆积柱形图系列中,计划数量和实际数量是混着的,不能快速从图表中找到相应的数据;

2.代表各月累计数量的走势折线,没有分布到相对应的各个月份上,造成观感上的错觉;

3.主标题不够明显,对于各个系列的数据对比不能一目了然。

 

【设计制作新图表】

 

根据上面的这些内容,E图表述也给出了一些建议,同时做了一份“组合图”,如下所示:



 

【制图思路】

 

看到这样的一个数据源,按照观看者比较容易能够接受的图表习惯来说,对比类的最好是柱形图,走势类的最好是折线图。

 

在这样的一个思路基础上,作者没有使用其他图表的表现形式,延用了惯性思维,但是有一个原则要坚持:一个图表中的信息量不要太多,否则会显得很乱,而且让观阅者,不容易抓到重点信息。

 

所以,我们采用了一组组合图表来表达每一个系列的值。

 

【制作过程】

 

步骤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)的方式减少数值位数的显示,可以增加可读性,提高图表整体的观感。

 

 

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),更改标题内容,添加表元素,最后再进行一波配色:黄色(254248136),红色(2297070),蓝色(1654103)。下表的亮点在于,标题部分将数据中重要的“类型”内容体现了出来,方便阅读者更直观地进行数据的对比,如下图所示。



 

给它起个“好吃”的名字——“西红柿鸡蛋图”。

 

分别再以对应的数据源,制作AFSAWGMMC三个图表,用“按住ALT键,再移动图表”的锚定方式,将4个小图“组合”起来。

 

用同样的方法制作最后的合计图,注意的是,同一个图表中制作累计折线选择为“次坐标轴”即可。

 

 

到这里,设计制作的过程完成了,但是有时候事情的发展总是不会按照我们设想的那样进行,这位学生又来和我“诉苦”了,领导不同意用这个图,还是希望用原来的图来做。

 

好吧,作者E图表述前文已经写了之前的图表设计是有问题的,那么现在你的“雇主”非要“一意孤行”,怎么办呢?做事不入东,累死也没功。

 

【反转,再设计】

 

 

为了区分计划数量和实际数量,我一定要让系列区分开来,走势线也一定要对应到各个月份,否则我都会认为“图表没做到位”。

 

【第二次的制作过程】

 

步骤1:清洗数据


 

有的时候,同学们总会说作者E图表述的函数过程过于复杂,这是因为我们总要力求将工作表做成“自动化”的表格。今天为了方便同学们理解,就简化一下函数思考过程,介绍一种函数填充的方式。

 

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)

 

函数思路和上面讲到的一样,大家自己理解一下。关键看下面说的操作手法:

 

选择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单元格

 

函数解析:和B11单元格的作用是一样的,代表了实际数量折线图的数据源。这里的NA(),是指如果实际数量等于0的时候,不显示数据点。

 

步骤2:制作主图表

 

选中A2:Y10单元格区域,插入图表——堆积柱形图。

 

 

从这个图中的系列图形的颜色,大家不难发现,柱形系列已经有了区分,之前的计划柱形和实际柱形只能同时编辑,而现在就可以分开调整了。

 

 

去掉无用的图表元素,排版,配色,一波操作我们就不详细的说了,给大家一组配色方案吧。

 

 

因为在开始的时候,发现源数据上有“売上、计画、実績”这样的文本,猜想是一个日企,那么就给他们配了一组“日本风格的色系”。主颜色就是上面的配色,可以通过透明度、阴影、三维效果让颜色有更多的变化。

 

步骤3:加入累计走势折线


选中图表“绘图区”,单击鼠标右键,点选“选择数据”,将累计数据系列添加进图表。

 

 

然后更改图表类型,改成“带平滑线和数据标记的散点图”。

 

 

再将折线改成“次坐标显示”,并通过改变线条颜色、粗细等,美化折线图。

 

 

及此也就完成了我们的图表制作。

 

【编后语】

 

对于一组数据的图表表现,真的是仁者见仁智者见智,我觉得“西红柿鸡蛋图”可以很好表现数据对比和走势,但是人家领导不喜欢,那么我也只能按照他人的思路“再创作”。其实这个过程挺好的,我现在掌握的这些技能,就是在不断的“做、思考、再做”的过程中,慢慢积累下来的,希望同学们不仅要学技能,更要掌握学习的方法。

 

本文配套的练习课件请加入QQ群:1043683754下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel制作曲线图《年终报告,你需要这张Excel图表来“撑场面”》

Excel折线图新玩法5个小步骤,excel折线图比同事漂亮10

Excel告白图表一个苦逼Excel技术直男的追女日记:520,我为你今夜不眠!

Excel九宫格图表《秀翻全场!用excel制作一份“九宫格”图表》