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

Dans ce tutoriel, nous allons apprendre à insérer une formule en tant qu’outil de validation de données, afin de personnaliser au maximum les possibilités offertes par cette fonctionnalité.

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

 

Téléchargement

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

 

 

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.

 

Excel formation - Validation de données en formule - 01

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, …

Excel formation - Validation de données en formule - 02

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.

Excel formation - Validation de données en formule - 03

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.

Excel formation - Validation de données en formule - 04

 

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 :

Excel formation - Validation de données en formule - 05

 

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

       Excel formation - Validation de données en formule - 06

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

       Excel formation - Validation de données en formule - 07

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

       Excel formation - Validation de données en formule - 08

 

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 :

Excel formation - Validation de données en formule - 09             

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 :

Excel formation - Validation de données en formule - 10

Et voici le résultat :

Excel formation - Validation de données en formule - 11

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

Excel formation - Validation de données en formule - 12

Excel formation - Validation de données en formule - 13

 

   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 :

Excel formation - Validation de données en formule - 14

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 :

Excel formation - Validation de données en formule - 15

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

Excel formation - Validation de données en formule - 16

 

=$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.

Excel formation - Validation de données en formule - 17

Excel formation - Validation de données en formule - 18

 

   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 :

 

Excel formation - Validation de données en formule - 19

 

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

 

Saisie de données en majuscule :

Excel formation - Validation de données en formule - 20

 

 =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 :

 

Excel formation - Validation de données en formule - 21

 

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

 

 



Articles qui pourraient vous intéresser

Comment empêcher la suppression ou la modification des cellules sur Excel

Comment créer un formulaire de saisie intégré dans une feuille de calcul sur Excel

Calculer la racine n-ième d’un nombre sur Excel

Comment synthétiser les informations sur Excel : le mode plan

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.