Comparaison de listes dans Excel : Fuzzy Logic donne un coup de vieux à VLOOKUP

à

Si vous avez déjà eu à effectuer des comparaisons de listes dans Microsoft Excel, vous êtes forcement à l’aise avec VLOOKUP (ou “RECHERCHEV” si vous avez un Excel en Français). Dans la vraie vie, les choses peuvent se corser un peu et une petite phase d’analyse de données peut se transformer en un cauchemar de consolidation !

Mon VLOOKUP va très bien merci

La grande force de VLOOKUP est de faire des recherches très efficaces (rapides, fiables et précises) sur un grand nombre de lignes. C’est une arme redoutable pour comparer des données exactes ou pour faire un premier passage et dégrossir une liste récalcitrante.
Petit exemple :

On va donc gentiment appliquer la formule classique du VLOOKUP / RechercheV, encadrée par un IFERROR / SiErreur pour y voir plus clair.

On arrive donc à un résultat très satisfaisant. On retrouve bien uniquement les emails de réponse qui doivent apparaître. Tout le monde est content ! 

Pourquoi chercher plus loin ?

Toutes les listes ne sont pas égales. Dans l’exemple dessous, on va regarder une liste un peu différente. Ici j’ai voulu reproduire un cas assez classique où l’on a une liste de prospects d’un coté et les réponses de ces prospects à un formulaire de l’autre. Forcement, notre belle liste toute propre du départ n’est plus aussi fidèle lorsque le prospect en question doit remplir un formulaire sur un salon avec un bout de papier. Voilà à quoi cette liste pourrait ressembler :

A partir du moment ou les données sont moins propres, VLOOKUP va prendre du plomb dans l’aile. Reprenons donc notre formule pour voir ce qui se passe ici :

Malheureusement, ça se confirme, VLOOKUP est moins à l’aise lorsque les données sont plus “sales” :

VLOOKUP a plus d’un tour dans son sac

Vous aurez bien remarqué la dernière variable de la formule qui dit “FALSE”. Cet argument répond à la question “est-ce que j’ai le droit d’aller prendre des résultats approximatifs”. Regardons tout de suite ce que ça donne pour voir si on est sauvés pour autant :

La formule :

Et le résultat :

Bien que partant d’une bonne intention, le résultat est moyen, voire mauvais. A défaut d’autre solution, cette option peut permettre de gagner un peu de temps pour un premier passage, avant de faire un sérieux ménage manuel.

Plus fort que VLOOKUP, voici FUZZY Logic

Sur StackOverflow.com, une communauté d’experts vous expliquera toute une série d’algorithmes de comparaisons basés sur des scripts VBA. Ça peut être un peu impressionnant au début mais au delà du côté barbant, l’idée revient généralement à regarder une cellule A, une cellule B et voir si la ressemblance est assez forte pour se dire que c’est un “partial match”. On dit alors que si la différence entre la cellule A et B est inférieure à un certain seuil, alors la correspondance est probablement valide.

L’un des algorithmes les plus reconnus est la distance de Levenshtein qui “donne une mesure de la similarité entre deux chaînes de caractères” (Wikipedia). L’idée est de se fixer un seuil de tolérance et d’automatiser tout ça pour effectuer des comparaisons de manière industrielle.

Reprenons notre dernière liste pour voir ce que ça donne.

En appliquant correctement la configuration de Fuzzy logic, voilà ce que l’on obtient, en quelques clics :

Pour la petite histoire, voilà la procédure à suivre

1. On sélectionne sa liste A et on fait un CTRL + L. On sélectionne sa liste B et on fait un CTRL + L
2. On va chercher l’icone “Fuzzy Logic” dans le menu / ruban d’excel; un menu vient s’afficher sur la droite de l’écran.
   1. On vérifie que le “Tableau1″ fait bien face au “Tableau2″
   2. On choisit les colonnes à afficher (ici j’e n’ai pris que ma colonne 2)
   3. On choisit le nombre de propositions faites par Fuzzy (ici une seule)
   4. On choisit le seuil de tolérance, plus il est bas (vers la gauche), plus on réduit l’écart possible entre la cellule source et la proposition faite par Fuzzy (ici 0,2).
3. On appuie sur Go, et quelques secondes après, boum, c’est réglé. C’est pas loin d’être magique.

Les limitations de Fuzzy Logic

Dès l’instant où l’on sort d’une comparaison parfaite, on s’expose à des résultats dont l’exactitude peut varier. Il ne faut donc pas s’attendre à ce qu’un simple algorithme résolve tous nos problèmes.

Autre point de détail, si vous avez des listes un peu conséquentes, vous devrez apprendre à patienter un peu. Pour venir un bout d’une comparaison de 17000 lignes contre 43000 lignes, il m’aura fallu près de 3 heures avec un PC plutôt costaud (Core i7, 8Go de RAM, Windows 8 & Office 2013 64 bits).

Conclusion

Fuzzy Logic est fabuleux. Grâce à lui, j’ai pu me sortir en quelques jours d’une comparaison qui m’aurait pris deux bonnes semaines d’un travail particulièrement pénible en temps normal. C’est d’ailleurs ce qui m’a conduit à en faire un article sur ce site, pour en faire profiter tout le monde !

Pour le moment, vous devrez aller sur le site de Microsoft pour télécharger et installer ce plugin officiel, gratuit et INDISPENSABLE. Dans le fichier zip que vous y trouverez, Microsoft a inclus un fichier d’exemple ainsi que de la documentation pour aller un peu plus loin que l’humble exemple exposé ici. C’est très bien fait.

Vous trouverez aussi de nombreuses solutions payantes chez des éditeurs tiers. Je n’ai essayé que deux autres plugins similaires mais, outre le fait qu’ils soient payants, c’est avec Fuzzy Logic de Microsoft que j’ai obtenu les meilleurs résultats. Si vous avez d’autres propositions, je suis preneur !