Ignorer les accents dans les comparaisons de textes Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonction VBA de recherche pour ignorer les accents

Dans cette formation VBA Excel qui poursuit les travaux sur l'extraction des enregistrements avec leurs images, nous proposons d'améliorer les performances des recherches textuelles. Jusqu'alors, une différence d'accent conduisait à des extractions infructueuses. Nous proposons de pallier ce problème en bâtissant une fonction VBA Excel, capable d'ignorer ces caractères latins. L'objectif est de réaliser des comparaisons souples, sur des termes de recherche. Et dans le prochain volet, nous exploiterons ces résultats, pour créer un véritable moteur de recherche Excel, capable de raisonner sur un assemblage de mots-clés.



Comme l'illustre la capture ci-dessus de l'application finalisée, il est proposé à l'utilisateur d'effectuer des recherches d'idées de sorties, sur le département référencé dans la base de données. Et comme vous le remarquez, la recherche est réalisée sur la Drôme, saisie avec un accent circonflexe. Cependant, ce département est inscrit sans aucun accent dans la base de données. Et pourtant, les résultats de recherche sont concluants puisque toutes les données correspondantes sont extraites.

Source et présentation de la problématique
Comme il s'agit de tester une fonction VBA Excel sur des données à comparer avec ou sans accents, nous avons besoin de récupérer une base existante.

• Télécharger le classeur comparer-texte-sans-accents.xlsm en cliquant sur son lien,
• Puis, l'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur ne propose qu'une seule feuille nommée Importation. Les formules d'extraction sur la base d'une recherche textuelle, sont déjà posées. Vous notez la présence d'un calcul intermédiaire en colonne F. Il consiste à repérer tous les enregistrements par des numéros incrémentés, pour lesquels le département concorde avec le terme de recherche saisi en H4. C'est une technique que nous avions notamment mise en oeuvre dans la formation Excel pour éliminer les doublons d'une liste. La formule est la suivante :

=SIERREUR(SI(CHERCHE($H$4;D3)>0; MAX($F$2:F2)+1; ''); '')

La fonction Cherche est exploitée pour retourner une position indiquant si le terme a été trouvé dans la cellule désignée, celle du département.

Ce résultat intermédiaire est exploité dans la zone d'extraction, située à partir de la ligne 7, entre les colonnes H et K. Et vous remarquez que tous les enregistrements concordants sont importés, avec leurs informations de champ restituées. Le calcul de départ est le suivant :

=SIERREUR(INDEX(bd;EQUIV(LIGNE(H1); F:F;0)-2;1);'')

C'est l'imbrication des fonction Excel Index et Equiv qui permet d'extraire les résultats en fonction du mot clé de recherche. La fonction Equiv réalise la recherche du numéro incrémenté grâce à la fonction Excel Ligne. Elle retourne ainsi les indices de ligne où se trouvent les enregistrements concordants. La fonction Index n'a plus qu'à réaliser le croisement avec l'indice de colonne connu, pour extraire l'information de champ demandée.

• En cellule H4, taper le terme de recherche suivant : Ardèche, avec l'accent grave,

Après validation, vous constatez que toutes les idées de sorties recensées pour le département de l'Ardèche, ont parfaitement été importées dans la zone d'extraction. La fonction Excel Cherche a bien trouvé la correspondance sur ce terme en colonne D.

• En cellule H4, taper le même terme mais cette fois sans accent, soit : Ardeche,

Dans ce contexte pourtant similaire, plus aucun résultat n'apparaît. La zone d'extraction est vidée. Pour une différence aussi mineure, les calculs d'extraction ne fonctionnent plus. Le département de l'Ardèche est référencé avec accent dans la base de données. La même saisie sans accent n'est donc pas trouvée dans la colonne de recherche.

C'est pourquoi nous proposons de bâtir une fonction VBA Excel pour supprimer les accents. Elle sera exploitable sur la feuille de calcul, comme n'importe quelle autre fonction Excel. Nous l'utiliserons en colonne A de la feuille Importation, pour réaliser une copie des départements, purgés de leurs accents. Nous adapterons également le calcul intermédiaire en colonne F. Il devra réaliser la recherche du terme saisi, purgé de ses accents, non plus dans la colonne D, mais dans la colonne A. L'utilisateur pour alors saisir les expressions à sa guise, avec ou sans accents.

Supprimer les accents par le code VBA
La fonction que nous devons créer doit traiter l'information de la cellule qui lui est passée en paramètre. Il s'agit donc de déclarer une variable de type String dans sa signature.

• Basculer dans l'éditeur de code Visual Basic Excel par le raccourci clavier ALT + F11,
• Dans l'arborescence de l'explorateur de projet, sur la gauche de l'éditeur, déployer l'affichage du dossier Modules,
• Puis, double cliquer sur l'élément Module1,

Nous avions en effet prévu un module en amont pour recevoir le code de la fonction VBA Excel que nous allons créer. Cette action a permis d'afficher sa feuille de code au centre de l'écran.

• Dans sa feuille de code, au centre de l'écran, créer la fonction sansAccent, comme suit :

Function sansAccent(chaine As String) As String

End Function

Comme nous l'avions appris dans la formation VBA pour créer des fonctions, c'est le mot clé Function qui permet d'initialiser une telle procédure particulière. La mission d'une fonction est de réaliser un traitement selon le ou les paramètres qui lui sont passés. Ici, le paramètre attendu correspond au texte de la cellule désignée. Donc nous déclarons la variable chaine comme un String. Après traitement, la fonction doit retourner la valeur résultante, qui sera une chaîne de texte retravaillée. C'est pourquoi nous typons la fonction elle-même comme une variable de type String.

Il s'agit désormais de déclarer les variables nécessaires au traitement.

• Dans les bornes de la fonction VBA, ajouter les déclarations de variables suivantes :

Dim ch_avec As String: Dim ch_sans As String
Dim tampon As String: Dim i As Byte: Dim position As String

Les variables ch_avec et ch_sans sont prévues pour énumérer respectivement les caractères recensés avec accents et leurs homologues sans accent. Si un accent de la première variable est trouvé dans la chaîne passée en paramètre, il sera remplacé par la lettre correspondante, identifiée dans la seconde variable. La variable tampon doit servir à mémoriser les traitements intermédiaires, au travers d'une boucle parcourant l'ensemble des caractères. A l'issue, elle retournera le résultat à la variable de la fonction, identifiée par son propre nom. For logiquement, la variable i est déclarée pour parcourir l'ensemble des lettres, dans une boucle. Enfin, la variable position permettra de déterminer si un accent est trouvé dans la chaîne.

Nous devons maintenant réaliser les affectations nécessaires pour ensuite débuter le traitement.

• A la suite du code de la fonction VBA, ajouter les affectations suivantes :

ch_avec = 'ÉÈÊËÔéèêëàçùôûïî'
ch_sans = 'EEEEOeeeeacuouii'
tampon = chaine

Dans la première variable, nous énumérons les lettres accentuées les plus répandues, y compris en majuscules. Dans la deuxième variable, nous énumérons ces mêmes lettres, dans le même ordre et sans accent. Ainsi, lorsqu'un accent sera trouvé, il s'agira de remplacer la lettre identifiée par son homologue, située à la même position dans la deuxième variable. En effet, nous récupérons le contenu de la cellule passée en paramètre dans la variable tampon, pour traitement.

Il s'agit désormais d'enclencher une boucle parcourant l'ensemble des caractères contenus dans la variable tampon. Cette boucle doit initialiser son traitement sur la position 1, pour le premier caractère. Elle doit le poursuivre jusqu'au dernier. Cette information peut être obtenue grâce à la fonction VBA Len. Cette dernière retourne le nombre de caractères contenus dans la chaîne qui lui est passée en paramètre. Comme les bornes sont connues, il est indiqué d'exploiter une boucle d'instruction For.

• A la suite du code VBA, ajouter la boucle suivante :

For i = 1 To Len(tampon)

Next i

A l'intérieur de cette boucle, pour chaque lettre passée en revue, il s'agit de vérifier la présence éventuelle d'un accent. Si elle est confirmée, cet accent doit être remplacé par son homologue et concaténé dans la variable tampon.

• Dans les bornes de la boucle, ajouter les deux instructions VBA suivantes :

position = InStr(ch_avec, Mid(tampon, i, 1))
If position > 0 Then Mid(tampon, i, 1) = Mid(ch_sans, position, 1)

Nous exploitons tout d'abord la fonction Instr que nous avions notamment apprise au travers de la formation pour créer le jeu du pendu en VBA Excel. Elle permet de retourner la position d'une occurrence cherchée, passée en deuxième paramètre. Cette occurrence n'est autre que la lettre en cours de lecture dans la chaîne. Nous y accédons grâce à la fonction VBA Mid qui permet de prélever un bout de chaîne. Ce prélèvement s'effectue dans la variable tampon, à partir du caractère en cours de lecture, renseigné par la variable i. Et il s'effectue sur une longueur d'un seul caractère, comme précisé en troisième argument de la fonction Mid. Cette lettre est donc cherchée dans la variable ch_avec, grâce à la fonction InStr.

Si la position retournée est supérieure à zéro, cela signifie que la présence d'un accent a été décelée. Dans ce cas, nous remplaçons cette lettre accentuée (Mid(tampon,i, 1) =) par son homologue sans accent, à la position trouvée dans la chaîne avec accents (Mid(ch_sans,position, 1)).

A chaque passage donc, nous sommes censés remplacer les accents lorsqu'ils existent. En conséquence, après le traitement de la boucle, nous devrions récupérer une chaîne de texte purgée de ses accents.

Nous devons donc affecter ce résultat à la variable de la fonction sansAccent. C'est ainsi que cette dernière retournera le résultat de son traitement, dans la cellule où elle est écrite.

• Après la boucle, soit après l'instruction Next i, ajouter l'affectation de la fonction :

sansAccent = tampon

Le résultat étant retourné, il est temps de tester cette fonction.

• Enregistrer les modifications (CTRL + S),
• Basculer sur la feuille Importation du classeur (ALT + F11),
• Sélectionner la première cellule du calcul, soit la cellule A3,
• Taper le symbole = pour débuter le calcul,
• Saisir les premières lettres de la fonction sansAccent,

Comme vous le remarquez, elle est reconnue par Excel et proposée contextuellement dans l'info-bulle.

• Valider la proposition ou terminer la saisie suivie d'une parenthèse, soit sansAccent(,
• Désigner le premier département, soit la cellule D3,
• Fermer la parenthèse de la fonction sansAccent,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Puis, double cliquer sur la poignée de la cellule pour répliquer le calcul sur la hauteur du tableau,

Comme vous le constatez, l'information du département est restituée quasiment à l'identique, puisque les accents ont disparu. Les lettres accentuées ont été remplacées par leurs homologues, issus de la variable ch_sans.



Notre fonction VBA pour supprimer les accents est parfaitement fonctionnelle. Il va désormais s'agir de l'appliquer pour réaliser des comparaisons insensibles aux caractères latins. A ce stade en revanche, comme elle est écrite dans ce classeur, elle n'est reconnue et disponible que pour ce dernier. Afin de rendre la fonction portable, il est nécessaire de l'ajouter dans un complément Excel (Xlam). C'est ce que nous avait appris la formation VBA Excel pour créer des fonctions disponibles à chaque utilisation.

Le code VBA complet de notre fonction sansAccent est le suivant :

Function sansAccent(chaine As String) As String
Dim ch_avec As String: Dim ch_sans As String
Dim tampon As String: Dim i As Byte: Dim position As String

ch_avec = 'ÉÈÊËÔéèêëàçùôûïî'
ch_sans = 'EEEEOeeeeacuouii'
tampon = chaine

For i = 1 To Len(tampon)
position = InStr(ch_avec, Mid(tampon, i, 1))
If position > 0 Then Mid(tampon, i, 1) = Mid(ch_sans, position, 1)
Next i

sansAccent = tampon
End Function

Ignorer les accents dans les recherches de mots clés
Il s'agit désormais d'adapter le calcul intermédiaire, grâce à cette nouvelle fonction sansAccent, pour que les comparaisons soient insensibles aux caractères latins. L'utilisateur pourra saisir les termes de recherche à sa guise, avec ou sans accents.

• Sélectionner la première cellule du calcul intermédiaire, soit la cellule F3,
• Dans la fonction Cherche, remplacer le second argument D3, par la cellule A3,
• Valider la modification par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée de la cellule pour répliquer le calcul sur tout le tableau,

Si vous aviez conservé l'ancien terme de recherche, soit Ardeche sans accent, vous voyez apparaître instantanément tous les résultats de recherche. Mais inversement cette fois, si vous tapez le même terme avec l'accent, tous les résultats disparaissent car le terme n'est plus trouvé. Pour que l'extraction soit insensible aux caractères latins, le terme de recherche doit être traité par la fonction sansAccent.

• Sélectionner de nouveau la première cellule du calcul intermédiaire, soit la cellule F3,
• Dans la fonction Cherche, encadrer la cellule cherchée H4 avec la fonction sansAccent, soit :

sansAccent($H$4)

• Valider la modification par le raccourci clavier CTRL + Entrée,
• Puis, répliquer la formule sur tout le tableau en double cliquant sur la poignée de la cellule,

Les idées de sorties pour le département de l'Ardèche tapé avec accent sont parfaitement extraites. Indifféremment, si vous tapez désormais le même département sans l'accent, les mêmes résultats sont offerts.

• Dans la cellule H4 de la zone de recherche, taper le terme Isere sans accent,

Bien que ce département soit recensé en base de données avec un accent, tous les résultats de la recherche sont concordants, et les enregistrements correspondants sont extraits. Grâce à la création de la fonction VBA Excel sansAccent, nous sommes en mesure de réaliser des comparaisons sur des chaînes de caractères, insensibles aux différences d'accentuation. Nous exploiterons ces résultats dans la prochaine formation VBA Excel pour bâtir un véritable moteur de recherche. Celui-ci bien entendu devra être en mesure d'ignorer les caractères latins. Mais surtout, il devra permettre à l'utilisateur de saisir à la suite, autant de mots clés qu'il le souhaite. Il devra fournir l'extraction de tous les enregistrements qui correspondent à l'ensemble des mots clés tapés, pour des recherches affinées.