Excel创建多重条件排名 [英] Excel creating multiply conditions ranking

问题描述

我想在这个图表中作出有条件的排名：

I am trying to make a conditional ranking in this chart:

在右边你可以从匹配1,2,3中查看总分和排名。（sum）
在 A 和 C 我有一个领带得分（3分，都排在第二名），我想添加第二和第三个条件来确定最终排名。
如果得分（两者都是排名相同），那么要确定谁是第二名，谁是第三名，我想使用 XX的列第一。更高的价值将赢得第二名，在一个情况下仍然有一个关系（两者都是0的相同值），它将继续并检查 X的价值列，由于行 C = 27和行 A = 21，行 C 将被排名第二，线 A 将被排名第三。
如果还有一个关系，还要添加一个继续3条件的选项。

On the right you can see the total score and ranking from the Match 1,2,3 (sum) on line A and C I have a tie score (3 points and both ranked at 2nd place), I'd like to to add a 2nd and 3rd conditions to determinate the final ranking. If there is a tie in score (both are in same ranking), then to determine who is 2nd and who is 3rd place I want to use the values from the XX's column first. The higher value will win the 2nd place and in a case there is still a tie (as both are the same value of 0), it will continue and check the value on the X's column and since line C = 27 and line A = 21, line C will be ranked as 2nd place and line A will be ranked as 3rd place. Also will like to add an option to continue with 3 condition if there is still a tie.

我正在使用一个简单的排名公式 = RANK（AK7，$ AK $ 7：$ AK $ 10）确定排名。

I am using a simple ranks formula =RANK(AK7,$AK$7:$AK$10) to determine the rank.

推荐答案

非常简单的数学解决方案：与权重进行线性组合。
执行类似

Very simple 'math' solution: do a linear combination with weights. Do something like

Wscore = 1000*Score + 10*XXvalue + Xvalue

然后使用简单的排名函数排序值。

then sort values using simple rank function.

只需根据您的值分配，如果需要。

Just change weights according to your values distribution, if needed.

这篇关于Excel创建多重条件排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！