Excel如何調整財務報表的差異比率格式




兩期差異是財務比率中的基本款、也是最常用款,雖然是很簡單的加減乘除,但如果要對格式呈現上追毛求庛,也是需要一番功夫,以下分享:

一、乾乾淨淨的損益表,有兩期金額。

乾乾淨淨的損益表

二、職業習慣,會計人拿到這個資料,總是要加上「差異金額」(=C15-B15)和「%」(=D15/C15),不然報表送出去,實在是有失水準。仔細一看,出現了一個「#DIV/0!」表示除以零產生錯誤。

「#DIV/0!」表示除以零產生錯誤

三、解決方案很簡單,那就對分子為零的情況,特殊處理:「=IF(C15=0,”NA”,D15/C15)」。可是再仔細一看,如果本期為正數,前期為負數,差異金額理所當然是正數,差異比率正除以負,絶對是負數,如同圖片上標黃色的部份。這個如果是會計人,大家都可以理解是套套公式,然而筆者就遇過在管理月會上,大老闆提出疑問:本期金額增加了,比率不是應該是正的嗎?雖然,當場能解釋幾句,可是,這個解釋幾句,有可能給老闆印象就差評了,要知道,老闆都不是人當的,老闆的心情,千萬一定要照顧好,所以還是修補一下。

對分子為零的情況,特殊處理

四、於是,再來一個特殊狀況特殊處理:「=IF(AND(B12>=0,C12<0),-D12/C12,D12/C12)」,在若P則Q的IF中嵌進一個強勢AND,如果本月正上月負,原公式的結果把它正負逆轉,否則維持原公式。

在若P則Q的IF中嵌進一個強勢AND

不過,解決了之後這個,馬上又發現,如果兩個月都是負數,照樣會有正負差不好第一時間理解的可能性。

五、照樣照句:「=IF(AND(B16<0,C16<0),-D16/C16,D16/C16)」輸入程式碼,命令Excel遇到兩個負負就給我逆轉!聰明的讀者很快會發現,咦,剛才那個公式的特殊狀況條件之一是上月為負,現在這個公式也也有一個相同的特殊條件,那麼直接下公式:「IF(C16<0,-D16/C16,D16/C16」不就萬事OK了?

恭喜啦,能夠如此舉一反三,代表對於邏輯判斷的函數運用,達到不用老師教的地步了,在這裡我仍然落落長的公式,畢竟,分享嘛,保留完整的函數語言。

命令Excel遇到兩個負負就給我逆轉

六、把上面三個特殊情況的條件,用邏輯判斷函數併在一個公式裡:「=IF(C6=0,”NA”,IF(OR(AND(B6>=0,C6<0),AND(B6<0,C6<0)),-D6/C6,D6/C6))」大功告成。

雖然說,好像可以弄個VBA或陣列的精簡一下語言,但我不會,從來也沒想到要學,因為會計人其實也需要太高深的Excel,幾個邏輯判斷組合在一起,以我事務所加業界多年實務經驗,非常夠用了,況且,通常會計上每個月要整理的報表都一樣,所以儘管公式落落長,但只要第一次把它架好,下個月複製貼上,下下個月一再複製貼上,一直複製貼上就好了,挺方便的。

用邏輯判斷函數併在一個公式裡

七、講完公式,順便講講格式。注意到了嗎,上面那個表跑出來的百分比,負數的話,是前面加個減字負號(-151%),不是會計人一般習慣的括號負號((151%)),因為Excel預設的百分比格式就是如此,要改,只能量身訂作。按下Ctrl+1,也就是儲存格格式的快速鍵,出現的視窗顯示目前的格式為「0%」。

出現的視窗顯示目前的格式為「0%」

八、大膽地直接於格式視窗修改成:「0%;[紅色](0%)」完整而言,格式的定義有四個區塊:「A;B;C;D」,A為正數格式、B為負數格式、C為零格式、D為文字格式,省略代表不特別規定格式,依照系統預設值。所以剛才的「0%;[紅色](0%)」意思是正數時正常百分比符號,負數時紅色字體並加括號。

負數時紅色字體並加括號

九、修改完格式按確定之後,百分比果然長得不一樣,怎樣,不賴吧!

百分比果然長得不一樣




當前文章延伸閱讀:
經典操作