Comment restreindre et valider les données dans votre modèle financier Excel


Posted on 2017-09-08 11:46:12 category:Logiciel


Une fois que vous avez terminé de créer un modèle financier, vous serez peut-être tenté de le garder pour vous, car vous ne voulez pas que quiconque gâche vos formules ou utilise le modèle de manière inappropriée.Les modèles doivent être collaboratifs, mais vous devez créer votre modèle de manière à ce qu'il soit facile à utiliser pour les autres et difficile à gâcher.Un excellent moyen de rendre votre modèle robuste pour les autres consiste à appliquer des validations et des protections de données au modèle.De cette façon, l'utilisateur ne peut saisir que les données qu'il est censé saisir.

Restreindre la saisie des données utilisateur dans votre modèle financier

Pour un exemple pratique d'utilisation de la validation des données, prenons cette analyse des coûts de projet.

modélisation financière utilisant le référencement absolu
Calcul du taux de personnel journalier en utilisantréférencement absolu.

Votre collègue utilise le modèle que vous avez créé et il peut dire par la façon dont la cellule D3 a été formatée (avec ombrage) que vous vous attendiez à ce que les gens y apportent des modifications.Il ne sait plus combien de jours ce projet va continuer, alors il tape TBA dans la cellule D3 à la place.Dès qu'il tape TBA, ça gâche vraiment les choses!Comme vous pouvez le voir ci-dessous, les formules que vous avez déjà créées attendaient un nombre dans la cellule D3, pas du texte.

texte provoquant une erreur dans le modèle financier
Texte dansune entrée provoquant des erreurs.

Au lieu de permettre à l'utilisateur de mettre quoi que ce soit dans n'importe quelle cellule, vous pouvez modifier les propriétés de cette cellule pour autoriser uniquement la saisie de nombres.Vous pouvez également le modifier pour n'autoriser que les nombres entiers ou les nombres dans une plage donnée.

Suivez ces étapes:

  1. Download File 0601.xlsx et sélectionnez l'onglet étiqueté 6-17.
  2. Sélectionnez la cellule D3.
  3. Accédez à l'onglet Données du ruban et appuyez sur l'icône Validation des données dans la section Outils de données.

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

  4. Dans l'onglet Paramètres, dans la liste déroulante Autoriser, sélectionnez Nombre entier;dans la liste déroulante Données, sélectionnez Supérieur à;et dans le champ Minimum, entrez 0.

Désormais, seuls les nombres entiers supérieurs à zéro peuvent être saisis dans la cellule D3.Essayez de saisir du texte tel que TBA.Essayez de saisir une valeur négative.Excel ne le permet pas et une alerte d'erreur apparaît.

Si vous le souhaitez, vous pouvez saisir un message d'avertissement dans l'onglet Message d'entrée de la boîte de dialogue Validation des données.Par exemple, vous pouvez souhaiter que le message suivant apparaisse: «Attention!Saisissez uniquement des valeurs numériques. »Dans l'onglet Alerte d'erreur, vous pouvez entrer un autre message qui s'affiche si quelqu'un ignore l'avertissement et tente de saisir du texte non valide.

Création de listes déroulantes avec des validations de données dans votre modèle financier

Non seulement l'outil de validation des données empêche les utilisateurs de saisir des données incorrectes dans votre modèle, mais vous pouvez également l'utiliser pour créer des listes déroulantes.Dans la boîte de dialogue Validation des données, dans la liste déroulante Autoriser, sélectionnez Liste.Dans le champ Source, saisissez les valeurs que vous souhaitez voir apparaître dans la liste avec une virgule entre elles, telles que Oui, Non. Une liste déroulante simple est créée dans la cellule B12 avec seulement deux options: Oui et Non. L'utilisateurne peut rien entrer d'autre.

Validation des données dans le modèle financier
Utilisation de la validation des données pour créerune simple liste déroulante.

Personne ne peut entrer une valeur dans une cellule qui va à l'encontre de vos règles de validation des données, mais il est toujours possible de coller sur une cellule qui est restreinte par la validation des données.De cette façon, les utilisateurs peuvent entrer par inadvertance (ou délibérément) des données dans votre modèle que vous n'aviez pas l'intention de faire.

Vous pouvez également créer une liste déroulante qui renvoie aux cellules existantes dans le modèle.Par exemple, ci-dessous, vous ne voulez pas que les utilisateurs incluent une région qui n'est pas incluse dans la liste affichée dans la colonne F. Vous pouvez donc utiliser une liste de validation des données, mais au lieu de taper les valeurs (ce qui serait très-consuming), vous pouvez créer un lien vers la plage contenant déjà les régions - $ F $ 2: $ F $ 5 - ce qui est un moyen beaucoup plus rapide d'insérer une liste déroulante.

validation des données pour créer une liste dynamique dans le modèle financier
Utilisation de la validation des données pour créer une liste déroulante dynamique liée.

Étant donné que vous avez lié la liste déroulante, cette liste déroulante est désormais dynamique.Si quelqu'un modifie l'une des cellules de la plage F2: F5, les options de la liste déroulante changeront automatiquement.

Protéger et verrouiller les cellules dans votre modèle financier

Vous pouvez également ajouter une protection à votre modèle en accédant à l'onglet Révision du ruban et en cliquant sur le bouton Protéger la feuille dans la section Modifications.Entrez un mot de passe si vous en voulez un, puis cliquez sur OK.Cela protégera chaque cellule de la feuille de calcul entière, de sorte que personne ne pourra apporter de modifications du tout

Si vous souhaitez que les utilisateurs puissent modifier certaines cellules, vous devrez désactiver la protection, mettre en surbrillance ces cellules (et uniquement les cellules que vous souhaitez modifier), accéder à la page d'accueilsur le ruban, puis cliquez sur le bouton Format dans la section Cellules.Désélectionnez l'option Verrouiller la cellule qui apparaît dans la liste déroulante.Réactivez la protection et seules les cellules sélectionnées seront déverrouillées.

Gardez à l'esprit qu'il est raisonnablement facile de déchiffrer un mot de passe Excel (recherchez sur Internet un cracker de mot de passe Excel), donc si quelqu'un veut entrer et apporter des modifications à votre modèle protégé, il peut.Je vous recommande de traiter les mots de passe Excel comme un moyen de dissuasion et non comme une solution de sécurité définitive.

À propos de l'auteur du livre

Danielle Stein Fairhurst est la principale modélisatrice financière de Plum Solutions avec de nombreuses années d'expérience dans le domaine.Son groupe LinkedIn «Modélisation financière dans Excel» compte plus de 40 000 abonnés.Elle est également l'auteur de plusieurs articles et autres livres ainsi que d'un bulletin d'information sur la modélisation financière.

82238