Excel四捨五入加總誤差:簡單公式不再少1差1

Excel四捨五入加總誤差是實務上很常見的狀況,尤其是在報表取整數或者千元表達時很容易發生,本文介紹如何設計SUM ROUND併用的公式,輕鬆將計算誤差修正。

職場會遇到的狀況

通常稍具規模企業,隨便一個數字幾十萬上下,所以會計師的查核報表,皆是以仟元表達,因為基於重大性原則,幾十幾百的數字尾巴都被砍掉了。同樣道理,企業內部財務報表或是會議簡報,為了便於閱讀理解,大部份也會採用仟元表達。於是乎,如何快速將Excel報表「成仟上萬」,值得好好琢磨琢磨,在此分享相關小技巧:

解決方法Excel教學

Step 1 原始財務報表

系統報表跑出來是以元為單位,看起來很傷眼力,不容易解讀,其實那些仟元以下的數字,根本沒有攸關性,不影響管理決策(不痛不癢),可以無視摃掉。

原始財務報表

Step 2 簡單計算取仟元

說到取仟元,簡單直接的就是帶公式:「=C5/1000」,除以一千拉下來,仟元以下尾數一刀砍,看起來清爽許多。

簡單計算取仟元

Step 3 快速選擇性貼上

除了簡單公式,當然還有更華麗炫技的,隨便找個空白儲存格輸入「1000」,把這儲存格按「Ctrl+C」快速組合鍵複製,然後選取C6到C10的範圍,按住Ctrl鍵不放,再選取E6到E10範圍,如此便同時選取不同區域範圍的儲存格,接著滑鼠右鍵,選單中點擊「選擇性貼上」。

快速選擇性貼上

Step 4 除法運算貼上

在跳出來的視窗勾選「除」功能,最後按「確定」。

除法運算貼上

Step 5 報表仟元表達

剎那間,所有被選取數字皆已成仟,這一招還蠻炫的吧!

報表仟元表達

Step 6 Excel四捨五入加總誤差

上面作法有個小問題,全部數字取仟位,遇到有加總情形,很容易出槌,合計數有可能不等於各個數字相加,會有加一減一的尾差。以範例來說,「43,561+117,967+65,342+58,983=285,853」,和儲存格C6「285,854」差了1元。這是因為縱使取了仟元,但Excel非常貼心,各個儲存格仍然保留原來數值,只是顯示時自動四捨五入進仟位了。以圖示為例,表面上怎麼看都是「285,854」,實際檢視儲存格內容,仟以下尾數仍在(285854.381585)。所以ABCD四項收入加總,每項收入可能剛好都被四捨砍掉,可是依照原值加起來,總數卻大有可能該五入進位了。

Excel四捨五入加總誤差

Step 7 ROUND函數公式

有個解決四捨五入的函數:「ROUND」。首先依照原來方法除以1000,關鍵是外面再包一層函數:「=ROUND(C5/1000,0)」,意思是將數值「C5/1000」四捨五入,進位到整數,函數中逗號後面的零,表示取到零個小數點,也就是取整數。

ROUND函數公式

Step 8 SUM函數合計

同樣公式拉下來,四項收入加總數仍然有尾差,重點在必須將加總儲存格的內容改為函數:「=SUM(D6:D9)」,如此一來,保證不會有尾差,可以放心交出報表囉!

想要進階使用數字格式代碼千元表達,或者以萬元為單位?可以參考贊贊小屋另一篇文章:

Excel千元表達:6種必學方法,萬元為單元也可以

SUM函數合計

結語:報告品質與專業形象

本文所介紹的報表尾差,不知情者光看簡報,會覺得奇怪,勢必得解釋一番,雖然對內對外都是補充說明的,但再怎麼解釋,印象分數已經差一截了。因此這個SUM加總程序可以考慮列入報表SOP,每張報表即使公式設置再怎麼完美,有加總的地方還是要一一檢查避免出錯。魔鬼出在細節裡,職場工作者的帳務品質和專業形象,同樣出在細節裡。

YouTube video

參考資源

  1. 微軟ROUND 函數說明。
  2. 贊贊小屋Excel教學手冊。

最新文章: