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

Excel 03/ Chap 13 - SIMULATIONS

1. FONCTION "VALEUR CIBLE"

• La cellule à modifier ne doit pas contenir de formule, juste une valeur.
• La cellule à définir contient une formule dépendant, directement ou indirectement, de 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 :

• La cellule B3 contient la formule =B1*B2
• La cellule B5 contient la formule =B4*B2
• La cellule B7 contient la formule =B3-B5-B6

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

• "Cellule à définir" : cliquez dans la zone de saisie, puis sélectionnez la cellule à définir (dans l'exemple B7) ;
• "Valeur à atteindre" : saisissez la valeur souhaitée (dans l'exemple 2000) ;
• "Cellule à modifier" : cliquez dans la zone de saisie, puis sélectionnez la cellule à modifier (dans l'exemple B2).

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.

• Si on applique la fonction "Valeur cible" pour une Valeur à atteindre de 1000 €, on obtient un minimum de 4 toiles à vendre.
• Si le prix moyen d'une toile est augmenté à 500 € : pour viser un bénéfice d'au moins 3000 €, le peintre devra vendre 8 toiles.
 

2. LES TABLES DE DONNEES

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 :

• Sélectionnez la plage de cellules contenant les valeurs du paramètre (dans l'exemple 20, 50, 60 et 100) ainsi que les formules (dans l'exemple =10*A1 et =A1+7).
  Dans l'exemple, on sélectionne la plage A1:C5 ;
• Puis affichez la fenêtre "Table" : menu Données > Table.
  • Valeurs du paramètre en colonne : cliquez dans la zone "Cellule d'entrée en colonne", et sélectionnez la cellule d'entrée en colonne (dans l'exemple, cellule A1).
  • Valeurs du paramètre en ligne : la méthode est similaire ; la cellule d'entrée est alors saisie dans la zone "Cellule d'entrée en ligne".
• Validez.

Dans l'exemple, on obtient la table :

Table de données à deux entrées

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.

• Sélectionnez la plage de cellules contenant les valeurs des deux paramètres (dans l'exemple, sélectionnez la plage A1:C4) ;
• Affichez la fenêtre "Table" : menu Données > Table.
  Cliquez dans la zone de saisie "Cellule d'entrée en ligne" et sélectionnez la cellule contenant la valeur d'entrée du paramètre défini en ligne (dans l'exemple, sélectionnez E1).
  Faites de même dans la zone de saisie "Cellule d'entrée en colonne", avec le second paramètre (dans l'exemple, sélectionnez la cellule F1) ;
• Validez.

Dans l'exemple, on obtient la table :

3. LES SCENARIOS

Création d'un scénario

• Affichez la fenêtre "Gestionnaire de scénarios" : menu Outils > Gestionnaire de scénarios. Cliquez sur le bouton Ajouter, pour ajouter un scénario.
• Dans la fenêtre "Ajouter un scénario" : attribuez un nom au scénario, et précisez les cellules variables, en les sélectionnant. Saisissez éventuellement un commentaire au scénario. Les deux options "Changements interdits" et "Masquer" ne peuvent être prises en compte que si la feuille est protégée (menu Outils > Protection). Validez.
• La fenêtre "Valeurs de scénarios" s'affiche. Saisissez les valeurs à tester.

Exemple :

• Dans la cellule D2, saisissez la formule =B2*C2, puis étendez-la jusqu'en D7 (cliquez-glissez sur la poignée de la cellule D2).
• Dans la cellule D8, saisissez la formule =somme(D2:D7).

Un scénario possible est le suivant :

Afficher, modifier ou supprimer un scénario

Fusionner des scénarios

• Ouvrez tous les classeurs contenant des scénarios à fusionner ;
• Sélectionnez la feuille dans laquelle seront fusionnés les scénarios ;
• Dans la fenêtre "Gestionnaire de scénarios", cliquez sur "Fusionner". Fusionnez feuille par feuille.

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 d'un point-virgule.