SUMPRODUCT实现多条件排名-5页文档资料.doc

《SUMPRODUCT实现多条件排名-5页文档资料.doc》由会员分享，可在线阅读，更多相关《SUMPRODUCT实现多条件排名-5页文档资料.doc（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、如有侵权，请联系网站删除，仅供学习与交流SUMPRODUCT实现多条件排名【精品文档】第 5 页Excel用SUMPRODUCT实现多条件排名 (2012-12-27 17:37:24)转载标签：excelsumproduct函数多条件排名杂谈工作中经常要同时做国内排名、省内排名、市内排名。其实就是要在多个条件共同限定的一个小范围内进行排名。用SUMPRODUCT函数即可快速实现。原文地址 前些日子市里搞了一次模拟考试，下发了汇总后的成绩表。全市三所学校各个专业的学生成绩都放到了一个工作表中，格式如图1所示。为了做好成绩分析，主任要求做好两个排名：一是排出每位学生在全市相同专业的学生中的名次；

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

3、中“定义名称”按钮，在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称“zongfen”。此时，对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”，如图2所示。（图2）按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032，分别为它们指定名称“xuexiao”和“zhuanye”。完成后，这准备工作就算是结束了。二、排定名次：在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式“=SUMPRODUCT(zhuanye=$J2)*($H2zongfen)+1”，按下回车键，结果出来了吧？

4、向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。在L1单元格输入标题“校内专业排名”。点击L2单元格，输入公式“=SUMPRODUCT(zhuanye=$J2)*($H2zongfen)*(xuexiao=$I2)+1”，按回车键。并拖动填充句柄至最后一行，行了，所有排名工作就此宣告结束。不到十分钟吧？最后结果如图3所示。（图3）如果您也遇到类似的问题，比如平行班的成绩汇总在一张工作表中，而我们又需要学生的班内名次，那么不妨照此办理一回，呵呵，那效果，真的是谁用谁知道啊。说明：这里我们使用的是SUMPRODUCT函数的多条件计数功能。语法：SUMPRODUCT(条件1)*(条件2)*

5、(条件3)*(条件n)作用：统计同时满足条件1、条件2到条件n的记录的个数。SUMPRODUCT函数的具体介绍请参见下文：SUMPRODUCT函数介绍SUMPRODUCT函数在Excel中，用途极其广泛、统计功能何其强大。下面，就给您详细介绍SUMPRODUCT函数的使用吧。一、SUMPRODUCT函数语法及功能SUMPRODUCT函数功能：计算工作表内多列中对应值相乘之后的和，即乘积之和。其语法为：SUMPRODUCT(array1,array2,array3,)其中，Array1, array2, array3, 为2到30个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，

6、否则，函数SUMPRODUCT将返回错误值#VALUE!。 该函数将非数值型的数组元素作为0处理。二、SUMPRODUCT函数乘积之和举例表如下：ABCD（列号）121020（第2行）343040（第3行）565060（第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)作用

7、：统计同时满足条件1、条件2到条件n的记录的个数。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)，公式解释：统计性别为男且职称为中级职称的职工的人数、使用SUMPRODUCT进行多条件求和语法：SUMPRODUCT(条件1)*(条件2)* (条件3) *(条件n)*某区域)作用：汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*C2:C10)公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设C列为工资）SUMPRODUCT(条件1*条件2*条件3.条件N)利用此函数进行多条件计数时，*：满足所有条件；SUMPRODUCT(条件1+条件2+条件3.+条件N) +：满足任一条件例子一：=SUMPRODUCT(G1:G3=男)*(E1:E3=60)*1,(B2:B13=男)*1)例子三：多条件求和汇总三班籍贯为浙男性人数，=SUMPRODUCT(B2:B13=男)*1,(D2:D13=三班)*1,(E2:E13=浙)*1)大体就如此，如果您想学习具体的例子，请看如下的几个例子：