excel如何做两个表格(Excel表格制作教程:12种多列数据求和,最后一种方法太厉害!)

wufei123 发布于 2023-11-03 阅读(805)

全套Excel视频教程,微信扫码观看编按:按条件求和,工作中很常见如果是根据条件求单列数据之和,SUMIF函数即可解决,但如果是求多列数据呢?我们这里分享12种方法,各有各的特色先来看一下什么是按条件求多列数据之和。

类似下图这样的数据,需要根据G列的产品名称在H列汇总数据。条件区域在B列,而要求和的数据在C、D、E三列中。这种求和就是按条件求多列数据之和,简称多列条件求和。

这类条件求和,在实际工作中经常会遇到,但直接用一个SUMIF函数或者透视表是无法完成的今天给大家分享解决这个问题的12个套路公式(有没有被惊到?),当然你能掌握其中的两三种就够用了(请允许我像孔乙己那样炫耀一回)。

公式1:=SUMIF(B:B,G2,C:C)+SUMIF(B:B,G2,D:D)+SUMIF(B:B,G2,E:E)

刚才说过无法直接用一个sumif函数求和,因为sumif要求条件区域和求和区域大小相同,而本例显然不满足这个要求用三个sumif分别求和后再相加,这不难理解,但是如果要求和的列更多的话,还是有点麻烦公式2:

=SUM(IF(B$2:B$16=G2,C$2:E$16))

这是一个数组公式,需要按住Ctrl、shift和回车键完成输入数组有自扩展性,利用这个特性就可以将一列条件与三列数据进行判断满足条件的时候为对应数字,不满足条件时得到FALSE,这是if函数省略第三参数以及第三参数前逗号的用法。

在这个公式中,用if做条件判断得到需要求和的数字,再用sum实现最终的求和结果。公式3:=SUM((B$2:B$16=G2)*C$2:E$16)

这个公式是比较常用的一种套路,与公式2的区别在于少了用if函数进行判断,它直接利用了逻辑值参与计算公式同样需要三键输入如果不习惯三键的话,SUM数组公式可以用SUMPRODUCT函数取代关于SUMPRODUCT函数的用法可以查看《加了*的 SUMPRODUCT函数无所不能》。

公式为:=SUMPRODUCT((B$2:B$16=G2)*C$2:E$16),两个公式原理完全一致,可以视为同样的公式公式4:=SUMPRODUCT((B$2:B$16=G2)*(C$2:C$16+D$2:D$16+E$2:E$16))。

这可以视为公式3的另一种思路,当求和区域是连续的多列时,两个公式都可以用;如果要求和的多列是不连续的,例如只求第1周和第3周的和,则只适合用公式4以上四个公式都属于比较基础、常用的套路下面要分享的公式,会涉及一些稍有难度或者难以理解的函数。

如果你有一定的基础,可以结合公式自己去研究一下;如果感到难以理解的话,也可以先收起来,作为日后学习的一个方向公式5:=SUMPRODUCT((B$2:B$16=G2)*MMULT(C$2:E$16,{1;1;1}))

SUMPRODUCT和MMULT函数联手,感到蒙圈了没有?公式6:=SUM(MMULT((B$2:B$16=G2)*C$2:E$16,{1;1;1}))

注意哦,这个公式可不是简单的把SUMPRODUCT换成SUM了要看懂这两个公式,必须对MMULT函数有所了解如果对这个函数还比较陌生的话,咱们换一个大家稍微熟悉点的OFFSET函数也可以对OFFSET不熟悉的可以查看《Excel进阶之路必学函数:动态统计之王——OFFSET(上篇)》。

公式7:=SUM(SUMIF(B:B,G2,OFFSET(B:B,,{1,2,3})))

这个公式其实是对公式1的优化,利用OFFSET得到了三个一列的求和区域,相当于用一个SUMIF和OFFSET实现了三个SUMIF的工作公式的优势在于当求和列增加的时候,只需要在OFFSET里增加偏移数即可。

通常能用OFFSET构造的多区域数据,INDIRECT也可以搞。公式8:=SUM(SUMIF(B:B,G2,INDIRECT("c"&{3,4,5},)))

INDIRECT函数比较牛的地方是有两种引用方式,也就是RC模式和A1模式,函数的第二参数就是确定使用何种引用方式的公式9:=SUM(SUMIF(B:B,G2,INDIRECT({"c","d","e"}&1)))。

注意仔细区分这两个公式中INDIRECT里的区别实际上,7、8、9这三个公式的思路差不多,都是用函数构造多个单列区域,为SUMIF服务,区别只是OFFSET与INDIRECT,以及INDIRECT的两种引用形式。

公式10:=SUM(DSUM(A$1:E$16,{3,4,5},G$1:G2))-SUM(H$1:H1)

这个公式的关键是DSUM函数DSUM是一个数据库类的求和函数,可以实现条件求和,有兴趣的朋友可以自己了解一下这个函数,看看教程《DSUM,最简单的条件求和函数!你知道不?》公式11:=SUMPRODUCT(COUNTIF(G2,B$2:B$16)*C$2:E$16)。

SUMPRODUCT和COUNTIF都是比较常用的函数这个公式中,COUNTIF充当了条件判断的角色,你能看明白其中的门道吗?公式12:=MMULT(MMULT(N(G2:G6=TRANSPOSE(B2:B16)),C2:E16),{1;1;1})。

最后这个公式无疑是很有分量的,不然不足以压轴特别要说明的一点是这个公式要选定公式区域,然后按照数组公式的输入方式完成,而不是先有数组公式再下拉的那种用法12个公式有很简单的,也有比较难的,有你能看懂能使用的,也有你暂时还无法理解的。

但不管怎样,相信你都能通过今天的内容有一些新的收获今天教程的课件请到QQ群:946920877下载,或者添加老师微信领取。

Excel教程相关推荐工作再忙,也要学会这5个神级快捷键!【Excel教程】【限时免费领取】Excel教程:17集Excel函数公式,滴答老师主讲!Excel教程:Excel中折腾我们又无法解决的问题

让工作提速百倍的「Excel案例实战课」↓ 点击阅读原文,1元抢购。

亲爱的读者们,感谢您花时间阅读本文。如果您对本文有任何疑问或建议,请随时联系我。我非常乐意与您交流。

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

大众 新闻80484