Interdire la saisie d'accents dans un formulaire Excel

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


Sujets que vous pourriez aussi aimer :


Contrôler les caractères proscrits

Les caractères latins finissent toujours par être problématiques en informatique. Parmi eux, les accents sont les plus redoutés. Les anglo-saxons ne les exploitent pas dans leur langue et langage. Ces accents sont par exemple proscrits dans les adresses mail et les url de sites Web. Ils posent souvent des soucis d'encodage aux développeurs.



Dans cette formation, nous apportons la solution pour les débusquer tous à l'aide d'une seule formule.

Source et présentation de la problématique
Pour la mise en place de cette solution, nous proposons de réceptionner un classeur offrant des données adaptées.

• Télécharger le classeur controler-caracteres-interdits.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons donc un classeur muni de deux feuilles nommées respectivement Clients et Formulaire. La feuille Clients héberge une base de données des clients de l'entreprise. Ils peuvent accéder à leur compte par le biais d'une adresse mail, rattachée à la société et qui leur est offerte. Cette adresse est construite par concaténation du nom et du prénom selon la formule suivante :

=MINUSCULE(D3 & '.' & E3 & '@' & 'sti.com')

Mais comme vous l'avez constaté, le souci provient des accents émanant du nom ou du prénom. Ils ne sont pas gérés dans cette formule. Nous avions d'ailleurs appris à exploiter la fonction Substitue par imbrications afin de les neutraliser tous. Ici, l'idée consiste à repérer ces anomalies par mise en forme conditionnelle. La syntaxe de sa règle sera forcément particulière. Une seule formule doit être en mesure de passer en revue chacun des caractères. Et seuls les calculs matriciels sont capables de réaliser des traitements récursifs assimilés.

La deuxième feuille héberge un extrait du formulaire d'inscription que nous avions développé à l'occasion d'une formation.



Des contrôles de validité sont en place sur chacun des champs. La soumission n'est autorisée que lorsque toutes les saisies sont jugées conformes. Par exemple, un code postal est nécessairement composé de 5 chiffres tandis qu'un numéro de téléphone doit offrir 10 chiffres.

• Cliquer sur le champ du Mail pour sélectionner sa cellule E15,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la boîte de dialogue qui suit, activer l'onglet Options,

L'adresse Mail doit nécessairement proposer un point et le symbole de l'arobase. C'est entre autres ce que contrôle la règle de validité en vigueur sur ce champ :

=ET(CHERCHE('@'; E15)>0; CHERCHE('.'; E15)>0; NBCAR(E15)>6)

Mais à aucun moment elle ne vérifie si des accents sont insérés par mégarde. Ce contrôle doit donc être renforcé.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur le formulaire,

Maintenant que la problématique est posée, il s'agit d'avancer la solution à ces maux.

Chercher les accents dans un texte
Dans une chaîne de texte, la position des caractères latins potentiels n'est pas connue à l'avance. De plus, la nature de l'accent lui-même s'il existe, ne peut être anticipée. Nous n'avons donc d'autre solution que de passer en revue l'ensemble des caractères composant la chaîne. Et si l'un d'entre eux est détecté, il faut le faire savoir. La fonction Excel Cherche est nécessaire :

=Cherche(Texte_cherché; Chaîne_de_recherche)

Mais elle ne permet d'effectuer la recherche que d'un seul accent à la fois. Comme ils sont nombreux, les imbrications nécessaires compliqueraient fortement la syntaxe. C'est pourquoi, nous proposons de lui imbriquer la fonction Excel STXT dans une logique de calcul matriciel.

=STXT(Texte; Position_de_départ; longueur)

Sur une longueur à définir en troisième paramètre, elle permet d'extraire un fragment de la chaîne à passer en premier paramètre, à partir d'une position de départ à fixer en deuxième paramètre. Pour analyser chaque caractère indépendamment, la longueur doit être fixée à un caractère. C'est la position de départ qui doit varier de façon récursive incrémentée de manière à les passer tous en revue, du premier au dernier.

• En bas de la fenêtre Excel, cliquer sur l'onglet Clients pour revenir sur sa feuille,
• Sélectionner toutes les adresses mail, soit la plage de cellules H3:H41,
• En haut de la fenêtre Excel, cliquer sur l'onglet Accueil pour activer son ruban,
• Dans la section Styles du ruban, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Cliquer alors dans la zone de saisie juste en dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction matricielle pour la somme suivie de deux parenthèses, soit : SommeProd((,

En effet, nous souhaitons engager un traitement récursif. Il ne s'agit pas d'analyser les cellules de deux matrices ligne à ligne mais de décliner ce principe pour analyser chaque caractère de chaque cellule.

• Sélectionner le premier mail en cliquant sur sa cellule H3,
• Enfoncer trois fois de suite la touche F4 du clavier pour la libérer totalement,

De fait, les deux dollars disparaissent. Chaque mail doit être scruté. Donc, la cellule étudiée doit se déplacer en même temps que l'analyse de la mise en forme conditionnelle progresse sur les lignes du dessous.

• Taper alors l'inégalité suivante : <>'',

La première condition posée sur cette première matrice assimilée, consiste à réaliser l'étude dans la mesure où la cellule possède effectivement un contenu.

• Fermer la parenthèse de la première condition matricielle,
• Puis, taper le symbole de l'étoile (*) suivi d'une parenthèse ouvrante,

C'est ainsi, dans une syntaxe particulière que nous avons déjà apprise que nous allons pouvoir recouper une seconde condition matricielle.

• Saisir la fonction de test d'erreur suivie d'une parenthèse, soit : EstErreur(,

Ainsi, selon l'information retournée (Vrai ou Faux), nous saurons si la présence d'accents a été décelée.

• Taper la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Saisir la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Sélectionner le premier mail à découper, soit la cellule H3 complètement défigée,
• Taper un point-virgule (;) pour passer dans l'argument du point de départ,
• Saisir la fonction donnant la ligne d'une référence suivie d'une parenthèse, soit : Ligne(,
• Saisir la fonction d'interprétation des coordonnées suivie d'une parenthèse, soit : Indirect(,
• Inscrire alors la syntaxe suivante: '1:'&NBCAR(H3),

Cette notation permet de simuler une plage de cellules sur la longueur des caractères contenus dans l'adresse Mail. Comme elle est inscrite dans un calcul matriciel, celui-ci évaluera chacun des indices retournés par la fonction Ligne. Il en résulte un point de départ qui progresse d'un caractère à chaque passage pour l'analyse du mail.

• Fermer la parenthèse de la fonction Indirect puis fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Puis, saisir le chiffre 1,

Comme nous le disions, dans ce raisonnement matriciel récursif, à chaque progression dans la chaîne, nous prélevons un seul de ses caractères. L'objectif, une fois restitué à la fonction Cherche, est de détecter s'il s'agit de l'un des accents référencés.

• Fermer alors la parenthèse de la fonction Stxt,
• Puis, taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Saisir alors la chaîne suivante sans omettre les guillemets : 'éèêëùâôàî',

Il s'agit de l'énumération des accents recensés. Nous en avons même profité pour glisser un espace entre les deux dernières lettres accentuées. Ainsi, pour chaque caractère du mail étudié par le calcul matriciel, la recherche de correspondance sera effectuée dans cette chaîne statique. Si la correspondance est avérée, la fonction EstErreur renverra Faux. En conséquence, nous saurons que le mail attribué est erroné puisqu'il porte au moins un accent.

• Fermer la parenthèse de la fonction Cherche et fermer la parenthèse de la fonction EstErreur,
• Taper ensuite l'égalité suivante : =Faux, en guise de critère à satisfaire,
• Fermer la parenthèse de la seconde condition matricielle,
• Puis, fermer la parenthèse de la fonction SommeProd,

La syntaxe de la règle que nous avons bâtie pour déceler la présence d'accents est donc la suivante :

=SommeProd((H3 <> '')*(EstErreur(Cherche(Stxt(H3; Ligne(Indirect('1:'&NBCAR(H3))); 1); 'éèêëùâôàî'))=Faux))

Si la règle est satisfaite, donc si un accent est trouvé, la cellule du mail correspondant doit ressortir dynamiquement et de façon évidente.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un rouge foncé,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un gris très clair pour la couleur du texte,
• Puis, valider ces attributs de format par le bouton Ok,

De retour sur la première boîte de dialogue, nous pouvons visualiser l'apparence que revêtira chaque cellule portant des accents.

• Valider la création de cette règle de mise en forme conditionnelle par le bouton Ok,

De retour sur la base de données, tous les mails non conformes sont dynamiquement repérés. Si vous ajoutez un accent ou un espace dans l'un des noms ou prénoms, à validation, le mail ainsi reconstruit, surgit comme erroné. Il s'agit donc d'une solution fort précieuse pour entreprendre des travaux de nettoyage sur des bases de données.

Interdire la saisie des accents
Désormais, nous pouvons exploiter ces acquis pour contrôler la saisie des utilisateurs dans des formulaires par exemple.

• En bas de la fenêtre Excel, cliquer sur l'onglet Formulaire pour activer sa feuille,

Nous l'avons constaté en début de formation, une règle de validité multicritère existe pour contrôler la conformité de la saisie du mail en cellule E15. Il doit nécessairement proposer un point et une arobase ainsi qu'une certaine longueur. Nous devons lui ajouter la condition matricielle scrutant la présence d'accents prohibés.

• Cliquer sur le champ du Mail pour sélectionner sa cellule E15,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la zone de saisie de la règle de validité, adapter l'anicienne syntaxe :

=ET(CHERCHE('@'; E15)>0; CHERCHE('.'; E15)>0; NBCAR(E15)>6)

• Par la suivante :

=ET(CHERCHE('@'; E15)>0; CHERCHE('.'; E15)>0; NBCAR(E15)>6;SOMMEPROD((E15 <> '')*(ESTERREUR(CHERCHE(STXT(E15; LIGNE(INDIRECT('1:'&NBCAR(E15))); 1);'éèêëùâôàî'))=FAUX))=0)

Nous ajoutons donc le calcul matriciel à la suite des conditions à satisfaire et à recouper dans la fonction ET. Puis, nous ajoutons un critère à satisfaire (=0) sur le résultat matriciel de la fonction SommeProd. Celui-ci est implicite dans une règle de mise en forme conditionnelle. Il agit comme une double négation et indique qu'aucun espace ou accent n'a été détecté.

• Valider cette règle et ignorer l'alerte d'erreur si elle intervient,
• En E15, taper et valider une adresse mail contenant un ou des accents,

Comme vous pouvez le voir, nous avons considérablement renforcé le contrôle sur ce champ. La validation de l'adresse n'est pas autorisée en raison de la présence détectée d'accents.

• Cliquer sur le bouton Annuler,
• Puis, saisir et valider la même adresse sans accent,

Cette fois l'inscription du mail est acceptée.

Imposer la saisie en majuscules
Pour terminer et renforcer encore le contrôle de validité sur les champs du formulaire Excel, nous proposons d'ajouter une astuce intéressante. Elle consiste à imposer la saisie en majuscules. Le champ du nom semble dédié pour cette mise en oeuvre.

Comme vous le savez, Excel considère deux cellules identiques à partir du moment où elles sont écrites strictement de la même façon et ce, quelle que soit la casse. Par contre, si nous exploitons la fonction Exact dans la comparaison, Excel relèvera cette différence. Et donc, nous allons l'exploiter dans le contrôle de validité pour le champ du nom.

• Cliquer sur le champ du nom de famille pour sélectionner sa cellule C9,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Avec la liste déroulante de la zone Autoriser, choisir Personnalisé,
• Dans la zone Formule, saisir la syntaxe suivante :

=ET(NBCAR(C9)>3; EXACT(C9; MAJUSCULE(C9)))

Il s'agit d'une double condition à satisfaire grâce à la fonction ET. Tout d'abord, nous nous assurons que la saisie est suffisante grâce à la fonction NbCar. Ensuite, nous exploitons la fonction Exact. Elle permet de comparer deux cellules qui lui sont passées en paramètre. Ici, en premier paramètre, nous lui passons l'information du nom telle qu'elle a été saisie. En second paramètre, nous lui passons l'information du nom convertie en majuscule. Si les deux concordent parfaitement, cela signifie que la saisie a bien été réalisée en majuscules.

• Valider la création de la règle avec le bouton Ok,
• Puis, saisir un nom de famille en minuscules,

Comme vous le constatez, l'alerte se déclenche aussitôt. La fonction Excel Exact fait son travail. La différence de casse est repérée et la saisie est refusée.

Si vous annulez l'alerte et saisissez le même nom en majuscules, cette fois ce dernier est autorisé à validation.