Excel之差異比率格式調整 - 會計

Excel之差異比率格式調整

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

圖片參考：

http://www.b88104069.com/archives/1819

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

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

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

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


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

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


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

六、把上面三個特殊情況的條件，用邏輯判斷函數併在一個公式裡：「
=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％）」完整而言，格式
的定義有四個區塊：「A;B;C;D」，A為正數格式、B為負數格式、C為零格式
、D為文字格式，省略代表不特別規定格式，依照系統預設值。所以剛才的「
0％;[紅色]（0％）」意思是正數時正常百分比符號，負數時紅色字體並加括
號。

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

--

會計人的Excel小教室：

https://www.facebook.com/acctexcel


--