Excel千元表達是上市櫃公司財務報表的固定格式,實務上還有可能報表需要顯示萬元為單位,本文介紹4種數值格代碼,2組函數公式,輕鬆完成這項任務。
目錄
Toggle一、千分位數值格式
Excel工作表上選擇資料範圍,快速組合鍵「Ctrl+1」叫出【設定儲存格格式】視窗,在預設的【數值】中選擇【數值】類別,小數位數設定為【0】,勾選【使用千分位(,)符號】,【負數表示方式】選擇用紅色字表達,從截圖可以看到顯示效果。
二、自訂格式代碼
同樣按「Ctrl+1」進入【設定儲存格格式】,類別選擇【自訂】,類型顯示【#,##0_);[紅色](#,##0)】,等於是可以查看剛剛所設定的格式,以數值代碼方式如何呈現。
接下來開始介紹Excel數值格式代碼:【#,##0_);[紅色](#,##0)】。其中【#】代表位數,【#,##0】中的「,」便是千分位,【;】分號作用為適用對象的區隔,以此為例,分號前面是正數的格式設定,分號後面是負數的格式設定,這裡正數格式代碼後面有一個「_)」,是因為正數有括號負數無括號,為了正負數對齊,因此使用空格符號「_」,下底線右邊符號表示空格寛度,所以「_)」意思是保留一個右括號寛度的空格,如此便能對齊正負數的位數。另外,負數的格式代碼前面多了「[紅色]」,表示以紅字表達負數。
三、零值顯示方式
在初步瞭解數值格式代碼後,可以做個小小測試。針對較為特殊的數值零值,Excel提供了一個簡單的選單,若自訂格式代碼的個數位為「#」,零將不會顯示出來,如果是自訂格式為「0」,將會顯示「0」,因此可以在【自訂】的類型那裡,先把原先的格式代碼清空,再輸入「#」或者「0」,如下截圖可以參考,讀者也可以自己測試看看會更加清楚。
四、Excel千元表達
為了瞭解如何將報表金額千元表達,如下截圖中的格式A及格式B是兩組相同的數字內容,其中格式B便是仟元表達,它其實很簡單在個位數後面新增一個「,」,也就是於類別選擇【自訂】,於正數的地方新增逗號,負數的地方也新增逗號,金額將會取仟元。注意到C2儲存格內容仍然是「1234500」的數值,表示實際數值並沒有仟元格式設定而改變。
五、顯示萬元為單位
進一步運用格式代碼,例如【0!.0,_);[紅色](0!.0,)】會以萬元表達,其中「!」是強制顯示代碼,和先前第二步驟的「_)」有點類似的作用,「!.」表示強制顯示「.」,而在後方的逗點代表數值要取千元,負數格式設定比照正數,因此設定為萬元可以用「!.」表示。注意到Excel格式代碼沒有萬位只有仟元,23.4其實是234仟元,只是中間加一個「.」,看起來好像是萬元,這裡在進行儲存格計算時也許會不小心造成錯誤或誤解。
這裡很顯然有一個問題,「123.5」減掉「(100.0)」卻是「23.4」,會有尾差,這是因為Excel各別針對每個儲存格在呈現時會自動四捨五入,雖然數值資料沒有變是「1234500」,但取萬元或仟元時整體看起來會有四捨五入尾差。
六、INT函數去尾差
設計Excel函數公式可以去除尾差,例如「=INT(B2/10000)」是利用INT函數先除以一萬再取整數,亦即無條件捨去小數,和前面步驟的格式代碼設置不同,經過公式計算後,數值已改變沒有小數了,此時再進行加總便能完全避免尾差的情形。
七、ROUND函數用法
除了以INT函數等於無條件捨去小數,還可以利用ROUND函數將數值四捨五入計算,例如公式:「=ROUND(B2,-4)/10000)」,作用是將數字四捨五入到小數點左邊的第四位數,1234500會變成1230000,再除10000就得到真正的萬元123,如此也可以避免尾差,只是這時候數值內容已經改變,和先前單純設定格式不太一樣,也要特別留意。
八、4種格式代碼
最後再複習一下本篇文章所學到的Excel數值格式代碼如下表所示。
格式代碼 | 說明 |
# | 數字代碼,只顯示有效值(零值不顯示) |
0 | 數字代碼,任何數字都會顯示(包括零) |
, | 仟位代碼,顯示仟位或仟元表達(可設置百萬表達) |
! | 強制顯示後面符號的代碼 |
Excel自訂數值格式代碼
由於格式代碼會造成實際內容和顯示出來的有些差異,可能會造成問題,而且無法執行後續的資料處理,因此除非很適合的場合,否則建議有需要的話可以像本篇文章一樣使用函數公式進行轉換。另外其實在Excel還有一些其他格式代碼可以使用,贊贊小屋Excel教學文章之後有適當範例會再繼續跟讀者分享,也許在某些特殊場合會派得上用場。
到Excel臉書社團下載範例檔案:會計人的Excel小教室。
本文章相關教學影片:
一次學會Excel所有必須技能的大全集課程: