Como ordenar valores no Excel ignorando os zeros?
Ao trabalhar com um conjunto de dados no Excel que contém números e zeros, a aplicação da função de ordenação padrão normalmente move todos os Valores Zero para o topo da lista ao ordenar por ordem crescente. Contudo, em muitas situações, poderá pretender ordenar os seus dados mantendo os Valores Zero na parte inferior, ignorando-os efetivamente durante o processo de ordenação. Por exemplo, ao analisar montantes de vendas, números de produção ou resultados de testes, poderá desejar focar-se nos resultados diferentes de zero e apenas rever as entradas nulas no final. Isto pode tornar os relatórios mais claros e significativos para análise posterior.
Ordenar valores ignorando zeros com uma coluna auxiliar
Ordenar Valores Ignorando Zeros com uma Única Coluna de Fórmula (Método Alternativo)
Ordenar Valores Ignorando Zeros com Código VBA (Solução Alternativa)
Ordenar valores ignorando zeros com uma coluna auxiliar
Para ordenar dados mantendo os valores zero na parte inferior, pode utilizar uma coluna auxiliar com uma fórmula específica. Esta abordagem é prática, compatível com todas as versões do Excel e ideal quando tem uma lista com zeros intercalados entre outros valores, permitindo agrupar os números diferentes de zero e posicionar os zeros no final.
1. Numa célula vazia adjacente aos seus valores (por exemplo, B2, ao lado dos dados originais na coluna A), introduza a seguinte fórmula:
=IF(A2=0,"",A2)Esta fórmula verifica o valor na célula A2: se for zero, devolve uma célula vazia («»); caso contrário, mantém o valor original. Utilize a alça de preenchimento para copiar a fórmula para todas as linhas relevantes dos seus dados. Dessa forma, a sua coluna auxiliar exibirá apenas os valores diferentes de zero, deixando as células com zero em branco. A imagem seguinte mostra como fica a coluna auxiliar após aplicar a fórmula:
Dica: Certifique-se de que está a referenciar a coluna e o intervalo de células corretos na sua fórmula, de acordo com a configuração real da sua folha de cálculo, para evitar erros.
2. Depois de criar a coluna auxiliar, selecione todas as células dessa coluna, aceda ao separador Dados e escolha Ordenar do menor para o maior. Na caixa de diálogo Aviso de Ordenação que aparece, certifique-se de selecionar Expandir a seleção, para que toda a linha seja ordenada em conjunto com os valores da coluna auxiliar.
Dica: Expandir a seleção é essencial; caso contrário, apenas a coluna auxiliar será ordenada, deixando os seus dados desalinhados. Certifique-se sempre de que está a ordenar todo o intervalo de dados.
3. Clique em Ordenar. Os dados serão agora organizados por ordem crescente, com todos os valores não nulos no topo e os zeros na parte inferior, tal como pretendido. Após a ordenação, pode eliminar ou ocultar a coluna auxiliar, caso já não seja necessária.
Nota: Se os seus dados incluírem fórmulas, copie e cole-os como valores antes de ordenar para evitar resultados inesperados.
Uma vantagem fundamental deste método é a sua simplicidade e compatibilidade com todas as versões do Excel. No entanto, exige a adição de uma coluna auxiliar, o que pode não ser ideal para utilizadores que pretendam evitar colunas extra. Além disso, se o seu conjunto de dados for atualizado frequentemente, terá de garantir que quaisquer novas linhas estejam incluídas no intervalo da fórmula da coluna auxiliar.
Resolução de problemas: Se os zeros não estiverem a mover-se para a parte inferior conforme esperado, verifique se a fórmula foi introduzida corretamente e copiada para todas as células relevantes, e confirme que a seleção foi expandida durante o processo de ordenação.
Dica. Por predefinição, o Excel coloca as células vazias no fim da ordenação. Se precisar que as células vazias (que representam os zeros originais) apareçam no topo, utilize a função Classificação Avançada do Kutools para Excel e selecione a opção Células em branco no início, conforme ilustrado na imagem seguinte. Esta funcionalidade permite cenários de ordenação mais flexíveis, como quando pretende destacar primeiro as entradas em falta ou nulas. A ferramenta oferece uma avaliação gratuita com todas as funcionalidades durante 30 dias.Transfira aqui para experimentar.
Ordenar Valores Ignorando Zeros com uma Única Coluna de Fórmula (Método Alternativo)
Este método revela-se especialmente útil quando pretende extrair e ordenar exclusivamente os valores diferentes de zero de uma lista, sem recorrer a passos adicionais nem às funções de ordenação incorporadas. A abordagem adapta-se perfeitamente a cenários em que necessita de criar, de forma independente, uma lista ordenada de valores não nulos, preservando intactos os dados originais.
1. Introduza a seguinte fórmula de matriz numa nova coluna (por exemplo, na célula C2):
=SMALL(IF($A$2:$A$11<>0,$A$2:$A$11),ROW(A1))2. Prima Ctrl+Shift+Enter (para Excel 2019 e versões anteriores) ou apenas Enter (para Excel 365/Excel 2021). Em seguida, arraste a alça de preenchimento até onde for necessário. Esta fórmula devolve os valores não nulos do Intervalo de Dados Original, ordenados na coluna A.
Explicação: IF($A$2:$A$110,$A$2:$A$11) filtra os valores não nulos, enquanto SMALL(...,ROW(A1)) devolve os resultados ordenados por ordem crescente. Ajuste o intervalo $A$2:$A$11 de acordo com os seus dados e certifique-se de que tem linhas suficientes na coluna C para incluir todos os valores não nulos; caso contrário, a fórmula apresentará erros nas células excedentárias.
Esta abordagem é eficiente e preserva a lista original intacta, apresentando os valores ordenados apenas numa nova localização.
Nota: A fórmula listará primeiro os valores únicos e só depois os ordenará.
Ordenar Valores Ignorando Zeros com Código VBA (Solução Alternativa)
Se se vir repetidamente a ordenar listas para ignorar zeros, automatizar com VBA pode Economizar Tempo. Esta solução é adequada para utilizadores familiarizados com macros e que precisem de gerir listas grandes ou frequentemente atualizadas. Contudo, deve sempre fazer uma cópia de segurança dos seus dados antes de executar qualquer script VBA.
1. Aceda a Ferramentas de Programador > Visual Basic para abrir a janela Microsoft Visual Basic para Aplicações. Clique em Inserir > Módulo e cole o seguinte código no módulo:
Sub SortIgnoreZeros()
Dim rng As Range
Dim lastRow As Long
Dim ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng = Application.InputBox("Select the range to sort", xTitleId, "", Type:=8)
lastRow = rng.Rows.Count
' Move nonzeros to top
rng.Sort Key1:=rng, Order1:=xlAscending, Header:=xlNo
End Sub2Após introduzir o código, prima 
para executar a macro. Aparecerá uma caixa de diálogo a solicitar que selecione o intervalo que pretende ordenar. O script VBA ordenará o intervalo selecionado por ordem crescente, colocando os zeros na parte inferior. Certifique-se de que seleciona apenas a coluna relevante com valores; caso contrário, as linhas poderão ficar desalinhadas.
Esta abordagem VBA é muito eficiente para tarefas repetitivas, mas use precaução com dados não guardados e verifique sempre a seleção antes de executar a macro.
Em resumo, é possível ordenar valores ignorando zeros no Excel através de vários métodos, cada um com vantagens distintas. A escolha do método ideal depende da frequência da tarefa, do tamanho dos seus dados e de pretender ou não manter os dados originais inalterados. Se encontrar resultados inesperados na ordenação, verifique sempre os intervalos das suas fórmulas, confirme as opções de expansão da ordenação e considere manter uma cópia de segurança da sua folha de cálculo. Explorar as funcionalidades incorporadas do Excel, fórmulas complementares, macros VBA ou as capacidades avançadas de ordenação oferecidas pelo Kutools pode ajudá-lo a encontrar o fluxo de trabalho mais adequado às suas necessidades.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos