Tutoriel

Validation de données avancée sur Excel

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.

Capture écran Option de validation dans une cellule
Figure 1 : Critère dans une 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.

Capture écran Message de saisi validation de données
Figure 2 : Info-bulle message d'entrée.

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.

Capture écran onglet alerte d'erreur
Figure 3 : Définition du message d'erreur.

Icône "Arrêt"

Elle est sans ambiguïté. Le tableur refuse toutes valeurs qui ne répondent pas à la contrainte.

Capture écran validation de données arrêt sur erreur
Figure 4 : Message d'erreur style "Arrêt".

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

Capture écran validation de données avertissement sur erreur
Figure 5 : Message d'erreur style "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.
Capture écran validation de données information sur erreur
Figure 6 : Message d'erreur style "Informations".

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".

Capture écran validation de données information sur erreur
Figure 7 : Sélectionner les validations de 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".

Capture écran validation de données à l'aide d'une formule
Figure 8 : Utiliser le résultat d'une formule comme contrainte de saisie.

Toute date dont JourSem() retourne une valeur supérieure à 5 est rejetée.

Capture écran erreur validation de données personnalisée
Figure 9 : Erreur dans le résultat d'une formule.

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".

Capture écran validation de données doublons
Figure 10 : Contrainte utilisant la plage entière.

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).

Capture écran erreur validation de données personnalisée
Figure 11 : Erreur, doublons interdits.

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".

Capture écran validation d'une formule
Figure 12 : Validation sur une formule de calcul.

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.

Capture écran erreur validation d'une formule
Figure 13 : Contrainte sur une 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).

>