![]() |
![]() | |
|
Excel: Outils d'analyseTable de données ![]() ![]() Gestionnaire de scénarios IntroductionIl arrive que des erreurs s'infiltrent dans les formules et cause des problèmes au modèle. Excel vous offre l'outil Audit pour vérifier les formules et pour déterminer les dépendances entre les formules. Vous pouvez ainsi trouver les problèmes et les régler. Après avoir conçu votre modèle décisionnel, vous voulez qu'Excel vous aide à trouver la solution optimale selon certaines conditions. Le logiciel possède plusieurs outils pour vous aider. Cette page explique le fonctionnement de cinq de ces outils : l'audit, la valeur cible, la table de données, le gestionnaire de scénarios et le solveur. Chacun répond à vos besoins dans différentes situations. AuditExcel offre l'outil Audit pour repérer facilement les dépendances entre les cellules et de déterminer la cause d'un problème dans le modèle. Cette partie vous démontre comment utiliser les options de la barre d'outils Audit. |
|
Excel Opérations de base Présentation Mise en page et Impression Fonctions Fonctions matricielles Copier, coller Graphiques Macro commandes Outils d'analyse Commentaires Liste de données Tableau croisé dynamique Exercices Quiz Thèmes Graphique: Insérer un second axe Graphique: créer un graphique demi-lune Graphique: Créer une pyramide d'âge Gestionnaire de scénarios Mise en forme conditionnelle Remplissage instantané et analyse rapide Solveur Somme automatique Sous-total Tableau croisé dynamique Table de données (1 et 2 variables) Valeur cible Validation - Liste de choix | ||
Word 2003 | ||
![]() | ||
Précédent Suivant Page Principale FAQ Nouveautés Plan du site Références Motivations Manuels de formation Fichiers de démonstration Droits d'auteur Text in English | ||
![]() |
||
Par courriel Abonnez-vous à l'infolettre LCI |
||
Ce modèle est le résultat de l'exercice d'Excel qui se retrouve dans cette section. À part des valeurs initiales pour les produits et charges et le taux de croissance, tous les autres chiffres sont des formules. Ce modèle sera utilisé pour démontrer les possibilités d'Audit. Repérer les antécédentsLa première chose qu'on veut généralement savoir est quelles sont les cellules à la source du résultat qui se retrouve dans cette cellule. Pour cela, il faut voir les cellules antécédentes.
L'option vous montre les cellules qui sont utilisées pour déterminer le chiffre qui se retrouve dans la cellule. La formule de la cellule D19 est bien =D18+C19 . C'est le cumulatif du mois précédant plus le profit net du mois. La barre d'outils AuditLes options sous le menu Audit vous permettent plusieurs possibilités pour voir les dépendances entre les cellules. Mais il est parfois préférable d'avoir toutes les options dans une barre d'outils. Contrairement à toutes les autres barres d'outils, celle-ci ne se retrouve pas sous le menu Affichage mais bien sous le menu Outils et Audit. Cette omission a été corrigée avec Excel XP (2002).
La barre d'outils Audit va apparaître. Voici une brève description des boutons de celle-ci.
La prochaine partie consiste à essayer chacun des boutons de la barre d'outils Audit.
Ceci démontre que vous pouvez allez chercher les antécédents de plusieurs cellules. Aussi, vous pouvez utiliser autant le menu Outils et Audit ou directement la barre d'outils Audit pour accomplir vos recherches. Pour alléger la lecture de cette partie de la page, seulement la barre d'outils sera mentionnée pour les prochaines commandes. Mais vous pouvez aussi choisir l'option Audit sous le menu Outils pour réaliser les mêmes options.
Repérer les dépendantsAudit vous permet de voir quelles sont les cellules qui sont à la source du résultat dans la cellule mais aussi les cellules qui dépendent de cette même cellule pour leurs propres besoins.
La flèche indique que la cellule D7, qui est le total des revenus du mois, dépend des chiffres des cellules D3 à D5.
La seconde flèche indique que la cellule D16, qui calcule le profit brut pour le mois dépend en partie de la cellule D7 pour avoir le bon résultat. Ce petit exercice démontre aussi qu'il est possible d'avoir plusieurs flèches de dépendances en même temps sur une feuille de calcul.
Repérer une erreurIl arrive par moments que des erreurs s'infiltrent dans le modèle. Cela peut être une erreur de frappe ou de logique. Mais il peut être difficile de retrouver la source du problème. Le prochain exercice va vous démontrer l'avantage de l'option "Repérer une erreur" dans Audit.
Bien sûr, cela affecte plusieurs cellules qui dépendent du chiffre qui se retrouve dans cette cellule incluant le cumulatif trimestriel à la cellule D19.
Il faut maintenant trouver la cause de l'erreur.
La fonction retrace toutes les cellules affectées jusqu'à la source. Dans ce cas, le problème commence à la cellule C3. La formule de cette cellule dépend des cellules B21 et B3. Il est donc facile de vérifier ces deux cellules pour voir que le problème est à la cellule B3. Une façon de s'assurer que le contenu d'une cellule est un chiffre et non du texte est de prendre une cellule vide, de commencer une formule qui ajoute un à la cellule que suspectez être la source du problème. Pour cet exemple, la formule serait =B3+1. Il est évident que cela donnera une erreur.
Le problème a été réglé. Vous pouvez donc retirer les flèches de référence.
Données non validesExcel vous offre la possibilité de mettre des bornes à des cellules. Ceci est pour vous assurer que les valeurs dans les cellules soient possibles selon votre modèle. Mais cela est surtout pratique lors de l'entrée de données. Mais la validation peut aussi être pratique pour des cellules ayant des formules. Pour cet exemple, prenons l'hypothèse que le cumulatif des revenus pour le mois de janvier ne peut pas être au-dessus de 300 000 $. En ce moment, il n'y a aucune manière de vérifier cette limitation. Il faut premièrement mettre une validation pour ensuite s'assurer qu'elle est respectée grâce à Audit.
La validation est placée. Il faut maintenant vérifier qu'elle fonctionne correctement.
Rien n'apparaît dans le modèle. Ceci est normal puisque la valeur de la cellule B7 est en dessous du maximum de la validation.
La cellule B7 est maintenant encerclée en rouge. Ceci démontre que la valeur dans la cellule ne respecte pas la validation que vous avez placée auparavant. Cette option avec la validation peut être un outil puissant pour s'assurer que le modèle montre des valeurs qui sont réalisables et non irréalistes.
Éléments supplémentaires dans ExcelLes concepteurs d'Excel ont ajouté des éléments intéressants sur cette barre d'outils à la version XP (2002). Vous pouvez suivre les valeurs de cellules en tout temps en les "espionnant " et aussi vérifier des formules. La barre d'outils Audit est aussi maintenant disponible au même endroit que les autres barres sous le menu Affichage. Espionner une celluleVous pouvez suivre les changements des cellules importantes de votre modèle en affichant les valeurs dans une barre d'outils appelée "Volet espions". Mais il faut placer un espion sur les cellules qui vous intéressent.
Cette option est intéressante parce qu'elle vous permet de suivre les valeurs des cellules importantes de votre modèle même si elles ne sont pas présentement à l'écran.
Évaluation de formulesLa barre d'outils Audit vous permet aussi de voir les résultats intermédiaires d'une formule composée du contenu de plusieurs cellules. Cela peut vous montrer l'endroit où il y a un problème dans votre formule.
Appuyez plusieurs fois sur chacun des boutons au bas de la fenêtre pour voir le potentiel de cette nouvelle option.
La table de données ( 1 ou 2 variables )
Excel vous offre plusieurs outils pour analyser les résultats de votre modèle. Voici l'une des plus veilles techniques: la table de données. Cet outil vous permet de créer un tableau qui affiche le résultat de certaines cellules si on change le contenu d'une ou de deux variables. Au lieu d'essayer manuellement des "qu'arrivera-t-il à telle cellule si" avec plusieurs valeurs, le résultat est affiché rapidement sous forme d'un tableau. Les prochains exercices consistent à créer des tables de données à une et deux variables pour les paiements hypothécaires d'une maison. Avant de pouvoir créer une table, il faut un modèle complet. Pour ce cas, il faut entrer les données et les formules nécessaires pour calculer les paiements. L'avant dernière ligne est la formule pour calculer les paiements selon le taux d'intérêt, le nombre de paiements par année et le montant total à payer. La dernière ligne du calcul indique la somme totale, capital et intérêt, que vous devrez payer avant d'être le " vrai " propriétaire de la maison. C'est probablement un peu plus que vous imaginiez.
Table de données à une variableAvant de pouvoir utiliser une table de données, vous devez avoir un modèle qui fonctionne correctement. Sinon, les résultats de la table ne valent rien.
Pour créer une table de données, il y a toujours deux informations qui sont nécessaires. Il y a les cellules dont vous voulez voir les résultats et les valeurs que vous voulez changer par rapport aux valeurs actuelles. Ces informations sont toujours affichées sur la première ligne et la première colonne du tableau. Il est aussi important de ne pas mélanger les valeurs et les cellules. L'une est placée horizontalement et l'autre verticalement. Pour une table à une variable, il n'y a pas d'importance si les valeurs à essayer et les cellules dont vous voulez voir le résultat sont sur une ligne ou une colonne. Selon les données que vous avez entrées de l'image précédente, les valeurs à essayer sont sur la première colonne et les cellules à voir le résultat sont sur la première ligne. La table de données va afficher l'effet sur les paiements et le total, les cellules de la première ligne, selon les changements du taux d'intérêt de la première colonne. Puisqu'on veut savoir dans ce tableau ce qui arrive au paiement et au total lorsque le taux d'intérêt change, il faut aussi savoir dans quelle cellule se retrouve le taux d'intérêt. Dans cet exercice, il s'agit de la cellule B2. Ensuite, il suffit à indiquer à Excel où l'on veut que la table remplace le contenu de la première ligne, de la première colonne ou des deux. Puisqu'il s'agit d'une table de données à une variable, seulement l'une deux cases suivantes aura une adresse de cellule.
Excel a calculé ce qui arriverait au paiement et au total si l'on change le taux d'intérêt de 6,5% à 8,5%.
La table s'ajuste immédiatement pour afficher le nouveau résultat. Il faudra cependant refaire la table si vous voulez ajouter d'autres taux d'intérêt ou d'autres cellules à comparer. La table de données à deux variablesLe dernier exercice créait une table de données à une variable. Vous avez pu voir le résultat aux paiements lorsque le taux d'intérêt est changé. Le prochain exercice consiste à développer une nouvelle table de données ayant deux variables. La prochaine table va afficher ce qui arrive au total des paiements si on augmente le nombre de paiements par année selon le taux d'intérêt.
La première ligne représente le nombre de paiements par année. Vous retrouvez cette valeur dans la cellule B4 du modèle précédent. Comme pour la table à une variable, la première colonne démontre les différents taux d'intérêt. La valeur est dans la cellule B2. À l'intersection de la première ligne et de la première colonne se retrouve l'adresse de la cellule dont vous voulez voir le résultat. Pour cet exercice, il s'agit du total des paiements qui est normalement dans la cellule B6. La structure d'une table à deux variables permet de regarder ce qui arrive à une cellule à la fois.
Comme le tableau l'indique, il y a une petite baisse si vous payez plus souvent durant l'année. Les intérêts s'accumulent sur un plus petit montant. Ce qui est plus important à remarquer est la différence du total selon le taux d'intérêt. Les sommes d'argent peuvent devenir importantes avec le temps. Tout cela pour dire à quel point il est important de se battre pour le taux d'intérêt le plus bas possible. Bien qu'il soit très intéressant à savoir ce qui arrive à certaines cellules grâce aux tables de données, cette technique a aussi des désavantages. En utilisant une table de données à deux variables, vous pouvez seulement voir le résultat d'une cellule à la fois. Dans ce dernier cas, vous pouvez toujours changer l'adresse de la cellule à comparer par une autre. Par exemple, changez le contenu de la cellule A16 par =B5. Vous pouvez voir ce qui arrive aux paiements et au total mais pas les deux en même temps. Pour contourner ce problème, il faut créer une autre table. Une autre limitation plus importante est que vous pouvez faire des essais avec un maximum de deux variables. Le gestionnaire de scénarios ouvre d'autres possibilités pour comparer des situations. Gestionnaire de scénarios
Que faire pour comparer plusieurs possibilités? Vous avez monté un modèle d'une compagnie ou d'un système. On vous offre ensuite plusieurs possibilités. Comment savoir quelle est la meilleure? Avec le gestionnaire de scénarios, vous pouvez entrer ces différentes possibilités dans des "scénarios". Une fois fait, le gestionnaire de scénarios peut vous montrer le résultat de cellules importantes tel que le profit brut, les revenus, les charges, des ratios ... Bref, n'importe quelle cellule que vous considérez importante. Mais, avant d'utiliser le gestionnaire de scénarios, vous devez avoir un modèle qui fonctionne correctement. Assurez-vous de valider votre modèle avec plusieurs tests. Changez des valeurs des cellules variables et vérifiez les résultats. Il arrive très souvent d'avoir oublié quelque chose dans un modèle; que ce soit un chiffre ou une formule. C'est pour cette raison qu'il faut absolument vérifier le modèle. Si le modèle donne de mauvais résultats, le gestionnaire de scénarios en donnera aussi. L'une des limitations du gestionnaire de scénarios est que toutes les cellules variables et toutes les cellules résultantes doivent être sur la même feuille de calcul. Certains de vos calculs peuvent être sur une autre feuille de calcul; mais pas les cellules variables ou les cellules dont vous voulez voir le résultat. L'objectif de cet exercice est de connaître le résultat de la cellule A3 selon un scénario optimiste ou pessimiste. Donc, il faut créer deux scénarios: l'un nommé "optimiste" et l'autre nommé "pessimiste". Par après, vous pourrez créer vos propres scénarios pour répondre à vos besoins. Vous n'êtes pas obligé de les appeler "optimiste" ou "pessimiste". De plus, vous n'êtes pas limité à deux scénarios. Vous pouvez avoir plusieurs scénarios pour comparer les suppositions de Pierre, Jean et Jacques. En plus, vous pourrez aussi fusionner ces scénarios si nécessaires. Pour comprendre le gestionnaire de scénarios, prenons l'exemple simplifié suivant.
L'écran suivant va apparaître. C'est de cette fenêtre que vous allez créer deux scénarios: un optimiste et l'autre pessimiste. Si vous faites une erreur, ne vous inquiétez pas. Vous pourrez toujours appuyer sur le bouton modifier par après pour apporter vos corrections.
Vous pouvez écrire les adresses ou, en utilisant le bouton gauche de la souris et la touche Ctrl, cliquez sur les cellules nécessaires. Les cellules variables sont celles dont vous voulez changer le montant. Elles ne changeront pas les valeurs de la feuille de calcul. Mais ces cellules seront utilisées seulement pour le scénario.
Excel va ensuite vous demander quels sont les montants pour les cellules variables.
Il faut ajouter le scénario pessimiste.
Il ne reste qu'à voir quel est le résultat dans la cellule A3. Si vous avez fait une erreur dans l'un ou l'autre des scénarios, c'est maintenant le temps de la changer. Cliquez sur le nom du scénario et appuyez sur le bouton modifier. Par après, continuez avec le reste de la procédure.
Excel vous offre deux genres de rapports: Synthèse de scénarios et Tableau croisé dynamique. La synthèse de scénarios génère une nouvelle feuille de calcul avec la liste des cellules variables et des cellules dont vous voulez voir le résultat. Une fois que la synthèse est générée, elle ne changera pas si vous apportez des changements au modèle ou aux scénarios. Il faudra régénérer une nouvelle synthèse avec les nouveaux résultats. Il sera possible de comparer les différentes synthèses selon vos changements. Le tableau croisé dynamique vous permet aussi de comparer le résultat de plusieurs variables. Mais le tableau dynamique vous permet de changer la présentation et d'accomplir des analyses plus approfondies. Pour plus d'informations sur le tableau croisé dynamique, cliquez sur ce lien.
Excel va ensuite vous demander quelles sont les cellules dont vous voulez voir le résultat. Vous pouvez choisir plusieurs cellules. Pour les sélectionner, vous pouvez écrire les adresses des cellules ou, en gardant un doigt sur la touche Ctrl, cliquez sur les cellules de votre choix. Le gestionnaire de scénario vous limite à un maximum de 32 cellules dont vous pouvez voir le résultat dans la synthèse.
Excel va prendre quelques instants pour créer une nouvelle feuille de calcul avec les résultats de tous vos scénarios.
Le tableau se sépare en deux parties. La partie du haut inclut l'adresse des cellules ainsi que sa valeur pour chacun des résultats. La partie du bas affiche les résultats selon les scénarios. Chaque scénario que vous avez ajouté se retrouve dans une colonne. Les cellules de couleur grise de la partie du haut veulent dire que vous avez changé la valeur de la cellule par rapport à la valeur en cours de la feuille de calcul. Effacer la feuille de synthèseSi vous voulez effacer la feuille pour réessayer.
Attention! Assurez-vous que la feuille que vous voulez effacer est celle qui est affichée à l'écran. Dans ce cas, vous voulez effacer la feuille de calcul avec les résultats du gestionnaire de scénarios et non celle qui a votre modèle ! La feuille du classeur va ensuite s'effacer. Donner des noms aux cellulesVous avez peut-être remarqué que le nom des cellules apparaît à la gauche de la synthèse ( $A$1, $A$2, $A$3 ). Mais que représente exactement le contenu de ces cellules? Ne serait-il pas plus intéressant d'avoir un nom représentatif pour la cellule? Ceci est un autre avantage de donner des noms aux cellules importantes.
Pour avoir le souligné, appuyez sur les touches MAJ (Shift) et moins ( - ). Excel ne permet pas d'avoir des espaces lorsqu'on donne un nom à une cellule. Une manière de contourner cette limitation est de mettre un souligné entre les mots. Ex.: Revenu_mois_août, bénéfice_annuel.
Pour les besoins de l'exercice il faut aussi donner un nom aux cellules A2 et A3.
Il est maintenant temps de régénérer une nouvelle feuille de synthèse avec les noms des cellules.
Il est beaucoup plus facile de comprendre les résultats lorsque vous avez des noms représentatifs. Mais on peut faire un peu de nettoyage. Il est possible de retirer les soulignés des noms des variables. Il faut suivre les étapes suivantes.
Cette option permet de remplacer des mots ou des caractères par d'autres. Elle est disponible dans toutes les applications Office.
Le texte est maintenant encore plus lisible après un simple remplacement. Le gestionnaire de scénarios est très intéressant pour comparer plusieurs situations. Mais, il faut faire attention à trois choses: le bouton "Afficher", le point pour les décimales et le remplacement d'une formule par un chiffre. Dans le coin supérieur droit de la fenêtre du gestionnaire de scénarios, le bouton "Afficher" vous permet d'afficher les résultats d'un scénario sur le classeur. Cela est intéressant pour voir l'effet du scénario sur tout le modèle. Il faut cependant faire attention à cette option. Si vous continuez de travailler, cela sera avec les valeurs du scénario et non les valeurs originales. Pour revenir aux valeurs initiales:
Un autre problème du gestionnaire de scénario est qu'il ne convertit pas le point du clavier numérique en virgule pour les décimales comme Excel le fait lorsque vous entrez un chiffre dans une cellule. Donc, lorsque vous entrez la valeur d'une variable dans un scénario en utilisant le clavier numérique, celui-ci va entrer 5.5 au lieu de 5,5 . C'est pour cette raison que vous retrouvez dans les résultats de la feuille synthèse #VALEUR! . Si c'est le cas, modifier le scénario en changeant la valeur en plaçant une virgule à la place de la décimale. Il faut aussi faire très attention de ne pas mettre parmi la liste des cellules variables une cellule qui contient une formule. Sinon, le gestionnaire de scénarios va automatiquement remplacer la formule par la valeur affichée. Voici un exemple.
Pour l'exercice, il faut créer un scénario dont la cellule variable est A3.
Excel va vous avertir que l'une des cellules que vous avez sélectionnées, la cellule A 3 pour l'exercice, contient une formule. Il vous avertit qu'il va convertir la formule en une valeur au moment d'afficher le scénario ou aussi lors de la création de la synthèse du scénario. Il vous est encore possible d'éviter la conversion en modifiant votre scénario pour ne pas inclure la cellule.
La valeur n'a pas vraiment d'importance pour l'exemple.
Excel vous affiche la feuille de synthèse du scénario.
Bien que la valeur soit la même, il n'y a plus de formule. Le gestionnaire de scénarios a remplacé la formule par la valeur du moment. Donc, pour éviter cette situation, ne sélectionnez jamais une cellule contenant une formule dans la liste des cellules variables. Excel vous avertit même avant de faire la conversion. Il y a un autre exercice sur le gestionnaire de scénarios sur la page de création de modèles de ce site. Vous pourrez vous pratiquer encore une fois si vous le voulez. Fusion de scénarios Une limitation des scénarios est qu'ils sont seulement disponibles sur la feuille de calcul sur lequel ils ont été créés. Ceci n'est pas avantageux si vous voulez les utiliser ailleurs. Le bouton de fusion de scénarios vous permet de copier tous les scénarios d'une feuille de calcul à une autre ou même à un autre document d'Excel. Cela peut être pratique lorsque plusieurs personnes travaillent sur un même modèle mais développent des scénarios différents. Cette commande permet donc de regrouper plusieurs scénarios pour pouvoir les utiliser et les comparer. En fait, on devrait plutôt l'appeler regroupement de scénarios.
Pour les besoins de l'exercice, il y a déjà les scénarios Optimiste et Pessimiste. Nous allons supposer qu'il existe un autre scénario dans un autre document d'Excel.
La case classeur va afficher le nom de tous les documents Excel, ou classeur selon le terme d'Excel, ouvert à ce moment. Puisqu'il y a seulement deux documents ouverts, Excel présume que les scénarios recherchés sont dans l'autre document. Vous pouvez changer de document à votre choix. La case feuille affiche le nom des feuilles de calcul qui existe dans le classeur. Dans ce cas, il y a seulement une feuille de calcul: Évolution. Au bas de la fenêtre, il est inscrit le nombre de scénarios inclus dans la feuille.
Le scénario qui était sur la feuille de calcul Évolution a été recopié ou "importé". Si cette dernière feuille avait eu plusieurs scénarios, tous auraient été recopiés. Au cas où deux scénarios auraient eu le même nom, Excel va automatiquement changer le nom du scénario importé. |
Abonnez-vous à l'infolettre Abonnez-vous à l'infolettre du site pour recevoir le manuel " 150 fonctions Excel expliquées ". Vous recevrez aussi les prochaines infolettres sur les nouveautés du site et des formations détaillées que vous ne trouverez nulle part ailleurs. |