在 Excel 中计算两个数字之间的百分比变化
无论是商业分析、学术研究还是个人财务管理,掌握百分比变化计算对于有效的数据分析至关重要。本文将从 Excel 中计算百分比增加和减少的基本公式开始,然后深入研究更高级的操作,包括处理负数、处理零值以及根据给定的百分比变化反向计算原始值或新值。
计算百分比变化的基本公式
有两个基本公式可以计算两个数字之间的百分比变化:
公式1:
=(new_value - old_value) / old_value
公式2:
=new_value / old_value – 1
这些公式可以帮助您确定某个数字以百分比形式增加或减少了多少。下面您将找到两个示例,说明如何使用这些公式来计算两个数字之间的百分比变化。
计算增长百分比
如果新值大于旧值,则结果是百分比增加。如下面的屏幕截图所示,假设您有一个网站,注册用户每年都在增长。要计算每年用户增长的百分比,您可以执行以下操作。
第一步:应用公式得到结果
选择一个空白单元格(本例中为 D2),输入以下公式之一,然后按 输入 得到结果的关键。选择该单元格并将其拖动 填充手柄 下来得到其余的结果。结果显示为一般数字,如下图所示:
=(C2-B2)/B2
=C2/B2-1
步骤 2:将结果格式化为百分比
要将数字格式设置为百分比,请保持结果单元格处于选中状态,然后选择 百分比样式 按钮在 联系电话 下的组 主页 标签。
结果
现在已经计算出了每年用户增长的百分比。请参阅下面的屏幕截图:
计算减少百分比
如果新值小于旧值,则表示减少。下面的屏幕截图显示产品价格下降。要计算这些价格的下降百分比,请按以下步骤操作。
第一步:应用公式得到结果
选择一个空白单元格(本例中为 D2),输入以下公式之一,然后按 输入 得到结果的关键。选择该单元格并将其拖动 填充手柄 下来得到其余的结果。结果显示为一般数字,如下图所示:
=(C2-B2)/B2
=C2/B2-1
步骤 2:将结果格式化为百分比
要将数字格式设置为百分比,请保持结果单元格处于选中状态,然后选择 百分比样式 按钮在 联系电话 下的组 主页 标签。
结果
这些价格的下降百分比现已计算出来。请参阅下面的屏幕截图:
使用负数计算百分比变化
在计算百分比变化时,您可能会遇到负数,如下所示:
让我们用具体的例子来说明每个场景:
两个数都是负数
即使两个值均为负值,百分比变化的标准公式仍然有效。
例如,假设一家公司的亏损从上季度的 10,000 美元(旧值为 -10,000)增加到本季度的 50,000 美元(新值为 -50,000)。如下图所示,使用标准公式(=(C2-B2)/B2)的收益率增加了 400%,表明损失从绝对值 10,000 美元增加到 50,000 美元,翻了两番。
其中一个数为负数
在这种情况下,您可能会遇到旧值为负数或新值为负数的情况。
当新值为负数时,标准百分比变化公式也能正常工作。
例如,股票的价值从正转为负。上个月它的估价为 10 美元(旧值是 10)。本月,由于市场低迷,其价值跌至-10美元(新值为-10)。为了计算这两个数字之间的百分比变化,我应用了标准公式(=(C2-B2)/B2) 得到-200% 的结果。这代表显着下降,表明从盈利转变为亏损。
例如,一家企业从亏损转为盈利。去年,这家小企业亏损了 10000 美元(旧值是 -10000),今年盈利了 20000 美元(新值是 20000)。如果应用标准百分比公式来计算百分比变化,结果将不正确。
这里的结果 -300 在实际场景中具有误导性。它暗示着负面的变化,暗示事情已经恶化,但实际上,情况已经从亏损转为盈利。
在这种情况下,您需要使用 ABS功能 使分母为正可确保百分比变化结果准确反映从负到正的转变。这是新公式:
=(C2-B2)/ABS(B2)
这里的结果 300% 表示实际从亏损转为盈利的正向变化。
- 请注意,ABS 方法可能会产生误导性结果。如下图所示,所有业务均由亏损转为盈利,其中F公司盈利最多。然而,与其他相比,它显示的百分比变化较小,这是没有意义的。因此,请谨慎使用此方法!
计算零变化百分比
虽然在 Excel 中计算百分比变化非常简单,但当计算涉及零时,您可能会遇到一些困难。
旧值为零
当旧值为零时,标准公式返回 #DIV / 0! 错误值。看截图:
在这种情况下,最可接受的解决方案是将百分比变化视为 100%。在基础数学中,从 0 到任何正数的变化都将被视为 100% 的增长。
以下是基本公式:
公式1:
=IFERROR((new value - old value) / old value, 1)
公式2:
=IFERROR((new value / old value - 1, 1)
将结果显示为 100% 而不是 #DIV/0!错误,您可以应用以下公式之一:
=IFERROR((C2 - B2) / B2, 1)
=IFERROR((C2 / B2) -1, 1)
新值为零
当新值为0时,结果为-1,即-100%。结果-100%代表完全减少。在这种情况下,结果是合理的,因为从正值更改为 0 将减少 100%。结果是可以接受的。
根据百分比变化计算旧值
有时,您可能会遇到这样的情况:您知道百分比变化和最新值,现在您需要确定原始值。例如,如果今年的销售额为 12,000 美元,比去年增长了 20%,那么您如何找到去年的销售额?在这种情况下,您可以使用以下公式:
基本公式:
=New value / (1 + Percentage Change)
选择一个空白单元格,例如C2,输入以下公式,然后按 输入 获得结果的关键。
=B2/(1+A2)
根据百分比变化计算新值
相反的情况是您知道百分比变化和原始值,并且需要确定新值。例如,如果今年的销售额为 10,000 美元,您的目标是明年的销售额比今年高 20%,您将如何计算明年的销售额?
基本公式:
=Old value * (1 + Percentage Change)
选择一个空白单元格,例如C2,输入以下公式,然后按 输入 获得结果的关键。
=B2*(1+A2)
总之,掌握Excel中两个数字之间的百分比变化的计算是一项多才多艺的技能,可以显着增强您的数据分析能力。无论是处理简单的增加和减少,还是涉及负数或零值的更复杂的场景,本教程都提供了解决这些任务的各种公式。对于那些渴望深入研究 Excel 功能的人,我们的网站拥有丰富的教程。 在这里了解更多 Excel 提示和技巧.
相关文章
创建带有百分比变化的柱形图
本教程演示如何在 Excel 中创建具有百分比变化的柱形图。
在堆积柱形图中显示百分比
本教程演示如何在 Excel 中以堆积柱形图显示百分比。
计算两个日期之间的百分比
这篇文章讨论如何根据今天的日期计算完成任务的百分比。
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!