Excel自訂數值格式代碼,報表數字顯示千元表達

Excel儲存格格式可以善用自訂數值格式代碼,更為靈活地控制如何顯示數值資料,本文以損益表數字千元表達為例說明,進而介紹以顯示值為準的選項設置。

一、簡單的損益表

延續上一節範例,簡單的一個損益表,主要針對收入部份作說明。Excel好處是公式相當容易延伸複製,所以如果收入設置好了,想套用到整個損益表,只是一瞬間的事而已。

二、儲存格格式代碼

滑鼠選取黃色範圍,組合快速鍵「Ctrl+1」進入「儲存格格式」視窗,在「數值」頁籤移到「自訂」,顯示的是目前數值格式代碼:「#,##0」;[紅色](#,##0)」。在此簡單說明,數值格式代碼以分號(;)區隔成四個段落,分別是正數、負數、零值、文本的格式代碼,因此這裡看到的代碼,分別定義了正數和負數。「#」是數字代碼,只顯示有效值,零不顯示,「0」也是數字代碼,任何數字包括零都會顯示,「,」是仟分位代碼,所以第一部份的「#,##0」,代表是取到整數位,打上仟分位符號,即使儲存格值是零,也要顯示為「0」,不能空白。「」表示正數在右邊留下一個字元空格,下劃線是空格的代碼,把這個和分號後面的「[紅色](#,##0)」對比,可以知道作用在於讓正數負數對齊,「[]」是顏色代碼,裡面可以填上顏色名稱,在這裡是會計人慣用的負數紅字。

三、仟分位格式代碼

在正數和負數代碼後面都加一個「,」,其後沒有數字代碼,表示取仟元表達,仟元以下隱藏,修改之後的代碼:「#,##0,_);[紅色](#,##0,)」,顯示出來的報表如我們所願。

四、沉默的小數尾差

桌子上計算機拿來核算:43,561+117,967+65,342-5,898=220,972,銷貨收入C5卻是顯示220,973。仔細將整數和三位小數分別顯示,發現是隱身於小數後面的尾數的緣故。

五、SUM函數加總

在合計儲存格輸入公式「=SUM(C6:C9)」,也是於事無補,看不見的尾數仍然存在,問題並沒有解決。對策之一是上一節提到的,先Rounding再Sum,如此需要執行好幾個步驟,接下來嘗試另外一種方法。

六、以顯示值為準

第一章有提到Excel工作表選項,在這裡要做特殊的設定:「檔案>選項>進階>計算此活頁簿時」裡面,有一個「以顯示值為準」,將方框打勾,會跳出示警視窗:「資料將永遠失去其精准度」,這個表示如果儲存格數值有小數,在公式計算時,會純粹以顯示數值作計算,原本的尾數不再保留,這就是失其精準度的意思。

七、再也沒有尾差

設定好回到工作表,終於出現「220,972」。仔細看C6儲存格內容,是「43561000」,表示沒有顯示的部份已經都歸零了,所以在D欄E欄,以D6和E6為例,公式都是「C6/1000」,以整數位和小數三位顯示的絶對數值都是一樣的,沒有看不見的尾數。

Excel選項設置的考量

如同跳出來的提醒訊息,「以顯示值為準」是基本設定,一旦打勾了,Excel所有資料的計算方法都會有所改變。以會計人而言,報表數據加總,當然希望以顯示值為基準計算,可是在某些情形,例如匯率換算,可能又希望計算前後保留尾數,這個在設定改變之後,必須特別留意。

YouTube video

最新文章: