运用SUMPRODUCT函数快速统计高中成绩

李新会

在excel 2003中，SUMPRODUCT函数功能是返回相应区域或数组乘积的和，我们可以运用它来对符合条件的单元格数目进行求和，可以设置多个求和条件，使用场合非常广泛。灵活运用SUMPRODUCT函数，可以完成很多复杂的统计任务。我结合高中成绩统计实例介绍一下该函数的用法。

一、建立对应工作表

首先在EXCEL中建立成绩、上线分、分数段、学校、班级五个工作表。成绩表中存放学生成绩，格式如图1。考号第2位代表学校，前4位代表班级，学校、班级这两列数据用mid（）函数从考号中提取即可。上线分表用来存放各科的分数线，如图2。分数段、学校、班级三个表用来存放统计结果。

二、定义区域，简化公式

在统计时，公式中往往需要指明计算的单元格区域，公式写得很长，容易出错。可以将相应单元格区域定义一下，简化公式，增强可读性。选中成绩表所有数据，单击 “插入→名称→指定”，在打开的窗口中选择名称创建于“首行”，单击“确定”，这样就快速定义了序号，考号，语文等多列区域。比如要计算语文一列的平均分，如果没有定义区域，必须输入公式“=AVERAGE（d2：d3000）”，现在只需输入公式“=AVERAGE（语文）”。在成绩表中定义的区域在其他工作表中可以直接使用，这个技巧在编写复杂公式时效果尤其明显。

三、统计不同分数段人数

切换到分数段工作表，如图3所示，需要统计各校不同分数段的人数，可以用SUMPRODUCT函数完成。在B5单元格中输入公式“=SUMPRODUCT（（学校="1"）*（总分<>0））”，可以求出一中（其学校代码为1）实际参加考试人数，指定总分<>0可以将缺考的学生去掉。在C5单元格中输入公式“=SUMPRODUCT（（学校="1"）*（总分>=900））”求出一中总分在940分以上的人数。用同样方法算出一中在不同分数段的人数。写完一中对应的公式后，复制公式到下一行，用查找替换将所有公式中的“1”改为“2”，马上可以完成二中（代码为2）各项数据的统计，以此类推，统计其它学校的数据。

提示：SUMPRODUCT函数中指定的条件最少写两个，不够两个时用1代替另一个条件。即SUMPRODUCT（（学校="1"）*1）表示求一中人数。

四、统计上线人数

切换到学校工作表，如图4所示，统计各校上线人数，在C5单元格中输入公式“=SUMPRODUCT（（学校="1"）*（总分>=上线分！$B$2））”求出总分上重点线的人数，其中“上线分！$B$2”表示引用上线分工作表中B2单元格的数据，B2单元格存放着总分的重点分数线。注意这里我们用“上线分！$B$2”而不是直接写一个具体数字，是为了将来再次考试统计时方便，因为每次考试的重点线都不一样，再次统计时只需要修改上线分表中B2单元格的数据即可，不用再修改C5单元格的公式。同理，在D5单元格输入公式“=SUMPRODUCT（（学校="1"）*（总分>=上线分！$C$2））”，上线分！$C$2单元格存放着总分的本科分数线。E5单元格（本率即本科人数占总人数比率）输入公式“=D5/$B5*100”。G5单元格（存放总分的平均分）输入公式“=SUMPRODUCT（（学校="1"）*总分）/SUMPRODUCT（（学校="1"）*（总分<>0））”。其中SUMPRODUCT（（学校="1"）*总分）求出一中所有学生总分之和，SUMPRODUCT（（学校="1"）*（总分<>0））求出一中的实考人数，两者相除求出一中平均分。要统计语文等学科的重点人数，本科人数等数据时，写入类似公式即可。写完一中对应的公式后，复制公式到下一行，用查找替换将所有公式中的“1”改为“2”，马上可以完成二中学校各项数据的统计，以此类推，统计其它学校的数据。

提示：可以将函数某个条件直接写成字段名，表示对符合条件的单元格数据进行合计，比sumif函数更灵活。即 SUMPRODUCT（（学校="1"）*总分）即表示求一中所有学生总分之和。

五、统计班级数据

切换到班级工作表，如图5所示，需求出每个班级上线人数。在D5单元格输入公式“=SUMPRODUCT（（班级= B5）*（总分<>0））”求出1101班级实考人数。在E5单元格中输入公式“=SUMPRODUCT（（班级= B5）*（总分>=上线分！$B$2））”求出1101班级总分上重点线的人数。在I5单元格中输入公式“=SUMPRODUCT（（班级=B5）*总分）/SUMPRODUCT（（班级= B5）*（总分<>0））”求出1101班级总分的平均分。其它学科的统计公式与总分类似。1101班右边的公式编写好后，用复制再查找替换的方法编写出每个班级的计算公式。注意“班级”一列格式设为文本。

到此为止，所有公式编写完毕，这么多公式第一次编写确实很累，但却是一劳永逸。因为每次考试统计的项目都是一样的，以后你只需把成绩表和上线分表中的数据替换一下，马上可完成所有数据的统计。