在 Excel 中計算兩個數字之間的百分比變化
無論是商業分析、學術研究或個人財務管理,掌握百分比變化計算對於有效的數據分析至關重要。本文將從 Excel 中計算百分比增加和減少的基本公式開始,然後深入研究更高級的操作,包括處理負數、處理零值以及根據給定的百分比變化反向計算原始值或新值。
計算百分比變化的基本公式
有兩個基本公式可以計算兩個數字之間的百分比變化:
公式1:
=(new_value - old_value) / old_value
公式2:
=new_value / old_value – 1
這些公式可以幫助您確定某個數字以百分比形式增加或減少了多少。下面您將找到兩個範例,說明如何使用這些公式來計算兩個數字之間的百分比變化。
計算成長百分比
如果新值大於舊值,則結果是百分比增加。如下面的螢幕截圖所示,假設您有一個網站,註冊用戶每年都在成長。要計算每年使用者成長的百分比,您可以執行以下操作。
第一步:應用公式得到結果
選擇一個空白儲存格(本例為 D2),輸入下列公式之一,然後按 Enter 得到結果的關鍵。選擇該儲存格並將其拖曳 填充手柄 下來得到其餘的結果。結果顯示為一般數字,如下圖所示:
=(C2-B2)/B2
=C2/B2-1
步驟 2:將結果格式化為百分比
若要將數字格式設為百分比,請保持結果儲存格處於選取狀態,然後選擇 百分比樣式 按鈕在 聯繫電話 下的組 首頁 標籤。
結果
現在已經計算了每年用戶成長的百分比。請參閱下面的螢幕截圖:
計算減少百分比
如果新值小於舊值,則表示減少。下面的螢幕截圖顯示產品價格下降。若要計算這些價格的下降百分比,請依照下列步驟操作。
第一步:應用公式得到結果
選擇一個空白儲存格(本例為 D2),輸入下列公式之一,然後按 Enter 得到結果的關鍵。選擇該儲存格並將其拖曳 填充手柄 下來得到其餘的結果。結果顯示為一般數字,如下圖所示:
=(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,輸入下列公式,然後按 Enter 獲得結果的關鍵。
=B2/(1+A2)
根據百分比變化計算新值
相反的情況是您知道百分比變化和原始值,並且需要確定新值。例如,如果今年的銷售額為 10,000 美元,您的目標是明年的銷售額比今年高出 20%,您將如何計算明年的銷售額?
基本公式:
=Old value * (1 + Percentage Change)
選擇一個空白儲存格,例如C2,輸入下列公式,然後按 Enter 獲得結果的關鍵。
=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選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!