Como encontrar e substituir valores maiores que / menores que um valor específico no Excel?

Trabalhar com grandes conjuntos de dados no Excel muitas vezes exige que você identifique e substitua células que atendam a critérios específicos, como valores maiores ou menores que um determinado limite. Por exemplo, você pode precisar substituir todos os números acima de 500 por 0, ou substituir quaisquer valores abaixo de um padrão de desempenho por uma mensagem de aviso. Ao contrário da ferramenta de Localizar e Substituir padrão – que só localiza correspondências exatas ou parciais de texto/número – substituições condicionais baseadas em comparações numéricas exigem abordagens alternativas. Este tutorial descreve vários métodos práticos para lidar com esses cenários de forma eficiente, ajudando você a economizar tempo e minimizar erros manuais.

Encontrar e substituir valores maiores que / menores que um valor específico com código VBA

Encontrar e substituir valores maiores que / menores que um valor específico com Kutools para Excel

Fórmula do Excel - Use a função SE em uma coluna auxiliar para substituir valores maiores ou menores que um limite

Outros Métodos Internos do Excel - Filtrar/Classificar e Substituir

Encontrar e substituir valores maiores que / menores que um valor específico com código VBA

Por exemplo, imagine que você deseja localizar rapidamente todos os valores em seu conjunto de dados que são maiores que 500 e alterá-los para 0 em uma única operação. Essa é uma necessidade comum em ajustes de notas, marcação de resultados de conformidade ou limpeza de dados. Usando VBA, você pode automatizar todo esse processo e evitar edições manuais repetitivas.

A seguinte solução VBA permite que você substitua todos os valores de células maiores que – ou menores que – um número específico de uma vez. Você pode personalizar o valor de comparação e a substituição conforme necessário para o seu caso:

1. Pressione as teclas ALT + F11 para abrir a janela Microsoft Visual Basic for Applications.

2. Clique em Inserir > Módulo e cole o seguinte código na Janela de Módulo.

Código VBA: Encontre e substitua valores maiores ou menores que 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, pressione a tecla F5 para executar este código. Quando solicitado, selecione o intervalo de dados onde você deseja encontrar e substituir valores. (Selecionar apenas os dados relevantes ajuda a evitar substituições indesejadas em células não relacionadas.)

4. Clique em OK na caixa de diálogo. O código irá automaticamente percorrer o intervalo selecionado e substituir todos os valores maiores que 500 por 0 (ou conforme especificado).

Notas e Dicas:

• Você pode ajustar o limite e o valor de substituição modificando estas linhas no código:
  If Rng.Value >500 Then
  Rng.Value =0
• Este código só altera números. Se seus dados incluírem células em branco ou entradas não numéricas, elas permanecerão inalteradas.
• Antes de executar o VBA, considere salvar uma cópia de backup do seu arquivo caso você queira reverter as alterações.
• Se você encontrar um prompt de segurança de macro, certifique-se de que as macros estão habilitadas para este livro.

Encontrar e substituir valores maiores que / menores que um valor específico com Kutools para Excel

Se você não tem experiência com VBA ou codificação, o Kutools para Excel oferece uma maneira gráfica de resolver esse problema. Sua utilidade Selecionar Células Específicas permite localizar todas as células que correspondem às suas condições e substituir seus conteúdos de uma vez, minimizando erros do usuário e acelerando a limpeza de dados.

Após instalar o Kutools para Excel, siga os passos abaixo:

1. Selecione o intervalo de dados que você deseja processar.

2. Vá para 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:

1. Selecione Célula para o tipo de Seleção.
2. Escolha Maior que (ou Menor que, conforme necessário) de Tipo específico.
3. Digite seu valor limite no campo adjacente (por exemplo, 500).

4. Clique em OK. Todas as células que atendem aos seus critérios serão destacadas de uma vez. Agora, digite o valor de substituição desejado e pressione Ctrl + Enter juntos; cada valor selecionado será atualizado instantaneamente.

Dicas Adicionais:

• Você pode usar outros critérios, como Menor que, Igual a, ou Contém, dependendo das suas necessidades.
• Para evitar substituições acidentais, verifique sua seleção antes de pressionar Ctrl + Enter.

Baixe e experimente gratuitamente o Kutools para Excel agora!

Fórmula do Excel - Use a função SE em uma coluna auxiliar para substituir valores maiores ou menores que um limite

Essa abordagem utiliza a função SE interna do Excel para criar uma coluna auxiliar, facilitando a verificação visual e, em seguida, aplicar substituições baseadas em condições. É especialmente útil se você quiser mais transparência antes de sobrescrever seus dados originais, ou se precisar de uma maneira não destrutiva de testar como ficarão os resultados. Você também pode personalizar a lógica de substituição para diferentes cenários, como relatórios, categorização ou sinalização de outliers.

1. Insira uma nova coluna ao lado dos seus dados (por exemplo, se seus dados estiverem na coluna A, insira uma nova coluna B).

2. Na primeira célula da coluna auxiliar (por exemplo, B2), insira a seguinte fórmula para substituir todos os valores maiores que 500 por 0:

=IF(A2>500,0,A2)

Se você deseja substituir valores menores que um limite (por exemplo, menos de 200), use:

=IF(A2<200,0,A2)

Você pode substituir 500 ou 200 e 0 por qualquer limite e valor de substituição para atender às suas necessidades. A referência A2 deve ser ajustada com base no seu intervalo de dados real.

3. Pressione Enter após digitar a fórmula. Depois, copie a fórmula para o restante da coluna auxiliar (arraste a alça de preenchimento para baixo ou clique duas vezes nela).

4. Uma vez que você confirme que a coluna auxiliar produz o resultado desejado, selecione e copie os novos dados, depois clique com o botão direito no intervalo de dados original e escolha Colar Especial > Valores para sobrescrever os dados originais com os resultados calculados.

Dicas e Precauções:

• As fórmulas da coluna auxiliar facilitam identificar e revisar as mudanças antes de substituir os dados originais, reduzindo riscos.
• Tenha cuidado com as referências de células se você aplicar fórmulas em intervalos não contíguos – certifique-se de alinhamento correto.
• Essa abordagem preserva seus dados originais até que você termine a revisão e decida sobrescrevê-los.
• Se você tiver grandes conjuntos de dados, usar fórmulas pode ser mais lento do que o VBA ou o Kutools, mas é mais seguro para revisar as mudanças nos dados.

Outros Métodos Internos do Excel - Filtrar e Substituir

A filtragem pode ajudar a selecionar visualmente todos os valores maiores ou menores que seu requisito específico, para que você possa substituir todas as células relevantes rapidamente usando a edição padrão do Excel. Este método é flexível e não requer fórmulas ou código, tornando-o adequado para aqueles que preferem trabalhar diretamente com a interface do Excel para tarefas únicas ou visuais.

1. Selecione seu intervalo de dados e habilite o filtro clicando em Dados > Filtro.

2. Clique na seta suspensa na coluna que você deseja avaliar. Escolha Filtros Numéricos > Maior Que (ou Menor Que), depois insira o valor limite (por exemplo, 500).

3. O Excel exibirá apenas as linhas que correspondem aos seus critérios de filtro. Selecione todas as células filtradas visíveis em sua coluna.

4. Digite o valor de substituição (por exemplo, 0) e pressione Ctrl + Enter – o Excel substituirá apenas as células visíveis (filtradas) no momento.

5. Desative o filtro para ver e verificar seu conjunto de dados final.

Dicas, Prós & Contras:

• O método de filtragem e substituição é simples e ideal para conjuntos de dados moderados onde você quer confirmação visual de quais células foram alteradas.
• Para colunas contendo fórmulas, esse método irá sobrescrever e possivelmente quebrar as fórmulas; use com cautela.
• Se você selecionou acidentalmente o intervalo errado e fez alterações, pressione Ctrl + Z para desfazer, depois ajuste sua seleção ou critérios de filtro e tente novamente.

Artigos Relacionados:

Como encontrar e substituir correspondência exata no Excel?

Como substituir texto por imagens correspondentes no Excel?

Como encontrar e substituir cor de preenchimento no Excel?