Excel Sandrine Marcos 0 Commentaires

La validation de données personnalisée dans Excel : un outil essentiel que vous ne soupçonniez pas

La validation de données personnalisée dans Excel : un guide complet pour maîtriser cet outil essentiel

La validation de données dans Excel permet de restreindre les types d’informations pouvant être saisies dans une cellule, qu’il s’agisse de nombres entiers, de décimales, de dates ou d’un nombre spécifique de caractères. Cependant, l’une des fonctionnalités les plus puissantes de cet outil reste la validation de données personnalisée, qui permet de créer des règles spécifiques en utilisant des formules.

Matériel nécessaire

  • Microsoft Excel (version 2016 ou ultérieure recommandée)
  • Un classeur Excel pour la pratique
  • Connaissances de base des formules Excel
  • Accès aux fonctions LEN, RIGHT, LEFT, VALUE, ISNUMBER, NOT et AND

Étape 1 : Préparation et bonnes pratiques

Conseil professionnel : éviter de construire la formule directement dans la boîte de dialogue

Lorsque l’on utilise la validation de données dans Excel, la tendance naturelle consiste à sélectionner les cellules concernées, cliquer sur “Validation des données” dans l’onglet Données du ruban, puis créer la règle directement dans la boîte de dialogue en sélectionnant “Personnalisé” et en tapant la formule dans le champ Formule.

LIRE AUSSI  Comment créer vos propres fonctions avec LAMBDA dans Excel

Pour les formules de validation complexes, il convient plutôt de procéder dans la feuille de calcul elle-même, puis de copier le résultat dans le champ Formule de la boîte de dialogue une fois terminé.

Avantages de cette approche :

  • Obtention d’info-bulles lors de la saisie de formules dans une cellule
  • Possibilité de tester chaque partie de la formule individuellement
  • Meilleure visibilité du processus de construction

Étape 2 : Premier exemple – Une lettre et six chiffres

Configuration du scénario

Dans cet exemple, l’objectif consiste à créer une règle de validation pour que chaque code d’entreprise comprenne une lettre suivie de six chiffres, soit sept caractères au total.

Définition des règles individuelles

Avant de construire la formule finale, il faut définir les règles individuelles :

  1. Le code doit contenir exactement sept caractères
  2. Les six derniers caractères doivent être des chiffres
  3. Le premier caractère doit être une lettre

Construction des formules individuelles

Pour vérifier le nombre de caractères, on utilise la fonction LEN. Dans une cellule d’aide (par exemple F2), on tape :

=LEN(C2)=7

Cette formule retourne VRAI si le nombre de caractères dans la cellule C2 égale sept.

Pour tester si les six derniers caractères sont des chiffres, on utilise les fonctions RIGHT, VALUE et ISNUMBER :

=ISNUMBER(VALUE(RIGHT(C2,6)))

Pour vérifier que le premier caractère est une lettre (et non un chiffre), on utilise :

=NOT(ISNUMBER(VALUE(LEFT(C2,1))))

Combinaison des formules avec la fonction AND

Une fois les trois formules individuelles créées et testées, il faut les combiner avec la fonction AND. La formule finale ressemble à :

=AND(LEN(C2)=7,ISNUMBER(VALUE(RIGHT(C2,6))),NOT(ISNUMBER(VALUE(LEFT(C2,1)))))

Application de la règle de validation

  1. Copier la formule combinée depuis la barre de formule
  2. Sélectionner les cellules concernées (C2 à C6)
  3. Cliquer sur “Validation des données” dans l’onglet Données
  4. Sélectionner “Personnalisé” dans le champ Autoriser
  5. Coller la formule dans le champ Formule
  6. Configurer le message de saisie et l’alerte d’erreur
  7. Cliquer sur OK
LIRE AUSSI  Trois méthodes pour empêcher les fonctions volatiles dans Excel

Étape 3 : Deuxième exemple – Deux lettres spécifiques et quatre chiffres

Adaptation des règles

Dans ce second exemple, les codes doivent contenir les lettres “CC” suivies de quatre chiffres, soit six caractères au total.

Formules adaptées

Vérification du nombre de caractères :

=LEN(C2)=6

Vérification que les quatre derniers caractères sont des chiffres :

=ISNUMBER(VALUE(RIGHT(C2,4)))

Vérification que les deux premiers caractères sont “CC” :

=LEFT(C2,2)="CC"

Important : Il faut toujours utiliser des guillemets doubles autour du texte dans les formules Excel.

Formule combinée finale

=AND(LEN(C2)=6,ISNUMBER(VALUE(RIGHT(C2,4))),LEFT(C2,2)="CC")

Étape 4 : Troisième exemple – Deux lettres quelconques et trois chiffres

Complexité supplémentaire

Ce dernier exemple nécessite deux lettres (quelconques) suivies de trois chiffres. La difficulté réside dans la validation que les deux premiers caractères sont bien des lettres.

Problème avec l’approche standard

La formule =NOT(ISNUMBER(VALUE(LEFT(C2,2)))) ne fonctionne pas correctement car une chaîne contenant à la fois des lettres et des chiffres est considérée comme textuelle.

Solution avec validation caractère par caractère

Il faut vérifier chaque caractère individuellement :

Premier caractère :

=NOT(ISNUMBER(VALUE(LEFT(C2,1))))

Deuxième caractère :

=NOT(ISNUMBER(VALUE(MID(C2,2,1))))

Trois derniers caractères :

=ISNUMBER(VALUE(RIGHT(C2,3)))

Formule finale complète

=AND(LEN(C2)=5,NOT(ISNUMBER(VALUE(LEFT(C2,1)))),NOT(ISNUMBER(VALUE(MID(C2,2,1)))),ISNUMBER(VALUE(RIGHT(C2,3))))

Étape 5 : Optimisation et gestion avancée

Utilisation du volet Presse-papiers

Pour faciliter la construction de formules complexes :

  1. Ouvrir le volet Presse-papiers depuis l’onglet Accueil
  2. Copier chaque sous-formule individuellement
  3. Construire la formule AND en cliquant sur chaque élément du presse-papiers
  4. Séparer chaque argument par une virgule

Duplication des règles de validation

Pour appliquer les mêmes règles à d’autres cellules :

  1. Copier une cellule contenant déjà la règle
  2. Sélectionner les cellules de destination
  3. Appuyer sur Ctrl+Alt+V pour ouvrir Collage spécial
  4. Cocher “Validation”
  5. Cliquer sur OK
LIRE AUSSI  L'intégration de Python dans Excel permet maintenant d'analyser et de modifier des images directement dans vos feuilles de calcul

Étape 6 : Configuration des messages et alertes

Message de saisie

Dans l’onglet “Message de saisie” de la boîte de dialogue Validation des données :

  • Cocher “Quand la cellule est sélectionnée”
  • Saisir un titre explicite
  • Rédiger un message d’aide clair

Alerte d’erreur

Dans l’onglet “Alerte d’erreur” :

  • Choisir le style d’alerte (Arrêt, Avertissement, ou Information)
  • Définir un titre d’erreur
  • Rédiger un message d’erreur informatif

Étape 7 : Tests et validation

Procédure de test

  1. Tester avec des valeurs valides pour vérifier l’acceptation
  2. Tester avec des valeurs invalides pour vérifier le rejet
  3. Vérifier que les messages d’aide et d’erreur s’affichent correctement
  4. Tester les cas limites (longueur exacte, caractères spéciaux)

Nettoyage final

Une fois les règles de validation appliquées et testées :

  • Supprimer les cellules d’aide utilisées pour construire les formules
  • Vider le presse-papiers si nécessaire
  • Documenter les règles de validation pour les utilisateurs

Conclusion

La validation de données personnalisée dans Excel représente un outil puissant pour garantir l’intégrité des données saisies. En combinant plusieurs fonctions Excel avec la fonction AND, il devient possible de créer des règles de validation sophistiquées qui vérifient simultanément plusieurs critères. Cette approche méthodique, qui consiste à construire et tester chaque composant individuellement avant de les combiner, assure la fiabilité des règles créées et facilite leur maintenance ultérieure.

La maîtrise de ces techniques permet aux utilisateurs d’Excel de créer des systèmes de saisie robustes, réduisant significativement les erreurs de données et améliorant la qualité globale des informations traitées dans leurs feuilles de calcul.

Rédactrice spécialisée en édition de site. Formation de journaliste et passionnée par les nouvelles technologies, l’intelligence artificielle et la rédaction web.

Related Posts

Laisser un commentaire