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
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?