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

当Excel数据源不规范时,你还会求和吗?

 

作者:郅龙来源:部落窝教育发布时间:2021-08-03 16:38:10点击:1431

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


编按:

各位小伙伴大家好呀!我是爱生活爱Excel的小E~在处理Excel数据时经常会涉及到求和,如果我们的Excel数据源是完整规范的,用我们常见的SUM函数就能轻松搞定求和问题。如果我们的数据源像下面那样,错位了,不规范了,又该如何求和?其实依旧可以用SUM函数进行区域错位求和。跟着小E一起来学习一下吧~

 

最近收到一个这样的问题求助:

 

 

数据源是左边这种格式,按月存放的商品销量,现在需要汇总成右边的格式。然后这位同学就不知道该咋办了?

 

这个问题还挺典型的,由于数据源结构设计不当为后期统计带来了麻烦。因此今天教程内容分成两个部分:首先帮这位同学解决问题,会介绍两个函数解法;然后再结合这个例子解释一下什么才是合理的数据源结构。

 

解决这个问题可以用到两个函数:SUMPRODUCTSUMIF

 

SUMPRODUCT解法1=SUMPRODUCT(IFERROR(($A$3:$E$14=H2)*$B$3:$F$14,0))

I2单元格输入上述公式,按住快捷键确认,然后用鼠标下拉公式至I14单元格结束。

 

 

这个公式的核心部分是($A$3:$E$14=H2)*$B$3:$F$14

H2是要汇总的商品名称,$A$3:$E$14是包含了所有商品名称的数据源区域。用H2单元格中的内容也就是“商品1”和这个区域中的每个数据做比较,就会得到一组逻辑值。

 

 

商品名称是 “商品1”的位置得到的就是TRUE,其他位置全部是FALSE

$B$3:$F$14是包含了所有销量的数据源区域。注意,这个区域不全是代表销量的数字,还有商品名称在其中。因此相乘后会出现三种情况,非零数字、0和错误值。

 

 

非零数字就是商品名称为“商品1”的销量,例如第一个就是950是商品名称不是“商品1”的销量与FALSE相乘的结果;错误值是文本(商品名称)与逻辑值相乘的结果,因为文本无法进行计算。

总结来说,大于零的数就是我们需要汇总的,0和错误值都是需要忽略的。

因此使用IFERROR函数将错误值变成0,然后再用SUMPRODUCT对数组进行求和就得到了最终的汇总结果。

 

SUMPRODUCT解法2=SUMPRODUCT(IF($A$3:$E$14=H2,$B$3:$F$14,0))

在单元格内输入上述公式,按住快捷键确认,然后用鼠标下拉公式结束。

 

 

与第一个公式不同,这个公式直接用IF函数把不是要找的商品名称所对应的销量都变成0,然后再用SUMPRODUCT去求和。

IF($A$3:$E$14=H2,$B$3:$F$14,0)得到的结果是这样的一个数组:

 

 

方法2的逻辑要比方法1简单直接,但是两个方法的核心都用到了区域错位的逻辑,也就是$A$3:$E$14$B$3:$F$14这两个区域。

每个区域都混合了商品名称和销量,对于人来说,视觉上感觉是两列数据为一组,但是对于Excel来说,并没有这些概念,Excel只是按照区域对应的位置去执行计算,遇到不能计算的就返回错误值。

这种区域错位是一种很常用的手法,大家都非常熟悉的SUMIF函数有一个本事就是支持错位求和,因此这个问题就有了第三种解法。

 

方法3=SUMIF($A$3:$E$14,H2,$B$3:$F$14)

 

 

这个公式看起来和平时用的SUMIF并没有什么区别,真要说有区别的话,就是条件区域和求和区域的选择,并不是我们习惯上的那种选择方法,而是用到了错位区域的原理。

用我们习惯的SUMIF写法公式大概得是这样的才行:

=SUMIF($A$2:$A$14,H2,$B$2:$B$14)+SUMIF($C$2:$C$12,H2,$D$2:$D$12)+SUMIF($E$2:$E$11,H2,$F$2:$F$11)

 

 

但这才是3个月的,如果是12个月的那不是得12SUMIF相加吗?

通过这样对比大家是不是发现用了区域错位公式就很简单呢。


好了,问题解决了,还是需要说一下规范数据源的问题。

其实就这个问题来说,规范的数据源应该是三列,月份、商品名称和销量。

 

手机屏幕的截图

中度可信度描述已自动生成

 

如果是这样的数据源,要实现按月汇总或者按商品汇总都非常容易,用数据透视表就能实现。

月份+商品:

 

 

月份汇总:

 

表格

描述已自动生成

 

商品汇总:

 

表格

描述已自动生成

 

根本就不会有什么难度,所以说要用Excel做数据统计,好的数据源才是成功的99%

 

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

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

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

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏

版权申明:

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