Regroupement flou dans Excel : comment fusionner des données similaires mais non identiques

Travailler avec des données non standardisées dans Excel peut devenir une activité frustrante. Souvent, nous sommes confrontés à des valeurs qui devraient être considérées comme égales mais qui présentent de petites différences de frappe, de formatage ou de syntaxe. Regrouper manuellement ces éléments prend un temps précieux et augmente le risque d’erreurs. Heureusement, Excel offre une solution efficace mais peu connue : le regroupement flou dans Power Query, une technique avancée de correspondance floue qui simplifie considérablement ce processus.

Comprendre le regroupement flou dans Excel

Le regroupement flou est une fonctionnalité avancée de Power Query qui permet d’identifier et de fusionner automatiquement des éléments similaires mais non identiques. Mais avant d’approfondir, clarifions : Power Query, qu’est-ce que c’est ? Il s’agit d’un puissant outil de préparation de données intégré à Excel qui permet de transformer et de nettoyer les données de manière efficace. Contrairement aux fonctions standard d’Excel qui nécessitent des correspondances exactes, le regroupement flou utilise des algorithmes de similarité pour reconnaître les variantes de la même donnée. Cette fonctionnalité est particulièrement utile lorsque vous travaillez avec :

• Des listes de noms avec des variantes orthographiques (ex. « Jean Dupont » et « Dupont Jean »)
• Des données saisies par différentes personnes avec des conventions différentes
• Des informations provenant de systèmes différents avec des formatages non uniformes
• Des réponses à des questions ouvertes dans des sondages ou des questionnaires

Le regroupement flou est basé sur un « score de similarité » configurable qui détermine à quel point deux chaînes doivent être similaires pour être considérées comme équivalentes. De plus, vous pouvez créer des tables de traduction personnalisées pour mapper des termes spécifiques que vous souhaitez traiter comme identiques. Dans les paragraphes suivants, je vous guiderai à travers toutes les étapes nécessaires pour implémenter cette puissante fonctionnalité dans vos feuilles de calcul, y compris la préparation intelligente des données et l’opération d’agrégation.

Préparer les données pour le regroupement flou

Avant d’utiliser le regroupement flou, il est essentiel d’organiser correctement les données. Une préparation adéquate garantira des résultats optimaux et simplifiera l’ensemble du processus. La première étape consiste à convertir vos données en une table Excel. C’est une exigence essentielle pour utiliser Power Query et accéder aux fonctionnalités de regroupement flou.

Pour créer une table :

1. Sélectionnez n’importe quelle cellule de vos données
2. Appuyez sur Ctrl+T ou allez dans l’onglet « Insertion » et cliquez sur « Tableau »
3. Assurez-vous que l’option « Mon tableau comporte des en-têtes » est sélectionnée si la première ligne contient les noms des colonnes
4. Confirmez en cliquant sur « OK »

Une fois la table créée, il est conseillé de lui attribuer un nom significatif et concis. Cela facilitera sa référence dans les formules et dans Power Query. Pour renommer la table :

1. Sélectionnez n’importe quelle cellule de la table
2. Dans l’onglet « Création de tableau » qui apparaît, modifiez le nom dans le champ « Nom du tableau » en haut à gauche

Il est également important de vérifier la qualité des données avant de continuer. Vérifiez la présence de cellules vides, d’erreurs de formatage ou de caractères spéciaux qui pourraient affecter le processus de regroupement. Si nécessaire, effectuez un nettoyage des données en supprimant les espaces supplémentaires, en uniformisant les majuscules/minuscules ou en corrigeant les erreurs évidentes. Cette phase de nettoyage des données est cruciale pour garantir l’intégrité de l’ensemble de données et améliorer l’efficacité du regroupement flou.

Enfin, identifiez les colonnes qui contiennent les valeurs que vous souhaitez regrouper. Le regroupement flou fonctionne mieux lorsqu’il est appliqué à une seule colonne à la fois, vous devrez peut-être réorganiser vos données en conséquence. Cette préparation intelligente des données vous aidera à obtenir des résultats plus précis dans les étapes suivantes.

Créer une table de traduction personnalisée

L’une des caractéristiques les plus puissantes du regroupement flou est la possibilité d’utiliser une table de traduction personnalisée. Cette table, qui sert de table de référence, permet de définir explicitement quels termes doivent être considérés comme équivalents, indépendamment de leur score de similarité. La table de traduction doit avoir une structure spécifique avec deux colonnes :

• De : contient les valeurs originales que vous souhaitez mapper
• À : contient les valeurs vers lesquelles vous souhaitez convertir les termes originaux

Par exemple, vous pourriez vouloir considérer « courriel », « e-mail » et « message électronique » comme le même concept. Dans ce cas, la table de traduction pourrait ressembler à ceci :

De	À
courriel	message électronique
e-mail	message électronique
mail	message électronique

Pour créer cette table de transformation :

1. Insérez les en-têtes « De » et « À » dans deux cellules adjacentes
2. Remplissez les lignes avec les paires de valeurs à mapper
3. Sélectionnez toute la zone et convertissez-la en tableau (Ctrl+T)
4. Donnez un nom significatif à la table, par exemple « Traduction »

La table de traduction est particulièrement utile pour :

• Standardiser les terminologies spécifiques à un secteur
• Uniformiser les abréviations et les formes développées
• Gérer les synonymes ou les termes équivalents dans différents contextes
• Corriger les erreurs d’orthographe ou de formatage courantes

Plus votre table de traduction sera complète et précise, meilleurs seront les résultats du regroupement flou. Il vaut la peine de consacrer du temps à la création d’une table de traduction complète, surtout si vous prévoyez d’effectuer fréquemment des opérations de fusion sur des données similaires.

Importer les données dans Power Query

Une fois les données préparées et la table de traduction créée, il est temps d’importer le tout dans Power Query pour commencer le processus de regroupement flou. Le chargement des données dans Power Query est une étape fondamentale qui vous permettra d’appliquer des transformations avancées avant de recharger les résultats dans Excel.

Pour importer la table principale :

1. Sélectionnez n’importe quelle cellule de la table de données
2. Allez dans l’onglet « Données » du ruban
3. Cliquez sur « À partir d’un tableau/d’une plage » dans le groupe « Récupérer et transformer des données »

L’Éditeur Power Query s’ouvrira avec vos données. Cet environnement vous permet d’appliquer des transformations avancées avant de recharger les résultats dans Excel. Ensuite, vous devez également importer la table de traduction (si vous l’avez créée). Le processus est identique :

1. Retournez à Excel sans fermer l’Éditeur Power Query
2. Sélectionnez une cellule dans la table de traduction
3. Allez dans l’onglet « Données » et cliquez sur « À partir d’un tableau/d’une plage »

Vous aurez maintenant deux requêtes distinctes dans l’Éditeur Power Query, visibles dans le panneau « Requêtes » sur la gauche. Il est important que les deux requêtes soient disponibles dans l’environnement Power Query avant de procéder au regroupement flou.

Avant de continuer, il est conseillé de vérifier que les types de données sont corrects dans les deux tables. Power Query attribue automatiquement des types de données en fonction du contenu, mais il peut parfois être nécessaire de les corriger :

1. Sélectionnez la colonne à modifier
2. Cliquez droit et choisissez « Modifier le type »
3. Sélectionnez le type de données approprié (généralement « Texte » pour les données à regrouper)

Avec les données correctement importées et formatées, vous êtes prêt à appliquer le regroupement flou. Si vous avez besoin de charger plusieurs fichiers à partir d’un dossier, Power Query offre également cette fonctionnalité, ce qui peut être utile pour des projets plus complexes impliquant de multiples sources de données.

Appliquer le regroupement de base dans Power Query

Avant d’utiliser le regroupement flou, il est utile de comprendre comment fonctionne le regroupement standard dans Power Query. Cela nous fournira la base pour ensuite modifier la formule et implémenter le regroupement flou. Pour appliquer un regroupement standard :

1. Dans l’Éditeur Power Query, sélectionnez la colonne qui contient les valeurs à regrouper
2. Allez dans l’onglet « Transformer » du ruban
3. Cliquez sur le bouton « Regrouper par »

La boîte de dialogue « Regrouper par » s’ouvrira avec plusieurs options :

• Regrouper par : sélectionnez la colonne à utiliser pour le regroupement
• Nouvelle colonne : entrez un nom pour la colonne qui contiendra les résultats du regroupement
• Opération : choisissez « Toutes les lignes » pour conserver toutes les données originales

Après avoir configuré ces paramètres, cliquez sur « OK » pour appliquer le regroupement standard. Power Query générera une formule M qui utilise la fonction Table.Group(). Cette formule apparaîtra dans la barre de formule en haut de l’éditeur. Le résultat sera une nouvelle table avec deux colonnes :

• La colonne avec les valeurs uniques trouvées dans le champ sélectionné
• Une colonne contenant des tables imbriquées avec toutes les lignes correspondant à chaque valeur unique

Ce regroupement standard, cependant, ne fonctionne qu’avec des correspondances exactes. Pour obtenir un regroupement basé sur la similarité, nous devons modifier la formule générée et la transformer en un regroupement flou, en implémentant ainsi une correspondance floue plus flexible.

Modifier la formule pour le regroupement flou

L’étape cruciale pour implémenter le regroupement flou consiste à modifier manuellement la formule générée par le regroupement standard. Cela est nécessaire car l’interface utilisateur de Power Query n’offre pas de bouton direct pour le regroupement flou. Après avoir appliqué le regroupement standard, observez la barre de formule en haut de l’éditeur. Vous verrez une formule similaire à celle-ci :

= Table.Group(#"Type modifié", {"NomColonne"}, {{"Données", each _, type table [NomColonne=nullable text]}})

Pour la convertir en un regroupement flou, vous devez :

1. Changer Table.Group en Table.FuzzyGroup
2. Ajouter un quatrième paramètre qui définit les options du regroupement flou

La formule modifiée devrait apparaître ainsi :

= Table.FuzzyGroup(#"Type modifié", {"NomColonne"}, {{"Données", each _, type table [NomColonne=nullable text]}}, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.8, TransformationTable=Traduction])

Les options du quatrième paramètre contrôlent le comportement du regroupement flou :

• IgnoreCase : lorsque réglé sur true, le regroupement ignore les différences entre majuscules et minuscules
• IgnoreSpace : lorsque réglé sur true, les espaces sont ignorés lors de la comparaison
• Threshold : une valeur entre 0 et 1 qui détermine à quel point deux chaînes doivent être similaires pour être regroupées (0.8 est un bon point de départ)
• TransformationTable : le nom de la requête qui contient la table de traduction

Après avoir modifié la formule, appuyez sur Entrée ou cliquez sur la coche à côté de la barre de formule pour appliquer la modification. Power Query effectuera le regroupement flou selon les paramètres spécifiés.

Il est important de noter que la valeur de seuil nécessite des expérimentations. Une valeur trop élevée (proche de 1) exigera une similarité presque parfaite, tandis qu’une valeur trop basse (proche de 0) pourrait regrouper des éléments qui ne devraient pas être considérés comme similaires. Cette opération d’agrégation basée sur la similarité des chaînes est le cœur du regroupement flou.

Configurer les options de similarité

Le succès du regroupement flou dépend en grande partie de la bonne configuration des options de similarité. Ces options déterminent quels éléments seront considérés comme similaires et donc regroupés ensemble. L’option Threshold (seuil) est particulièrement importante. Elle représente le score de similarité minimum (de 0 à 1) nécessaire pour que deux chaînes soient considérées comme équivalentes :

• Une valeur de 1.0 nécessite une correspondance exacte (équivalent au regroupement standard)
• Une valeur de 0.0 regrouperait tous les éléments ensemble (rarement utile)
• Des valeurs entre 0.7 et 0.9 sont généralement plus efficaces pour la plupart des applications

Le choix de la valeur optimale dépend de la nature de vos données :

• Pour des données avec de petites variations orthographiques : essayez avec 0.8-0.9
• Pour des variations plus significatives dans la formulation : essayez avec 0.6-0.8
• Pour des concepts liés mais exprimés différemment : essayez avec 0.5-0.7

Les options IgnoreCase et IgnoreSpace sont plus simples à configurer :

• IgnoreCase=true : utile dans la plupart des cas, car les différences entre majuscules et minuscules indiquent rarement des significations différentes
• IgnoreSpace=true : utile lorsque les espaces sont incohérents (ex. « base de données » vs « basededonnees »)

Il est conseillé de commencer par des paramètres conservateurs (seuil élevé) et de réduire progressivement la valeur si nécessaire. Après chaque modification, examinez attentivement les résultats pour vérifier que le regroupement est logique et cohérent avec vos attentes.

Gardez à l’esprit que vous pouvez toujours revenir en arrière et modifier ces paramètres si les résultats ne sont pas satisfaisants. Le processus d’optimisation des options de similarité est souvent itératif et nécessite des expérimentations. Certains algorithmes de similarité, comme l’algorithme de similarité de Jaccard, peuvent être particulièrement efficaces pour certains types de données, il vaut donc la peine d’explorer différentes options.

Développer les résultats du regroupement

Après avoir appliqué le regroupement flou, vous obtiendrez un tableau à deux colonnes : la colonne des valeurs regroupées et une colonne contenant des tables imbriquées avec toutes les données originales. Pour rendre ces résultats plus utilisables, vous devez développer les tables imbriquées.

Pour développer les résultats :

1. Dans la colonne contenant les tables imbriquées, cliquez sur l’icône d’expansion (deux flèches divergentes) dans l’en-tête de colonne
2. Dans la boîte de dialogue qui apparaît, sélectionnez les colonnes que vous souhaitez inclure dans les résultats développés
3. Choisissez de conserver ou de supprimer le préfixe du nom de la colonne originale
4. Cliquez sur « OK » pour appliquer l’expansion

Ce processus d’expansion de tableau transformera la structure imbriquée en un tableau plat avec toutes les données originales, mais désormais organisées selon le regroupement flou appliqué. Chaque ligne affichera la valeur regroupée ainsi que les données originales correspondantes.

Si la table originale contenait de nombreuses colonnes, vous pourriez vouloir sélectionner uniquement les plus pertinentes lors de l’expansion pour maintenir les résultats gérables. Vous pouvez toujours modifier cette sélection ultérieurement si nécessaire. Dans certains cas, il peut également être utile d’envisager la suppression de colonnes non nécessaires pour simplifier davantage l’ensemble de données.

L’expansion des résultats est particulièrement utile lorsque vous souhaitez :

• Visualiser toutes les valeurs originales qui ont été regroupées ensemble
• Vérifier l’exactitude du regroupement flou
• Effectuer des analyses supplémentaires sur les données regroupées

Préparer les données pour la visualisation ou le reporting

Après l’expansion, il est conseillé de réorganiser les colonnes de manière logique pour faciliter l’interprétation des résultats. Vous pouvez le faire en faisant glisser les en-têtes de colonnes à la position souhaitée ou en utilisant l’option « Déplacer » dans le menu contextuel des colonnes. Cette étape est importante pour créer une série de données bien disposée et ordonnée qui sera plus facile à analyser et à présenter.

À ce stade, vous pourriez également envisager de standardiser les valeurs de certaines colonnes pour garantir la cohérence de vos rapports. Par exemple, vous pourriez vouloir uniformiser le format des champs de date ou vous assurer que tous les noms sont dans un format cohérent (ex. « Nom, Prénom »). Ces opérations de nettoyage final contribueront à améliorer la qualité globale de votre ensemble de données.

Charger les résultats dans Excel

Une fois le regroupement flou terminé et la sortie configurée comme souhaité, il est temps de charger les résultats à nouveau dans Excel pour l’analyse finale ou la présentation. Pour charger les résultats :

1. Dans l’Éditeur Power Query, allez à l’onglet « Accueil » du ruban
2. Cliquez sur le bouton « Fermer et charger » pour envoyer les données directement à Excel
3. Ou bien, cliquez sur la flèche sous « Fermer et charger » et sélectionnez « Fermer et charger dans… » pour plus d’options

Dans la boîte de dialogue « Importer les données », vous pouvez choisir :

• Tableau : charge les données sous forme de tableau Excel formaté (option recommandée)
• Tableau croisé dynamique : crée directement un tableau croisé dynamique à partir des données regroupées
• Connexion uniquement : crée uniquement une connexion aux données sans les charger dans une feuille
• Ajouter ces données au modèle de données : utile pour des analyses plus complexes ou pour une utilisation avec Power Pivot

Sélectionnez également l’emplacement où vous souhaitez charger les données :

• Feuille de calcul existante : spécifiez une cellule dans une feuille existante
• Nouvelle feuille de calcul : crée une nouvelle feuille pour les résultats

Après avoir confirmé vos choix, Excel chargera les données regroupées à l’emplacement spécifié. Les données conserveront un lien dynamique avec la requête Power Query, ce qui signifie que vous pourrez actualiser les résultats si les données source changent.

Pour actualiser les données à l’avenir :

1. Sélectionnez n’importe quelle cellule dans le tableau des résultats
2. Allez dans l’onglet « Données » du ruban
3. Cliquez sur « Actualiser tout » ou « Actualiser » dans le groupe « Requêtes et connexions »

Ceci réexécutera la requête Power Query, appliquant à nouveau le regroupement flou aux données mises à jour. Cette fonctionnalité de mise à jour automatique est particulièrement utile lorsque vous travaillez avec des données qui changent fréquemment ou lorsque vous souhaitez fusionner des requêtes provenant de différentes sources.

Vérifier et affiner les résultats

Après avoir chargé les résultats dans Excel, il est essentiel de vérifier l’exactitude du regroupement flou et d’apporter les affinements nécessaires. Même avec les meilleurs paramètres, le regroupement automatique pourrait ne pas être parfait du premier coup. Voici quelques stratégies pour vérifier et améliorer les résultats :

1. Examinez les groupes créés : triez les données par la valeur regroupée et vérifiez que tous les éléments de chaque groupe sont effectivement liés. Recherchez les anomalies ou les éléments qui semblent déplacés.
2. Identifiez les faux positifs : éléments différents qui ont été regroupés par erreur. Ceux-ci indiquent que le seuil de similarité pourrait être trop bas.
3. Recherchez les faux négatifs : éléments similaires qui n’ont pas été regroupés comme prévu. Ceux-ci suggèrent que le seuil pourrait être trop élevé.
4. Mettez à jour la table de traduction : si vous trouvez des erreurs récurrentes, ajoutez de nouvelles correspondances à la table de traduction pour les corriger explicitement.
5. Modifiez les paramètres de similarité : retournez à l’Éditeur Power Query et modifiez la valeur de seuil ou les autres options de similarité pour améliorer les résultats.

Pour modifier la requête et affiner le regroupement :

1. Sélectionnez n’importe quelle cellule dans le tableau des résultats
2. Allez dans l’onglet « Requête » ou « Données » du ruban
3. Cliquez sur « Modifier » pour rouvrir l’Éditeur Power Query
4. Modifiez la formule de regroupement flou ou la table de traduction
5. Fermez et rechargez à nouveau pour mettre à jour les résultats

L’affinage du regroupement flou est souvent un processus itératif qui demande plusieurs tentatives pour obtenir les résultats optimaux. N’hésitez pas à expérimenter avec différents paramètres jusqu’à trouver la combinaison qui convient le mieux à vos données spécifiques. Ce processus d’affinement contribuera à garantir l’intégrité de l’ensemble de données et la qualité de vos résultats finaux.

Cas d’utilisation pratiques du regroupement flou

Le regroupement flou dans Excel est un outil polyvalent avec de nombreuses applications pratiques dans divers secteurs. Voici quelques cas d’utilisation courants où cette fonctionnalité peut faire la différence :

• Nettoyage des bases de données clients : Identifier les doublons avec de petites variations dans les noms (ex. « Dupont S.A. » et « Dupont SA »)
  • Standardiser les noms d’entreprises acquises ou ayant des marques différentes
  • Unifier les enregistrements clients provenant de systèmes différents
• Analyse des retours et sondages : Regrouper les réponses à des questions ouvertes ayant une signification similaire
  • Identifier les thèmes communs dans les avis ou commentaires des clients
  • Catégoriser les suggestions ou les plaintes pour priorisation
• Gestion des stocks : Standardiser les noms de produits saisis manuellement
  • Identifier des produits similaires ou équivalents provenant de fournisseurs différents
  • Consolider des catégories de produits avec des nomenclatures légèrement différentes
• Analyse financière : Regrouper des postes de dépenses similaires mais enregistrés sous des noms différents
  • Standardiser les descriptions des transactions bancaires
  • Consolider les catégories de coûts pour des rapports plus précis
• Recherche et analyse de marché : Regrouper les noms de concurrents avec différentes variantes orthographiques
  • Standardiser les noms de lieux ou de régions géographiques
  • Unifier les termes du secteur ou le jargon technique

Pour chacun de ces cas d’utilisation, le regroupement flou offre un gain de temps significatif par rapport à la catégorisation manuelle, tout en réduisant le risque d’erreurs humaines. La clé du succès est d’adapter les paramètres de similarité et la table de traduction aux besoins spécifiques de votre scénario.

Limitations et alternatives au regroupement flou

Malgré sa puissance, le regroupement flou dans Power Query présente certaines limitations qu’il est important de connaître :

Principales limitations :

• Fonctionne mieux avec des textes relativement courts ; les phrases longues peuvent donner des résultats imprévisibles
• Nécessite Power Query, qui pourrait ne pas être disponible dans toutes les versions d’Excel
• Les performances peuvent se dégrader avec des ensembles de données très volumineux (des dizaines de milliers de lignes)
• L’algorithme de similarité n’est pas complètement transparent ou personnalisable
• Ne gère pas bien les comparaisons multilingues ou les caractères spéciaux

Pour les situations où le regroupement flou n’est pas adapté, considérez ces alternatives :

• Fonctions de recherche approximative : RECHERCHEV combiné avec des fonctions comme SIMILAIRE ou DIST.TEXTE
• Formules matricielles complexes pour identifier des correspondances approximatives
• Compléments tiers spécialisés dans les correspondances floues
• Approches externes à Excel : Logiciels spécialisés pour la déduplication des données
• Outils ETL (Extract, Transform, Load) avec des fonctionnalités de correspondance floue
• Solutions de base de données avec des capacités de recherche floue
• Langages de programmation comme Python ou R avec des bibliothèques pour la correspondance floue
• Méthodes hybrides : Prétraitement des données pour standardiser les formats communs
• • Regroupement initial basé sur des parties du texte (ex. premières lettres)
  • Combinaison de regroupement automatique et de révision manuelle

Si le regroupement flou dans Power Query ne répond pas à vos besoins, évaluez si l’une de ces alternatives pourrait être plus adaptée à votre cas spécifique. Dans de nombreux scénarios, une approche combinée utilisant différentes techniques peut offrir les meilleurs résultats.

Conclusions et meilleures pratiques

Le regroupement flou dans Power Query représente un outil puissant mais souvent sous-estimé dans l’arsenal d’Excel. Il permet d’automatiser un processus qui autrement prendrait des heures de travail manuel et serait sujet aux erreurs. Pour obtenir les meilleurs résultats avec le regroupement flou, considérez ces meilleures pratiques :

1. Préparez adéquatement les données : nettoyez les données avant le regroupement, en supprimant les formatages incohérents ou les caractères spéciaux non nécessaires.
2. Investissez dans la table de traduction : une table de traduction bien construite peut améliorer significativement les résultats, surtout pour les termes spécifiques au secteur ou les abréviations courantes.
3. Itérez et affine : ne vous attendez pas à des résultats parfaits du premier coup. Soyez prêt à expérimenter avec différents paramètres de similarité et à affiner le processus.
4. Vérifie les résultats : vérifiez toujours manuellement un échantillon des résultats pour vous assurer que le regroupement est logique et cohérent avec vos attentes.
5. Documentez le processus : prenez note des paramètres utilisés et des décisions prises, surtout si vous prévoyez de répéter le processus à l’avenir.
6. Considérez le contexte : adaptez les paramètres de similarité au contexte spécifique de vos données et au niveau de précision requis.
7. Conservez les données originales : conservez toujours une copie des données originales non regroupées pour référence future ou pour des itérations alternatives.

Le regroupement flou est particulièrement précieux à une époque de volume et de variété croissants de données. Maîtriser cette technique vous permettra de transformer des données désordonnées et incohérentes en informations structurées et utilisables, améliorant significativement la qualité de vos analyses et de vos rapports dans Excel.

Pubblicato in Excel