Conteúdo
Se você precisar classificar a lista, há muitas maneiras à sua disposição, a mais fácil delas são os botões de classificação na guia ou no menu Data (Dados - Classificar). No entanto, existem situações em que a ordenação da lista precisa ser feita automaticamente, ou seja, fórmulas. Isso pode ser necessário, por exemplo, ao gerar dados para uma lista suspensa, ao calcular dados para gráficos etc. Como classificar uma lista com uma fórmula em tempo real?
Método 1. Dados numéricos
Se a lista contiver apenas informações numéricas, a classificação pode ser feita facilmente usando as funções MENOS (PEQUENA) и LINHA (FILEIRA):
função MENOS (PEQUENA) retira da matriz (coluna A) o n-ésimo menor elemento em uma linha. Aqueles. SMALL(A:A;1) é o menor número na coluna, SMALL(A:A;2) é o segundo menor e assim por diante.
função LINHA (FILEIRA) retorna o número da linha para a célula especificada, ou seja, ROW(A1)=1, ROW(A2)=2 etc. Neste caso, é usado simplesmente como gerador de uma sequência de números n=1,2,3… nossa lista ordenada. Com o mesmo sucesso, foi possível fazer uma coluna adicional, preenchê-la manualmente com a sequência numérica 1,2,3… e referir-se a ela ao invés da função ROW.
Método 2. Lista de texto e fórmulas regulares
Se a lista não contiver números, mas texto, a função SMALL não funcionará mais, então você terá que seguir um caminho diferente, um pouco mais longo.
Primeiro, vamos adicionar uma coluna de serviço com uma fórmula onde o número de série de cada nome na futura lista ordenada será calculado usando a função COUNTIF (CONT.SE):
Na versão em inglês será:
=CONT.SE(A:A,»<"&A1)+CONT.SE($A$1:A1,"="&A1)
O primeiro termo é uma função para contar o número de células que são menores que a atual. A segunda é uma rede de segurança caso qualquer nome ocorra mais de uma vez. Então eles não terão os mesmos, mas números sucessivamente crescentes.
Agora os números recebidos devem ser organizados sequencialmente em ordem crescente. Para isso você pode usar a função MENOS (PEQUENA) da primeira via:
Bem, finalmente, resta apenas retirar os nomes da lista por seus números. Para fazer isso, você pode usar a seguinte fórmula:
função MAIS EXPOSTOS (COMBINE) procura na coluna B o número de série desejado (1, 2, 3, etc.) e, de fato, retorna o número da linha onde este número está localizado. Função ÍNDICE (ÍNDICE) retira da coluna A o nome neste número de linha.
Método 3: Fórmula de matriz
Este método é, de fato, o mesmo algoritmo de posicionamento do Método-2, mas implementado por uma fórmula de matriz. Para simplificar a fórmula, o intervalo de células C1:C10 recebeu o nome Lista (selecione as células, pressione Ctrl + F3 e o botão Crie):
Na célula E1, copie nossa fórmula:
=ÍNDICE(Lista; CORRESP(PEQUENO(CONT.SE(Lista; “<"&Lista); LINHA(1:1)); CONT.SE(Lista; "<"&Lista); 0))
Ou na versão em inglês:
=ÍNDICE(Lista, CORRESP(PEQUENO(CONT.SE(Lista, «<"&Lista), LINHA(1:1)), CONT.SE(Lista, "<"&Lista), 0))
e empurre Ctrl + Shift + Enterpara inseri-lo como uma fórmula de matriz. Em seguida, a fórmula resultante pode ser copiada em todo o comprimento da lista.
Se você quiser que a fórmula leve em conta não um intervalo fixo, mas possa ajustar ao adicionar novos elementos à lista, precisará alterar um pouco a estratégia.
Primeiro, o intervalo da lista precisará ser definido dinamicamente. Para fazer isso, ao criar, você precisa especificar não um intervalo fixo C3:C10, mas uma fórmula especial que fará referência a todos os valores disponíveis, independentemente de seu número. Clique Alt + F3 ou abra a aba Fórmulas - Gerenciador de Nomes (Fórmulas — Gerenciador de Nomes), crie um novo nome e no campo Ligação (Referência) digite a seguinte fórmula (presumo que o intervalo de dados a ser classificado começa na célula C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=DESVIO(C1,0,0,SCHÖTZ(C1:C1000),1)
Em segundo lugar, a fórmula de matriz acima precisará ser esticada com uma margem - com a expectativa de dados adicionais inseridos no futuro. Nesse caso, a fórmula de matriz começará a dar um erro #NUMBER nas células que ainda não foram preenchidas. Para interceptá-lo, você pode usar a função SE ERRO, que precisa ser adicionado “ao redor” de nossa fórmula de matriz:
=SE ERRO(ÍNDICE(Lista; CORRESP(PEQUENO(CONT.SE(Lista; “<"&Lista); LINHA(1:1)); CONT.SE(Lista; "<"&Lista); 0));»»)
=SEERRO(NDEX(Lista, CORRESP(PEQUENO(CONT.SE(Lista, «<"&Lista), LINHA(1:1)), CONT.SE(Lista, "<"&Lista), 0));"")
Ele captura o erro #NUMBER e gera um void (aspas vazias).
:
- Classificar intervalo por cor
- O que são fórmulas de matriz e por que elas são necessárias
- Classificação SORT e matrizes dinâmicas no novo Office 365