A classificação de dados integrada do Excel é incrível, mas não é dinâmica. Se você classificar os dados e depois adicionar dados a eles, precisará classificá-los novamente.
Classificar dados em ordem alfabética
Neste post, vou mostrar várias maneiras de classificar os dados em ordem alfabética usando fórmulas. Isso significa que você pode adicionar dados e ele os classificará automaticamente para você.
Quando os dados são todos texto sem duplicatas
Suponha que você tenha os dados mostrados abaixo:
Neste exemplo, todos os dados estão em formato de texto (sem números, espaços em branco ou duplicados). Para classificar isso, usarei uma coluna auxiliar. Na coluna ao lado dos dados, use a seguinte fórmula CONT.SE:
= CONT.SE ($ A $ 2: $ A $ 9, "<=" & A2)
Esta fórmula compara um valor de texto com todos os outros valores de texto e retorna sua classificação relativa. Por exemplo, na célula B2, ele retorna 8, pois há 8 valores de texto que são menores ou iguais ao texto ‘US’ (ordem alfabética).
Agora, para classificar os valores, use a seguinte combinação das funções INDEX, MATCH e ROWS:
= ÍNDICE ($ A $ 2: $ A $ 9, CORRESPONDÊNCIA (LINHAS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Esta fórmula simplesmente extrai os nomes em ordem alfabética. Na primeira célula (C2), procura o nome do país que possui o número mais baixo (Austrália tem 1). Na segunda célula, ele retorna Canadá (que tem o número 2) e assim por diante …
Alérgico a colunas auxiliares ??
Aqui está uma fórmula que fará o mesmo sem a coluna auxiliar.
= ÍNDICE ($ A $ 2: $ A $ 9, CORRESPONDÊNCIA (LINHAS ($ A $ 2: A2), CONTAR ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Esta é uma fórmula de matriz, então use Control + Shift + Enter em vez de Enter.
Vou deixar para você descodificar.
Experimente você mesmo … Baixe o arquivo de exemplo
Esta fórmula funciona bem se você tiver texto ou valores alfanuméricos.
Mas falha miseravelmente se:
- Você tem dados duplicados (tente colocar US duas vezes).
- Existem espaços em branco nos dados.
- Você tem uma combinação de números e texto (tente colocar 123 em uma das células).
Quando os dados são uma mistura de números, texto, duplicados e espaços em branco
Agora, este é um pouco complicado. Usarei 4 colunas auxiliares para mostrar como funciona (e, em seguida, forneceremos uma fórmula enorme que fará isso sem as colunas auxiliares). Suponha que você tenha os dados mostrados abaixo:
Você pode ver que há valores duplicados, em branco e números. Portanto, usarei colunas auxiliares para tratar de cada um desses problemas.
Coluna auxiliar 1
Insira a seguinte fórmula CONT.SE na coluna auxiliar 1
= CONT.SE ($ A $ 2: $ A $ 9, "<=" & A2)
Esta fórmula faz o seguinte:
- Ele retorna 0 para espaços em branco.
- No caso de duplicatas, ele retorna o mesmo número.
- Texto e números são processados paralelamente e esta fórmula retorna o mesmo número para texto e número (por exemplo, 123 e Índia recebem 1).
Coluna auxiliar 2
Insira a seguinte função IS na coluna auxiliar 2:
= - ISNUMBER (A2)
Coluna Auxiliar 3
Insira a seguinte fórmula na coluna auxiliar 3:
= - ISBLANK (A2)
Coluna Auxiliar 4
Insira a seguinte fórmula na coluna auxiliar 4
= SE (ISNUMBER (A2), B2, SE (ISBLANK (A2), B2, B2 + $ C $ 10)) + $ D $ 10
A ideia dessa fórmula é separar espaços em branco, números e valores de texto.
- Se a célula estiver em branco, ele retorna o valor na célula B2 (que sempre seria 0) e adiciona o valor na célula D10. Em poucas palavras, ele retornará o número total de células em branco nos dados
- Se a célula for um valor numérico, ela retornará a classificação comparativa e adicionará o número total de espaços em branco. Por exemplo, para 123, ele retorna 2 (1 é a classificação de 123 nos dados e há 1 célula em branco)
- Se for texto, ele retorna a classificação comparativa e adiciona o número total de valores numéricos e espaços em branco. Por exemplo, para a Índia, adiciona a classificação comparativa do texto no texto (que é 1) e adiciona o número de células em branco e o número de valores numéricos.
Resultado final - dados classificados
Agora usaremos essas colunas auxiliares para obter a lista classificada. Aqui está a fórmula:
= IFERROR (ÍNDICE ($ A $ 2: $ A $ 9, CORRESPONDÊNCIA (PEQUENO ($ E $ 2: $ E $ 9, LINHAS ($ F $ 2: F2) + $ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Este método de classificação agora se torna infalível. Eu mostrei o método para 8 itens, mas você pode estendê-lo para quantos itens desejar.
Experimente você mesmo … Baixe o arquivo de exemplo
Uma fórmula para classificar tudo (sem colunas auxiliares)
Se você pode lidar com fórmulas extremas, aqui está uma fórmula multifuncional que classificará os dados em ordem alfabética (sem nenhuma coluna auxiliar).
Aqui está a fórmula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, CORRESPONDÊNCIA (PEQUENO (NÃO ($ A $ 2: $ A $ 9 = "") * IF (ISNUMBER ($ A $ 2: $ A $ 9), CONTAR ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), LINHAS ($ A $ 2: A2) + SOMA (- ISBLANK ($ A $ 2: $ A $ 9))), NÃO ($ A $ 2: $ A $ 9 = "") * SE (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Insira esta fórmula em uma célula e arraste-a para baixo para obter a lista classificada. Além disso, como esta é uma fórmula de matriz, use Control + Shift + Enter em vez de Enter.
Esta fórmula tem utilidade no mundo real. O que você acha? Eu adoraria aprender com você. Deixe suas pegadas na seção de comentários!
