分门别类解决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位小数即可。