实用Excel技巧分享:得到数据对比、趋势和比例贡献的图表(数据对比趋势图)

在日常工作中，我们经常会遇到数据对比图，数据趋势图也是我们必备的图表技能。相信很多同学都能完成。但最近一个同学的精彩领导提出:“既要有对比效果，又要有趋势效果，还要有全年按月展示的多个系列图表，这些都要做成一张图！”

所以，在我们的工作中，“难”的不是技能不足，而是老板脑子宽。所以今天我给大家一个制作“多系列对比/趋势图”的思路。

[数据来源]

在我们阅读下面的文章之前，学生可以自己考虑一下，如果允许你设计这样的数据，你会是什么样子。

[正文]

如果只有一组数据。

没什么好说的，柱形图、条形图、环比图随便你用。

即使我们需要看到百分比和趋势线，我们也可以在数据的C列中插入一个百分比辅助列，如下所示:

第一步:选中单元格A1:B4的区域，在工具栏中插入-图表-聚类柱形图；

然后你得到柱形图的初稿。

步骤2:右键单击图表区的任意位置，然后在弹出菜单中单击选择数据。

在弹出的设置框中，点击左侧的“增加”系列，输入如下数据或选择工作表区域输入内容；

第三步:在图表区的任意位置单击鼠标右键，选择“更改图表类型”；

将比例系列的图表类型更改为“带平滑线和数据标记的散点图”。

这样，就获得了第二步的图表原型。

第四步:选择图中比例系列的图形，点击鼠标右键，在菜单中选择“格式化数据系列”选项；

在系列选项中，单击"次轴"、

获取如下图表:

我们来美化一下图表界面，加上数据标签，就不赘述了。举个例子吧。

再看看今天的主要数据。

不得不说，在实际工作中，图表的制作要重点突出一些数据。如果把很多信息挤在一张图里，就会很乱。

柱形图和折线图的组合(这里作者用的是散点图)很常见，这种组合既有对比又有趋势，非常方便观看。那么我们今天的主要数据也能达到这种效果吗？下面是一个例子:

[初步制图规划]

如果我们决定用柱形图和折线图来表示这个表格，那么我们需要在纸上或者脑海中有一个大概的草图:每组图形对应一个项目团队12个月的数据。难点是每组图形的底部要对齐；该多段线应出现在该项目的图形组中。

[图表类型选择]

很明显，如果多个柱形图叠加，我们应该选择“堆叠柱形图”，但如果用原始数据操作，就会是下面这个图。

每个项目组的底部没有对齐，这不是我们所需要的，但是如果我们在每个系列的中间插入一个“占位数字”，是否可以“抬高”每个系列的数字？然后将占位符图形的颜色和边框设置为“无色”，就可以模拟出每行都是底部对齐的柱形图的效果，如下图:

同学们可以看看。这里，我们删除了A点和B点的占位符图形的颜色，而C点的绿色占位符图形仍然保留。你能看到底部对齐了吗？

在空间被占用的情况下，我们可以在这些空间上添加分散的点来标注比例，最后将这些分散的点连接起来模拟折线图的效果。因此，我们使用“具有平滑线和数据标记的散点图”作为趋势线。

[分类数据]

我们需要在两个系列的图形之间插入一个占位符图形，如果在数据表中考虑，就是增加一个辅助数据。我们来看看数据处理阶段。

“好的图表往往不是来源数据做出来的”，善于利用辅助数据是使图表高级的重要标志，所以也有“图表的使用最终归结为函数的使用”的说法。

上图中的“红”字是一系列数据标签的显示值，“蓝”字是占位符图形，是图表中散乱点的标记点。我们来逐一看看各个数据的功能设计思路:

● A:项目A的数据标签值

B8细胞功能:=B2

●点a占位符:a项占位符图形。

B9细胞功能:=最大值($B$2:$M$4)*B8

注:B2:M4是源数据区，我们取数据中最大值的两倍作为图中最高比例参考，所以每个项目做出来的图会有一个统一的比例。其原理是:数据点值+对应的占位符值=数据源中的最大值*2，所以对应的占位符值=数据源中的最大值*数据点值。

●B:B项的数据标签值。

B10细胞功能:=B3

●b点占位符:b项占位符图形。

B11细胞功能:=最大值($B$2:$M$4)*B10

●C点占位符:C项占位符图形。

B12细胞功能:=B4

●C点占位符:C项占位符图形。

B13细胞功能:=最大值($B$2:$M$4)*B12

●A、B、C比例:A、B、C项数据点分别占本月所有项目数据总量的比例。

B14单元格功能:= B2/总和(B$2:B$4)

B15单元格功能:= B3/总和(B$2:B$4)

B16细胞功能:= B4/总和(B$2:B$4)

● A比例点:项目A的趋势图，对应散点的坐标位置。

B17细胞功能:=B8+B9*1/3

注意:散点图本身需要水平和垂直两个坐标点。如果我们不设置X轴，默认是序列号，如下:

为了更好地适应柱形图，我们的Y轴设置为“相应项目占位符的三分之一”。

●B、C比例点:B、C项目的趋势图，对应分散点的坐标位置。

B18单元格函数:=SUM(B8:B10)+B11*1/3

B19单元格函数:=SUM(B8:B12)+B13*1/3

注意:同学们要注意，每个项目的散点Y轴坐标都有“高度”的要求，所以他们的数据来源是累加其他项目的数据，加上对应比例点三分之一的位置，更接近对应的柱形图。

[图表制作]

数据整理最困难的部分已经完成了。还记得我们在制作一组数据时是如何插入图表的吗？试试看！

蓝色区域:柱形图

选择A7:M13区域，插入“堆积柱形图”，为A、B、c系列添加“数据标签”

黄色区域:散点图

按照文章开头为单组数据设计图表的操作步骤，重复步骤2和3。

选择B17:M17区域并插入“带平滑线和数据标记的散点图”。

选择B18:M18区域并插入“带平滑线和数据标记的散点图”。

选择B19:M19区域并插入“带有平滑线和数据标记的散点图”。

添加“数据标签”

添加散点图的数据标签，并选择数据标签。在格式数据标签中，将其设置为根据单元格中的相应值显示内容:

这样，学生将得到下面的图表“空白”。

[后期处理]

后期整理工作可以参照作者E图中表示的“一删二调三排”的做法来处理。

1.删除图表中的“纵轴”和“刻度线”以及不需要显示的图例；

2.调整柱形图颜色和平滑线颜色，在柱形图和平滑线上设置不需要显示为无线条和无填充的标记点。

3.通过以上操作，图表已经基本形成。选择一系列柱形图，并将间隙宽度调整为5%。

将柱形图上的数据标签位置调整到轴的内侧，将平滑线上的数据标签位置调整到中心；

然后调整图表中各元素的位置，改变字体、字体颜色等。，我们来完成上图的效果。

[编者按]

对于图表的制作，我建议不要使用Excel的所有默认效果。图表更像是平面设计。每个人的审美不一样，效果也不一样。但“可视化”既然是给人看的，就要讲究审美。最后分享一下今天图文教程用的这套配色方案，在“其他颜色-自定义”中输入RGB值即可。

相关学习推荐:excel教程

以上是实用Excel技巧的分享:一张图表可以得到数据对比、趋势、比例贡献等细节。更多请关注主机参考其他相关文章！

这几篇文章你可能也喜欢：

• 实用excel技巧分享:7个实用定位技巧帮你提高工作效率（Excel中定位功能有什么妙用）
• Excel函数学习巧用COUNTIFS函数在一分钟内得到没有复数的统计数据。
• 干货分享:word和excel文档如何转换（如何将Word和Excel转换成同一个pdf）
• Excel表格协作:与同事共享工作簿并制作表格！
• Excel图表学习制作折线图和柱形图组合图表（excel2003条形图和折线图组合）

本文由主机参考刊发，转载请注明：实用Excel技巧分享:得到数据对比、趋势和比例贡献的图表(数据对比趋势图) https://zhujicankao.com/93770.html