Excel: Valeur cible et solveur

Tu dois devenir capable de

Savoir Expliquer l’intérêt de l’usage des outils abordés dans ce chapitre ; Citer les caractéristiques des différents éléments qui interviennent dans le calcul d'une valeur cible ; Savoir faire Utiliser l’outil « valeur cible » ; Utiliser l’outil « solveur » en posant les contraintes nécessaires ;

Atteindre une valeur cible Les calculs effectués habituellement avec Excel utilisent des valeurs numériques qui constituent les données d'un problème. A l'aide d'un certain nombre de formules disposées dans les cellules des feuilles de calculs, on détermine alors un ou plusieurs résultats. Mais imaginons que, dans certains cas, on connaisse les résultats attendus et que l'on se demande quelles doivent être les données. - Donc, dans le premier cas, c'est un peu comme si je calculais le prix à payer pour acheter des barres de chocolat ; dans le deuxième cas, je détermine combien de barres de chocolat je puis acheter avec la somme dont je dispose. - C'est tout à fait cela. Mais attention à la crise de foie. En marche avant L’exemple très simple ci-contre permet d’illustrer le concept : dans la cellule A2, indique la valeur 2 dans la cellule B2, indique la valeur 7 dans la cellule C2, indique la formule « =A2*B2 » Le résultat de l'opération apparaît en C2. C'est bien 14. En marche arrière Posons-nous maintenant la question inverse. Quelle devrait être la valeur de la cellule A2 pour obtenir le résultat 28 dans la cellule C2? On connaît le résultat calculé (28) , la formule pour le calculer (=A2*B2), mais pas la première donnée. L'animation ci-dessous indique la marche à suivre pour faire déterminer quelle donnée fournira la résultat attendu. Examine-la attentivement. Reproduis cette résolution en suivant les étapes indiquées ci-dessous. Dans une nouvelle feuille de calcul, indique les valeurs de A2, B2 et la formule dans la cellule C2. Dans le menu Outils, sélectionne la commande Valeur cible Dans la boîte de dialogue Valeur cible, indique la référence de la cellule dont tu souhaites définir la valeur. Dans le cas présent, c'est la cellule C2. Clique sur le bouton et désigne la cellule C2 par un clic de souris. Pour terminer, clique sur le bouton de la boîte de dialogue Cellule à définir. Dans la zone de saisie Valeur à atteindre, indique la valeur 28. Finalement, indique que la cellule à modifier est bien A2 par la même méthode que tu as utilisée pour désigner C2. Clique sur OK. Très rapidement, le tableur signale qu'il a trouvé une solution possible avec la valeur 28 pour valeur cible. Dans certains cas, il est possible que le tableur ne puisse trouver de solution ou pas de solution exacte. Dans ce cas, les informations données dans la boîte de dialogue indiquent l'état de la résolution du problème au moment de l'échec. Les exemples ci-dessous correspondent à un autre calcul! Le tableur trouve une solution approximative Le tableur ne peut trouver de solution A retenir! Dans la recherche d'une valeur cible: la valeur ciblée doit se trouver dans une cellule qui contient une formule la valeur ciblée doit obligatoirement être un nombre la cellule à modifier ne peut contenir une formule mais uniquement une valeur

Quand tu te sens prêt(e) à répondre à quelques questions sur les notions abordées ici, passe à l'étape suivante.

Valeur cible: synthèse Les trois informations à fournir pour déterminer une valeur cible sont: La cellule qui contient la valeur cible, sa valeur et une cellule variabe La cellule qui contient la valeur cible, la formule contenue dans cette cellule, une formule variable Les références de trois cellules quelconques Lors de la détermination d'une valeur cible, la cellule qui contient cette valeur: doit contenir une formule peut contenir une formule ou une valeur doit contenir une valeur mais pas une formule Lors de la détermination d'une valeur cible, la cellule à modifier doit contenir une formule doit contenir une valeur mais pas une formule peut contenir une valeur ou une formule Lors de la recherche d'une valeur cible: Le tableur trouve toujours un résultat exact Le tableur trouve toujours un résultat, exact ou approximé Le tableur ne trouve pas toujours un résultat Lors de la recherche d'une valeur cible, le nombre de cellules que l'on peut faire varier pour obtenir cette valeur est : 1 cellule 2 cellules 3 cellules

Quand tu as répondu à toutes les questions et bien compris toutes les réponses, passe à l'étape suivante.

Nombre de mensualités d'un emprunt Tu es employé(e) de banque. Un client disposant d'un certaine économie mensuelle souhaite faire un emprunt. Il te demande de calculer combien de mensualités il devra payer afin de rembourser cet emprunt, sans dépasser ses capacités de remboursement. Imaginons que le montant de l'emprunt soit de 5000 € et que les intérêts se montent à 0,35% par mois. Ce client peut disposer de 200 € d'économies par mois. En marche avant Commençons par établir le calcul du montant du remboursement mensuel dans les conditions précisées et pour 18 mois, par exemple. Etablis les titres dans la colonne A d'une feuille de calculs vierge, comme dans l'illustration ci-contre. Dans la cellule B1, indique le montant de l'emprunt: soit 5 000 €. Dans la cellule B2, indique la valeur du taux d'intérêt: soit 0,35% Dans la cellule B3, établis la formule qui permet de calculer le montant des intérêts mensuels. Quand tu as déterminé cette formule, écris-la. Remarque que cette formule fonctionne pour n'importe quel montant emprunté et pour n'importe quel taux. Dans la cellule B5, indique le nombre de mensualités envisagées. Dans la cellule B6, indique la formule qui calcule le montant des intérêts pour le nombre de mensualités. Dans la cellule B7, indique la formule qui calcule le montant total à payer, capital et intérêts. Dans la cellule B8, indique la formule qui calcule le montant du remboursement mensuel. Vérifie que les calculs sont corrects pour n'importe quel montant emprunté et n'importe quel taux d'intérêt. En marche arrière Imaginons que notre client ne soit pas capable de rembourser 295,28 €/mois, comme dans l'exemple précédent. Combien de mensualités doit-il envisager pour une capacité de remboursement mensuel de 200 €? Quelle est ici la valeur cible? Quelle est la cellule variable? Réponds à ces deux questions avant de poursuivre. La valeur cible est: 200€ de remboursement mensuel 0,30% de taux d'intérêt 15 mois de remboursement Si l'on se réfère à l'illustration en haut de la page, la cellule variable est: B2 B3 B5 Dans le menu Outils, sélectionne la commande Valeur cible Dans la boîte de dialogue Valeur cible, indique la référence de la cellule B8 comme cellule à définir. Indique la valeur 200 (€) pour valeur de cette cellule. Indique la cellule B5 comme cellule variable. Clique sur le bouton OK. Le tableur détermine que le nombre de mensualités est 27,397. - 27,397 mensualités? Mais ça ne veut rien dire... - Effectivement, cela ne veut rien dire. Mais c'est l'occasion de rappeler que l'ordinateur ne réfléchit pas. Il traite des nombres sans avoir la moindre possibilité de comprendre ce que signifient ces nombres. Quand tu as résolu le problème précédent, vérifie que tu es capable de déterminer le nombre de mensualités si l'on envisage de rembourser 500 € par mois pour un capital de 10 000 €.

Quand ta feuille de calculs fonctionne correctement pour tous les cas, passe à l'étape suivante.

Exercices non commentés Résous les exercices ci-dessous. Pour connaître le prix TTC d'un article sur lequel le taux de TVA est de 21%, un commerçant dispose de la feuille de calcul illustrée ci-contre. Pour des raisons commerciales, il voudrait vendre un article au prix de 199,90€ TTC. A quel prix hors TVA doit-il facturer cet article? La cellule B2 d'une feuille de calculs contient une valeur x ; la cellule C2 contient la valeur de f(x)=3.sin(x)-4.cos²(x) On demande de déterminer pour quelle valeur de x, la fonction f(x) a pour valeur 0. La cellule B2 d'une feuille de calculs contient une valeur x ; la cellule C2 contient la valeur de f(x) = . On demande de déterminer pour quelle valeur de x, la fonction f(x) a pour valeurs: 0 -3

Quand tu as résolu les exercices proposés, passe à l'étape suivante.

Détermination du minimum d'une fonction La technique de recherche d'une valeur cible est puissante. Cependant, elle est limitée à la recherche d'une valeur unique en fonction de la valeur d'une cellule unique. Pour résoudre des problèmes plus complexes, on peut faire appel à un complément d'Excel appelé Solveur. Installer le solveur Le solveur est un outil additionnel d'Excel. Il n'est pas installé par défaut. Il faut donc vérifier qu'il est installé avant de pouvoir l'utiliser. Dans le menu Outils, vérifie la présence de la commande Solveur... Si la commande figure dans le menu, tu peux passer les lignes qui suivent jusqu'au prochain titre. Sélectionne la commande Macros Complémentaires... Dans la boîte de dialogue qui apparaît, sélectionne l'option Solveur et coche la case correspondante. Clique sur le bouton OK. Le solveur est maintenant installé. Il se pourrait, en fonction de l'installation originale d'Excel, que le Solveur n'apparaisse pas dans la boîte de dialogue présentée ci-dessus. Il faut alors procéder à une ré-installation d'Excel à partir du CD d'installation. Recherche du maximum absolu Nous allons chercher la valeur du maximum de la fonction f(x) = 4.x.(1-x). Commençons par établir une feuille de calculs qui détermine la valeur de f(x) pour n'importe quelle valeur de x, comme sur l'illustration ci-dessous. Tout ceci ne demande aucun outil particulier. Dans le menu Outils, sélectionne la commande Solveur Dans la zone Cellule cible à définir, sélectionne la cellule B2 Parmi les options Egale à, sélectionne Max Dans la zone Cellules variables, sélectionne la cellule A2 Clique sur Résoudre. Le solveur annonce qu'il a trouvé une solution: Clique sur le bouton OK pour garder cette solution. Quelle méthode pourrait-on envisager pour trouver le minimum de la fonction en utilisant le solveur? Tente de mettre cette méthode en oeuvre et explique pourquoi elle ne donne pas de résultat probant.

Quand tu as déterminé le maximum de la fonction et réfléchis à la méthode pour trouver le minimum, passe à l'étape suivante.

Minimum d'une fonction dans un intervalle A l'étape précédente, nous avons déterminé le minimum absolu d'une fonction mathématique. Le solveur permet également de déterminer le minimum d'une fonction dans un intervalle déterminé. Sur une feuille de calcul vierge, établis quatre cellules comme sur l'illustration ci-dessous: Nous allons déterminer le minimum de cette fonction dans l'intervalle [0 ; -3.14]. Démarre le solveur Dans la zone Cellule cible à définir, sélectionne la cellule B2 Parmi les options Egale à, sélectionne Min Dans la zone Cellules variables, sélectionne la cellule A2 Clique sur le bouton Ajouter de la zone Contraintes Définis la première contrainte comme illustré ci-dessous: Clique sur le bouton Ajouter Ajoute la deuxième contrainte: la valeur de x > -3,14 (attention, il faut obligatoirement utiliser le "." et non la "," dans cette valeur qui apparaît dans la contrainte). Clique sur le bouton OK Clique sur le bouton Résoudre. Que se passe-t-il lorsque l'on demande de déterminer le maximum de la fonction précédente entre -6,28 et +6,28? Quelle remarque cela impose-t-il?

Quand tu as déterminé la valeur du minimum et du maximum de la fonction dans l'intervalle demandé, passe à l'étape suivante.

Un peu de pâtisserie avec le solveur La confection d'une tarte demande d'utiliser: La confection de gaufres demande d'utiliser: 2 oeufs 250 grammes de farine 0,5 litres de lait 3 oeufs 300 grammes de farine 0,7 litres de lait La feuille de calculs illustrée ci-desous devra permettre de calculer les quantités de farine et de lait lorsque l'on change le nombre d'oeufs. Elle calcule également le coût de chaque ingrédient au tarif suivant: oeufs: 0,20€/pièce farine: 0,50€/kg lait: 0,60€/Litre Le bénéfice est calculé en doublant le coût. Cette feuille est disponible au téléchargement en cliquant sur l'illustration (clic droit / Enregistrer la cible sous). Complète la feuille de calculs de manière à ce qu'elle détermine les quantités des différents ingrédients pour X gaufres et Y tartes. On dispose de 100 oeufs, 12 kg de farine et 25 litres de lait. Quelles pâtisseries faut-il produire pour obtenir le meilleur bénéfice? Si une première tentative de résolution de l'exercice fournit des résultats irréalistes, il faut ajouter les contraintes qui conviennent.

Quand tu as déterminé la meilleure façon de faire des bénéfices pâtissiers, passe à l'étape suivante.

Kidmobil: l'heure des jeux La société « KidMobil » fabrique du matériel pour plaines de jeux. Elle propose, à son catalogue, trois jeux différents : le modèle « Économique », le modèle « De Luxe » et le modèle « King Size ». Ces différents articles sont fabriqués à partir de pièces détachées qui peuvent servir pour différents jeux. Le tableau 1 ci-dessous indique quelles pièces sont utilisées pour chacun des jeux ainsi que le nombre ce chaque pièce nécessaire. Modèle Poutre P1 Poutre P2 Poutre P3 Traverse T1 Balançoire Escarpolette Fixation Économique 3 0 0 0 2 0 4 De Luxe 3 1 0 4 2 0 4 King Size 2 0 1 0 3 2 10 Chaque article dégage une marge bénéficiaire propre indiquée dans le tableau 2. Modèle Marge bénéficiaire Économique 150 € De Luxe 175 € King Size 250 € Le stock de pièces détachées en magasin est indiqué dans le tableau 3. Pièce Stock Poutre P1 220 Poutre P2 40 Poutre P3 30 Traverse T1 150 Balançoire 210 Escarpolette 50 Fixation 550 On se demande comment utiliser le stock de pièces détachées disponible afin de dégager le meilleur bénéfice total après la vente. Pour répondre à cette question, on établit une feuille de calcul sur le modèle précisé ci-dessous. Les parties grisées du tableau correspondent à des informations données ; la ligne « Fabrication » indique les quantités de chaque article que l’on envisage de fabriquer ; toutes les autres informations sont calculées ; la cellule intitulée « Reste minimum » indique le plus petit nombre d’articles restant en stock après fabrication. Elle est calculée à l’aide d’une fonction statistique. Établis la feuille de calcul de manière tout à fait classique. Modifie les valeurs du nombre de pièces fabriquées pour chaque modèle de manière à pouvoir évaluer le profit total réalisé dans chacun des cas. Quelle est la combinaison qui permet d’atteindre le meilleur profit ? Attention, il ne faut pas utiliser plus d’articles que le nombre en stock! Utilise le solveur afin de déterminer le profit maximum réalisable. Détermine d’abord quelles sont les cellules variables et quelles sont les contraintes éventuelles pour ce problème.

Quand tu as déterminé la méthode pour maximiser le profit de ton entreprise, passe à l'étape suivante.

Maximisation des intérêts d’un dépôt en banque

Pour le calcul des intérêts composés sur un certain capital, on peut déterminer, chaque année, le montant des intérêts. Ce montant est ensuite ajouté au capital. L'année suivante, on reproduit le calcul avec le capital augmenté des intérêts. Et ainsi de suite, d'année en année.

L’illustration ci-contre représente l’évolution de la valeur d’un dépôt en banque à un taux d’intérêt composé déterminé: les cellules B1 et D1 sont variables ; les cellules B4 à B14 contiennent des formules qui permettent de déterminer la valeur du dépôt après chaque année. Construis cette feuille de calcul. Utilise ensuite le solveur pour déterminer le taux nécessaire pour obtenir 17 000 € au bout de 10 ans pour un dépôt de 10 000 €. En modifiant légèrement la feuille de calcul, il est possible de faire déterminer le taux nécessaire pour multiplier le capital de départ par un certain facteur multiplicatif. Il suffit alors de vérifier que l’écart par rapport à l’objectif fixé est nul.