销贷比公式3-Excel用SUMPRODUCT实现多条件排名

2020年9月24日发(作者：曲振侔)

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

（图1）
这个工作以前也做过，每次都得将数据按专业、按学校分别筛选出来复制到
不同的工作表中，然后在不同的工作表中用RANK函数进行排序。全市三所学校
一千多个学生，每所学 校都有七到八个专业，所以这个筛选复制工作也是费时费
力，筛选复制完成后还要在十多个工作表中进行 排名工作，非常麻烦。不过这一
次，工作完成得却异常顺利，只需要十分钟就可以完成全部的工作了。因 为，这
次我们使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程
如 下：
一、准备工作：
选定总分所在的H2:H1032单元格区域，点击功能区“公式”选 项卡“定义
的名称”功能组中“定义名称”按钮，在弹出的“新建名称”对话框“名称”输
入框 中输入为此区域定义的名称“zongfen”。此时，对话框下方的“引用位置”
后的输入框中已经自 动输入我们选定的单元格区域“=对口!$$H$$2:$$H$$1032”，如
图2所示。


（图2）
按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单 元格区域
J2:J1032，分别为它们指定名称“xuexiao”和“zhuanye”。
完成后，这准备工作就算是结束了。
二、排定名次：
在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式:
“=SUMPRODU CT((zhuanye=$$J2)*($$H2了 吧？向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。
在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式:
“=SUMPRODU CT((zhuanye=$$J2)*($$H2了 吧？向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。

（图3）
如果您也遇到类似的问题，比如平行班的成绩汇总在一张工作表中，而我们
又需要学生的班内名次，那么 不妨照此办理一回，呵呵，那效果，真的是谁用谁
知道啊。
说明：
这里我们使用的是SUMPRODUCT函数的多条件计数功能。
语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
作用：统计同时满足条件1、条件2到条件n的记录的个数。
SUMPRODUCT函数的具体介绍请参见下文。


SUMPRODUCT函数介绍
http:
SUMPRODUCT函数在Excel中，用途极其广泛、统计功能何其强大。
下面，就给您详细介绍SUMPRODUCT函数的使用吧。
一、SUMPRODUCT函数语法及功能
SUMPRODUCT函数功能：计算工作表内多列中对应值相乘之后的和，即乘
积之和。
其语法为：
SUMPRODUCT(array1,array2,array3, …)
其中，Array1, array2, array3, … 为 2 到 30 个数组，其相应元素需要进行
相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回
错误值 #VALUE!。 该函数将非数值型的数组元素作为 0 处理。
二、SUMPRODUCT函数乘积之和举例
表如下：








A B C D （列号）
1 2 10 20 （第2行）
3 4 30 40 （第3行）
5 6 50 60 （第4行）
公式： =SUMPRODUCT(A2:B4, C2:D4)，说明：两个数组的所有元素对应相乘，
然后把乘积相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60(结果为910)
三、多条件求和+求个数
Ａ、使用SUMPRODUCT进行多条件计数 ，计数就是统计满足条件的个数。
语法：＝SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))
作用：统计同时满足条件1、条件2到条件n的记录的个数。
实例：=SUMPRODUCT ((A2:A10=男中级职称，公式解释：统
计性别为男且职称为中级职称的职工的人数
Ｂ、使用SUMPRODUCT进行多条件求和
语法：＝SUMPRODUCT((条件1)*(条件2)* (条件3) *…(条件n)*某区域)
作用：汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。
实例： =SUMPRODUCT((A2:A10=男中级职称
公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设C
列为工资）
SUMPRODUCT(条件1*条件2*条件3...条件N)利用此函数进行多条件计数时，
* ：满足所有条件；
SUMPRODUCT(条件1+条件2+条件3...+条件N) + ：满足任一条件
例子一：
=SUMPRODUCT((G1:G3=男
这个公式的意思就是要这样来统计，G1:G3满足条件是男的，同时E1:E3的

数值小于等于60，满足这两个条件的总人数。 首先这是一个数组公式，要按
Ctrl+Shift+Enter结束。
例子二：多条件求和
求男性及格人数：=SUMPRODUCT((C2:C13>=60) *1,(B2:B13=男
例子三：多条件求和
汇总三班籍贯为浙男性人数，=SUMPRODUCT((B2:B13=男三班
浙
大体就如此，如果您想学习具体的例子，请看如下的几个例子：
http:
http:
http: