小課堂：如何用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- 下載案例表格

下載案例檔案

，可以跟著這篇文章一起動手練習。

連結： https：//pan。baidu。com/s/1LaWrsde9CwazLOXsPrJuNg 提取碼： 3hdq

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

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，設定自定義單元格格式為“；；；”，可以把數字隱藏起來。

3- 總結

瀑布圖的難點在輔助列

這個瀑布圖圖的圖表製作部分，難度並不大，真正難的是輔助列的計算，以及原理的理解。

如果你是財務，想知道「瀑布圖」「步進圖」的製作原理，請在評論區裡告訴我。

本文翻譯：pakaccountants。com，強烈推薦這個網站給Excel圖表愛好者，裡面有大量實用的Excel圖表製作教程，包括：

- 啞鈴圖

- 損益圖

- 甘特圖

- 象限圖等等

如果你的英文還可以的話，可以點選「閱讀原文」，直接訪問這個網站。

如果你的英文一般，請把文章連結貼在評論區 ，回頭我翻譯給大家看。

好了，今天就到這裡，下課！

我是拉小登，一個會設計表格的Excel老師