分門別類解決Excel求和出錯(cuò)問(wèn)題

平淡

原因1：文本格式所導(dǎo)致

在進(jìn)行一些數(shù)字的求和時(shí)，由于數(shù)據(jù)格式為文本格式，這樣會(huì)導(dǎo)致SUM函數(shù)無(wú)法正確求和。如公司的ER P系統(tǒng)導(dǎo)出的數(shù)據(jù)都是文本格式（在單元格的左上角會(huì)有綠色三角形標(biāo)記），這樣求和就會(huì)出錯(cuò)（圖1）。

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

原因2：數(shù)據(jù)包含特殊格式

在Excel中，數(shù)字格式中不能包含除數(shù)字之外的其他格式。如在網(wǎng)上復(fù)制的數(shù)據(jù)，在數(shù)據(jù)中間或者開(kāi)頭、結(jié)尾包含空格，空格的存在會(huì)導(dǎo)致求和錯(cuò)誤。此時(shí)可以選中數(shù)據(jù)中的任意空格并復(fù)制，接著按下“Ctrl+H”打開(kāi)“查找和替換”對(duì)話框，然后在“查找內(nèi)容”處輸入粘貼的空格，“替換為”設(shè)置為空，點(diǎn)擊“全部替換”，之后就可以正常求和了。

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

公式解釋：

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

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

公式解釋：

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

原因3：數(shù)據(jù)中包含單位符號(hào)

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

公式解釋：

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

原因4：小數(shù)點(diǎn)誤差導(dǎo)致求和出錯(cuò)

Excel計(jì)算數(shù)據(jù)時(shí)，它是根據(jù)單元格的存儲(chǔ)值來(lái)計(jì)算的，而不是根據(jù)顯示值來(lái)計(jì)算的。但在日常輸入數(shù)據(jù)時(shí)，如公司的工資數(shù)值保留了三位小數(shù)，這樣導(dǎo)致存儲(chǔ)數(shù)值和顯示值（使用貨幣格式時(shí)默認(rèn)四舍五入顯示）不同。比如員工1的原始工資數(shù)值是3111.005，輸入后顯示為3111.01（四舍五入顯示），但是在實(shí)際求和統(tǒng)計(jì)時(shí)參與計(jì)算的數(shù)值仍為3111.005，最終導(dǎo)致統(tǒng)計(jì)數(shù)據(jù)和應(yīng)發(fā)工資數(shù)據(jù)出現(xiàn)差異（圖6）。

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

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