HI,大家好,我是星光。
按指定条件对数据统计求和是工作中最常见的表格问题之一,今天给大家分享7个函数公式,都是拿来即可套用的模块化用法,可以解决单条件求和、模糊条件求和、并且关系的多条件求和、或关系的多条件求和、交叉表求和、动态表求和、多表求和等常见问题。 如下图所示,A~D列是成绩表,包含了多个学员及科目的成绩,需要据此统计F列指定人名的总分。如下图所示,A~C列是数据源,B列是公司全称,C列是金额,需要根据E列公司的简称统计对应的销售总额。=SUMIF(B:B,'*'&E2&'*',C:C) *是通配符,代表0到多个字符,'*'&D2&'*'则表示包含D2关键字的任意字符串。如下图所示,A~D列是一张成绩表,包含了多个学员及科目的成绩明细。现在需要在G2:I3区域编写公式,统计不同班级不同科目的总分。并且关系的多条件求和常使用SUMIFS函数,基本语法如下: 如下图所示,A~D列是一张成绩表。假设需要统计一班和二班的成绩总和。也就是只要A列的班级等于一班或二班,即对成绩累加求和。SUMIF函数的第2参数是一个常量数组{'一班','二班'},对这两个班级分别求和,返回一个内存数组{308,335},最后再使用SUMPRODUCT函数汇总。假设又需要统计一班和二班的语文成绩总和,多个字段的多条件求和可以换用SUMIFS函数:如下图所示,A~E列为数据源,是一张成绩表,包含了班级和各科成绩;需要在I列查询G列班级和H列科目的成绩总分。
A:A,0,MATCH(H2,B$1:E$1,0)) SUMIF函数的条件区域是班级所在的源表A列,查询条件是G2的班级,求和区域则不固定,有时候是数学列,有时候是语文列。此时使用一个OFFSET函数根据科目名称搭建动态求和区域。MATCH函数计算G2单元格的科目在B$1:E$1区域内首次出现的序列,比如返回结果为3;OFFSET函数以A:A列为基点,向下偏移0行,向右移动3列,返回D列的引用,也就是数学成绩所在的列。除了使用SUMIF函数外,也可以使用SUM或SUMPRODUCT函数。
=SUM((A$2:A$9=G2)*(C$1:E$1=H2)*C$2:E$9) 如下图所示,有3张工作表,分别命名为1月、2月、3月。每张工作表的结构都是相似的,A列是分公司的名称,B列是收入金额。现在需要在查询表中,根据B2单元格指定公司的名称、B3单元格指定的月份,汇总相关销售额。例如,当B1单元格为A城,B3单元格为1月时,表示从1月工作表中汇总A城的销售额。 INDIRECT('''&B2&''!A:A'), 公式使用INDIRECT函数创建对指定工作表A列和B列的引用,分别作为SUMIF的条件和求和区域,对B1单元格指定的城市汇总求和。 依然以上图所示数据为例,现在需要在汇总表中,统计A列各公司1-3月销售总额。=SUMPRODUCT( SUMIF( INDIRECT('''&{'1月','2月','3月'}&''!A:A'), A2, INDIRECT('''&{'1月','2月','3月'}&''!B:B') ) ) INDIRECT('''&{'1月','2月','3月'}&''!A:A')部分,{'1月','2月','3月'}是目标工作表名称,使用INDIRECT函数创建对这些工作表A列的引用,作为SUMIF的条件区域。同样的方式再创建对这些工作表B列的引用,作为SUMIF的求和区域。对这些工作表分别求和返回多个结果,最后使用SUMPRODUCT函数汇总。打个响指,今天给大家分享的内容就这些。有啥疑惑之处可以在VIP会员微信群中提问交流,挥挥手,右下角点个赞,咱们明天再见。
|