Tableaux Excel

Guide : Validation des Données dans Excel

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 ?

  1. Sélectionnez une cellule ou une plage de cellules.
  2. Allez dans l’onglet Données.
  3. 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

  1. 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.
  2. Décimales :
    • Permet les nombres avec décimales.
    • Exemple : Autoriser uniquement des notes entre 0 et 20 avec décimales.
  3. 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 :

  1. Sélectionnez les cellules où ajouter la liste déroulante.
  2. Allez dans Validation des données.
  3. Dans Autoriser, sélectionnez Liste.
  4. 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 :

  1. Autoriser uniquement des nombres pairs : =MOD(A1, 2)=0
  2. Limiter l’entrée à une longueur maximale : =NBCAR(A1)<=10
  3. Autoriser une entrée seulement si une autre cellule a une valeur spécifique : =B1="Oui"

4. Personnaliser les Messages

  1. Message d’Entrée :
    • Affiche une note lorsqu’une cellule est sélectionnée.
    • Exemple : “Entrez un nombre entre 1 et 100.”
  2. 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

  1. Créez une plage nommée pour une liste de valeurs (ex. Catégories pour A1:A5).
  2. 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 :

  1. Créez une liste principale (ex. Catégories : Fruits, Légumes).
  2. Créez des sous-listes pour chaque catégorie (ex. Fruits : Pomme, Orange).
  3. 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

ÉtapeAction
1. Accéder à la validation des donnéesAllez dans Données > Validation des données.
2. Choisir une règleSélectionnez parmi Nombre, Liste, Date, ou utilisez une formule personnalisée.
3. Ajouter des messagesPersonnalisez les messages d’entrée et d’erreur pour guider les utilisateurs.
4. Tester et ajusterSaisissez 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 :

  1. Les départements saisis sont limités à une liste pré-approuvée.
  2. Les âges des employés sont compris entre 18 et 65 ans.
  3. Les dates d’embauche ne doivent pas être futures.
  4. 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épartementPostes
RHResponsable, Assistant
ITDéveloppeur, Analyste
FinanceComptable, Auditeur

Structure du Formulaire

Dans une nouvelle feuille, créez un tableau pour les employés :

NomDépartementPosteÂgeDate d’embauche
Employé 1
Employé 2

2. Mettre en Place les Règles de Validation

a. Validation pour le Département

  1. 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

  1. 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.
  2. 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

  1. 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().
  2. 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

  1. 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

  1. 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.
  2. Ajoutez des Scénarios :
    • Par exemple, un employé nommé “Alice” avec le département “Finance” et le poste “Comptable”.

4. Résultats Attendus

NomDépartementPosteÂgeDate d’embauche
AliceFinanceComptable3501/02/2023
BobITDéveloppeur2815/03/2022
ClaraRHResponsable4010/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.
  • Solution : Réappliquez la validation.

6. Résumé

ColonneType de ValidationExemple
DépartementListe déroulanteRH, IT, Finance
ÂgeNombre entier (18 à 65)35
Date d’embaucheDate (inférieure ou égale à AUJOURDHUI)01/02/2023
PosteListe déroulante dépendante du DépartementComptable (Finance)

Autres articles

Calculer un écart-type dans Excel
L’écart-type est une mesure de la dispersion des données autour...
Read more
Guide : Formules Matricielles dans Excel pour...
Les formules matricielles dans Excel pour Mac permettent de traiter...
Read more
Guide : Comprendre et Utiliser les Formules...
Les formules matricielles permettent de réaliser des calculs avancés en...
Read more

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *