un de mes amisJe suis souvent en contact avec lui au sujet de certains des problèmes réels auxquels il est confronté lorsqu'il travaille avec des données dans Excel.
Plusieurs fois, je traduis sa requête dans un didacticiel Excel sur ce site, car cela pourrait également être utile à mes autres lecteurs.
C'est aussi un de ces tutoriels.
Mon ami m'a appelé la semaine dernière avec les questions suivantes :
J'ai des données d'adresse dans une colonne dans Excel et je souhaite identifier/filtrer les cellules qui contiennent des chaînes de texte répétées (mots) dans l'adresse.
Voici un ensemble de données similaire dans lequel il souhaite filtrer les cellules (cellules avec des flèches rouges) contenant des chaînes de texte répétées :
Ce qui rend cela difficile maintenant, c'est que les données ne sont pas cohérentes.Comme il s'agit d'une compilation d'ensembles de données créés manuellement par les commerciaux, il peut y avoir des divergences dans les ensembles de données.
Pensez-y:
- Toute chaîne de texte peut être répétée dans cet ensemble de données.Par exemple, il peut s'agir d'un nom de région ou d'un nom de ville, ou des deux.
- Les mots sont séparés par des espaces et il n'y a pas de cohérence dans le fait que le nom de la ville soit après six ou huit caractères.
- Il existe des milliers d'enregistrements de ce type et vous devez filtrer ceux qui contiennent des chaînes de texte répétées.
Après avoir envisagé de nombreuses options telles que le texte dans les colonnes et les formules, j'ai finalement décidé d'utiliser VBA pour faire le travail.
J'ai donc créé une fonction VBA personnalisée ("IdDuplicate") pour analyser ces cellules et me donner TRUE s'il y a des mots en double dans la chaîne de texte et FALSE s'il n'y a pas de doublons (comme indiqué ci-dessous):
Cette fonction personnalisée analyse chaque mot de la chaîne de texte et vérifie combien de fois il apparaît dans le texte.Renvoie TRUE si le nombre est supérieur à 1 ;Sinon retourner FALSE.
De plus, il a été créé pour ne compter que les mots de plus de trois caractères.
Une fois que j'ai les données VRAI/FAUX, je peux facilement filtrer tous les enregistrements qui sont VRAI.
Maintenant, laissez-moi vous montrer comment faire cela dans Excel.
Code VBA pour fonction personnalisée
Cela se fait en créant une fonction personnalisée dans VBA.Cette fonction peut ensuite être utilisée comme n'importe quelle autre fonction de feuille de calcul dans Excel.
Voici son code VBA :
Fonction IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Étape -1 Si Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB : Fin de la fonction
Comment utiliser ce code VBA
Maintenant que vous avez le code VBA, vous devez le placer dans le backend d'Excel afin qu'il fonctionne comme une fonction de feuille de calcul normale.
Voici les étapes pour mettre le code VBA dans le backend :
- Allez dans l'onglet développeur.
- Cliquez sur Visual Basic (vous pouvez aussi utiliser le raccourci clavier ALT+F11)
- Dans le backend de l'éditeur VB ouvert, cliquez avec le bouton droit sur n'importe quel objet du classeur.
- Allez dans "Insérer" et cliquez sur "Module".Cela insérera l'objet module du classeur.
- Dans la fenêtre de code du module, copiez et collez le code VBA mentionné ci-dessus.
Une fois que vous avez le code VBA dans le backend, vous pouvez utiliser la fonction - 'IdDuplicates' comme n'importe quelle autre fonction de feuille de calcul régulière.
Cette fonction prend un paramètre, la référence de cellule de la cellule où vous avez le texte.
Le résultat de cette fonction est VRAI (s'il contient des mots répétés) ou FAUX (s'il n'y a pas de répétitions).Une fois que vous avez cette liste TRUE/FALSE, vous pouvez filtrer celles avec TRUE pour obtenir toutes les cellules contenant des chaînes de texte répétées.
Remarque : Le code que j'ai créé ne tient compte que des mots de plus de trois caractères.Cela garantit que s'il y a des mots dans la chaîne de texte contenant 1, 2 ou 3 caractères (par exemple 12 A, KGM ou LDA), ces mots sont ignorés lors du comptage des doublons.Vous pouvez facilement modifier ce paramètre dans le code si nécessaire.
Cette fonctionnalité n'est disponible que dans le classeur où vous avez copié le code dans le module.Si vous souhaitez que ce code soit également disponible dans d'autres classeurs, vous devez copier et coller ce code dans ces classeurs.Vous pouvez également créer un complément (en l'activant, la fonctionnalité sera disponible dans tous les classeurs de votre système).
N'oubliez pas non plus d'enregistrer ce classeur avec une extension .xlsm (car il contient du code de macro).
