Cours BARDON - Microsoft Office
ACCUEIL
•  Télécharger    •  Imprimer
EXCEL 2007
SOMMAIRE
Chapitre précédent
Chapitre suivant

EXCEL 07/ Chap 13 - SIMULATIONS

Les simulations présentées, fonction "Valeur cible", tables et scénarios, consistent à faire varier des paramètres, puis à examiner les résultats obtenus en fonction des différentes valeurs testées.

1. FONCTION "VALEUR CIBLE"

Elle permet de connaître quelle doit être la valeur contenue dans une cellule pour atteindre une valeur définie dans une autre cellule.
Exemple : quel doit être le prix de vente d'un produit (contenu dans la cellule à modifier)
pour obtenir un bénéfice donné (valeur cible, contenu dans la cellule à définir).
Autrement dit, en utilisant les expressions de la fenêtre "Valeur cible", la fonction "Valeur cible" indique, en fonction de la "valeur cible" contenue dans la "cellule à définir", quelle doit être la valeur de la "cellule à modifier".

Exemple

Vous êtes artiste peintre. Vous vendez vos toiles à un prix moyen de 400 €.
Vous aimeriez connaître le nombre minimum de toiles que vous devez vendre par mois pour gagner 2000 €/mois.
Vos frais s'élèvent à 80 € en moyenne par tableau, et vous louez un atelier 150 €/mois, charges comprises.

Modélisez ces données sur une feuille de calcul :

On a donc, en valeurs : B7 = (400 * B2) - (80 * B2) - 150
Cette expression est équivalente à : B2 = (B7 + 150) / 320

A une valeur "cible" de bénéfice (cellule B7), la fonction "Valeur cible" renvoie en résultat la valeur du nombre de toiles à vendre (cellule B2).
La cellule à définir est B7, la valeur à atteindre est 2000, la cellule à modifier est B2.

Application de la fonction Valeur cible

Affichez la fenêtre "Valeur cible" : sous l'onglet "Données", dans le groupe "Outils de données", activez le bouton "Analyse de scénarios"Valeur cible. Validez.
La fenêtre "Etat de la recherche" s'affiche. Si la recherche a abouti, le tableau de simulation est rempli.

Dans l'exemple, on obtient le tableau suivant :

Pour obtenir un bénéfice d'au moins 2000 €, le peintre doit donc vendre 7 toiles.

Autres simulations :

2. LES TABLES DE DONNEES

Une table de données affiche des résultats de formules, résultats dépendant d'un ou de deux paramètres.
On peut ainsi créer des tables à une ou à deux entrées.

Table de données à une entrée

On fait varier les valeurs d'un paramètre sur plusieurs formules.
Dans la table, on peut entrer les valeurs du paramètre en colonne et les formules en ligne, ou bien l'inverse.

Exemple :

La table est remplie.

Dans l'exemple, on obtient la table :

Table de données à deux entrées

On fait varier les valeurs de deux paramètres sur une seule formule.

Exemple :

La formule est =10*E1+F1. Elle comporte les deux paramètres E1 et F1, qui correspondent aux deux cellules d'entrée.
On souhaite connaître les résultats de la formule selon les valeurs de ses deux paramètres.
Le premier paramètre a ses valeurs en ligne (la cellule d'entrée en ligne est E1) : 4 et 9.
Le second paramètre a ses valeurs en colonne (la cellule d'entrée en colonne est F1) : 20, 50 et 60.
On aurait pu faire l'inverse.

La table est remplie.

Dans l'exemple, on obtient la table de données :

3. LES SCENARIOS

La méthode des scénarios permet de faire varier de nombreux paramètres, jusqu'à 32 paramètres.

Création d'un scénario

Validez, ou ajoutez un autre scénario (ce qui validera le scénario précédent).

Exemple :

La plage des cellules variables est B2:C7. Les valeurs de ces cellules sont définies dans la fenêtre "Valeurs de scénarios".

Un scénario possible est le suivant :

En changeant quantités ou prix (valeurs de cellules de la plage B2:C7), on obtient d'autres scénarios.

Afficher, modifier ou supprimer un scénario

Pour afficher, modifier ou supprimer un scénario, affichez d'abord la fenêtre "Gestionnaire des scénarios" : sous l'onglet "Données", dans le groupe "Outils de données", activez le bouton "Analyse de scénarios" > Gestionnaire de scénarios.
Choisissez le scénario, puis cliquez sur Afficher, Modifier ou Supprimer. La suppression d'un scénario est définitive.

Fusionner des scénarios

Des scénarios relatifs à une même modélisation peuvent se trouver sur plusieurs feuilles, voire sur plusieurs classeurs, si différentes personnes ont travaillé sur le projet.
Afin que tous les scénarios soient affichables sur une même feuille, il est possible de les fusionner :

Synthèse de scénarios

Un rapport de synthèse affiche pour chaque scénario les valeurs testées dans les cellules variables et les résultats obtenus dans les cellules résultantes. Il est créé automatiquement, sur une nouvelle feuille de calcul. Il peut comporter jusqu'à 251 scénarios.

Pour le créer, affichez la fenêtre "Synthèse de scénarios" en activant le bouton "Synthèse" de la fenêtre "Gestionnaire de scénarios".
Cliquez dans la zone de saisie "Cellules résultantes", et sélectionnez les cellules résultantes. Leurs références s'affichent, séparées par un point-virgule.

Dans le classeur actif, EXCEL crée une nouvelle feuille de calcul, nommée "Synthèse de scénarios".
Pour chaque scénario, sont affichées les valeurs des cellules variables et celles des cellules résultantes.
Le rapport est nettement plus lisible si les cellules variables et résultantes ont reçu des noms explicites, au lieu d'être désignées par leurs références.