Como encontrar o valor mais próximo no Excel?

Imagine que tem uma lista de números na coluna B e precisa de identificar qual o valor mais próximo de um número específico — por exemplo, 18. Ao utilizar uma fórmula de matriz no Excel, consegue determinar esse valor de forma rápida e eficiente, sem precisar de percorrer manualmente toda a lista.

Para começar, selecione uma célula vazia e introduza a seguinte fórmula. Depois de escrever a fórmula, certifique-se de premir Ctrl + Shift + Enter em vez de apenas Enter. Isto garantirá que a fórmula seja executada como uma fórmula de matriz, o que é essencial para funcionar corretamente:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Esta abordagem é ideal quando precisa de obter o número mais próximo dentro de uma gama contínua. Funciona na perfeição na maioria dos casos em que a precisão numérica e as correspondências exatas são essenciais. No entanto, tenha em atenção que as fórmulas de matriz podem ser exigentes em termos de recursos com conjuntos de dados muito grandes. Se encontrar problemas de desempenho ou receber mensagens de erro como #VALOR!, verifique novamente as suas referências de células e certifique-se de que premiu corretamente Ctrl + Shift + Enter.

Por vezes, poderá precisar não apenas do valor mais próximo, mas de selecionar todos os números que estejam dentro de um determinado intervalo em relação ao seu valor-alvo — frequentemente designado por intervalo de desvio. O Kutools para Excel oferece uma solução prática através da sua funcionalidade Selecionar Células Especiais, permitindo-lhe selecionar rapidamente todos os valores cuja diferença em relação ao seu alvo esteja dentro de um limite especificado.

Por exemplo, imagine que o seu valor-alvo é 18 e que definiu um desvio de 2. Isso significa que pretende selecionar todos os valores na sua gama compreendidos entre 16 (18 – 2) e 20 (18 + 2). Veja como fazer isto passo a passo:

1. Selecione o intervalo que pretende pesquisar (por exemplo, B3:B22) e aceda a Kutools > Selecionar > Selecionar células específicas.

2. Na caixa de diálogo Selecionar células específicas:

• Em Selecionar tipo, escolha Célula.
• Em Especificar tipo:
  – Defina a primeira lista pendente como Maior ou Igual a e introduza 16 na caixa.
  – Defina a segunda lista pendente como Menor ou Igual a e introduza 20.

3. Clique em OK para executar. O Kutools notificá-lo-á sobre quantas células cumprem os seus critérios e destacará todos os valores mais próximos dentro do desvio especificado, tal como se mostra abaixo:

Esta solução é ideal para identificar rapidamente todos os valores próximos em massa, especialmente ao lidar com gamas amplas com tolerâncias variáveis. Note que a precisão da sua seleção depende da definição clara do seu desvio — se este for demasiado estreito ou amplo, poderá perder Dados Relevantes ou incluir valores indesejados.

Para utilizadores que procuram automatização ou precisam de realizar pesquisas personalizadas por valores mais próximos — tanto em dados numéricos como em texto — ao longo de várias folhas ou grandes conjuntos de dados, uma macro VBA oferece uma solução eficiente e flexível. Ao programar o Excel para analisar sistematicamente a diferença entre o valor pretendido e todos os candidatos, obtém não só o número mais próximo, mas também a cadeia de caracteres mais semelhante com base na distância textual.

Esta abordagem revela-se vantajosa sempre que for necessária uma automatização integrada, especialmente em conjuntos de dados demasiado vastos para métodos manuais ou na execução de tarefas recorrentes. Contudo, lembre-se de que as macros VBA exigem a ativação das macros e um conhecimento básico do ambiente VBA. Antes de executar qualquer macro, faça sempre uma cópia de segurança dos seus dados para evitar perdas acidentais.

1. Clique em Programador > Visual Basic. Na janela Microsoft Visual Basic para Aplicações, clique em Inserir > Módulo e copie o seguinte código para o módulo:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Em seguida, vá à sua folha de cálculo e introduza esta fórmula: =FindClosest(B3:B22; E2) numa célula vazia. Prima Enter para obter o valor mais próximo.

Ao rever ou apresentar dados, é muitas vezes útil identificar visualmente os valores mais próximos de um alvo sem filtrar ou reorganizar os seus dados. A funcionalidade integrada do Excel Usar Formatação Condicional permite-lhe destacar as células mais próximas do seu valor-alvo, tornando-as imediatamente visíveis à primeira vista. Embora este método não devolva o valor exato em si, é altamente eficaz para uma análise rápida dos dados e para criar impacto visual.

A principal vantagem deste método é o destaque dinâmico e não destrutivo, que se adapta automaticamente à medida que os dados ou os valores-alvo são alterados. É especialmente indicado para painéis, apresentações e cenários de revisão em que a visibilidade é essencial. Pode ser menos preciso quando vários valores partilham a mesma «proximidade» e não gera o valor propriamente dito para processamento posterior.

1. Selecione o intervalo de células que pretende analisar (por exemplo, B3:B22).

2. No separador Base, clique em Usar Formatação Condicional > Nova Regra.

3. Na caixa de diálogo, escolha Utilizar uma fórmula para determinar quais as células a formatar. Em seguida, na caixa da fórmula, introduza a seguinte fórmula:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Clique em Formatar e escolha uma cor de destaque; depois, clique em OK e novamente em OK para aplicar a regra.

Isto irá destacar todas as células no seu intervalo selecionado cujos valores forem os mais próximos do valor-alvo em E2.

Se estiver a trabalhar com gamas extensas ou obtiver resultados inesperados, verifique novamente se as suas referências estão corretas e se as referências absolutas e relativas estão definidas conforme pretendido (utilize o símbolo $ para fixar a célula-alvo e as referências da gama).