excel图表可视化利润表的变化过程

今日分享主题：excel图表可视化利润表变化的过程。

先说明下：本文操作使用的是2019版本，2013版本以上操作界面也差不多。如果是2013以下版本，操作界面可能会有差异，但功能都有，要自己找找。

我在昨天的文章说了瀑布图也可以用条形图来制作。今天就继续来分享条形图制作瀑布图的一个实际应用场景：利润表的变化过程。

直接举例来说，这是大家常见的利润表（数据虚拟）：

通常情况下，我们都是直接以这样一个纯数据的Excel表去呈现的。虽然利润表科目已经很精简，但从可视化的角度说，数据量不算少，看起来并不直观。

这个时候，我们可以借助瀑布图来呈现，效果会有明显的改善：

那这个图怎么来做的呢？根据前面介绍瀑布图的做法，我们知道，要添加辅助数据，借助堆积条形图来实现。

这其中添加辅助数据是最关键的，而且也是最烧脑的。前面做最基本的瀑布图时一张瀑布图告诉你每个月的钱都花哪儿去了，也有朋友反馈不太能理解原理，所以我后面又专门写了都说瀑布图难做，其实只是你没理解原理这篇文章来进一步说明。

现在到了一个实际应用场景，原始数据并不标准，所以辅助数据还不能完全照搬原来的逻辑来添加，得仔细思考做一些调整。但因为有了前面的基础，这个思考会简单很多。

我们知道，无论怎么呈现，瀑布图的原理都是从一个起点出发，然后经过增减变动，到达一个结果。

在这里，显然是从“一、营业总收入”出发，最终到“五、净利润”。这个过程中除了收益、成本费用科目参与了单纯的增减计算外，利润类科目比如“三、营业利润”，本身只是一个结果，并不是作为增减项目。

所以，这与我们前面讲的瀑布图的基本做法确实不一样，可以说更复杂了。怎么办呢？那就把复杂问题拆解。先从科目开始。

这里无非就是3种性质的科目：收入类、成本类、利润类。除了利润类科目，收入类、成本类科目都参与计算，而且收入类与成本类的所代表的科目性质还不一样。收入类科目代表增加，成本类科目代表减少。

但是它们在利润表中的数据都是正数，看不出方向。而我们做瀑布图是要考虑方向的，增加为正数，减少为负数。所以，这里我们可以根据性质给每个科目增加正负号，然后生成带正负号的金额。

利润类科目方向本来也应该是“+”，但后面在添加辅助数据时，这个“+”会参与计算影响结果，所以这里用“=”表示。

现在D列的数据有正有负，数据本身就体现了科目的增减性质，这就相当于我们到这一步才准备好了初始数据源。接下来，我们就来考虑添加辅助数据。

为了更好地理解，我还是从图表出发来说明。从图表中可以看出，这是由3组数据做出的堆积条形图。

其中呈现出来的2组，正好是利润表中科目指标大小，而且都是朝着横坐标轴的正方向，这就意味着呈现出来的所有数据都要取正数，而且收入类与利润类为一个数据系列，成本类科目为另一个数据系列。

所以，这2组条形图的数据源就可以添加出来，以第2行为例：

正（收入类、利润类科目）：=IF(D2>0,D2,0)

负（成本类科目）：=IF(D2<0,-D2,0)

另外还有一组，其实是靠近纵坐标轴的占位，只不过其条形图被填充为无色，所以看起来没有。

接下来，我们再来看占位的数据要如何取。占位的起点肯定是0，所以我们关键要知道占位的末端数取值。而占位的末端数取值又要等于各指标呈现在条形图中的起点。所以，确定占位的取值，也就是确定指标条形图的起点取值。

从图表中看出，除了“一、营业总收入”科目没有占位，还有利润类科目也没有占位，所以意味着利润类科目的占位数据要为0。

此时，占位取值的公式可以局部写出来：=IF(C3=”=”,0,再思考收入类或成本类科目如何取值）。

这里就体现了前面说的为啥利润类科目的方向要写成“=”了，否则就会与收入类科目混淆。

而进一步观察，当科目是成本类时，其条形图的起点是前面各科目累积（包含当前科目）；当科目是收入类时，其条形图的起点是前面各科目累积（不包含当前科目）。这样，我们就很容易写出占位的公式。考虑到第1项无占位，故以第2项“营业成本”为例：

占位=IF(C3=”=”,0,IF(D3>0,不包含当前科目的前面各科目累积,包含当前科目的前面各科目累积))。

进一步，再把各项科目累积公式写出来：=SUMIF(C$2:C2,”<>”&”=”,D$2:D2)。

这样，我们就把占位的公式也分步写出来：

到这里，千山万水，我们终于把完整的数据源准备好了。接下来就是具体作图。

1. 选择合适图表。

按住Crtl键，选中E1:F18，H1:H18，插入堆积条形图。

2. 条形图设置逆序类别。

双击纵坐标轴，弹出“设置坐标轴格式”窗格，在“坐标轴选项”下勾选逆序类别。（这是条形图独有的操作）

3. 调整数据系列顺序，将“占位”紧挨着纵坐标轴。

选中图表，右击“选择数据”，弹出“选择数据源”对话框，选中“占位”，单击“上移”箭头移到最上面。

4. 将“占位”数据系列填充为无色。

选中占位条形图，右击“设置数据系列格式”，弹出“设置数据系列格式”窗格，在“填充”下勾选“无填充”。

5. 美化图表。

至此，图表的大致轮廓就出来了，接着就是一些美化：删除网格线、删除图例、调整条形图间隙、调整坐标轴大小、颜色填充、字体、锚定对齐到单元格等（具体操作就不展开了）。

今天的分享就到这里，非常感谢你的耐心阅读，希望对你有所启发。