跳到主要內容

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

在Excel中,預算與實際差異圖表用於比較兩組系列數據,並顯示兩個數據系列的差異或方差。 如果差異為負值,則顯示一個彩色的條或欄,如果為正值,則顯示另一彩色的條或欄,如下所示。 本文,我將討論如何在Excel工作簿中創建比較方差圖和最小的單元格比較方差圖。


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

要創建預算與實際差異圖表,請執行以下步驟:

1。 在單元格D2中輸入以下公式,以計算預算和實際值之間的差,然後將填充手柄向下拖動到所需的單元格,請參見屏幕截圖:

=C2-B2

2。 然後,選擇列A和列D中的數據,然後單擊 插入 > 插入柱形圖或條形圖 > 聚集列,請參見屏幕截圖:

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

4。 然後,右鍵單擊水平軸,然後選擇 格式軸 上下文菜單中的選項,請參見屏幕截圖:

5。 在開 格式軸 窗格中的 軸選項 標籤,點擊 標籤 展開本節,然後選擇 來自 標籤位置 下拉列表,水平軸將放置在圖表的底部,請參見屏幕截圖:

6。 然後,右鍵單擊任一條,然後選擇 格式化數據系列 選項,請參見屏幕截圖:

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

  • 選擇 固體填充 選項;
  • 格紋 如果為負則反轉 複選框
  • 分別為正值和負值指定兩種顏色 顏色 落下。

8。 在設置條形的顏色之後,您應該添加數據標籤,請單擊以選中圖表中的條形,然後單擊 圖表元素 圖標,然後檢查 數據標籤 來自 圖表元素 列錶框,請參見屏幕截圖:

9。 列的欄是如此之窄以至於看起來並不漂亮,在這種情況下,您可以根據需要調整欄的寬度,請右鍵單擊任一欄,然後選擇 格式化數據系列格式化數據系列 窗格中的 系列選項 標籤,更改 間隙寬度 根據您的需要,在這種情況下,我將其更改為 80%,請參見屏幕截圖:

保養竅門:

創建圖表後,有時您可能需要以不同的顏色顯示數據標籤,例如,綠色的正數據標籤和橙色的負數據標籤,請按照以下步驟操作:

從原始數據表中的“差異”列中選擇數據,然後按 Ctrl + 1 打開 單元格格式 對話框中 單元格格式 對話框,單擊 習俗 來自 類別 列錶框,然後將以下代碼鍵入到 類別 文本框:

[Color10]General;[Color53] -General

然後點擊 OK 按鈕,您將根據需要獲得結果,請參見屏幕截圖:

備註:如果需要其他顏色,請查看以下顏色索引表:


在單元格中創建迷你預算與實際方差圖

如果要在單元格中插入迷你預算與實際方差圖,則以下步驟可能會對您有所幫助:

1。 在單元格D2中輸入以下公式,以計算預算和實際值之間的差,然後將填充手柄向下拖動到所需的單元格,請參見屏幕截圖:

=C2-B2

2。 然後,顯示方差的負值,請將以下公式應用到單元格E2中,然後將公式複製到所需的其他單元格中,請參見屏幕截圖:

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

3。 顯示方差的正值,請在單元格F2中鍵入以下公式,然後將公式複製到所需的其他單元格中,請參見屏幕截圖:

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

4。 然後,您應該插入一個將用作圖表條的特定符號,請單擊 插入 > 符號,並在中 符號 對話框中選擇 塊元素 您幾乎可以從 子集 下拉列表,然後單擊 全塊 符號,然後單擊 插入 插入符號,請參見屏幕截圖:

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

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
備註:在以上公式中, E2 是單元格包含負方差值, A16 是包含您要插入的特定符號的單元格以及數字 100 是變量數據,用於根據方差值計算符號的數量,您可以將其更改為自己的值。

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

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
備註:在以上公式中, F2 是單元格包含正方差值, A16 是包含您要插入的特定符號的單元格以及數字 100 是變量數據,用於根據方差值計算符號的數量,您可以將其更改為自己的值。

7。 然後,選擇列G中的單元格,然後單擊 首頁 > 對齊 要在單元格上對齊數據,請參見屏幕截圖:

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


創建具有驚人功能的預算與實際差異圖表

Excel的Kutools 提供Excel所沒有的許多特殊類型的圖表,例如 項目符號圖, 目標和實際圖表, 差異箭頭圖 等等。 借助其便捷的工具, 差異比較圖,您可以在工作表中快速輕鬆地創建預算與實際差異圖表,或者在單元格中創建迷你預算與實際差異圖表。 點擊下載Kutools for Excel免費試用!


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


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


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項  |  刪除空白行  |  合併列或儲存格而不遺失數據  |  沒有公式的回合 ...
超VLookup: 多重標準  |  多重價值  |  跨多頁  |  模糊查詢...
副詞。 下拉清單: 簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  將列與 選擇相同和不同的單元格 ...
特色功能: 網格焦點  |  設計圖  |  大方程式酒吧  |  工作簿和工作表管理器 | 資源庫 (自動文字)  |  日期選擇器  |  合併工作表  |  加密/解密單元格  |  按清單發送電子郵件  |  超級濾鏡  |  特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符 ...)  |  50+ 圖表 類型 (甘特圖 ...)  |  40+ 實用 公式 (根據生日計算年齡 ...)  |  19 插入 工具 (插入二維碼, 從路徑插入圖片 ...)  |  12 轉化 工具 (數字到單詞, 貨幣兌換 ...)  |  7 合併與拆分 工具 (高級合併行, 拆分 Excel 儲存格 ...)  |  ... 和更多

Kutools for Excel 擁有超過 300 個功能, 確保只需點擊一下即可獲得您所需要的...

產品描述


Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的選項卡,就像 Chrome、Edge 和 Firefox 一樣。
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
bonjour,
Comment faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget ?
merci
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations