Excel: Obtendo os cinco principais valores de uma linha em uma lista separada por vírgulas

Question

+-----+-----+-----+-----+-----+-----+-----+-----+
|     | A   | B   | C   | D   | E   | F   | G   |
+-----+-----+-----+-----+-----+-----+-----+-----+
| 1   | 5   | 6   | 5   | 9   | 4   | 8   | 3   |
+-----+-----+-----+-----+-----+-----+-----+-----+

Considerando a folha acima. Eu estou procurando um caminho no Excel / Calc para obter os 5 primeiros números.

A única maneira que posso pensar em fazê-lo até agora é usar 5 funções LARGE diferentes

=LARGE(A1:G1, 1)
=LARGE(A1:G1, 2)
=LARGE(A1:G1, 3)
=LARGE(A1:G1, 4)
=LARGE(A1:G1, 5)

Isso me daria os cinco números mais altos. Eu queria saber se havia uma maneira de fazer isso com uma única fórmula que retornaria:

9, 8, 6, 5, 5

em uma única célula?

Outra questão, em vez de imprimi-las, também gostaria de somar os números em uma célula diferente.

    

Answer 1

Você pode usar TEXTJOIN () como uma fórmula de matriz.

=TEXTJOIN(",",TRUE,LARGE(A1:G1,ROW($1:$5)))

O ROW($1:$5) quando inserido como uma matriz retorna a matriz {1,2,3,4,5} retornando os cinco maiores números maiores para o TEXTJOIN.

Você força o Array confirmando a fórmula com Ctrl-Shift-Enter em vez de Enter ao sair do modo Editar.

O TEXTJOIN está disponível no Office 365 Excel.

Para obter a soma, podemos usar o mesmo LARGE dentro de um SUMPRODUCT:

=SUMPRODUCT(LARGE(A1:G1,ROW($1:$5)))

Paraversõesmaisantigas,vocêficacom:

=LARGE(A1:G1,1)&"," & LARGE(A1:G1, 2) & "," & LARGE(A1:G1, 3) & "," & LARGE(A1:G1, 4) & "," & LARGE(A1:G1, 5) 

Ou células auxiliares.