采购：一个SUM函数，浪费了我整整1个小时

我是拉小登，一个会设计表格的 Excel 老师~

昨天有个同学的提问，让我非常的头秃。

公式的长度有 6 行！！！

拉登老师的头发也是一根一根长出来的，不是充话费送的！我们来一起保护它，好吗？

类似的奇葩问题并不少，上周还给一个采购的同学解答了一个 SUM 函数的问题，问题很简单，但是我却花了 1 个小时才搞定。

为了避免类似低效工作，在你身上重演，请务必看完这篇文章。

问题描述

年底采购小姐姐做数据盘点的时候，遇到了一个很奇怪的事情。

表格 E 列中明明有数据，但是 SUM 求和的结果却是 0。

这样的问题我见的太多了，看一眼我就知道问题在哪里。

问题原因：

数字求和为 0，通常是因为是数据被保存成了文本类型，而导致的。

解决方法：

非常简单，使用分列功能，把文本转成数字就可以了。

本以为是一个很简单的问题，我就要过来表格，随手帮她解答一下，结果，这是一个无底洞，前前后后花了我 1 个小时时间。

我跟大家回顾一下这个过程。

公式嵌套错误排查


我们从头开始，一步一步还原整个排查的过程。

▋分列功能，文本转数字

前面说过，SUM 求和为 0 的问题，常规方法是使用分列功能，把文本转成数字就可以搞定。

不过，很显然，这个操作没有成功，求和结果还是 0。每个单元格的格式都已经是「常规」。

那么问题肯定是出现在某个单元格里，没办法，只能逐个的排查了。

每个单元格，是前两列数据相减得出来的，那么排查过程中，免不了还要检查前面引用的两列数据，这就麻了烦了。

▋SUM 求和错误排查

这样做显然是行不通的，数据有 45 行，再乘以前面两列，那就是 45*3=135 个单元格，没个半小时搞不定。

换个思路！

既然是 SUM 求和出错的，那么就每个单元格都相加测试一下，看哪个单元格开始算错了。
这让我想到了行列锁定中的「拉灯模式」用法。

在第 1 个数据旁边的空白单元格里输入 SUM 公式：

公式如下：

• =SUM($E$3:E3)
  

这样填充公式后，可以实时查看，截止到当前单元格的求和结果，容易排查出错误。

果不其然，在第 17 行的时候，我们发现了错误：

SUM 函数计算错误，数据没有累加。再检查左侧的单元格里的公式：

• = C17-D17

• = 0 - 161740.71

• = 0
  

这个计算显然是不对的，然后，我再对应检查左侧的两个单元格。这两个单元格分别引用了下面两个单元格：

• =秋叶!J685

• =秋叶!R685

  
  

到这里，第 1 个 SUM 函数的错误就排查完毕了，错误的原因指向了「秋叶」这个工作表。

我们继续往下排查。

循环引用排查


来到「秋叶」这个工作表里查看一下，依次排查这两个单元格。

R685 也是一个 SUM 函数，计算结果正常。

J685 这个单元格就不对了，和第 1 步出现了相同的问题。SUM 函数引用了一大堆的数据，计算结果也是 0。

• =SUM(J101+J393+J426+J431+J478+J497+J535+J544+J576+J623+J656+J663+J684+J569+J587)
  

没办法，一个一个排查单元格吧。

▋逐个排查错误

一个很大的问题，「秋叶」这个表里的数据更多，有 600 多行，一个一个排查的话，要累死人。

怎么准确地排查 SUM 函数里对应的公式呢？

我想了一个好办法，把前面的 SUM 函数，拆分成单独的 TYPE 函数，判断每个单元格的值，是数字还是文本：

• =TYPE(J101)

• =type(J393)

• =type(J426)

• =type(J431)

• =type(J478)

• =type(J497)

• =type(J535)

• =type(J544)

• =type(J576)

• =type(J623)

• =type(J656)

• =type(J663)

• =type(J684)

• =type(J569)

• =type(J587)
  

拆分的方法，也是费了一番周折，把公式粘贴到 Word 中，使用通配符完成替换，具体如下：

然后把公式，复制粘贴到 Excel 中，就可以检查单元格值，是文本还是数字了。

这样，我们很快的就找到了错误的单元格，错误指向 J426 单元格。

▋万人坑循环引用

再继续顺藤摸瓜，找到 J426 单元格，又是相同的问题，SUM 函数求和，有数据但是求和结果为 0。

这一次的情况，和前面第 1 步是一样的，是对连续的数据求和，所以排查方法，也是一样的。

在旁边增加一个辅助列，使用 SUM 函数+拉灯模式，排查是哪个单元格出了问题。

然后我们顺利的找到了错误的单元格 J414，再检查一下错误单元格里的公式。

罪魁祸首终于找到了，J414 里的公式又引用了自己，造成了循环引用，然后产生了连锁反应，所以和 J414 相关的计算，全部都出错了。

总结

今天的内容有点烧脑，罪魁祸首就是公式的循环引用。

在实际工作中，应当避免公式中的循环引用，在 Excel 中还有一个功能，可以一键检查循环引用↓↓↓

在【公式】选项卡中，点击【错误检查】-【循环引用】，就可以快速找到循环引用的单元格。

除此之外，我们还学习到了下面几种，排查 SUM 函数错误的方法：

❶ 分列功能，可以快速完成文本到数字的转换。

❷ 公式排查方法，使用拉灯模式，查找替换法，可以对单个单元格进行公式计算排查。

❸ 循环引用，要尽可能的避免循环引用的计算，一旦发现不了，随着公式嵌套的越来越多，排查类似错误的难度会越来越大。

好了，今天的内容就是这样，下课！

上面的技巧，哪个是你不会的呢？

如果你想和我实时交流 Excel 问题，可以参加《3 天 Excel 集训营》，上课方式是直播+录播，有名师带学、助教答疑。

秋叶 Excel 读者专享

官网价 99 

现在仅需 1 元

你也有可能成为 Excel 高手

点击下方小程序即可报名

👇👇👇

↑↑↑

现在报名免费获取

307 个函数清单

35 个常用函数说明

……

*广告