实用excel技能分享:一个图表来获得数据比较，趋势和比例贡献（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细胞功能:=总和（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技巧分享:快捷方式示例【Ctrl+E】
• Excel函数学习的选择函数vs IF函数
• 【整理分享】10组神奇实用的Excel快捷键，大神也在用！（excel快捷键及excel常用技巧整理）
• 实用Excel技巧分享:快速拆分工作表的两种方法(excel2019中的拆分工作表)
• 实用excel技巧分享:查找和替换的两种绝妙方法（在Excel中查找和替换的位置）

本文由主机参考刊发，转载请注明：实用excel技能分享:一个图表来获得数据比较，趋势和比例贡献（Excel比较趋势图） https://zhujicankao.com/100272.html