Como classificar números facilmente no Excel, ignorando as células em branco?
Ao trabalhar com dados no Excel, é comum encontrar listas que contêm células em branco. Se utilizar funções padrão do Excel para classificação, como RANK ou RANK.EQ nestas listas, as células em branco normalmente originam erros ou resultados indesejados na classificação. Isso dificulta a interpretação dos seus dados — especialmente se pretender manter as células em branco vazias, sem valores de erro ou posições arbitrárias. Classificar números de forma eficiente, ignorando automaticamente as células em branco, melhora a clareza e a usabilidade dos seus resultados, tornando a sua folha de cálculo mais profissional e fácil de ler.
Neste artigo, encontra instruções passo a passo para utilizar fórmulas e macros VBA na realização desta tarefa. As soluções detalhadas incluem ainda explicações dos parâmetros, dicas práticas e sugestões para resolução de problemas, ajudando-o a evitar armadilhas comuns.
- Classificar valores ignorando células em branco por ordem crescente utilizando fórmulas
- Classificar valores ignorando células em branco por ordem decrescente utilizando uma fórmula
- Classificar valores ignorando células em branco utilizando VBA
Classificar valores ignorando células em branco por ordem crescente utilizando fórmulas
Em situações em que precisa atribuir classificações por ordem crescente, mas deseja ignorar células em branco, uma abordagem comum consiste em utilizar várias colunas auxiliares e fórmulas com lógica apropriada para garantir que os valores vazios sejam excluídos da classificação.
Cenário aplicável: Utilize este método quando pretender obter classificações incrementais (da menor para a maior), mantendo as células vazias nas suas posições — especialmente em intervalos contínuos onde entradas em falta não devem interferir na numeração da classificação.
Para efetuar uma classificação crescente ignorando células em branco, siga estes passos, que envolvem duas colunas auxiliares para construir o resultado:
1. Selecione uma célula vazia adjacente aos seus valores – por exemplo, a célula B2 se a sua lista começar em A2 – e introduza a seguinte fórmula:
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))A fórmula devolve um espaço em branco se A2 estiver vazio; caso contrário, gera números decimais com base no valor de A2, acrescentando .0, .1, .2, .3, etc., à medida que arrasta a alça de preenchimento para copiar a fórmula por todas as linhas com dados.
Explicação dos parâmetros e dicas:
- $A2: A primeira célula a ordenar. Ajuste caso a sua lista comece numa linha diferente.
- $B$2: A célula onde introduz esta fórmula. Preste atenção às referências absolutas (por exemplo, $A$2 e $B$2) para garantir que a fórmula se comporte corretamente ao ser copiada para baixo.
2. Na coluna seguinte, por exemplo C2, introduza esta fórmula para gerar uma lista ordenada dos valores auxiliares:
=SMALL($B$2:$B$8,ROW()-ROW($C$1))Esta fórmula extrai sequencialmente os menores, os seguintes menores e os valores subsequentes de B2:B8 (nota: altere o intervalo se os seus dados forem mais extensos) à medida que copia a fórmula para baixo.
Explicação dos parâmetros:
- $B$2:$B$8: O intervalo onde é aplicada a fórmula auxiliar anterior (a primeira).
- $C$1: Célula imediatamente acima daquela em que está a introduzir a fórmula; este desfasamento controla a ordem de classificação.
3. Na célula D2, introduza a seguinte fórmula para atribuir classificações, mantendo os espaços em branco intactos:
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")Esta fórmula compara o valor em B2 com os resultados ordenados em C2:C8. Se houver correspondência, apresenta a classificação; caso contrário (por exemplo, para células vazias), não mostra nada, mantendo a aparência de uma célula vazia. Arraste a alça de preenchimento para aplicar a todas as linhas relevantes.
Parâmetros:
- $B2: A célula que contém o valor auxiliar utilizado na classificação.
- $C$2:$C$8: O intervalo dos valores auxiliares ordenados.
Precauções: Se adicionar ou remover dados, lembre-se de atualizar todos os intervalos em cada fórmula para que correspondam ao novo tamanho dos seus dados. Para listas muito extensas, considere utilizar intervalos dinâmicos ou Tabelas do Excel e reduza ajustes manuais de intervalos.
Resolução de problemas: Se as classificações estiverem em falta ou desalinhadas, verifique se todos os intervalos das fórmulas auxiliares estão corretamente alinhados. Desalinhamentos entre colunas provocam classificações incorretas ou erros não intencionais.
Classificar valores ignorando células em branco por ordem decrescente utilizando uma fórmula
Quando pretende atribuir classificações por ordem decrescente — em que o valor mais alto recebe a classificação 1 — existe uma abordagem mais rápida que utiliza apenas uma fórmula. Esta solução é especialmente útil para pontuações de testes, metas de vendas e conjuntos de dados semelhantes, nos quais as células em branco indicam informações em falta ou indisponíveis, e não deseja que ocupem posições na classificação nem exibam erros.
Selecione uma célula na mesma linha da primeira entrada de dados, onde pretende que o resultado apareça, e introduza:
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))Após introduzir a fórmula, utilize a alça de preenchimento para a copiar ao longo dos seus dados. Esta fórmula verifica se a função RANKdevolve um erro (por exemplo, quando)A2 está em branco); nesse caso, deixa o resultado em branco em vez de apresentar “#N/D”. Se a célula contiver um valor válido, apresenta a classificação apropriada.
Parâmetros:
- A2: A célula a classificar (ajuste-a ao seu intervalo de dados).
- A$2:A$8: O intervalo completo dos seus dados (use referência absoluta para copiar).
Lembrete sobre erros: Se continuar a ver erros “#N/D”, verifique se as referências da fórmula correspondem ao seu intervalo de dados pretendido e se não existem valores não numéricos nas células a ordenar.
Classificar valores ignorando células em branco utilizando VBA
Para utilizadores familiarizados com macros e que pretendam automatizar a classificação de um intervalo contendo células em branco — quer por ordem crescente, quer decrescente — uma macro VBA personalizada pode simplificar significativamente o processo, eliminando a necessidade de múltiplas colunas auxiliares e a manutenção contínua de fórmulas.
Como utilizar:
1. Aceda ao separador Programador e clique em Visual Basic para abrir o editor do Microsoft Visual Basic para Aplicações. Se o separador Programador não estiver visível, consulte este guia: Mostrar o separador Programador no Excel.
2. Na nova janela do Microsoft Visual Basic para Aplicações, clique em Inserir > Módulo e cole qualquer um dos seguintes códigos na janela do Módulo:
- Para efetuar uma classificação crescente ignorando células em branco:
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - Para efetuar uma classificação decrescente ignorando células em branco:
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3. Prima F5 para executar a macro. Ser-lhe-á apresentada uma caixa de diálogo a solicitar que selecione o intervalo que pretende classificar. De seguida, outra caixa de diálogo pedir-lhe-á para escolher a primeira célula onde os resultados da classificação deverão ser colocados. A macro apresentará então as classificações a partir da célula selecionada, deixando vazias quaisquer células em branco no intervalo de origem.
Dicas:
- Se nada acontecer, verifique se as macros estão ativadas e se tem permissão para executar código no seu livro.
- A execução de uma macro VBA não pode ser anulada. Certifique-se de que copiou ou fez uma cópia de segurança dos seus dados antes de executar a macro.
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