Microsoft Excel est très permissif, et les saisies incorrectes sont la première source d'erreurs (avec la structure des classeurs).
C'est la raison d'être de la fonctionnalité "Validation des données" qui permet de poser des contraintes de saisie sur les cellules.
Vous pouvez par exemple n'autoriser que des entiers positifs, autoriser que les dates, limiter le nombre de caractères autorisé, etc., et beaucoup d'utilisateurs savent maintenant mettre une liste de choix dans une cellule par ce procédé.
L'article n'est pas destiné à apprendre le fonctionnement de base de la "Validation de données" sur Microsoft Excel, mais d'approfondir cette fonctionnalité sous-exploitée.
Paramétrer les contraintes
Onglet "options"
Optimiser les options de validation
Dans la zone du critère, nous posons souvent une valeur fixe.
Il est possible de poser cette valeur dans une cellule et dans la zone de critère faire référence à cette cellule.
C'est intéressant dans le cadre d'un modèle qui possèderait une feuille de paramétrage.
Ainsi selon le cas, on peut changer ce paramètre sans rentrer dans le menu de validation.
Message de saisie
Il est possible de désactiver l'info-bulle en décochant l'option "Quand la cellule est sélectionnée".
Dans ce cas pensez à mettre sur la feuille un commentaire qui explique la contrainte.
Alerte d'erreur
Liste "Style"
Elle permet de choisir l'icône qui apparait sur la boite de dialogue.
Les icônes ne représentent pas qu'un choix décoratif. Elles ont une signification précise sur le comportement à tenir en cas d'erreur.
Icône "Arrêt"
Elle est sans ambiguïté. Le tableur refuse toutes valeurs qui ne répondent pas à la contrainte.
Les icônes "Avertissement" et "Informations" affichent le message en cas d'erreur, mais permettent toutefois de valider la valeur fausse.
La différence se situe sur l'action à tenir sur une erreur.
Avertissement
- Le bouton "Oui" accepte la valeur invalide.
- Le bouton "Annuler" revient à la saisie précédente.
- Le bouton "Non" permet de modifier la valeur hors contrainte.
Informations
- Le bouton "Ok" accepte la valeur invalide.
- Le bouton "Annuler" revient à la saisie précédente.
Retrouver les validations de données
Il faut tout d'abord se positionner sur la feuille dont nous souhaitons connaître les plages comportant une validation.
Sur l'onglet "Accueil", dans le groupe "Édition", cliquons sur l'icône "Rechercher et sélectionner".
Il reste à choisir l'option "Validation des données".
Excel sélectionne les plages de la feuille qui comportent des "Validations de données".
Contrainte calculée
Quand vous choisissez l'option "Personnalisé" dans la liste "Autoriser", une zone "Formule" s'affiche.
Vous pouvez y écrire une expression qui sera évaluée lors de la validation d'une cellule.
Interdire la saisie d'une date qui tombe un week-end
Pour trouver quel jour tombe une date, nous utilisons la fonction JourSem().
Elle retourne un nombre qui indique le jour de la semaine d'une date (1 pour lundi, 2 pour mardi, etc.).
Elle prend deux paramètres :
- La date à évaluer.
- Le jour de début de semaine.
C'est un code qui identifie quel jour commence la semaine.
Avec la valeur 1, la semaine commence un dimanche. La fonction retournera 1 pour le dimanche, 2 pour le lundi, etc.
Avec la valeur 2, la semaine commence un lundi (norme ISO). Si la date est un lundi la fonction donnera 1, pour une mardi la valeur 2 et pour le dimanche la valeur 7.
C'est bien sûr ce modèle que nous utiliserons étant en Europe.
Si la date du 14 août 2016 qui est un dimanche est dans la cellule "A1" :
La formule JourSem(A2; 1), affichera 1 car la semaine commence le dimanche.
La formule JourSem(A2; 2), affichera 7 car la semaine commence le lundi.
Écrire la contrainte
Dans la zone "Formule", j'écris l'instruction "=JourSem(A2; 2)" et je dis que le résultat doit être inférieur à 6 (samedi) : "=JourSem(A2; 2) < 6".
Toute date dont JourSem() retourne une valeur supérieure à 5 est rejetée.
Interdire la saisie de doublons
Fonction NB.SI()
Cette fonction permet de compter combien de fois une valeur existe dans une plage (combien de fois un article est vendu, par exemple).
Si le résultat de cette fonction ne peut être supérieur à 1, alors la valeur ne pourra apparaître qu'une seule fois.
Elle possède deux paramètres, la plage qui contient les valeurs à compter et la valeur critère : = NB.SI($A$1 : $A$10; "Toto").
Dans l'exemple, je demande combien de fois apparaît la mention "Toto" dans la plage "$A$1 : $A$10".
Quand nous faisons référence à une plage dans une validation de données, il ne faut pas oublier de bloquer les adresses (adresses absolues).
Limites
- La validation est uniquement effective lors de la validation du contenu d'une cellule.
Ainsi, elle est inopérante lors de la copie (ou recopie incrémentale) d'une valeur dans la cellule affublée de la contrainte. - Si les contraintes changent, aucun contrôle n'est effectué sur les données existantes.
- Le recalcule manuel peut empêcher son activation.
Une formule ne réagit pas à la validation
Si en cellule "C1", j'ai le calcul "A1 + B1".
Mettre une contrainte en "C1" par exemple : un nombre inférieur à 10.
La somme ne sera évaluée qu’une seule fois lors de la validation de la formule.
Conclusion
Les validations de données issues du résultat d'une fonction ouvrent des perspectives dans la sécurisation des feuilles de calcul.
Vous trouverez d'autres exemples dans le classeur validation-données (Excel 2010).
Achetez mon ouvrage !
Mon PDF « Créer un planning perpétuel sur Microsoft Excel sans macro » est disponible à la vente.
Pour plus d’informations, rendez-vous sur la page dédiée.