Calculez malin à l'aide du Solveur

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.

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.

Etape 4 : créez un rapport

Dans notre exemple, nous avons copié le tableau initial pour pouvoir comparer avec la simulation. Il est également possible d'éditer un rapport qui présente la cellule cible et les cellules variables avec leurs valeurs d'origine et finales.1 Reprenez les points de l'étape 3. Vous constatez que le Solveur a gardé en mémoire les contraintes précédemment définies. Cliquez alors sur Résoudre.2 Dans la fenêtre vous informant que le Solveur a trouvé un résultat, laissez l'option Garder la solution du solveur cochée et cliquez, dans la zone Rapports, sur l'option Réponses,puis sur OK.3 Le Solveur a créé un rapport dans une nouvelle feuille de calcul. Pour le consulter, cliquez, dans la partie basse de l'écran, sur l'onglet nommé Rapport des réponses suivi du numéro du rapport, ici 1 :4 Le tableau affiche en haut le nom du classeur, la date du rapport, la référence des cellules cibles et variables et rappelle les contraintes définies (voir l'illustration 3). Pour éditer le rapport, cliquez sur le bouton Imprimer.

Etape 5 : sauvegardez le résultat

Vous pouvez créer des simulations et sauvegarder leurs résultats. Pour enregistrer la simulation de notre exemple, continuez comme suit.1 Reprenez le point 1 de l'étape 3 pour retrouvez les contraintes de notre exemple. Cliquez sur le bouton Résoudre. Puis, dans la fenêtre qui s'affiche, cliquez sur Enregistrer le scénario.Tapez le nom souhaité dans la zone Nom du scénario, par exemple Max100?"ufs. Validez en cliquant sur OK et de nouveau sur OK.2 Créez une nouvelle contrainte en reprenant les points de 1 à 3 de l'étape 3. Avant d'effectuer le point 4, nous vous conseillons de supprimer les contraintes existantes en cliquant, dans la liste des contraintes, sur leur libellé puis sur le bouton Supprimer. Définissez les nouvelles contraintes comme au point 5. Au point 6, choisissez l'option Garder la solution du solveur et enregistrez le scénario sous le nom Max266?"ufs. Votre tableau a été modifié avec le résultat de cette nouvelle simulation.3 Dans le menu Outils, choisissez l'option Gestionnaire de scénarios.4 La fenêtre qui s'ouvre présente la liste des scénarios créés. Cliquez sur le nom du scénario dont vous souhaitez retrouver les résultats, ici Max100?"ufs :puis cliquez sur le bouton Modifier. Cliquez ensuite sur le bouton OK de la nouvelle fenêtre.5 Dans la fenêtre Valeurs de scénarios, vérifiez que les valeurs proposées sont bien, selon vos besoins, des nombres entiers ou des décimaux ayant une virgule (et non un point) comme séparateur. Par exemple, si vous voyez 119.995,remplacez la valeur en tapant 119,995 ou 120.Validez en cliquant sur OK. Affichez le résultat de la simulation en cliquant sur Afficher. Ici, le résultat de la simulation de notre exemple saffiche alors dans le tableau.