Comment trouver la valeur la plus proche ou la plus voisine dans Excel ?

Supposons que vous ayez une liste de nombres en colonne B et que vous souhaitiez identifier celle qui se rapproche le plus d’une valeur cible — par exemple, 18. Grâce à une formule matricielle dans Excel, vous pouvez déterminer cette valeur en un clin d’œil, sans avoir à parcourir manuellement toute la liste.

Pour commencer, sélectionnez une cellule vide et saisissez la formule suivante. Une fois la formule saisie, veillez à appuyer sur Ctrl + Maj + Entrée au lieu de simplement sur Entrée. Cela garantira que la formule s’exécute comme une formule matricielle, ce qui est essentiel à son bon fonctionnement :

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

Cette approche est particulièrement adaptée lorsque vous devez récupérer le nombre le plus proche au sein d’une plage continue. Elle fonctionne parfaitement dans la plupart des cas où la précision numérique et les correspondances exactes sont essentielles. Cependant, gardez à l’esprit que les formules matricielles peuvent être gourmandes en ressources avec des jeux de données très volumineux. Si vous rencontrez des problèmes de performance ou des messages d’erreur tels que #VALEUR!, vérifiez attentivement vos références de cellules et assurez-vous d’avoir correctement appuyé sur Ctrl + Maj + Entrée.

Il arrive que vous n’ayez pas besoin uniquement de la valeur la plus proche, mais que vous souhaitiez plutôt sélectionner tous les nombres situés dans une certaine plage autour de votre valeur cible — ce qu’on appelle couramment une plage d’écart. Kutools pour Excel propose une solution pratique grâce à sa fonctionnalité Sélectionner des cellules spéciales, qui vous permet de sélectionner rapidement toutes les valeurs dont l’écart par rapport à votre cible est inférieur à un seuil donné.

Par exemple, imaginons que votre valeur cible soit 18 et que vous ayez défini un écart maximal de 2. Cela signifie que vous souhaitez sélectionner toutes les valeurs de votre plage situées entre 16 (18 – 2) et 20 (18 + 2). Voici la marche à suivre, étape par étape :

1. Sélectionnez la plage que vous souhaitez analyser (par exemple, B3:B22), puis accédez à Kutools > Sélectionner > Sélectionner des cellules spécifiques.

2. Dans la boîte de dialogue Sélectionner des cellules spécifiques :

• Sous Sélectionner le type, choisissez Cellule.
• Dans Spécifier le type :
  - Définissez la première liste déroulante sur Supérieur ou égal à et saisissez 16 dans le champ.
  - Définissez la deuxième liste déroulante sur Inférieur ou égal à et saisissez 20.

3. Cliquez sur OK pour exécuter. Kutools vous indiquera combien de cellules répondent à vos critères et mettra en évidence toutes les valeurs les plus proches dans l’écart spécifié, comme illustré ci-dessous :

Cette solution est idéale pour identifier rapidement et en masse toutes les valeurs proches, notamment lorsqu’on travaille avec de vastes plages de données et des tolérances variables. Gardez à l’esprit que la précision de votre sélection dépend directement de la clarté avec laquelle vous définissez votre écart : s’il est trop étroit, vous risquez de manquer des données pertinentes ; s’il est trop large, vous pourriez inclure des valeurs non pertinentes.

Pour les utilisateurs souhaitant automatiser leurs recherches ou effectuer des recherches personnalisées de valeurs les plus proches — qu’il s’agisse de données numériques ou textuelles — sur plusieurs feuilles ou de grands ensembles de données, une macro VBA s’impose comme une solution à la fois efficace et flexible. En programmant Excel pour comparer systématiquement l’écart entre votre cible et tous les candidats, vous obtenez non seulement le nombre le plus proche, mais aussi la chaîne de caractères la plus similaire selon une distance textuelle.

Cette approche offre un avantage indéniable dès qu’une automatisation intégrée s’impose — notamment pour traiter des plages trop vastes pour les méthodes manuelles ou pour exécuter des tâches récurrentes. Toutefois, gardez à l’esprit que les macros VBA exigent l’activation des macros ainsi qu’une connaissance de base de l’environnement VBA. Avant toute exécution, sauvegardez toujours vos données afin d’éviter toute perte accidentelle.

1. Cliquez sur Développeur > Visual Basic. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion > Module, puis copiez le code suivant dans le module :

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. Ensuite, accédez à votre feuille de calcul et saisissez cette formule : =FindClosest(B3:B22; E2) dans une cellule vide. Appuyez sur Entrée pour obtenir la valeur la plus proche.

Lors de l’analyse ou de la présentation de données, il est souvent utile d’identifier visuellement les valeurs les plus proches d’une cible sans filtrer ni réorganiser vos données. La fonctionnalité intégrée d’Excel Utiliser la mise en forme conditionnelle vous permet de mettre en évidence les cellules les plus proches de votre valeur cible, pour un repérage immédiat. Bien que cette méthode ne renvoie pas la valeur exacte elle-même, elle s’avère particulièrement efficace pour une analyse rapide et une mise en valeur visuelle percutante.

Le principal avantage de cette méthode réside dans sa mise en évidence dynamique et non destructive, qui s’adapte automatiquement aux modifications des données ou de la valeur cible. Elle est particulièrement adaptée aux tableaux de bord, aux présentations et aux scénarios d’analyse où la visibilité est essentielle. Toutefois, elle peut manquer de précision lorsque plusieurs valeurs présentent le même niveau de « proximité », et ne fournit pas la valeur elle-même pour un traitement ultérieur.

1. Sélectionnez la plage de cellules que vous souhaitez analyser (par exemple, B3:B22).

2. Sous l’onglet Accueil, cliquez sur Utiliser la mise en forme conditionnelle > Nouvelle règle.

3. Dans la boîte de dialogue, choisissez Utiliser une formule pour déterminer les cellules à mettre en forme. Puis, dans la zone de formule, saisissez la formule suivante :

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

4. Cliquez sur Format et choisissez une couleur de mise en évidence, puis cliquez sur OK, et à nouveau sur OK pour appliquer la règle.

Cela mettra en évidence toutes les cellules de votre plage sélectionnée dont les valeurs sont les plus proches de la valeur cible située en E2.

Si vous travaillez avec de grandes plages ou obtenez des résultats inattendus, vérifiez soigneusement que vos références sont exactes et que les références absolues ou relatives sont définies comme prévu (utilisez le symbole « $ » pour verrouiller la cellule cible ainsi que les références de plage).