下载掌阅APP，畅读海量书库
立即打开
本质上同比分析和环比分析都是对比分析内容，因为要把今年的数据与去年同期相比，把这个月数据和上个月相比，归根到底，就是一个比较分析。
如果老板问，今年销售收入与去年同期相比，增长情况如何？也许马上就会想到用Excel绘制一个柱形图，如图2-82所示。本案例素材是“案例2-5.xlsx”。
这个图表并不完美，缺少了一些重要信息，例如，增长率是多少？具体增长了多少？如果再添加一个上升或下降的箭头，是不是更有对比效果？
首先，两根柱形并不是同一类数据，一个是去年的，一个是今年的，因此需要用两种不同的颜色来表示，这样对比才明显。
其次，根据实际情况是同比增加还是同比下降，添加一个上升箭头或下降箭头，醒目标识同比增长或同比下降，这样的对比分析效果就更加强烈。
最后，在图表上插入一个文本框，显示同比增长率情况说明。
这样就得到一个内容丰富的同比分析图表，如图2-83所示。
图2-82 两年销售额同比分析
图2-83 修饰美化后的同比分析图表
还要注意一个问题是，在绘制单一两根柱形时，有时数值轴并不是从0开始的，而是被自动设置了，这样会造成错误的理解，如图2-84所示。从图表上看，今年似乎比去年下降很多，实际上并非如此，出现这种情况的原因是，数值轴刻度的最小值是自动设置的，设置为了2080，这样显得两个柱形相差很大。
此时，需要将数值轴的最小刻度设置为0，才能得到正确的图表，如图2-85所示。
图2-84 数值轴最小刻度被自动设置，两个柱形看起来相差很大
当然，有时需要强化其之间的差异，这时，可以将数值轴的最小刻度设置为一个合适的值。
图2-85 设置数值轴的最小刻度为0
如果要分析的各项目之间各自独立，没有关联性，例如各产品销售的同比分析，此时，需要合理设置去年柱形和今年柱形的颜色和重叠比例，以便使图形看起来更加美观，信息更加清晰。
图2-86是各产品两年销售的同比分析报表，那么，应该如何绘制可视化同比分析图表来清晰展示出每个产品的同比增长情况？本案例素材是“案例2-5.xlsx”。
这个表格反映了两个重要信息：一个信息是总销售的增长情况，另一个信息是每个产品的销售增长情况。
图2-86 各产品两年销售同比分析报表
总销售增长情况可以采用2.6.1节制作单一的同比分析的方法制作图表；每个产品的同比增长情况，则需要使用两个图表来表达：一个是柱形图，一个是折线图（不显示线条，仅显示标记点）。
将3个图表进行组合，在左上角添加一个形状，填写文字说明，最后的可视化分析报告如图2-87所示。
图2-87 产品销售同比分析报告
各产品销售同比分析图表是普通柱形图，重点是设置系列的间隙宽度和重叠比例，以及去年柱形和今年柱形的填充颜色。这个设置比较烦琐，需要一点一点来仔细设置。
各产品增长率就是一个简单的折线图，不显示线条，设置数据点标记格式，然后再设置同比增加和同比下降百分比数字的自定义格式，自动根据正负数显示不同颜色，再显示每个数据点的垂直线。
有些情况下要做的同比分析的数据系列属于两种不同类型的数据，但其之间有逻辑上的关联，例如销售额和毛利，毛利是销售额的一部分，两者比例就是毛利率，毛利占销售额比例越大，毛利率则越大。对于这样的问题，可以绘制嵌套柱形图来比较二者的大小，外部柱形是销售额，内部柱形是毛利。
图2-88是同时考虑销售额和毛利的同比分析图表，毛利嵌在销售额中，在坐标轴标签上同时显示年份名称和毛利率。
在Excel中，这个图表绘制并不复杂，主要是设置次坐标轴和辅助坐标轴。下面是主要制作步骤。
首先绘制普通柱形图，如图2-89所示。
图2-88 销售额和毛利同比增长分析
图2-89 绘制的普通柱形图
将系列“毛利”绘制在次坐标轴上，如图2-90所示。
图2-90 将系列“毛利”绘制在次坐标轴
删除右侧的次数值轴，然后分别选择销售额和毛利，分别设置其间隙宽度，将销售额和毛利分别显示出来，如图2-91所示。
图2-91 分别设置销售额和毛利的间隙宽度
在单元格设计辅助坐标轴标签，使用公式将年度名称和毛利率组合成新字符串，如图2-92所示，单元格D7公式为：
=D2&CHAR(10)&"毛利率 "& TEXT(D4/D3,"0.0%")
图2-92 设计辅助坐标轴标签
然后将图表的分类轴引用区域修改为这个辅助坐标轴区域，得到如图2-93所示的图表。这样，坐标轴标签中就同时显示年份名称和毛利率。
图2-93 修改调整分类坐标轴引用区域
最后对图表进行适当的格式化和美化，完成最终图表的制作。
如果要将每个产品销售放在一起做同比分析，不仅要观察每个产品的同比增长情况，还需要看所有产品合计数的增长情况，此时，可以绘制堆积柱形图。
对图2-94所示的数据，如何对比分析各产品各年的销售，并观察所有产品销售合计的各年变化趋势？本案例素材是“案例2-5.xlsx”。
图2-94 2018—2021年各产品销售统计
图2-94所示的表格可以绘制如图2-95所示的堆积柱形图，并显示系列线，将图例显示在右侧，调整各项目的先后顺序使其与工作表顺序相同，这样的图表是比较清晰的。
图2-95 分析各产品历年销售
一般堆积柱形图中，调整各项目的先后次序是一个必需的操作，因为需要保持图表上各项目的上下顺序与工作表上各项目的上下顺序相同。
这种顺序调整是在“选择数据源”对话框中进行。选择某个项目，单击“上移”或“下移”按钮即可，如图2-96所示。
图2-96 调整各项目的先后次序
某些具有特殊意义的数据使用普通柱形图或者条形图，都无法把数据的含义准确无误地表达出来。例如，资金的流入和流出对比，今年的财务指标与去年的财务指标对比，此时，可以绘制两个方向的条形图。
图2-97是两年的主要财务指标，现在要求绘制如图2-98所示的分左右条形的图表（有人称之为旋风图）。
图2-97 两年财务指标
图2-98 两年财务指标对比图
图2-98是堆积条形图，需要先设计G～J列的辅助区域，如图2-99所示。H列中去年数据引用原始数据中去年的负数，J列今年的数据引用原始数据中今年的正数，中间I列输入一个固定的百分比数字，用于显示财务指标名称。
公式设计完成后，再对H列设置自定义数字格式，将负数显示为正数，自定义数字格式代码为：
0.00%;0.00%;0.00%
图2-99 设计辅助区域
用辅助区域G～J列绘制基本堆积条形图，如图2-100所示。
图2-100 绘制的基本堆积条形图
然后设置图表格式，包括设置坐标轴逆序类别，将中间的条形设置为无填充、无线条，添加数据标签为“类别名称”，两侧的条形添加数据标签为“值”，调整间隙宽度，设置条形颜色，调整数字标签位置，等等。
图2-101是公司各部门两年的入职人数和离职人数统计报表。现在要制作一个分析各部门两年流动性的分析报告。
本案例素材是“案例2-6.xlsx”。
图2-101 各部门两年入职人数和离职人数统计报表
首先绘制总公司两年入职和离职人数的对比分析图表，如图2-102所示。这个图表的特点如下。
（1）横向上，分别比较两年的入职人数和两年的离职人数，例如，入职人数去年是81人，今年是109人；离职人数去年是107人，今年是139人。
（2）纵向上，分别比较每年的入职人数和离职人数。例如，去年入职人数是83人，离职人数是107人；今年入职人数是109人，离职人数是139人。
图2-102 公司两年入职人数和离职人数对比分析图表
这个图表的绘制数据区域是图2-103所示的辅助区域，图表是堆积条形图，设置起来比较烦琐，与2.6.5节介绍的两年财务指标对比分析图一样，这里不再赘述。
图2-103 设计辅助区域
当分析每个部门的两年流动性时，至少需要从两个角度来分析。
（1）去年和今年的入职人数对比，发生了什么变化。
（2）去年和今年的离职人数对比，发生了什么变化。
因此，可以绘制如图2-104所示的左右布局的对比分析图，其制作步骤如下。
图2-104 各部门去年和今年入职人数和离职人数对比分析图
首先设计辅助区域，如图2-105所示。可以使用VLOOKUP函数快速从原始数据区域查找数据，没必要一个一个单元格进行链接。
例如，单元格R3公式为：
=-VLOOKUP(O3,$B$4:$F$10,2,0)
单元格R3公式为：
=-VLOOKUP(O3,$B$4:$F$10,4,0)
其他单元格公式以此类推。
图2-105 设计辅助区域
以辅助区域绘制堆积条形图，如图2-106所示。
图2-106 绘制基本的堆积条形图
将系列“入职人数”“年份”和“离职人数”绘制在次坐标轴上，如图2-107所示。然后将主坐标轴的分类轴区域设置为O列的部门区域，将次坐标轴的分类轴区域设置为P列的年份区域。
图2-107 将系列“入职人数”“年份”和“离职人数”绘制在次坐标轴
添加次要纵坐标轴，将主分类轴和次分类轴均设置“逆序类别”，并设置系列的重叠比例为100%，间隙宽度为50%，得到如图2-108所示的图表。
图2-108 设置坐标轴格式
删除图表左侧的次要纵坐标轴、图表顶部水平轴，并设置不显示底部水平轴的标签，得到如图2-109所示的图表。
图2-109 继续设置坐标轴格式
选择系列“年份”，设置为无填充、无轮廓，添加数据标签（标签显示类别名称），得到如图2-110所示的图表。
图2-110 显示年份名称
最后将图例移到图表顶部，并删除图例中的不需要项，修改图表标题，设置条形颜色，手动插入水平线条，将各部门数据上下隔开，就得到了用户需要的图表。