EXCEL瀑布圖怎麼製作 - 老徐的Excel

下方有視頻

圖1

瀑布圖幫助我們直觀地了解正值和負值在一段時間或類別中的累積效應。一個常見的用途是在財務分析中跟蹤利潤或現金流是如何得出的。

圖2

我不喜歡類別標籤垂直旋轉, 唯一的方法, 使他們水平和隨時跟隨圖標。

另一個煩惱是你無法調整繪圖區域大小, 只能調整整個圖表區域。還有其他一些問題。

插入 Excel 瀑布圖

對於 Office 365 用戶創建瀑布圖很容易, 您只需要一些數據。

圖3

小貼士: 避免空行, 因為這些行將顯示為圖表中的間隙。此外, 必須將負值 (如支出) 作為負值輸入, excel 才能正確繪製這些負值.

選擇單元格 A5:B19 中的數據 > "插入" 選項卡 > 瀑布圖:

圖4

設置小計和總計;一個接一個地右鍵單擊圖表中的列 > "設置為總計":

圖5

你應該看到, 正列、負列和總列自動格式化為不同的顏色。

格式化提示

刪除網格線和垂直軸, 因為如果我們有數據標籤, 我們不需要它們。只需左鍵單擊即可選擇它們, 然後按 "刪除" 鍵。我甚至會刪除收入圖表中的圖例和標題.

圖6

早期版本中的 Excel 瀑布圖

現在, 如果你沒有 Office 365 不要絕望, 因為你可以用一些巧妙的技巧構建 Excel 瀑布圖。

您甚至可以構建瀑布條形圖, 並將其與 p 對齊 & L 如下所示:

圖7

我們先看一下瀑布柱形圖。

手動瀑布柱形圖數據

您將需要3個額外的列 (C:E, 如下圖所示) 來支持圖表:

圖8

列 C 包含總計。此系列將是柱形圖。

列 D 和 E 支持向上條系列。列 D 包含當前行之前的餘額的運行總計, 列 E 包含當前行的餘額的運行總計。這些值是向上/向下條形圖的底部和頂部。

創建手動 Excel 瀑布圖

第1步:選擇單元格 A5:A19 中的數據, 然後按住 ctrl 並選擇單元格 A5:A19

第2步:插入圖表;> 折線圖 "中插入選項卡。它應該如下所示:

圖9

第3步:更改小計和總計的圖表類型; 在圖表中選擇 "系列 1" 標記 > 右鍵單擊 > 更改系列圖表類型 > 從列表中選擇 "組合圖" (excel 2013 後續對話框如下所示):

圖10

第4步:添加向上/向下欄; 選擇圖表中的 "串行 2" 或 "串行 3" 行 > 單擊 + 小部件 > 選中 "向上" 欄的複選框:

註解: excel 2007 和2010用戶轉到 "圖表工具: 布局" 選項卡, 單擊 "向上條形圖" 按鈕, 然後從菜單中選擇 "向上條形圖".

第5步:隱藏兩行和標記; 選擇 > 行打開格式窗格 > 將 "行設置為" 無 ", 將" 標記 "設置為" 無 ".

圖11

第6步:設置向上的 (下) 條的顏色填充;左鍵單擊一次以選擇它們, 然後右鍵單擊以打開迷你工具欄並選擇新的 "填充" 和 "大綱" 顏色, 或者使用 "圖表工具" 選項卡上的 "填充顏色" 調色板:

圖12

根據需要對總計和正值重複顏色格式。

第7步:添加數據標籤;從 "圖表工具: 格式" 選項卡中, 從 "當前選擇" 下拉列表中選擇 "series1", 如下所示:

圖13

excel 2013 以後; 單擊圖表右側的 + 小部件按鈕 > 數據標籤.

excel 2007 和 2010(A)圖表工具: "布局" 選項卡 > 數據標籤.

這將向小計和總計列添加標籤。

第8步:對於 "向下" 條形圖標籤,您需要採取稍有不同的方法, 這種方法僅在 excel 2013 中可用; 從 "圖表工具: 格式" 選項卡下拉列表中選擇 "系列 2"。然後單擊圖表右側的 + 小部件 > 數據標籤 > "更多選項..."。> 標籤選項: "單元格中的值" > 選擇列 b 中的值:

圖14

注意：

對於 Excel 2007 或2010用戶, 沒有簡單的方法來添加標籤。在圖表中添加標籤將導致混亂, 您必須進行清理。要整理它們, 請使用2次左鍵單擊選擇每個標籤框, 然後在編輯欄中單擊並鍵入 =, 然後單擊圖表源數據表中包含標籤值的單元格, 然後按 ENTER。對剩餘標籤重複此操作。

第9步:調整; 刪除網格線、垂直軸、標題和圖例。你最終應該會得到這樣的結果:

圖15

瀑布圖

正如我前面提到的, 我不喜歡水平軸上的垂直標籤。它們很難閱讀, 這在一開始就違背了圖表的目的。

瀑布條形圖 (如下所示) 解決了這些問題。它需要對3個系列進行稍有不同的計算, 因為 "balance" 系列是條形圖可見部分的墊腳石, 實際上隱藏在圖表中。

在下圖中, 我以淺灰色顯示了條形圖的 "balance" 系列部分, 但稍後我們會將填充顏色設置為 "無", 以便條形的 "更改" 部分看起來像是浮動的。

圖16

創建手動瀑布條形圖

第1步:選擇數據單元A5:A19 > 按住 ctrl 並選擇單元格 A5:A19

第2步:插入圖表;> 堆疊條形圖插入選項卡

步驟 3: 修復類別訂單; 雙擊 "格式軸選項" 中 > 的軸標籤 > 選中 "按相反順序的類別"

步驟 4: 隱藏餘額系列; 在圖表中選擇系列 1, 即 "填充" 系列 > 將文件和邊框設置為無:

圖17

第五步:設置條填充顏色;一次選擇一個正值列，在我的例子中是「Sales」和「Consulting」，然後將填充顏色設置為不同的顏色。我選擇了藍色。

步驟6:格式化[可選];刪除標題、圖例、網格線和垂直軸並刪除它們。

第七步:定位圖表;將圖表與損益表對齊，覆蓋D:E欄，如下圖所示:

圖18

注意，我在第12行為'Expenses'頭添加了一個空行。這個圖表的另一個好處是，您可以避免源數據中出現空行，因為您使用了列A中的標籤作為圖表軸標籤。提示:如果不希望B列中的費用顯示負號，請使用自定義數字格式(注意，我已將值四捨五入到最近的千位)格式化，如下所示:

圖19

圖表將正確地處理它們，因為基礎值仍然是負數，但它們將像普通損益表一樣，不顯示帶負號的負數:

瀏覽器版本過低，暫不支持視頻播放