分門別類解決Excel求和出錯問題

平淡

原因1：文本格式所導致

在進行一些數字的求和時，由于數據格式為文本格式，這樣會導致SUM函數無法正確求和。如公司的ER P系統導出的數據都是文本格式（在單元格的左上角會有綠色三角形標記），這樣求和就會出錯（圖1）。

解決的方法是使用VALUE函數轉換為數字格式。如上述的例子，定位到D2單元格并輸入公式“=VA LUE（C2）”，下拉填充到D10單元格，這樣在D11單元格中輸入“=SUM（D2：D10）”求和就不會出錯了（圖2）。

原因2：數據包含特殊格式

在Excel中，數字格式中不能包含除數字之外的其他格式。如在網上復制的數據，在數據中間或者開頭、結尾包含空格，空格的存在會導致求和錯誤。此時可以選中數據中的任意空格并復制，接著按下“Ctrl+H”打開“查找和替換”對話框，然后在“查找內容”處輸入粘貼的空格，“替換為”設置為空，點擊“全部替換”，之后就可以正常求和了。

如果數據單元格中包含的是不可見的換行符，那么就需要先找出換行符號，然后再使用SUBSTITUTE函數替換，最后再求和即可。比如下面的數據就包含換行符，先定位到C2單元格，輸入公式“=CODE（RIGHT（B2））”并下拉填充，可以在C列中看到換行符號（圖3）。

公式解釋：

先使用RIGHT函數對B2單元格中的數據從右往左提取換行符號的代碼，這里代碼10表示B2單元格包含“CHAR（10）”換行符，然后再將其返回作為CODE函數文本字符串中第一個字符的數字代碼。

繼續定位到D2單元格，輸入公式“=--SUBSTITUTE（B2，CHAR（10），" " ）”并下拉，將換行符替換為空字符，這樣在D列中就會顯示真正的數字了。最后在D4單元格中輸入“=SUM（D2：D3）”即可正確求和（圖4）。

公式解釋：

這里將B2單元格作為將SUBSTITUTE函數的“原字符串”參數，將CHAR（10）作為被替換的字符，進行替換的字符是“""”，最后再使用“--”將文本轉換為數值數據。這樣公式下拉后可以將原來的換行符替換掉，并且實現數據的正確求和了。

原因3：數據中包含單位符號

一些新手在Excel中輸入數據時總是喜歡添加單位符號，單位符號的存在也容易導致求和失敗。此時可以使用SUMPRODUCT嵌套SUBSTITUTE函數進行正確的求和。比如在下圖中需要統計總的噸數。定位到B10單元格并輸入公式“=SUMPRODUCT（--SUBSTITUTE（B2：B9，"噸"，""））&"（噸）"”求和即可（圖5）。

公式解釋：

先使用SUBSTITUTE函數將中文單位替換為空，然后再將其作為SUMPRODUCT函數的求和區域，最后使用“&”符號和單位“（噸）”連接顯示。

原因4：小數點誤差導致求和出錯

Excel計算數據時，它是根據單元格的存儲值來計算的，而不是根據顯示值來計算的。但在日常輸入數據時，如公司的工資數值保留了三位小數，這樣導致存儲數值和顯示值（使用貨幣格式時默認四舍五入顯示）不同。比如員工1的原始工資數值是3111.005，輸入后顯示為3111.01（四舍五入顯示），但是在實際求和統計時參與計算的數值仍為3111.005，最終導致統計數據和應發工資數據出現差異（圖6）。

雖然上述統計的差異數據不大，但是如果統計人數較多，如10000人，那么最終數據誤差會變為100。要避免類似情況的出現，可以使用ROUND函數進行四舍五入，在原始數據B列后插入一個新列，接著定位到C2單元格，輸入公式“=ROUND（B2，2）”并下拉，然后以這個數值作為實際發放的工資數據再統計即可（圖7）。

如果需要讓Excel按照顯示數值求和統計，也可以點擊“文件→選項”，在打開的窗口中切換到“高級”，在右側的窗格中切換到“計算此工作簿時”，勾選其下的“將精度設置為所顯示的精度”，點擊“確定”（圖8）。這樣在Excel進行數據統計時就按照單元格顯示的數值統計，我們只要將數據格式設置為保留2位小數即可。