Excel用SUMPRODUCT实现多条件排名.docx

Excel用SUMPRODUCT实现多条件排名
http://publish./2007/0919/
前些日子市里搞了一次模拟考试,下发了汇总后的成绩表。全市三所学校各个专业的学生成绩都放到了一个工作表中,格式如图1所示。为了做好成绩分析,主任要求做好两个排名:一是排出每位学生在全市相同专业的学生中的名次;二是排出每位学生在本校本专业中的名次;两个排名都以总分为依据。
(图1)
这个工作以前也做过,每次都得将数据按专业、按学校分别筛选出来复制到不同的工作表中,然后在不同的工作表中用RANK函数进行排序。全市三所学校一千多个学生,每所学校都有七到八个专业,所以这个筛选复制工作也是费时费力,筛选复制完成后还要在十多个工作表中进行排名工作,非常麻烦。不过这一次,工作完成得却异常顺利,只需要十分钟就可以完成全部的工作了。因为,这次我们使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程如下:
准备工作:
选定总分所在的H2:H1032单元格区域,点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮,在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称“zongfen”。此时,对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”,如图2所示。
(图2)
按同样的方法,选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032,分别为它们指定名称“xuexiao”和“zhuanye”。
完成后,这准备工作就算是结束了。
排定名次:
在K1单元格输入标题“按专业排名”。点击K2单元格,输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen))+1”,按下回车键,结果出来了吧?向下拖动其填充句柄至最后一行,OK,按专业排名就算完成了。
在K1单元格输入标题“按专业排名”。点击K2单元格,输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen))+1”,按下回车键,结果出来了吧?向下拖动其填充句柄至最后一行,OK,按专业排名就算完成了。
(图3)
如果您也遇到类似的问题,比如平行班的成绩汇总在一张工作表中,而我们又需要学生的班内名次,那么不妨照此办理一回,呵呵,那效果,真的是谁用谁知道啊。
说明:
这里我们使用的是SUMPRODUCT函数的多条件计数功能。
语法:=SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
作用:统计同时满足条件1、条件2到条件n的记录的个数。
SUMPRODUCT函数的具体介绍请参见下文。
SUMPRODUCT函数介绍

SUMPRODUCT函数在Excel中,用途极其广泛、统计功能何其强大。
下面,就给您详细介绍SUMPRODUCT函数的使用吧。
一、SUMPRODUCT函数语法及功能
SUMPRODUCT函数功能:计算工作表内多列中对应值相乘之后的和,即乘积之和。
其语法为:
SUMPRODUCT(array1,array2,array3, …)
其中,Array1, array2, array3, …为 2 到 30 个数组