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 :

excel_fuzzy_lookup_1

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

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 ! excel_fuzzy_lookup_2

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 :excel_fuzzy_lookup_3

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 :excel_fuzzy_lookup_formula_2

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

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 :excel_fuzzy_lookup_formula_3

Et le résultat :excel_fuzzy_lookup_5

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.
excel_fuzzy_lookup_3

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

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 !

9 thoughts on “Comparaison de listes dans Excel : Fuzzy Logic donne un coup de vieux à VLOOKUP”

  1. Bonjour,

    j’ai souhaité utiliser ce complément sous Excel 2010, mais une fois installé, l’icône “Fuzzy Logic” ou “Fuzzy lookup” n’apparaît pas dans le ruban. Même en passant par le paramétrage d’Excel Option/Compléments/Compléments COM… ça ne fonctionne pas.
    Avez-vous rencontré ce problème lors de la première utilisation ou bien est-ce que j’oublie quelque chose ??

    • Bonjour, en partant du principe que vous êtes administrateur de la machine et que vous avez lancé l’installation alors qu’Excel était fermé, l’installation du plugin doit se terminer avec l’apparition d’un titre de menu “Fuzzy Lookup” au premier niveau du ruban (similaire à “Affichage” ou “Données”).
      Une fois l’installation finie, il faut lancer Excel et accepter l’activation du plugin. J’ai eu l’occasion de tester sur Excel 2007 et sur Excel 2010.

  2. J’ai ré-installé le tout avec Excel fermé (ce qui n’était pas le cas au premier essai) et effectivement “Fuzzy Lookup” est maintenant bien installé. Merci pour cette piste.

  3. ’ai trouvé ce post sur votre blog consacré à l’extension Fuzzy Logic. Je pensais avoir trouvé le graal grâce à vous. Ma vie de petit entrepreneur aurait été bien facilitée. Hélas, je ne trouve pas ce composant pour Mac. Avez-vous un conseil à me donner pour pouvoir réaliser des comparaisons intelligente sous Excel avec mon mac ?

    • Mmm ça ne me dit rien. Je viens de jeter un coup d’œil sur google mais il semblerait que les utilisateurs de Mac n’aient pas de solution pour l’instant (via excel ou non) pour faire des recherches approximatives.

      Si vous trouvez quelque chose, n’hésitez pas à partager l’info !

      • Merci d’avoir pris le temps de me répondre.

        J’ai bien trouvé quelque chose (http://forum.excel-pratique.com/excel/algorithme-levenshtein-automatise-recherche-de-la-meilleur-t48493.html#p271046) mais je suis incapable de simplifier et adapter cette macro à ce dont j’ai besoin :

        – prendre un fichier (A) composé notamment de
        “identifiant_unique/prénom/nom/fonction/organisation/email”
        – comparer à un autre fichier (B) comprenant notamment
        “identifiant_unique/prénom/nom/fonction/organisation/email…”
        (je peux tout à fait coller l’un dans une feuille et l’autre dans une autre
        feuille d’un même classeur)
        – indiquer la distance que je retiens pour déterminer quels sont les
        doublons potentiels
        – faire la comparaison entre les deux uniquement sur prénom/nom,
        – faire apparaitre (idéalement dans une nouvelle feuille du même classeur)
        les doublons potentiels les uns en dessous des autres, avec pour chacun les
        données “identifiant_unique/prénom/nom/fonction/organisation/email” afin de
        faire la MAJ à la main.

  4. 1 an que je cherchais une solution pour réduire notre datacleaning ! comment dire que cet article vient de changer mon quotidien…
    Un grand merci Grégory !

  5. Bonjour et merci beaucoup pour cet article qui semble répondre a mon problème. Juste une petite question supplémentaire , comment faites vous quand vous n avez qu une seule liste. Et que les doublons mal enregistres sont mélangés parmi les bons enregistrements ? Et que par conséquent il peux y avoir plusieurs matching similaires possibles . merci par avance pour votre aide précieuse . Yves

    • Bonjour, un cas intéressant ! Je n’ai jamais eu ce cas à résoudre mais j’imagine que le but est de réduire le nombre de variations, quitte à faire quelques ajustements à la main. Je ferai ça en trois étapes. (1) Dédoublonnez votre liste. (2) dupliquez la dans une autre colonne et faites un fuzzylookup pour que toutes les variations approchantes se calquent sur une seule proposition. (3) Une fois que vous avez votre table de traduction, faites un simple vlookup pour remplacer les valeurs “impropres” de votre liste originale par celles proposées par cette méthode.

Un commentaire ?