Validation des données avec Microsoft Excel

■■ Je saisis les scores des matchs de basket-ball professionnels dans Excel. Je sais qu’une équipe marque de 50 à 200 points par match. Une fois, j’ai entré 1 000 points au lieu de 100 points, ce qui a gâché mon analyse. Existe-t-il un moyen par lequel Excel peut m’empêcher de faire ce type d’erreur?

■■ Je saisis la date et le montant de mes dépenses professionnelles pour une nouvelle année. Au début de l’année, J’entre souvent l’année précédente dans le champ Date par erreur. Puis-je configurer Excel pour m’empêcher de faire ce type d’erreur?

■■ Je saisis une longue liste de chiffres. Excel peut-il m’avertir si j’entre une valeur non numérique?

■■ Mon assistante doit saisir des abréviations d’état lorsqu’elle saisit des dizaines et des dizaines de transactions de vente. Pouvons-nous établir une liste d’abréviations d’état pour minimiser les chances qu’elle saisisse une abréviation incorrecte?

Une grande partie de notre travail implique souvent une saisie de données époustouflante. Lorsque vous entrez beaucoup d’informations

dans Microsoft Excel, il est facile de commettre une erreur. La fonctionnalité de validation des données dans Excel 2013 peut considérablement réduire les chances que vous commettiez une erreur coûteuse. Pour configurer la validation des données, vous commencez par sélectionner la plage de cellules à laquelle vous souhaitez appliquer la validation des données. Choisissez Validation des données dans l’onglet Données puis spécifiez les critères (comme vous le verrez dans les exemples de ce chapitre) qu’Excel utilise pour signaler tout non valide données saisies.

 

Je saisis les scores des matchs de basket-ball professionnels dans Excel. Je sais qu’une équipe marque de 50 à 200 points par match. Une fois, j’ai entré 1 000 points au lieu de 100 points, ce qui a gâché mon analyse. Existe-t-il un moyen par lequel Excel peut m’empêcher de faire ce type d’erreur?

Supposons que vous entriez le nombre de points marqués par l’équipe à domicile dans les cellules A2: A11 et que vous entriez le nombre de points marqués par l’équipe visiteuse dans les cellules B2: B11. (Vous trouverez le travail pour résoudre ce problème dans le fichier .) Vous voulez vous assurer que chaque valeur entrée dans la plage A2: B11 est un nombre entier compris entre 50 et 200.

Commencez par sélectionner la plage A2: B11, puis choisissez Validation des données dans l’onglet Données. Sélectionnez l’onglet Paramètres, sélectionnez Nombre entier dans la liste Autoriser, puis remplissez la boîte de dialogue Validation des données, comme illustré à la figure 1 ci-dessous.

FIGURE 1: Utilisez l’onglet Paramètres de la boîte de dialogue Validation des données pour configurer les critères de validation des données.

La réponse par défaut d’Excel aux données non valides (appelée alerte d’erreur) est un message indiquant : «La valeur entrée n’est pas valide. Un utilisateur a des valeurs restreintes qui peuvent être entrées dans la cellule. » Vous pouvez utiliser l’onglet Alerte d’erreur dans la boîte de dialogue Validation des données (voir la figure ci-dessous) pour modifier la nature de l’alerte d’erreur, y compris l’icône, le titre de la boîte de message et le texte du message lui-même.

Dans l’onglet Message d’entrée, vous pouvez créer une invite ou une alerte qui informe un utilisateur du type de données pouvant être entrées en toute sécurité. Le message s’affiche sous forme d’infobulle dans la cellule sélectionnée. Par exemple, vous pouvez saisir une alerte d’erreur indiquant «Veuillez saisir un nombre entier compris entre 50 et 200». Après avoir tapé un nombre qui viole ce critère, disons 34, dans la cellule E5, vous verrez alors le message illustré à la figure 3.

FIGURE 2 : Cette figure illustre les options de l’onglet Alerte d’erreur dans la boîte de dialogue Validation des données.

FIGURE 3: Alerte d’erreur pour l’exemple de validation des données de basket-ball.

Je saisis la date et le montant de mes dépenses professionnelles pour une nouvelle année. Au début de l’année, j’entre souvent l’année précédente dans le champ de date par erreur. Puis-je configurer Excel pour m’empêcher de faire ce type d’erreur? Supposons que ce soit au début de 2013 et que vous entrez la date dans la plage de cellules A2: A20. . Sélectionnez la plage A2: A20, puis choisissez Validation des données dans l’onglet Données. Remplissez l’onglet Paramètres de la boîte de dialogue Validation des données, comme illustré à la figure ci-dessous. Ensuite, vous ne serez pas autorisé à entrer une date antérieure au 1/1/2013.

FIGURE 4: Utilisez des paramètres tels que ceux-ci pour garantir la validité des dates que vous entrez.

Si vous entrez une date dans cette plage qui se produit avant le 1er janvier 2013, vous serez averti

l’erreur. Par exemple, la saisie du 15/01/2012 dans la cellule A3 fait apparaître l’alerte d’erreur que vous définissez.

Je saisis une longue liste de chiffres. Excel peut-il m’avertir si j’entre une valeur non numérique?

Pour libérer toute la puissance de la validation des données, vous pouvez utiliser les paramètres personnalisés. Lorsque vous sélectionnez Personnalisé dans la liste Autoriser de l’onglet Paramètres de la boîte de dialogue Validation des données (voir Figure 5), vous utilisez une formule pour définir des données valides. Une formule que vous entrez pour la validation des données fonctionne de la même manière qu’une formule utilisée pour la mise en forme conditionnelle, qui est décrite dans l’article 23, «Mise en forme conditionnelle». Vous entrez une formule qui est vraie si et seulement si le contenu de la première cellule de la plage sélectionnée est valide.

Lorsque vous cliquez sur OK dans la boîte de dialogue de validation des données, et La formule est copiée dans la formule de validation des cellules restantes de la plage. Lorsque vous entrez une valeur dans une cellule de la plage sélectionnée, Excel affiche une alerte d’erreur si la formule que vous avez entrée renvoie Faux.

Pour illustrer l’utilisation du paramètre Personnalisé, supposons que vous souhaitiez vous assurer que chaque entrée du B2: la plage de cellules B20 est un nombre.  La clé pour résoudre ce problème est d’utiliser la fonction Excel ESTNUM. La fonction ESTNUM  renvoie Vrai si la fonction fait référence à une cellule qui contient des données numériques. La fonction renvoie False si la fonction fait référence à une cellule qui contient une valeur non numérique. Pour vous assurer que la saisie d’une entrée non numérique dans B2: B20 créera une erreur, procédez comme suit.

Avec le curseur dans la cellule B2, sélectionnez la plage de cellules B2: B20. Cliquez sur l’onglet Données, cliquez sur Validation des données dans le groupe Outils de données, puis remplissez l’onglet Paramètres de la boîte de dialogue Validation des données, comme illustré à la figure 45.

FIGURE 5 : Utilisez la fonction ESTNUM pour vous assurer que les données d’une plage sont numériques.

Après avoir cliqué sur OK, vous recevrez une invite d’erreur si vous essayez d’entrer une valeur non numérique dans B2: B20. Par exemple, si vous tapez John dans la cellule B3, vous recevez une alerte d’erreur.

Si vous cliquez sur Validation des données lorsque vous travaillez dans la cellule B3, la formule illustrée à la figure 5 s’affiche comme = ESTNMONRE (B3). Cela montre que la formule entrée dans la cellule B2 est copiée correctement. Si vous saisissez John dans la cellule B3, = ESTNOMBTRE (B3) renvoie False et vous recevez l’alerte d’erreur.

Mon assistante doit saisir des abréviations d’état lorsqu’elle saisit des dizaines et des dizaines de transactions de vente. Pouvons-nous établir une liste d’abréviations d’état pour minimiser les chances qu’elle saisisse une abréviation incorrecte?

La clé de ce problème de validation des données est d’utiliser les critères de validation de la liste. Commencez par entrer une liste d’abréviations d’état. Voir le fichier Statedv.xlsx. Dans cet exemple, utilisez la plage I6: I55 et nommez la plage en abrégé. Ensuite, sélectionnez la plage dans laquelle vous entrerez les abréviations d’état. (L’exemple utilise D5: D156.) Après avoir cliqué sur Validation des données sous l’onglet Données, remplissez la boîte de dialogue Validation des données comme illustré à la figure ci-dessous.

Désormais, chaque fois que vous sélectionnez une cellule dans la plage D5: D156, un clic sur la flèche déroulante affiche une liste d’abréviations d’état, comme le montre la figure 7. Seules les abréviations qui apparaissent sur la liste sont des valeurs valides dans cette plage. Si vous n’utilisez pas la liste déroulante et saisissez plutôt une abréviation d’état, vous recevrez un message d’erreur si vous entrez une abréviation incorrecte (telle que ALK pour l’Alaska).

FIGURE 6 La boîte de dialogue Validation des données peut être utilisée pour définir une liste de valeurs valides.

FIGURE :7 Cette figure montre une liste déroulante des abréviations d’état.

Remarques

Voici quelques informations supplémentaires que vous trouverez utiles lorsque vous explorerez les capacités d’Excel pour vous aider.

vous résolvez des problèmes et validez des données:

■■ Si vous appuyez sur F5, cliquez sur Spécial dans la boîte de dialogue Atteindre, puis sélectionnez Validation des données, Excel sélectionne toutes les cellules avec des paramètres de validation des données. Vous pouvez également utiliser la boîte de dialogue Atteindre spécial pour sélectionner toutes les cellules contenant la validation des données.

■■ Dans les versions d’Excel antérieures à Excel 2010, si vous vouliez utiliser une liste déroulante de validation des données basée sur une liste de sources de données dans une autre feuille de calcul, vous deviez nommer la liste (comme dans l’exemple de cet article) pour la liste déroulante pour fonctionner. Depuis Excel 2010, cette limitation a été supprimée.

■■ Si vous utilisez la technique de plage dynamique décrite à l’article 21, «La fonction DECALER», les modifications que vous apportez (ajout ou suppression d’éléments) à la liste des sources de données sont automatiquement reflétées dans la liste déroulante. (Voir le problème 10 dans ce chapitre.) De plus, si vous nommez les données liste source sous forme de tableau Excel (voir Article , «Tables»), les modifications de la source de données sont reflétées dans la liste déroulante tant que vous pointez sur la plage de la liste et n’essayez pas de taper le nom de la table.

■■ Supposons que vous souhaitiez utiliser une liste déroulante pour sélectionner une entreprise à laquelle vous vendez des friandises. Vous voulez une autre liste déroulante que vous pouvez utiliser pour sélectionner la liste des friandises que vous vendez dans le magasin sélectionné. Le problème est que le même ensemble de barres chocolatées peut ne pas être vendu dans chaque magasin. Comment créer une telle sélection de liste imbriquée (souvent appelée sélection en cascade)? Supposons que les magasins soient Target et CVS; supposons que vous affectiez un nom de plage de Target à la liste des barres de chocolat vendues chez Target et un nom de plage de CVS à la liste des barres de chocolat vendus à CVS. Si la liste déroulante pour la sélection de magasin est, par exemple, A20, vous pouvez créer la liste déroulante appropriée dans la cellule B20 en cliquant sur Validation des données et en remplissant la formule = INDIRECT (A20) pour la liste sélectionnée. Comme indiqué dans l’article 22, «La fonction INDIRECTE», si A20 contient CVS, la liste désactivera la plage CVS qui contient toutes les barres chocolatées vendues chez CVS, etc. Voir le problème 11 dans ce chapitre.

■■ Pour effacer la validation des données d’une plage, sélectionnez la plage, choisissez Validation des données sur les données onglet, puis sélectionner supprimer tout.

■■ Si vous avez une longue liste, vous souhaiterez probablement invoquer la fonction de saisie semi-automatique Excel. Si vous démarrez la plage de votre liste déroulante dans la cellule sous la fin de la liste (aucune cellule vide autorisée), la saisie semi-automatique fonctionnera. Voir le fichier .

■■ Vous pouvez également utiliser la validation des données pour définir des critères basés sur la longueur du texte dans une cellule (voir Problème 4 dans cet article) ou l’heure de la journée (voir Problème 15, également dans cet article).

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Fonctions Excel

Macro VBA Utiles

Excel Pratique

Programmation VBA

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x
()
x