Guide de formation avec exercices et cas pratiques

Excel 2002 Avancé
Patrick Morié, Bernard Boyer

©Tsoft et Groupe Eyrolles, 2003 ISBN : 2-212-11238-6

5 - ANALYSE ET SIMULATION

MODÈLE ITÉRATIF
1 - NOTION D’ITÉRATION
Dans certains modèles, il arrive qu’une formule renvoie indirectement à elle-même. La formule utilise son propre résultat dans son calcul. En résumé, elle boucle : on parle alors de référence circulaire. Pour résoudre ce type de problème, Excel procède par itérations, ce qui consiste à répéter le calcul en tenant compte à chaque fois du résultat du calcul précédent. Une référence circulaire peut être soit divergente (elle n’amène aucun résultat significatif), soit convergente (elle converge vers une valeur). Exemple 1 : Calculons la prime d’un commercial qui est égale à 10 % du bénéfice net. Or le bénéfice net dépend du montant de la prime : les formules en B2 et B3 contiennent une référence circulaire car chacune fait référence à l’autre.

Solution : Prime = 9,09 Exemple 2 : Résoudre l’équation à deux inconnues suivante : X=(Y+100)/2 Y=X/2

Solution : X=66,67 et Y=33,33

2 - AUTORISER LE CALCUL ITÉRATIF
Par défaut, Excel n’effectue pas d’itération. Quand une référence circulaire est créée comme dans les deux exemples précédents, il affiche le message suivant :

• Cliquer «Annuler» Dans ce cas, pour demander à Excel de chercher une solution par itérations : • Outils/Options, puis cliquer sur l’onglet Calcul
(a) (b)

• • • •

Cocher xItération Taper en (a) le nombre maximum d’itérations (recalcul de la formule) Taper en (b) la valeur d’écart entre deux calculs à partir de laquelle l’itération doit s’arrêter Cliquer «OK» © Tsoft/Eyrolles – Excel 2002 Avancé – Formation avec exercices et cas pratiques

68

UTILITAIRES D'ANALYSE
Cet outil propose dix-neuf types d’analyses statistiques :

On précise la position des données, puis Excel génère un tableau de synthèse et, sur demande, un graphique. Il s’agit d’une macro