Excel損益表百分比公式：差異比率函數及負數紅色 - 贊贊小屋

Excel計算差異金額及比例時因為有正有負，兩期差異百分比必須特別注意。

本文以損益表為例，介紹如何利用IF、AND、OR邏輯函數，以及自訂數值格式代碼。

Excel損益表百分比公式：差異比率函數及負數紅色 2015-04-20 邏輯函數 2,308次瀏覽 目錄 一、收入項目損益表二、差異金額及比率三、IF函數排除錯誤四、AND邏輯函數五、靈活修改公式六、OR函數組合公式七、數值格式代碼八、負數紅字括號九、自訂百分比格式 Excel計算差異金額及比例時因為有正有負，兩期差異百分比必須特別注意。

本文以損益表為例，介紹如何利用IF、AND、OR邏輯函數，以及自訂數值格式代碼。

分析兩期差異，不但是查核各科目必要的分析性複核程序，也是公司會計編製財務報表時，必須執行的複核機制，通常更是向上層作財務報告時，必須附上的分析說明。

因為將當期的結算數據，和上期金額一作比較說明，冷冰冰的財務數字，馬上有了管理上的實質意義。

兩期差異雖然都只是簡單的加減乘除，但如果要對格式呈現上更加專業嚴謹，也是需要一些Excel小技巧，以下分享： 一、收入項目損益表 此次範例的損益表，為了方便公式說明，只截取收入部份，並且設計有ABCDE五個客戶，兩個月的金額有正有負，所以這一節分享的，不僅適用於損益表，同時也適用收入明細表、或者各種有正有負的財務管理報表。

二、差異金額及比率 幾乎已成職業習慣，會計人拿到這類報表，總是要加上「差異金額」（=B5-C5）和「％」（=D5/C5），將滑鼠移到儲存格右下角，鼠標由白十字變成黑十字，往下拉便可以複製公式。

仔細一看，出現了一個「#DIV/0！」，這是除以零所產生的錯誤訊息。

三、IF函數排除錯誤 解決方案是針對分母為零的情況，加個條件公式：「=IF（C7=0,”NA”,D7/C7）」。

不過再仔細看，如果本月為正數，上月為負數，差異金額理所當然是正數，但差異比率因為是正除以負，變成負數，如同圖片標黃色部份。

這個如果是會計人，大家都可以理解是套了公式，然而筆者遇過在簡報會議上，老闆提出疑問：本月金額增加，差異比率不是應該為正嗎？雖然說，當場可以解釋幾句，但是這個解釋幾句，在會議就有點不必要，如果能考慮到這個可能造成錯覺的表達，予以修改，也許會更好。

四、AND邏輯函數 那就再來一個特殊狀況處理：「＝IF（AND（B9＞＝0,C9＜0）,－D9／C9,D9／C9）」，在原來的條件式外面，再冠上一個若P則Q的IF函數，並且以AND函數作為判斷，如果本月為正、上月為負，原差異比率公式的結果要正負逆轉，否則（AND函數不成立）維持原公式。

不過，解決了這個問題，馬上又會發現，如果兩個月都是負數，照樣有正負差異不好理解的狀況，如圖標黃色部份。

五、靈活修改公式 照樣照句，輸入公式：「＝IF（AND（B10＜0,C10＜0）,－D10／C10,D10／C10）」。

命令Excel遇到兩個負負，計算結果正負逆轉。

聰明讀者很快會發現，剛才那個公式的條件之一是上月為負，現在這個公式的條件之一也是上月為負，那麼直接修改公式：「＝IF（C9＜0,－D9／C9,D9／C9）」，一切OK了。

六、OR函數組合公式 把上面三個特殊情況的條件，併在一個公式裡：「＝IF（D10＝0,”NA”,IF（OR（AND（C10＞＝0,D10＜0）,AND（C10＜0,D10＜0））,－E10／D10,E10／D10））」到這個階段，對於IF、AND、OR等邏輯函數的應用，應該已經能完整理解，可以舉一反三了。

像這樣多重IF判斷公式，看起來不容易直接理解，而且不一定所有狀況都能夠應付。

其實Excel還有更高階的陣列和VBA，不過依照我多年實務經驗，幾個IF套起來已經夠用了。

況且，通常會計每個月都是例行性報表，所以儘管公式相當長，只要第一次把它架好，下個月複製貼上，下下個月一樣再複製貼上，一直複製貼上就好了，還是挺方便的。

七、數值格式代碼 講完公式，再講講格式。

報表跑出來的百分比，負數的話，是前面加個減字負號（-200%），並非會計人一般習慣的括號負數（（200%））。

這是Excel預設的百分比格式，想要有所變化，只能量身訂作，也就是先前章節提到數值格代碼。

按下快速键「Ctrl＋1」，出現的「儲存格格式」視窗顯示目前的格式為「0％」。

八、負數紅字括號 直接於視窗欄位修改：「0％;[紅色]（0％）」完整地說，數值定義格式有四個區塊：「A;B;C;D」，A為正數格式、B為負數格式、C為零格式、D為文字格式，省略代表不作特別規定，系統會依照預設值顯示。

「0％;[紅色]（0％）」代表正數時顯示正常百分比符號，負數時顯示紅色字體並加括號。

九、自訂百分比格式 修改完格式，按「確定」，百分比格式果然已經改變。

加入Line社群，口袋裡的Excel小教室！ 延伸閱讀： Excel報表資料正規化：IF函數公式填滿空白儲存格 搜尋 最新文章 Excel函數教學：最大值最小值以及多條件陣列公式 2022-05-24 營業稅離線建檔系統線上申報：轉出及勾稽申核 2022-05-18 VBA大量爬蟲程式：陣列、Application.Wait、Hyperlinks實務應用 2022-05-14 ExcelVBA網路爬蟲強化：ByVal、OnErrorGoTo、DoLoopUntil 2022-05-05 VBAhtml網頁原始碼：精準ie爬蟲，陣列文字處理 2022-04-24 文章分類 Excel 程式 SEO 電影 人生 小說 職場 藝術 VBA Office 投資 音樂 分享 法律 美食 旅遊 所有文章分類 贊贊老師 與我聯絡 YouTube 部落格 贊贊書屋 所有課程 選單 贊贊老師 與我聯絡 YouTube 部落格 贊贊書屋 所有課程 客服信箱：[email protected] 客服LineID：b88104069 關閉 插入/編輯連結 關閉 請輸入目標網址 網址 連結文字 在新分頁中開啟連結 或連結到現有的內容 搜尋 尚未指定搜尋詞彙。

以下顯示最近發佈的項目。

搜尋或使用向上/向下鍵以選取項目。

取消