超級促銷週Claude Skills — 20% 折扣
Tips

如何在 Excel 中進行差異分析:逐步教學

Powerdrill Bloom·
如何在 Excel 中進行差異分析:逐步教學

若要在 Excel 中進行差異分析,請將每個項目的預算和實際數據並排。用實際值減去預算值以得到金額差異。將該差異除以預算以得到百分比。接著將每一行標記為有利或不利,並解釋最大的差距。

本指南將逐步介紹公式、工作表佈局以及一個完整的實作範例。內容還涵蓋了如何繪製結果圖表,以及如何將營收差距拆解為價格和數量。Excel 步驟參考自 2026 年 11 月 10 日讀取的 Microsoft 支援頁面。

差異分析顯示了什麼

差異是指計畫與實際發生情況之間的差距。差異分析會逐行條列這些差距,並解釋其中重要的部分。

大多數團隊會將實際值與以下三種基準之一進行比較:

  • 預算 (Budget)。 年初設定的計畫。
  • 預測 (Forecast)。 年中設定的更新計畫。
  • 前期 (Prior period)。 上個月、上季度或去年同期。

Microsoft 針對此任務提供的 Copilot 技能對這項工作有很好的描述。其差異分析技能可以「將實際結果與預算、預測或前期進行比較,並透過價格、數量、組合、費率或時間點來解釋差異」。

輸出通常是一個表格加上一些文字說明。表格顯示每個差距。說明則解釋領導者需要採取行動的三到四個關鍵點。

核心公式

三個公式即可完成大部分工作。假設預算在 B 欄,實際值在 C 欄。

指標 公式 它告訴你什麼
金額差異 =C2-B2 實際值與預算的差距(以貨幣計)
百分比差異 =IFERROR((C2-B2)/ABS(B2),"") 差距相對於計畫的大小
狀態 =IF(D2=0,"符合計畫",IF(D2>0,"高於計畫","低於計畫")) 該行項目的變動方向

ABS 函數在預算為負數時能保持正負號正確。IFERROR 則在預算為零時讓儲存格保持空白,而不是顯示錯誤。

方向並不等同於好或壞。營收高於計畫是好消息。成本高於計畫則是壞消息。接下來的章節將說明如何處理這種情況。

如何設定工作表

整潔的佈局讓公式易於複製。每個項目使用一列,並包含以下欄位:

  1. 項目。 營收、銷貨成本、薪資、行銷等等。
  2. 類型。 輸入「營收」或「成本」,以便 Excel 知道哪個方向是有利的。
  3. 預算。 該期間的計畫金額。
  4. 實際。 來自會計匯出的金額。
  5. 差異。 實際值減去預算值。
  6. 差異 %。 差異除以預算。
  7. 有利或不利。 根據類型和方向建立的標籤。
  8. 解釋。 針對重大項目的簡短說明。

確保預算和實際值屬於同一期間且會計科目對應一致。差異分析的品質取決於這種匹配程度。如果預算中薪資被拆分為兩個科目,實際值也應以同樣方式拆分。

使用 Ctrl+T 將表格格式化為 Excel 表格。這樣新列就會自動套用公式。

如何在 Excel 中計算差異

佈局就緒後,步驟就很簡單。假設上述欄位從第 2 列開始,類型在 B,預算在 C,實際值在 D。

  1. 在 E2 輸入 =D2-C2 以計算金額差異。
  2. 在 F2 輸入 =IFERROR((D2-C2)/ABS(C2),"") 並將其格式化為百分比。
  3. 在狀態欄中,輸入下方顯示的有利測試公式。
  4. 將公式向下複製到每一列。
  5. 新增一個總計列,並從總計值計算其差異,而不是將百分比相加。

以下是狀態欄第 2 列的有利測試公式:

=IF(E2=0,"符合計畫",IF(B2="營收",IF(E2>0,"有利","不利"),IF(E2<0,"有利","不利")))

它會先讀取類型欄。對於營收,正差異是有利的。對於成本,負差異是有利的,因為你的支出低於計畫。

在總計列中,百分比需要特別注意。將各行百分比相加會得到錯誤答案。應改為將總差異除以總預算。

如何標記重要的差異

並非每個差距都需要說明。大多數團隊會設定重大性規則,例如「超過 5% 且超過 $2,000」。通過這兩項測試的行項目才需要解釋。

一個簡單的公式即可標記它們。新增一個「重大」欄位,並在第 2 列輸入 =AND(ABS(F2)>0.05,ABS(E2)>2000)。該儲存格對重大項目會顯示 TRUE。

顏色可以讓表格更易於快速瀏覽。Excel 的設定格式化條件可以新增圖示集。Microsoft 表示圖示集可以「標註數據並將其分類為三到五個類別,並由閾值分隔」。

若要新增圖示集,請選取差異 % 欄位。然後前往「常用」>「設定格式化條件」>「圖示集」。Microsoft 指出,在「3 個箭頭」集合中,「綠色上箭頭代表較高的值」。對於成本行,請反轉圖示順序,以便上升顯示為警告。

如何解釋驅動因素

數字顯示差距在哪裡。驅動因素則解釋原因。Microsoft 的技能描述列出了五種常見驅動因素:價格、數量、組合、費率和時間點。

價格。 你的銷售或購買價格與計畫不同。價格差異 = (實際價格 − 預算價格) × 實際數量。

數量。 你的銷售或使用數量不同。數量差異 = (實際數量 − 預算數量) × 預算價格。

組合。 產品、地區或客戶的比例發生了變化。即使總量不變,轉向低利潤產品也會降低利潤。

費率。 成本費率發生變化,例如時薪或供應商費用。

時間點。 資金在不同期間移動。3 月份的發票在 4 月份支付,會在兩個月都顯示為差距。

價格和數量共同解釋了完整的營收差距。每次都值得進行這項檢查,因為它可以驗證計算是否正確。

差異分析範例

這是一個虛構公司 Cedar and Pine Coffee 的實作範例。其財務主管將第三季度的實際值與預算進行比較。

項目 類型 預算 實際 差異 差異 % 狀態
營收 營收 $250,000 $236,500 −$13,500 −5.4% 不利
銷貨成本 成本 $100,000 $97,200 −$2,800 −2.8% 有利
薪資 成本 $60,000 $64,800 $4,800 8.0% 不利
行銷 成本 $20,000 $26,000 $6,000 30.0% 不利
租金 成本 $15,000 $15,000 $0 0.0% 符合計畫
營業利潤 $55,000 $33,500 −$21,500 −39.1% 不利

總計核對無誤。營收減少了 $13,500,銷貨成本節省了 $2,800,薪資多花了 $4,800,行銷多花了 $6,000。總計為 −$21,500,與利潤差距相符。

有三行通過了「超過 5% 且超過 $2,000」的重大性規則:營收、薪資和行銷。銷貨成本僅變動了 2.8%,因此不予說明。

營收差距需要進一步觀察。預算假設以 $5.00 售出 50,000 單位。該季度以 $5.50 售出 43,000 單位。

  • 數量差異。 (43,000 − 50,000) × $5.00 = −$35,000。
  • 價格差異。 ($5.50 − $5.00) × 43,000 = +$21,500。
  • 總計。 −$35,000 + $21,500 = −$13,500,與營收行相符。

因此,價格上漲雖然維持住了,但卻犧牲了數量。給領導者的說明正是如此。行銷支出比計畫多出 30%,但銷量卻下降了。這引發了關於哪些活動有效率的疑問。

薪資也需要檢查。$4,800 的差距可能是費率驅動因素(如加薪),也可能是數量驅動因素(如加班)。財務主管詢問營運經理後得知,有兩家店增加了週末班次。這使其成為與營業時間相關的數量差距,而非加薪。

給領導者的最終摘要可以用一句話概括:營業利潤比預算低了 $21,500,主因是價格上漲導致的銷量損失超過了其收益,且行銷和週末班次支出超出了計畫。

如何撰寫差異說明

表格只是差異分析的一半。領導者會閱讀說明,因此每條說明都應回答三個問題:什麼變動了、為什麼變動,以及接下來該怎麼辦。

好的說明遵循簡單的模式:

  • 什麼。 「行銷支出超出預算 $6,000,高於計畫 30.0%。」
  • 為什麼。 「兩項付費活動的執行時間長於計畫。」
  • 下一步。 「我們已於 10 月 3 日暫停其中一項活動,預計第四季將符合計畫。」

每條說明保持在兩到三句話。使用與表格相同的項目順序,以便讀者將說明與數字對應。

區分一次性差距與趨勢。一次性的維修費用與每個月都會重複發生的加薪不同。清楚標記重複發生的差距,因為它們會改變預測。

與每個項目的負責人共同撰寫說明。財務團隊看到的是數字,但預算負責人知道原因。與每位負責人進行簡短確認,可以將猜測轉化為解釋。

最後,在頂部添加一行摘要。以上述範例而言,內容應為:「第三季營業利潤比預算低 $21,500,主要源於銷量下降和行銷支出增加。」

時間點與措辭同樣重要。大多數團隊在結帳後每個月都會進行差異分析。每月的節奏可以在還有時間採取行動時發現問題。

季度審查則更深入。它們會增加價格和數量的拆解、重新預測,以及審查預算本身是否仍然合理。年終審查則根據原始計畫審視全年表現。

每次都使用相同的範本。當欄位和規則保持固定時,每月的差異分析所花的時間會更少,且更容易跨期間觀察趨勢。

如何繪製差異圖表

圖表可以幫助領導者看清從預算到實際的路径。最適合的是瀑布圖。

Microsoft 表示,瀑布圖「顯示數值增加或減少時的累計總計」。它還補充說「瀑布圖也被稱為橋接圖」。

若要建立瀑布圖,請列出從預算利潤到實際利潤的步驟。從 $55,000 開始,然後加上每個項目對利潤的影響,最後以 $33,500 結束。選取數據並選擇「插入」>「插入瀑布圖」>「瀑布圖」。

將第一條和最後一條長條設定為總計。Microsoft 的頁面指出,起始和結束欄「通常從水平軸開始」,而中間的長條則是懸浮的。增加和減少會以顏色區分,使最大的驅動因素脫穎而出。

對於逐月追蹤,簡單的差異 % 直條圖也有效。我們關於如何建立預算 vs 實際報告的指南涵蓋了圍繞此表格的完整月報。

使用 AI 進行差異分析

一旦表格存在,公式只需幾分鐘即可完成。緩慢的部分通常是科目對應、尋找驅動因素和撰寫說明。AI 工具可以根據你的匯出檔案草擬這些步驟。

以下是使用 Powerdrill Bloom 的三個步驟方法。

步驟 1:上傳你的預算和實際值

從你的會計或規劃工具匯出預算和實際值。AI Budget Analysis Tool 接受「來自會計和 FP&A 的 Excel、CSV 或 PDF 匯出檔」。

如果你想要進行價格和數量拆解,請包含數量和價格數據。確保兩個檔案中的科目名稱相同。

在 Powerdrill Bloom 中上傳預算和實際值檔案以進行差異分析

步驟 2:以自然語言詢問差異和驅動因素

描述你需要的輸出。例如:「按項目比較第三季實際值與預算。標記超過 5% 且 $2,000 的行項目,並將營收差距拆解為價格和數量。」

工具頁面將「套用重大性閾值的逐行差異」列為一種視圖。它還列出了一個包含「原始預算、年初至今實際值和修訂後的年終預測」的重新預測活頁簿。

步驟 3:檢查數字並匯出報告

在分享任何內容之前,將總計與你自己的表格進行比較。然後匯出結果。預算分析頁面列出了一個「標記重大行項目並附帶驅動因素的差異儀表板」。

對於持續性的視圖,AI Finance Dashboard Builder 涵蓋了損益表、現金、營運資金和預測。試用 Powerdrill Bloom 處理上季度的預算和實際值。

在匯出報告前檢查差異總計

應避免的常見錯誤

在幾乎每一次初次差異分析中都會出現一些錯誤。

  • 混淆正負號。 統一決定差異是實際減預算,並在所有地方使用。
  • 將所有正差距稱為好。 即使數字是正的,較高的成本也是不利的。
  • 相加百分比。 應從總金額重新計算總百分比。
  • 比較不同期間。 將年初至今的實際值與月度預算進行比較會產生錯誤的差距。
  • 忽略時間點。 遲到的發票可能會在兩個月內產生兩個相反的差距。
  • 解釋所有事情。 對微小差距的說明會掩蓋重要的部分。
  • 手動輸入數字。 從匯出檔貼上或連結實際值,以免打錯字造成錯誤差距。
  • 跳過總計檢查。 各行差異相加應等於總差異。如果不相等,則表示漏掉了一列或重複對應。

常見問題

Excel 中差異的公式是什麼?

對於預算差異,使用實際減預算,例如 =C2-B2。對於百分比,使用 =IFERROR((C2-B2)/ABS(B2),"") 並將儲存格格式化為百分比。

我如何知道差異是否有利?

這取決於行項目類型。對於營收,實際高於預算是有利的。對於成本,實際低於預算是有利的。類型欄和 IF 公式可以標記每一列。

什麼是重大差異?

它是大到需要解釋的差距。團隊會設定自己的規則,例如超過 5% 且超過 $2,000。通過這兩項測試的行項目會獲得文字說明。

哪種圖表最適合差異分析?

瀑布圖(也稱為橋接圖)顯示了每一行如何將總計從預算移動到實際。Excel 在「插入」>「插入瀑布圖」下提供此功能。

Excel 中的 Copilot 可以進行差異分析嗎?

Microsoft 為 Excel 中的 Copilot 列出了一項差異分析技能。它可以將實際值與預算、預測或前期進行比較,並草擬說明供審查。這需要 Copilot 授權。

來源: Microsoft 支援,在 Excel 中搭配 Copilot 使用內建技能 · Microsoft 支援,建立瀑布圖 · Microsoft 支援,使用設定格式化條件來醒目提示 Excel 中的資訊。