Excel Avance

omplémentaire qui doit être installée si cela n’a pas encore été fait.

1 - INSTALLER LES UTILITAIRES D’ANALYSE

• Outils/Macros complémentaires • Cocher xUtilitaire d’analyse • Cliquer «OK»

2 - UTILISER LES UTILITAIRES D’ANALYSE

• Outils/Utilitaire d’analyse

• Sélectionner un type d’analyse • Cliquer «OK» Exemple : calcul de la moyenne mobile d’une série.

(a) (b)

(c)

(d)

(a) (b) (c) (d)

Cliquer dans cette zone, puis sélectionner la plage contenant les données de la série. Préciser si la plage contient des libellés. Indiquer ici la référence de la cellule du coin supérieur gauche de la plage de résultat. Indiquer si un graphique doit être généré.

• Cliquer «OK» © Tsoft/Eyrolles – Excel 2002 Avancé – Formation avec exercices et cas pratiques 69

TABLE D'HYPOTHÈSES

Cette fonction permet de tester plusieurs hypothèses pour une formule. Il peut y avoir une ou deux variables et les résultats sont présentés dans un tableau.

1 - TABLE À SIMPLE ENTRÉE (UNE VARIABLE)

L’exemple suivant est un modèle qui permet de calculer le montant du remboursement mensuel pour un prêt. Il utilise pour cela la fonction VPM (valeur des paiements). L’objectif est de voir quelle est l’influence d’une variation du taux d’intérêt sur le montant du remboursement mensuel.

(a)

(b)

(c)

Créer le modèle de manière habituelle : • Saisir les données utilisées par la formule, ici en (a) • Créer la formule, ici =VPM(C8/12;C7;-C6) en (b) Créer et remplir la table : Comme on veut tester l’influence d’une variation du taux d’intérêt, la variable est le taux. • Saisir en (c), sous la formule créée précédemment et dans la colonne précédente, les valeurs du taux à tester • Sélectionner la plage de cellules contenant la formule et les valeurs de test, ici B11:C17 • Données/Table

(a) (b)

• Cliquer en (a) si les valeurs à tester sont en ligne ou en (b) si les valeurs à tester sont en colonne (dans notre exemple, elles sont en colonne) • Y taper la référence de la cellule variable (ici il s’agit du taux, soit la cellule C8) ou cliquer sur cette cellule dans la feuille de calcul • Cliquer «OK» La table se remplit et présente les montants des remboursements en fonction des taux saisis :

70

© Tsoft/Eyrolles – Excel 2002 Avancé – Formation avec exercices et cas pratiques

TABLES D'HYPOTHÈSES

2 - TABLE À DOUBLE ENTRÉE (DEUX VARIABLES)

L’exemple suivant est un modèle qui permet de calculer le montant du remboursement mensuel pour un prêt. Il utilise pour cela la fonction VPM (valeur des paiements). L’objectif est de voir quelle est l’influence d’une variation du taux d’intérêt et d’une variation de la durée du prêt sur le montant du remboursement mensuel. Il y a donc deux variables : le taux et la durée.

(a) (b) (c)

(d)

Créer le modèle de manière habituelle : • Saisir les données utilisées par la formule, ici en (a) • Créer la formule, ici =VPM(C8/12;C7;-C6) en (b) Créer et remplir la table : • Saisir en (c), à droite de la formule précédemment créée, les valeurs à tester pour la première variable (la durée) • Saisir en (d), sous la formule, les valeurs à tester pour la seconde variable (le taux) • Sélectionner la plage de cellules contenant la formule et les valeurs de test, ici C11:F17 • Données/Table

(a) (b)

• Cliquer en (a) • Taper la référence de la cellule variable dont les valeurs de test ont été saisies en ligne (ici la durée, donc taper C7) ou cliquer sur cette cellule dans la feuille • Cliquer en (b) • Taper la référence de la cellule variable dont les valeurs de test ont été saisies en colonne (ici le taux, donc taper C8) ou cliquer sur cette cellule dans la feuille • Cliquer «OK» La table se remplit et présente les montants en fonction des taux et des durées saisies :

© Tsoft/Eyrolles – Excel 2002 Avancé – Formation avec exercices et cas pratiques

71

VALEUR CIBLE

Le principe consiste à raisonner à l’envers : on crée une formule, puis on indique le résultat souhaité. Excel calcule alors quelle doit être la valeur de l’une des données utilisées par le calcul pour que ce résultat soit atteint. Exemple : Le modèle suivant calcule le montant du remboursement mensuel pour un prêt, étant donnés le montant de l’emprunt, sa durée et le taux d’intérêt. Question : sachant que ma capacité de remboursement est de 500 euros par mois, combien puis-je emprunter ?

(a)

(b)

Créer le modèle : • Saisir les données (a) • Créer en (b) la formule utilisant ces données Rechercher la valeur : • Sélectionner la cellule contenant la formule (ici, C10) • Outils/Valeur cible

(a) (b)

• Indiquer en (a) le résultat souhaité pour la formule (ici, 500) • Indiquer en (b) les références de la cellule dont Excel va pouvoir modifier la valeur (ici, le montant de l’emprunt : C5) • Cliquer «OK»

• Cliquer «OK» Excel affiche alors le résultat de sa recherche :

72

© Tsoft/Eyrolles – Excel 2002 Avancé – Formation avec exercices et cas pratiques

SOLVEUR

Le solveur permet de chercher les valeurs que doivent avoir certaines variables pour que le résultat d’un calcul soit optimisé (maximal, minimal ou égal à une valeur précise). Il est possible de préciser des contraintes sur divers éléments du modèle : prix inférieur à 500, marge supérieure à 10%, etc. Pour résoudre un problème à l’aide du solveur, il faut identifier trois éléments : − La cellule cible : cellule dont le résultat doit être maximal, minimal ou égal à une valeur. − Les cellules variables : cellules dont les valeurs peuvent être modifiées par le solveur. − Les contraintes (optionnelles) : bornes dans lesquelles doivent rester certaines valeurs. Il s’agit d’une macro complémentaire qui doit être installée si cela n’a pas encore été fait.

1 - INSTALLER LE SOLVEUR

• Outils/Macros complémentaires • Cocher xComplément Solveur • Cliquer «OK»

2 - RECHERCHER UNE VALEUR QUI EN MAXIMISE UNE AUTRE

Exemple