跳到主要內容

在 Excel 中計算兩個數字之間的百分比變化

無論是商業分析、學術研究或個人財務管理,掌握百分比變化計算對於有效的數據分析至關重要。本文將從 Excel 中計算百分比增加和減少的基本公式開始,然後深入研究更高級的操作,包括處理負數、處理零值以及根據給定的百分比變化反向計算原始值或新值。


計算百分比變化的基本公式

有兩個基本公式可以計算兩個數字之間的百分比變化:

公式1:

=(new_value - old_value) / old_value

公式2:

=new_value / old_value – 1

這些公式可以幫助您確定某個數字以百分比形式增加或減少了多少。下面您將找到兩個範例,說明如何使用這些公式來計算兩個數字之間的百分比變化。


計算兩個數字之間的百分比變化

本節將逐步向您展示如何計算兩個數字之間的增加和減少百分比。


計算成長百分比

如果新值大於舊值,則結果是百分比增加。如下面的螢幕截圖所示,假設您有一個網站,註冊用戶每年都在成長。要計算每年使用者成長的百分比,您可以執行以下操作。

第一步:應用公式得到結果

選擇一個空白儲存格(本例為 D2),輸入下列公式之一,然後按 Enter 得到結果的關鍵。選擇該儲存格並將其拖曳 填充手柄 下來得到其餘的結果。結果顯示為一般數字,如下圖所示:

=(C2-B2)/B2
=C2/B2-1

步驟 2:將結果格式化為百分比

若要將數字格式設為百分比,請保持結果儲存格處於選取狀態,然後選擇 百分比樣式 按鈕在 聯繫電話 下的組 首頁 標籤。

結果

現在已經計算了每年用戶成長的百分比。請參閱下面的螢幕截圖:

備註: 在公式, C2 是包含新值的儲存格,且 B2 是包含舊值的儲存格。

計算減少百分比

如果新值小於舊值,則表示減少。下面的螢幕截圖顯示產品價格下降。若要計算這些價格的下降百分比,請依照下列步驟操作。

第一步:應用公式得到結果

選擇一個空白儲存格(本例為 D2),輸入下列公式之一,然後按 Enter 得到結果的關鍵。選擇該儲存格並將其拖曳 填充手柄 下來得到其餘的結果。結果顯示為一般數字,如下圖所示:

=(C2-B2)/B2
=C2/B2-1

步驟 2:將結果格式化為百分比

若要將數字格式設為百分比,請保持結果儲存格處於選取狀態,然後選擇 百分比樣式 按鈕在 聯繫電話 下的組 首頁 標籤。

結果

這些價格的下降百分比現已計算出來。請參閱下面的螢幕截圖:

備註: 在公式, C2 是包含新價格的儲存格, B2 是包含舊價格的儲存格。

進階作業

本節示範了在計算兩個數字之間的百分比變化時可能遇到的不同情況。


使用負數計算百分比變化

在計算百分比變化時,您可能會遇到負數,如下所示:

讓我們用具體的例子來說明每個場景:

兩個數都是負數

即使兩個值均為負值,百分比變化的標準公式仍然有效。
例如,假設一家公司的虧損從上季的 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,輸入下列公式,然後按 Enter 獲得結果的關鍵。

=B2/(1+A2)

備註:在此公式中, B2 是包含新值的儲存格,並且該儲存格 A2 包含百分比變化。

根據百分比變化計算新值

相反的情況是您知道百分比變化和原始值,並且需要確定新值。例如,如果今年的銷售額為 10,000 美元,您的目標是明年的銷售額比今年高出 20%,您將如何計算明年的銷售額?

基本公式:

=Old value * (1 + Percentage Change)

選擇一個空白儲存格,例如C2,輸入下列公式,然後按 Enter 獲得結果的關鍵。

=B2*(1+A2)

備註:在此公式中, B2 是包含舊值的儲存格,並且該儲存格 A2 包含百分比變化。

總而言之,掌握Excel中兩個數字之間的百分比變化的計算是一項多才多藝的技能,可以顯著增強您的數據分析能力。無論是處理簡單的增加和減少,還是涉及負數或零值的更複雜的場景,本教程都提供了解決這些任務的各種公式。對於那些渴望深入研究 Excel 功能的人,我們的網站擁有豐富的教學。 在這裡了解更多 Excel 提示和技巧.

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (26)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
=((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
doesnt work
This comment was minimized by the moderator on the site
it is possible to calculate three values with the percentage change?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi Edi Silva,
You can try this formula: =(first value-second value)/(third value-second value)
This comment was minimized by the moderator on the site
I have 0 in A1, 5 in B1, increase in percentage should be 500%. However with the given formula (B1-A1)/A1 = (0-500)/0 gives me a #DIV/0! error.In case if i apply =IF(A2<>0,(A3-A2)/A2,"0"), it return s me "0". neither of them helps. Any solution to this???
This comment was minimized by the moderator on the site
In case anyone still needs an answer for this problem, use this =IF(A1<>0, (B1-A1)/A1, (B1-A1))
using the percent style format will make (B1-A1) in this case (5-0) = 5 become 500%.
This comment was minimized by the moderator on the site
Hey Stanley,

Thanks for the formula!
Would you mind explaining the formula, please?
This comment was minimized by the moderator on the site
SMH SMH SMH SMH SMH
This comment was minimized by the moderator on the site
I hope you're not that dumb, dividing by 0
This comment was minimized by the moderator on the site
Any help for picking up value only it falls between 85% to 99% else blank.
Such as A is 10 and in B if we put any number , and in C it show the value only if it is fals between 85% to 99% of A
This comment was minimized by the moderator on the site
When a cell is blank, and another cell Has a number, how do you
This comment was minimized by the moderator on the site
Hello, Need help on how to write an formula with this situation.
So i have a report card design to show the grade of each performance of the work output. I set a standard for a specific task in amount of 350. However, based on available volume, not all the month output can be reach to 350. Therefore, we calculated that for this month, the person can achieve their 100% goal by just reaching 174. However, that person can go beyond that number to reach 350. What i want to do here is that our grade level is showing below. If they achieve 174, they would get a grade level B which is between 95% to 105%. But in order to get to maximum 120% of grade A+, the person will need to reach 350. This is where i stuck with. So i know that if a person reaches 180, he/she would get somewhere 102 or 103%. But i dont know how to calculate this and be able to set a maximum 120% between the number of 174 and 350

So right now if the number double from 174 to 348, the % is 200%.... but i need to set this maximum 120% only. How can i do this?

SCORE GRADE

0.00% -

40.00% E-

45.00% E

50.00% E+

55.00% D-

65.00% D

70.00% D+

75.00% C-

80.00% C

85.00% C+

90.00% B-

95.00% B

105.00% B+

110.00% A-

115.00% A

120.00% A+
This comment was minimized by the moderator on the site
Percentage 'change' and percentage 'difference' are two different things. This formula represents percentage change, for example if you are are comparing values of the same statistic over time (e.g. product sales this year compared to product sales last year). Percentage difference is different. Difference can be used when comparing two different statistics to each other (e.g. Mary's annual sales compared to Martha's annual sales). Mary didn't have an increase or decrease on Martha's sales over the same time period, they were just different. To calculate percentage difference you take the absolute value of the difference between two numbers. Then divide it by the average of those two numbers and then multiple by 100 to get the percentage. For example, if the two values are 40 and 60. The difference is 20, ignoring whether it's positive or negative. The average is 50. 20/50 = .4 X 100 = 40%. The percentage increase between these two numbers, if it is an increase, would be 50%. The percentage decrease would be 33%. Close, but all very different.
This comment was minimized by the moderator on the site
1.The percentage increase between these two numbers, if it is an increase, would be 50%. The percentage decrease would be 33%. Close, but all very different.....  How did you get the 50 and the 33?2. If you say Marias YOY is 8% and Martha's YOY is -25%, how do you calculate the difference between those two YOYs?
This comment was minimized by the moderator on the site
The formula that works in all cases is "=(new_value - old_value) / abs(old_value)"
This comment was minimized by the moderator on the site
How can I chnage regular percent change into Year over Year PCH?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations