Comment utiliser une formule pour valider les données sur Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel

Nom

E-mail

En soumettant ce formulaire, j’accepte que mes informations soient utilisées exclusivement dans le cadre de ma demande et de la relation commerciale éthique et personnalisée qui pourrait en découler si je le souhaite.

 

1. La validation des données

La validation des données permet d’analyser les données saisies par un utilisateur afin de s’assurer que celles-ci correspondent effectivement avec des règles définies par la personne qui a créé le classeur Excel, ce qui évite un grand nombre d’erreurs et de plantage de formules.

Les usages de la validation des données sont nombreux, on peut ainsi contrôler que l’utilisateur a bien saisi une donnée numérique, une date, un nombre entier, …

Souvent la validation des données permet également d’afficher un menu déroulant permettant de sélectionner une donnée à saisir parmi un ensemble de données pré-enregistrées.

Lorsque la saisie n’est pas conforme avec la règle mis en place, celle-ci ne pourra pas être confirmée, et un message d’erreur va inviter l’utilisateur à réitérer sa saisie.

2. Une formule pour valider les données

Lorsque les règles de la validation des données proposées par Excel ne sont pas suffisantes, il est possible d’aller beaucoup plus loin en utilisant une formule pour la validation des données.

Cette formule doit renvoyer un résultat booléen, c’est-à-dire VRAI ou FAUX. Dans le premier cas, la saisie va pouvoir être validée, alors que dans le second cas, celle-ci sera purement et simplement bloquée (sauf à définir un comportement différent).

Pour mettre en place ce type de validation de données, il suffit de sélectionner l’option Personnalisé depuis le menu Autoriser :

   2.1. Restreindre le nombre de caractères saisis

Pour ce premier exemple, nous souhaitons valider que le nombre de caractères saisis par l’utilisateur est compris entre cinq et dix caractères.

Nous allons alors procéder de la manière suivante :

•        Sélectionner la cellule dans laquelle nous souhaitons définir la validation (dans notre exemple il s’agit de la cellule B2, retenez bien les coordonnées de celle-ci pour adapter ce qui suit juste après de la cellule sélectionnée) :

•        Puis, cliquer sur le bouton Validation de données (groupe Outils de données, du menu Données dans le menu ruban d’Excel) :

•        Dans la fenêtre qui s’affiche à l’écran, choisir Personnalisé depuis le menu Autoriser, et saisir la formule comme sur la capture suivante (nous détaillerons celle-ci juste après)

Revenons sur la formule utilisée :

 =ET(NBCAR(B2)>=5;NBCAR(B2)<=10)

Dans cette formule nous effectuons deux contrôles qui doivent être tous les deux vrais (formule ET()) :

•        Le premier contrôle « NBCAR(B2)>=5 » vérifie que la cellule B2 (la cellule normalement sélectionnée, à adapter si cela n’est pas le cas) contient au moins cinq caractères (pour cela, nous utilisons la formule NBCAR() qui retourne le nombre de caractères contenus dans une cellule),
•        Le second contrôle « NBCAR(B2)<=10 » vérifie que la cellule B2 contient cette fois-ci dix caractères ou moins

Ensuite, nous pouvons confirmer la création de la validation de données en appuyant sur le bouton [OK].

A présent, lorsque l’utilisateur va saisir une chaîne de caractères possédant moins de cinq caractères, ou plus de dix, celui-ci sera alors confronté à un message d’erreur empêchant de valider sa saisie :

Si ce message n’est pas suffisant explicite, il est possible de le personnaliser. Pour cela revenons dans la fenêtre de validation des données, puis dans l’onglet Alerte d’erreur :

Et voici le résultat :

Enfin, il est également possible d’afficher un message lorsque l’utilisateur sélectionne la cellule pour lui expliquer ce l’on attend de lui avant qu’il entre sa saisie (onglet Message de saisie) :

   2.2. Valider une saisie sur une plage complète

La validation de données permet également de définir une règle commune à un ensemble de cellules.

Pour ce nouvel exemple, nous allons mettre en place un mini-outil de reporting qui va permettre de présenter le prévisionnel d’activité d’une entreprise :

Ici la seule contrainte à mettre en place est de vérifier que le résultat de l’entreprise pour l’année 2019 va être bénéficiaire.

Pour cela, nous commençons par repérer la cellule dans la laquelle se trouve le résultat de l’entreprise (ici, la cellule B13), puis nous sélectionnons toutes les cellules sur lesquelles nous souhaitons insérer cette validation de données :

Et enfin, nous définissons la règle de validation suivante :

=$B$13>0

Attention de bien respecter la référence absolue à la cellule B13 (le signe « $ »). Pour tout savoir sur les références relatives et absolues, vous pouvez consulter cet article sur excelformation.fr.

   2.3. Autres exemples

Empêcher la saisie de données en doubles : vérifier que la plage de cellule (A16:C18) ne contient pas de données en double :

 =NB.SI($A$16:$C$18;A16)=1

Saisie de données en majuscule :

 =EXACT(B20;MAJUSCULE(B20))

Note, la formule EXACTE() permet de comparer deux chaînes de caractères afin de s’assurer que celles-ci sont strictement identique, en tenant compte de la casse (distinction majuscules/minuscules).

Saisie d’une date du mois en cours :

 ET(MOIS(B21)=MOIS(AUJOURDHUI());ANNEE(B21)=ANNEE(AUJOURDHUI()))