access sum函数出错_采购：一个SUM函数，浪费了我整整1个小时

今日目标：

学会公式错误检查方法

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

点击图片，查看大图

公式的长度有6行！！！

拉登老师的头发也是一根一根长出来的

不是充话费送的

我们来一起保护它

好吗？

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

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

1- 问题描述

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

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

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

问题原因：

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

解决方法：

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

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

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

2- 公式嵌套错误排查

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

1- 分列功能，文本转数字

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

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

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

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

2- SUM求和错误排查

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

换个思路！

既然是SUM求和出错的，那么就每个单元格都相加测试一下，看哪个单元格开始算错了。

这让我想到了行列锁定中的「拉灯模式」用法。

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

公式如下：

=SUM($E$3:E3)

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

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

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

= C17-D17= 0 - 161740.71= 0

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

=秋叶!J685=秋叶!R685

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

我们继续往下排查。

3- 循环引用排查

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

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相关的计算，全部都出错了。

4- 总结

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

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

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

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

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

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

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

好了，今天的内容就是这样，记得点「在看」，下课！

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

= = 推荐文章 = =

采购：项目统计表很烂，领导设计的，我有啥办法？

采购：BOM表型号多列转一列，这样做更简单