LeCompagnon.info

Excel

Excel: Outils d'analyse

Introduction
Audit Vidéo sur l'outil Audit et la fenêtre espion

Valeur cible
Exercice disponible pour ce thème
Un exercice sur Valeur cible pour Excel 2007 est aussi disponible.

Table de données
Exercice disponible pour ce thème
Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur les tables de données pour Excel 2007 est aussi disponible.

Gestionnaire de scénarios
Exercice disponible pour ce thème
Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur le gestionnaire de scénarios pour Excel 2007 est aussi disponible.

Le solveur
Exercice disponible pour ce thème Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur le solveur pour Excel 2007 est aussi disponible.

Introduction

Il 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.

Audit

Excel 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
Autres formations

Word 2003
Word 2007
Excel
Excel 2007
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Navigation
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
Contact
Par courriel
Abonnez-vous à
l'infolettre LCI
Partager






Abonnez-vous à l'infolettre LeCompagnon.info

201 fonctions pour les pressés









*Allez chercher et ouvrez le fichier demoxl3.xls situé à la page des Fichiers de démonstration.

Excel: Modèle de croissance selon le contenu de la cellule B21

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édents

La 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.

*Placez le pointeur sur la cellule D19.

Excel: Menu Outils, Audit et Repérer les antécédents

*Du menu Outils, sélectionnez les options Audit et Repérer les antécédents.

Excel: Affiche les antédédents de la cellule D19. Ce sone la formule de la cellule D19 a besoin pour afficher  le cellule q

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 Audit

Les 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).

*Du menu Outils, sélectionnez les options Audit et Afficher la barre d'outils Audit.

Barre d'outils Audit

La barre d'outils Audit va apparaître. Voici une brève description des boutons de celle-ci.

Boutons Description
Repérer les antécédentsSupprimer les flèches des antécédents Affiche ou retire les lignes qui montrent la source des chiffres pour la formule de la cellule.
Repérer les dépendantsSupprimer les flèches des dépendants Affiche ou retire les lignes des cellules qui dépendent du contenu de la cellule
Supprimer toutes les flèches Effacer les lignes de référence entre les cellules.
Repérer une erreur Trouver rapidement la cellule qui cause une erreur dans la formule.
Nouveau commentaire Ajouter un commentaire à une cellule.
Entourer les données non valideseffacer les cercles de validation Affiche ou masque les données non valides selon les validations que vous avez mises auparavant.

La prochaine partie consiste à essayer chacun des boutons de la barre d'outils Audit.

*De la barre d'outils Audit, appuyez sur le bouton Repérer les antécédents.

Excel: Audit qui montre les antécédents deux niveaux en arrière

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.

*Pour retirer toutes les lignes, appuyez sur le boutonSupprimer toutes les flèches.

Repérer les dépendants

Audit 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.

*Placez le pointeur sur la cellule D7.
*De la barre d'outils Audit, appuyez sur le bouton Repérer les antécédents.

Excel: Audit montre les antécédents d'une formule Somme()

La flèche indique que la cellule D7, qui est le total des revenus du mois, dépend des chiffres des cellules D3 à D5.

*De la barre d'outils Audit, appuyez sur le bouton Repérer les dépendants.

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.

*Pour retirer toutes les lignes, appuyez sur le boutonSupprimer toutes les flèches.

Repérer une erreur

Il 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.

*Placez le pointeur dans la cellule B3.
*Entrez le texte suivant: ABC.

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.

*Placez le pointeur la cellule D19.

Il faut maintenant trouver la cause de l'erreur.

*De la barre d'outils Audit, appuyez sur le bouton Repérer une erreur.

Excel: Les antécédents de Audit démontre la source d'une 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.

*Entrez la valeur 100 000 dans la cellule B3.

Le problème a été réglé. Vous pouvez donc retirer les flèches de référence.

*Pour retirer toutes les lignes, appuyez sur le boutonSupprimer toutes les flèches.

Données non valides

Excel 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.

*Placez le pointeur sur la cellule B7.
*Du menu Données, sélectionnez l'option Validation.
*Sélectionnez l'onglet Options.

Autoriser: décimal, Données: compreise entre, Minimum: 0, Maximum 300000

*Entrez les valeurs dans les cases appropriées.
*Appuyez sur le bouton OK.

La validation est placée. Il faut maintenant vérifier qu'elle fonctionne correctement.

*De la barre d'outils Audit, appuyez sur le bouton Entourer les données non valides.

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.

*Placez le pointeur sur la cellule B3.
*Entrez la valeur 250 000.
*De la barre d'outils Audit, appuyez sur le bouton .

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.

*De la barre d'outils Audit, appuyez sur le bouton effacer les cercles de validation.
*Placez le pointeur sur la cellule B3.
*Entrez la valeur 1000 000.

Éléments supplémentaires dans Excel XP

Les 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.

Barre d'outils Audit d'Excel XP

Espionner une cellule

Vous 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.

*Placez le pointeur sur la cellule D19.
*De la barre d'outils Audit d'Excel XP, appuyez sur le bouton Afficher le volet espions.

Volet espion avec aucune cellule suivi

*Appuyez sur le bouton Ajouter un espion.

Excel: Fenêtre où l'on choisi les cellules à "espionner" dans le volet Espion.

*Assurez-vous que la cellule sélectionnée soit la cellule D19.
*Appuyez sur le bouton Ajouter.

Volet Espion avec le suivi de la cellule D19 à une valeur de 40 031,25 $

*Placez le pointeur sur la cellule B3.
*Entrez la valeur 150 000.

Volet espion avec le suivi de la cellule D19 maintenant à 173 468,75 $

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.

*Fermez le volet espions.
*Changez la valeur de la cellule B3 à 100 000.

Évaluation de formules

La 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.

*De la barre d'outils Audit d'Excel XP, appuyez sur le bouton Évaluation de formule.

Excel: évaluation des formules

Appuyez plusieurs fois sur chacun des boutons au bas de la fenêtre pour voir le potentiel de cette nouvelle option.

*Appuyez sur le bouton Fermer.

Valeur cible

Exercice disponible pour ce thème Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur les tables de données pour Excel 2007 est aussi disponible.

Le concept de la valeur cible est de savoir quelle devrait être la valeur d'une variable pour que le résultat soit un montant X. Dans l'exemple qui suit, quel devrait être le montant de la cellule A1 pour que la cellule A3 affiche un montant de 500? Vous connaissez déjà le résultat voulu mais pas ce que devrait être la valeur d'une des variables. Au lieu de faire plusieurs essais manuellement, Excel peut vous trouver le montant recherché en utilisant l'option de valeur cible.

*Entrez les valeurs et les formules suivantes dans les cellules appropriées.

A1: 100 A2:200 A3:=A1+A2

*Du menu Outils, sélectionnez l'option Valeur cible.

Cellule à définir: A3, Valeur à atteindre: 500, Cellule à modifier: A1

Pour qu'Excel trouve la valeur cible, le logiciel a besoin de trois informations.

Quelle est la cellule dont vous connaissez le résultat voulu? Cette cellule devrait toujours avoir une formule.

*Écrivez dans la case Cellule à définir la valeur A3.

Quelle est la valeur que vous voulez atteindre dans cette cellule?

*Écrivez dans la case Valeur à atteindre la valeur 500.

Quelle est l'adresse de la cellule variable? Celle qu'Excel peut changer pour essayer d'atteindre la valeur voulue. Cette case devrait seulement contenir un chiffre; pas de formules. Sinon, Excel va refuser de procéder.

*Écrivez dans la case Cellule à modifier la valeur A1.
*Appuyez sur le bouton OK.

Excel va ensuite faire des essais pour déterminer quel est le montant recherché pour avoir le bon montant dans la cellule à définir. Pour cet exemple, Excel a trouvé le résultat suivant.

Cellules avec les nouveaux résultats. A1: 300, A2:200, A3:500

Il faut que la cellule A1 ait un montant de 300 pour que la cellule A3 ait le montant recherché de 500. Il y a aussi des cas ou Excel ne trouvera pas de résultats. Soit qu'il n'y a pas de liens directs ou indirects entre la cellule à définir et la variable ou que les contraintes imposées par le modèle rendent impossible la valeur cible.

Il y a cependant des limites à l'option de valeur cible. Elle permet qu'une seule cellule, ou variable, soit modifiée. La valeur résultante doit être connue. Il est impossible de minimiser ou de maximiser le résultat.

La table de données ( 1 ou 2 variables )

Exercice disponible pour ce thème Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur les tables de données pour Excel 2007 est aussi disponible.

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.

B1:100 000, B2: 0,075, B3:25, B4:12, B5:=vpm(B2/B4;B3*B4;B1) , B6:=B5*B4*B3

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.

*Entrez le texte, les chiffres et les formules suivants dans les cellules appropriées.
*Pour la cellule B5, vous pouvez entrer la fonction en utilisant le bouton Bouton insérer une formule.
OU
*Du menu Insertion, sélectionnez l'option Fonction.
*De la section Finance, sélectionnez la fonction VPM.

Taux:B2/B4, Nom:B3*B4, Va:B1

*Entrez les données dans les cases appropriées.

Table de données à une variable

Avant 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.

Table avec les taux à la gauche et les cellule dont un veut voir le résultat en haut.

*Entrez les chiffres et les formules suivantes dans les cellules appropriées.

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.

*Faites un bloc avec les cellules de A9 à C14.
*Du menu Données, sélectionnez l'option Table.

Fenêtre table: B2 pour les colonnes

*Cliquez dans la case Cellule d'entrée en colonne.
*Cliquez ensuite sur la cellule, celle qui sera remplacée par chacune des valeurs de la première colonne de la table de données. Pour les besoins de l'exercice, sélectionnez la cellule B2.
*Appuyez sur le bouton OK.

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%.

*De la cellule A14, changez le taux d'intérêt de 8,5% à 10%.

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 variables

Le 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.

Table avec les taux à la gauche et le nombre de paiements en haut.

*Entrez les chiffres et la formule dans les cellules appropriées.

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.

*Du menu Données, sélectionnez l'option Table.

Fenêtre table: B4 pour les lignes et B2 pour les colonnes

*Entrez les adresses de cellules dans les cases appropriées.
*Appuyez sur le bouton OK.

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

Exercice disponible pour ce thème Une vidéo de formation est accessible pour ce thèmeUn exercice et une vidéo de formation sur le gestionnaire de scénarios pour Excel 2007 est aussi disponible.

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.

*Si ce n'est pas déjà fait, entrez les montants et les formules dans les cellules appropriées.

a1: 100 a2: 200 a3:+a1+a2

*Du menu Outils, sélectionnez l'option Gestionnaire de scénario.

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.

Écran initial du gestionnaire de scénarios

*Appuyez sur le bouton Ajouter.

Scénario optimiste, cellules variables A1:A2

*Dans la case Nom du scénario, entrez le nom du scénario: Optimiste.
*À la case "Cellules variables:", entrez les adresses des cellules dont vous voulez changer dans votre modèle.

Sélectionnez les cellules A1 et A2.

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.

*À votre choix, vous pouvez aussi écrire un commentaire. Ceci est pour vous donner plus de détails sur le scénario . Par exemple, vous pourriez écrire: "voici un scénario très optimiste pour la compagnie", ou "voici les évaluations de Mario" etc.
*Une fois que vous avez entré les données, appuyez sur le bouton OK.

Excel va ensuite vous demander quels sont les montants pour les cellules variables.

*Pour la cellule A1, entrez 1 million.
*Pour la cellule A2, entrez 2 millions.
*Appuyez sur le bouton OK.

A1:100 000, A2:200 000

Il faut ajouter le scénario pessimiste.

*Appuyez sur le bouton Ajouter.

Nom: Pessimiste, Variables A1:A2

*Encore une fois, entrez le nom du scénario, les adresses des cellules variables et, si nécessaire, un commentaire.
*Pour les cellules A1 et A2, entrez la valeur 0.

Valeurs A1:0, A2:0

*Puisqu'il n'y a plus de scénarios à ajouter, appuyez sur le bouton OK.

Fenêtre principal du gestionnaire de scénario avec les deux nouveaux scénarions d'inscrits

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.

*Appuyez sur le bouton Synthèse.

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.

*Sélectionnez l'option Synthèse de scénarios.

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.

*Pour cet exemple, sélectionnez seulement la cellule A3.
*A
ppuyez sur le bouton OK.

Synthèse de scénario et valeur résultante de A3.

Excel va prendre quelques instants pour créer une nouvelle feuille de calcul avec les résultats de tous vos scénarios.

Nouvelle feuille de calcul de synthèse des 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èse

Si vous voulez effacer la feuille pour réessayer.

*Du menu Édition, sélectionnez Supprimer une feuille.

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 cellules

Vous 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.

*Sélectionnez la feuille de calcul avec les chiffres et la formule.
*Placez le pointeur dans la cellule A1.
*Du menu Insertion, sélectionnez les options Nom et Définir.

*Dans la première case, entrez le nom suivant: Produit_1 .

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.

*Appuyez sur le bouton OK.

Pour les besoins de l'exercice il faut aussi donner un nom aux cellules A2 et A3.

*Placez le pointeur dans la cellule A2.
*Du menu Insertion, sélectionnez les options Nom et Définir.
*Entrez le nom Produit_2 et appuyez sur le bouton OK.
*Placez le pointeur dans la cellule A3.
*Du menu Insertion, sélectionnez les options Nom et Définir.
*Entrez le nom Bénéfices et appuyez sur le bouton OK.

Il est maintenant temps de régénérer une nouvelle feuille de synthèse avec les noms des cellules.

*Du menu Outils, sélectionnez l'option Gestionnaire de scénarios.
*Appuyez sur le bouton Synthèse.

*Assurez-vous de choisir un rapport de type Synthèse de scénario et aussi que la cellule résultante soit la cellule A3.
*Appuyez sur le bouton OK.

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.

*Du menu Edition, sélectionnez l'option Remplacer.
OU
*Appuyez sur les touches Ctrl et H.

Cette option permet de remplacer des mots ou des caractères par d'autres. Elle est disponible dans toutes les applications Office.

*Dans la case Rechercher, entrez le souligné ( MAJ et - ).
*Dans la case Remplacer par, entrez un espace ( barre d'espacement ).
*Appuyez sur le bouton Remplacer tout.

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:

*Fermez la fenêtre du gestionnaire de scénarios.
*Appuyez sur le bouton Annuler.
OU
*Du menu Édition, sélectionnez l'option Annuler Afficher.

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.

*Si ce n'est déjà fait, entrez les chiffres et la formule suivante.

a1: 100 a2: 200 a3:+a1+a2

Pour l'exercice, il faut créer un scénario dont la cellule variable est A3.

*Du menu Outils, sélectionnez l'option Gestionnaire de scénarios.
*Appuyez sur le bouton Ajouter.

*Pour le scénario, entrez pour nom "Test" et la cellule A3 pour les cellules variables.
*Appuyez sur le bouton OK.

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.

*Appuyez sur le bouton OK.

*Changez la valeur à 500.
*Appuyez sur le bouton OK.

La valeur n'a pas vraiment d'importance pour l'exemple.

*Appuyez sur le bouton Synthèse.

*Sélectionnez pour la cellule résultante la cellule A3 et sélectionnez le rapport Synthèse de scénarios.
*Appuyez sur le bouton OK.

Excel vous affiche la feuille de synthèse du scénario.

*Retournez à la feuille de calcul ayant les chiffres et la formule.
*Placez le pointeur dans la cellule A3.

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.

*Si vous voulez copier des scénarios d'un document Excel à un autre, il faut premièrement ouvrir les deux documents.
*Pour passer d'un document à un autre, utilisez la commande Fenêtre suivi du nom du document.
*Sélectionnez le document dans lequel où vous voulez recevoir les scénarios.
*Sélectionnez la feuille de calcul où vous voulez recevoir les scénarios.
*Du menu Outils, sélectionnez l'option Gestionnaire 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.

*Appuyez sur le bouton Fusionner.

Fen&#exceloutils234;tre de fusion de scénarios

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.

*Une fois que vous avez sélectionné le bon classeur et la bonne feuille de calcul, appuyez sur le bouton OK.

Résultat dans la fen&#exceloutils234;tre principale du gestionnaire de scénarios

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é

Le solveur

Exercice disponible pour ce thème Une vidéo de formation est accessible pour ce thème Un exercice et une vidéo de formation sur le solveur pour Excel 2007 est aussi disponible.

Le solveur sert à trouver la solution optimale de votre modèle selon les conditions que vous lui déterminez. Il peut autant trouver comment maximiser vos profits que de minimiser vos pertes ou déterminer quelle est la meilleure manière d'atteindre au certain nombre.

Le modèle est très simple. Il faut découvrir quels sont les chiffres à mettre dans les cellules A1 et A2 pour maximiser le résultat de la cellule A3. La première action est d'activer le solveur.

*Du menu outils, sélectionnez solveur.

*Si vous ne voyez pas l'option dans Excel, sélectionnez Outils, Macro supplémentaire.
*De la liste, choisissez solveur.

L'option va maintenant apparaître dans le menu outils.

&#exceloutils201;cran initial du solveur

Si l'option n'est pas affichée, installez-le à partir du CD d'Office 97. L'installation d'Excel n'est pas complète.

Dans la première case de la fenêtre, le solveur vous demande quelle est la cellule à optimiser . Pour l'exemple, il s'agit de la cellule A3. Ensuite le solveur veut savoir s'il doit maximiser, minimiser ou optimiser à une certaine valeur la cellule à optimiser. Après, le solveur a besoin de savoir quelles sont les cellules qu'il peut modifier pour essayer d'optimiser la cellule cible.

Pour que le solveur fonctionne correctement, il faut lui déterminer des contraintes qu'il va respecter. Vous pouvez ajouter jusqu'à 100 contraintes de votre choix. Pour cet exemple, il faut ajouter 4 contraintes qui déterminent les maximums et les minimums des cellules A1 et A2. Les montants minimums et maximums que les cellules A1 et A2 doivent respecter sont inscrits dans les colonnes C et D.

Pourquoi ne pas simplement entrez ces montants directement dans la contrainte?

Il est beaucoup plus facile de changer le contenu d'une cellule que d'essayer de retrouver une contrainte parmi une longue liste pour ensuite la modifier. Il est donc préférable de mettre les montants des contraintes dans des cellules que dans les contraintes elles-mêmes.

*Appuyez sur le bouton Ajouter.
*Cliquez sur la première case de la fenêtre pour Ajouter une contrainte.
*Cliquez sur la case A1 et écrivez dans celle-ci A1.
*Cliquez sur la petite flèche pointant vers le bas et sélectionnez l'option <=.
*Cliquez sur la troisième case de la fenêtre pour Ajouter une contrainte.
*Cliquez sur la case D1 ou écrivez dans celle-ci D1.
*Appuyez sur le bouton OK.

Vous venez d'ajouter votre première contrainte. Celle-ci indique au solveur que la cellule A1 doit être inférieure ou égale au contenu de la cellule D1. Vous venez de déterminer le maximum que la cellule A1 peut atteindre. La prochaine contrainte sert à déterminer le minimum que la cellule A1 peut atteindre. Les deux autres servent pour le maximum et le minimum de la cellule A2.

*Ajoutez les trois autres contraintes ci-dessous en appuyant sur le bouton Ajouter au bas de la fenêtre ou celui de la fenêtre du solveur.

*Appuyez sur le bouton OK

Voici la liste des contraintes que vous venez d'ajouter.

Toutes les informations dont le solveur a besoin pour résoudre le problème ont été entrées. Il y a :

+l'adresse de la cellule à définir
+les adresses des cellules variables et
+les contraintes à respecter.

Il ne reste un dernier point à vérifier.

*Appuyez sur le bouton Options.

Voici les quelques options possibles du solveur.

*Augmentez le nombre d'itérations à environ 3000.

Les itérations sont le nombre d'essais que le solveur va accomplir pour essayer de trouver la solution optimale. S'il n'y a pas assez d'itérations, le solveur ne trouvera pas la solution optimale. Il va vous afficher la meilleure solution avec le nombre d'itérations disponibles.

*Appuyez sur le bouton OK.

Il ne reste qu'à demander au solveur de trouver la solution optimale.

*Appuyez sur le bouton Résoudre.

Voici le résultat. Excel a déterminé que le maximum possible pour la cellule A1 est de 1 million et que celui de la cellule A2 est de 2 millions. Donc, pour un total de 3 millions dans la cellule A3. Ceci était un exemple très simple. Mais vous pouvez maintenant utiliser les concepts que vous venez de voir pour laisser le solveur déterminer la solution optimale pour des modèles beaucoup plus complexes.

Note:
Il arrive qu'avec les marges de manoeuvre allouées qu'il y ait plusieurs solutions optimales possibles. Le solveur va seulement vous montrer la première solution qu'il a trouvée.



 

Vous avez apprécié cette page?

Ajoutez-la dans vos favoris en utilisant l'un des métaoutils ci-dessous. Cliquez sur l'un des boutons. Vous
serez surpris par toutes les références disponibles.

Cliquez sur le bouton de votre choix

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.

Cliquez ici pour vous abonner à l'infolettre





Ce site est hébergé par 1&1.com