Skip to main content

在 Excel 中創建預算與實際差異圖表

Author: Xiaoyang Last Modified: 2025-05-12

在 Excel 中,預算與實際差異圖表用於比較兩組系列數據,並顯示這兩組數據的差異或方差。如果差異為負值,則顯示一種顏色的條形或柱狀;如果是正值,則顯示另一種顏色的條形或柱狀,如下截圖所示。本文將介紹如何在 Excel 工作簿中創建對比差異圖表以及單元格內的小型對比差異圖表。

Budget vs Actual Variance chart in Excel


在 Excel 中創建預算與實際差異圖表

要創建預算與實際差異圖表,請按照以下步驟操作:

1. 在 D2 單元格中輸入以下公式以計算預算和實際值之間的差異,然後向下拖動填充柄至需要的單元格,見截圖:

=C2-B2

steps of creating budget vs actual variance chart in Excel

2. 然後選擇 A 列和 D 列中的數據,然後點擊 插入 > 插入柱形圖或條形圖 > 簇狀柱形圖,見截圖:

steps of creating budget vs actual variance chart in Excel

3. 插入柱形圖後,您可以根據需要刪除圖表中不需要的元素,例如刪除圖表標題、垂直軸、網格線,您將得到如下截圖所示的結果:

steps of creating budget vs actual variance chart in Excel steps of creating budget vs actual variance chart in Excel steps of creating budget vs actual variance chart in Excel

4. 然後右鍵單擊水平軸,並從上下文菜單中選擇 格式化坐標軸 選項,見截圖:

steps of creating budget vs actual variance chart in Excel

5. 在打開的 格式化坐標軸 窗格中,在 坐標軸選項 標籤下,點擊 標籤 以展開此部分,然後從 標籤位置 下拉列表中選擇,水平軸將放置在圖表的底部,見截圖:

steps of creating budget vs actual variance chart in Excel

6. 然後,右鍵單擊任意一個條形,並選擇 格式化數據系列 選項,見截圖:

steps of creating budget vs actual variance chart in Excel

7. 在打開的 格式化數據系列 窗格中,在 填充與線條 標籤下,從 填充 部分,請進行以下操作:

  • 選擇 純色填充 選項;
  • 勾選 如果為負值則反轉 複選框;
  • 分別從 顏色 下拉列表中指定正負值的兩種顏色。

steps of creating budget vs actual variance chart in Excel

8. 設置條形顏色後,然後您應該添加數據標籤,請點擊選擇圖表中的條形,然後點擊 圖表元素 圖標,然後從 圖表元素 列表框中勾選 數據標籤,見截圖:

steps of creating budget vs actual variance chart in Excel

9. 柱形條太窄看起來不美觀,在這種情況下,您可以根據需要調整條形寬度,請右鍵單擊任意一個條形,然後選擇 格式化數據系列,在 格式化數據系列 窗格中,在 系列選項 標籤下,更改 間距寬度 至您需要的值,本例中我將其更改為 80%,見截圖:

steps of creating budget vs actual variance chart in Excel

提示:

創建圖表後,有時您可能希望數據標籤顯示不同的顏色,例如,正值數據標籤為綠色,負值數據標籤為橙色,請按以下操作進行:

從原始數據表中選擇 方差 列中的數據,然後按 Ctrl + 1 打開 設置單元格格式 對話框,在 設置單元格格式 對話框中,從 分類 列表框中點擊 自定義,然後在 類型 文本框中輸入以下代碼:

[Color10]General;[Color53] -General

steps of creating budget vs actual variance chart in Excel

然後點擊 確定 按鈕,您將得到所需的結果,見截圖:

steps of creating budget vs actual variance chart in Excel

注意:如果您需要其他顏色,請查看以下顏色索引表:

steps of creating budget vs actual variance chart in Excel


在單元格中創建小型預算與實際差異圖表

如果您想在單元格中插入小型預算與實際差異圖表,以下步驟可能會對您有所幫助:

1. 在 D2 單元格中輸入以下公式以計算預算和實際值之間的差異,然後向下拖動填充柄至需要的單元格,見截圖:

=C2-B2

steps of creating mini budget vs actual variance chart in cells

2. 然後顯示方差的負值,請在 E2 單元格中應用以下公式,然後將該公式複製到其他需要的單元格,見截圖:

=IF(D2<0,D2,"")

steps of creating mini budget vs actual variance chart in cells

3. 顯示方差的正值,請在 F2 單元格中輸入以下公式,然後將該公式複製到其他需要的單元格,見截圖:

=IF(D2>0,D2,"")

steps of creating mini budget vs actual variance chart in cells

4. 然後,您應該插入一個特定符號作為圖表條形,請點擊 插入 > 符號,在 符號 對話框中,從 子集 下拉列表中選擇 幾乎每種字體中都能找到的 方塊元素,然後點擊 全塊 符號,再點擊 插入 以插入該符號,見截圖:

steps of creating mini budget vs actual variance chart in cells

5. 插入特殊符號後,然後在 G2 單元格中應用以下公式,並將該公式拖動到其他單元格,您將得到以下結果:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
注意:在上述公式中,E2 是包含負方差值的單元格,A16 是包含您插入的特定符號的單元格,數字 100 是基於方差值計算符號數量的變量數據,您可以根據需要更改它。

steps of creating mini budget vs actual variance chart in cells

6. 繼續在 H2 單元格中應用以下公式,然後將該公式拖動到其他單元格,您將得到以下結果:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
注意:在上述公式中,F2 是包含正方差值的單元格,A16 是包含您插入的特定符號的單元格,數字 100 是基於方差值計算符號數量的變量數據,您可以根據需要更改它。

steps of creating mini budget vs actual variance chart in cells

7. 然後,選擇 G 列中的單元格,然後點擊 首頁 > 右對齊 以將數據右對齊在單元格中,見截圖:

steps of creating mini budget vs actual variance chart in cells

8. 最後,您可以根據需要分別為兩列設置字體顏色,您將得到如下截圖所示的結果:

steps of creating mini budget vs actual variance chart in cells


使用驚人功能創建預算與實際差異圖表

Kutools for Excel 提供了數十種 Excel 不具備的特殊類型圖表,例如 子彈圖目標與實際圖差值箭頭圖 等等。憑藉其便捷的工具——差異對比圖表,您可以快速輕鬆地在工作表中創建預算與實際差異圖表,或在單元格內創建小型預算與實際差異圖表。 點擊免費試用 Kutools for Excel!


下載預算與實際差異圖表示例文件

sample of creating budget vs actual variance chart in Excel


視頻:在 Excel 中創建預算與實際差異圖表


最佳的辦公生產力工具

Kutools for Excel - 幫助您脫穎而出

? Kutools AI 助手:基於智能執行生成代碼創建自訂公式分析數據並生成圖表調用 Kutools 函數…來徹底改變數據分析方式。
熱門功能查找、標記或識別重複值  |  刪除空行  |  合併列或單元格而不丟失數據  |  四捨五入無需公式 ...
超級 VLookup多條件  |  多值  |  跨多工作表  |  模糊查找...
高級下拉列表簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  比較列以選擇相同和不同的單元格 ...
特色功能網格聚焦  |  設計檢視  |  增強編輯欄  |  工作簿與工作表管理器 | 資源庫(自動文本)  |  日期提取器  |  合併資料  |  加密/解密儲存格  |  按清單發送電子郵件  |  超級篩選  |  特殊篩選(篩選粗體/斜體/刪除線...) ...
頂級 15 種工具集12 個文字工具添加文本刪除特定字符 ...)  |  50+ 圖表 類型甘特圖 ...)  |  40+ 實用 公式基於生日計算年齡 ...)  |  19 個插入工具插入QR碼從路徑插入圖片 ...)  |  12 個轉換工具金額轉大寫匯率轉換 ...)  |  7 個合併與分割工具高級合併行分割Excel單元格 ...)  |  ... 還有更多

Kutools for Excel 擁有超過 300 種功能,確保您需要的功能只需點擊一下即可實現...


Office Tab - 啟用 Microsoft Office(包括 Excel)中的分頁閱讀和編輯功能

  • 一秒鐘內在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標點擊,告別滑鼠手。
  • 當查看和編輯多個文檔時,您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的分頁功能,就像 Chrome、Edge 和 Firefox 一樣。