Criando uma classificação no Excel, combinando valores em células não contíguas

Question

Eu tenho um arquivo do excel que se parece com isso:

   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

em que cada grupo de 5 colunas relata alguns dados relativos a uma determinada pessoa.

Eu quero construir cinco classificações com base nos valores nas cinco colunas de cada grupo. Por exemplo, a classificação das colunas "A, F, K" deve ser "Bob, Charles, Alice" (porque o valor em A de Bob é 71, o valor em A de Charles é 13 e o valor em A de Alice é 10). Da mesma forma, a classificação de "B, G, L" seria "Alice, Charles, Bob" ou "Charles, Alice, Bob" (porque existe um empate: 35, 35, 15).

Suponho que devo usar uma mistura de INDEX / MATCH, (V) LOOKUP e LARGE, mas realmente não sei por onde começar. O mais longe que eu tenho é algo ao longo das linhas de

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

Isso (deve) produzir o primeiro, segundo e terceiro maior valor para o intervalo "A2, F2, K2", mas não sei como chegar ao nome da pessoa relacionada a esse valor a partir daí. Eu tenho alguns problemas generalizando os exemplos que encontrei com as funções INDEX / MATCH e lookup para essa estrutura de dados atípica (em grupos de cinco colunas).

EDIT: Os nomes (Alice, Bob, Charles) estão em células mescladas.

    

Answer 1

Você pode escrever os dados de maneira diferente para usar o Rank:

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

E você pode arrastar cada fórmula na mesma coluna

    

Answer 2

Estou sugerindo uma solução, mas só é adequado se esses forem os únicos dados na sua planilha. Se você tiver linhas repetidas abaixo em linhas semelhantes nas linhas 1 e amp; 2 então isso se tornará uma solução ineficiente e talvez você precise optar pelo VBA provavelmente.

Veja a imagem abaixo.

A matriz está alinhada no intervalo G7: J12.

Fórmula em 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))

Agora arraste as respectivas fórmulas para a direita e faça uma pequena modificação na função LARGE. Para a segunda coluna, isto é Col I coloco o segundo parâmetro como 2 para ambos os LARGE e para a terceira coluna, ou seja, Col J, coloque 3.

Honestamente, descarte essa solução se você tiver linhas repetidas abaixo e precisar fazer isso para cada conjunto ou linhas contidas nela.