excel如何快速汇总多个类别的总和?

时间:2024-01-29 19:21:39

这个需求是一位在当前抗疫一线的朋友提出的,和各位分享一下。

需求情况

因为众所周知的原因,他每天都需要为照顾的小区居民购买、运送生活物资。小区居民通过表单的形式提交自己每日的需求,最终汇总到一张excel表里,如下图:

每一行就是一户居民当日需要采购的物资

物资的价格在另外一张名叫“价格”的表当中如下:

这位朋友需要做的就是把每一户要采购的物资总金额计算出来,然后再进行后面的工作。

解决思路

大致思索一番,其实这个问题可以有多个办法来解决。

方法一:excel函数

这个办法有点繁琐,但是简单有效。

以第一户为例,把总金额计算在I2单元格,公式为:

=IF(E2="/",0,1)*价格!$A$3+IF(F2="/",0,1)*价格!$B$3+IF(G2="/",0,1)*价格!$C$3+IF(H2="/",0,1)*价格!$D$3

公式里的IF函数的作用是,对每项物资进行判断,等于“/”的就为0,不等的就为1,然后再和价格相乘,最后加总求和。

然后再向下拖即可。

方法二:sumproduct函数

这个方法和上面的思路一样,但是公式更简化。

还是以第一户为例,把总金额计算在K2单元格,公式为

=SUMPRODUCT(--(E2:H2<>"/"),价格!$A$3:价格!$D$3)

SUMPRODUCT()函数的作用就是乘积汇总,–(E2:H2<>”/”)表示筛选不等于“/”的项目,标记为1;等于“/”的项目,标记为0;然后再和对应的价格相乘,最后汇总。

然后再向下拖即可。

方法三:VBA

思路基本一致,使用更加灵活,一键完成到位。

为了方便,就直接调用方法二中的SUMPRODUCT()函数。

Sub caijia1()
Range("Q2", "Q10").ClearContents
j = [a2].End(xlDown).Row
For i = 2 To j
    Range("M" & i).Value = Evaluate("SUMPRODUCT(--(E" & i & ":H" & i & "<>""/""),价格!$A$3:价格!$D$3)")    
Next
End Sub

这样的好处是不用再手动的输入函数,然后再往下拖。只需要一键即可完成计算,即使是数据有增加、或者减少,VBA都可以自动识别,省心省力。

暂时就想到这么多。如果你也有好的办法,欢迎告诉我!

相关阅读:Excel VBA 实例(25) – 班级随机点名并播放
Excel VBA 实例(24) – 新股(债)中签一键批量查询
Excel VBA 实例(23) – 一键批量提取word表格内容
Excel VBA 实例(22) – 一键筛选其他工作表或工作簿的数据

 

欢迎交流!