Excel報表仟元萬元表達:數值格式代碼及去除尾差

Excel報表金額視情況可能需要仟元或萬元表達,本文介紹如何設置儲存格自訂數值格代碼完成這項任務,針對會造成尾差的情形,同時補充INT及ROUND函數用法。

一、千分位數值格式

Excel工作表上選擇資料範圍,快速組合鍵「Ctrl+1」叫出【設定儲存格格式】視窗,在預設的【數值】中選擇【數值】類別,小數位數設定為【0】,勾選【使用千分位(,)符號】,【負數表示方式】選擇用紅色字表達,從截圖可以看到顯示效果。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 1

二、自訂格式代碼

同樣按「Ctrl+1」進入【設定儲存格格式】,類別選擇【自訂】,類型顯示【#,##0_);[紅色](#,##0)】,等於是可以查看剛剛所設定的格式,以數值代碼方式如何呈現。

接下來開始介紹Excel數值格式代碼:【#,##0_);[紅色](#,##0)】。其中【#】代表位數,【#,##0】中的「,」便是千分位,【;】分號作用為適用對象的區隔,以此為例,分號前面是正數的格式設定,分號後面是負數的格式設定,這裡正數格式代碼後面有一個「_)」,是因為正數有括號負數無括號,為了正負數對齊,因此使用空格符號「_」,下底線右邊符號表示空格寛度,所以「_)」意思是保留一個右括號寛度的空格,如此便能對齊正負數的位數。另外,負數的格式代碼前面多了「[紅色]」,表示以紅字表達負數。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 3

三、零值顯示方式

在初步瞭解數值格式代碼後,可以做個小小測試。針對較為特殊的數值零值,Excel提供了一個簡單的選單,若自訂格式代碼的個數位為「#」,零將不會顯示出來,如果是自訂格式為「0」,將會顯示「0」,因此可以在【自訂】的類型那裡,先把原先的格式代碼清空,再輸入「#」或者「0」,如下截圖可以參考,讀者也可以自己測試看看會更加清楚。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 5

四、金額取仟元表達

為了瞭解如何將金額取仟元表達,如下截圖中的格式A及格式B是兩組相同的數字內容,其中格式B便是仟元表達,它其實很簡單在個位數後面新增一個「,」,也就是於類別選擇【自訂】,於正數的地方新增逗號,負數的地方也新增逗號,金額將會取仟元。注意到C2儲存格內容仍然是「1234500」的數值,表示實際數值並沒有仟元格式設定而改變。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 7

五、強制顯示萬元

進一步運用格式代碼,例如【0!.0,_);[紅色](0!.0,)】會以萬元表達,其中「!」是強制顯示代碼,和先前第二步驟的「_)」有點類似的作用,「!.」表示強制顯示「.」,而在後方的逗點代表數值要取千元,負數格式設定比照正數,因此設定為萬元可以用「!.」表示。注意到Excel格式代碼沒有萬位只有仟元,23.4其實是234仟元,只是中間加一個「.」,看起來好像是萬元,這裡在進行儲存格計算時也許會不小心造成錯誤或誤解。

這裡很顯然有一個問題,「123.5」減掉「(100.0)」卻是「23.4」,會有尾差,這是因為Excel各別針對每個儲存格在呈現時會自動四捨五入,雖然數值資料沒有變是「1234500」,但取萬元或仟元時整體看起來會有尾差。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 9

六、INT函數去尾差

設計函數可以去除尾差,例如公式:「=INT(B2/10000)」是利用INT函數先除以一萬再取整數,亦即無條件捨去小數,和前面步驟的格式代碼設置不同,經過公式計算後,數值已改變沒有小數了,此時再進行加總便能完全避免尾差的情形。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 11

七、ROUND函數用法

除了以INT函數等於無條件捨去小數,還可以利用ROUND函數將數值四捨五入計算,例如公式:「=ROUND(B2,-4)/10000)」,作用是將數字四捨五入到小數點左邊的第四位數,1234500會變成1230000,再除10000就得到真正的萬元123,如此也可以避免尾差,只是這時候數值內容已經改變,和先前單純設定格式不太一樣,也要特別留意。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 13

八、格式符號代碼

最後再複習一下本篇文章所學到的Excel數值格式代碼如下表所示。

Excel報表仟元萬元表達:數值格式代碼及去除尾差 15

Excel自訂數值格式代碼

由於格式代碼會造成實際內容和顯示出來的有些差異,可能會造成問題,而且無法執行後續的資料處理,因此除非很適合的場合,否則建議有需要的話可以像本篇文章一樣使用函數公式進行轉換。另外其實在Excel還有一些其他格式代碼可以使用,贊贊小屋之後有適當範例會再繼續跟讀者分享,也許在某些特殊場合會派得上用場。

加強學習:Excel基本操作文章