二维码 购物车
部落窝在线教育欢迎您!

文本格式的求和,及求和中最容易出现的问题解疑

 

作者:老菜鸟来源:部落窝教育发布时间:2021-10-28 15:09:18点击:2682

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

Excel的日常操作中,我们最容易碰到的便是求和公式,但是在给数字求和时,总会遇到各种各样的问题,让人丈二和尚摸不着头脑。今天就让我们来系统性地给大家讲解一下,单纯求和中,最容易出现的一些小失误,希望大家可以举一反三。

 

今天和大家来聊聊Excel的几个关于求和的问题。可能有的同学就纳闷了,求和这么简单还用聊吗?不就是SUM函数。

如果真的这么简单肯定就没啥聊的了,不信就看看下面这些问题你会几个?

Ps:今天讨论的都是单纯的求和问题,并没有那种合并单元格求和、筛选求和、特殊的条件求和等等复杂的问题。


问题一:数字求和结果为0

假如有一列数字,使用SUM函数求和时却发现结果为0,通常有两种可能。

1.数字是文本格式

例如下图中的这种情况:

 

 

对于这种情况有三种方法。

方法1:数字的单元格左上角有个绿色小三角,可以通过小三角将数据转为数值类型,求和结果就正确了。

 

 

操作要点:一定要从第一个有绿色三角的单元格开始选择,是最简单的一种方法。

方法2:选中这一列,用分列功能进行处理。

 

 

操作要点:分列的时候直接点完成即可。

方法3:直接用公式=SUMPRODUCT(B2:B12*1)求和

 

 

公式中的(B2:B12*1)这部分是通过乘法运算将文本型的数字转换为数值,再利用SUMPRODUCT函数可以对数组求和的特性来解决问题的。

 

2.有不可见字符

这种问题常见于系统导出的数据,数据不是文本格式,但是求和结果也是零,例如下图演示的情况。

 

 

这种只是不可见字符中的一种类型,还有些不可见字符更加隐蔽,在编辑栏也看不出问题,遇到这种情况可以先使用LEN函数做个检测。

 

 

LEN函数的功能是得到单元格里的字符数,检查结果表示单元格里数据的长度,明显可以看出比实际数据多了一个字符,说明有一个不可见字符。

遇到这类情况,可以直接用公式=SUMPRODUCT(CLEAN(F2:F12)*1)求和,CLEAN(F2:F12)的作用是清除单元格中的不可见字符,再利用*1将清除后的数据变成一个数组,由SUMPRODUCT函数完成求和。

 

问题二:对带单位的数字求和

有些同学总是习惯在数据后面加上单位,当然也可能领导要求这样做的,造成的后果就是无法求和。

 

 

对于这类问题,正确的解决方法是先把单位去掉,然后用自定义格式加上需要的单位,这样就不会影响求和了,操作方法看动画演示。

 

 

当然也可以直接用公式=SUMPRODUCT(SUBSTITUTE(F2:F12,"","")*1)进行求和。

 

 

这个公式首先是用SUBSTITUTE(F2:F12,"","")""字替换为空得到一组数字,然后再利用*1变成数组后由SUMPRODUCT完成求和。

 

上面的两个方法对于大多数情况来说都可以搞定的,如果你遇到的情况更加复杂,可以参考之前的教程:

https://mp.weixin.qq.com/s/tU_2rBy9XQA3OvXfu0u8RQ

 

问题三:数据中有错误值

数据中有错误值的情况简直是太常见了,有匹配不到结果返回错误值的,也有分母为零造成错误值的,还有用了一些复杂公式产生错误值的,这不是我们今天讨论的要点,我们只讨论当求和的数据中出现错误值时该怎么办?

下面这个图里模拟了三种错误值,直接求和的话结果也是错误值。

 

 

遇到这样的情况当然是要找出每种错误出现的原因,从源头去解决问题。

但是如果想直接在保留错误值的情况求和的话,也有三个公式可以使用。

 

 

公式1=SUM(IFERROR(B2:B12,0))

利用IFERROR函数将区域中的错误值变成0,然后再用SUM函数求和,注意这个公式是数组公式,需要同时按Ctrlshift和回车键完成输入。

公式2=SUMIF(B2:B12,"<9e307")

公式中的9e307,表示9乘以10307次幂,是一个非常大的数值,SUMIF只对小于9E307的数值部分进行求和,并且自动忽略区域中的错误值。本例中因为求和区域和条件区域相同,所以可以省略SUMIF函数的第三个参数。

公式3=AGGREGATE(9,6,B2:B12)

AGGREGATE函数第一参数使用9,表示汇总方式为求和,第二参数使用6,表示忽略错误值。也就是在忽略错误值的前提下,对B2:B12区域进行求和。

 

问题四:循环引用导致求和结果为零

这是一种特殊的人为错误,从表面上看不出任何问题。

 

 

没有文本格式的数字,也没有不可见字符,但是求和结果是0

实际上在编辑公式的时候,或者打开这个文件的时候,Excel会出现一个提示:

 

 

这就是在告诉你计算不正确的原因是因为存在了循环引用。

如果你不知道是哪个单元格出错的话,可以依次点击【公式】-【错误检查】-【循环引用】,就能看到有问题的单元格了。

 

 

然后再去检查公式,就能发现B13单元格里的公式是=SUM(B2:B13),求和的范围包含了B13,只要将B13改成B12OK了。

 

问题五:时间的求和问题

最后一类问题是涉及到时间的求和,例如在对一个人的加班时长求和时,结果可能不是你所希望看到的。

 

 

最后合计的加班时长竟然只有3:40,结果肯定有问题。

出现这种问题的原因是因为在Excel中时间累计到24小时以上就会自动向日期进位,也就是24小时变成1天,只有不足24小时的部分才会以时间的形式显示。

解决方法也有两个。

 

 

公式1=TEXT(SUM(B2:B12),"dh小时m")可以将合计时长以xx小时x分的形式显示。

公式2=TEXT(SUM(B2:B12),"[h]小时m")可以将合计时长以x小时x分的形式显示,[h]两边加上方括号就表示小时这部分不用向上进位。

 

今天分享的内容就是这么多,回到开头的那个话题,这些求和的问题你会几个呢?

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。