C’est quoi la validation des données dans Excel ?

Cet article est un extrait de l’e-book gratuit que vous pouvez retrouver au téléchargement à la rubrique « Mes livres » de ce site. Pour découvrir cet ebook et son contenu, cliquez simplement sur ce lien et téléchargez Le petit Excel illustré. Cliquez sur ce lien pour afficher tous les articles du petit Excel illustré.

C’est quoi la validation des données ?

Dans un classeur Excel, tu comprendras très vite que tes données doivent être cohérentes. La validation des données permet de contrôler les données saisies afin de vérifier qu’elles correspondent à ce que tu attends.

Le plus simple c’est que nous partions sur un exemple : tu as envoyé un fichier Excel à tous tes amis et tu leur a demandé leur prénom, leur nom, leur âge, leur date de naissance et leur numéro de téléphone pour mettre à jour ton carnet d’adresses. Et voici ce que tu as reçu… Tes amis ont répondu mais, ils n’ont pas renseigné les données tous de la même façon. Par exemple, certains on indiqué leur âge en lettres, d’autres en chiffres. Certains ont mélangé lettres et chiffres dans le numéro de téléphone, certaines cellules sont mal renseignées (E5) et il y a même une cellule vide (F4). Ce fichier va être difficile à exploiter.

Imagine que tu veuilles savoir le nombre de tes amis qui ont plus de 16 ans…

Avec ces données, tu vas devoir tout corriger à la main avant d’utiliser des formules de calcul car dans le cas présent, le résultat de ton calcul est faux. En effet, trois de tes amis ont plus de 16 ans, mais ils ont répondu en écrivant du texte et les cellules contenant du texte sont ignorées dans la formule de calcul. Ainsi, E2 et E4 ne sont pas prises en compte. La seule cellule exploitable est la cellule E6, et donc le résultat est 1 alors que nous devrions avoir 3.

Comme tu peux le constater, ici nous aurons le même problème si on veut travailler avec les dates de naissance ou les civilités…

La solution ? Obliger les utilisateurs à saisir des données sous une certaine forme et c’est justement à ça que sert la Validation des données.

METTRE EN PLACE UNE VALIDATION

Nous allons mettre en place une validation des données pour l’âge. Clique dans la cellule E2 et dans le Ruban, à la section Données, clique sur le bouton Validation des données.

La fenêtre Validation des données s’ouvre et tu constates que pour le moment, ta cellule accepte Tout.

Changeons ça !

Dans le menu déroulant, choisis Nombre entier, puis dans la zone Minimum, indique 0 et dans la zone Maximum indique 100. Valide en cliquant sur OK.

Tu viens de dire à Excel : dans cette cellule, je ne veux rien d’autre qu’un nombre entier (le texte sera refusé, les nombres à virgule aussi) et ce nombre doit être compris entre 0 et 100.

À partir de maintenant, toute tentative pour saisir autre chose qu’un nombre entier entre 0 et 99 provoquera un message d’erreur :

Tu as vu que dans le menu déroulant Autoriser, tu as de nombreuses possibilités pour ta validation :

• Tout — Aucune validation, on peut saisir n’importe quoi dans la cellule. C’est l’état par défaut et c’est pour ça que tes amis ont pu écrire ce qu’il ont voulu ;
• Nombre entier — c’est ce que nous venons de voir, seul un nombre entier sera accepté, rien d’autre ;
• Décimal — avec ce choix, on acceptera des nombres à virgule ;
• Liste — permet d’utiliser une liste de termes pré-remplis ;
• Date — accepte une date uniquement (parfait pour les dates de naissance) ;
• Heure — accepte uniquement une heure ;
• Longueur du texte — accepte un certain nombre de caractères dans la cellule ;
• Personnalisé — fait appel à des formules de calcul.

Dans notre validation, nous avons les données comprise entre 0 et 100. Mais dans la fenêtre de validation, le second menu déroulant te permet de choisir d’autre critères de validation.

N’hésite pas à jouer avec cette fenêtre et à tester toutes les possibilités !

Dans cet exemple, j’ai appliqué une validation sur une seule cellule, pense à appliquer cette validation à toute la colonne de la plage.

MESSAGES DE SAISIE PERSONNALISÉS

Si tu rouvres la fenêtre Validation des données, tu pourras voir deux onglets :

• Message de saisie
• Alerte d’erreur.

Le premier permet d’indiquer un petit message quand l’utilisateur clique dans la cellule pour le prévenir qu’une validation est active et lui expliquer ce qu’il doit faire. Le second permet de personnaliser le message d’erreur si l’utilisateur ne respecte pas la règle ou s’il se trompe.

Lorsque l’utilisateur clique dans la cellule, le petit post-it jaune lui indique la marche à suivre. S’il se trompe, le message d’erreur personnalisé apparaît.

VALIDER AVEC UNE LISTE

Dans le tableau que tu as envoyé à tes amis, dans la première colonne tu as demandé de choisir la civilité (Madame, Mademoiselle, Monsieur). Mais comme pour les autres données, tes amis ont répondu un peu n’importe quoi alors que toi ce que tu voulais c’était Mme pour madame, Melle pour mademoiselle et M. Pour monsieur. Donc, ne leur laisse pas le choix !

Retourne dans l’onglet Données et clique à nouveau sur Validation des Données. Dans le menu Autoriser, choisis Liste et dans le champ Source, indique les trois termes que tu veux proposer, séparés par un point-virgule ( ; ).

Coche la case Liste déroulante dans la cellule, et valide par OK.

De retour dans ton fichier, tu constates qu’un petit menu est présent dans les cellules. Si tu ouvres ce menu déroulant, tu trouveras les trois termes à utiliser. Ainsi, plus d’erreur de saisie pour tes utilisateurs ! Ils ne peuvent plus écrire dans la cellule autrement qu’en choisissant un élément du menu !

Effectivement, si tu as beaucoup de valeurs et si tu dois mettre souvent ces valeurs à jour, ce n’est pas la bonne solution. Dans ce cas, il faut mettre la liste quelque part dans le classeur et indiquer à la validation de données où se trouve cette liste. Voilà un travail tout indiqué pour notre feuille à outils !

Regarde dans l’onglet INDEX du fichier de travail. Tu verras que la première colonne contient nos trois données :

Pour indiquer à la validation de données d’utiliser cette liste, dans le champ source, indique INDEX! (le nom de la feuille), puis $A$2:$A$4.

A2:A4, ça représente la plage de cellule qui contient ta liste, et on place des dollars pour utiliser des références absolues (cette plage sera toujours la même).

Si tu prévois d’ajouter d’autres termes à cette liste, tu peux d’emblée indiquer $A:$A pour utiliser toute la colonne ! Ainsi tu n’auras pas besoin de modifier ta validation à chaque nouvel ajout dans ta liste.

Attention cependant, comme nous l’avons vu dans les bonnes habitudes à prendre, à partir de maintenant, la colonne A de ta feuille outils ne devra contenir rien d’autre que des éléments de la liste Civilités.