Connu au travers des célèbres listes déroulantes, l’outil validations des données est un outil que nous avons appris à apprécier au fil du temps, notamment sur des classeurs où nous sommes plusieurs à apporter des modifications. Y compris quand c’est un classeur personnel, cela fait du bien d’avoir Excel lui-même pour nous empêcher de commettre une erreur.
Comment est-ce que ça fonctionne ? Le principe reste le même, quel que soit le type de validation des données qui est appliqué à une cellule : nous paramétrons une restriction de saisie dessus, pour empêcher l’utilisateur d’entrer les mauvaises données au mauvais endroit.
Dans le seul cas des listes déroulantes, il y a l’utilité supplémentaire de ne pas devoir entrer les données au clavier mais de les sélectionner à la souris, ce qui peut faire gagner du temps, mais l’idée reste la même y compris dans ce cas-là : appliquer une restriction de saisie.
La question « quels types de validations des données puis-je appliquer ? » devient donc « quels genres de restrictions puis-je appliquer à ma cellule ? » Explorons cela, juste après avoir regardé ce qu’Excel met en place autour de ces restrictions.
Cet article suit une "série" qui explore les différents domaines d'Excel :
- Effectuer des calculs
- Manipuler les textes
- Manipuler les dates
- Vérifier des conditions et utiliser les valeurs VRAI et FAUX
- Rechercher des valeurs en fonctions de différents critères
- Les validations des données
- Etc
Les 2 rubriques « de synergie » dans la fenêtre des validations des données
Qui dit restrictions, dit que l’utilisateur doit en être conscient, et introduit également une question de dureté de la restriction. Microsoft y a pensé et a introduit 2 rubriques de plus que celle nécessaire pour configurer le type de validation des données : le message de saisie et l’alerte d’erreur.
Le message de saisie
Cette partie sert à annoncer à l’utilisateur ce qu’il peut ou ne peut pas écrire dans la cellule, simplement en cliquant sur la cellule en question … en théorie. En pratique, c’est une partie 100% personnalisable du point de vue du message en lui-même, nous pouvons donc nous en servir simplement pour laisser un commentaire sur la cellule, même si aucune restriction n’y est appliquée. Cela n’est pas un défaut mais bel et bien une possibilité supplémentaire !
L’exemple en image :
Bien entendu, on peut aussi annoncer la restriction qui est appliquée sur la cellule (et c’est même pour cela que cette rubrique est faite), et ça a en fait une belle synergie dans le sens où un utilisateur prévenu, est un utilisateur moins sujet à perdre du temps à se demander ce qu’il peut saisir ou pas :
L’alerte d’erreur
L’alerte d’erreur est la rubrique où nous contrôlons la souplesse des restrictions appliquées par la validation des données. Contrairement au message de saisie, cette partie n’a de sens que si une restriction est appliquée. Son usage ne peut donc pas être détourné. En revanche, le message d’erreur, de la même manière que le message de saisie, est totalement personnalisable.
Sur Excel, il existe 3 types d’alertes d’erreur (du plus strict au moins strict) :
- Stop (par défaut) : Toute saisie non conforme sera tout simplement refusée par Excel
- Avertissement : Un avertissement apparaît au moment de la saisie non conforme, mais Excel laisse la possibilité soit de la garder, ou de l’annuler comme dans le cas d’un stop.
- Information : Excel se contentera de signaler la non-conformité de la valeur, avant de tout de même l’accepter automatiquement :
En somme, la rubrique Alerte d’erreur est la rubrique qui permet de contrôler à quelle point la saisie est strictement contrôlée, tout en offrant la possibilité d’y ajouter un message personnalisé pour expliquer, dans les mots de l’utilisateur, pourquoi ça s’est mal passé.
La rubrique la plus importante : Options
Nous entrons enfin dans le vif du sujet puisque jusqu’ici, nous parlions uniquement de ce qu’il y a autour de restrictions de saisie, mais pas des restrictions elles-mêmes. C’est le sujet de cette partie !
Il existe 6 types d’autorisations spécifiques et une autorisation personnalisée, au travers d’une formule. Le 8ème type de validation des données, « Tout », est l’absence de restriction appliquée à la cellule ou la plage. Explorons-les tous, un par un.
Les types de restriction sont : Nombre entier, Décimal, Liste, Date, Heure, Longueur du texte, et Personnalisé.
Pour les restrictions de type Nombre entier, Décimal, Date, Heure et longueur du texte, nous disposons de plusieurs possibilités de restriction mathématiques, étant donné que nous traitons avec des nombres (oui, les dates et heures sont aussi des nombres sur Excel).
Pour schématiser tout cela, utilisons une notation d’intervalle mathématique avec a étant la borne inférieure et b la borne supérieure (dans le seul cas de la longueur du texte, l’approche est indirecte, puisque le nombre considéré n’est pas le nombre directement, mais le nombre de caractères. Le principe reste cependant le même, puisque les restrictions s’appliquent bien sur un nombre, même si c’est du texte qui est saisi) :
- Comprise entre : Pour encadrer ce nombre entre a et b : [a ;b]
- Non comprise entre, accepte tous les nombres soit supérieurs à b ou inférieurs à a,
- mais pas entre les 2 : ]-∞ ;a[∪]b ;+ ∞[
- Egale à : la seule valeur acceptée est a : [a ;a]
- Différente de : la seule valeur refusée est a : ]-∞ ;a[∪]a ;+ ∞[
- Supérieure à : la valeur doit être strictement supérieure à a : ]a ;+ ∞[
- Inférieure à : la valeur doit être strictement inférieure à b : ]-∞ ;b[
- Supérieure ou égale à : la valeur doit être supérieure ou égale à a : [a ;+ ∞[
- Inférieure ou égale à : la valeur doit être inférieure ou égale à b : ]-∞ ;b]
Ces intervalles sont valables pour toutes les validations des données liées aux nombres, nous choisissons donc de ne pas le repréciser dans la description de la validation des données. Cependant, la mention « voir les intervalles de nombres paramétrables » sera inscrite, à chaque fois que ceux-ci sont valables.
A noter également, et c’est là que nous entrons dans les validations des données « avancées », que a et b (dans le cas des nombres), ou encore les valeurs contenues dans les listes déroulantes, peuvent être déterminés par une formule. Autrement dit, il est possible d’écrire une formule directement dans les critères d’intervalle, tant que le résultat de la formule correspond bien au type de validation des données. Par exemple, si nous mettons une validation des données « Date », parce que nous souhaitons accepter uniquement des dates futures par rapport au jour présent, il est possible de choisir date supérieure à =AUJOURDHUI().
En ce qui concerne les formules (dans les cellules restreintes), si leur résultat correspond à la validation des données mise en place, elle sera acceptée, sinon elle sera refusée (selon le type d’alerte d’erreur) de la même manière qu’une saisie de valeur.
Nombre entier
Thème récurrent dans les types de validations des données, ce que ça fait peut être facilement déduit du nom du type de validation des données. Donc, à la question « que puis-je saisir ici ? », la réponse est « un nombre entier, mais pas n’importe lequel non plus ! » (voir les intervalles de nombres paramétrables). En d’autres termes, selon l’intervalle, il n’est possible de saisir qu’un nombre entier, positif, nul ou négatif (pas de chiffre après la virgule !).
Petit détail à noter : même si ce n’est pas la validation des données la plus appropriée pour cela, la validation des données nombre entier accepte également des dates, si celles-ci correspondent aux intervalles, puisque ce sont également des nombres (par exemple, sur Excel, 01/01/2025 est la même chose que 45658). En effet, ce qu’il faut retenir sur les dates, c’est que le nombre 1, c’est aussi égal à 1 jour en termes de dates.
Décimal
Ce type de validation des données est la même que « Nombre entier », avec l’autorisation supplémentaire d’y ajouter des chiffres après la virgule, bien entendu si et seulement si ce nombre décimal respecte l’intervalle défini (voir les intervalles de nombres paramétrables).
Petit détail à noter : de la même manière que pour les nombres entiers et les dates, les décimaux, bien que pas forcément les plus appropriés, acceptent également les dates et heures (puisque 1 heure est égale au nombre décimal 1/24).
Date
Après nombre entier et décimal, l’outil validation des données propose Liste et non pas date, mais nous choisissons ici de regrouper les validations des données liées aux nombres, c’est pourquoi nous passons directement aux dates. Pour en savoir plus sur les listes déroulantes, pas de panique, leur partie est un peu plus bas sur cette page !
Ici encore, le type de restriction est explicite, la saisie n’acceptera que … des nombres entiers (puisqu’une date est un nombre entier). Donc, comparé à « nombre entier », la différence n’est pas au niveau du type de restriction, mais plutôt une simple question d’affichage dans l’outil validation des données, dans le cas où les valeurs sont saisies directement dedans. (voir les intervalles de nombres paramétrables)
Heure
La différence entre nombre entier et décimal est la même qu’entre date et heure. On en déduit donc que réellement, ce type de validation des données n’accepte que … les nombres décimaux. De la même manière que les dates étant en réalité des nombres entiers, les heures sont en réalité des nombres décimaux. Après tout une heure, une minute ou une seconde n’est ni plus ni moins qu’une fraction d’une journée. (voir les intervalles de nombres paramétrables)
Longueur du texte
Ce type de validation des données est un peu un cas particulier. Jusqu’ici, nous avons discuté de validations des données qui restreignent la valeur en elle-même directement. Ici, c’est une restriction indirecte, puisque peu importe ce qui est saisi (ou généré par le biais d’une formule), cette validation des données ne s’intéressera qu’au nombre de caractères qui le constitue et non plus à ce que c’est.
C’est une validation des données qui s’applique à du texte (en théorie, mais valable pour les nombres également), mais réellement, cela reste une validation des données de « type nombre », avec les mêmes contraintes d’intervalles paramétrables qu’un nombre entier ou décimal (voir les intervalles de nombres paramétrables). Par exemple, cette validation des données est notamment utilisée pour contrôler qu’un nom comporte au moins 2 lettres, ou encore qu’il y ait bien le bon nombre de chiffres dans une cellule réservée à un numéro de sécurité sociale ou SIRET etc.
Liste
C’est incontestablement la star des validations des données, tant et si bien que personnellement, je connaissais l’existence des listes déroulantes bien avant de découvrir l’outil qui en est responsable : la validation des données. C’est aussi le seul type de validation des données qui offre une fonctionnalité supplémentaire à la restriction de saisie : la possibilité de sélectionner la valeur dans une petite fenêtre.
A noter qu’à partir de ce type de validation des données (donc pour Liste et pour Personnalisé), les intervalles de nombres ne sont plus valables, puisque nous ne contrôlons plus forcément un nombre, ou une particularité numérique d’un texte.
L’essence de la liste est la suivante : les valeurs autorisées sont répertoriées quelque part (directement dans la validation des données, ou dans une plage du classeur). Toute valeur non inclue dans la liste est donc refusée (selon le type d’alerte d’erreur, bien entendu). Ces valeurs peuvent être des nombres, des textes, des booléens, ou tout ça en même temps dans la même liste. Vous retrouverez bientôt sur ce site un article qui traite en profondeur ce sujet, tant il est possible de créer des listes déroulantes incroyables, avec l’aide de certaines formules.
Personnalisé
Ce type de validation des données existe pour toutes les restrictions qui ne sont pas prévues dans le cadre d’un autre type de validation des données. Voici quelques exemples (absolument non exhaustifs) de restrictions que l’on ne peut appliquer qu’au travers d’une validation des données personnalisée, avant d’exposer le principe général sur lequel ce type de validation de données repose :
- Exiger que tout texte dans la cellule soit inscrit en majuscules
- Exiger un préfixe devant toute valeur saisie dans la cellule
- Exiger que la cellule ne peut être remplie que si une autre cellule n’est pas vide
- Exiger que le total d’une plage, dont la cellule fait partie ne dépasse pas un certain nombre
- Exiger que la valeur saisie ne fasse pas doublon avec une autre cellule dans une plage
- Une combinaison de plusieurs de ces exemples
- Etc…
En regardant ces exemples, on voit une large variété de restrictions possibles, y compris des restrictions qui dépendent d’autres cellules. Bien qu’aucun de ces exemples n’est possible au travers des autres types de validation des données, la validation des données personnalisée peut être utilisée pour reproduire les restrictions prévues par tous les autres types de validation des données. Cela étant, cette validation des données ne génère pas de liste déroulante.
Le principe de la validation des données personnalisée est le même que celui des mises en formes conditionnelles avec formule ou encore que celui d’une fonction SI, c’est le principe de test logique. Un test logique sur Excel est une formule qui compare deux valeurs (égalité ou inégalité) et renvoie VRAI si l’égalité ou l’inégalité est vérifiée, ou FAUX si elle ne l’est pas. Par extension, la validation des données n’acceptera la saisie que si le test logique appliqué renvoie VRAI. Pour en savoir plus sur les tests logiques, cet article peut vous aider : [Article sur les meilleures fonctions logiques].
En résumé ?
Les validations des données sont un sujet dont le principe est simple : restreindre les valeurs qui peuvent être contenues dans une cellule, mais dont les applications sont quasiment infinies, notamment au travers des listes et des validations des données personnalisées.
Voici ce qu’il faut retenir de cet article :
- On peut prévenir l’utilisateur de l’existence de la restriction,
- On peut contrôler à quel point on proscrit les non-conformités aux valeurs attendues,
- Toutes les restrictions peuvent être définies par une (ou plusieurs) valeur, ou bien par le résultat d’une formule,
- 5 des 7 types de validation sont des validations compatibles uniquement avec des nombres, et parmi eux, plusieurs sont interchangeables (heure et décimal par exemple),
- Les 2 autres validations des données sont plus généralistes, et (mon opinion) plus puissantes.
