Analyse des données Excel - Validation des données

La validation des données est un outil très utile et facile à utiliser dans Excel avec lequel vous pouvez définir des validations de données sur les données saisies dans votre feuille de travail.

Pour n'importe quelle cellule de la feuille de calcul, vous pouvez

Afficher un message d'entrée sur ce qui doit y être entré.
Limitez les valeurs saisies.
Fournissez une liste de valeurs parmi lesquelles choisir.
Afficher un message d'erreur et rejeter une entrée de données non valide.

Considérez le Risk Tracker suivant qui peut être utilisé pour saisir et suivre les informations sur les risques identifiés.

Dans ce tracker, les données saisies dans les colonnes suivantes sont validées avec des contraintes de données prédéfinies et les données saisies ne sont acceptées que lorsqu'elles répondent aux critères de validation. Sinon, vous obtiendrez un message d'erreur.

Probability
Impact
Catégorie de risque
Source de risque
Status

La colonne Exposition au risque aura des valeurs calculées et vous ne pouvez pas saisir de données. Même la colonneS. No. est défini pour avoir des valeurs calculées qui sont ajustées même si vous supprimez une ligne.

Vous allez maintenant apprendre à configurer une telle feuille de calcul.

Préparer la structure pour la feuille de travail

Pour préparer la structure de la feuille de calcul -

Commencez avec une feuille de calcul vierge.
Mettez l'en-tête dans la rangée 2.
Mettez les en-têtes de colonne dans la ligne 3.
Pour les en-têtes de colonne Probabilité, impact et exposition au risque -
- Faites un clic droit sur la cellule.
- Cliquez sur Formater les cellules dans la liste déroulante.
- Dans la boîte de dialogue Format des cellules, cliquez sur l'onglet Alignement.
- Tapez 90 sous Orientation.
Fusionner et centrer les cellules des lignes 3, 4 et 5 pour chacun des en-têtes de colonne.
Mettre en forme les bordures pour les cellules des lignes 2 à 5.
Ajustez les largeurs de ligne et de colonne.

Votre feuille de calcul ressemblera à ceci -

Définir des valeurs valides pour la catégorie de risque

Dans les cellules M5 - M13, entrez les valeurs suivantes (M5 est le cap et M6 - M13 sont les valeurs)

Valeurs de catégorie
Les utilisateurs finaux
Client
La gestion
Programme
Programme
Environnement
Produit
Projet

Cliquez sur la première cellule sous la colonne Catégorie de risque (H6).
Cliquez sur l'onglet DONNÉES sur le ruban.
Cliquez sur Validation des données dans le groupe Outils de données.
Sélectionnez Validation des données… dans la liste déroulante.

La boîte de dialogue Validation des données s'affiche.

Cliquez sur l'onglet Paramètres.
Sous Critères de validation, dans le Allow: liste déroulante, sélectionnez l'option List.

Sélectionnez la plage M6: M13 dans la zone Source: qui apparaît.
Cochez les cases Ignorer les espaces vides et la liste déroulante Dans la cellule qui apparaissent.

Définir le message d'entrée pour la catégorie de risque

Cliquez sur l'onglet Message d'entrée dans la boîte de dialogue Validation des données.
Cochez la case Show input message lorsque la cellule est sélectionnée.
Dans la case sous Titre:, tapez Catégorie de risque:
Dans la zone sous Message d'entrée: choisissez la catégorie du risque dans la liste.

Définir une alerte d'erreur pour la catégorie de risque

Pour définir une alerte d'erreur -

Cliquez sur l'onglet Alerte d'erreur dans la boîte de dialogue Validation des données.
Cochez la case Afficher l'alerte d'erreur après la saisie de données non valides.
Sélectionnez Arrêter sous Style: liste déroulante
Dans la zone sous Titre:, tapez Entrée non valide:
Dans la zone sous Message d'erreur: tapez Choisissez une valeur dans la liste déroulante.
Cliquez sur OK.

Vérifier la validation des données pour la catégorie de risque

Pour la première cellule sélectionnée sous Catégorie de risque,

Les critères de validation des données sont définis
Le message d'entrée est défini
L'alerte d'erreur est définie

Maintenant, vous pouvez vérifier vos paramètres.

Cliquez dans la cellule pour laquelle vous avez défini les critères de validation des données. Le message d'entrée apparaît. Le bouton déroulant apparaît sur le côté droit de la cellule.

Le message d'entrée est correctement affiché.

Cliquez sur le bouton déroulant sur le côté droit de la cellule. La liste déroulante apparaît avec les valeurs qui peuvent être sélectionnées.
Vérifiez les valeurs de la liste déroulante avec celles qui sont utilisées pour créer la liste déroulante.

Les deux ensembles de valeurs correspondent. Notez que si le nombre de valeurs est supérieur, vous obtiendrez une barre de défilement sur le côté droit de la liste déroulante.

Sélectionnez une valeur dans la liste déroulante. Il apparaît dans la cellule.

Vous pouvez voir que la sélection de valeurs valides fonctionne correctement.

Enfin, essayez de saisir une entrée non valide et vérifiez l'alerte d'erreur.

Tapez Personnes dans la cellule et appuyez sur Entrée. Le message d'erreur que vous avez défini pour la cellule s'affiche.

Vérifiez le message d'erreur.
Vous avez la possibilité de réessayer ou d'annuler. Vérifiez les deux options.

Vous avez correctement défini la validation des données pour la cellule.

Note - Il est très important de vérifier l'orthographe et la grammaire de vos messages.

Définir des critères valides pour la colonne de catégorie de risque

Vous êtes maintenant prêt à appliquer les critères de validation des données à toutes les cellules de la colonne Catégorie de risque.

À ce stade, vous devez vous rappeler deux choses -

Vous devez définir les critères du nombre maximal de cellules pouvant être utilisées. Dans notre exemple, il peut varier de 10 à 100 en fonction de l'endroit où la feuille de calcul sera utilisée.
Vous ne devez pas définir les critères pour la plage de cellules indésirable ou pour la colonne entière. Cela augmentera inutilement la taille du fichier. C'est ce qu'on appelle un formatage excessif. Si vous obtenez une feuille de calcul d'une source extérieure, vous devez supprimer le formatage excessif, que vous apprendrez dans le chapitre sur Inquire de ce didacticiel.

Suivez les étapes ci-dessous -

Définissez les critères de validation pour 10 cellules sous Catégorie de risque.
Vous pouvez facilement le faire en cliquant sur le coin inférieur droit de la première cellule.
Maintenez le symbole + qui apparaît et tirez-le vers le bas.

La validation des données est définie pour toutes les cellules sélectionnées.

Cliquez sur la dernière colonne sélectionnée et vérifiez.

La validation des données pour la colonne Catégorie de risque est terminée.

Définir les valeurs de validation pour la source de risque

Dans ce cas, nous n'avons que deux valeurs - interne et externe.

Cliquez dans la première cellule sous la colonne Source de risque (I6)
Cliquez sur l'onglet DONNÉES sur le ruban
Cliquez sur Validation des données dans le groupe Outils de données
Sélectionnez Validation des données… dans la liste déroulante.

La boîte de dialogue de validation des données s'affiche.

Cliquez sur l'onglet Paramètres.
Sous Critères de validation, dans la liste déroulante Autoriser:, sélectionnez l'option Liste.
Tapez Internal, External dans la zone Source: qui apparaît.
Cochez les cases Ignorer les espaces vides et la liste déroulante Dans la cellule qui apparaissent.

Définir le message d'entrée pour la source de risque.

Définir l'alerte d'erreur pour la source de risque.

Pour la première cellule sélectionnée sous Source de risque -

Les critères de validation des données sont définis
Le message d'entrée est défini
L'alerte d'erreur est définie

Maintenant, vous pouvez vérifier vos paramètres.

Cliquez dans la cellule pour laquelle vous avez défini les critères de validation des données. Un message d'entrée apparaît. Le bouton déroulant apparaît sur le côté droit de la cellule.

Le message d'entrée s'affiche correctement.

Cliquez sur le bouton fléché de la liste déroulante sur le côté droit de la cellule. Une liste déroulante apparaît avec les valeurs qui peuvent être sélectionnées.
Vérifiez si les valeurs sont les mêmes que celles que vous avez saisies - Interne et Externe.

Les deux ensembles de valeurs correspondent. Sélectionnez une valeur dans la liste déroulante. Il apparaît dans la cellule.

Vous pouvez voir que la sélection de valeurs valides fonctionne correctement. Enfin, essayez de saisir une entrée non valide et vérifiez l'alerte d'erreur.

Tapez Financial dans la cellule et appuyez sur Entrée. Le message d'erreur que vous avez défini pour la cellule s'affiche.

Vérifiez le message d'erreur. Vous avez correctement défini la validation des données pour la cellule.
Définir des critères valides pour la colonne Source de risque
Appliquez les critères de validation des données aux cellules I6 - I15 de la colonne Source de risque (c'est-à-dire la même plage que celle de la colonne Catégorie de risque).

La validation des données est définie pour toutes les cellules sélectionnées. La validation des données pour la colonne Source de risque est terminée.

Définir les valeurs de validation pour le statut

Répétez les mêmes étapes que vous avez utilisées pour définir les valeurs de validation pour la source de risque.
Définissez les valeurs de la liste sur Ouvert, Fermé.
Appliquez les critères de validation des données aux cellules K6 - K15 de la colonne Statut (c'est-à-dire la même plage que celle de la colonne Catégorie de risque).

La validation des données est définie pour toutes les cellules sélectionnées. La validation des données pour l'état de la colonne est terminée.

Définir les valeurs de validation pour la probabilité

Les valeurs du score de probabilité de risque sont comprises entre 1 et 5, 1 étant faible et 5 étant élevé. La valeur peut être n'importe quel entier compris entre 1 et 5, tous deux inclus.

Cliquez dans la première cellule sous la colonne Source de risque (I6).
Cliquez sur l'onglet DONNÉES sur le ruban.
Cliquez sur Validation des données dans le groupe Outils de données.
Sélectionnez Validation des données… dans la liste déroulante.

La boîte de dialogue Validation des données s'affiche.

Cliquez sur l'onglet Paramètres.
Sous Critères de validation, dans la liste déroulante Autoriser:, sélectionnez Nombre entier.

Sélectionnez entre sous Données:
Tapez 1 dans la case sous Minimum:
Tapez 5 dans la case sous Maximum:

Définir le message d'entrée pour la probabilité

Définissez l'alerte d'erreur pour la probabilité et cliquez sur OK.

Pour la première cellule sélectionnée sous Probabilité,

Les critères de validation des données sont définis.
Le message d'entrée est défini.
L'alerte d'erreur est définie.

Maintenant, vous pouvez vérifier vos paramètres.

Cliquez sur la cellule pour laquelle vous avez défini les critères de validation des données. Un message d'entrée apparaît. Dans ce cas, il n'y aura pas de bouton déroulant car les valeurs d'entrée sont définies pour être dans une plage et non dans une liste.

Le message d'entrée est correctement affiché.

Entrez un entier compris entre 1 et 5 dans la cellule. Il apparaît dans la cellule.

La sélection de valeurs valides fonctionne correctement. Enfin, essayez de saisir une entrée non valide et vérifiez l'alerte d'erreur.

Tapez 6 dans la cellule et appuyez sur Entrée. Le message d'erreur que vous avez défini pour la cellule s'affiche.

Vous avez correctement défini la validation des données pour la cellule.

Définissez des critères valides pour la colonne Probabilité.
Appliquez les critères de validation des données aux cellules E6 - E15 de la colonne Probabilité (c'est-à-dire la même plage que celle de la colonne Catégorie de risque).

La validation des données est définie pour toutes les cellules sélectionnées. La validation des données pour la colonne Probabilité est terminée.

Définir les valeurs de validation pour l'impact

Pour définir les valeurs de validation pour Impact, répétez les mêmes étapes que celles utilisées pour définir les valeurs de validation pour la probabilité.

Appliquez les critères de validation des données aux cellules F6 - F15 de la colonne Impact (c'est-à-dire la même plage que celle de la colonne Catégorie de risque).

La validation des données est définie pour toutes les cellules sélectionnées. La validation des données pour la colonne Impact est terminée.

Définir l'exposition au risque de colonne avec des valeurs calculées

L'exposition au risque est calculée comme un produit de la probabilité et de l'impact du risque.

Exposition au risque = Probabilité * Impact

Tapez = E6 * F6 dans la cellule G6 et appuyez sur Entrée.

0 sera affiché dans la cellule G6 car E6 et F6 sont vides.

Copiez la formule dans les cellules G6 - G15. 0 sera affiché dans les cellules G6 - G15.

La colonne Exposition au risque étant destinée aux valeurs calculées, vous ne devez pas autoriser la saisie de données dans cette colonne.

Sélectionnez les cellules G6-G15
Cliquez avec le bouton droit de la souris et dans la liste déroulante qui apparaît, sélectionnez Formater les cellules. La boîte de dialogue Format de cellule s'affiche.
Cliquez sur l'onglet Protection.
Cochez l'option Locked.

Cela permet de garantir que la saisie de données n'est pas autorisée dans ces cellules. Toutefois, cela n'entrera en vigueur que lorsque la feuille de calcul est protégée, ce que vous ferez lors de la dernière étape une fois que la feuille de calcul est prête.

Cliquez sur OK.
Ombrez les cellules G6-G15 pour indiquer qu'il s'agit de valeurs calculées.

Format des valeurs de numéro de série

Vous pouvez laisser à l'utilisateur le soin de remplir la colonne S. No.. Toutefois, si vous mettez en forme les valeurs S. No., la feuille de calcul semble plus présentable. En outre, il indique le nombre de lignes de la feuille de calcul.

Tapez = row () - 5 dans la cellule B6 et appuyez sur Entrée.

1 apparaîtra dans la cellule B6. Copiez la formule dans les cellules B6-B15. Les valeurs 1 à 10 apparaissent.

Ombrez les cellules B6-B15.

Emballer

Vous avez presque terminé votre projet.

Masquez la colonne M qui contient les valeurs de catégorie de données.
Bordures de format pour les cellules B6-K16.

Cliquez avec le bouton droit sur l'onglet de la feuille de calcul.
Sélectionnez Protéger la feuille dans le menu.

La boîte de dialogue Protéger la feuille apparaît.

Cochez l'option Protéger la feuille de calcul et le contenu des cellules verrouillées.
Tapez un mot de passe sous Mot de passe pour déprotéger la feuille -
- Mot de passe est sensible à la casse
- La feuille protégée ne peut pas être récupérée si le mot de passe est oublié
- Il est recommandé de conserver une liste des noms de feuilles de calcul et des mots de passe quelque part
Sous Autoriser tous les utilisateurs de cette feuille de calcul à: cochez la case Sélectionner les cellules déverrouillées.

Vous avez protégé les cellules verrouillées dans la colonne Exposition au risque de la saisie de données et avez conservé le reste des cellules déverrouillées modifiables. Cliquez sur OK.

le Confirm Password la boîte de dialogue apparaît.

Entrez à nouveau le mot de passe.
Cliquez sur OK.

Votre feuille de calcul avec l'ensemble de validation des données pour les cellules sélectionnées est prête à être utilisée.