当Excel数据源不规范时，你还会求和吗？
作者：郅龙来源：部落窝教育发布时间：2021-08-03 16:38:10点击：1928
编按：
各位小伙伴大家好呀！我是爱生活爱Excel的小E~在处理Excel数据时经常会涉及到求和，如果我们的Excel数据源是完整规范的，用我们常见的SUM函数就能轻松搞定求和问题。如果我们的数据源像下面那样，错位了，不规范了，又该如何求和？其实依旧可以用SUM函数进行区域错位求和。跟着小E一起来学习一下吧~
最近收到一个这样的问题求助：
数据源是左边这种格式，按月存放的商品销量，现在需要汇总成右边的格式。然后这位同学就不知道该咋办了？
这个问题还挺典型的，由于数据源结构设计不当为后期统计带来了麻烦。因此今天教程内容分成两个部分：首先帮这位同学解决问题，会介绍两个函数解法；然后再结合这个例子解释一下什么才是合理的数据源结构。
解决这个问题可以用到两个函数：SUMPRODUCT和SUMIF。
SUMPRODUCT解法1：=SUMPRODUCT(IFERROR(($A$3:$E$14=H2)*$B$3:$F$14,0))
在I2单元格输入上述公式，按住快捷键
这个公式的核心部分是($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”的销量，例如第一个就是95；0是商品名称不是“商品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个月的那不是得12个SUMIF相加吗？
通过这样对比大家是不是发现用了区域错位公式就很简单呢。
好了，问题解决了，还是需要说一下规范数据源的问题。
其实就这个问题来说，规范的数据源应该是三列，月份、商品名称和销量。
如果是这样的数据源，要实现按月汇总或者按商品汇总都非常容易，用数据透视表就能实现。
月份+商品：
月份汇总：
商品汇总：
根本就不会有什么难度，所以说要用Excel做数据统计，好的数据源才是成功的99%。
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者郅龙；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。