Validation de données et listes déroulantes : fiabiliser la saisie

Un journal saisi à la main finit toujours par contenir des fautes : AC avec un espace, ac en minuscules, Banque à la place de BQ, une date en 2062 au lieu de 2026, un montant négatif saisi par erreur. Chaque variante crée une catégorie de plus dans un tri, un filtre ou un tableau croisé. La validation de données empêche ces fautes à la source, et elle coûte cinq minutes à mettre en place.

Ce que vous saurez faire à la fin de l’épisode

• créer une liste déroulante sur un journal, un compte, un taux ;
• restreindre une date à l’exercice et un montant à une valeur positive ;
• écrire une règle personnalisée (pièce unique, longueur d’un numéro) ;
• rédiger des messages de saisie et d’erreur clairs ;
• repérer les données non valides déjà présentes dans un fichier.

Où se trouve la validation ?

Données › Validation des données ouvre une boîte à trois onglets :

• Options : ce qui est autorisé (le critère) ;
• Message de saisie : une bulle qui s’affiche quand on sélectionne la cellule (« Choisissez le journal ») ;
• Alerte d’erreur : le message qui s’affiche si la saisie est refusée, avec trois niveaux : Arrêt (refus), Avertissement (l’utilisateur peut passer outre), Information (simple note).

Les critères disponibles dans Autoriser :

Le cas : saisir un journal sans fautes

Le journal d’Atlas Négoce comporte six colonnes : date, journal, compte, pièce, montant hors taxes, taux de TVA. Les valeurs autorisées pour les journaux, les comptes et les taux sont rangées dans une feuille Listes.

Figure 1 : les listes sont saisies une seule fois dans une feuille de référence. Elles alimentent les listes déroulantes de la feuille Saisie.

1. Une liste déroulante pour le journal

1. Sélectionnez la colonne Jnl de la feuille Saisie (B2:B50).
2. Données › Validation des données, Autoriser : Liste.
3. Dans Source, cliquez sur la feuille Listes et sélectionnez A2:A5. Excel écrit =Listes!$A$2:$A$5.
4. Laissez cochées Ignorer si vide et Liste déroulante dans la cellule.
5. Dans l’onglet Alerte d’erreur, saisissez un titre (« Journal inconnu ») et un message (« Choisissez un journal dans la liste »).

Figure 2 : une flèche apparaît à droite de la cellule active ; la liste propose les quatre journaux.

Même méthode pour les comptes (Listes!$C$2:$C$7) et les taux de TVA (Listes!$F$2:$F$4). La colonne Compte devient ainsi impossible à mal saisir : plus de 61111, plus de 6 111, plus de fautes qui rendraient une recherche infructueuse.

Pour une liste courte et stable, on peut taper les valeurs directement dans Source en les séparant par des points-virgules : AC;VT;BQ;OD. L’inconvénient est qu’il faudra rouvrir la validation à chaque ajout ; la feuille de listes est meilleure.

2. Une date dans l’exercice

Autoriser : Date, Données : comprise entre, Date de début 01/01/2026, Date de fin 31/12/2026. Mieux : utilisez des formules pour suivre le paramètre Exercice de l’épisode précédent : =DATE(Exercice;1;1) et =DATE(Exercice;12;31). Quand l’exercice changera, la règle suivra. Une date saisie en 2062 ou en 2062-01-05 par erreur est alors refusée.

3. Un montant strictement positif

Autoriser : Décimal, Données : supérieur à, Minimum 0. Les montants du journal sont saisis en valeur absolue dans la colonne débit ou crédit : le signe n’est pas une information. Cette règle bloque aussi les textes (« 12 500 MAD ») qui ne sont pas des nombres.

4. Une pièce unique

Dans un journal, deux écritures différentes ne doivent pas porter le même numéro de pièce (au sein d’un même journal). Le critère Personnalisé accepte une formule qui doit renvoyer VRAI pour que la saisie soit acceptée :

=NB.SI($D$2:$D$50;D2)=1

Lecture : « le nombre de fois où la valeur de D2 apparaît dans D2:D50 doit être exactement 1 ». À l’entrée de FA-0101 une seconde fois, la formule renvoie FAUX et la saisie est refusée. Notez la référence à la première cellule de la plage (D2, relative) : Excel adapte la formule à chaque ligne.

D’autres formules personnalisées utiles :

Les messages : expliquer plutôt que refuser

Un utilisateur qui voit « Cette valeur ne correspond pas aux restrictions de validation des données définies pour cette cellule » ne sait pas ce qu’on attend de lui. Rédigez deux messages :

• Message de saisie (facultatif) : une phrase d’aide qui s’affiche à la sélection (« Journal : AC, VT, BQ ou OD »). Utile pour les cellules moins évidentes ;
• Alerte d’erreur : titre court + message qui dit ce qui est attendu (« La date doit être comprise entre le 01/01/2026 et le 31/12/2026 »).

Choisissez le style Arrêt pour les règles strictes (compte, date, journal) et Avertissement pour les règles de bon sens qui admettent des exceptions (un montant exceptionnellement élevé).

Les limites : ce que la validation ne fait pas

• Elle ne contrôle pas le collage. Un Ctrl + V remplace non seulement la valeur mais aussi la règle de validation de la cellule. Pour la protéger, il faut verrouiller la feuille et ne laisser modifiables que les cellules de saisie (épisode 49), ou utiliser le collage spécial Valeurs.
• Elle ne contrôle pas les données existantes. Si vous ajoutez une règle à un journal déjà rempli, les valeurs fautives restent. Utilisez Données › Validation des données › Entourer les données non valides : Excel dessine un cercle rouge autour de chaque valeur qui ne respecte pas la règle, et Effacer les cercles de validation les retire.
• Elle ne remplace pas les contrôles comptables : une écriture avec le bon compte et le bon journal peut quand même être déséquilibrée. La validation traite la forme, pas le fond (épisode 33).

Quelques bonnes pratiques

1. Une feuille Listes unique, distincte de la feuille Paramètres si les listes sont longues, avec un en-tête et un nom par colonne.
2. Une liste = une colonne sans cellule vide au milieu ; sinon la liste déroulante affiche des lignes vides.
3. Des listes évolutives : mettez-les en tableau Excel, ou nommez-les.
4. Pas de validation sur les formules : elle n’a de sens que sur les cellules de saisie.
5. Documentez : dans l’onglet Lisez-moi, indiquez quelles colonnes sont contrôlées et par quelle règle.

À vous de jouer

Dans le classeur d’exercice (les règles de validation y sont déjà posées sur la feuille Saisie) :

1. Cliquez sur une cellule de la colonne Jnl et choisissez VT dans la liste. Essayez de saisir XX : quel message obtenez-vous ?
2. Saisissez une date de 2025 dans la colonne Date : que se passe-t-il ?
3. Saisissez -500 dans Montant HT : même question.
4. Ajoutez la validation personnalisée de pièce unique sur la colonne Pièce (D2:D50) avec =NB.SI($D$2:$D$50;D2)=1, puis tentez de saisir FA-0101 une seconde fois.
5. Collez la valeur XX (copiée depuis une autre cellule) dans la colonne Jnl avec Ctrl + V. Puis utilisez Entourer les données non valides.

Correction. Aux étapes 1 à 3, Excel refuse la saisie avec le message d’erreur rédigé (journal inconnu, date hors exercice, montant invalide). À l’étape 4, la formule renvoie FAUX parce que NB.SI compte alors 2 occurrences de FA-0101 : la saisie est refusée. À l’étape 5, le collage contourne la règle : XX est accepté, et c’est la commande Entourer les données non valides qui l’encercle en rouge.

À retenir

• La validation de données contrôle ce que l’utilisateur peut saisir : liste, nombre, date, longueur, formule.
• Les listes se rangent dans une feuille de référence et se réutilisent partout.
• Rédigez un message d’erreur qui dit ce qui est attendu.
• Le collage contourne la validation : prévoyez la protection de feuille et le contrôle a posteriori.
• La validation contrôle la forme d’une saisie, pas sa justesse comptable.

Épisode 12 : l’impression et l’export PDF, pour que vos états soient lisibles sur papier et prêts à signer. C’est la dernière étape du module 2.