3 formules de validation de données personnalisées incontournables pour éviter les erreurs dans Excel

La validation des données dans Microsoft Excel est un excellent moyen de limiter ce qui peut être saisi dans une cellule. Cependant, créer des règles à l'aide d'une formule vous permet d'appliquer des paramètres encore plus précis. J'en utilise trois dans la plupart de mes feuilles de calcul.

Où saisir les formules de validation des données personnalisées (et comment configurer des alertes)

Avant de partager mes formules avec vous, je vais vous montrer rapidement où vous devrez les saisir et comment configurer des alertes d'erreur.

Sélectionnez toutes les cellules pertinentes et dans l'onglet Données, cliquez sur la partie principale du bouton fractionné « Validation des données ». Ensuite, cliquez sur « Personnalisé » dans la liste déroulante Autoriser de l'onglet Paramètres et saisissez la formule dans le champ Formule.

Une fois que vous avez entré votre formule, ouvrez l'onglet « Alerte d'erreur », définissez le style sur « Stop » et saisissez un titre et un message qui apparaîtront lorsque la règle que vous avez définie n'est pas respectée.

Formule 1 : Garantir des entrées uniques

La validation des données peut être utilisée pour éviter les valeurs en double dans une colonne, telles que les identifiants d'employé ou les numéros de facture, de sorte que chaque entrée diffère des autres.

Supposons que vous saisissiez les détails d'un employé dans ce tableau Excel et que vous souhaitiez que toutes les valeurs de la colonne Employee_ID soient uniques. Si quelqu'un essaie de saisir un identifiant qui existe déjà, vous avez besoin d'Excel pour bloquer l'entrée.

Pour appliquer cela, sélectionnez d’abord toute la colonne dans laquelle vous souhaitez que la règle soit appliquée.

Pour sélectionner une colonne dans un tableau Excel formaté, sélectionnez la cellule la plus haute (à l’exclusion de l’en-tête de colonne) et appuyez sur Ctrl+Maj+Flèche vers le bas. Vous pouvez également passer votre souris sur l'en-tête de colonne jusqu'à ce que vous voyiez une petite flèche noire vers le bas, puis cliquer une fois.

Ensuite, lancez la boîte de dialogue « Validation des données » et dans l'onglet Paramètres, saisissez ceci dans le champ Formule (en supposant que vous souhaitiez restreindre la colonne A) :

=COUNTIF($A:$A,A2)=1

Voici ce que fait chaque partie de cette formule :

• $A : $A : Les signes dollar verrouillent la plage de colonnes, de sorte que la règle vérifie toujours la même liste complète d'entrées dans la colonne A. Cela pérennise la formule et tient compte du fait que vous ne pouvez pas utiliser de références de table structurées dans les formules de validation de données personnalisées.
• A2 : Excel ajuste automatiquement cette référence relative afin que chaque cellule de la colonne soit validée individuellement.
• =1 : C'est la condition du test. La formule renvoie VRAI (avec réussite de la validation) uniquement si la valeur saisie apparaît exactement une fois dans la plage.

Ensuite, allez dans l'onglet « Alerte d'erreur », sélectionnez « Arrêter » et saisissez un titre approprié (comme ID en double détecté) et un message (tel que Cet ID d'employé existe déjà dans la liste. Veuillez saisir un identifiant unique.)

Enfin, cliquez sur « OK » et entrez un identifiant en double dans la cellule suivante de la colonne A pour voir le filet de sécurité d'unicité en action.

La validation des données s'exécute uniquement lorsque vous saisissez ou modifiez des données après qu'une règle a été imposée à une cellule.

Formule 2 : Bloquer les entrées de dates futures

Lorsque vous enregistrez des événements passés dans une feuille de calcul Excel, vous pouvez utiliser la fonction AUJOURD'HUI simple mais puissante pour vous assurer que les dates futures ne sont pas saisies.

Imaginons que vous suiviez les dates d'expédition dans la colonne B de ce tableau et que vous souhaitiez vous assurer que seules les dates passées peuvent être saisies.

Tout d'abord, les cellules de cette colonne doivent être correctement formatées en tant que dates afin qu'Excel reconnaisse l'entrée. Pour ce faire, après avoir sélectionné la colonne, appuyez sur Ctrl+1 pour ouvrir la boîte de dialogue Format de cellule, puis dans l'onglet Nombre, sélectionnez « Date ». Ensuite, choisissez un format qui vous convient et qui correspond à vos paramètres régionaux, puis cliquez sur « OK ».

Après avoir fermé la boîte de dialogue Format de cellule, avec la plage toujours sélectionnée, ouvrez « Validation des données », sélectionnez « Personnalisé » et entrez la formule suivante (en supposant que les dates sont dans la colonne B) :

=B2<=TODAY()

Voici comment fonctionne la formule :

• B2 : Il s'agit de la référence relative à la cellule en cours de validation.
• <= : Lorsqu'il est utilisé pour évaluer des dates, le inférieur ou égal à opérateur signifie avant ou à la date de.
• AUJOURD'HUI(): Cela renvoie la date actuelle du système.

Même si la fonction AUJOURD'HUI se met à jour chaque fois que le classeur est ouvert ou recalculé, Excel ne réexécute pas le contrôle de validation à moins que la cellule contenant la date ne soit modifiée. Cela signifie qu'une inscription acceptée aujourd'hui restera toujours valable.

Ensuite, dans l'onglet Alerte d'erreur, sélectionnez « Arrêter » et ajoutez un titre approprié (tel que Date future non autorisée) et un message (comme La date doit être aujourd'hui ou une date passée. Veuillez corriger votre entrée.)

Maintenant, cliquez sur « OK » et entrez une date future pour voir un message d'erreur qui bloque l'entrée non valide.

Pour bloquer les dates passées au lieu des dates futures, retournez l'opérateur de < à > dans la formule.

Pourquoi utiliser Personnalisé au lieu de l'option de date intégrée

Excel propose une option Date intégrée dans le champ Autoriser qui permet également d'obtenir le même résultat. Cependant, l'utilisation de Custom est une meilleure option pour deux raisons principales :

• Combinaison logique : Il est plus facile de combiner la fonction AUJOURD'HUI avec AND ou OR pour appliquer deux règles en même temps.
• Flexibilité: La saisie d'une formule permet une logique de date dynamique complexe, comme l'utilisation d'EOMONTH ou la vérification uniquement des jours de la semaine.

Formule 3 : imposer un format de saisie spécifique

L'un des plus gros problèmes lorsque vous nettoyez des données est le formatage incohérent. Par exemple, vous pouvez avoir une colonne dont les entrées doivent contenir des nombres à 10 chiffres, donc toutes les entrées plus longues, plus courtes ou contenant des lettres doivent être rejetées. Vous pouvez appliquer simultanément des règles strictes sur la longueur et le type de données en utilisant AND, LEN et ISNUMBER.

Supposons que vous ayez configuré cette feuille de calcul et que vous soyez sur le point de saisir des numéros de série dans la colonne B. Ceux-ci doivent comporter 10 caractères et contenir uniquement des chiffres.

Pour mettre en œuvre cette règle, sélectionnez la colonne entière, ouvrez « Validation des données », sélectionnez « Personnalisé » et entrez cette formule (en supposant que vous souhaitiez restreindre les entrées dans la colonne B) :

=AND(LEN(B2)=10,ISNUMBER(B2+0))

C'est ce que nous appliquons avec cette formule :

• ET(…): Cette fonction garantit que toutes les conditions doivent être remplies pour que la validation réussisse.
• LEN(B2)=10 : La fonction LEN compte le nombre de caractères dans la cellule B2, et celui-ci doit être égal à 10.
• ESTNUMÉRO(B2+0) : L'ajout de +0 est une astuce d'Excel : si la cellule B2 ne contient que des caractères numériques, cela oblige Excel à la traiter comme un nombre, même si la cellule est formatée en texte. Si tous les caractères sont des nombres, ISNUMBER renvoie TRUE.

Ensuite, dans l'onglet Alerte d'erreur, sélectionnez « Arrêter » et saisissez un titre (comme Entrée invalide) et un message (tel que L'entrée doit comporter exactement 10 chiffres et ne contenir aucune lettre ni caractère spécial.)

Cliquez sur « OK » et entrez un numéro à neuf chiffres ou un code alphanumérique à 10 caractères pour voir l'alerte.

---

Dans le troisième exemple ci-dessus, je vous ai montré comment limiter le nombre de caractères et autoriser uniquement les chiffres. Toutefois, vous pouvez saisir des règles de validation de données personnalisées qui imposent une combinaison plus nuancée de lettres et de chiffres dans un ordre particulier, par exemple une lettre suivie de six chiffres. La clé dans ce scénario est de créer les règles dans la feuille de calcul elle-même, puis de copier les résultats dans la boîte de dialogue Validation des données.