Como localizar e substituir valores superiores ou inferiores a um valor específico no Excel?
Trabalhar com grandes conjuntos de dados no Excel exige frequentemente identificar e substituir células que cumpram critérios específicos, como valores superiores ou inferiores a um determinado limiar. Por exemplo, poderá precisar de substituir todos os números acima de 500 por 0 ou trocar quaisquer valores abaixo de um padrão de desempenho por uma mensagem de aviso. Ao contrário da ferramenta padrão Localizar e Substituir — que deteta apenas correspondências exatas ou parciais de texto ou números —, as substituições condicionais baseadas em comparações numéricas exigem abordagens alternativas. Este tutorial apresenta vários métodos práticos para lidar com estes cenários de forma eficiente, ajudando-o a poupar tempo e a minimizar erros manuais.
Localizar e Substituir valores superiores/inferiores a um valor específico com código VBA
Localizar e Substituir valores superiores/inferiores a um valor específico com Kutools para Excel
Outros métodos incorporados no Excel – Filtrar/Ordenar e Substituir
Localizar e Substituir valores superiores/inferiores a um valor específico com código VBA
Por exemplo, imagine que pretende localizar rapidamente todos os valores no seu conjunto de dados superiores a 500 e substituí-los por 0 numa única operação. Trata-se de uma necessidade comum em ajustes de classificações, marcação de resultados de conformidade ou limpeza de dados. Com o VBA, pode automatizar todo este processo e eliminar edições manuais repetitivas.
A seguinte solução em VBA permite substituir, de uma só vez, todos os valores das células superiores ou inferiores a um número específico. Pode ajustar o valor de comparação e o valor de substituição conforme as necessidades do seu caso:
1. Prima e mantenha premidas as teclas ALT + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Clique em Inserir > Módulo e cole o seguinte código na Janela do Módulo.
Código VBA: Localizar e Substituir valores superiores ou inferiores a um valor específico
Sub FindReplace()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value > 500 Then
Rng.Value = 0
End If
Next
End Sub
3. Em seguida, prima a tecla F5 para executar este código. Quando solicitado, selecione o intervalo de dados onde pretende Localizar e Substituir valores. (Selecionar apenas os dados relevantes ajuda a evitar substituições não intencionais em células não relacionadas.)
4. Clique em OK na caixa de diálogo. O código irá analisar automaticamente o seu intervalo selecionado e substituir todos os valores superiores a 500 por 0 (ou conforme especificado).
Notas e Dicas:
- Pode ajustar o limiar e o valor de substituição modificando estas linhas no código:
If Rng.Value >500Then
Rng.Value =0 - Este código afeta apenas números. Se os seus dados incluírem células vazias ou entradas não numéricas, estas permanecerão inalteradas.
- Antes de executar o VBA, guarde uma cópia de segurança do seu ficheiro para poder reverter as alterações, se necessário.
- Se surgir uma mensagem de segurança relacionada com macros, certifique-se de que as macros estão ativadas para este livro.
Localizar e Substituir valores superiores/inferiores a um valor específico com Kutools para Excel
Se não tiver experiência com VBA ou programação, o Kutools para Excel oferece uma solução gráfica para resolver este problema. A sua funcionalidade Selecionar células específicas permite identificar todas as células que correspondam às suas condições e substituir os respetivos conteúdos de uma só vez, minimizando erros do utilizador e acelerando a limpeza dos dados.
Após instalar o Kutools para Excel, siga os passos abaixo:
1. Selecione o intervalo de dados que pretende processar.
2. Aceda a Kutools > Selecionar > Selecionar células específicas para abrir a caixa de diálogo Selecionar células específicas.
3. Na caixa de diálogo Selecionar células específicas:
- Selecione Célula para o Selecionar tipo.
- Escolha Maior que(ou)Menor que, conforme necessário) em Especificar tipo.
- Introduza o seu valor de limiar no campo adjacente (por exemplo, 500).
4. Clique em OK. Todas as células que cumprirem os seus critérios serão destacadas imediatamente. Introduza agora o valor de substituição pretendido e prima simultaneamente Ctrl + Enter; todos os valores selecionados serão atualizados instantaneamente.
 |  |  |
Dicas Adicionais:
- Pode utilizar outros critérios, tais como Menor que, Igual a ou Contém, consoante as suas necessidades.
- Para evitar substituições acidentais, verifique novamente a sua seleção antes de premir Ctrl + Enter.
Descarregue e experimente gratuitamente o Kutools para Excel agora!
Fórmula do Excel – Utilize a função SE numa coluna auxiliar para substituir valores superiores ou inferiores a um limiar
Esta abordagem utiliza a função incorporada do Excel SE para criar uma coluna auxiliar, facilitando a verificação visual e a posterior aplicação de substituições condicionais. É especialmente útil se pretender maior transparência antes de sobrescrever os dados originais ou se precisar de uma forma não destrutiva de testar como ficarão os resultados. Além disso, pode personalizar a lógica de substituição para diferentes cenários — como relatórios, categorização ou identificação de valores atípicos.
1. Insira uma nova coluna junto aos seus dados (por exemplo, se os seus dados estiverem na coluna A, insira a nova coluna B).
2. Na primeira célula da coluna auxiliar (por exemplo, B2), insira a seguinte fórmula para substituir todos os valores superiores a 500 por 0:
=IF(A2>500,0,A2)Se pretender substituir valores inferiores a um limiar (por exemplo, inferiores a 200), utilize:
=IF(A2<200,0,A2)Pode substituir 500, 200 e 0 por quaisquer valores de limiar e substituição que se adequem às suas necessidades. A referência A2 deverá ser ajustada de acordo com o seu intervalo de dados real.
3. Prima Enter após introduzir a fórmula. Em seguida, copie-a para o resto da coluna auxiliar (arraste a alça de preenchimento para baixo ou clique duas vezes na alça).
4. Assim que confirmar que a coluna auxiliar produz o resultado pretendido, selecione e copie os novos dados, clique com o botão direito do rato no intervalo de dados original e escolha Colar Especial > Valores para substituir os dados originais pelos resultados calculados.
Dicas e Precauções:
- As fórmulas de coluna auxiliar facilitam a identificação e revisão das alterações antes de substituir os dados originais, minimizando riscos.
- Tenha cuidado com as referências de células ao aplicar fórmulas a intervalos não contíguos — certifique-se de que o alinhamento está correto.
- Esta abordagem mantém os seus dados originais intactos até que termine a revisão e decida substituí-los.
- Se tiver conjuntos de dados grandes, utilizar fórmulas poderá ser mais lento do que o VBA ou o Kutools, mas oferece maior segurança na revisão de alterações nos dados.
Outros métodos incorporados no Excel – Filtrar e Substituir
A filtragem pode ajudar a selecionar visualmente todos os valores superiores ou inferiores ao seu requisito específico, permitindo-lhe substituir rapidamente todas as células relevantes utilizando a edição padrão do Excel. Este método é flexível e não requer fórmulas nem código, sendo adequado para quem prefere trabalhar diretamente com a interface do Excel em tarefas pontuais ou visuais.
1. Selecione o seu intervalo de dados e ative o filtro clicando em Dados > Filtrar.
2. Clique na seta suspensa da coluna que pretende avaliar. Escolha Filtros de Números > Maior Do Que(ou)Menor Do Que) e, em seguida, introduza o valor limite (por exemplo, 500).
3. O Excel irá apresentar apenas as linhas que correspondem às suas condições de filtro. Selecione todas as células visíveis filtradas na sua coluna.
4. Introduza o valor de substituição (por exemplo, 0) e prima Ctrl + Enter — o Excel substituirá apenas as células atualmente visíveis (filtradas).
5. Desative o filtro para visualizar e verificar o seu conjunto final de dados.
Dicas, Vantagens e Desvantagens:
- Filtrar e substituir é um método simples e ideal para conjuntos de dados moderados, quando pretende confirmar visualmente as células alteradas.
- Para colunas com fórmulas, este método irá substituí-las e poderá quebrá-las; use com precaução.
- Se selecionou acidentalmente o intervalo errado e efetuou alterações, prima Ctrl + Z para anular, ajuste depois a sua seleção ou as Condições de Filtro e tente novamente.
Artigos relacionados:
Como localizar e substituir uma correspondência exata no Excel?
Como substituir texto por imagens correspondentes no Excel?
Como Localizar e Substituir Cor de preenchimento no Excel?
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