Validation des données Excel : ajouter, utiliser, copier et supprimer la validation des données dans Excel

Dans Excel, la fonction de validation des données est un outil puissant que vous pouvez utiliser pour limiter ce qu'un utilisateur peut entrer dans une cellule. Par exemple, vous pouvez définir des règles pour limiter la longueur du texte, restreindre les entrées à des formats spécifiques, imposer des valeurs uniques, ou vous assurer que le texte commence ou se termine par certains caractères. Ces fonctionnalités aident à maintenir l'intégrité des données et à réduire les erreurs dans vos feuilles de calcul.

Ce tutoriel explore comment ajouter, utiliser et supprimer la validation des données dans Excel. Il couvre à la fois les opérations fondamentales et avancées, offrant des instructions détaillées étape par étape pour vous aider à appliquer efficacement cette fonctionnalité à vos tâches.

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

La fonctionnalité "Validation des données" peut vous aider à restreindre le contenu saisi dans votre feuille de calcul. Normalement, vous pouvez créer des règles de validation pour empêcher ou n'autoriser qu'un certain type de données à être entré dans une liste de cellules sélectionnées.

Quelques utilisations de base de la fonctionnalité de validation des données :

1. « Toute valeur » : aucune validation n'est effectuée, vous pouvez saisir n'importe quoi dans les cellules spécifiées. 2. « Valeur entière » : seuls les nombres entiers sont autorisés. 3. "Décimal" : permet la saisie de nombres entiers ainsi que de nombres décimaux. 4. « Liste » : seules les valeurs de la liste prédéfinie peuvent être saisies ou sélectionnées. Les valeurs sont affichées dans une liste déroulante. 5. "Date" : seules les dates sont autorisées. 6. « Heure » : seules les heures sont autorisées. 7. "Longueur du texte" : n'autoriser que la saisie de textes d'une longueur spécifiée. 8. « Personnalisé » : créer des règles de formule personnalisées pour valider la saisie des utilisateurs.

 2. Comment ajouter une validation des données dans Excel ?

Dans une feuille de calcul Excel, vous pouvez ajouter une validation des données en suivant les étapes ci-dessous :

1. Sélectionnez une liste de cellules où vous souhaitez définir la validation des données, puis cliquez sur "Données" > "Validation des données" > "Validation des données", voir capture d'écran :

2. Dans la boîte de dialogue « Validation des données », sous l'onglet « Paramètres », veuillez créer vos propres règles de validation. Dans les champs de critères, vous pouvez fournir l'un des types suivants :

• "Valeurs" : Saisissez les nombres directement dans les champs de critères ;
• « Référence de cellule » : Faire référence à une cellule dans la feuille de calcul ou dans une autre feuille de calcul ;
• "Formules" : Créez des formules plus complexes en tant que conditions.

À titre d'exemple, je vais créer une règle qui n'autorise que les nombres entiers compris entre 100 et 1000 à être saisis. Voici les critères définis comme le montre la capture d'écran ci-dessous :

3. Après avoir configuré les conditions, vous pouvez aller dans l'onglet "Message de saisie" ou "Alerte d'erreur" pour définir le message de saisie ou l'alerte d'erreur pour les cellules de validation comme vous le souhaitez. (Si vous ne souhaitez pas configurer l'alerte, cliquez sur "OK" pour terminer directement.)

3.1) Ajouter un message de saisie (facultatif) :

Vous pouvez créer un message qui apparaît lors de la sélection d'une cellule contenant une validation des données. Ce message aide à rappeler à l'utilisateur ce qu'il peut saisir dans la cellule.

Allez à l'onglet "Message de saisie" et effectuez les actions suivantes :

Cochez l'option "Afficher un message lors de la sélection de la cellule" ; Saisissez le titre et le message de rappel souhaités dans les champs correspondants ; Cliquez sur "OK" pour fermer cette boîte de dialogue.

Maintenant, lorsque vous sélectionnez une cellule validée, une boîte de message s'affichera comme suit :

3.2) Créer des messages d'erreur significatifs (facultatif) :

En plus de créer le message de saisie, vous pouvez également afficher des alertes d'erreur lorsque des données invalides sont entrées dans une cellule avec validation des données.

Allez à l'onglet "Alerte d'erreur" de la boîte de dialogue "Validation des données", veuillez procéder comme suit :

Cochez l'option "Afficher une alerte d'erreur après la saisie de données invalides" ; Dans la liste déroulante "Style", sélectionnez le type d'alerte souhaité dont vous avez besoin : « Arrêter (par défaut) » : Ce type d'alerte empêche les utilisateurs de saisir des données invalides. "Avertissement" : Avertit les utilisateurs que les données sont invalides, mais ne les empêche pas de les saisir. "Information" : Informe les utilisateurs uniquement en cas de saisie de données non valide. Saisissez le titre et le message d'alerte souhaités dans les champs correspondants ; Cliquez sur "OK" pour fermer la boîte de dialogue.

Lorsqu'une valeur invalide est saisie, une boîte d'alerte apparaîtra, comme le montre la capture d'écran ci-dessous :

Option "Arrêter" : Cliquez sur "Réessayer" pour saisir une nouvelle valeur ou sur "Annuler" pour abandonner la saisie.

Option "Avertissement" : Cliquez sur "Oui" pour accepter la saisie invalide, "Non" pour la modifier, ou "Annuler" pour la supprimer.

Option "Information" : Cliquez sur "OK" pour accepter la saisie invalide ou sur "Annuler" pour la rejeter.

Remarque : Si vous ne définissez pas votre propre message personnalisé dans la boîte de dialogue "Alerte d'erreur", une alerte "Arrêt" par défaut sera affichée, comme indiqué ci-dessous :

 3. Exemples de base pour la validation des données

Lors de l'utilisation de cette fonction de validation des données, 8 options intégrées vous sont proposées pour configurer la validation des données. Par exemple : toute valeur, nombres entiers et décimaux, date et heure, liste, longueur du texte et formule personnalisée. Dans cette section, nous discuterons de la manière d'utiliser certaines de ces options intégrées dans Excel.

3.1 Validation des données pour les nombres entiers et décimaux

1. Sélectionnez une liste de cellules où vous souhaitez autoriser uniquement des nombres entiers ou décimaux, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue « Validation des données », sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez l'élément correspondant "Nombre entier" ou "Décimal" dans la liste déroulante "Autoriser".
• Ensuite, choisissez l'un des critères dans la zone "Données" (dans cet exemple, je choisis l'option "entre").
• Conseils : Les critères comprennent : entre, non entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, saisissez les valeurs « Minimum » et « Maximum » dont vous avez besoin (dans ce cas, des nombres compris entre 0 et 100).
• Enfin, cliquez sur le bouton "OK".

3. Maintenant, seuls les nombres entiers compris entre 0 et 100 peuvent être saisis dans les cellules sélectionnées.

3.2 Validation des données pour les dates et heures

Pour valider une date ou une heure spécifique à saisir, il est facile d'utiliser cette fonctionnalité de "Validation des données". Veuillez suivre les étapes ci-dessous :

1. Sélectionnez une liste de cellules où vous souhaitez autoriser uniquement des dates ou heures spécifiques, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue « Validation des données », sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez l'élément correspondant "Date" ou "Heure" dans la liste déroulante "Autoriser".
• Ensuite, choisissez l'un des critères dans la boîte "Données" (ici, je choisis l'option "supérieur à").
• Conseils : Les critères comprennent : entre, non entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, entrez la "Date de début" dont vous avez besoin (je souhaite que les dates soient supérieures au 20/8/2021).
• Enfin, cliquez sur le bouton "OK".

3. Maintenant, seules les dates supérieures au 20/08/2021 peuvent être saisies dans les cellules sélectionnées.

3.3 Validation des données pour la longueur du texte

Si vous avez besoin de limiter le nombre de caractères qui peuvent être saisis dans une cellule, par exemple, pour restreindre le contenu à un maximum de 10 caractères pour une plage spécifique, cette fonctionnalité de "Validation des données" peut également vous aider.

1. Sélectionnez une liste de cellules où vous souhaitez limiter la longueur du texte, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue « Validation des données », sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez "Longueur du texte" dans la liste déroulante "Autoriser".
• Ensuite, choisissez l'un des critères dans la boîte "Données" (dans cet exemple, je choisis l'option "inférieur à").
• Conseils : Les critères comprennent : entre, non entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, entrez le nombre "Maximum" que vous devez limiter (je souhaite que la longueur du texte ne dépasse pas 10 caractères).
• Enfin, cliquez sur le bouton "OK".

3. Désormais, les cellules sélectionnées n'autorisent que la saisie de chaînes de texte comportant moins de 10 caractères.

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

Avec cette fonctionnalité puissante de "Validation des données", vous pouvez également créer rapidement et facilement une liste déroulante dans les cellules. Veuillez procéder comme suit :

1. Sélectionnez les cellules cibles où insérer la liste déroulante, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue « Validation des données », sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez "Liste" dans la liste déroulante "Autoriser".
• Dans la zone de texte « Source », saisissez directement les éléments de la liste séparés par des virgules. Par exemple, pour limiter la saisie de l'utilisateur à trois choix, tapez Non démarré,En cours,Terminé, ou vous pouvez sélectionner une liste de cellules contenant les valeurs pour insérer la liste déroulante basée sur ces valeurs.
• Enfin, cliquez sur le bouton "OK".

3. Maintenant, la liste déroulante a été créée dans les cellules comme le montre la capture d'écran ci-dessous :

Cliquez pour en savoir plus sur les informations détaillées de la liste déroulante…

4. Règles personnalisées avancées pour la validation des données

Dans cette section, je vais vous présenter comment créer des règles de validation de données personnalisées avancées pour résoudre différents types de problèmes, tels que : créer des formules de validation permettant uniquement des nombres ou chaînes de texte, uniquement des valeurs uniques, uniquement des numéros de téléphone spécifiés, adresses e-mail, et ainsi de suite.

4.1 La validation des données permet uniquement les nombres ou les textes

Autoriser uniquement la saisie de nombres avec la fonction Validation des données

Pour n'autoriser que des nombres dans une plage de cellules, veuillez procéder comme suit :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que seuls des nombres soient saisis.

2. Cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui apparaît, sous l'onglet "Paramètres", veuillez effectuer les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule suivante dans la zone de texte "Formule". ("A2" est la première cellule de la plage sélectionnée que vous souhaitez limiter).

  =ISNUMBER(A2)

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

3. Désormais, seuls les nombres pourront être saisis dans les cellules sélectionnées.

Remarque : Cette fonction "ISNUMBER" permet toute valeur numérique dans les cellules validées, y compris les entiers, les décimales, les fractions, les dates et les heures.

Autoriser uniquement la saisie de chaînes de texte avec la fonction de validation des données

Pour limiter les entrées de cellules au texte uniquement, vous pouvez utiliser la fonctionnalité "Validation des données" avec une formule personnalisée basée sur la fonction "ISTEXT", veuillez procéder comme suit :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que seuls des chaînes de texte soient saisis.

2. Cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui apparaît, sous l'onglet "Paramètres", veuillez effectuer les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule suivante dans la zone de texte "Formule". ("A2" est la première cellule de la plage sélectionnée que vous souhaitez limiter)

  =ISTEXT(A2)

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

3. Maintenant, lors de la saisie de données dans des cellules spécifiques, seules les données au format texte sont autorisées.

4.2 Validation des données autorisant uniquement les valeurs alphanumériques

Pour certaines utilisations, vous pouvez souhaiter autoriser uniquement les caractères alphabétiques et les valeurs numériques tout en restreignant les caractères spéciaux tels que ~, %, $, ou les espaces. Cette section présente quelques méthodes utiles.

 Autoriser uniquement les valeurs alphanumériques avec la fonction Validation des données

Pour empêcher les caractères spéciaux et n'autoriser que les valeurs alphanumériques, créez une formule personnalisée dans la fonction "Validation des données" en suivant ces étapes :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que seuls des valeurs alphanumériques soient saisies.

2. Cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée que vous souhaitez limiter.

3. Désormais, seuls les caractères alphabétiques et les valeurs numériques sont autorisés à être saisis, et les caractères spéciaux seront restreints lors de la saisie, comme le montre la capture d'écran ci-dessous :

 Autoriser uniquement les valeurs alphanumériques grâce à une fonctionnalité étonnante

La formule ci-dessus peut sembler complexe à comprendre et à mémoriser. Ici, je vous présente une fonctionnalité pratique appelée "Limite de saisie" de "Kutools pour Excel", qui simplifie considérablement cette tâche.

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que seuls des valeurs alphanumériques soient saisies.

2. Ensuite, cliquez sur "Kutools" > "Limite de saisie" > "Limite de saisie", voir capture d'écran :

3. Dans la boîte de dialogue "Limite de saisie" qui s'affiche, sélectionnez l'option "Interdire la saisie de caractères spéciaux", voir capture d'écran :

4. Ensuite, cliquez sur le bouton "Ok", puis dans les boîtes de dialogue suivantes, cliquez sur "Oui" > "OK" pour terminer l'opération. Maintenant, dans les cellules sélectionnées, seuls les caractères alphabétiques et les valeurs numériques sont autorisés, voir capture d'écran :

4.3 La validation des données permet aux textes de commencer ou de se terminer par des caractères spécifiques

Si toutes les valeurs d'une certaine plage doivent commencer ou se terminer par un caractère ou une sous-chaîne spécifique, vous pouvez utiliser la validation des données avec une formule personnalisée basée sur les fonctions EXACT, GAUCHE, DROITE ou NB.SI.

 Autoriser les textes à commencer ou terminer par des caractères spécifiques avec une seule condition

Par exemple, si vous souhaitez que les entrées de texte dans des cellules spécifiques commencent ou se terminent par « CN », suivez ces étapes :

1. Sélectionnez une plage de cellules qui n'autorisent que les textes commençant ou se terminant par certains caractères.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".
  N'autoriser que la saisie de textes commençant par CN :

  =EXACT(LEFT(A2,2),"CN")

  N'autoriser que la saisie de texte se terminant par CN :

  =EXACT(RIGHT(A2,2),"CN")

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, le nombre "2" est le nombre de caractères que vous avez spécifié, et "CN" est le texte avec lequel vous souhaitez commencer ou terminer.

3. Désormais, seules les chaînes de texte commençant ou se terminant par les caractères spécifiés pourront être saisies dans les cellules sélectionnées. Sinon, une alerte d'avertissement apparaîtra, comme le montre la capture d'écran ci-dessous :

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

Remarque : L'astérisque * est un caractère générique qui correspond à un ou plusieurs caractères.

 Autoriser les textes commençant ou se terminant par des caractères spécifiques avec plusieurs critères (logique OU)

Par exemple, si vous souhaitez que les entrées de texte commencent ou se terminent par « CN » ou « UK » comme le montre la capture d’écran ci-dessous, vous devez ajouter une autre instance de EXACT en utilisant un signe plus (+). Veuillez suivre les étapes ci-dessous :

1. Sélectionnez une plage de cellules qui n'autorise que les textes commençant ou se terminant par plusieurs critères.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".
  N'autoriser que la saisie de texte commençant par CN ou UK :

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  Autoriser uniquement la saisie de texte se terminant par CN ou UK :

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, le nombre "2" est le nombre de caractères que vous avez spécifié, et "CN" et "UK" sont les textes spécifiques par lesquels vous souhaitez commencer ou terminer.

3. Désormais, seules les chaînes de texte commençant ou se terminant par les caractères spécifiés peuvent être saisies dans les cellules sélectionnées.

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Remarque : L'astérisque * est un caractère générique qui correspond à un ou plusieurs caractères.

4.4 La validation des données permet les entrées qui doivent contenir / ne doivent pas contenir un texte spécifique

Dans cette section, je vais expliquer comment appliquer la validation des données pour permettre des valeurs qui doivent contenir ou ne doivent pas contenir un sous-chaîne spécifique ou l'une des nombreuses sous-chaînes dans Excel.

 Les entrées doivent contenir un ou plusieurs textes spécifiques

Les entrées doivent contenir un texte spécifique

Pour permettre des entrées contenant une chaîne de texte spécifique, par exemple, toutes les valeurs saisies doivent contenir le texte "KTE" comme indiqué dans la capture d'écran ci-dessous, vous pouvez appliquer la validation des données avec une formule personnalisée basée sur les fonctions TROUVE et ESTNUM. Veuillez procéder comme suit :

1. Sélectionnez une plage de cellules qui n'autorise que les textes contenant un certain texte.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante Autoriser.
• Ensuite, entrez l'une des formules ci-dessous dans la zone de texte "Formule".
  Sensible à la casse :

  =ISNUMBER(FIND("KTE",A2)) 

  Non sensible à la casse :

  =ISNUMBER(SEARCH("KTE",A2))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, le texte "KTE" est la chaîne de caractères que les entrées doivent contenir.

3. Maintenant, lorsque la valeur saisie ne contient pas le texte requis, une boîte de dialogue d'avertissement s'affichera.

Autoriser les entrées doit contenir l'un des nombreux textes spécifiques

La formule ci-dessus ne fonctionne que pour une seule chaîne de texte. Si vous avez besoin d'autoriser l'une des plusieurs chaînes de texte dans les cellules, comme illustré dans la capture d'écran suivante, vous devez utiliser les fonctions SOMMEPROD, TROUVE et ESTNUM ensemble pour créer une formule.

1. Sélectionnez une plage de cellules qui n'autorise que les textes contenant l'un des nombreux éléments.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, entrez l'une des formules ci-dessous selon vos besoins dans la zone de texte "Formule".
  Sensible à la casse :

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  Non sensible à la casse :

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• Ensuite, cliquez sur "OK" pour fermer la boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, "C2:C4" est la liste des valeurs pour lesquelles vous souhaitez autoriser les entrées contenant l'une d'entre elles.

3. Et maintenant, seules les entrées contenant l'une des valeurs de la liste spécifique peuvent être saisies.

Autoriser les entrées ne doit pas contenir un ou plusieurs textes spécifiques

Autoriser les entrées ne doit pas contenir un texte spécifique

Pour valider que les entrées ne doivent pas contenir un texte spécifique, par exemple, pour permettre des valeurs qui ne doivent pas contenir le texte « KTE » dans une cellule, vous pouvez utiliser les fonctions ESTERREUR et TROUVE pour créer une règle de validation des données. Veuillez procéder comme suit :

1. Sélectionnez une plage de cellules qui n'autorise que les textes ne contenant pas un certain texte.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, entrez l'une des formules ci-dessous dans la zone de texte "Formule".
  Sensible à la casse :

  =ISERROR(FIND("KTE",A2))

  Non sensible à la casse :

  =ISERROR(SEARCH("KTE",A2))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, le texte "KTE" est la chaîne de caractères que les entrées ne doivent pas contenir.

3. Désormais, les entrées contenant le texte spécifique seront empêchées d'être saisies.

Autoriser les entrées ne doit pas contenir l'un des nombreux textes spécifiques

Pour empêcher la saisie de l'une des nombreuses chaînes de texte dans une liste, comme le montre la capture d'écran ci-dessous, suivez ces étapes :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez empêcher la saisie de certains textes.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".
  Sensible à la casse :

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  Non sensible à la casse :

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• Ensuite, cliquez sur "OK" pour fermer la boîte de dialogue.

Remarque : Dans les formules ci-dessus, "A2" est la première cellule de la plage sélectionnée, "C2:C4" est la liste des valeurs que vous souhaitez empêcher si les entrées en contiennent une quelconque.

3. Désormais, les entrées contenant l'un des textes spécifiques ne pourront plus être saisies.

4.5 La validation des données n'autorise que les valeurs uniques

Si vous souhaitez empêcher la saisie de données en double dans une plage de cellules, cette section présente quelques méthodes rapides pour résoudre cette tâche dans Excel.

Autoriser uniquement les valeurs uniques en utilisant la fonction de validation des données

Normalement, la fonction de validation des données avec une formule personnalisée basée sur la fonction COUNTIF peut vous aider. Veuillez suivre les étapes ci-dessous :

1. Sélectionnez les cellules ou la colonne dans lesquelles vous souhaitez qu'uniquement des valeurs uniques soient saisies.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

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

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, « A2:A9 » est la plage de cellules pour laquelle vous souhaitez n'autoriser que des valeurs uniques, et « A2 » est la première cellule de la plage sélectionnée.

3. Désormais, seules les valeurs uniques peuvent être saisies, et un message d'avertissement apparaîtra si des données en double sont entrées, comme le montre la capture d'écran ci-dessous :

 Autoriser uniquement les valeurs uniques en utilisant le code VBA

Le code VBA suivant peut également vous aider à empêcher la saisie de valeurs en double, veuillez procéder comme suit :

1. Faites un clic droit sur l'onglet de la feuille où vous souhaitez autoriser uniquement des valeurs uniques, puis choisissez "Afficher le code" dans le menu contextuel. Dans la fenêtre "Microsoft Visual Basic pour Applications" qui s'affiche, copiez et collez le code suivant dans le module vide :

Code VBA : N'autoriser que des valeurs uniques dans une plage de cellules :

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Remarque : Dans le code ci-dessus, « A1:A100 » et « A:A » représentent les cellules de la colonne dans lesquelles vous souhaitez empêcher les doublons. Veuillez les modifier selon vos besoins.

2. Ensuite, enregistrez et fermez ce code. Maintenant, lors de la saisie d'une valeur en double dans les cellules A1:A100, une boîte de dialogue d'avertissement apparaît, comme le montre la capture d'écran ci-dessous :

Autoriser uniquement les valeurs uniques en utilisant une fonctionnalité pratique

Si vous disposez de "Kutools pour Excel", avec sa fonctionnalité "Empêcher les doublons", vous pouvez rapidement configurer une validation des données pour éviter les doublons dans une plage de cellules en quelques clics seulement.

1. Sélectionnez la plage de cellules pour laquelle vous souhaitez empêcher les valeurs en double et n'autoriser que des données uniques.

2. Ensuite, cliquez sur "Kutools" > "Limite de saisie" > "Empêcher les doublons", voir capture d'écran :

3. Un message d'avertissement apparaîtra, vous informant que la validation des données sera supprimée lors de l'application de cette fonctionnalité. Cliquez sur "Oui", puis dans la boîte de dialogue suivante, cliquez sur "OK", comme le montrent les captures d'écran ci-dessous :

4. Maintenant, lorsque vous saisissez des données en double dans vos cellules spécifiées, une boîte de dialogue s'affiche pour vous rappeler que les données en double ne sont pas valides, voir capture d'écran :

4.6 La validation des données permet uniquement les majuscules / minuscules / majuscules en début de mot

La fonction de validation des données est un outil puissant qui peut aider les utilisateurs à imposer des entrées en majuscules, minuscules ou avec une casse correcte dans une plage de cellules. Veuillez suivre les étapes ci-dessous :

1. Sélectionnez la plage de cellules dans laquelle vous souhaitez qu'il ne soit possible de saisir que des majuscules, des minuscules ou des initiales en majuscule.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, entrez l'une des formules ci-dessous dont vous avez besoin dans la zone de texte "Formule".
  ne permettre que le texte en majuscules :

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

  N'autoriser que le texte en minuscules

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  N'autoriser que le texte en casse correcte

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, seules les entrées conformes à la règle que vous avez créée seront acceptées.

4.7 La validation des données permet des valeurs qui existent / n'existent pas dans une autre liste

Autoriser ou empêcher des valeurs en fonction de leur présence dans une autre liste peut être une tâche difficile pour de nombreux utilisateurs. En fait, vous pouvez utiliser la fonctionnalité de validation des données avec une formule simple basée sur la fonction COUNTIF pour y faire face.

Par exemple, je souhaite que seules les valeurs de la plage C2:C4 soient saisies dans une plage de cellules, comme le montre la capture d'écran ci-dessous. Pour résoudre cette tâche, veuillez procéder comme suit :

1. Sélectionnez la plage de cellules à laquelle vous souhaitez appliquer la validation des données.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez l'une des formules ci-dessous dont vous avez besoin dans la zone de texte "Formule".
  N'autoriser que les valeurs existant dans une autre colonne

  =COUNTIF($C$2:$C$4,A2)>0

  Empêcher les valeurs existantes dans une autre colonne

  =COUNTIF($C$2:$C$4,A2)=0

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser, "C2:C4" est la liste des valeurs que vous souhaitez interdire ou autoriser si les entrées en font partie.

3. Désormais, seules les entrées conformes à la règle que vous avez créée peuvent être saisies ; les autres seront bloquées.

4.8 La validation des données force la saisie uniquement au format de numéro de téléphone

Lorsque vous saisissez les informations des employés de votre entreprise, une colonne nécessite la saisie du numéro de téléphone. Pour garantir une saisie rapide et précise des numéros de téléphone, vous pouvez dans ce cas configurer une validation des données pour les numéros de téléphone. Par exemple, je souhaite que seuls les numéros au format (123) 456-7890 soient autorisés dans une feuille de calcul. Cette section présentera deux astuces rapides pour accomplir cette tâche.

 Forcer uniquement le format de numéro de téléphone avec la fonction Validation des données

Pour n'autoriser qu'un format de numéro de téléphone spécifique à être saisi, suivez ces étapes :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez qu'un format de numéro de téléphone spécifique soit saisi, puis faites un clic droit et choisissez "Format Cells" dans le menu contextuel, voir capture d'écran :

2. Dans la boîte de dialogue "Format de cellule", sous l'onglet "Nombre", sélectionnez "Personnalisé" dans la liste déroulante "Catégorie" à gauche, puis saisissez le format de numéro de téléphone dont vous avez besoin dans la zone de texte Type. Par exemple, je vais utiliser ce format "(###) ###-####", voir capture d'écran :

3. Ensuite, cliquez sur "OK" pour fermer la boîte de dialogue.

4. Après avoir formaté les cellules, sélectionnez-les à nouveau et ouvrez la boîte de dialogue "Validation des données" en cliquant sur "Données" > "Validation des données" > "Validation des données". Dans la fenêtre qui apparaît, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule suivante dans la zone de texte Formule.

  =AND(ISNUMBER(A2),LEN(A2)=10)

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne pour laquelle vous souhaitez valider le numéro de téléphone.

5. Maintenant, lors de la saisie d'un numéro à 10 chiffres, il sera automatiquement converti au format de numéro de téléphone spécifique dont vous avez besoin, comme le montrent les captures d'écran :

Remarque : Si le nombre saisi ne comporte pas 10 chiffres, une boîte de dialogue d'avertissement s'affichera pour vous le rappeler, voir capture d'écran :

 Forcer uniquement le format de numéro de téléphone en utilisant une fonctionnalité utile

La fonctionnalité "Validate Phone Number" de "Kutools for Excel" peut également vous aider à forcer la saisie d'un format de numéro de téléphone uniquement, en quelques clics.

1. Sélectionnez la liste des cellules qui n'autorisent qu'un numéro de téléphone spécifique, puis cliquez sur "Kutools" > "Limite de saisie" > "Seuls les numéros de téléphone peuvent être saisis", voir capture d'écran :

2. Dans la boîte de dialogue « Numéro de téléphone », sélectionnez le format de numéro de téléphone spécifique dont vous avez besoin, ou vous pouvez créer votre propre format en cliquant sur le bouton « Ajouter », voir capture d'écran :

3. Après avoir sélectionné ou défini le formatage du numéro de téléphone, cliquez sur "OK". Désormais, seuls les numéros de téléphone respectant le format spécifique pourront être saisis, sinon, un message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :

4.9 La validation des données force la saisie uniquement d'adresses e-mail

Supposons que vous deviez saisir plusieurs adresses e-mail dans une colonne d'une feuille de calcul, afin d'éviter la saisie de formats d'adresses e-mail incorrects. Normalement, vous pouvez définir une règle de validation des données pour n'autoriser que les formats d'adresses e-mail.

Forcer uniquement le format des adresses e-mail en utilisant la fonction de validation des données

En utilisant la fonction de validation des données avec une formule personnalisée, vous pouvez rapidement créer une règle pour empêcher la saisie d'adresses e-mail invalides. Suivez ces étapes :

1. Sélectionnez les cellules dans lesquelles vous souhaitez qu'aucun autre type de données que des adresses e-mail ne soit saisi, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule suivante dans la zone de texte Formule :

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, si le texte saisi ne correspond pas au format d'adresse e-mail, une boîte de message d'avertissement apparaîtra pour vous notifier, voir capture d'écran :

 Forcer uniquement le format des adresses e-mail grâce à une fonctionnalité pratique

"Kutools for Excel" propose une fonctionnalité impressionnante : "Validate Email Address". Grâce à cet outil, vous pouvez empêcher la saisie d'adresses e-mail invalides en un seul clic.

1. Sélectionnez les cellules dans lesquelles vous ne permettez que la saisie d'adresses e-mail, puis cliquez sur "Kutools" > "Limite de saisie" > "Seules les adresses e-mail peuvent être saisies". Voir la capture d'écran :

2. Ensuite, seul le format d'adresse e-mail est autorisé, sinon, une boîte de message d'avertissement apparaîtra pour vous rappeler, voir capture d'écran :

4.10 Validation des données forçant la saisie uniquement d'adresses IP

Dans cette section, je vais présenter quelques astuces rapides pour définir une validation des données afin d'accepter uniquement des adresses IP dans une plage de cellules.

Forcer uniquement le format des adresses IP avec la fonction de validation des données

Pour n'autoriser que la saisie d'adresses IP dans une plage de cellules spécifique, suivez ces étapes :

1. Sélectionnez les cellules dans lesquelles vous souhaitez qu'une adresse IP soit saisie, puis cliquez sur "Données" > "Validation des données" > "Validation des données".

2. Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, si une adresse IP invalide est saisie dans la cellule, une boîte de message d'avertissement apparaîtra, comme le montre la capture d'écran ci-dessous :

 Forcer uniquement le format des adresses IP avec du code VBA

Ici, le code VBA suivant peut également aider à n'autoriser que la saisie d'adresses IP et à restreindre toute autre entrée. Veuillez procéder comme suit :

1. Faites un clic droit sur l'onglet de la feuille et cliquez sur "Afficher le code" dans le menu contextuel. Dans la fenêtre "Microsoft Visual Basic pour Applications" qui s'ouvre, copiez le code VBA ci-dessous dedans.

Code VBA : valider les cellules pour n'accepter que les adresses IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Remarque : Dans le code ci-dessus, "A2:A10" est la plage de cellules où vous souhaitez n'accepter que des adresses IP.

2. Ensuite, enregistrez et fermez ce code. Désormais, seules les adresses IP valides peuvent être saisies dans les cellules spécifiées.

Forcer uniquement le format des adresses IP en utilisant une fonctionnalité simple

Si vous avez « Kutools for Excel » installé dans votre classeur, sa fonction « Validate IP Address » peut également vous aider à résoudre cette tâche.

1. Sélectionnez les cellules dans lesquelles vous ne permettez que la saisie d'adresses IP, puis cliquez sur "Kutools" > "Limite de saisie" > "Seules les adresses IP peuvent être saisies". Voir capture d'écran :

2. Après avoir appliqué cette fonctionnalité, seul une adresse IP est autorisée à être saisie, sinon, une boîte de dialogue d'avertissement s'affichera pour vous le rappeler, voir capture d'écran :

4.11 Validation des données restreignant les valeurs qui dépassent la valeur totale

Supposons que vous ayez un rapport mensuel de dépenses et que le budget total soit de 18 000 $. Vous devez vous assurer que le montant total dans la liste des dépenses ne dépasse pas ce total prédéfini, comme illustré dans la capture d'écran ci-dessous. Dans ce cas, vous pouvez créer une règle de validation des données en utilisant la fonction SOMME pour empêcher que la somme des valeurs ne dépasse le total prédéfini.

1. Sélectionnez la liste des cellules où vous souhaitez limiter les valeurs.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =SUM($B$2:$B$7)<=18000

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, « B2:B7 » est la plage de cellules dans laquelle vous souhaitez limiter les entrées.

3. Maintenant, lors de la saisie des valeurs dans la plage B2:B7, si le total des valeurs est inférieur à 18 000 $, la validation est réussie. Si une valeur quelconque fait dépasser le total de 18 000 $, une boîte de dialogue d'avertissement apparaîtra pour vous notifier.

4.12 Validation des données restreignant la saisie de cellule en fonction d'une autre cellule

Lorsque vous souhaitez limiter les entrées de données dans une liste de cellules en fonction de la valeur d'une autre cellule, la fonction de validation des données peut également vous aider à résoudre cette tâche. Par exemple, si la cellule C1 contient le texte « Oui », la plage A2:A9 permet toutes les entrées. Cependant, si la cellule C1 contient tout autre texte, les entrées dans la plage A2:A9 sont restreintes, comme le montrent les captures d'écran ci-dessous :

Pour résoudre ce problème, veuillez procéder comme suit :

1. Sélectionnez la liste des cellules où vous souhaitez limiter les valeurs.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =$C$1="Yes"

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "C1" est la cellule contenant le texte spécifique que vous souhaitez utiliser, et le texte « Oui » est le texte sur lequel vous souhaitez limiter les cellules, veuillez les modifier selon vos besoins.

3. Maintenant, si la cellule C1 contient le texte « Oui », n'importe quoi peut être saisi dans la plage A2:A9 ; si la cellule C1 contient un autre texte, vous ne pourrez pas entrer de valeur, comme illustré dans la démonstration ci-dessous :

4.13 La validation des données ne permet que la saisie des jours de semaine ou des week-ends

Si vous avez besoin que seuls les jours ouvrables (du lundi au vendredi) ou les week-ends (samedi et dimanche) soient saisis dans une liste de cellules, la "Validation des données" peut également vous aider. Veuillez suivre les étapes ci-dessous :

1. Sélectionnez la plage de cellules où vous souhaitez entrer les jours de la semaine.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez l'une des formules ci-dessous dans la zone de texte "Formule" selon vos besoins.
  Autoriser uniquement les jours de la semaine

  =WEEKDAY(A2,2)<6

  Autoriser uniquement les week-ends

  =WEEKDAY(A2,2)>5

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, vous ne pouvez entrer que des dates de semaine ou de week-end dans les cellules spécifiées en fonction de votre sélection.

4.14 Validation des données permettant la saisie de dates basées sur la date du jour

Parfois, vous pourriez avoir besoin de n'autoriser que les dates supérieures ou inférieures à aujourd'hui à être saisies dans une liste de cellules. La fonctionnalité "Validation des données" avec la fonction "AUJOURD'HUI" peut vous y aider. Veuillez procéder comme suit :

1. Sélectionnez la plage de cellules où vous souhaitez qu'une date future (date supérieure à aujourd'hui) soit saisie.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =A2>Today()

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, seules les dates postérieures à aujourd'hui peuvent être saisies dans les cellules. Sinon, une boîte de message d'avertissement apparaîtra pour vous notifier, voir capture d'écran :

=A2<Today()

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 La validation des données permet de saisir l'heure en fonction de l'heure actuelle

Si vous souhaitez valider des données en fonction de l'heure actuelle, par exemple, seules les heures avant ou après l'heure actuelle peuvent être saisies dans les cellules. Vous pouvez créer votre propre formule de validation des données, veuillez procéder comme suit :

1. Sélectionnez la plage de cellules où vous souhaitez que seuls les horaires avant ou après l'heure actuelle soient saisis.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Heure » dans la liste déroulante « Autoriser ».
• Ensuite, choisissez « inférieur à » pour n'autoriser que les heures avant l'heure actuelle, ou « supérieur à » pour autoriser les heures après l'heure actuelle selon vos besoins dans le menu déroulant « Données ».
• Ensuite, dans la zone "Heure de fin" ou "Heure de début", saisissez la formule ci-dessous :

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, "A2" est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, seuls les horaires avant ou après l'heure actuelle peuvent être saisis dans des cellules spécifiques.

4.16 Validation des données pour la date d'une année spécifique ou en cours

Pour n'autoriser que les dates d'une année spécifique ou de l'année en cours à être saisies, vous pouvez utiliser une validation des données avec une formule personnalisée basée sur la fonction ANNÉE.

1. Sélectionnez la plage de cellules où vous souhaitez qu'uniquement les dates d'une année spécifique soient saisies.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Personnalisé" dans la liste déroulante "Autoriser".
• Ensuite, saisissez la formule ci-dessous dans la zone de texte "Formule".

  =YEAR(A2)=2020

• Cliquez sur le bouton "OK" pour fermer cette boîte de dialogue.

Remarque : Dans la formule ci-dessus, « A2 » est la première cellule de la colonne que vous souhaitez utiliser, « 2020 » est le numéro de l'année que vous souhaitez restreindre.

3. Ensuite, seules les dates de l'année 2020 peuvent être saisies, sinon, une boîte de message d'avertissement apparaîtra comme le montre la capture d'écran ci-dessous :

=YEAR(A2)=YEAR(TODAY())

4.17 Validation des données pour la date de la semaine ou du mois en cours

Si vous souhaitez permettre aux utilisateurs de saisir des dates pour la semaine ou le mois en cours dans des cellules spécifiques, cette section présente quelques formules pour accomplir cette tâche dans Excel.

Autoriser la saisie de la date de la semaine en cours

1. Sélectionnez la plage de cellules où vous souhaitez que seules les dates de la semaine en cours soient saisies.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez "Date" dans la liste déroulante "Autoriser".
• Ensuite, choisissez « entre » dans le menu déroulant « Données ».
• Dans la zone de texte « Date de début », entrez cette formule :

  =TODAY()-WEEKDAY(TODAY(),3)

• Dans la zone de texte « Date de fin », saisissez cette formule :

  =TODAY()-WEEKDAY(TODAY(),3)+6

• Enfin, cliquez sur le bouton "OK".

3. Ensuite, seules les dates de la semaine en cours pourront être saisies, les autres dates seront bloquées comme le montre la capture d'écran ci-dessous :

 Autoriser la saisie de la date du mois en cours

Pour n'autoriser que les dates du mois en cours à être saisies, veuillez procéder comme suit :

1. Sélectionnez la plage de cellules où vous souhaitez que seules les dates du mois en cours puissent être saisies.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données" qui s'affiche, sous l'onglet "Paramètres", effectuez les opérations suivantes :

• Sélectionnez « Date » dans la liste déroulante « Autoriser ».
• Ensuite, choisissez entre les options du menu déroulant "Données".
• Dans la zone de texte « Date de début », saisissez cette formule :

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• Dans la zone de texte « Date de fin », saisissez cette formule :

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• Enfin, cliquez sur le bouton "OK".

3. Désormais, seules les dates du mois en cours peuvent être saisies dans les cellules sélectionnées.

5. Comment modifier la validation des données dans Excel ?

Pour modifier ou changer une règle de validation des données existante, suivez les étapes ci-dessous :

1. Sélectionnez l'une des cellules contenant la règle de validation des données.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données" pour accéder à la boîte de dialogue "Validation des données". Dans cette boîte, modifiez ou changez les règles selon vos besoins, puis cochez l'option "Appliquer ces modifications à toutes les autres cellules ayant les mêmes paramètres" afin d'appliquer cette nouvelle règle à toutes les autres cellules qui partagent les critères de validation d'origine. Voir capture d'écran :

3. Cliquez sur "OK" pour enregistrer les modifications.

6. Comment trouver et sélectionner des cellules avec validation des données dans Excel ?

Si vous avez créé plusieurs règles de validation des données dans votre feuille de calcul et que vous devez maintenant trouver et sélectionner les cellules auxquelles ces règles ont été appliquées, la commande "Atteindre spécial" peut vous aider à sélectionner tous les types de validation des données ou un type spécifique de validation des données.

1. Activez la feuille de calcul dans laquelle vous souhaitez rechercher et sélectionnez les cellules avec validation des données.

2. Ensuite, cliquez sur "Accueil" > "Rechercher et sélectionner" > "Atteindre une sélection spéciale", voir capture d'écran :

3. Dans la boîte de dialogue "Atteindre spécial", sélectionnez "Validation des données" > "Toutes", voir capture d'écran :

4. Toutes les cellules avec validation des données ont maintenant été sélectionnées dans la feuille de calcul actuelle.

Astuce : Si vous souhaitez sélectionner un type spécifique de validation des données, commencez par sélectionner une cellule contenant la validation des données souhaitée, puis accédez à la boîte de dialogue "Atteindre spécial", et choisissez "Validation des données" > "Identique".

7. Comment copier la règle de validation des données vers d'autres cellules ?

Supposons que vous ayez créé une règle de validation des données pour une liste de cellules et que maintenant, vous deviez appliquer la même règle de validation des données à d'autres cellules. Au lieu de recréer la règle, vous pouvez copier et coller la règle existante vers d'autres cellules rapidement et facilement.

1. Cliquez pour sélectionner une cellule contenant la règle de validation que vous souhaitez utiliser, puis appuyez sur "Ctrl + C" pour la copier.

2. Ensuite, sélectionnez les cellules que vous souhaitez valider. Pour sélectionner plusieurs cellules non adjacentes, appuyez et maintenez la touche "Ctrl" tout en sélectionnant les cellules.

3. Ensuite, faites un clic droit sur la sélection, puis choisissez l'option "Collage spécial", voir capture d'écran :

4. Dans la boîte de dialogue "Collage spécial", sélectionnez l'option "Validation", voir capture d'écran :

5. Cliquez sur le bouton "OK", la règle de validation est maintenant copiée vers les nouvelles cellules.

8. Comment utiliser la validation des données pour encercler les entrées invalides dans Excel ?

Parfois, vous pouvez avoir besoin de créer des règles de validation des données pour des données existantes ; dans ce cas, certaines données invalides peuvent apparaître dans la plage de cellules. Comment vérifier les données invalides et les modifier ? Dans Excel, vous pouvez utiliser la fonctionnalité "Encercler les données invalides" pour mettre en évidence les données invalides avec un cercle rouge.

Pour encercler les données invalides dont vous avez besoin, vous devez appliquer la fonctionnalité "Validation des données" afin de définir une règle pour la plage de données. Veuillez suivre les étapes ci-dessous :

1. Sélectionnez la plage de données dans laquelle vous souhaitez encercler les données invalides.

2. Ensuite, cliquez sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue "Validation des données", définissez la règle de validation selon vos besoins. Par exemple, ici, je vais valider les valeurs supérieures à 500, voir capture d'écran :

3. Ensuite, cliquez sur "OK" pour fermer la boîte de dialogue. Après avoir défini la règle de validation des données, cliquez sur "Données" > "Validation des données" > "Encercler les données invalides", puis toutes les valeurs invalides inférieures à 500 ont été encerclées avec un ovale rouge. Voir les captures d'écran :

Remarques :

• 1. Dès que vous corrigez une donnée invalide, le cercle rouge disparaîtra automatiquement.
• 2. Cette fonctionnalité "Encercler les données invalides" ne peut encercler que 255 cellules au maximum. Lorsque vous enregistrez le classeur actuel, tous les cercles rouges seront supprimés.
• 3. Ces cercles ne sont pas imprimables.
• 4. Vous pouvez également supprimer les cercles rouges en cliquant sur "Données" > "Validation des données" > "Effacer les cercles de validation".

9. Comment supprimer la validation des données dans Excel ?

Pour supprimer les règles de validation des données d'une plage de cellules, de la feuille de calcul actuelle ou de l'ensemble du classeur, utilisez les méthodes suivantes.

Supprimer la validation des données dans la plage sélectionnée avec la fonction de validation des données

1. Sélectionnez les cellules avec validation de données que vous souhaitez supprimer.

2. Cliquez ensuite sur "Données" > "Validation des données" > "Validation des données". Dans la boîte de dialogue qui s'affiche, sous l'onglet "Paramètres", cliquez sur le bouton "Tout effacer", voir capture d'écran :

3. Ensuite, cliquez sur le bouton "OK" pour fermer cette boîte de dialogue. La règle de validation des données appliquée à la plage sélectionnée a été supprimée immédiatement.

Astuce : Pour supprimer la validation des données de la feuille de calcul actuelle, veuillez d'abord sélectionner toute la feuille, puis appliquer les étapes ci-dessus.

 Supprimer la validation des données dans la plage sélectionnée avec une fonctionnalité pratique

Si vous disposez de "Kutools for Excel", sa fonction "Supprimer les restrictions de validation des données" peut également vous aider à supprimer les règles de validation des données d’une plage sélectionnée ou de toute la feuille de calcul.

1. Sélectionnez la plage de cellules ou la feuille de calcul entière contenant la validation des données que vous souhaitez supprimer.

2. Ensuite, cliquez sur "Kutools" > "Limite de saisie" > "Supprimer les restrictions de validation des données", voir capture d'écran :

3. Dans la boîte de dialogue qui apparaît, cliquez sur "OK", et la règle de validation des données sera effacée comme vous le souhaitez.

 Supprimer la validation des données de toutes les feuilles de calcul avec du code VBA

Pour supprimer les règles de validation des données de l'ensemble du classeur, les méthodes ci-dessus peuvent être chronophages s'il y a beaucoup de feuilles de calcul. Ici, le code ci-dessous peut vous aider à traiter cette tâche rapidement.

1. Maintenez enfoncées les touches "ALT + F11" pour ouvrir la fenêtre "Microsoft Visual Basic for Applications".

2. Ensuite, cliquez sur "Insérer" > "Module", et collez la macro suivante dans la fenêtre "Module".

Code VBA : Supprimer les règles de validation des données dans toutes les feuilles de calcul :

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Ensuite, appuyez sur la touche "F5" pour exécuter ce code, et toutes les règles de validation des données seront immédiatement supprimées de l'ensemble du classeur.