如何在 Excel 中进行差异分析:分步指南

要在 Excel 中进行差异分析,请将每个项目的预算数据和实际数据并排排列。用实际值减去预算值得到金额差异。将该差异除以预算值得到百分比。然后将每一行标记为有利或不利,并解释最大的差距。
本指南将介绍公式、工作表布局以及一个完整的操作实例。它还涵盖了如何绘制结果图表,以及如何将收入差距分解为价格和销量。Excel 操作步骤参考自 2026 年 11 月 10 日读取的 Microsoft 支持页面。
差异分析显示了什么
差异是计划与实际发生情况之间的差距。差异分析逐行罗列这些差距,并解释那些重要的差距。
大多数团队会将实际值与以下三个基准之一进行比较:
- 预算。 年初设定的计划。
- 预测。 年中设定的更新计划。
- 前期。 上个月、上季度或去年同期。
Microsoft 为此任务提供的 Copilot 技能很好地描述了这项工作。其差异分析技能“将实际结果与预算、预测或前期进行比较,并按价格、销量、组合、费率或时间解释差异”。
输出通常是一个表格加上一些书面说明。表格显示每一个差距。说明则解释领导者需要采取行动的三到四个关键点。
核心公式
三个公式可以完成大部分工作。假设预算在 B 列,实际值在 C 列。
| 指标 | 公式 | 它告诉你的信息 |
|---|---|---|
| 金额差异 | =C2-B2 | 实际值与预算值的货币差距 |
| 百分比差异 | =IFERROR((C2-B2)/ABS(B2),"") | 差距相对于计划的大小 |
| 状态 | =IF(D2=0,"符合计划",IF(D2>0,"高于计划","低于计划")) | 该行数据的变动方向 |
ABS 函数在预算为负数时保持符号正确。IFERROR 在预算为零时使单元格留空,而不是显示错误。
方向并不等同于好或坏。收入高于计划是好消息。成本高于计划则是坏消息。接下来的章节将展示如何处理这种情况。
如何设置工作表
整洁的布局使公式易于复制。每个项目使用一行,并包含以下列:
- 项目。 收入、销售成本、工资、市场营销等。
- 类型。 输入“收入”或“成本”,以便 Excel 知道哪个方向是有利的。
- 预算。 该期间的计划金额。
- 实际。 来自会计导出的金额。
- 差异。 实际减去预算。
- 差异 %。 差异除以预算。
- 有利或不利。 基于类型和方向的标签。
- 解释。 对重大项目的简短说明。
保持同一期间和同一会计科目的预算与实际值匹配。差异分析的准确性取决于这种匹配度。如果预算中工资被拆分为两个账户,实际值中也要以同样的方式拆分。
使用 Ctrl+T 将表格格式化为 Excel 表格。这样新行就会自动套用公式。
如何在 Excel 中计算差异
布局就绪后,步骤就很简短了。假设上述列从第 2 行开始,类型在 B 列,预算在 C 列,实际在 D 列。
- 在 E2 中,输入 =D2-C2 计算金额差异。
- 在 F2 中,输入 =IFERROR((D2-C2)/ABS(C2),"") 并将其格式化为百分比。
- 在“状态”列中,输入下面显示的有利性测试公式。
- 将公式向下复制到每一行。
- 添加总计行,并根据总计计算其差异,而不是通过累加百分比。
以下是状态列第 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 的页面指出,起始列和结束列“通常从水平轴开始”,而中间的柱线则是悬浮的。增加和减少通过颜色编码,因此最大的驱动因素会脱颖而出。
对于逐月跟踪,简单的差异 % 柱形图也有效。我们关于如何构建预算与实际报告的指南涵盖了围绕此表展开的完整月报。
使用 AI 进行差异分析
表格建立后,公式只需几分钟即可完成。慢的部分通常是映射账户、寻找驱动因素和撰写说明。AI 工具可以根据你的导出数据起草这些步骤。
以下是使用 Powerdrill Bloom 的三个步骤。
第 1 步:上传你的预算和实际值
从你的会计或计划工具中导出预算和实际值。AI Budget Analysis Tool 接受“来自会计和 FP&A 的 Excel、CSV 或 PDF 导出文件”。
如果你想要进行价格和销量分解,请包含销量和价格数据。保持两个文件中的账户名称一致。
第 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 中的信息。