小课堂：如何用Excel制作，财务损益表瀑布图
今日目标：
学习瀑布图的制作
原文标题：《Create Waterfall Charts in Excel – Visualize Income Statements!》
原文作者：Hasaan Fazal
原文地址：https://pakaccountants.com/better-profit-and-loss-statements-waterfall-charts-excel/
在Excel中使用瀑布图，来呈现总数的增长和下降，可以非常直观的看到，数数据从起点到终点的变化过程。
看过这篇教程，在Excel中创建瀑布图，并没有你想象的那么难！
甚至说，很简单！
在财务的工作报告中，通常会有大量的数字。你可能会用特定的格式，来标记有关键数字，让报告更加整洁。
但是数字就是数字，大多数时候需要一定的经验，才能理解每个数字的含义。
但是，如果我们可以把数字变成图表，变的可视化呢？用图形、图表来呈现数字，即便是非财务、会计人员，也能很好的看懂报告。
这就是我们今天要学习的：损益表+瀑布图。
从上图你可以看到，图表和左边（或右边）的损益表数字是保持一致的，这样可以产生视觉上的关联，更具吸引力和易于理解。
想象一下你的Excel仪表板中使用了这个图表！那简直太酷了。
接下来是操作步骤。
1- 操作步骤
1- 下载案例表格
评论区置顶评论，下载案例文件，可以跟着这篇文章一起动手练习。
2- 准备辅助列数据
在D7单元格中，输入辅助列名称：Subtotal。
然后按照下面的规律，给其他辅助单元格输入名称：
– E7: “Working”
– F7: “Padding”
– G7: “Right +ve”
– H7: “Left +ve”
– I7: “Right -ve”
– J7: “Left -ve”
3- 添加小计公式
在「Subtotal」列，手动添加公式，直接引用每项的起始数值，计算出每个分类的小计。
4- 数据累计求和计算
在单元格E8中输入下面的公式：
=IF(D8<>””,D8,SUBTOTAL(9,C$8:C8))
这个公式，只是计算D8是否为空，如果不为空，就对C8上方的数据进行累计求和。
5- 添加Right +ve公式
在G8单元格中，输入下面的公式，然后向下拖动填充公式。
=IF(ISBLANK(D8),IF(OR(E8<0,C8<0),0,MIN(E8,C8)),0)
6- 添加Left +ve公式
在H8单元格中，输入下面的公式，然后向下拖动填充公式。
=IF(ISBLANK(D8),IF(AND(C8>0,E8<0),-C8,IF(AND(C8>0,E8>0,E8<C8),-(C8-E8),0)),0)
7- 添加Right -ve公式
在I8单元格中，输入下面的公式
=IF(ISBLANK(D8),IF(AND(C8<0,E8>0),ABS(C8),IF(AND(C8<0,E8<0,E8>C8),ABS(C8-E8),0)),0)
8- 添加Left-ve公式
在最后一列的J8单元格中，输入下面的公式：
=IF(ISBLANK(D8),IF(E8>0,0,IF(AND(C8<0,E8<0),MAX(C8,E8),0)),0)
9- 添加Padding列公式
现在最后一点，但非常重要，决定我们这个瀑布图是否成功。
在单元格F8并输入以下公式：
=IF(OR(D8<>””,AND(G8<>0,H8<>0),AND(I8<>0,J8<>0)),0,MIN(ABS(E7),ABS(E8))*SIGN(E8))
公式全部填写完成后，效果如下：
这样，所有的基础数据就准备好了。
10- 添加图表
选择「Items」数据列，然后按住Ctrl键，分别选择「Subtotal」「padding」和最后的4列「Rigth+ve」「Left+ve」「Right-ve」「Left-ve」。
在「插入」选项卡中，找到「图表」功能组，插入一个「堆积柱形图」，然后你会看到一个非常「丑」的图表。
不要慌，接下来我们就来美化这个图表。
11- 设置图表属性
首先，删除无用的图表元素。
鼠标点选「Chart title」图表标题，按下delete键删除掉。
然后在纵坐标上点击右键，在右侧的属性面板中，找到并勾选「逆序类别」选项，把坐标轴顺序调换过来。
12- 隐藏Padding数据系列
选中Padding数据系列（在底部的图例中，可以找到Padding数据系列的颜色）。
然后设置颜色填充为「无填充」。
现在是不是有点瀑布图的感觉了？接下来我们再来做一些细致的调整，让图表变的更好看。
13- 设置间隙宽度
在任意数据条上点击右键，选择最后一项「设置数据系列格式」，然后设置「间隙宽度」为30%。
14- 删除坐标轴和图例
删除图例和坐标轴。
15- 隐藏y坐标轴标签
选择纵坐标轴，然后右键设置坐标轴样式，找到「标签」选项，设置标签为无，这样可以隐藏纵坐标轴。
16- 设置y坐标轴样式
为了让纵坐标轴更加的明显，选中纵坐标轴，把线条设置的粗一点，颜色设置为黑色。
这样可以让用户清楚的看到损益的平衡，是偏向左边还是偏向右边。
17- 区分数据系列颜色
图表中总共有4个数据系列，为了让数据对比更加的直观，我们把颜色统一为2种。正数用黑色，负数用红色。
设置完颜色后，可以随机修改一下「Revenue」的数值，看看数据条是否显示正常。
18- 隐藏图表数据，美化图表
最后一步了，选择图表的任意位置，然后设置图表的背景填充为「无填充」，边框样式为「无边框」。
这个时候，背后的数字会显示出来，选中这些单元格，按下Ctrl+1，设置自定义单元格格式为“;;;”，可以把数字隐藏起来。
然后调整一下图表的宽度和位置，损益瀑布图表就大功告成了。
为了让图表更加的干净，我还在「视图」选项卡中，把「网格线」关掉了。
这个动态瀑布图还有一个好处，就是他的大小是固定的，数据系列会根据数字的大小，自动的变化。
我就说，用Excel做瀑布图不难吧！
2- 下载案例文件
评论区置顶评论，下载本节案例文件
3- 总结
瀑布图的难点在辅助列
这个瀑布图图的图表制作部分，难度并不大，真正难的是辅助列的计算，以及原理的理解。
如果你是财务，想知道「瀑布图」「步进图」的制作原理，请在评论区里告诉我。
图表学习网站推荐
本文翻译：pakaccountants.com，强烈推荐这个网站给Excel图表爱好者，里面有大量实用的Excel图表制作教程，包括：
– 哑铃图
– 损益图
– 甘特图
– 象限图等等
如果你的英文还可以的话，可以点击「阅读原文」，直接访问这个网站。
如果你的英文一般，请把文章链接贴在评论区 ，回头我翻译给大家看。
好了，今天就到这里，下课！
我是拉小登，一个会设计表格的Excel老师
= = 推荐文章 = =
https://pakaccountants.com/category/excel-charts/
链接: https://pan.baidu.com/s/1LaWrsde9CwazLOXsPrJuNg 提取码: 3hdq
联系作者