Un de mes amis travaille dans une entreprise d'analyse de la santé. Il se connecte souvent avec moi sur certains des problèmes réels auxquels il est confronté lorsqu'il travaille avec des données dans Excel.
Souvent, je convertis ses requêtes en didacticiels Excel sur ce site, car cela pourrait également être utile à mes autres lecteurs.
C'est aussi l'un de ces tutoriels.
Mon ami m'a appelé la semaine dernière avec le problème suivant :
Il y a des données d'adresse dans une colonne dans Excel, et je veux identifier/filtrer les cellules où l'adresse contient des chaînes de texte (mots) en double.
Voici l'ensemble de données similaire dans lequel il voulait filtrer les cellules contenant une chaîne de texte en double (celles avec des flèches rouges):
Ce qui rend cela difficile, c'est qu'il n'y a pas de cohérence dans ces données. Puisqu'il s'agit d'une compilation d'ensembles de données qui ont été créés manuellement par les commerciaux, il peut y avoir des variations dans l'ensemble de données.
Considère ceci:
- Toute chaîne de texte peut se répéter dans cet ensemble de données. Par exemple, il peut s'agir du nom de la zone ou du nom de la ville ou des deux.
- Les mots sont séparés par un espace et il n'y a aucune cohérence quant à savoir si le nom de la ville serait là après six ou huit mots.
- Il existe des milliers d'enregistrements comme celui-ci, et le besoin est de filtrer les enregistrements contenant des chaînes de texte en double.
Après avoir examiné de nombreuses options (telles que le texte dans les colonnes et les formules), j'ai finalement décidé d'utiliser VBA pour y parvenir.
J'ai donc créé une fonction VBA personnalisée (« IdDuplicate ») pour analyser ces cellules et me donner VRAI s'il y a un mot en double dans la chaîne de texte, et FAUX s'il n'y a pas de répétitions (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. Si le nombre est supérieur à 1, il renvoie VRAI; sinon, il renvoie FALSE.
En outre, 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 la 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 le code VBA correspondant :
Function 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 If 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 : fonction de fin
Merci Walter d'avoir suggéré une meilleure approche de ce code !
Comment utiliser ce code VBA
Maintenant que vous avez le code VBA, vous devez le placer dans le backend d'Excel, afin qu'il puisse fonctionner comme une fonction de feuille de calcul normale.
Voici les étapes pour mettre le code VBA sur le backend :
- Allez dans l'onglet Développeur.
- Cliquez sur Visual Basic (vous pouvez également utiliser le raccourci clavier ALT + F11)
- Dans le back-end de VB Editor qui s'ouvre, cliquez avec le bouton droit sur l'un des objets du classeur.
- Allez dans « Insérer » et cliquez sur « Module ». Cela insérera l'objet module pour le classeur.
- Dans la fenêtre Code du module, copiez et collez le code VBA mentionné ci-dessus.
Une fois que vous avez le code VBA dans le back-end, vous pouvez utiliser la fonction "IdDuplicates" comme n'importe quelle autre fonction de feuille de calcul normale.
Cette fonction prend un seul argument, qui est la référence de cellule de la cellule où vous avez le texte.
Le résultat de la fonction est TRUE (s'il y a des mots en double) ou FALSE (s'il n'y a pas de doublons). Une fois que vous avez cette liste de VRAI/FAUX, vous pouvez filtrer celles avec VRAI pour obtenir toutes les cellules qui contiennent des chaînes de texte en double.
Remarque : j'ai créé le code uniquement pour prendre en compte les mots de plus de trois caractères. Cela garantit que s'il y a des mots de 1, 2 ou 3 caractères (tels que 12 A, K G M ou L D A) dans la chaîne de texte, ceux-ci sont ignorés lors du comptage des doublons. Si vous le souhaitez, vous pouvez facilement modifier cela dans le code.
Cette fonction ne sera disponible que dans le classeur où vous avez copié le code dans le module. Si vous souhaitez que cela soit également disponible dans d'autres classeurs, vous devez copier et coller ce code dans ces classeurs. Alternativement, vous pouvez également créer un complément (activation qui rendrait cette fonction disponible dans tous les classeurs de votre système).
N'oubliez pas non plus d'enregistrer ce classeur dans l'extension .xlsm (car il contient un code de macro).
