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

一个特殊的求和案例:每个汇总值所对应的行数和列数都不固定

 

作者:老菜鸟来源:部落窝教育发布时间:2022-05-20 15:01:13点击:1406

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

编按:

今天来给大家分享一个特殊的求和问题,即每个汇总值所对应的行数和列数都是不固定,我们最后用到的是SUMPRODUCT函数,没思路的同学赶紧来看一看吧!

 

最近看到一位群友的求助,觉得问题很典型,特意拿出来和大家分享一下。

模拟数据如图所示:

 

 

上半部分是销售数量的明细表,我们要按平台和商品分类对销量进行统计,统计表放在数据源的下方,上方黄色的区域之和对应下方黄色的一个单元格。

问题不难理解,但确实有点麻烦。因为每个汇总值所对应的行数和列数都不固定。

以下先给出一个解决方案,然后再做解析。

输入公式:

=SUMPRODUCT($B$2:$P$18*(LEFT($A$2:$A$18,LEN($A22))=$A22)*(LEFT($B$1:$P$1,LEN(B$21))=B$21))

 

 

这个公式看起来挺复杂,原理还是比较好理解的,下面分段进行介绍,希望大家能看明白。

这个问题的本质是多条件求和,只不过条件有点特殊。对于多条件求和的问题,用SUMPRODUCT基本能囊括完。

其次要理解的是,这个问题涉及到两组条件,条件1是平台(店铺),条件2是分类(品名)。

所以公式的主体结构就能确定了,=SUMPRODUCT(数据区域*条件1*条件2)

看上去是不是挺简单?

数据区域是$B$2:$P$18,这也好理解,难点就在于两个条件的表达方式,下面来详细解释一番。

条件1:平台(店铺)

 

 

通过对比发现,店铺名称都是以平台名称作为开头的,所以用LEFT函数就可以从店铺名称里提取出平台名称,问题是平台名称的长度不是固定的,有的两个字,有的三个字,因此只用LEFT还不够,再组合LEN函数来确定要截取的长度就可以了。

公式中的LEFT($A$2:$A$18,LEN($A22))这部分得到的结果如图所示。

 

 

这里用到了一个数组计算,按照A22单元格里字符的长度,在$A$2:$A$18这个区域截取内容,有五个是和A22单元格的内容一样,由此确定这五行数据是需要汇总的。

因此第一个条件是:(LEFT($A$2:$A$18,LEN($A22))=$A22)

注意$A22使用了锁定列的混合引用方式,因为公式在拖动的时候要始终固定在A列。

同样的原理,第二个条件是判断分类和品名之间的关系,公式(LEFT($B$1:$P$1,LEN(B$21))=B$21)中唯一要注意的就是B$21变成了锁定行的混合引用,这是因为公式在拖动的时候始终要固定在一行。

经过以上分析再来看整个公式,就比较清晰了,你看明白了吗?

总结一下,这个公式的难点是两个条件都是部分包含的关系,这与以往的例子有所不同。

不过,好在,最后我们给出了完美的解决方案!

好啦!以上就是今天的所以内容啦,你学会了吗?

 

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

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

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

IMG_256

相关推荐:

你会累计求和吗?这5个技巧简直太好用了!

Vlookup函数能隔列求和,你知道怎么操作吗?

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

工资表转工资条,VLOOKUP有绝招!

版权申明:

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