通过匹配非连续单元格中的值在 Excel 中创建排名

通过匹配非连续单元格中的值在 Excel 中创建排名

我有一个如下所示的 Excel 文件:

   A  B  C  D  E        F  G  H  I  J      K  L  M  N  O  
1  Alice                Bob                Charles
2  10 35 54 9 21        71 15 43 75 98     13 35 66 80 20

其中每组 5 列报告与特定人相关的一些数据。

我想根据每组 5 列的值建立 5 个排名。例如,“A、F、K”列的排名应该是“Bob、Charles、Alice”(因为 Bob 的 A 值是 71,Charles 的 A 值是 13,Alice 的 A 值是 10)。同样,“B、G、L”的排名要么是“Alice、Charles、Bob”,要么是“Charles、Alice、Bob”(因为存在平局:35、35、15)。

我想我应该混合使用 INDEX/MATCH、(V)LOOKUP 和 LARGE,但真的不知道从哪里开始。我得到的最多的东西是

LARGE((A2, F2, K2), 1)
LARGE((A2, F2, K2), 2)
LARGE((A2, F2, K2), 3)

这(应该)输出范围“A2、F2、K2”的第一、第二和第三大的值,但我不知道如何从那里找到与该值相关的人的名字。我在将使用 INDEX/MATCH 和查找函数找到的示例推广到这种非典型数据结构(以五列为一组)时遇到了一些麻烦。

编辑:名字(Alice、Bob、Charles)位于合并的单元格上。

答案1

您可以以不同的方式编写数据以使用等级:

在 E2=RANK($B2,$B2:$D2,0)
在 F2=RANK($C2,$B2:$D2,0)
在 G2=RANK($D2,$B2:$D2,0)

您可以将每个公式拖到同一列中

在此处输入图片描述

答案2

我建议一种解决方案,但是它只适用于这是您工作表中的唯一数据的情况。如果您在第 1 行和第 2 行的类似行上有重复行,那么这将成为一个低效的解决方案,您可能需要选择 VBA。

请参阅下面的屏幕截图。

矩阵在范围 G7:J12 中对齐。

H8 中的公式

=IF(CHOOSE(1,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$A$1,IF(CHOOSE(2,$A$2,$F$2,$K$2)=LARGE(($A$2,$F$2,$K$2),1),$F$1,$K$1))

H9

=IF(CHOOSE(1,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$A$1,IF(CHOOSE(2,$B$2,$G$2,$L$2)=LARGE(($B$2,$G$2,$L$2),1),$F$1,$K$1))

H10

=IF(CHOOSE(1,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$A$1,IF(CHOOSE(2,$C$2,$H$2,$M$2)=LARGE(($C$2,$H$2,$M$2),1),$F$1,$K$1))

H11

=IF(CHOOSE(1,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$A$1,IF(CHOOSE(2,$D$2,$I$2,$N$2)=LARGE(($D$2,$I$2,$N$2),1),$F$1,$K$1))

H12

=IF(CHOOSE(1,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$A$1,IF(CHOOSE(2,$E$2,$J$2,$O$2)=LARGE(($E$2,$J$2,$O$2),1),$F$1,$K$1))

现在将相应的公式拖到右侧,并对 LARGE 函数进行轻微修改。对于第二列,即 Col I,将其中 LARGE 的第二个参数设置为 2,对于第三列,即 Col J,将其设置为 3。

在此处输入图片描述

老实说,如果下面有重复的行,并且您需要对其中的每一组或每行都执行此操作,请放弃此解决方案。

相关内容