您的位置: 首页 > EXCEL技巧 > Excel函数 >

Excel中的“多关键字”排名公式

时间:2013-12-14 整理:docExcel.net

在Excel中对某列进行排名时通常使用RANK函数,但有时排名需要考虑多列的情况,例如在对B列排名时,如果B列有相同的数值,则按C列排名。通常,要获得这样的多列排名结果,可以在“排序”对话框中依次选择或添加“主要关键字”和“次要关键字”,然后进行排序即可。但在不改变表格结构的情况下,要获得“多关键字”的排名,可以用下面的一些公式。

例如下图为某电视节目在播出日期内的收视率和收视份额,需要用公式返该日期内按收视率的降序排名,在收视率的排名相同时,则按收视份额降序排名。其中D列为直接使用RANK函数对B列排名的结果,可以看到,B列数值相同时,其排名也是相同的,不符合要求。

按多列排名图例

假如数据在A1:E22区域,在E2中输入公式:

=RANK(B2,B$2:B$22)+SUMPRODUCT(N((B$2:B$22=B2)*(C$2:C$22>C2)))

然后向下填充公式即可得到先按B列后按C列降序的排名结果。

上述公式先用RANK函数获得初步的排名,对于B列数值相同的情况,用SUMPRODUCT函数得到这些相同值对应C列数值的“排名”,最后返回所需结果。也可使用下面的两个公式:

   =SUMPRODUCT(N((B2*1000+C2)<(B$2:B$22*1000+C$2:C$22)))+1

该公式将B列的数值乘以一个相对同行C列数值较大的数,如本例为“1000”,再加上同行C列的值,最后比较合计值得到排名。类似地也可以用C列除以一个较大数值:

=SUMPRODUCT(N((B2+C2/1000)<(B$2:B$22+C$2:C$22/1000)))+1

另外,如果用于排名的数值都为非负整数,可用“&”连接各列数值,再用SUMPRODUCT函数排名。如下图A1:E21区域为某公司年终考核的数据,要求先按总分列降序排名,如果总分相同,则依次按“业务能力”和“工作态度”列降序排名。

多“关键字”排名例二

在F2中输入公式:

=SUMPRODUCT(N(E2&B2&C2<(E$2:E$21&B$2:B$21&C$2:C$21)))+1

然后填充公式到F21即可。

我要把表格二的数据导入到表格一关键字对应的 问题:我要把表格二的数据导入到表格一键字对应的位置
回答:Vlookup函数匹配数据的时候需要单元格中的内容是完全匹配的,所以你上面的公式会报错,增加一下通配符应该能解决;您试一下这个公式=VLOOKUP(CONCATENATE("*",A2,"*&...
excel中排序应该用什么公式 问题:excel中排序应该用什么公式
回答:... 2.选中所有数据区域,执行“数据/排序”后,在“主要键字”栏选中要排序的列标题(或是用列表示)并选中“降序”后“确定”。 注意:排序过程中,被排序的数据所在行随数据所排顺序而整体变动。 筛选:如果要对某些...
区域中包含错误值的排名公式 问题:区域中包含错误值的排名公式
回答:...需要在包含错误的区域中获取各数值的排名,可用下面的公式,以上图为例对A列按降序排名,在B2单元格中输入公式: =IF(ISERROR(A2), ,COUNTIF($A$2:$A$16, > &A2)+1) 然后填充公式至B16单元格,如图。 按升序排名时,将“>”号...
在Excel中进行中国式排名 问题:在Excel中进行中国式排名
回答:...,可以先对表格按降序的方式排序,然后在辅助列中输入公式,获得中国式排名。 如下图A1:D19区域中是某公司年度考核成绩表,总成绩在D列中,现在要对D列进行中国式排名。 1.选择D2:D19列中的任一单元格,单击工具栏中的...
怎么样实现同一表格中不同行的相同数字 问题:怎么样实现同一表格中不同行的相同数字
回答:...百行,分散了,你此时再执行菜单中的,排序,以A列为键字,升降序无所谓这样就好对比了
在excel中怎么把两列数据对换位置以便使用vlook 问题:在excel中怎么把两列数据对换位置以便使用vlookup函数
回答:当键字所在列不在首列时,Vlookup()函数无法使用。但是,可以使用Match()和Offset()配合使用,起到与Vlookup()函数相同的作用。如图:公式:E4=OFFSET($A$1,MATCH(D4,$B$2:$B$8),0)
相关推荐: