我有一个如下所示的 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
答案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。
老实说,如果下面有重复的行,并且您需要对其中的每一组或每行都执行此操作,请放弃此解决方案。