Tutoriel Ecxel : Validation des données Excel ( partie 2)

22 août 202222 août 2022 AZ

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre comment valider des données Excel

Excel: Appliquer la validation des données : nombres (entiers et décimaux), textes, dates et heures. Définissez les messages d’entrée et d’erreur.

La validation des données Excel vous permet de limiter les informations pouvant être saisies dans une cellule ou une plage de cellules.
L’objectif principal de la validation des données est de contrôler la saisie des valeurs pour éviter l’imputation de données incorrectes ou incohérentes.
Pour effectuer ce type de vérification, Data Validation applique un classement logique sur les valeurs textuelles et numériques (y compris les formats de date et d’heure).

La validation de données la plus utilisée est certainement la liste déroulante. Ce type de validation a été couvert dans un didacticiel précédent : Validation des données Excel : Organisation d’une liste déroulante.
Cette section prend en considération les validations de données qui définissent l’insertion d’entiers, de décimales, la longueur du texte et les règles relatives au format de la date et de l’heure.
Il explique également comment identifier la présence de validations de données sur une feuille de travail et comment intervenir pour les modifier ou les supprimer.
Les explications et les images du tutoriel se réfèrent à Excel Microsoft 365, les fonctions décrites sont cependant applicables à toutes les versions précédentes d’Excel. (2007-2019).

Introduction à la validation des données Excel

Les validations de données permettent de définir des règles pour limiter les valeurs pouvant être imputées dans une cellule ou une plage de cellules.
A cet effet, Excel fournit des règles prédéfinies. Grâce à l’option de personnalisation, il est possible de définir de nouvelles règles en appliquant des formules de comparaison logiques.
Pour aider l’utilisateur à comprendre quelles valeurs peuvent être saisies dans une cellule, Data Validation permet de créer des messages (Input message) qui s’activent dès qu’on clique sur une cellule contenant une règle.
Si la valeur saisie ne correspond pas aux règles définies pour cette cellule, un message d’erreur apparaît (voir image ci-dessous) qui peut être personnalisé à l’aide de l’onglet Message d’erreur.

La boîte de dialogue Validation des données

La boîte de dialogue Validation des données définit les options de base pour définir les règles de validation.
Pour y accéder : dans l’onglet Données, groupe de boutons Outils de données, cliquez sur Validation des données.

La boîte de dialogue de validation des données est organisée en trois onglets : Paramètres, Message d’entrée et Message d’erreur.

Onglet Paramètres. Représente le menu principal de la validation des données, dans lequel il est possible de définir les critères de validation et les règles qui doivent être appliquées aux valeurs saisies dans les cellules.
Message d’entrée. Il permet de rédiger un message guide, qui fournit des informations utiles pour remplir les cellules. Le message d’explication s’affichera en sélectionnant la cellule contenant la validation.

Message d’erreur. Il permet de créer un message d’erreur personnalisé qui est proposé lorsque les valeurs imputées ne satisfont pas aux règles de validation.

Tout d’abord, il est important de définir les options dans l’onglet Paramètres de validation des données. (voir image ci-dessous)

L’onglet Paramètres de validation des données

Dans l’onglet Paramètres, champ Autoriser : cliquez sur le menu déroulant à côté de l’élément Toute valeur. Dans le menu déroulant (voir image), vous pouvez choisir le type de validation des données que vous souhaitez définir pour la cellule sélectionnée (ou plage de cellules).
Les éléments mis à disposition par le menu sont les suivants :
De n’importe quelle valeur. Après avoir décrit la Validation comme une limitation de la saisie de données, cette option semble être une contradiction. Cependant, vous pouvez profiter de ce paramètre pour définir un message d’entrée dans les cellules sans la présence d’une règle, créant ainsi un guide pour remplir la feuille de travail.
Nombre entier. Permet de saisir uniquement des nombres entiers dans les cellules.
Décimal. Permet la saisie de chiffres contenant des nombres décimaux.
Liste. Création d’une liste déroulante, c’est-à-dire la définition de listes parmi lesquelles des entrées il sera possible de choisir quelle valeur insérer.
Date. En appliquant le classement, il vous permet de saisir uniquement des valeurs de type date.
Maintenant. En appliquant le classement, il vous permet de saisir uniquement des valeurs de type heure.
Longueur du texte. Définit une longueur de texte.
Personnalisé. Vous permet d’entrer une formule personnalisée. Dans le champ Formule, vous pouvez définir une expression logique qui donne Vrai ou Faux. Si True, il sera possible d’insérer la valeur.

Options de la boîte de dialogue Validation des données (onglet Paramètres)

Ignora celle vuote: permette di lasciare una cella vuota senza necessariamente dover inserire un valore.
Applica le modifiche a tutte le altre celle con le stesse impostazioni: Questa impostazione appare quando si sta modificando una regola che è condivisa anche da altre celle.

Ce type de validation permet de créer des règles concernant l’insertion de nombres entiers ou décimaux.
Une fois que vous avez sélectionné la cellule ou la plage de cellules, dans l’onglet Validation des données, choisissez d’abord si Entier ou Décimal.
Dans le champ Données ci-dessous, en cliquant sur le bouton il est possible d’identifier les critères à appliquer :
entre; non compris entre ; égal à; différent de; plus grand que; moins que; Plus grand ou égal à; inférieur ou égal à.

Dans le cas de l’exemple proposé dans l’image ci-dessous, une plage de valeurs comprises entre 1000 et 2000 a été définie comme critère de validation des données.

Deuxième exemple d’application d’une validation de données : les nombres

Comme second exemple nous proposons une validation qui évalue un nombre variable (la disponibilité des pièces en stock) et ne permet pas de saisir une valeur de vente supérieure à la quantité disponible.

Cellule B7 sélectionnée, dans l’onglet Validation des données, comme Autoriser : un entier a été défini ; Données : inférieures ou égales ; Valeur maximale = B3. (voir schéma ci-dessous).
Suite à la validation dans la cellule B7, il est possible d’imputer uniquement des valeurs inférieures ou égales au contenu de la cellule B3.

Observation. Dans la boîte de dialogue Validation des données, onglet Message d’entrée et Message d’erreur, le texte a été saisi : « VENTE PRODUIT entrez le nombre de tablettes que vous souhaitez vendre ». Le message apparaît lorsque la cellule B7 est sélectionnée.
Pour compléter le paramétrage, vous pouvez définir un message d’erreur du type : “Ce nombre de Tablette n’est pas disponible en stock” le message est renvoyé si un utilisateur met en place une vente avec un nombre de pièces supérieur au nombre actuellement disponible dans la cellule B3 .

Appliquer la validation des données au format de date et d’heure

Dans la validation des dates, comme pour les nombres, il est possible d’appliquer des opérateurs pour définir la règle :
entre; non compris entre ; égal à; différent de; plus grand que; moins que; Plus grand ou égal à; inférieur ou égal à.

Premier exemple d’application Valider le format data to date

Lorsque vous remplissez un formulaire, vous souhaitez empêcher la fixation d’une date de paiement supérieure à 60 jours à compter d’aujourd’hui.
Dans l’exemple illustré dans l’image, la date actuelle est saisie dans la cellule B3 et la date de paiement prévue doit être saisie dans la cellule B7.
Une fois que la cellule B7 est sélectionnée dans l’onglet Valider la date, Autoriser : la date et la règle inférieure à ont été sélectionnées. La formule = B3 + 60 a été saisie dans le champ Date de fin réussi

Remarques. Une autre expression faisant référence à 60 jours à compter de la date actuelle pourrait appliquer la formule : = AUJOURD’HUI () + 60.
Dans la boîte de dialogue Valider la date proposée dans l’exemple, le message de saisie “PAIEMENT – insérer une date n’excédant pas 60 jours à compter d’aujourd’hui” a également été programmé lorsque la cellule B7 est sélectionnée le texte du message apparaît.

Dans le message d’entrée, vous pouvez saisir un texte qui sera affiché dans une case jaune. La forme du message rappelle les rectangles des Notes (version Microsoft 365) ou les “commentaires” des versions précédentes d’Excel.
Le message s’affiche lorsque la cellule est sélectionnée, La mise en page du message est visible dans l’image ci-dessus.
Dans la fenêtre Message d’erreur, vous pouvez écrire le texte qui s’affichera si l’entrée de date ne respecte pas la règle Date de fin : dans les soixante jours.

Deuxième exemple d’application de la validation des données au format d’heure

Dans l’exemple illustré dans l’image ci-dessous, une règle a été créée qui empêche la saisie d’une heure d’entrée postérieure (supérieure) à 9h00

Dans la boîte de dialogue de validation des données, le format d’heure a été sélectionné, en définissant un critère inférieur à : 9h00.
L’éventuelle possibilité (exception) d’insérer une valeur supérieure à ce temps peut être gérée dans l’onglet Message d’erreur (expliqué ci-dessous).

Validation des données Excel : longueur du texte (définir le nombre de caractères)

Une autre opportunité offerte par la Validation des Données concerne l’insertion d’un texte pour lequel il est possible de définir une longueur minimale ou maximale.

Dans l’exemple proposé dans l’image ci-dessous, nous avons voulu créer une règle de longueur de texte relative à l’imputation du code fiscal.
Dans la boîte de dialogue Validation des données, la longueur du texte a été définie comme règle, les données : ont été choisies comme critère, définissant les valeurs minimale et maximale : 14.
À la suite de la validation, un code de seulement 14 caractères peut être saisi dans la cellule, un de plus et un de moins.

Validations de données Excel : configurer un message d’entrée

Le message d’entrée, comme prévu dans les images des exemples précédents, est utilisé pour créer un texte guide pour la compilation d’une ou plusieurs cellules de la feuille de travail.
Dès que vous cliquez sur la cellule sélectionnée, le message apparaît. Le message (qui peut être glissé et déplacé) reste affiché jusqu’à ce que vous sélectionniez une autre cellule ou appuyiez sur le bouton ESC.

Pour créer un message d’entrée dans la boîte de dialogue Validation des données, cliquez sur Message d’entrée et dans les champs ci-dessous, décrivez un titre et un texte du message.

Observation. L’option Toute valeur proposée dans la boîte de dialogue de validation des données peut être utilisée pour créer un guide pour remplir la feuille de calcul. Même si aucune règle n’est appliquée, lorsque la cellule contenant la validation est sélectionnée, le message d’entrée est toujours proposé.

En plus de la définition d’un message d’entrée, il est possible de définir un message d’erreur qui s’affiche si une donnée erronée est saisie et non conforme aux règles de validation (donnée invalide).

Validations de données Excel : Définir un message d’erreur

Lorsque les valeurs saisies dans une validation de données ne sont pas cohérentes avec la règle définie, Excel renvoie un message d’erreur générique : “La valeur ne correspond pas aux restrictions de validation définies pour cette cellule”
Ce message d’information est très générique et ne fournit pas de références à la règle appliquée. Pour retourner des informations plus détaillées sur l’erreur et ce qui peut être attribué à l’intérieur d’une cellule, il est possible de programmer un message d’erreur personnalisé.

Pour continuer, dans la boîte de dialogue Validation des données, sélectionnez l’onglet Message d’erreur : et remplissez les champs Titre et Message d’erreur.

Lorsqu’une valeur saisie dans une cellule contenant une règle de validation s’avère être fausse, Excel peut appliquer trois types d’action : Pause ; Avis et informations.

Abandonner : utilisé pour empêcher l’utilisateur d’entrer des données non valides dans une cellule et il n’est pas possible de procéder à l’insertion de données incompatibles avec le contrôle de validation.
Avertissement : il sert à avertir que les données saisies ne sont pas valides, sans toutefois empêcher leur saisie.
Lorsqu’un message d’avertissement s’affiche, vous pouvez choisir Oui pour accepter l’entrée non valide, Non pour la modifier ou Annuler pour la supprimer.
Information : sert uniquement à avertir que les données saisies ne sont pas valides, sans vérification supplémentaire de la saisie.

Le résultat de la définition d’un message personnalisé est illustré dans l’image ci-dessous.

Rechercher et supprimer une validation de données

Pour savoir quelles cellules ont des validations, dans l’onglet Accueil, groupe Modifier les boutons, cliquez sur Rechercher et sélectionner.
Rechercher et sélectionner, en plus de rechercher des textes, vous permet également de rechercher et de sélectionner des propriétés sur la feuille, y compris les validations.
Une fois l’option Rechercher des validations de données sélectionnée, toutes les cellules de la feuille contenant des validations seront sélectionnées.

Supprimer une validation de données

Sélectionnez la ou les cellules contenant les validations de données, dans la boîte de dialogue Validation des données, en bas à gauche, cliquez sur le bouton Effacer tout. (Voir image).

Modifier une validation de données

Pour procéder à une modification, sélectionnez la cellule ou la plage de cellules contenant la validation des données que vous souhaitez modifier.
Dans la boîte de dialogue Validation des données, sélectionnez la condition que vous souhaitez modifier, une fois que vous avez effectué les modifications souhaitées, cliquez sur le bouton OK.
Si d’autres cellules de la feuille de calcul ont le même paramètre de validation, vous pouvez activer la coche pour l’option : Appliquer les modifications à toutes les autres cellules avec les mêmes paramètres