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

Dans Excel, la fonctionnalité Validation des données est un outil puissant que vous pouvez utiliser pour limiter ce qu'un utilisateur peut saisir 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 garantir que le texte commence ou se termine par certains caractères. Ces fonctionnalités permettent de maintenir l'intégrité des données et de réduire les erreurs dans vos feuilles de calcul.

Ce didacticiel explique comment ajouter, utiliser et supprimer la validation des données dans Excel. Il couvre les opérations fondamentales et avancées, en fournissant 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 » vous permet de restreindre le contenu de votre feuille de calcul. Vous pouvez généralement créer des règles de validation pour interdire ou autoriser la saisie de certains types de données 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 de saisir des nombres entiers ainsi que des décimales. 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. « Temps » : seuls les temps sont autorisés. 7. « Longueur du texte » : autorise uniquement la saisie d'une longueur de texte spécifiée. 8. « Personnalisé » : créez des règles de formule personnalisées pour valider la saisie des utilisateurs.

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

Dans la feuille de calcul Excel, vous pouvez ajouter la validation des données en procédant comme suit :

1. Sélectionnez une liste de cellules dans lesquelles 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 », créez vos propres règles de validation. Dans les cases de critères, vous pouvez fournir l'un des types suivants :

• « Valeurs » : saisissez directement des nombres dans les cases de critères ;
• « Référence de cellule » : référencer une cellule dans la feuille de calcul ou une autre feuille de calcul ;
• « Formules » : créez des formules plus complexes comme conditions.

À titre d'exemple, je vais créer une règle qui autorise uniquement la saisie de nombres entiers entre 100 et 1000, définissez ici les critères comme illustré ci-dessous :

3. Après avoir configuré les conditions, accédez à l'onglet « Message de saisie » ou « Alerte d'erreur » pour définir le message de saisie ou l'alerte d'erreur souhaité pour les cellules de validation. (Si vous ne souhaitez pas définir d'alerte, cliquez sur « OK » pour terminer directement.)

3.1) Ajouter un message d'entrée (facultatif) :

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

Accédez à l'onglet « Message d'entrée » et procédez comme suit :

Cochez l'option « Afficher le message de saisie lorsque la cellule est sélectionnée » ; Entrez le titre et le message de rappel que vous souhaitez dans les champs correspondants ; Cliquez sur « OK » pour fermer cette boîte de dialogue.

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

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

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

Accédez à l'onglet « Alerte d'erreur » de la boîte de dialogue « Validation des données », procédez comme suit :

Cochez l'option « Afficher une alerte d'erreur après la saisie de données non valides » ; Dans la liste déroulante « Style », sélectionnez le type d’alerte dont vous avez besoin : « Stop (par défaut) » : ce type d’alerte empêche les utilisateurs de saisir des données non valides. « Avertissement » : avertit les utilisateurs que les données ne sont pas valides, mais n'empêche pas de les saisir. « Information » : informe les utilisateurs uniquement d'une 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 non valide est saisie, une boîte d'alerte apparaît, comme indiqué dans la capture d'écran ci-dessous :

Option « Arrêter » : cliquez sur « Réessayer » pour saisir à nouveau une valeur ou sur « Annuler » pour annuler l'entrée.

Option « Avertissement » : cliquez sur « Oui » pour accepter l'entrée non valide, sur « Non » pour la modifier ou sur « Annuler » pour la supprimer.

Option « Informations » : cliquez sur « OK » pour accepter l’entrée non valide ou sur « Annuler » pour la supprimer.

Remarque : si vous ne définissez pas votre propre message personnalisé dans la case « Alerte d'erreur », une case d'alerte « Arrêter » par défaut s'affichera, comme indiqué ci-dessous :

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

Lorsque vous utilisez cette fonctionnalité de validation des données, 8 options intégrées vous permettent de définir la validation des données. Tels que : toute valeur, nombres entiers et décimaux, date et heure, liste, longueur de texte et formule personnalisée. Dans cette section, nous verrons comment utiliser certaines des options intégrées dans Excel ?

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

1. Sélectionnez une liste de cellules dans lesquelles vous souhaitez autoriser uniquement les nombres entiers ou les décimales, 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 ».
• Et puis, choisissez l'un des critères dans la case « Données » (dans cet exemple, je choisis l'option « entre »).
• Astuces : Les critères contiennent : entre, pas entre, égal à, non égal à, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, entrez 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. Désormais, seuls des nombres entiers compris entre 0 et 100 peuvent être saisis dans les cellules sélectionnées.

3.2 Validation des données pour la date et l'heure

Pour valider une date ou une heure spécifique à saisir, c'est facile en utilisant cette « Validation des données », veuillez procéder comme suit :

1. Sélectionnez une liste de cellules dans lesquelles vous souhaitez autoriser uniquement les 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 ».
• Et puis, choisissez un des critères dans la case « Données » (Ici je choisis l'option « supérieur à »).
• Astuces : Les critères contiennent : entre, pas entre, égal à, non égal à, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, entrez la « Date de début » dont vous avez besoin (je veux les dates supérieures à 8/20/2021).
• Enfin, cliquez sur le bouton « OK ».

3. Désormais, seules les dates supérieures à 8/20/2021 peuvent être saisis dans les cellules sélectionnées.

3.3 Validation des données pour la longueur du texte

Si vous devez limiter le nombre de caractères pouvant être saisis dans une cellule, par exemple pour limiter le contenu à 10 caractères maximum pour une plage donnée, cette « Validation des données » peut également vous être utile.

1. Sélectionnez une liste de cellules dans lesquelles 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 ».
• Et puis, choisissez l'un des critères dans la case « Données » (dans cet exemple, je choisis l'option « inférieur à »).
• Astuces : Les critères contiennent : entre, pas entre, égal à, non égal à, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
• Ensuite, entrez le nombre « Maximum » que vous devez limiter (je veux 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 ne permettent de saisir que des chaînes de texte de moins de 10 caractères

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

Grâce à cette puissante fonctionnalité de « Validation des données », vous pouvez créer rapidement et facilement des listes déroulantes dans les cellules. Procédez comme suit :

1. Sélectionnez les cellules cibles dans lesquelles 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 les éléments de la liste directement, séparés par des virgules. Par exemple, pour limiter la saisie utilisateur à trois choix, saisissez « Non commencé », « En cours », « Terminé ». Vous pouvez également sélectionner une liste de cellules contenant les valeurs à utiliser pour insérer la liste déroulante.
• Enfin, cliquez sur le bouton « OK ».

3. Maintenant, la liste déroulante a été créée dans les cellules comme illustré ci-dessous :

Cliquez pour connaître les informations plus 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 vos types de problèmes, tels que : créer des formules de validation pour autoriser uniquement des nombres ou des chaînes de texte, uniquement des valeurs uniques, uniquement des numéros de téléphone spécifiés, des adresses e-mail, etc. .

4.1 La validation des données autorise uniquement les chiffres ou les textes

Autoriser uniquement la saisie de chiffres avec la fonction de validation des données

Pour autoriser uniquement les nombres dans une plage de cellules, procédez 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 ». 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 ».
• Saisissez ensuite la formule suivante dans la zone de texte « Formule ». (« A2 » est la première cellule de la plage sélectionnée à limiter.)

  =ISNUMBER(A2)

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

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

Remarque : cette fonction « ISNUMBER » autorise toutes les valeurs numériques 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 cellule au texte uniquement, vous pouvez utiliser la fonction « Validation des données » avec une formule personnalisée basée sur la fonction « ISTEXT », procédez comme suit :

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

2. Cliquez sur « 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 ».
• Saisissez ensuite la formule suivante dans la zone de texte « Formule ». (« A2 » est la première cellule de la plage sélectionnée à limiter.)

  =ISTEXT(A2)

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

3. Désormais, lors de la saisie de données dans les cellules spécifiques, seules les données au format texte peuvent être autorisées.

4.2 La validation des données n'autorise que les valeurs alphanumériques

Pour certains usages, vous souhaiterez peut-être autoriser uniquement les lettres et les valeurs numériques tout en limitant 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 de validation des données

Pour éviter les caractères spéciaux et autoriser uniquement 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 seules 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 apparaît, sous l'onglet « Paramètres », veuillez effectuer les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 alphabets et les valeurs numériques peuvent être saisis, et les caractères spéciaux seront limités lors de la saisie, comme illustré ci-dessous :

 Autorisez uniquement les valeurs alphanumériques en utilisant une fonctionnalité étonnante

La formule ci-dessus peut paraître complexe à comprendre et à mémoriser. Je vous présente ici une fonctionnalité pratique appelée « Empêcher la saisie » de « Kutools for Excel », qui simplifie considérablement cette tâche.

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

2. Cliquez ensuite sur « Kutools » > « Empêcher la saisie » > « Empêcher la saisie », voir capture d'écran :

3. Dans la boîte de dialogue « Empêcher la saisie » qui apparaît, sélectionnez l'option « Empêcher la saisie de caractères spéciaux », voir capture d'écran :

4. Cliquez ensuite sur le bouton « OK », puis, dans les boîtes de dialogue suivantes, cliquez sur « Oui » > « OK » pour terminer l'opération. Dans les cellules sélectionnées, seules les lettres et les valeurs numériques sont autorisées (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 à se 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 », procédez comme suit :

1. Sélectionnez une plage de cellules qui autorise uniquement les textes commençant ou se terminant par certains caractères.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

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

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

  Autoriser uniquement 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é, « CN » est le texte par lequel vous souhaitez commencer ou terminer.

3. Désormais, seule la chaîne de texte commençant ou se terminant par les caractères spécifiés peut être saisie dans les cellules sélectionnées. Dans le cas contraire, un message d'avertissement s'affichera, comme illustré dans 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 à commencer ou à se terminer 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 illustré ci-dessous, vous devez ajouter une autre instance de EXACT en utilisant un signe plus (+). Veuillez suivre les étapes suivantes :

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

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez la formule ci-dessous dans la zone de texte « Formule ».
  Autoriser uniquement 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é, « CN » et « UK » sont les textes spécifiques par lesquels vous souhaitez commencer ou terminer.

3. Désormais, seule la chaîne de texte commençant ou se terminant par les caractères spécifiés peut être saisie 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 Les entrées autorisées pour la validation des données doivent contenir / ne doivent pas contenir de texte spécifique

Cette section, je vais parler de la façon d'appliquer la validation des données pour autoriser les valeurs doivent contenir ou ne doivent pas contenir une sous-chaîne spécifique ou l'une des nombreuses sous-chaînes dans Excel.

 Les entrées autorisées doivent contenir un ou un des nombreux textes spécifiques

Les entrées autorisées doivent contenir un texte spécifique

Pour autoriser les entrées qui contiennent une chaîne de texte spécifique, par exemple, toutes les valeurs saisies doivent contenir le texte "KTE" comme illustré ci-dessous, vous pouvez appliquer la validation des données avec une formule personnalisée basée sur les fonctions FIND et ISNUMBER. Merci de faire comme ceci :

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

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 apparaît, 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 aux majuscules et minuscules:

  =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 texte que les entrées doivent contenir.

3. Désormais, lorsque la valeur saisie ne contient pas le texte requis, une boîte de dialogue d'avertissement apparaît.

Les entrées autorisées doivent 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 que l'une des nombreuses chaînes de texte soit autorisée dans les cellules, comme indiqué dans la capture d'écran suivante, vous devez utiliser les fonctions SOMMEPROD, TROUVER et ESTNUMBRE ensemble pour créer une formule.

1. Sélectionnez une plage de cellules qui autorise uniquement les textes contenant l’un des nombreux éléments.

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 apparaît, 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 aux majuscules et minuscules:

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

  Non sensible à la casse :

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

• Et puis, 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 autoriser à contenir dans les entrées.

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

Les entrées autorisées ne doivent pas contenir un ou un des nombreux textes spécifiques

Les entrées autorisées ne doivent pas contenir un texte spécifique

Pour valider les entrées ne doivent pas contenir de texte spécifique, par exemple, pour autoriser des valeurs qui ne doivent pas contenir le texte « KTE » dans une cellule, vous pouvez utiliser les fonctions ESTERREUR et FIND pour créer une règle de validation des données. Merci de faire comme ceci :

1. Sélectionnez une plage de cellules qui autorise uniquement les textes qui ne contiennent pas certains textes.

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 apparaît, 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 aux majuscules et minuscules:

  =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 texte que les entrées ne doivent pas contenir.

3. Désormais, les entrées contenant le texte spécifique ne pourront pas être saisies.

Les entrées autorisées ne doivent pas contenir l'un des nombreux textes spécifiques

Pour empêcher la saisie d'une des nombreuses chaînes de texte d'une liste, comme indiqué dans la capture d'écran ci-dessous, procédez comme suit :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que certains textes soient bloqués.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez la formule ci-dessous dans la zone de texte « Formule ».
  Sensible aux majuscules et minuscules:

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

  Non sensible à la casse :

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

• Et puis, 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 contiennent l'une d'entre elles.

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 des 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 fonctionnalité de validation des données avec une formule personnalisée basée sur la fonction COUNTIF peut vous aider, procédez comme suit :

1. Sélectionnez les cellules ou la colonne dans lesquelles vous souhaitez uniquement saisir des valeurs uniques.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 autoriser uniquement les valeurs uniques, et « A2 » est la première cellule de la plage sélectionnée.

3. Désormais, seules des valeurs uniques peuvent être saisies et un message d'avertissement apparaîtra si des données en double sont saisies, comme indiqué dans 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, procédez comme suit :

1. Cliquez avec le bouton droit sur l'onglet de la feuille pour lequel vous souhaitez autoriser uniquement les valeurs uniques, puis choisissez « Afficher le code » dans le menu contextuel. Dans la fenêtre contextuelle « Microsoft Visual Basic pour Applications », copiez et collez le code suivant dans le module vide :

Code VBA : autoriser uniquement les 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 » sont les cellules de la colonne dont vous souhaitez éviter la duplication. Veuillez les modifier selon vos besoins.

2. Enregistrez et fermez ce code. Désormais, lorsque vous saisissez une valeur en double dans les cellules A1:A100, un message d'avertissement s'affiche, comme illustré dans la capture d'écran ci-dessous :

Autoriser uniquement des valeurs uniques en utilisant une fonctionnalité pratique

Si vous disposez de « Kutools for Excel », avec sa fonction « Empêcher les doublons », vous pouvez rapidement définir la validation des données pour éviter les doublons dans une plage de cellules en quelques clics.

1. Sélectionnez la plage de cellules pour laquelle vous souhaitez empêcher les valeurs en double mais autoriser uniquement les données uniques.

2. Cliquez ensuite sur « Kutools » > « Empêcher la saisie » > « Empêcher les doublons », voir capture d'écran :

3. Un message d'avertissement s'affichera, vous informant que la validation des données sera supprimée lors de l'application de cette fonctionnalité. Cliquez sur « Oui », puis sur « OK » dans la boîte de dialogue qui s'affiche, comme illustré dans les captures d'écran ci-dessous :

4. Maintenant, lorsque vous entrez 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 autorise uniquement les majuscules / minuscules / la casse appropriée

La fonctionnalité de validation des données est un outil puissant qui peut aider les utilisateurs à appliquer des majuscules, des minuscules ou une casse appropriée dans une plage de cellules. Veuillez suivre les étapes suivantes :

1. Sélectionnez la plage de cellules dans laquelle vous souhaitez uniquement saisir du texte en majuscules, en minuscules ou en majuscules.

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 apparaît, 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 ».
  autoriser uniquement le texte en majuscules :

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

  Autoriser uniquement le texte en minuscules

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

  Autoriser uniquement le texte en majuscules et minuscules

  =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 autorise les valeurs qui existent / n'existent pas dans une autre liste

Autoriser ou interdire 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 fonction de validation des données avec une formule simple basée sur la fonction NB.SI pour gérer cela.

Par exemple, je souhaite que seules les valeurs de la plage C2:C4 soient saisies dans une plage de cellules comme illustré ci-dessous, pour résoudre ce travail, procédez comme suit :

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

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 apparaît, 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 ».
  Autoriser uniquement les valeurs existant dans une autre colonne

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

  Empêcher que des valeurs existent 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 empêcher 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 interdites.

4.8 La validation des données n'oblige à saisir que le format du numéro de téléphone

Lorsque vous saisissez les informations des employés de votre entreprise, une colonne doit saisir le numéro de téléphone, pour garantir une saisie rapide et précise des numéros de téléphone. Dans ce cas, vous pouvez définir la validation des données pour les numéros de téléphone. Par exemple, je veux juste que le numéro de téléphone au format (123) 456-7890 soit autorisé à entrer dans une feuille de calcul, cette section présentera deux astuces rapides pour résoudre cette tâche.

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

Pour autoriser uniquement la saisie d'un format de numéro de téléphone spécifique, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir un format de numéro de téléphone spécifique, puis faites un clic droit, choisissez « Format de cellule » dans le menu contextuel, voir capture d'écran :

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

3. Cliquez ensuite 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 ». Dans la boîte de dialogue contextuelle, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 dans laquelle vous souhaitez valider le numéro de téléphone.

5. Désormais, lorsque vous saisissez un numéro à 10 chiffres, il sera automatiquement converti au format de numéro de téléphone spécifique selon vos besoins, voir les captures d'écran :

Remarque : si le numéro saisi ne comporte pas 10 chiffres, un message d’avertissement apparaîtra pour vous le rappeler, voir capture d’écran :

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

La fonction « Valider le numéro de téléphone » de « Kutools for Excel » peut également vous aider à forcer la saisie du format du numéro de téléphone en quelques clics seulement.

1. Sélectionnez la liste des cellules qui n'autorisent que des numéros de téléphone spécifiques, puis cliquez sur « Kutools » > « Empêcher la saisie » > « Valider le numéro de téléphone », 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 formatage 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, seul le numéro de téléphone avec le formatage spécifique peut être saisi. Sinon, un message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :

4.9 La validation des données force uniquement les adresses email à saisir

En supposant que vous deviez saisir plusieurs adresses e-mail dans une colonne d'une feuille de calcul, pour empêcher la saisie de certaines adresses e-mail incorrectes, vous pouvez normalement définir une règle de validation des données pour autoriser uniquement le formatage des adresses e-mail.

Forcer uniquement le format des adresses e-mail à l'aide de la fonction de validation des données

En utilisant la fonctionnalité 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 non valides. Suivez ces étapes :

1. Sélectionnez les cellules dans lesquelles vous souhaitez que seules les adresses e-mail soient saisies, 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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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. Maintenant, si le texte saisi ne correspond pas au format de l'adresse e-mail, une boîte de message d'avertissement apparaîtra pour vous en informer, voir capture d'écran :

 Forcer uniquement le format des adresses e-mail à l'aide d'une fonctionnalité pratique

« Kutools for Excel » prend en charge une fonctionnalité étonnante : « Valider l'adresse e-mail ». Avec cet utilitaire, vous pouvez empêcher les adresses e-mail invalides en un seul clic.

1. Sélectionnez les cellules où seules les adresses e-mail sont autorisées, puis cliquez sur « Kutools » > « Empêcher la saisie » > « Valider l'adresse e-mail ». Voir capture d'écran :

2. Et puis, seul le formatage de l'adresse e-mail peut être saisi, sinon, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :

4.10 La validation des données n'oblige à saisir que les adresses IP

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

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

Pour autoriser uniquement la saisie d'adresses IP dans une plage de cellules spécifique, procédez comme suit :

1. Sélectionnez les cellules dans lesquelles vous souhaitez que seule l'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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 non valide est saisie dans la cellule, une boîte de message d'avertissement apparaîtra, comme indiqué dans la capture d'écran ci-dessous :

 Forcer uniquement le format des adresses IP avec le code VBA

Ici, le code VBA suivant peut également aider à autoriser uniquement la saisie d'adresses IP et à restreindre d'autres saisies, procédez comme suit :

1. Cliquez avec le bouton 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.

Code VBA : valider les cellules pour n'accepter que l'adresse 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 dans laquelle vous souhaitez accepter uniquement les adresses IP.

2. Enregistrez et fermez ensuite ce code. Seules les adresses IP valides peuvent désormais être saisies dans les cellules spécifiées.

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

Si « Kutools for Excel » est installé dans votre classeur, sa fonction « Valider l'adresse IP » peut également vous aider à résoudre cette tâche.

1. Sélectionnez les cellules où seules les adresses IP sont autorisées, puis cliquez sur « Kutools » > « Empêcher la saisie » > « Valider l'adresse IP ». Voir capture d'écran :

2. Après avoir appliqué cette fonctionnalité, désormais, seule l'adresse IP peut être saisie, sinon, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :

4.11 La validation des données limite les valeurs qui dépassent la valeur totale

Supposons que vous ayez un rapport de dépenses mensuel et que le total du budget soit de 18000 XNUMX $. Vous devez vous assurer que le montant total de la liste des dépenses ne dépasse pas ce total prédéfini, comme indiqué 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 SUM pour empêcher que la somme des valeurs ne dépasse un total prédéfini.

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que les valeurs soient limitées.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 » correspond à la plage de cellules dont vous souhaitez limiter les entrées.

3. Désormais, lors de la saisie des valeurs de la plage B2:B7, si le total est inférieur à 18000 18,000 $, la validation est réussie. Si une valeur entraîne un total supérieur à XNUMX XNUMX $, un message d'avertissement s'affiche pour vous en informer.

4.12 La validation des données restreint la saisie des cellules 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 ce problème. Par exemple, si la cellule C1 contient le texte « Oui », la plage A2:A9 autorise toutes les entrées. Cependant, si la cellule C1 contient un autre texte, les entrées dans la plage A2:A9 sont restreintes, comme indiqué dans les captures d'écran ci-dessous :

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

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que les valeurs soient limitées.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 », vous pouvez saisir n'importe quelle valeur dans la plage A2:A9. Si la cellule C1 contient un autre texte, vous ne pourrez saisir aucune valeur, voir la démonstration ci-dessous :

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

Si vous avez besoin que seuls les jours de la semaine (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 suivantes :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que les jours de la semaine ou les jours de la semaine 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 apparaît, 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 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. Désormais, vous ne pouvez saisir que des dates de semaine ou de week-end dans les cellules spécifiées en fonction de votre sélection.

4.14 La validation des données permet de saisir la date en fonction de la date du jour

Il peut parfois être nécessaire de n'autoriser la saisie que des dates postérieures ou postérieures à aujourd'hui dans une liste de cellules. La fonctionnalité « Validation des données » associée à la fonction « AUJOURD'HUI » peut vous être utile. Procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que seule la date future (date ultérieure à celle d'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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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. Désormais, seules les dates postérieures à aujourd'hui peuvent être saisies dans les cellules. Dans le cas contraire, un message d'avertissement apparaîtra pour vous avertir, voir capture d'écran :

=A2<Today()

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

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

Si vous souhaitez valider les 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, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement les heures avant ou après l'heure actuelle.

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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Heure » dans la liste déroulante « Autoriser ».
• Choisissez ensuite « inférieur à » pour autoriser uniquement les heures antérieures à l'heure actuelle, ou « supérieur à » pour autoriser les heures postérieures à l'heure actuelle selon vos besoins dans la liste déroulante « Données ».
• Et puis, dans la case « 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. Désormais, seules les heures antérieures ou postérieures à l'heure actuelle peuvent être saisies dans les cellules spécifiques.

4.16 Validation des données la date de l'année spécifique ou en cours

Pour autoriser uniquement la saisie des dates d'une certaine année ou de l'année en cours, vous pouvez utiliser la validation des données avec une formule personnalisée basée sur la fonction ANNÉE.

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que seules les dates d’une année donnée 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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Personnalisé » dans la liste déroulante « Autoriser ».
• Ensuite, entrez 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 illustré ci-dessous :

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

4.17 Validation des données la date dans la semaine ou le 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.

Permet de saisir la date de la semaine en cours

1. Sélectionnez la liste des cellules dans lesquelles 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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Date » dans la liste déroulante « Autoriser ».
• Et puis, choisissez « entre » dans la liste déroulante « Données ».
• Dans la zone de texte « Date de début », saisissez 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 peuvent être saisies, les autres dates seront bloquées comme illustré ci-dessous :

 Permet de saisir la date du mois en cours

Pour autoriser uniquement la saisie des dates du mois en cours, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez que seules les dates du mois 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 apparaît, sous l'onglet « Paramètres », effectuez les opérations suivantes :

• Sélectionnez « Date » dans la liste déroulante « Autoriser ».
• Et puis, choisissez parmi les « Données » dans le menu déroulant.
• 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 éditer la validation des données dans Excel ?

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

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

2. Cliquez ensuite sur « Données » > « Validation des données » > « Validation des données » pour accéder à la boîte de dialogue « Validation des données ». Dans la zone, modifiez les règles selon vos besoins, puis cochez l'option « Appliquer ces modifications à toutes les autres cellules avec les mêmes paramètres » pour appliquer cette nouvelle règle à toutes les autres cellules avec les critères de validation d'origine. Voir capture d'écran :

3. Cliquez sur « OK » pour enregistrer les modifications.

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

Si vous avez créé plusieurs règles de validation de données dans votre feuille de calcul, vous devez maintenant rechercher et sélectionner les cellules qui ont appliqué les règles de validation de données. La commande « Aller à spécial » peut vous aider à sélectionner tous les types de validation de données ou un type spécifique de validation de données.

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

2. Cliquez ensuite sur « Accueil » > « Rechercher et sélectionner » > « Aller à Spécial », voir capture d'écran :

3. Dans la boîte de dialogue « Aller à spécial », sélectionnez « Validation des données » > « Tout », 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.

Conseils : si vous souhaitez sélectionner un type spécifique de validation de données, sélectionnez d'abord une cellule contenant la validation de données souhaitée, puis accédez à la boîte de dialogue « Aller à spécial » et choisissez « Validation des données » > « Identique ».

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

Supposons que vous ayez créé une règle de validation des données pour une liste de cellules et que vous deviez maintenant 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 dans d'autres cellules rapidement et facilement.

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

2. Sélectionnez ensuite les cellules que vous souhaitez valider, pour sélectionner plusieurs cellules non adjacentes, maintenez la touche « Ctrl » enfoncée tout en sélectionnant les cellules.

3. Ensuite, faites un clic droit sur la sélection, sélectionnez 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 désormais copiée dans les nouvelles cellules.

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

Il peut parfois être nécessaire de créer des règles de validation pour des données existantes. Dans ce cas, des données invalides peuvent apparaître dans la plage de cellules. Comment vérifier et modifier les données invalides ? Dans Excel, vous pouvez utiliser la fonction « Entourer les données invalides » pour les mettre en évidence d'un cercle rouge.

Pour encercler les données non valides dont vous avez besoin, appliquez la fonctionnalité « Validation des données » afin de définir une règle pour la plage de données. Procédez comme suit :

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

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 validerai les valeurs supérieures à 500, voir capture d'écran :

3. Cliquez ensuite 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 » > « Entourer les données invalides ». Toutes les valeurs invalides inférieures à 500 seront alors entourées d'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é « Entourer les données invalides » ne peut encercler que 255 cellules au maximum. Lors de l'enregistrement du 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 la validation des 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 apparaît, sous l'onglet « Paramètres », cliquez sur le bouton « Tout effacer », voir capture d'écran :

3. Cliquez ensuite sur le bouton « OK » pour fermer la boîte de dialogue. La règle de validation des données appliquée à la plage sélectionnée est alors supprimée.

Conseils : Pour supprimer la validation des données de la feuille de calcul actuelle, veuillez d'abord sélectionner la feuille entière, 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 pour Excel », sa fonction « Effacer les restrictions de validation des données » peut également vous aider à supprimer les règles de validation des données de la plage sélectionnée ou de la feuille de calcul entière.

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. Cliquez ensuite sur « Kutools » > « Empêcher la saisie » > « Effacer les restrictions de validation des données », voir capture d'écran :

3. Dans la boîte de dialogue qui s'affiche, cliquez sur « OK » et la règle de validation des données sera effacée selon vos besoins.

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

Pour supprimer les règles de validation des données de l'ensemble du classeur, les méthodes ci-dessus prendront du temps s'il y a beaucoup de feuilles de calcul, ici, le code ci-dessous peut vous aider à gérer cette tâche rapidement.

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

2. Cliquez ensuite sur « Insertion » > « Module » et collez la macro suivante dans la fenêtre « Module ».

Code VBA : supprimez 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. Appuyez ensuite sur la touche « F5 » pour exécuter ce code et toutes les règles de validation des données ont été immédiatement supprimées de l'ensemble du classeur.