Como localizar 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 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 com uma mensagem de aviso. Ao contrário da ferramenta 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.

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

Localize e substitua 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 Embutidos no Excel - Filtro/Classificar e Substituir

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

Por exemplo, imagine que você deseje localizar rapidamente todos os valores em seu conjunto de dados que são maiores que 500 e alterá-los todos para 0 em uma única operação. Essa é uma necessidade comum em ajustes de classificação, marcação de resultados de conformidade ou limpeza de dados. Usando o 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 das células maiores que - ou menores que - um número específico de uma só vez. Você pode personalizar o valor de comparação e a substituição conforme necessário para o seu caso:

1. Mantenha pressionadas 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 Módulo.

Código VBA: Localize 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 localizar 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ê deseje reverter as alterações.
• Se você encontrar um aviso de segurança de macro, certifique-se de que as macros estejam habilitadas para esta pasta de trabalho.

Localize e substitua 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 identificar todas as células que correspondem às suas condições e substituir seu conteúdo 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 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) em Tipo Específico.
3. Digite o 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 de 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 embutida no 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ê deseja mais transparência antes de sobrescrever seus dados originais, ou se precisa 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 estão 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ê quiser substituir valores menores que um limite (por exemplo, menor que 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. Em seguida, copie a fórmula para o restante da coluna auxiliar (arraste a alça de preenchimento para baixo ou clique duas vezes nela).

4. Depois de confirmar 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 o risco.
• Tenha cuidado com as referências de células se você aplicar fórmulas a intervalos não contíguos — certifique-se de que o alinhamento está 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 alterações de dados.

Outros Métodos Embutidos no Excel - Filtro e Substituição

A filtragem pode ajudar a selecionar visualmente todos os valores maiores ou menores que seus requisitos específicos, para que você possa substituir rapidamente todas as células relevantes usando a edição padrão do Excel. Este método é flexível e não requer fórmulas ou códigos, tornando-o adequado para quem prefere 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 de menu suspenso na coluna que 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 filtro-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 que contêm fórmulas, este método sobrescreverá e possivelmente quebrará as fórmulas; use com cautela.
• Se você selecionou o intervalo errado e fez alterações acidentalmente, pressione Ctrl + Z para desfazer, depois ajuste sua seleção ou critérios de filtro e tente novamente.

Artigos relacionados:

Como localizar e substituir correspondência exata no Excel?

Como substituir texto com imagens correspondentes no Excel?

Como localizar e substituir cor de preenchimento no Excel?