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

Supposons que vous ayez une liste de nombres dans la colonne B et que vous deviez déterminer quelle valeur est la plus proche d'un nombre donné — par exemple, 18. L'utilisation d'une formule matricielle dans Excel vous permet d'identifier cette valeur efficacement sans avoir à parcourir manuellement la liste.

Pour commencer, sélectionnez une cellule vide et entrez la formule suivante. Une fois la formule saisie, assurez-vous de presser Ctrl + Shift + Entrée au lieu de simplement Entrée. Cela garantira que la formule fonctionnera comme une formule matricielle, ce qui est nécessaire pour son bon fonctionnement :

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

Cette approche est la plus adaptée lorsque vous devez récupérer le nombre unique le plus proche d'une plage continue. Elle fonctionne bien dans la plupart des cas où la précision numérique et les correspondances exactes sont cruciales. Cependant, gardez à l'esprit que les formules matricielles peuvent être gourmandes en ressources sur des ensembles de données très volumineux. Si vous rencontrez des problèmes de performance ou recevez des messages d'erreur comme #VALEUR!, vérifiez vos références de cellule et assurez-vous de presser correctement Ctrl + Shift + Entrée.

Il arrive que vous n'ayez pas besoin seulement de la valeur la plus proche, mais plutôt de sélectionner tous les nombres qui se situent dans une certaine plage autour de votre valeur cible — souvent appelée plage de déviation. Kutools pour Excel offre une solution pratique grâce à sa fonctionnalité Sélectionner des cellules spécifiques, permettant une sélection rapide de toutes les valeurs comprises dans une différence spécifiée par rapport à votre cible.

Par exemple, disons que votre valeur cible est 18 et que vous avez déterminé une valeur de déviation de 2. Cela signifie que vous souhaitez sélectionner toutes les valeurs dans votre plage qui sont comprises entre 16 (18-2) et 20 (18+2). Voici comment vous pouvez réaliser cela étape par étape :

1. Sélectionnez la plage que vous souhaitez rechercher (par exemple, B3:B22), puis allez dans Kutools > Sélectionner > Sélectionner des cellules spécifiques.

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

• Sous Type de sélection, choisissez Cellule.
• Dans Type spécifique:
  - Réglez la première liste déroulante sur Supérieur ou égal à et entrez 16 dans la case.
  - Réglez la deuxième liste déroulante sur Inférieur ou égal à et entrez 20.

3. Cliquez OK pour exécuter. Kutools vous indiquera combien de cellules correspondent à vos critères et mettra en évidence toutes les valeurs les plus proches dans la plage de déviation spécifiée comme indiqué ci-dessous :

Cette solution est idéale pour identifier rapidement toutes les valeurs proches en masse, surtout lorsqu'on traite des plages larges avec des tolérances variables. Notez que la précision de votre sélection dépend de la configuration claire de votre déviation — si votre déviation est trop étroite ou trop large, vous risquez de manquer des données pertinentes ou d'inclure des valeurs non désirées.

Pour les utilisateurs cherchant l'automatisation ou ayant besoin d'effectuer des recherches personnalisées de valeurs les plus proches — que ce soit pour des données numériques ou textuelles — sur plusieurs feuilles ou de grands ensembles de données, une macro VBA peut être une solution efficace et flexible. En programmant Excel pour vérifier systématiquement la différence entre votre cible et tous les candidats, vous pouvez récupérer non seulement le nombre le plus proche, mais aussi la chaîne de texte la plus proche en termes de distance textuelle.

Cette approche présente un avantage lorsqu'une automatisation intégrée est requise, surtout sur des plages trop vastes pour les méthodes manuelles ou lors de l'application de tâches récurrentes. Cependant, gardez à l’esprit que les macros VBA nécessitent l'activation des macros et une familiarité de base avec l'environnement VBA. Avant d'exécuter une macro, sauvegardez toujours vos données pour éviter toute perte involontaire.

1. Cliquez sur Développeur  > Visual Basic. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insérer > Module, et 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, allez sur votre feuille de calcul, et entrez cette formule : =FindClosest(B3:B22, E2) dans une cellule vide. Appuyez sur la touche Entrée pour obtenir la valeur la plus proche.

Lors de la revue 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 ou réorganiser vos données. La fonctionnalité de mise en forme conditionnelle intégrée d'Excel vous permet de mettre en évidence les cellules qui sont les plus proches de votre valeur cible, les rendant faciles à repérer en un coup d'œil. Bien que cette méthode ne retourne pas la valeur exacte elle-même, elle est efficace pour une analyse rapide des données et une mise en évidence visuelle.

Le principal avantage de cette méthode est une mise en évidence dynamique et non destructive qui peut s'adapter lorsque les données ou les valeurs cibles changent. Elle convient particulièrement aux tableaux de bord, aux présentations et aux scénarios de revue où la visibilité est essentielle. Elle peut être moins précise si plusieurs valeurs partagent la même « proximité », et elle ne renvoie 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. Sur l'onglet Accueil, cliquez sur Mise en forme conditionnelle > Nouvelle règle.

3. Choisissez Utiliser une formule pour déterminer les cellules à formater dans la boîte de dialogue. Ensuite, dans la zone de formule, entrez la formule suivante :

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

4. Cliquez sur Format et choisissez une couleur de surbrillance, puis cliquez sur OK, et encore sur OK pour appliquer la règle.

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

Si vous travaillez avec de grandes plages ou obtenez des résultats inattendus, revérifiez que vos références sont correctes et que les références absolues/relatives sont définies comme prévu (utilisez $ pour verrouiller la cellule cible et les références de plage).