前些日子市里搞了一次模拟考试,下发了汇总后的成绩表。全市三所学校各个专业的学生成绩都放到了一个工作表中,格式如图1所示。为了做好成绩分析,主任要求做好两个排名:一是排出每位学生在全市相同专业的学生中的名次;二是排出每位学生在本校本专业中的名次;两个排名都以总分为依据。

Excel用SUMPRODUCT实现有条件排名

  图1(点击看大图)

  这个工作以前也做过,每次都得将数据按专业、按学校分别筛选出来复制到不同的工作表中,然后在不同的工作表中用RANK函数进行排序。全市三所学校一千多个学生,每所学校都有七到八个专业,所以这个筛选复制工作也是费时费力,筛选复制完成后还要在十多个工作表中进行排名工作,非常麻烦。不过这一次,工作完成得却异常顺利,只需要十分钟就可以完成全部的工作了。因为,这次我们使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程如下:

  一、准备工作

  选定总分所在的H2:H1032单元格区域,点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮,在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称“zongfen”。此时,对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”,如图2所示。

Excel用SUMPRODUCT实现有条件排名

  图2

  按同样的方法,选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032,分别为它们指定名称“xuexiao”和“zhuanye”。

  完成后,这准备工作就算是结束了。

  二、排定名次

  在K1单元格输入标题“按专业排名”。点击K2单元格,输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

  在L1单元格输入标题“校内专业排名”。点击L2单元格,输入公式“=SUMPRODUCT((zhuanye=$J2)*($H2

Excel用SUMPRODUCT实现有条件排名

  图3(点击看大图)

  如果您也遇到类似的问题,比如平行班的成绩汇总在一张工作表中,而我们又需要学生的班内名次,那么不妨照此办理一回,呵呵,那效果,真的是谁用谁知道啊。a