28如何用函数处理交叉表求和问题？

最近会员群里经常有朋友提问交叉表求和的问题，今天集中分享2个常见的案例。

先说个简单的。

如下图所示，A~E列为数据源，是一张成绩表，包含了班级和各科成绩；需要在I列查询G列班级和H列科目的成绩总分。

I2单元格输入以下公式：

SUMIF函数 ▼

=SUMIF(A:A,G2,OFFSET(A:A,0,MATCH(H2,B$1:E$1,0)))

SUMIF是最常用的单条件求和函数，共有3个参数，基本语法如下：

SUMIF基本语法 ▼

=SUMIF(条件区域,条件,[求和区域])

对于以上公式来说，条件区域是班级所在的源表A列，查询条件是G2的班级，求和区域则不固定，有时候是数学列，有时候是语文列，有时候我会相信一切都有尽头，相聚离开都有时候…

此时使用一个OFFSET函数根据科目名称搭建动态求和区域。MATCH函数计算G2单元格的科目在B$1:E$1区域内首次出现的序列，比如返回结果为3；OFFSET函数以A:A列为基点，向下偏移0行，向右移动3列，返回D列的引用，也就是数学成绩所在的列。

SUMIF函数筛选出A列班级等于一班的数据，并对D列的成绩求和，即为结果。

除了使用SUMIF函数外，也可以使用SUM或SUMPRODUCT函数。

参考解法如下：

数组公式 ▼

=SUM((A$2:A$9=G2)*(C$1:E$1=H2)*C$2:E$9)

公式先判断A2:A9区域的班级是否等于G2单元格的班级，返回一个由逻辑值构成的一维垂直内存数组；然后判断C1:E1的科目是否等于H2单元格的科目，返回一个由逻辑值构成的一维水平内存数组；两个不同向的一维数组相互运算，按照函数数组的运算规则，返回一个n行m列的二维数组：

将这个二维数组和成绩区域C2:E9相乘，再统计求和即为结果。

如果你对函数数组的运算规则不甚了然，推荐阅读我们的往期教程：

……

打个响指，再说下第2个案例。

如上图所示，A~D列是数据源，需要据此在H2:J5区域统计相关人员在指定月份和项目上的总得分。

看我小眼神，发现了吧大熊迪，这题和上一题不能说一模一样，但几乎毫无差别，只是从单条件求和变成了多条件求和，条件区域都是固定的，只有求和区域是动态的。

参考公式如下：

交叉表多条件求和 ▼

=SUMIFS(

OFFSET($A:$A,0,

  MATCH($G2,$B$1:$D$1,0)),

$A:$A,$F2,

$B:$B,H$1

)

公式依然使用OFFSET函数搭建动态的求和区域，整个结构和之前的SUMIF函数是一样的。需要注意的是，SUMIF是先讲条件再求和，它最后一个参数是求和区域；SUMIFS是先求和再讲条件，它的第1参数是求和区域。

最后给大家留一个练手题儿。

如下图所示，A~D列是一张成绩表，需要统计一班语文二班数学三班英语的合计总分，模拟结果是801——摊手，这个练习题似乎很诡异，但确实很有练习效果。

参考解法如下：

交叉表多条件求和 ▼

=SUMPRODUCT(ISNUMBER(

FIND(A2:A16&C1:E1,

"一班语文二班数学三班英语"))

*C2:E16)