小课堂:如何用Excel制作,财务损益表瀑布图

作者: ladeng6666 分类: Excel 发布时间: 2020-11-16 10:52 阅读: 1,130

今日目标:

学习瀑布图的制作

原文标题:《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老师

= = 推荐文章 = =

小课堂:ROUND函数的3个用法,本以为我都会,涨见识了

小课堂:销售数据去重后统计数量,这个公式套路千万记住

https://pakaccountants.com/category/excel-charts/

链接: https://pan.baidu.com/s/1LaWrsde9CwazLOXsPrJuNg 提取码: 3hdq

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注