Excel損益表百分比公式:差異比率函數及負數紅色

Excel計算差異金額及比例時因為有正有負,兩期差異百分比必須特別注意。本文以損益表為例,介紹如何利用IF、AND、OR邏輯函數,以及自訂數值格式代碼。

分析兩期差異,不但是查核各科目必要的分析性複核程序,也是公司會計編製財務報表時,必須執行的複核機制,通常更是向上層作財務報告時,必須附上的分析說明。因為將當期的結算數據,和上期金額一作比較說明,冷冰冰的財務數字,馬上有了管理上的實質意義。兩期差異雖然都只是簡單的加減乘除,但如果要對格式呈現上更加專業嚴謹,也是需要一些Excel小技巧,以下分享:

一、收入項目損益表

此次範例的損益表,為了方便公式說明,只截取收入部份,並且設計有ABCDE五個客戶,兩個月的金額有正有負,所以這一節分享的,不僅適用於損益表,同時也適用收入明細表、或者各種有正有負的財務管理報表。

Excel損益表百分比公式:差異比率函數及負數紅色 1

二、差異金額及比率

幾乎已成職業習慣,會計人拿到這類報表,總是要加上「差異金額」(=B5-C5)和「%」(=D5/C5),將滑鼠移到儲存格右下角,鼠標由白十字變成黑十字,往下拉便可以複製公式。仔細一看,出現了一個「#DIV/0!」,這是除以零所產生的錯誤訊息。

Excel損益表百分比公式:差異比率函數及負數紅色 3

三、IF函數排除錯誤

解決方案是針對分母為零的情況,加個條件公式:「=IF(C7=0,”NA”,D7/C7)」。不過再仔細看,如果本月為正數,上月為負數,差異金額理所當然是正數,但差異比率因為是正除以負,變成負數,如同圖片標黃色部份。這個如果是會計人,大家都可以理解是套了公式,然而筆者遇過在簡報會議上,老闆提出疑問:本月金額增加,差異比率不是應該為正嗎?雖然說,當場可以解釋幾句,但是這個解釋幾句,在會議就有點不必要,如果能考慮到這個可能造成錯覺的表達,予以修改,也許會更好。

Excel損益表百分比公式:差異比率函數及負數紅色 5

四、AND邏輯函數

那就再來一個特殊狀況處理:「=IF(AND(B9>=0,C9<0),-D9/C9,D9/C9)」,在原來的條件式外面,再冠上一個若P則Q的IF函數,並且以AND函數作為判斷,如果本月為正、上月為負,原差異比率公式的結果要正負逆轉,否則(AND函數不成立)維持原公式。不過,解決了這個問題,馬上又會發現,如果兩個月都是負數,照樣有正負差異不好理解的狀況,如圖標黃色部份。

Excel損益表百分比公式:差異比率函數及負數紅色 7

五、靈活修改公式

照樣照句,輸入公式:「=IF(AND(B10<0,C10<0),-D10/C10,D10/C10)」。命令Excel遇到兩個負負,計算結果正負逆轉。聰明讀者很快會發現,剛才那個公式的條件之一是上月為負,現在這個公式的條件之一也是上月為負,那麼直接修改公式:「=IF(C9<0,-D9/C9,D9/C9)」,一切OK了。

Excel損益表百分比公式:差異比率函數及負數紅色 9

六、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套起來已經夠用了。況且,通常會計每個月都是例行性報表,所以儘管公式相當長,只要第一次把它架好,下個月複製貼上,下下個月一樣再複製貼上,一直複製貼上就好了,還是挺方便的。

Excel損益表百分比公式:差異比率函數及負數紅色 11

七、數值格式代碼

講完公式,再講講格式。報表跑出來的百分比,負數的話,是前面加個減字負號(-200%),並非會計人一般習慣的括號負數((200%))。這是Excel預設的百分比格式,想要有所變化,只能量身訂作,也就是先前章節提到數值格代碼。按下快速键「Ctrl+1」,出現的「儲存格格式」視窗顯示目前的格式為「0%」。

Excel損益表百分比公式:差異比率函數及負數紅色 13

八、負數紅字括號

直接於視窗欄位修改:「0%;[紅色](0%)」完整地說,數值定義格式有四個區塊:「A;B;C;D」,A為正數格式、B為負數格式、C為零格式、D為文字格式,省略代表不作特別規定,系統會依照預設值顯示。「0%;[紅色](0%)」代表正數時顯示正常百分比符號,負數時顯示紅色字體並加括號。

Excel損益表百分比公式:差異比率函數及負數紅色 15

九、自訂百分比格式

修改完格式,按「確定」,百分比格式果然已經改變。

Excel損益表百分比公式:差異比率函數及負數紅色 17