Fórmula genérica
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Resumo
Para classificar dados dinamicamente com números e texto em ordem alfabética, você pode usar uma fórmula para gerar uma classificação numérica em uma coluna auxiliar e, em seguida, usar INDEX e MATCH para exibir valores com base na classificação. No exemplo mostrado, a fórmula em C5 é:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
onde "dados" é o intervalo nomeado B5: B13.
Explicação
Esta fórmula primeiro gera um valor de classificação usando uma expressão baseada em CONT.SE:
=COUNTIF(data,"<="&B5)
que é explicado com mais detalhes aqui. Se os dados contiverem todos os valores de texto ou todos os valores numéricos, a classificação estará correta. No entanto, se os dados incluírem texto e números, precisamos "mudar" a classificação de todos os valores de texto para contabilizar os valores numéricos. Isso é feito com a segunda parte da fórmula aqui:
+(COUNT(data)*ISTEXT(B7))
Aqui, usamos a função COUNT para obter uma contagem dos valores numéricos nos dados e, em seguida, multiplicamos o resultado pelo resultado lógico de ISTEXT, que testa se o valor é texto e retorna VERDADEIRO ou FALSO. Isso efetivamente cancela o resultado COUNT quando estamos trabalhando com um número na linha atual.
Tratamento de duplicatas
Se os dados contiverem duplicatas, a fórmula pode ser alterada conforme mostrado abaixo para atribuir uma classificação sequencial aos valores que aparecem mais de uma vez:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Esta versão ajusta a lógica da função CONT.SE inicial e adiciona outro CONT.SE com uma referência de expansão para incrementar duplicatas.
Exibir valores classificados
Para recuperar e exibir valores classificados em ordem alfabética usando o valor de classificação calculado, E5 contém a seguinte fórmula INDEX e MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
onde "dados" é o intervalo nomeado B5: B13 e "classificação" é o intervalo nomeado C5: C13.
Para obter mais informações sobre como essa fórmula funciona, consulte o exemplo aqui.
Lidando com espaços em branco
As células vazias irão gerar uma classificação zero. Supondo que você deseja ignorar células vazias, isso funciona bem porque a fórmula INDEX e MATCH acima começa em 1. No entanto, você verá erros # N / A no final dos valores classificados, um para cada célula vazia. Uma maneira fácil de lidar com isso é envolver a fórmula INDEX e MATCH em IFERROR assim:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
