Ordenar por fórmula (sem VBA)

Question

Eu estou tentando classificar uma coluna curta de valores (decrescente) usando uma fórmula em uma coluna diferente. Então, se A1 através de A10 contiver números, em B1 eu digite:

=LARGE($A$1:$A$10,ROWS($1:1))

e copie:

Comovocêpodever,essaéafórmulaequivalenteacopiaracolunaAparaacolunaBedepoisclassificarBusandoClassificarnafaixadeopções.

SeosvaloresnacolunaAforemTexto,possofazeromesmousando:

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&$A$1:$A$10)),$A$1:$A$10)

e copiando:

Meu problema é criar uma fórmula que manipule texto e números. Aqui está um exemplo de dados em A e os resultados esperados em B :

10      zeta
alpha   gamma
zeta    beta
alpha   alpha
2       alpha
beta    10
1       4
gamma   4
4       2
4       1

Isso é fácil com VBA , mas eu preciso de uma solução apenas com fórmulas de planilha.

    

Answer 1

Isso requer que os resultados comecem pelo menos na segunda linha, apenas porque eu preciso de uma linha acima para a função COUNT ().

Também é uma fórmula de matriz:

=IF(LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10)<>0,LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10),LARGE(IF(ISNUMBER($A$1:$A$10),$A$1:$A$10),COUNT($C$1:$C1)+1))

Sendo um Array, use Ctrl-Shift-Enter ao sair do modo de edição. Se feito corretamente, o excel colocará {} em torno da fórmula

Como funciona:

Pegamos a fórmula para classificar o texto e o fizemos encontrar somente os dados não numéricos LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$1:$A$10,">="&IF(NOT(ISNUMBER($A$1:$A$10)),$A$1:$A$10))),$A$1:$A$10) Isso é feito no COUNTIF com a instrução IF ().

Isto retornará 0 após ordenar todo o número não numérico, então, quando for igual a 0, usaremos a função LARGE () somente nos dados numéricos. %código%.

Como COUNT () só contará o número, usamos isso como o nosso k no LARGE (). Contamos todos os números acima da célula atual e adicionamos 1 para obter o k adequado.

    

Answer 2

Você pode adicionar uma coluna de ajuda, que modifica os valores numéricos da coluna A?

Se você usar a fórmula

=TEXT(A1,"'0")

então sua segunda fórmula ligeiramente modificada funcionará

=LOOKUP(1,0/FREQUENCY(ROWS($1:4),COUNTIF($B$1:$B$10,">="&$B$1:$B$10)),$A$1:$A$10)