Tutoriel Excel : validation des données

La validation des données est une fonctionnalité d'Excel utilisée pour contrôler ce qu'un utilisateur peut entrer dans une cellule. Par exemple, vous pouvez utiliser la validation des données pour vous assurer qu'une valeur est comprise entre 1 et 10, pour vous assurer qu'une date est définie dans les 30 prochains jours ou pour qu'une entrée de texte contienne moins de 25 caractères etc…

La validation des données dans Excel vous permet de contrôler les données pouvant être entrées dans une cellule, elle peut simplement afficher un message à un utilisateur en lui indiquant ce qui est autorisé. Vous pouvez limiter l'utilisateur à la saisie d'une plage spécifiée de chiffres, de texte ou de date. Vous pouvez également utiliser la fonctionnalité de validation des données pour créer une liste déroulante Excel (qui est certainement l'une des fonctionnalités les plus cool et les plus puissantes d'Excel).

Vous pouvez utiliser la validation des données pour limiter le type de données ou les valeurs que les utilisateurs entrent dans une cellule en plus la validation des données vérifie si un autre utilisateur entre les données conformément aux critères que vous avez imposés. Dans ce tutoriel, vous pourrez l’apprendre avec des exemples étape par étape, vous apprendrez à utiliser la validation de données. Si vous partagez une feuille à utiliser avec un grand nombre de personnes, vous pouvez utiliser cette fonctionnalité pour valider la saisie des données.

Le tutoriel explique comment valider les données dans Excel : créer une règle de validation pour les nombres, les dates ou les valeurs textuelles, créer des listes de validation des données, copier la validation des données dans d’autres cellules, rechercher des entrées non valides, corriger et supprimer la validation des données.

Lors de la configuration d'un classeur pour vos utilisateurs, vous souhaiterez souvent contrôler les informations saisies dans des cellules spécifiques pour vous assurer que toutes les entrées de données sont exactes et cohérentes. Entre autres choses, vous souhaiterez peut-être n'autoriser que certains types de données, tels que des nombres ou des dates dans une cellule, ou limiter les nombres à une plage donnée et le texte à une longueur donnée. Vous pouvez même vouloir fournir une liste prédéfinie d'entrées acceptables pour éliminer les erreurs possibles. La validation de données sur Excel vous permet de faire toutes ces choses dans toutes les versions de Microsoft Excel 2016, 2013, 2010 et versions antérieures.

Qu'est-ce que la validation des données dans Excel ?

La validation des données Excel est une fonctionnalité qui limite (valide) les entrées utilisateur dans une feuille de calcul. Techniquement, vous créez une règle de validation qui contrôle le type de données pouvant être entré dans une cellule donnée.

Voici quelques exemples de ce que la validation de données Excel peut faire :

• Autoriser uniquement les valeurs numériques ou textuelles dans une cellule.
• Autoriser uniquement les nombres dans une plage spécifiée.
• Autoriser les entrées de données d'un particulier
• Restreindre les dates et heures en dehors d'une période donnée.
• Limiter les entrées à une sélection dans une liste déroulante.
• Validez une entrée basée sur une autre cellule.
• Afficher un message de saisie lorsque l'utilisateur sélectionne une cellule.
• Afficher un message d'avertissement lorsque des données incorrectes ont été entrées.
• Recherchez les entrées incorrectes dans les cellules validées.

Par exemple, vous pouvez configurer une règle limitant la saisie de données à des nombres à 4 chiffres compris entre 1 100 et 8000. Si l'utilisateur tape quelque chose de différent, Excel affiche une alerte d'erreur expliquant ce qu'il a mal fait :

Comment faire la validation des données dans Excel

Pour ajouter la validation des données dans Excel, procédez comme suit.

1. Ouvrez la boîte de dialogue Validation des données.

Sélectionnez une ou plusieurs cellules à valider, accédez à l'onglet « Données » puis dans groupe « Outils de données » cliquez sur le bouton « Validation des données ».

2. Créer une règle de validation Excel

Dans l'onglet Paramètres, définissez les critères de validation en fonction de vos besoins. Dans les critères, vous pouvez fournir l'un des éléments suivants :

Valeurs : saisissez des nombres dans les zones de critères comme indiqué dans la capture d'écran ci-dessous.

Références de cellule : créez une règle basée sur une valeur ou une formule dans une autre cellule.

Formules : permettent d’exprimer des conditions plus complexes comme dans cet exemple.

A titre d'exemple, créons une règle qui limite les utilisateurs à la saisie d'un nombre entier compris entre 1100 et 8000, pour cela nous allons autoriser les « nombres entiers » devant le champ « autoriser » et puis nous allons choisir « comprise entre » devant le champ « données » et on fixe le minimum sur 1100 devant le champ « minimum » et le maximum sur « 8000 » devant le champ maximum.

La règle de validation étant configurée, cliquez sur OK pour fermer la fenêtre de validation des données ou passez à un autre onglet pour ajouter un message d'entrée et / ou une alerte d'erreur comme expliquer dans les étapes suivantes.

3. Ajouter un message d'entrée (facultatif)

Si vous souhaitez afficher un message expliquant à l'utilisateur quelles données sont autorisées dans une cellule donnée, ouvrez l'onglet « Message de saisie » et procédez comme suit :

Assurez-vous que la case Afficher le message d'entrée « quand la cellule est sélectionnée » est cochée puis entrez le titre et le texte de votre message dans les champs correspondants.

Cliquez sur OK pour fermer la fenêtre de dialogue ou passer à l’onglet suivante pour ajouter une alerte d’erreur comme expliqué dans l’étape qui suit.

4. Afficher une alerte d'erreur (facultatif)

En plus du message d'entrée, vous pouvez afficher l'une des alertes d'erreur suivantes lorsque des données non valides sont entrées dans une cellule. Excel nous donne la possibilité de choisir entre 3 types d’alertes :

o   Stop (par défaut) : Le type d'alerte le plus strict empêchant les utilisateurs d'entrer des données non valides. Vous cliquez sur « Réessayer » pour taper une valeur différente ou sur « Annuler » pour supprimer l'entrée. La fenêtre stop est comme décrit dans la capture ci-dessous.

o   Avertissement : Avertit les utilisateurs que les données sont invalides, mais n'empêche pas la saisie. Vous cliquez sur « Oui » pour saisir l'entrée non valide, sur « Non » pour la modifier ou sur « Annuler » pour la supprimer.

o   Information : Le type d'alerte le plus permissif qui informe uniquement les utilisateurs d'une saisie de données non valide. Vous cliquez sur « OK » pour entrer la valeur non valide ou sur « Annuler » pour la supprimer de la cellule.

Pour configurer un message d'erreur personnalisé, accédez à l'onglet « Alerte d'erreur » et définissez les paramètres suivants :

Cochez la case Afficher l'alerte d'erreur après la saisie de données non valides (généralement sélectionnée par défaut).

Dans la zone Style, sélectionnez le type d’alerte souhaité. Pour moi je vais choisir « Stop ».

Entrez le titre et le texte du message d'erreur dans les cases correspondantes.

Cliquez sur OK.

Dès que l'utilisateur sélectionne la cellule validée, le message de saisie apparaît comme dans la capture ci -dessous.

Et si l'utilisateur entre des données non valides, Excel affichera une alerte spéciale expliquant l'erreur comme indiqué dans la capture d’écran ci-dessous.

Remarque : Si vous ne tapez pas votre propre message, l'alerte d'arrêt par défaut avec le texte suivant s'affichera : Cette valeur ne correspond pas aux restrictions de validation des données définies pour cette cellule.

Exemples de validation de données Excel

Lors de l'ajout d'une règle de validation de données dans Excel, vous pouvez choisir l'un des paramètres prédéfinis ou spécifier des critères personnalisés en fonction de votre propre formule de validation. Ci-dessous, nous discuterons de chacune des options.

Comme vous le savez déjà, les critères de validation sont définis dans l'onglet « Paramètres » de la boîte de dialogue « Validation des données » (onglet Données > Validation des données).

Nombres entiers et décimaux

Pour limiter la saisie de données à un nombre entier ou à une décimale, sélectionnez l'élément correspondant dans la zone « Autoriser » de la boite de dialogue de validation des données. Et puis, choisissez l'un des critères suivants dans la zone « Données » :

-          Égal ou non au nombre spécifié

-          Supérieur ou inférieur au nombre spécifié

-          Entre les deux nombres ou non pour exclure cette plage de nombres

Par exemple, voici comment créer une règle de validation Excel autorisant tout nombre entier supérieur à 0 :

Validation de la date et de l'heure dans Excel

Pour valider les dates, sélectionnez « Date » dans la zone « Autoriser », puis choisissez un critère approprié dans la zone « Données ». Vous avez le choix entre de nombreuses options prédéfinies : n'autorisez que les dates comprises entre deux dates, égales, supérieures ou inférieures à une date spécifique, et plus.

De même, pour valider les heures, sélectionnez « Heure » dans la zone « Autoriser », puis définissez les critères requis.

Par exemple, pour autoriser uniquement les dates comprises entre la date de début dans C9 et la date de fin dans C2, appliquez cette règle de validation de date Excel :

Longueur du texte

Pour autoriser la saisie de données d'une longueur spécifique, sélectionnez « Longueur du texte » dans la zone « Autoriser », puis choisissez les critères de validation en fonction de votre logique métier.

Par exemple, pour limiter la saisie à 10 caractères, créez cette règle :

Remarque : L'option Longueur du texte limite le nombre de caractères mais pas le type de données, ce qui signifie que la règle ci-dessus autorise le texte et les nombres de moins de 10 caractères ou de 10 chiffres, respectivement.

Liste de validation des données Excel (liste déroulante)

Pour ajouter une liste déroulante d'éléments à une cellule ou à un groupe de cellules, sélectionnez les cellules cibles et procédez comme suit :

Ouvrez la boîte de dialogue « Validation des données » (onglet Données -> Validation des données).

Dans l’onglet « Paramètres », sélectionnez Liste dans la liste « Autoriser ».

Dans la zone « Source », tapez les éléments de votre liste de validation Excel, séparés par des virgules. Par exemple, pour limiter la saisie utilisateur à trois deux, tapez Oui, Non.

Assurez-vous que la « liste déroulante Dans la cellule » est coché afin que la flèche de la liste déroulante apparaisse à côté de la cellule.

Cliquez sur OK.

Autres façons de créer une liste de validation de données dans Excel

Fournir des listes séparées par des virgules directement dans la zone « Source » est le moyen le plus rapide qui fonctionne bien pour les petites listes déroulantes qui ne risquent pas de changer. Dans d'autres scénarios, vous pouvez utiliser l'une des méthodes suivantes :

-          Créez une liste de validation de données à partir d'une plage de cellules.

-          Créez une liste de validation dynamique des données basée sur une plage nommée.

-          Créez une liste de validation des données Excel à partir de la table.

 La meilleure chose à faire est qu'un menu déroulant basé sur une table soit par nature dynamique et se met à jour automatiquement lorsque vous ajoutez ou supprimez des éléments de la table.

Comment éditer la validation des données dans Excel

Pour modifier une règle de validation Excel, procédez comme suit :

Sélectionnez l'une des cellules validées et ouvrez la boîte de dialogue « Validation des données » (onglet Données -> Validation des données) ensuite apportez les modifications requises. Cochez la case « Appliquer ces modifications à toutes les autres cellules avec les mêmes paramètres » pour copier les modifications que vous avez apportées à toutes les autres cellules avec les critères de validation d'origine. Cliquez sur OK pour enregistrer les modifications.

Par exemple, vous pouvez modifier votre liste de validation de données Excel en ajoutant ou en supprimant des éléments de la zone « Source » et appliquer ces modifications à toutes les autres cellules contenant la même liste déroulante :

Vous pouvez constatez que lorsque vous cochez la case « Appliquer ces modifications à toutes les autres cellules avec les mêmes paramètres », les cellules concernés ont mis en surbrillance par Excel.

Comment copier une règle de validation de données Excel dans d'autres cellules

Si vous avez créé une règle de validation pour une cellule et souhaitez valider d'autres cellules avec les mêmes critères ultérieurement, vous n'avez pas à recréer la règle à partir de zéro. Pour copier la règle de validation dans Excel, procédez comme suit :

Commencez par sélectionner la cellule à laquelle s'applique la règle de validation et appuyez sur Ctrl + C pour la copier ou faite un clic droit et choisissez « copier » dans le menu contextuel. Ensuite sélectionnez les autres cellules que vous souhaitez valider. Pour sélectionner des cellules non adjacentes, maintenez la touche Ctrl enfoncée tout en sélectionnant les cellules.

Cliquez avec le bouton droit de la souris sur la sélection, cliquez sur « Collage spécial -> Collage spécial » et sélectionnez « Validation » dans le menu contextuel. Cliquez sur OK.

Comment trouver des cellules avec validation de données dans Excel

Pour localiser rapidement toutes les cellules validées dans la feuille de calcul actuelle, accédez à l'onglet « Accueil » puis dans le groupe « édition », puis cliquez sur « Rechercher et sélectionner » et choisissez « Validation données ». Cela sélectionnera toutes les cellules auxquelles des règles de validation de données sont appliquées :

Comment supprimer la validation des données dans Excel

Dans l’ensemble, il existe deux façons de supprimer la validation des données dans Excel : l’approche standard conçue par Microsoft et la technique sans souris conçue par les geeks d’Excel qui ne lâchent jamais les mains du clavier si ce n’est absolument nécessaire.

Méthode 1 : moyen régulier pour supprimer la validation des données

Normalement, pour supprimer la validation des données dans les feuilles de calcul Excel, procédez comme suit :

Commencez par sélectionner la ou les cellules avec la validation des données. Puis sous l'onglet « Données », cliquez sur le bouton « Validation des données », ensuite sous l'onglet « Paramètres » de la boite de dialogue de validation des données cliquez sur le bouton « Tout effacer », puis sur OK.

Remarque : Pour supprimer la validation des données de toutes les cellules de la feuille en cours, utilisez la fonctionnalité « Rechercher et sélectionner » pour sélectionner toutes les cellules validées.

Pour supprimer une certaine règle de validation des données, sélectionnez une cellule avec cette règle, ouvrez la boîte de dialogue « Validation des données », cochez la case « Appliquer ces modifications à toutes les autres cellules avec les mêmes paramètres », puis cliquez sur le bouton « Tout effacer ».

Comme vous le voyez, la méthode standard est assez rapide mais nécessite quelques clics de souris, ce qui n’a rien de grave en ce qui me concerne. Mais si vous préférez travailler avec le clavier plutôt que sur une souris, l'approche suivante peut sembler attrayante.

Méthode 2 : Collage spécial pour supprimer les règles de validation des données

Le collage spécial d’Excel est conçu pour coller des éléments spécifiques de cellules copiées. En effet, il peut faire beaucoup plus de choses utiles. Entre autres, il peut rapidement supprimer les règles de validation des données d'une feuille de calcul. Voici comment :

Sélectionnez une cellule vide sans validation des données et appuyez sur Ctrl + C pour la copier ensuite sélectionnez les cellules à partir desquelles vous souhaitez supprimer la validation des données et appuyez sur Ctrl + Alt + V, puis sur N, qui est le raccourci pour Collage spécial -> Validation des données. Appuyez sur Entrée et c’est Terminé !

Conseils de validation des données Excel

Maintenant que vous connaissez les bases de la validation des données dans Excel, laissez-moi vous donner quelques astuces qui peuvent rendre vos règles beaucoup plus efficaces.

Validation des données Excel basée sur une autre cellule

Au lieu de taper les valeurs directement dans les zones de critères, vous pouvez les entrer dans certaines cellules, puis faire référence à ces cellules. Si vous décidez de modifier les conditions de validation ultérieurement, vous devrez simplement taper de nouveaux numéros sur la feuille, sans avoir à modifier la règle.

Pour entrer une référence de cellule, tapez-la dans la zone précédée d'un signe égal ou cliquez sur la flèche en regard de la zone, puis sélectionnez la cellule à l'aide de la souris. Vous pouvez également cliquer n'importe où dans la zone, puis sélectionner la cellule de la feuille.

Par exemple, pour autoriser tout nombre entier autre que le nombre dans A9, sélectionnez le critère « différent de » dans la zone « Données » et tapez = $A$9 dans la zone Valeur ou cliquez sur la zone et sélectionnez la cellule A9 :

Pour aller plus loin, vous pouvez entrer une formule dans la cellule référencée et demander à Excel de valider l'entrée en fonction de cette formule.

Par exemple, pour empêcher les utilisateurs d'entrer des dates après la date d'aujourd'hui, entrez la formule = AUJOURDHUI() dans une cellule, par exemple C10, puis configurez une règle de validation Date basée sur cette cellule :

Ou bien, vous pouvez entrer la formule = AUJOURDHUI() directement dans la zone « Date de début », ce qui aura le même effet.

Règles de validation basées sur des formules

Dans les cas où il n'est pas possible de définir un critère de validation souhaité en fonction d'une valeur ou d'une référence de cellule, vous pouvez l'exprimer à l'aide d'une formule. Par exemple, pour limiter l'entrée aux valeurs minimale et maximale de la liste de nombres existante, par exemple D2:D8, utilisez les formules suivantes:

= MIN ($D$2:$D$8)

= MAX ($D$2: $D$8)

Veillez à ce que la plage soit verrouillée en utilisant le signe $ (références absolues de cellules) afin que notre règle de validation Excel fonctionne correctement pour toutes les cellules sélectionnées.

Comment trouver des données invalides sur la feuille

Bien que Microsoft Excel permette d'appliquer la validation des données aux cellules contenant déjà des données, il ne vous avertira pas si certaines des valeurs existantes ne répondent pas aux critères de validation.

Pour rechercher des données non valides qui se trouvaient dans vos feuilles de calcul avant l'ajout de la validation des données, accédez à l'onglet « Données » et cliquez sur « Validation des données » puis cliquez sur « Entourer les données non valides ».

Cela va mettre en surbrillance toutes les cellules qui ne répondent pas aux critères de validation :

Dès que vous corrigez une entrée invalide, le cercle disparaîtra automatiquement. Pour supprimer tous les cercles, accédez à l'onglet « Données » et cliquez sur « Validation des données » puis sur « Effacer les cercles de validation ».

 Comment protéger une feuille de calcul avec la validation des données

Si vous souhaitez protéger une feuille de calcul ou un classeur avec un mot de passe, configurez d'abord les paramètres de validation des données souhaités, puis protégez la feuille. Il est important de déverrouiller les cellules validées avant de protéger la feuille de calcul, sinon vos utilisateurs ne pourront pas entrer de données dans ces cellules.

Comment partager un classeur avec validation de données

Pour permettre à plusieurs utilisateurs de collaborer sur le classeur, veillez à le partager après la validation des données. Après avoir partagé le classeur, vos règles de validation des données continueront de fonctionner, mais vous ne pourrez ni les modifier ni en ajouter de nouvelles.

La validation des données Excel ne fonctionne pas

Si la validation des données ne fonctionne pas correctement dans vos feuilles de calcul, c'est probablement pour l'une des raisons suivantes.

La validation des données ne fonctionne pas pour les données copiées

La validation des données dans Excel est conçue pour interdire la saisie directe de données non valides dans une cellule, mais elle ne peut empêcher les utilisateurs de copier des données non valides. Bien qu'il n'y ait aucun moyen de désactiver les raccourcis copier / coller (autrement qu'en utilisant VBA), vous pouvez au moins empêcher la copie de données en glissant-déposant des cellules. Pour ce faire, accédez à Fichier -> Options -> Options avancées -> Options d’édition et désactivez la case à cocher « Activer le poignée de recopie et le glisser-déposer de cellule ».

La validation des données Excel n'est pas disponible en mode d'édition de cellule

La commande de validation des données est indisponible (grisée) si vous entrez ou modifiez des données dans une cellule. Une fois que vous avez terminé d’éditer la cellule, appuyez sur la touche clavier « Entrée » ou sur « Échap » pour quitter le mode édition, puis effectuez la validation des données.

La validation des données ne peut pas être appliquée à un classeur protégé ou partagé

Bien que les règles de validation existantes continuent de fonctionner dans des classeurs protégés et partagés, il est impossible de modifier les paramètres de validation des données ou de définir de nouvelles règles. Pour ce faire, supprimez le partage et / ou la protection de votre classeur en premier.

Formules de validation de données incorrectes

Lors de la validation de données à l'aide de formules dans Excel, trois éléments importants doivent être vérifiés :

-          La formule de validation ne retourne pas les erreurs.

-          La formule ne fait pas référence à des cellules vides.

-          Des références de cellules appropriées sont utilisées.

Le recalcul manuel est activé

Si le mode de calcul manuel est activé dans Excel, des formules non calculées peuvent empêcher la validation correcte des données. Pour rétablir automatiquement l'option de calcul Excel, accédez à l'onglet Formules -> groupe Calcul, cliquez sur le bouton « Options de calcul », puis sur « Automatique ».

Règles de validation personnalisée et formules

Comment créer une règle de validation personnalisée basée sur une formule

Microsoft Excel comporte plusieurs règles de validation de données intégrées pour les nombres, les dates et le texte, mais elles ne couvrent que les scénarios les plus élémentaires. Si vous souhaitez valider des cellules avec vos propres critères, créez une règle de validation personnalisée basée sur une formule. Voici comment le faire :

-          Sélectionnez une ou plusieurs cellules à valider.

-          Ouvrez la boîte de dialogue Validation des données. Pour cela, cliquez sur le bouton « Validation des données » de l'onglet « Données » du groupe « Outils de données ».

Dans l'onglet « Paramètres » de la boîte de dialogue « Validation des données », sélectionnez « Personnalisé » dans la zone « Autoriser », puis entrez votre formule de validation des données dans la zone Formule. Cliquez sur OK.

Exemple de règles validation personnalisée

Validation des données Excel pour autoriser uniquement les chiffres

De manière surprenante, aucune des règles de validation des données Excel intégrées ne permet une situation très typique dans laquelle vous devez limiter les utilisateurs à la saisie de nombres uniquement dans des cellules spécifiques. Mais ceci peut être facilement réalisé avec une formule de validation de données personnalisée basée sur la fonction ESTNUM, comme celle-ci :

= ESTNUM(C2)

Où C2 est la cellule la plus haute de la plage que vous souhaitez valider.

Remarque : La fonction ESTNUM autorise toutes les valeurs numériques dans les cellules validées, y compris les entiers, les décimales, ainsi que les dates et les heures, qui sont également des nombres en termes d'Excel.

Validation des données Excel pour autoriser uniquement le texte

Si vous recherchez le contraire - autoriser uniquement les entrées de texte dans une plage de cellules donnée, créez une règle personnalisée avec la fonction ESTTEXTE, par exemple :

= ESTTEXTE(B2)

Où B2 est la cellule la plus haute de la plage sélectionnée.

Autoriser le texte commençant par un caractère spécifique

Si toutes les valeurs d'une plage donnée doivent commencer par un caractère ou une sou-chaîne particulière, effectuez la validation des données Excel basée sur la fonction NB.SI avec un caractère générique :

NB.SI(cellule ; "texte *")

Par exemple, pour vous assurer que tous les ID d'ordre de la colonne A commencent par le préfixe "AA-", "aa-", "Aa-" ou "aA-" (insensible à la casse), définissez une règle personnalisée avec cette validation de données. Formule : =NB.SI(A10;"aa-*")

Autoriser les entrées contenant un certain texte

Pour autoriser les entrées contenant du texte spécifique n'importe où dans une cellule (au début, au milieu ou à la fin), utilisez la fonction ESTNUM en combinaison avec TROUVE ou CHERCHE selon que vous voulez une correspondance sensible à la casse ou insensible à la casse :

Validation sensible à la casse :

ESTNUM (CHERCHE (texte ; cellule))

Validation sensible à la casse :

ESTNUM (TROUVE (texte ; cellule))

Dans notre jeu de données exemple, pour autoriser uniquement les entrées contenant le texte "AA" dans les cellules A2: A10, utilisez l'une des formules suivantes:

Insensible à la casse : = ESTNUM(CHERCHE ("AA"; A2))

Sensible à la casse : = ESTNUM (TROUVE ("AA" ; A2))

Les formules fonctionnent avec la logique suivante :

Vous recherchez la sous-chaîne "AA" dans la cellule A2 à l'aide de TROUVE ou CHERCHE, et les deux renvoient une position du premier caractère de la sous-chaîne. Si le texte n'est pas trouvé, une erreur est renvoyée. Pour toute valeur numérique renvoyée à la suite d'une recherche, la fonction ESTNUM renvoie VRAI et la validation des données aboutit. En cas d'erreur, ESTNUM renvoie FAUX et l'entrée ne sera pas autorisée dans une cellule.

Validation des données pour autoriser uniquement les entrées uniques et interdire les doublons

Dans les cas où une colonne ou une plage de cellules donnée ne doit pas contenir de doublons, configurez une règle de validation de données personnalisée pour autoriser uniquement les entrées uniques. Pour cela, nous allons utiliser la formule classique NBVAL pour identifier les doublons :

= NB.SI (plage ; critère)

Par exemple, pour vous assurer que seuls les ID d'ordre uniques sont entrés dans les cellules A2 à A10, créez une règle personnalisée avec cette formule de validation de données :

=NB.SI($A$2 : $A$10;A2)

Lorsqu'une valeur unique est entrée, la formule retourne VRAI et la validation réussit. Si la même valeur existe déjà dans la plage spécifiée (nombre supérieur à 1), NB renvoie FALSE et la validation de l'entrée échoue.

Veillez à bien verrouiller la plage avec les références de cellule absolues (A$2 : $A$10) et à utiliser une référence relative pour la cellule supérieure (A2) pour que la formule soit correctement ajustée pour chaque cellule de la plage validée.

Formules de validation pour les dates et heures

La validation de date Excel fournit un grand nombre de critères prédéfinis pour empêcher les utilisateurs de ne saisir que des dates comprises entre les deux dates spécifiées, supérieures, inférieures ou égales à une date donnée.

Si vous souhaitez davantage de contrôle sur la validation des données dans vos feuilles de calcul, vous pouvez répliquer la fonctionnalité intégrée avec une règle personnalisée ou écrire votre propre formule qui va au-delà des fonctionnalités intégrées de la validation de données Excel.

Autoriser les dates entre deux dates

Pour limiter l'entrée à une date comprise dans une plage spécifiée, vous pouvez utiliser la règle Date prédéfinie avec le critère "comprise entre" ou bien créer une règle de validation personnalisée avec cette formule générique : ET (cellule > = date_début) ; cellule

« Cellule » est la cellule la plus haute dans la plage validée, et les dates de début et de fin sont des dates valides fournies via la fonction DATE ou des références à des cellules contenant les dates.

Par exemple, pour n'autoriser que les dates du mois de janvier de l'année 2019, utilisez la formule suivante :

=ET(C2>=DATE(2019;1;1);C2

Valider les dates en fonction de la date du jour

Dans de nombreuses situations, vous souhaiterez peut-être utiliser la date du jour comme date de début de la plage de dates autorisée. Pour obtenir la date actuelle, utilisez la fonction AUJOURDHUI, puis ajoutez-lui le nombre de jours souhaité pour calculer la date de fin.

Par exemple, pour limiter la saisie des données à 6 jours à compter de maintenant, nous allons utiliser la règle Date intégrée avec les critères basés sur une formule :

Sélectionnez la « date » dans la liste « autoriser »

Sélectionner « comprise entre » dans les « données »

Dans la zone « Date de début », entrez = AUJOURDHUI()

Dans la zone « Date de fin », entrez = AUJOURDHUI() + 6

De la même manière, vous pouvez limiter les utilisateurs à la saisie de dates avant ou après la date du jour. Pour cela, sélectionnez inférieur ou supérieur à dans la zone « Données », puis entrez = AUJOURDHUI () dans les zones « Date de fin » ou « Date de début », respectivement.

Formule de validation sensible à la casse

Si la casse des caractères est importante, utilisez EXACT en combinaison avec la fonction GAUCHE pour créer une formule de validation sensible à la casse pour les entrées commençant par un texte spécifique :

EXACT (GAUCHE(cell; Nombre de caractére); text)

Par exemple, pour n'autoriser que les identifiants d'ordre commençant par "AA-" (ni "aa-" ni "Aa-" n'est autorisé), utilisez cette formule :

=EXACT(GAUCHE(A2;3); "AA-")

Dans la formule ci-dessus, la fonction GAUCHE extrait les 3 premiers caractères de la cellule A2 et EXACT effectue une comparaison sensible à la casse avec la sous-chaîne codée en dur ("AA-" dans cet exemple). Si les deux sous-chaînes correspondent exactement, la formule retourne VRAI et la validation réussit. Sinon la valeur FAUX sera renvoyée et la validation échoue.