I. Introduction▲
I-A. Objectifs de ce tutoriel▲
I-B. Version utilisée▲
La version utilisée pour ce tutoriel est la version 365 mise à jour au moment de la rédaction du tuto. Certaines options ou possibilités seront absentes de votre version, surtout pour la validation par liste et par formule personnalisée.
J’attirerai votre attention sur ces cas indisponibles dans des versions plus anciennes.
I-C. Niveau requis▲
Il n’est pas nécessaire d’avoir des connaissances étendues d’Excel pour les validations « intuitives ». Les validations par listes ou formules demanderont certaines manipulations spécifiques que je détaillerai au moment voulu.
II. La validation dans Excel▲
II-A. Préambule▲
II-A-1. Toute cellule possède une règle de validation▲
Par défaut, une cellule accepte n’importe quelle valeur en entrée, ce qui permet de dire que toute cellule possède en fait une règle de validation. Je complète ce propos en disant que toute cellule possède UNE ET UNE SEULE règle de validation. Il n’est donc pas possible d’exprimer plusieurs règles pour une même cellule.
II-A-2. La validation est une propriété de cellule▲
La validation dans Excel est en fait une validation de saisie appliquée sur une cellule. C’est donc une propriété liée à une cellule. Il est important de bien comprendre cela lorsque l’on met en place une validation sur une plage de cellules, car Excel crée alors autant de propriétés « validation » qu’il y a de cellules au moment ou la règle de validation est confirmée.
II-A-3. Copier Coller complet▲
Comme la validation est une propriété de cellule, le fait de copier-coller une cellule A sur une B remplace la validation de B par celle de A. On sera donc attentif à utiliser des collages partiels, principalement un collage spécial valeurs, lorsque l’on colle des données sur des cellules disposant d’une validation spécifique.
II-B. Validation dans des tables de données / tableaux structurés▲
Lorsqu’une validation est placée sur l’entièreté d’une colonne d’une table de données, elle s’étend aux nouvelles lignes créées dans le tableau.
Voir mon tutoriel « Comment créer et utiliser les tableaux structurés avec Excel » :
II-C. L’outil de validation▲
L’outil de validation est affiché via le ruban Données/Outils de données/Validation des données…
L’outil de validation se présente comme une boite de dialogue modale (elle gèle Excel tant qu’elle est ouverte) qui présente 3 onglets :
- Options, pour spécifier la règle de validation et ses options ;
- Message de saisie, qui permet d’accompagner l’utilisateur lors de sa saisie ;
- Alerte d’erreur, qui informe l’utilisateur d’une saisie erronée et l’oriente vers un choix d’action.
II-C-1. Prise en mains de l’outil▲
Pour la prise en mains de l’outil, nous allons créer une validation simple se basant sur une plage de dates valides. Cet exemple sera suffisant pour faire le tour de ses possibilités.
Pour cela, il suffit d’appeler l’outil et de remplir des contrôles souhaités via l’onglet Données/Outils de données/Validation de données….Toutes les cellules sélectionnées au moment de cliquer OK seront impactées par la validation créée. Parcourons ensemble des onglets de la boite de dialogue qui s’est ouverte à l’écran.
Lors de l’ouverture de la fenêtre avec la cellule active sans validation personnalisée, on voit que « tout » est autorisé comme saisie dans la cellule.
La fenêtre s’ouvre sur l’onglet qui était actif lors de sa dernière fermeture.
II-C-2. L’onglet Options : Mise en place d’une validation▲
On remarque donc que, par défaut, la validation d’une cellule accepte n’importe quelle valeur saisie. La liste déroulante permet de limiter les valeurs acceptées.
En choisissant « Date », Excel modifie la fenêtre pour nous permettre de préciser les conditions de la validation.
Les différents choix de la liste « Données » se passent de commentaires. Selon que vous choisissez une ou deux bornes à la plage des dates valides, Excel vous permettra de choisir le début, la fin ou les deux bornes de votre plage de dates. Il suffira alors de saisir les dates limites dans les contrôles correspondants pour créer la validation personnalisée.
En validant les choix avec OK, on crée la validation et nous pouvons alors la tester. La saisie d’une date de 2021 sera acceptée, les autres dates seront refusées avec l’affichage d’un message d’erreur bloquant.
On n’a alors pas d’autre choix que de réessayer une saisie ou de l’annuler. Notre validation a bien fonctionné et a bloqué toute entrée non valide.
Je rappelle ici que copier une cellule A pour la coller sur une cellule B remplacera la validation de B par celle de A. on parle donc bien ici d’une validation de la saisie !
II-C-3. L’onglet « Aide à la saisie » pour guider l’utilisateur▲
Le deuxième onglet permet de préciser le message qui sera affiché à l’activation de la cellule pour guider l’utilisateur dans la saisie de la valeur.
Si la case « Quand la cellule est sélectionnée » est cochée, le message s’affiche à chaque sélection de la cellule.
Décocher la case revient à vider les zones « Titre » et « Message à la saisie ».
Personnellement, je ne suis pas un grand adepte de cette aide à la saisie, surtout sur une table de données. Ca peut vite devenir fatiguant pour l’utilisateur du fichier. A utiliser donc selon votre goût et ceux de vos utilisateurs.
II-C-4. L’onglet « Alerte d’erreur »▲
Par défaut, l’utilisateur est bloqué dans sa saisie et un message générique l’informe de la saisie erronée. Notons que ce message change selon les versions. Ce qui est important est qu’il est « générique » et donc ne renseigne pas sur les bonnes valeurs à saisir. Il est possible de personnaliser ce message, mais également de modifier le niveau d’alerte, et de blocage, d’une saisie erronée.
Il existe trois niveaux d’alerte pour gérer la saisie erronée, appelés assez trompeusement « styles » car qui vont au-delà d’une modification d’icône :
- Arrêt et blocage total (mode par défaut) ;
- Alerte bloquante par défaut ;
- Information de saisie erronée.
II-C-4-a. Stop : Arrêt et blocage total▲
Par défaut, la validation est bloquante : STOP et icône rouge.
Ce mode est bloquant, en ce qu’il refuse toute saisie erronée, ne permettant que de tenter une nouvelle saisie ou de sortir du mode Saisie. C’est le mode par défaut qui affiche le rond rouge d’interdiction et soit le message générique, soit le message d’erreur personnalisé.
Le résultat est clairement plus « user friendly », non ? On remarquera au passage les boutons du message d’alerte, qui proposent par défaut de réessayer, d’annuler ou d’obtenir de l’aide.
Ce niveau de blocage sera probablement celui qui sera choisi le plus souvent, puisqu’il permet, hors copier-coller intempestif, de s’assurer que la donnée saisie valide les règles de gestion, autrement dit, qu’elle correspond à nos attentes.
II-C-4-b. Avertissement : bloquante par défaut▲
Un degré en dessous du blocage total, il existe le style « Avertissement » avec l’icone qui lui correspond.
Comme on peut le constater en testant la validation, cette alerte va bloquer l’utilisateur par défaut (celui qui ne lit pas le message et presser ENTER sans réfléchir) mais permet d’accepter tout de même la saisie même si elle ne valide pas les règles. Il suffit de cliquer sur Oui pour que la valeur erronée soit « validée ».
II-C-4-c. Informations : La valeur passe par défaut▲
Le niveau le moins bloquant va simplement avertir l’utilisateur que la saisie n’est pas correcte. Il devra la valider par OK mais pourra l’annuler s’il le souhaite.
Les niveaux Avertissement et Information sont bien sûr déconseillés sur des valeurs qui feront l’objet d’une analyse par formule ou TCD (tableau croisé dynamique) puisqu’il n’y aura pas de garantie de saisie « correcte ».
II-C-5. Expression des limites▲
Dans le cas vu ci-dessus, les dates bornes ont été saisies manuellement dans les contrôles idoines. C’est une mauvaise idée dans la mesure où il faudra modifier la règle de validation si la période est modifiée. Ainsi, en 2022, il sera probablement nécessaire de modifier les dates pour accepter uniquement les valeurs de l’année en cours
Cette saisie « en dur » (hard coding en anglais) est un des 7 péchés capitaux d’Excel. Il ne faut donc JAMAIS pratiquer ainsi mais saisir les valeurs dans des cellules et référencer ces cellules dans les critères de validation.
Nous verrons un peu plus loin dans le tutoriel comment procéder.
II-D. Le repérage des cellules en erreur de validation▲
II-D-1. Triangle vert sur la cellule en erreur▲
Excel indique par un triangle vert certaines erreurs ou incongruités dans vos cellules. A la date de rédaction de ce tuto, il existe 11 erreurs signalées par Exel. On les retrouve dans Fichier/Options/Formules/Règles de vérification des erreurs. Parmi ces indicateurs, on trouve l’indicateur de valeur non valide.
II-D-2. Cercles de repérage des erreurs▲
Excel peut repérer les cellules en erreur de validation et les entourer d’un cercle rouge. Cette option est très pratique lors de la vérification des données avant analyse.
II-E. Les validations « intuitives »▲
Par validation intuitive, j’entends une règle de validation qui ne demande pas d’explications très pointues pour la rédiger. Il suffit de lire les options et listes déroulantes affichées dans la fenêtre pour comprendre leur fonctionnement et les mettre en place rapidement.
Toutefois, il est intéressant de savoir ce que l’on peut remplir dans les contrôles des valeurs bornes de ces validations. Nous allons donc les passer en revue et mettre le focus sur leurs spécificités.
Erreur fréquente : Placer les valeurs « en dur » (hard coding en anglais). En effet, procéder ainsi, outre le fait que c’est un des 7 péchés capitaux d’Excel, va obliger la personne qui gère le classeur à entrer dans la validation des cellules pour réaliser la modification des bornes.
Comme pour les formules, on préférera donc placer les valeurs dans des cellules, nommées ou faisant partie d’un tableau de paramètres, et y faire référence dans les propriétés de la validation.
Pour rappel, toute cellule possède une règle de validation, qui par défaut accepte toute saisie. Chaque validation personnalisée restreint donc les possibilités de saisie au sein d’une cellule.
II-E-1. Généralités▲
Pour ces validations, nous avons systématiquement les choix suivants, qui vont altérer la présentation de la fenêtre et afficher ou masquer certains contrôles. Les bornes dont les contrôles sont affichés doivent être précisées :
Comprise entre : Toute valeur comprise entre les bornes, bornes comprises. ;
Non comprise entre : Toute valeur non comprise entre les bornes. Les bornes sont exclues des valeurs acceptables ;
- Egale à : Valeur unique acceptable ;
- Différente de : Valeur unique rejetée ;
- Supérieur à : Toute valeur supérieure à la borne. La borne n’est pas une donnée valide ;
- Inférieur à : Toute valeur inférieure à la borne. La borne n’est pas une donnée valide ;
- Supérieure ou égale à : Toute valeur supérieure à la borne. La borne est une donnée valide ;
- Inférieure ou égale à : Toute valeur inférieure à la borne. La borne est une donnée valide ;
Pour des questions pratiques liées au tutoriel, les bornes sont ici définies sur la même feuille que celle qui contient les cellules subissant la règle. Dans la pratique, vous pouvez bien entendu faire référence à d’autres feuilles du classeur.
Cela sera rendu plus aisé par la mise en place de plages ou cellules nommées.
II-E-2. Pointage vers des cellules, éventuellement nommées▲
La validation permet de référencer des cellules pour définir les bornes de valeurs. En effet, les contrôles de saisie disposent d’un bouton à droite qui permet deux choses :
- Réduire la fenêtre à la zone de contrôle ;
- Indiquer que l’on peut pointer vers une cellule du classeur.
II-E-2-a. Utilisation de références absolues pour le calcul des bornes▲
Hormis la saisie directe (hard coding) qui est à proscrire, une façon rapide de saisir une valeur de borne est de pointer vers une cellule. Si cette cellule n’est pas nommée, il conviendra de déterminer si la référence doit être absolue, relative ou mixte..
Dans l’exemple suivant, on a pointé sur B1 et B2 pour déterminer les bornes de la plage des dates. Cela permet de modifier rapidement ces bornes sans avoir à toucher à la règle de validation.
On peut alors régler les bornes en modifiant les valeurs de ces cellules.
On remarquera au passage que lorsque l’on pointe vers une cellule, Excel crée en fait une formule : =$B$1. Cette constatation ouvre la porte à l’utilisation de formules pour calculer les bornes.
II-E-2-b. Utilisation de références relatives pour le calcul des bornes▲
On peut également utiliser une référence relative pour pointer vers une borne. Ici, on exige que la date de fin d’un projet soit ultérieure à la date de début dudit projet. Remarquez bien au passage que la sélection de la colonne du tableau active la cellule C2 et que c’est relativement à cette cellule active que la formule est rédigée. C’est pourquoi on pointe vers B2.
Ainsi, Excel comprend qu’il doit tester la saisie par rapport à la valeur qui se trouve sur la même ligne une colonne à gauche. Dès lors, C3 sera testée par rapport à B3, C4 par rapport à B4, etc.
II-E-2-c. Utilisation d’une formule dans les contrôles des bornes▲
Il est donc possible d’utiliser une formule dans les contrôles des bornes pour fixer la valeur de celles-ci.
II-E-2-d. Utilisation d’une cellule nommée▲
On peut passer par des cellules nommées pour une meilleure gestion des paramètres et une clarification de la règle.
Il faut noter que, a contrario d’une formule dans une cellule, pointer vers une cellule nommée n’amène pas à utiliser le nom. Il faut saisir le nom manuellement ou presser la touche F3 pour appeler la fenêtre de collage d’un nom. On peut également passer par le ruban mais la manœuvre me semble moins intuitive.
Pour créer une cellule nommée de portée de classeur, il suffit de sélectionner la cellule puis, dans la zone de noms (à gauche sur la barre de formule), de saisir le nom souhaité et de valider avec ENTER.
On notera qu’une cellule nommée peut être relative. On notera également qu’il est possible d’utiliser des plages nommées dont la portée est la feuille.
II-E-2-e. Utilisation d’un tableau de paramètres▲
La validation de saisie n’accepte pas les références structurées. Il n’est donc pas possible de saisir t_MonTableau[MaColonne] dans les contrôles des bornes de nos validations.
Il est possible d’utiliser un tableau et d’aller y chercher les valeurs de bornes. L’avantage du tableau structuré est qu’il grandit au fur et à mesure que l’on y ajoute des lignes (voir mon tuto à ce sujet : https://fauconnier.developpez.com/tutoriels/tableaux-structures/). Mais la validation de saisie « ne connait pas » ce mécanisme.
Pour que ces formules s’adaptent, il va falloir passer par des plages nommées s’appuyant sur les colonnes du tableau structuré. Les formules de validation, pointant alors vers ces plages nommées, seront étendues aux nouvelles données.
Pour créer les plages nommées, le plus simple est de sélectionner chaque colonne du tableau utilisée dans la formule et d’appuyer la formule sur ces plages plutôt que sur les références.
On peut alors appuyer la validation sur les plages nommées.
Pour les versions n’acceptant pas RECHERCHEX, vous pourrez utilisez la combinaison INDEX+EQUIV.
II-F. La Validation par liste de validation▲
II-F-1. Liste de valeurs « in extenso »▲
Une première approche consiste à créer la liste à la main en saisissant les valeurs séparées par le signe ; dans le contrôle Source.
On conçoit aisément que cette façon de procéder ne peut s’appliquer qu’à des listes très courtes et qui ne sont jamais ou quasi jamais modifiées. En effet, on retrouve ici le hard coding d’une part (péché capital d’Excel) et la mise à jour de toutes les validations qui s’appuient sur cette liste peut vite s’avérer fastidieuse.
Même pour des petites listes qui ne seront jamais modifiées, je déconseille vivement cette pratique. Le hard coding est pour moi une technique à proscrire systématiquement dans le cadre d’un travail professionnel de qualité.
II-F-2. Liste de valeurs s’appuyant sur une plage▲
Plutôt que la saisie manuelle des items dans la validation, nous créerons la liste dans un tableau structuré et nous appuierons la liste de validation sur une colonne de ce tableau.
Rappel : Nous avons vu plus haut que la validation n’accepte pas les références structurées et qu’il fallait biaiser en créant des plages nommées qui peuvent être utilisées lors de la validation. Nous procéderons de même pour les listes de validation.
II-F-2-a. Liste simple▲
La technique est assez légère à mettre en place :
- Création du tableau structuré ;
- Création de la plage nommée s’appuyant sur une colonne du tableau ;
- Utilisation de cette plage nommée comme source de la liste de validation.
La liste de validation s’adaptera aux nouvelles entrées dans le tableau.
II-F-2-b. Validations en cascade▲
La version 365 a singulièrement simplifié la création de listes de validation en cascade.
II-F-3. Options des listes de validation▲
II-G. La validation par formule▲
La validation par formule vous permet a peu près toutes les fantaisies puisque c’est grosso modo votre capacité à formuler qui va vous limiter… ou pas 😉
Le principe est de créer une formule qui validera la saisie lorsqu’elle renvoie VRAI. La formule peut utiliser la valeur saisie et toute autre cellule ou plage du classeur. Comme pour les validations vues plus haut, on privilégiera les cellules et plages nommées, ce qui permettra d’utiliser par ricochet les tableaux structurés.
II-G-1. Validation par rapport à la valeur saisie▲
Pour la validation formulée, on va utiliser la référence, absolue, mixte ou relative, dans la formule de validation. Dans l’exemple suivant, on souhaite valider E1:E3 uniquement des dates pointant vers un lundi. E1 étant actif, la formule est rédigée par rapport à cette cellule, reprise en relative.
II-G-2. Validation par comparaison avec une autre cellule▲
On peut utiliser d’autres cellules dans la formule de validation et établir des règles complexe de validation.