Calculez malin à l'aide du Solveur
Utilisez le Solveur d'Excel pour obtenir la meilleure analyse de vos données...
Le Solveur est un outil d'analyse de données à utiliser dans une feuille Excel. Il permet de rechercher la valeur optimale d'une cellule en tenant compte des contraintes définies.Pour illustrer cette fonction, nous partons du scénario suivant : votre association veut vendre des pâtisseries lors d'un forum. Elle est limitée dans la quantité de certains ingrédients et souhaite connaître, avec ses éléments, le gain maximum qu'elle peut en retirer.
Brigitte Bourbé (Carm2i)Attention : le Solveur n'est pas installé automatiquement. C'est une macro complémentaire dont Excel a besoin.
Crêpes, pour 8 ?"ufs : 0,5 kg de farine et 1 litre de lait.
Gaufres : pour 6 ?"ufs, on a besoin de 0,5 kg de farine et 0,10 litre de lait.Tapez, respectivement dans les cellules B3 et B4, les valeurs 8 et 6.Les cellules C3 à D4 doivent tenir compte des proportions. Donc, pour valoriser les cellules C3 à D4 :
cellule C3, tapez =B3*0,5/8
cellule C4, tapez =B4*0,5/6
cellule D3, tapez =B3*1/8
cellule D4, tapez =B4*0,1/63 Le prix des ingrédients est de 0,15 euro l'?"uf, 1,5 euro le kg de farine et 1 euro le litre de lait.Voici comment renseigner les cellules E3 et E4 qui permettent de calculer le coût de chacune des recettes :
cellule E3, tapez =(B3*0,15)+(C3*1,5)+(D3*1)
cellule E4, tapez =(B4*0,15)+(C4*1,5)+(D4*1)4 On considère que le bénéfice est égal à deux fois le coût. Poursuivez alors ainsi :
cellule F3, tapez =B3*2
cellule F4, tapez =B4*25 Pour calculer le total des ?"ufs, cliquez dans la cellule B5 puis, dans la barre d'outils, cliquez deux fois sur le bouton Somme automatique :Recommencez l'opération pour les cellules C5 à F5.6 Pour comparer les données avec et sans contraintes, recopiez le tableau. Pour cela, cliquez dans la cellule A2 puis effectuez un cliquer-glisser jusqu'à la cellule F5. Appuyez ensuite successivement sur les touches Ctrl et C de votre clavier. Enfin, cliquez, ici, dans la cellule A8, et appuyez successivement sur les touches Ctrl et V de votre clavier.
laisser l'option Garder la solution du Solveur cochée et cliquez sur OK. Le tableau est mis à jour.
cocher l'option Rétablir les valeurs d'origine et cliquer sur OK. Le tableau reprend les valeurs initiales.
Brigitte Bourbé (Carm2i)Attention : le Solveur n'est pas installé automatiquement. C'est une macro complémentaire dont Excel a besoin.
Etape 1 : activez la macro
1 Ouvrez une feuille Excel puis, dans le menu Outils, choisissez l'option Macros Complémentaires :2 Dans la liste Macros complémentaires disponibles, cochez la case Complément Solveur :puis cliquez sur OK.Remarque : il se peut que le logiciel vous demande alors d'insérer le CD d'installation. Dans ce cas, suivez les instructions.Etape 2 : posez le problème
Le problème de notre exemple est le suivant :A partir d'une recette de crêpes et gaufres, déterminez quel bénéfice maximal on peut réaliser sachant que l'on dispose de 100 ?"ufs, 10 kg de farine et 10 litres de lait.1 Créez les en-têtes de colonnes et de lignes du tableau de l'illustration 1.2 Voici les proportions de farine et de lait par rapport au nombre d'?"ufs :Crêpes, pour 8 ?"ufs : 0,5 kg de farine et 1 litre de lait.
Gaufres : pour 6 ?"ufs, on a besoin de 0,5 kg de farine et 0,10 litre de lait.Tapez, respectivement dans les cellules B3 et B4, les valeurs 8 et 6.Les cellules C3 à D4 doivent tenir compte des proportions. Donc, pour valoriser les cellules C3 à D4 :
cellule C3, tapez =B3*0,5/8
cellule C4, tapez =B4*0,5/6
cellule D3, tapez =B3*1/8
cellule D4, tapez =B4*0,1/63 Le prix des ingrédients est de 0,15 euro l'?"uf, 1,5 euro le kg de farine et 1 euro le litre de lait.Voici comment renseigner les cellules E3 et E4 qui permettent de calculer le coût de chacune des recettes :
cellule E3, tapez =(B3*0,15)+(C3*1,5)+(D3*1)
cellule E4, tapez =(B4*0,15)+(C4*1,5)+(D4*1)4 On considère que le bénéfice est égal à deux fois le coût. Poursuivez alors ainsi :
cellule F3, tapez =B3*2
cellule F4, tapez =B4*25 Pour calculer le total des ?"ufs, cliquez dans la cellule B5 puis, dans la barre d'outils, cliquez deux fois sur le bouton Somme automatique :Recommencez l'opération pour les cellules C5 à F5.6 Pour comparer les données avec et sans contraintes, recopiez le tableau. Pour cela, cliquez dans la cellule A2 puis effectuez un cliquer-glisser jusqu'à la cellule F5. Appuyez ensuite successivement sur les touches Ctrl et C de votre clavier. Enfin, cliquez, ici, dans la cellule A8, et appuyez successivement sur les touches Ctrl et V de votre clavier.
Etape 3 : utilisez le Solveur
1 Cliquez dans la cellule F11 qui représente le total des bénéfices. Puis, dans le menu Outil, choisissez l'option Solveur.2 Dans la zone Cellule cible à définir, vous retrouvez la référence $F$11 :Le bénéfice devant être le plus grand possible, dans la zone Egale à, maintenez cochée l'option Max.3 La zone Cellules variables permet de définir les cellules devant être modifiées par le Solveur pour que la cellule cible (qui est définie au point 2 de cette étape) atteigne le résultat recherché tout en respectant les contraintes que vous allez définir.Cliquez ensuite dans la zone Cellules variables puis, suivant notre exemple, effectuez un cliquer-glisser, dans le tableau de la cellule B9 à B10. Vous obtenez alors :4 Pour définir une contrainte, cliquez sur le bouton Ajouter, à droite de la zone Contraintes.5 Pour définir la contrainte du nombre d'?"ufs maximal, cliquez dans la zone Cellule de la fenêtre qui s'ouvre puis, dans le tableau, sur la cellule B11. Puis cliquez sur la flèche de la zone <= et choisissez l'option =. Enfin, dans la zone Contraintes, tapez 100. Vous obtenez :puis cliquez sur Ajouter.Créez la contrainte pour la quantité maximale de farine comme suit :puis cliquez sur Ajouter.Recommencez ainsi pour la contrainte concernant la quantité maximale de lait :La quantité d'?"ufs doit être un nombre entier. Ajoutez alors la contrainte suivante : Après avoir sélectionné la cellule B9, dans la zone du milieu, choisissez l'option Ent. Vous obtenez alors :Ajoutez une contrainte identique pour la cellule B10. Lorsque toutes les contraintes ont été définies, cliquez sur OK. Elles apparaissent dans la zone Contraintes (voir l'illustration 2).6 Pour voir le résultat de la simulation, cliquez sur Résoudre. Le Solveur effectue les calculs et les affiche automatiquement dans le tableau. Une fenêtre informe qu'il a trouvé une solution. Selon le résultat obtenu, vous pouvez :laisser l'option Garder la solution du Solveur cochée et cliquez sur OK. Le tableau est mis à jour.
cocher l'option Rétablir les valeurs d'origine et cliquer sur OK. Le tableau reprend les valeurs initiales.