Salut tout le monde. À quelle fréquence rencontrez-vous des feuilles de calcul partagées remplies par plusieurs employés ? Par exemple, vous créez une base de données dans Excel et plusieurs personnes saisissent toutes les informations. Les erreurs ne peuvent être évitées ; des données incorrectes se glissent souvent dans les tableaux. Maintenant, si Excel pouvait les vérifier avant de les saisir et indiquer une erreur...

Mais il le peut ! Le programme dispose d'un outil puissant intégré appelé « Vérification des données », qui minimise les erreurs de saisie des informations.

Comment faire la validation des données dans Excel

Vous pouvez fournir une vérification des données au stade de la conception du document afin que l'utilisateur ne puisse pas saisir de données erronées ou ajouter une vérification aux tableaux existants.

Pour connecter l'outil « Validation des données », sélectionnez les cellules à vérifier et cliquez sur le « Ruban » Données – Utilisation des données – Validation des données" La fenêtre des paramètres « modérateur » s’ouvrira :

Dans l'onglet « Paramètres », vous devez définir la condition de vérification dans le champ « Type de données ». Il y a 8 types de conditions à vérifier :

1. N'importe quelle valeur– vérification des données désactivée
2. Entier– vous ne pouvez saisir qu'un nombre entier avec un critère supplémentaire ;
3. Nombre réel– tout nombre réel avec un critère supplémentaire
4. Liste– sélectionner une valeur dans une liste prédéfinie
5. Date– saisir uniquement la date qui répond au critère supplémentaire
6. Temps– entrez l'heure avec une condition supplémentaire
7. Longueur du texte– saisir un texte d'une certaine longueur
8. Un autre– entrez une valeur qui satisfait l’expression logique écrite manuellement

Dans cette fenêtre, vous pouvez cocher deux cases supplémentaires :

• Ignorer les cellules vides– ne pas appliquer la condition aux cellules vides
• Propager les modifications à d'autres cellules avec la même condition– trouver des cellules avec des conditions similaires et leur appliquer les mêmes modifications

Dans l'onglet Message d'entrée, saisissez une indication sur les données que vous souhaitez saisir dans la cellule.

Dans l'onglet Message d'erreur, sélectionnez l'événement qui se produira lorsque vous saisirez des données incorrectes. Commençons par faire une sélection dans la liste « Affichage » :

1. Arrêt– annuler la saisie des données erronées, afficher le message spécifié
2. Avertissement– le programme vous informera que la valeur ne répond pas aux exigences et vous précisera s'il faut la saisir dans la cellule
3. Message– à peu près la même chose qu’un avertissement, mais semble moins intimidant

Après cela, nous écrirons le titre et le corps du texte de la fenêtre d’avertissement.

Parlons maintenant plus en détail de chaque type de condition.

Condition entière dans Excel

Si vous ne pouvez saisir que des nombres entiers dans les cellules, sélectionnez ce type de données. La liste avec un paramètre supplémentaire dans lequel vous devez définir des restrictions deviendra active. Faites un choix :

• Entre valeur minimale et maximale
• Dehors plage de valeurs spécifiée
• Égal une certaine valeur
• Pas égal une certaine valeur
• Plus valeur définie
• Moins valeur définie
• Supérieur ou égal à signification
• Inférieur ou égal à signification

Condition sur valeur réelle

Les nombres réels sont tous des nombres positifs et négatifs, ainsi que zéro. Cette règle diffère du paragraphe précédent dans la mesure où des nombres fractionnaires peuvent également être saisis. Tous les autres paramètres sont similaires à ceux décrits ci-dessus.

Liste

Comme je l'ai déjà dit, appliquer cette règle signifie qu'il sera demandé à l'utilisateur de sélectionner une option à remplir dans une liste pré-compilée. Par exemple, si vous vendez plusieurs marques de voitures, vous pouvez limiter la capacité de l'opérateur à saisir une marque uniquement avec une liste de votre assortiment. Lorsque l'utilisateur sélectionne une cellule à remplir, une liste déroulante apparaît avec les options possibles. Il ne reste plus qu'à sélectionner celui souhaité avec la souris ou le clavier.

Pour spécifier une liste, décrivez-la dans le champ source. Cela peut être fait de plusieurs manières :

• Ecrivez la liste en cellules sur une feuille du livre, et dans le champ « Source » indiquez un lien vers ce tableau
• Créez un tableau nommé, précisez son nom dans le champ « Source »
• Lister les éléments directement dans le champ « Source » séparés par des points-virgules

L'utilisateur peut écrire sa propre valeur, différente de celles de la liste. Mais si le type de contrôle « Stop » est sélectionné, le programme ne vous permettra pas de le saisir dans la cellule. Avec d’autres méthodes de vérification, la valeur sera écrite dans la cellule, mais sera toujours considérée comme incorrecte.

Date

Ce filtre vous permettra de saisir uniquement la date dans la cellule. Permettez-moi de vous rappeler qu'une date dans Excel est aussi un nombre, cette règle a donc quelque chose en commun avec la condition d'une valeur réelle. Cependant, vous ne pouvez saisir qu'une date, en indiquant les mêmes paramètres que dans les deux premiers paragraphes.

Temps

Vérification de l'heure - comme dans le paragraphe précédent, seule une heure spécifique est définie sans référence à une date. De plus, un paramètre supplémentaire est introduit pour limiter la sélection.

Longueur du texte

Avec cette vérification, vous pouvez saisir à la fois un chiffre et du texte, mais il est proposé de poser une condition sur sa longueur (en caractères). La liste des paramètres est toujours la même : la longueur entre les valeurs données, en dehors de l'écart, égale à une certaine valeur, etc.

Un autre

Cette méthode de vérification est la plus flexible. Dans la ligne « Formule », vous pouvez spécifier votre propre formule logique de vérification si aucune des méthodes de vérification précédentes ne vous convient. Par exemple, pour que seuls les codes commençant par les caractères « SUV » et contenant 12 caractères soient saisis dans la cellule A1, on rentre la formule dans le champ : =ET(GAUCHE(A1,3)="SUV";LONGUEUR(A1)=12). Ce n'est que si cette règle est respectée que la valeur sera considérée comme correcte.

Comment vérifier les conditions dans les cellules Excel

Si vous avez appliqué la validation des données à une table contenant déjà des données, vous pouvez vérifier sa conformité aux règles spécifiées et, si nécessaire, la corriger manuellement. Pour ce faire, cliquez sur la flèche vers le bas à côté de la commande du ruban « Vérifier les données » et sélectionnez « Encercler les données invalides » dans le menu déroulant. Excel encerclera les cellules contenant des erreurs en rouge. Il ne reste plus qu'à corriger les erreurs, les cercles disparaîtront d'eux-mêmes.

Pour supprimer les sélections sans correction, dans le même menu, sélectionnez « Supprimer le trait des données invalides ».

Ainsi, dans cette leçon, nous avons examiné l'outil « Data Check », qui vous permet de trouver des erreurs dans le remplissage des tableaux avec des informations, ainsi que d'éviter l'apparition de nouvelles. Vous savez combien il est important que les données soient « propres », car il est plus facile de les traiter et les résultats des calculs refléteront plus précisément les processus étudiés. Il est donc préférable de mettre en service ce merveilleux outil. Ainsi, je me protège parfois lors de la création de tableaux de données. Je pense que « Data Check » sera également un assistant utile pour vous !

Je pars écrire un nouvel article important, cette fois-ci. Ne le manquez pas, surtout si vos tables sont grandes et complexes. À bientôt!

Lors de la validation des données, vous pouvez facilement spécifier les règles auxquelles les données doivent répondre. Malheureusement, Excel insiste sur le fait que les listes utilisées pour la validation doivent figurer sur la même feuille de calcul que les données en cours de validation. Heureusement, il existe toujours des moyens d’éviter cette exigence.

Dans cette astuce, nous allons vous présenter deux manières de valider des données en fonction d'une liste dans une autre feuille. Le premier tire parti des plages nommées d'Excel (qui sont abordées plus en détail au chapitre 3), tandis que le second utilise un appel de fonction.

Méthode 1 : plages nommées

Le moyen le plus simple et le plus rapide de surmonter les obstacles à la validation des données dans Excel consiste peut-être à nommer la plage contenant la liste. Pour créer une plage nommée, sélectionnez les cellules contenant la liste et entrez un nom dans la zone Nom de la barre de formule. Pour exécuter cet exemple, nous supposerons que la plage est nommée MyRange.

Sélectionnez la cellule dans laquelle vous souhaitez que la liste déroulante apparaisse, puis sélectionnez Données → Validation. Dans le champ Autoriser, sélectionnez Liste et dans le champ Source, saisissez =MyRange. Cliquez sur OK. Puisque vous avez utilisé une plage nommée, votre liste (même si elle se trouve sur une feuille différente) peut désormais être utilisée comme liste de validation.

Méthode 2 : fonction INDIRECT

Fonction INDIRECT(INDIRECT) vous permet de référencer une cellule contenant du texte représentant l'adresse de la cellule. Cette cellule peut être utilisée comme référence locale même si elle reçoit des données d'une autre feuille de calcul. Vous pouvez utiliser cette fonctionnalité pour créer un lien vers la feuille où se trouve la liste.

Disons que la liste est sur Sheetl dans la plage $A$1:$A$8 . Cliquez sur n’importe quelle cellule d’une autre feuille où cette liste de contrôle (liste de sélection) doit apparaître. Sélectionnez ensuite la commande Données → Validation et dans le champ Autoriser, sélectionnez Liste. Dans le champ Source, saisissez le code suivant : =INDIRECT("Sheetl!$A$1:$A$8"), dans la version russe d'Excel =INDIRECT("Sheetl!$A$1:$A$8"). Assurez-vous que la case Liste de valeurs valides (dans la cellule) est cochée et cliquez sur OK. La liste dans Sheetl doit apparaître dans la liste déroulante Validation.

Si le nom de la feuille sur laquelle se trouve la liste contient des espaces, vous devez utiliser la syntaxe de fonction suivante INDIRECT(INDIRECT): =INDIRECT(""Sheetl"!$A$1:$A$8") , dans la version russe d'Excel =INDIRECT(""Sheetl"!$A$1:$A$8") . La différence est qu'ici, il y a une apostrophe après le premier guillemet et la deuxième apostrophe est avant le point d'exclamation.

C'est une bonne idée de toujours utiliser des apostrophes simples, que le nom contienne ou non des espaces. Avec les apostrophes, vous pourrez toujours référencer des feuilles avec des noms sans espaces, et cela facilitera également les modifications ultérieures.

Avantages et inconvénients des deux méthodes

Pour les plages et fonctions nommées INDIRECT(INDIRECT) présente des avantages et des inconvénients. L'avantage d'utiliser une plage nommée est que la modification du nom de la feuille n'affectera pas la liste de validation. Cela met en évidence le manque de fonction INDIRECT(INDIRECT) : tout changement dans le nom de la feuille n'y sera pas automatiquement répercuté. Avantage des fonctionnalités INDIRECT(INDIRECT) : lorsque la première cellule ou ligne ou la dernière cellule ou ligne est supprimée d'une plage nommée, la plage nommée renverra un #REF ! . C'est l'inconvénient d'une plage nommée : si vous en supprimez des cellules ou des lignes, les modifications n'affecteront pas la liste de validation.

Récemment, ma fille a demandé s'il était possible dans Excel de faire une liste déroulante dans une cellule contextuelle, par exemple, en fonction du contenu de la cellule à gauche de la cellule avec la liste ? Je n'ai pas utilisé de listes déroulantes dans mon travail depuis un certain temps, j'ai donc d'abord décidé de rafraîchir mes connaissances sur la validation des données dans Excel. En fait, voyez la réponse à la question de ma fille.

Vérificateur de données

Excel vous permet de définir certaines règles qui détermineront quelles données peuvent être contenues dans une cellule. Par exemple, il est nécessaire que le nombre contenu dans la cellule appartienne à la plage de 1 à 12. Si l'utilisateur saisit une valeur incorrecte, le programme affichera un message correspondant (Fig. 1).

Riz. 1. Afficher un message concernant une saisie de données incorrecte

Téléchargez la note au format ou, exemples au format

Excel facilite la création de critères pour valider les données, notamment en utilisant des formules pour créer des critères plus complexes.

L'outil de validation des entrées présente un inconvénient fondamental : si l'utilisateur copie une cellule puis la colle dans une cellule dans laquelle les données d'entrée sont en cours de validation, alors toutes les formules définies pour la validation disparaîtront.

Définir le critère de test

Pour déterminer le type de données qu'une cellule ou une plage de cellules peut contenir, procédez comme suit :

1. Sélectionnez une cellule ou une plage de cellules.

2. Sélectionnez un onglet Données, région Travailler avec des données → Vérification des données. Excel affiche la boîte de dialogue Valider l'entrée.

3. Cliquez sur l'onglet Possibilités(Fig.2).

Riz. 2. Onglet Possibilités boîte de dialogue Validation des valeurs saisies

4. Sélectionnez l'une des options dans la liste déroulante Type de données. Selon l'option sélectionnée, l'apparence de l'onglet peut changer. Possibilités en ajoutant ou en supprimant des contrôles supplémentaires (pour définir une formule, vous devez sélectionner l'option Un autre).

5. À l'aide des contrôles disponibles sur cet onglet, définissez les critères de vérification des données. Les contrôles disponibles dépendent de la sélection effectuée à l'étape précédente.

6. (Facultatif) Cliquez sur l'onglet Message pour la saisie et entrez le message qui devrait apparaître à l'écran lors de la mise en évidence cellule spécifiée . Ce message s'affiche pour informer l'utilisateur quelles données peuvent être saisies. Si vous sautez cette étape, aucun message n'apparaîtra lorsque vous sélectionnerez une cellule.

7. (Facultatif) Cliquez sur l'onglet Message d'erreur et saisissez le message qui doit apparaître lorsque l'utilisateur entrera une valeur invalide . Sélection d'un type de message dans la liste Voir déterminera le choix dont disposera l'utilisateur lors de la saisie de données incorrectes. Pour éviter de saisir des valeurs incorrectes, vous devez sélectionner un type de message Arrêt. Si vous ignorez cette étape, un message d'erreur standard apparaîtra lorsqu'une erreur se produit.

8. Cliquez sur OK.

Après avoir effectué ces actions, les données saisies seront vérifiées dans la cellule ou la plage sélectionnée.

Types de données vérifiées

Languette Possibilités boîte de dialogue Validation des valeurs saisies fournit une large sélection de types de données pour les conditions de vérification. Vous pouvez sélectionner le type de données valides dans la liste déroulante Type de données(Comme indiqué, le reste des contrôles de cet onglet change en fonction du type de données sélectionné). Vous pouvez sélectionner les types de données suivants.

• N'importe quelle valeur. La sélection de cette option supprime la condition de validation des données. Cependant, le message saisi sera toujours affiché à moins que la case ne soit décochée Afficher un message d'erreur dans l'onglet Message à saisir.
• Entier. L'utilisateur doit saisir un entier. Utiliser une liste déroulante Signification vous pouvez définir une plage de valeurs acceptable. Par exemple, vous pouvez spécifier que la valeur d'entrée doit être un entier supérieur ou égal à 100.
• Valide. L'utilisateur doit saisir un numéro valide. La plage de valeurs valides peut être déterminée à l'aide de la liste déroulante Signification. Par exemple, vous pouvez spécifier que le nombre que vous saisissez doit être supérieur ou égal à 0 et inférieur ou égal à 1.
• Liste. L'utilisateur doit sélectionner une valeur dans la liste de valeurs proposée. Voir la section ci-dessous pour plus de détails. .
• Date. L'utilisateur doit saisir la date. Utiliser une liste déroulante Signification vous pouvez définir une plage de dates valide. Par exemple, vous pouvez spécifier que la date que vous saisissez doit être supérieure ou égale au 1er janvier 2012 et inférieure ou égale au 31 décembre 2012.
• Temps. L'utilisateur doit saisir une valeur temporelle. Utiliser une liste déroulante Signification vous pouvez définir une plage de valeurs acceptable. Par exemple, la valeur horaire saisie doit être supérieure à 12h00.
• Longueur du texte. La longueur de la chaîne d'entrée (nombre de caractères) est limitée. Utiliser une liste déroulante Signification vous pouvez déterminer la longueur de chaîne autorisée. Par exemple, vous pouvez spécifier que la longueur de la chaîne d'entrée doit être 1 (un caractère).
• Un autre. Une formule logique qui détermine si la saisie de l'utilisateur est correcte. La formule peut être saisie directement dans le champ Formule(qui apparaît lorsque vous sélectionnez ce type) ou définissez une référence à une cellule avec une formule. Vous trouverez ci-dessous des exemples de plusieurs formules utiles.

Dans l'onglet Possibilités boîte de dialogue Validation des valeurs saisies contient deux options.

• Ignorer les cellules vides. Si cette option est cochée, les entrées vides sont autorisées dans les cellules pour lesquelles la validation des données est définie. Ce drapeau agit un peu bizarrement. Si vous décochez la case et définissez une règle de validation des données dans la cellule, la case vous permettra de laisser la cellule vide. Mais dès que vous essayez d’entrer quelque chose d’inacceptable dans une cellule, puis que vous essayez de laisser la cellule vide, cela ne fonctionnera pas.
• Propager les modifications à d’autres cellules présentant la même condition. Si cette option est cochée, toutes les modifications apportées seront également appliquées aux autres cellules contenant les conditions de validation des données d'origine. Si vous avez défini une règle dans une ou plusieurs cellules, puis sélectionné une plage qui inclut ces cellules et quelques autres, et cliqué sur le menu Vérification des données, alors le message apparaîtra : « La zone sélectionnée contient des cellules sans conditions sur les valeurs. Étendre les conditions à ces cellules ? Si vous cliquez sur « Oui », un onglet apparaîtra Possibilités boîte de dialogue Validation des valeurs saisies. Alors maintenant, il y a une case à cocher Propager les modifications à d'autres cellules avec la même condition pas besoin de le mettre.

Il est important de se rappeler que même une fois la validation des entrées définie, l'utilisateur peut toujours saisir des valeurs incorrectes. Même si la liste déroulante Vue par onglets Message d'erreur type de message sélectionné Arrêt, la possibilité de saisir des données incorrectes existe toujours. Il convient également de prêter attention au fait que la vérification des données d'entrée ne répond pas aux résultats des calculs de formule. En d’autres termes, appliquer la validation des données à une cellule contenant une formule ne produira aucun résultat.

Excel a une commande Données → Travailler avec des données → Vérification des données → Entourez les informations incorrectes, après avoir sélectionné quelles valeurs incorrectes seront entourées en rouge (Fig. 3).

Riz. 3. Les cellules avec des valeurs invalides (valeurs supérieures à 100) sont encerclées

Création d'une liste déroulante

L’utilisation la plus courante de la validation des entrées consiste peut-être à créer une liste déroulante de valeurs. Sur la fig. La figure 4 montre un exemple dans lequel les noms de mois contenus dans la plage A1:A12 sont utilisés pour créer une liste déroulante.

Riz. 4. Liste créée à l'aide de Data Validator

Pour créer une liste comme celle-ci :

1. Entrez une liste de valeurs dans une colonne ou une ligne. Ces valeurs seront utilisées dans la liste déroulante.

2. Sélectionnez la cellule qui doit contenir la liste déroulante (D3 dans notre exemple).

3. Dans l'onglet Possibilités boîte de dialogue Validation des données d'entrée sélectionner le type de données Liste et sur le terrain Source spécifiez une plage qui contient une liste de valeurs (dans notre exemple, $A$1:$A$12).

4. Assurez-vous que la case est cochée Liste des valeurs valides.

5. Effectuez d'autres paramètres dans la boîte de dialogue Validation des données d'entrée, comme décrit dans la section précédente.

Après avoir terminé ces étapes, lorsque vous activez une cellule, un bouton déroulant apparaîtra à droite de celle-ci. Cliquez sur ce bouton pour développer la liste et sélectionner la valeur souhaitée.

Si la liste doit contenir un petit nombre de valeurs, elles peuvent être saisies directement dans le champ Source dans l'onglet Possibilités boîte de dialogue Validation des valeurs saisies(ce champ apparaîtra si vous sélectionnez dans la liste déroulante Taper données taper Liste). Entre les valeurs saisies, vous devez insérer un séparateur défini conformément aux paramètres régionaux (pour la Russie, il s'agit d'un point-virgule).

Si vous utilisez une plage pour définir une liste déroulante, elle doit se trouver sur la même feuille que la cellule activée. Dans le cas où la liste doit utiliser des valeurs d'une plage qui se trouve sur une autre feuille, vous pouvez lui donner un nom puis utiliser ce nom dans le champ Source(après le symbole égal). Par exemple, si la liste contient des valeurs de la liste MaListe, vous saisirez la formule suivante : =MaListe

Valider les données à l'aide de formules

Créer des contrôles de données simples est facile avec le validateur d'entrée. Mais le véritable pouvoir de cette propriété ne peut être ressenti que si vous travaillez avec des formules de validation de données.

Une formule définie pour valider les données doit renvoyer la valeur booléenne VRAI ou FAUX. S'il renvoie VRAI, la valeur saisie sera écrite dans la cellule. Si le résultat du calcul de la formule est FAUX, un message d'erreur apparaîtra contenant l'avertissement défini dans l'onglet Message d'erreur boîte de dialogue Validation des valeurs saisies.

Vous pouvez définir la formule dans la boîte de dialogue Validation des valeurs saisies. Pour ce faire, depuis la liste déroulante Type de données onglets Possibilités sélectionner le type Un autre. La formule peut être saisie directement dans le champ Formule ou insérez un lien vers la cellule contenant cette formule. Champ Formule apparaît dans l'onglet Possibilités seulement si le type est sélectionné Un autre.

Type de références de cellules dans les formules pour la validation des données

Si la formule saisie dans la boîte de dialogue Validation des valeurs saisies, contient une référence de cellule, elle sera traitée comme une référence relative à la cellule supérieure gauche de la plage sélectionnée.

L'exemple suivant expliquera pourquoi les liens relatifs doivent être utilisés dans les formules pour valider les données. Supposons que seuls les nombres impairs soient nécessaires dans la plage B2:B10. Dans la boîte de dialogue Validation des valeurs saisies Ce type de données n'existe pas, vous devez donc utiliser une formule pour créer un critère de validation des données. Pour créer un tel critère de validation des données, procédez comme suit :

1. Sélectionnez la plage B2:B10 pour que la cellule B2 soit activée.

2. Sélectionnez une équipe Données → Travailler avec des données → Vérification des données pour ouvrir la boîte de dialogue Validation des valeurs saisies.

3. Allez dans l'onglet Possibilités et sur la liste Type de données sélectionner Un autre.

4. Entrez la formule suivante dans la case Formule(Fig. 5) =INCOMPTE(B2). Cette formule utilise la fonction ODD, qui renvoie VRAI si son argument est un nombre impair.

5. Allez dans l'onglet Message d'erreur et sélectionnez le type de message Arrêt. Saisissez également le texte du message « Seuls les nombres impairs sont autorisés ».

6. Cliquez sur OK pour fermer la boîte de dialogue Validation des valeurs saisies.

Riz. 5. Saisir une formule dans la boîte de dialogue Validation des valeurs saisies

Notez que la formule que vous avez saisie contient une référence à la cellule supérieure gauche de la plage sélectionnée. Cette formule doit s'appliquer à toute la plage de cellules. Vous devez donc vous attendre à ce que chaque cellule de cette plage contienne la même formule. Puisque dans la formule le remplissage par cellule est relatif, cette formule change pour chaque cellule individuelle dans la plage B2:B10. Pour le vérifier, placez par exemple le curseur dans la cellule B5 et ouvrez la boîte de dialogue Validation des valeurs saisies. Dans cette fenêtre, vous devriez voir la formule =UNEVENT(B5)

En général, lorsque vous saisissez une formule pour tester des données dans une plage de cellules, vous devez utiliser une référence relative à la cellule activée, qui est généralement la cellule supérieure gauche de la plage sélectionnée. L'exception concerne les situations dans lesquelles vous devez établir un lien vers une cellule spécifique. Par exemple, vous souhaitez saisir uniquement les valeurs de la plage A1:B10 supérieures à la valeur de la cellule C1. Pour ce faire, utilisez la formule =A1>$C$1

Exemples de formules pour la validation des données

Voici quelques exemples qui utilisent des formules pour valider la saisie. Toutes les formules ont été saisies directement dans le champ Formule dans l'onglet Possibilités boîte de dialogue Validation des valeurs saisies.

Saisissez uniquement du texte. Pour autoriser uniquement la saisie de texte (et non de valeurs numériques) dans une cellule ou une plage, utilisez la formule suivante : =ETEXT(A1). Ici, on suppose que A1 est la cellule activée de la plage allouée.

Saisissez des valeurs supérieures à celles de la cellule précédente. La formule de validation des données suivante vous permet de saisir un nombre dans une cellule uniquement s'il est supérieur à la valeur de la cellule précédente : =A2>A1. La formule suppose que la cellule activée dans la plage sélectionnée est la cellule A2. Notez que cette formule ne peut pas être utilisée sur la première ligne d'une feuille de calcul.

Entrez uniquement des valeurs uniques. La formule de validation des données d'entrée suivante ne permettra pas à l'utilisateur de saisir des valeurs en double dans la plage A1:C20 : =COUNTIF($A$1:$C$20,A1)=1. Ici, on suppose que A1 est la cellule activée de la plage allouée. Notez que le premier argument de la fonction COUNTIF ($A$1:$C$20) est une référence absolue. Le deuxième argument (A1) est une référence relative qui change pour chaque cellule de la plage sélectionnée. Sur la fig. La figure 6 montre le fonctionnement de cette formule. Ici, une tentative est faite pour saisir la valeur 2 dans la cellule A5, qui se trouve déjà dans la plage A1:C20.

Riz. 6. Utilisez le validateur de données pour éviter les entrées en double

Saisir du texte commençant par la lettre A. La formule suivante utilise une technique qui vous permet de rechercher un symbole donné. Dans ce cas, la formule retournera VRAI si vous entrez une chaîne dans la cellule qui commence par la lettre A (quelle que soit la casse) : =LEFT(A1)= " a " . Cette formule suppose que la cellule activée dans la plage sélectionnée est la cellule A1.

Vous trouverez ci-dessous une formule de vérification des données légèrement modifiée. À l'aide de cette formule, vous pouvez saisir une chaîne composée de cinq lettres et commençant par la lettre A :
=COUNTIF (A1; "A????") =1

Cité dans le livre de John Walkenbach. Microsoft Excel 2007. Bible de l'utilisateur. – M : LLC « I.D. Williams », 2008. – pp. 482-489.

Il est facile de commettre des erreurs en saisissant de nombreuses informations dans les cellules d'un tableau. Dans EXCEL, il existe un outil permettant de vérifier les données saisies immédiatement après avoir appuyé sur la touche ENTRÉE - Validation des données.

Outil Vérification des données() ne serait pas aussi populaire si sa fonctionnalité se limitait uniquement à la vérification elle-même. Les techniques de base pour travailler avec cet outil sont brièvement énumérées ci-dessous.

A. Vérification des valeurs saisies

Comme vous pouvez le voir sur la figure ci-dessous, les conditions de vérification de la valeur saisie peuvent être configurées de manière très flexible.

Vous ne pouvez autoriser la saisie de valeurs dans une cellule d'un certain type qu'en sélectionnant la condition requise dans la liste déroulante :

• Entier. Seuls les nombres entiers appartenant à une certaine plage peuvent être saisis dans une cellule ;
• Valide. Seuls des nombres peuvent être saisis dans la cellule, y compris ceux comportant une partie décimale (vous ne pouvez pas saisir de texte, mais vous pouvez saisir une date) ;
• Date. On suppose que des dates allant du 01/01/1900 au 31/12/9999 seront saisies dans la cellule. En savoir plus sur le format de date dans l'article
• Temps. On suppose que dans la cellule avec Ce chèque Ce type entrera l'heure. Par exemple, la figure ci-dessous montre une condition dans laquelle il est permis de saisir l'heure dans une cellule qui n'appartient qu'à la seconde moitié de la journée, c'est-à-dire de 12:00:00 à 23:59:59. Au lieu de saisir fastidieusement la valeur 12:00:00, vous pouvez utiliser son équivalent numérique de 0,5. La possibilité de saisir des nombres au lieu de l'heure découle du fait que toute date dans EXCEL est associée à un entier positif, et donc l'heure (puisqu'elle fait partie du jour) correspond à la partie fractionnaire du nombre (par exemple, 0,5 il est midi). L'équivalent numérique pour 23:59:59 serait 0,99999.

• Longueur du texte. Seul un certain nombre de caractères peut être saisi dans une cellule. Avec cette restriction, vous pouvez saisir à la fois des chiffres et des dates, l'essentiel est que le nombre de caractères saisis ne contredise pas la limite de longueur du texte. Par exemple, si le nombre de caractères est limité à moins de 5, vous ne pouvez pas saisir une date postérieure au 13/10/2173, car il correspond au nombre 99999, et le 14/10/2173 vaut déjà 100000, soit 6 caractères. Fait intéressant, si vous limitez, par exemple, moins de 5 caractères, vous ne pourrez pas saisir la formule =ROOT(2) dans la cellule, car résultat =1,4142135623731 (en fonction de la précision spécifiée dans EXCEL), mais =ROOT(4) - vous pouvez, car le résultat =2, et ce n'est qu'un seul caractère.
• Liste. Probablement le type de données le plus intéressant. Dans ce cas, la saisie de valeurs dans une cellule peut être limitée à une liste préalablement définie. Par exemple, si vous spécifiez l'unité de mesure d'un produit comme source, séparée par un point-virgule pcs;kg;m²;cub.m, alors vous ne pourrez sélectionner autre chose que ces 4 valeurs dans la liste. Dans la source, vous pouvez spécifier une plage de cellules contenant une liste pré-générée ou un lien vers . Un exemple est donné dans l'article
• Un autre. La cellule est autorisée à saisir des valeurs qui satisfont à des critères plus complexes. Pour définir les critères, vous devez utiliser une formule. Considérons cette condition plus en détail.

Avec le type sélectionné Un autre, dans le champ Formule vous devez entrer une formule pour calculer la valeur logique. S'il est évalué à VRAI, alors une telle valeur peut être saisie dans la cellule, si elle est FAUX, alors votre valeur ne peut pas être saisie. Comme argument de formule, vous devez utiliser une référence à la cellule elle-même à laquelle elle est appliquée. Vérification des données ou une référence à une cellule qui en dépend. Par exemple,

Lors de la sélection de plusieurs cellules, n'oubliez pas, si nécessaire, de fournir une référence absolue aux cellules (par exemple, 1$AU : 20$AU ).

Lors de l'utilisation de l'outil Vérification des données, on suppose que des constantes seront saisies dans la cellule ( 123, produit1, 01/05/2010 etc.), même si personne n’interdit de saisir des formules. Dans ce cas, le résultat du calcul de la formule sera quand même vérifié. En général, je ne recommande pas de saisir des formules dans des cellules avec validation des données - il est facile de se tromper. Dans ce cas, je vous conseille d'utiliser .

DANS.Afficher un commentaire si la cellule est la cellule courante.

Utilisez l'onglet Message de sortie pour afficher le commentaire.

Contrairement à la note habituelle ( Réviser/Commenter/Créer un commentaire), qui disparaît lorsque le curseur de la souris quitte la cellule (si l'option n'est pas active Afficher toutes les notes), ce commentaire s'affiche à chaque fois qu'une cellule est sélectionnée.

AVEC.Affiche un message d'erreur détaillé.

Après avoir entré une valeur incorrecte Vérification des données peut afficher un message détaillé sur ce qui a été mal fait. C'est une sorte d'analogue Boîte de message() depuis VBA.

D.Création de plages liées (listes)

E. Utiliser des références à d'autres feuilles dans les règles

Dans EXCEL 2007 Vérification des données, comme dans, vous ne pouvez pas spécifier directement un lien vers les plages d'une autre feuille, par exemple, comme ceci =Sheet2!$A$1 . Vous permet de contourner cette limitation en utilisant .

Si dans Vérification des données vous devez faire, par exemple, une référence à une cellule A1 une autre feuille, vous devez d'abord définir cette cellule, puis la référencer Nom dans la règle Vérification des données.

Dans Excel 2010, en revanche, vous pouvez utiliser des règles de validation des données qui font référence à des valeurs dans d'autres feuilles. Dans Excel 2007 et Excel 97-2003, ce type de validation des données n'est pas pris en charge et n'apparaît pas sur la feuille de calcul. Toutefois, toutes les règles de validation des données restent disponibles dans le classeur et sont appliquées lorsque vous rouvrez le classeur dans Excel 2010, sauf si elles ont été modifiées dans Excel 2007 ou Excel 97-2003.

F. Comment fonctionne la vérification des antécédents

Vérification des données explicitement déclenché lors de la saisie de valeurs dans une cellule à partir du clavier puis en appuyant sur une touche ENTRER. Dans ce cas, une fenêtre décrivant l'erreur apparaît.

Si des valeurs sont insérées via Presse-papiers (Coller les valeurs) ou en utilisant un raccourci clavier CTRL+ D(en copiant la valeur de la cellule d'en haut) ou sont copiés de haut en bas, alors la vérification n'est pas effectuée explicitement. De plus, lors de la copie de valeurs, vous pouvez accidentellement supprimer complètement les règles. Vérifications des données, par exemple, si la cellule source n'est pas définie Vérification des données, et les données de celui-ci sont insérées via Presse-papiers et en utilisant une combinaison de touches CTRL+V.

Expliquons avec un exemple. Supposons que la cellule A1 appliqué Vérification des données sous réserve de vérification Un autre, où =ROW(A1)=1 est saisi dans le champ de formule, c'est-à-dire pour toutes les cellules de la première ligne, la condition Vérifications des données prendra la valeur VRAI, pour les autres lignes - FAUX, quel que soit le contenu de la cellule.

Maintenant, sélectionnons la cellule A2 et appuyez sur CTRL+D. Valeur à partir de A1 sera copié dans A2 avec l'état Vérifications des données. Malgré le fait que maintenant la condition Vérifications des données sera évalué à FALSE et aucun message d’avertissement ne sera affiché. Pour garantir que les données contenues dans les cellules répondent aux conditions définies dans Vérification des données, vous devez appeler la commande de menu Entourez les informations incorrectes (). Les cellules contenant des données incorrectes seront entourées d’ovales rouges. Maintenant, sélectionnons à nouveau la cellule A2 et appuyez sur la touche F2(passez en mode Édition), puis cliquez sur ENTRER- une fenêtre apparaîtra avec un message indiquant que la valeur saisie est incorrecte.

Il existe un autre moyen de contourner la vérification des antécédents. Disons que la saisie dans une cellule est limitée aux valeurs de 1 à 3. Maintenant dans n'importe quelle autre cellule sans Vérifications des données entrez la valeur 4. Sélectionnez cette cellule, dans Barre de formule sélectionnez la valeur 4 et copiez-la dans Presse-papiers. Sélectionnons maintenant la cellule avec Vérification des données et appuyez sur CTRL+V. La valeur a été insérée dans la cellule ! En plus, Vérification des données est resté intact, contrairement au cas où, grâce à Presse-papiers, par exemple, une valeur de WORD est insérée. Pour vous assurer que les données d'une cellule ne remplissent pas les conditions définies dans Vérification des données, vous devez appeler la commande de menu Entourez les informations incorrectes (Données/Travailler avec des données/Valider des données/Encercler les données incorrectes).

G. Recherche de cellules avec validation des données

S'il y a beaucoup de cellules sur la feuille avec Vérification des données, vous pouvez alors utiliser l'outil ( Accueil/ Rechercher et sélectionner/ Sélectionner un groupe de cellules).

Option Vérification des données Cet outil permet de sélectionner les cellules soumises à la validation des données (spécifiées à l'aide de la commande Données/Travail avec les données/Validation des données). Lors de la sélection d'un commutateur Tout le monde toutes ces cellules seront sélectionnées. Lors de la sélection de l'option Ces même Seules les cellules pour lesquelles les mêmes règles de validation des données sont définies que pour la cellule active sont mises en surbrillance.