Note: The other languages of the website are Google-translated. Back to English
English English

在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 for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

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

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (1)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
卓悅,
評論 faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget 嗎?
謝謝
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點