Excel Validation des Données : Comment Garantir des Saisies Impeccables ?

Qu'est-ce que la Validation des Données Excel ?

La validation des données Excel est un outil qui permet de définir des règles pour les données pouvant être saisies dans une cellule ou une plage de cellules. Cela permet de garantir la cohérence des données, de réduire les erreurs de saisie et de faciliter l'analyse des données. En d'autres termes, vous déterminez à l'avance ce qui est acceptable comme entrée, et Excel se charge de faire respecter ces règles.

Pourquoi utiliser la validation des données ?

• Réduire les erreurs de saisie: En limitant les options disponibles, vous minimisez les risques de fautes de frappe ou d'incohérences.
• Améliorer la qualité des données: Des données cohérentes et valides facilitent l'analyse et la prise de décision.
• Simplifier la saisie: Les listes déroulantes permettent aux utilisateurs de sélectionner des options prédéfinies, ce qui accélère le processus de saisie.
• Standardiser les données: Assurez-vous que les données sont saisies de manière uniforme, ce qui facilite le tri, le filtrage et la recherche.
• Guider les utilisateurs: Les messages d'erreur et d'entrée aident les utilisateurs à comprendre les règles de validation et à saisir des données correctes.

Comment Mettre en Place la Validation des Données ?

La validation des données est accessible via l'onglet Données du ruban Excel, dans le groupe Outils de données. Le bouton s'appelle "Validation des données".

Étape 1 : Sélectionner la ou les cellules

Sélectionnez la ou les cellules auxquelles vous souhaitez appliquer la validation des données. Vous pouvez sélectionner une seule cellule, une plage de cellules adjacentes, ou même des cellules non adjacentes (en maintenant la touche Ctrl enfoncée).

Étape 2 : Ouvrir la boîte de dialogue Validation des données

Cliquez sur l'onglet Données, puis sur le bouton Validation des données dans le groupe Outils de données. Une boîte de dialogue s'ouvre avec trois onglets : "Options", "Message de saisie" et "Alerte d'erreur".

Étape 3 : Définir les critères de validation (Onglet Options)

L'onglet Options est l'endroit où vous définissez les règles de validation. Le champ Autoriser vous permet de choisir le type de données autorisé. Voici les options disponibles :

• Nombre entier: Autorise uniquement les nombres entiers dans une plage spécifiée.
• Décimal: Autorise uniquement les nombres décimaux dans une plage spécifiée.
• Liste: Autorise uniquement les valeurs présentes dans une liste déroulante.
• Date: Autorise uniquement les dates dans une plage spécifiée.
• Heure: Autorise uniquement les heures dans une plage spécifiée.
• Longueur du texte: Autorise uniquement le texte d'une longueur spécifiée.
• Personnalisé: Autorise uniquement les valeurs qui répondent à une formule personnalisée.

En fonction du type de données choisi, vous devrez spécifier des critères supplémentaires, tels que la plage de valeurs autorisées, la source de la liste déroulante, ou la formule personnalisée.

Exemple 1 : Limiter la saisie à des nombres entiers entre 1 et 10

1. Sélectionnez la cellule (ou les cellules) où vous souhaitez appliquer la validation.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Nombre entier dans le champ Autoriser.
4. Dans le champ Données, sélectionnez entre.
5. Dans les champs Minimum et Maximum, saisissez respectivement 1 et 10.
6. Cliquez sur OK.

Maintenant, si vous essayez de saisir un nombre entier en dehors de la plage 1-10, ou un nombre décimal, Excel affichera un message d'erreur.

Exemple 2 : Créer une liste déroulante de choix

1. Sélectionnez la cellule (ou les cellules) où vous souhaitez afficher la liste déroulante.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Liste dans le champ Autoriser.
4. Dans le champ Source, saisissez les valeurs de la liste, séparées par des virgules (par exemple, Oui,Non,Peut-être). Vous pouvez aussi faire référence à une plage de cellules contenant les valeurs de la liste (par exemple, =A1:A3).
5. Cochez la case Liste déroulante dans la cellule si vous souhaitez afficher une flèche de liste déroulante.
6. Cliquez sur OK.

Une liste déroulante apparaîtra dans la cellule sélectionnée, permettant aux utilisateurs de choisir parmi les options que vous avez définies.

Exemple 3 : Validation avec une formule personnalisée

Vous pouvez utiliser des formules pour créer des critères de validation plus complexes. Par exemple, vous pouvez valider une date en vérifiant qu'elle est postérieure à une autre date.

1. Sélectionnez la cellule (ou les cellules) où vous souhaitez appliquer la validation.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Personnalisé dans le champ Autoriser.
4. Dans le champ Formule, saisissez la formule qui doit être évaluée à VRAI pour que la valeur soit valide. Par exemple, si la cellule A1 contient une date de début et que vous voulez valider la date saisie dans la cellule B1, vous pouvez utiliser la formule =B1>A1.
5. Cliquez sur OK.

Étape 4 : Personnaliser le message de saisie (Onglet Message de saisie)

L'onglet Message de saisie vous permet d'afficher un message lorsque la cellule est sélectionnée. Ce message peut contenir des instructions ou des informations supplémentaires pour aider l'utilisateur à saisir des données correctes.

• Titre: Le titre du message de saisie.
• Message de saisie: Le texte du message de saisie.
• Afficher ce message quand la cellule est sélectionnée: Cochez cette case pour afficher le message lorsque la cellule est sélectionnée.

Étape 5 : Personnaliser l'alerte d'erreur (Onglet Alerte d'erreur)

L'onglet Alerte d'erreur vous permet de définir le type d'alerte à afficher lorsqu'une valeur non valide est saisie. Vous pouvez choisir parmi trois styles d'alerte :

• Arrêt: Empêche l'utilisateur de saisir une valeur non valide. L'utilisateur doit corriger la saisie pour pouvoir continuer.
• Avertissement: Affiche un message d'avertissement, mais permet à l'utilisateur d'ignorer l'avertissement et de saisir la valeur non valide.
• Information: Affiche un message d'information, mais permet à l'utilisateur de saisir la valeur non valide.

Vous pouvez également personnaliser le titre et le message de l'alerte d'erreur.

• Style: Le style de l'alerte (Arrêt, Avertissement, Information).
• Titre: Le titre de l'alerte d'erreur.
• Message d'erreur: Le texte du message d'erreur.
• Afficher le message d'erreur après la saisie de données non valides: Cochez cette case pour afficher l'alerte d'erreur lorsqu'une valeur non valide est saisie.

Exemples Pratiques de Validation des Données

Exemple 1 : Validation des adresses e-mail

Pour valider une adresse e-mail, vous pouvez utiliser une formule personnalisée qui vérifie si la chaîne de caractères contient un caractère "@" et un point ".".

1. Sélectionnez la cellule où vous souhaitez valider l'adresse e-mail.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Personnalisé dans le champ Autoriser.
4. Dans le champ Formule, saisissez la formule suivante : =ET(ESTNUM(CHERCHE("@";A1));ESTNUM(CHERCHE(".";A1))) (en remplaçant A1 par la cellule à valider).
5. Cliquez sur OK.

Cette formule utilise les fonctions CHERCHE pour trouver la position des caractères "@" et "." dans la cellule, et la fonction ESTNUM pour vérifier si ces caractères sont présents (c'est-à-dire si CHERCHE renvoie un nombre). La fonction ET combine les deux conditions, de sorte que la formule renvoie VRAI uniquement si les deux caractères sont présents.

Exemple 2 : Validation des numéros de téléphone

Pour valider un numéro de téléphone, vous pouvez utiliser la validation de la longueur du texte et des formules pour vérifier le format.

1. Sélectionnez la cellule où vous souhaitez valider le numéro de téléphone.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Longueur du texte dans le champ Autoriser.
4. Dans le champ Données, sélectionnez égal à.
5. Dans le champ Longueur, saisissez le nombre de chiffres attendu pour le numéro de téléphone (par exemple, 10 pour un numéro de téléphone à 10 chiffres).
6. Si vous souhaitez également vérifier que la cellule ne contient que des chiffres, vous pouvez ajouter une validation personnalisée avec la formule suivante : =ESTNUM(A1*1) (en remplaçant A1 par la cellule à valider). Cette formule multiplie la valeur de la cellule par 1 et utilise la fonction ESTNUM pour vérifier si le résultat est un nombre.
7. Cliquez sur OK.

Exemple 3 : Validation d'une date de naissance

Vous pouvez valider une date de naissance pour vous assurer qu'elle se situe dans une plage d'années raisonnable.

1. Sélectionnez la cellule où vous souhaitez valider la date de naissance.
2. Ouvrez la boîte de dialogue Validation des données.
3. Dans l'onglet Options, sélectionnez Date dans le champ Autoriser.
4. Dans le champ Données, sélectionnez entre.
5. Dans les champs Date de début et Date de fin, saisissez les dates de début et de fin de la plage d'années autorisée. Par exemple, vous pouvez utiliser la formule =DATE(ANNEE(AUJOURDHUI())-100;1;1) pour la date de début (100 ans avant aujourd'hui) et =DATE(ANNEE(AUJOURDHUI())-18;12;31) pour la date de fin (18 ans avant aujourd'hui).
6. Cliquez sur OK.

Astuces et Bonnes Pratiques pour la Validation des Données

• Utiliser des noms de plages: Au lieu de faire référence directement à des plages de cellules dans la source des listes déroulantes, utilisez des noms de plages. Cela facilite la modification des listes et rend vos formules plus lisibles.
• Créer des listes dynamiques: Utilisez des formules pour créer des listes déroulantes qui se mettent à jour automatiquement lorsque les données sources changent. Par exemple, vous pouvez utiliser les fonctions UNIQUE et FILTRE pour créer une liste unique de valeurs à partir d'une plage de données.
• Copier la validation des données: Vous pouvez copier la validation des données d'une cellule à une autre en utilisant l'outil Reproduire la mise en forme (le petit pinceau dans l'onglet Accueil).
• Effacer la validation des données: Pour supprimer la validation des données d'une cellule, sélectionnez la cellule, ouvrez la boîte de dialogue Validation des données et cliquez sur le bouton Effacer tout.
• Utiliser des messages d'erreur clairs et informatifs: Personnalisez les messages d'erreur pour aider les utilisateurs à comprendre pourquoi leur saisie n'est pas valide et comment la corriger.

Erreurs Courantes et Comment les Éviter

• Oublier de personnaliser les messages d'erreur: Les messages d'erreur par défaut d'Excel sont souvent peu clairs et peu utiles. Prenez le temps de les personnaliser pour guider les utilisateurs.
• Utiliser des références de cellules incorrectes: Vérifiez attentivement les références de cellules dans vos formules de validation, surtout si vous copiez et collez la validation dans d'autres cellules.
• Créer des listes déroulantes trop longues: Les listes déroulantes trop longues peuvent être difficiles à utiliser. Envisagez d'utiliser des listes déroulantes en cascade (où le choix dans une première liste filtre les options disponibles dans une deuxième liste).
• Ne pas tester la validation: Après avoir configuré la validation des données, testez-la soigneusement pour vous assurer qu'elle fonctionne comme prévu.

Conclusion

La validation des données est un outil essentiel pour garantir la qualité et la cohérence des données dans vos feuilles de calcul Excel. En utilisant les différentes options de validation et en suivant les bonnes pratiques, vous pouvez réduire les erreurs de saisie, simplifier la saisie des données et faciliter l'analyse des données. N'hésitez pas à expérimenter avec les différents exemples et astuces présentés dans cet article pour tirer le meilleur parti de cette fonctionnalité puissante.