La validation des données dans Excel est une fonctionnalité puissante qui permet de contrôler les types de données que les utilisateurs peuvent entrer dans une cellule. Cela permet d’éviter les erreurs et de garantir l’intégrité des données.
1. Qu’est-ce que la Validation des Données ?
La validation des données consiste à définir des règles pour restreindre ou limiter les entrées dans une cellule ou une plage de cellules. Par exemple :
Autoriser uniquement des nombres compris entre 1 et 100.
Limiter les entrées à une liste déroulante.
Exiger des dates spécifiques.
2. Où Trouver la Validation des Données ?
Sélectionnez une cellule ou une plage de cellules.
Allez dans l’onglet Données.
Cliquez sur Validation des données dans le groupe Outils de données.
3. Types de Validation des Données
a. Validation Basée sur des Plages
Nombre :
Restreint l’entrée à une plage de nombres.
Exemple : Autoriser uniquement des nombres entre 1 et 100.
Étape : Dans la boîte de dialogue, choisissez :
Autoriser : Nombre entier.
Données : compris entre.
Minimum : 1.
Maximum : 100.
Décimales :
Permet les nombres avec décimales.
Exemple : Autoriser uniquement des notes entre 0 et 20 avec décimales.
Date et Heure :
Limite les entrées à une plage de dates ou d’heures.
Exemple : Accepter uniquement des dates après le 01/01/2024.
Autoriser : Date.
Données : supérieur à.
Date de début : 01/01/2024.
b. Validation avec une Liste Déroulante
Crée une liste déroulante pour limiter les choix disponibles.
Étapes :
Sélectionnez les cellules où ajouter la liste déroulante.
Allez dans Validation des données.
Dans Autoriser, sélectionnez Liste.
Dans Source, entrez les options séparées par des virgules : Option1, Option2, Option3OU :
Référencez une plage de cellules contenant les options (ex. A1:A5).
c. Validation Personnalisée avec Formules
Vous pouvez utiliser des formules pour créer des règles complexes.
Exemples :
Autoriser uniquement des nombres pairs :=MOD(A1, 2)=0
Limiter l’entrée à une longueur maximale :=NBCAR(A1)<=10
Autoriser une entrée seulement si une autre cellule a une valeur spécifique :=B1="Oui"
4. Personnaliser les Messages
Message d’Entrée :
Affiche une note lorsqu’une cellule est sélectionnée.
Exemple : “Entrez un nombre entre 1 et 100.”
Message d’Erreur :
Affiche un avertissement si une entrée non valide est saisie.
Types d’alertes :
Arrêt : Bloque complètement l’entrée.
Avertissement : Donne un avertissement mais permet l’entrée.
Information : Fournit une note mais n’empêche pas l’entrée.
5. Utilisation Avancée
a. Validation Dynamique avec Plages Nomées
Créez une plage nommée pour une liste de valeurs (ex. Catégories pour A1:A5).
Dans la validation des données, entrez =Catégories comme source.
b. Validation avec Valeurs Dynamiques
Utilisez la fonction DECALER ou INDIRECT pour créer une liste déroulante dépendante :
Créez une liste principale (ex. Catégories : Fruits, Légumes).
Créez des sous-listes pour chaque catégorie (ex. Fruits : Pomme, Orange).
Utilisez cette formule pour lier les sous-listes : =INDIRECT(A1)
6. Résolution des Problèmes
Problème : Validation Non Appliquée
Cause : Les données ont été copiées dans la cellule après l’application de la validation.
Solution : Réappliquez la validation ou utilisez Collage spécial > Valeurs.
Problème : Message d’Erreur Irrélevant
Cause : La règle ou la formule utilisée est incorrecte.
Solution : Vérifiez la logique de la formule.
7. Applications Pratiques
a. Gérer les Saisies
Liste déroulante pour choisir des options standardisées (ex. États, Catégories).
b. Vérifier des Dates
Limitez les dates à celles comprises dans un projet.
c. Simplifier les Calculs
Empêchez les erreurs de saisie qui pourraient invalider des formules.
Résumé des Étapes
Étape
Action
1. Accéder à la validation des données
Allez dans Données > Validation des données.
2. Choisir une règle
Sélectionnez parmi Nombre, Liste, Date, ou utilisez une formule personnalisée.
3. Ajouter des messages
Personnalisez les messages d’entrée et d’erreur pour guider les utilisateurs.
4. Tester et ajuster
Saisissez des données pour vérifier que les règles fonctionnent comme prévu.
Étude de Cas : Gestion d’un Formulaire de Saisie avec Validation des Données dans Excel
Problématique :
Vous êtes chargé de créer un formulaire de saisie des employés dans une entreprise. Ce formulaire doit garantir que :
Les départements saisis sont limités à une liste pré-approuvée.
Les âges des employés sont compris entre 18 et 65 ans.
Les dates d’embauche ne doivent pas être futures.
Le poste saisi doit être cohérent avec le département choisi (validation dépendante).
1. Organisation des Données
Données Préliminaires
Créez un tableau pour les départements et postes correspondants :
Département
Postes
RH
Responsable, Assistant
IT
Développeur, Analyste
Finance
Comptable, Auditeur
Structure du Formulaire
Dans une nouvelle feuille, créez un tableau pour les employés :
Nom
Département
Poste
Âge
Date d’embauche
Employé 1
Employé 2
2. Mettre en Place les Règles de Validation
a. Validation pour le Département
Créer une Liste Déroulante :
Sélectionnez les cellules de la colonne Département.
Allez dans Données > Validation des données.
Autoriser : Liste.
Source : Tapez directement les départements séparés par des virgules : RH, IT, Finance
Cliquez sur OK.
b. Validation pour l’Âge
Définir une Plage d’Âge :
Sélectionnez les cellules de la colonne Âge.
Allez dans Données > Validation des données.
Autoriser : Nombre entier.
Données : Compris entre.
Minimum : 18.
Maximum : 65.
Cliquez sur OK.
Ajouter un Message d’Erreur :
Allez dans l’onglet Alerte d’erreur.
Ajoutez un message comme :
Titre : Âge invalide.
Message : “Veuillez entrer un âge entre 18 et 65 ans.”
c. Validation pour la Date d’Embauche
Limiter les Dates au Présent ou au Passé :
Sélectionnez les cellules de la colonne Date d’embauche.
Allez dans Données > Validation des données.
Autoriser : Date.
Données : Inférieur ou égal à.
Date maximum : =AUJOURDHUI().
Ajouter un Message d’Entrée :
Allez dans l’onglet Message d’entrée.
Ajoutez un message comme :
Titre : Date invalide.
Message : “Entrez une date qui n’est pas dans le futur.”
d. Validation pour le Poste
Créer une Validation Dépendante (Poste dépendant du Département) :
Organiser les données :
Créez une plage nommée pour chaque département avec les postes correspondants :
RH : Responsable, Assistant.
IT : Développeur, Analyste.
Finance : Comptable, Auditeur.
Créer la liste déroulante dépendante :
Sélectionnez les cellules de la colonne Poste.
Allez dans Données > Validation des données.
Autoriser : Liste.
Source : Utilisez la formule : =INDIRECT(B2)
B2 : La cellule contenant le département correspondant.
3. Tester et Valider
Testez les Entrées :
Essayez de saisir des données non conformes (ex. : un âge supérieur à 65 ans ou un poste hors département).
Vérifiez que les messages d’erreur s’affichent correctement.
Ajoutez des Scénarios :
Par exemple, un employé nommé “Alice” avec le département “Finance” et le poste “Comptable”.
4. Résultats Attendus
Nom
Département
Poste
Âge
Date d’embauche
Alice
Finance
Comptable
35
01/02/2023
Bob
IT
Développeur
28
15/03/2022
Clara
RH
Responsable
40
10/01/2020
5. Résolution de Problèmes
Problème : Liste déroulante pour Poste vide
Cause : Le département sélectionné n’a pas de plage nommée correspondante.
Solution : Assurez-vous que chaque département a une plage nommée.
Problème : Aucune alerte ne s’affiche
Cause : La validation des données a été copiée par erreur.