Chapitre 17 · 17.22
Le Solveur : calculer la meilleure combinaison ou planification
La recherche de valeur cible (voir Calculer à rebours avec Valeur cible : quel prix donne 40 % de marge ?) modifie une seule cellule jusqu'à obtenir le résultat voulu. Mais vous avez souvent plusieurs paramètres à ajuster, ainsi que des limites à respecter. Combien de pains et de tartes faut-il cuire le samedi pour un bénéfice maximal, si le four ne tourne que 16 heures et que vous n'avez que 120 kilos de farine ? C'est à ce type de question que répond le Solveur. Peu de gens savent qu'Euro-Office en dispose.
L'exemple : la production du samedi
La boulangerie De Korenaar présente le problème ainsi dans une feuille de calcul :
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Pain | Tarte | Maximum | ||
| 2 | Bénéfice par pièce | 1,20 | 6,50 | ||
| 3 | Temps de four (min) | 6 | 25 | ||
| 4 | Farine (kg) | 0,5 | 0,3 | ||
| 5 | Quantité | 0 | 0 | ||
| 7 | Bénéfice total | =SOMMEPROD(B2:C2;B5:C5) | |||
| 8 | Temps de four utilisé | =SOMMEPROD(B3:C3;B5:C5) | 960 | ||
| 9 | Farine utilisée | =SOMMEPROD(B4:C4;B5:C5) | 120 | ||
| 10 | Tartes au maximum | =C5 | 25 |
La ligne 5 contient les quantités que le Solveur peut choisir ; vous commencez avec des zéros. SOMMEPROD multiplie par produit puis additionne : bénéfice par pièce fois quantité, temps de four par pièce fois quantité, et ainsi de suite. La colonne E contient les limites : 960 minutes de temps de four, 120 kilos de farine et 25 tartes au plus, car il n'y a pas davantage de commandes.
Pas à pas
- Dans l'onglet Données, cliquez sur Solveur. La fenêtre Paramètres du Solveur s'ouvre.
- Dans Définir l'objectif, choisissez la cellule contenant le bénéfice total : B7. Dans À, choisissez Max : le bénéfice doit être le plus élevé possible.
- Dans En modifiant les cellules variables, choisissez les quantités : B5:C5.
- Dans Soumis aux contraintes, cliquez sur Ajouter. Dans la fenêtre Ajouter une contrainte, saisissez B8 dans Référence de cellule, choisissez <= et saisissez E8 dans Contrainte. Cliquez sur Ajouter pour la contrainte suivante, ou sur OK lorsque vous avez terminé.
- Ajoutez de la même manière B9 <= E9 (farine) et C5 <= E10 (tartes).
- Laissez Rendre les variables non contraintes non négatives coché : ainsi, le Solveur ne choisit pas de quantités négatives. La Méthode de résolution est Simplex LP.
- Cliquez sur Résoudre.


Au bout d'un instant, la fenêtre Résultats du Solveur apparaît : « Le Solveur a trouvé une solution. Toutes les contraintes et conditions d'optimalité sont satisfaites. » Choisissez Conserver la solution trouvée et cliquez sur OK. Avec Restaurer les valeurs d'origine, les quantités reviennent à leurs valeurs précédentes.

Le résultat : 25 tartes et 55,83 pains, pour 229,50 euros de bénéfice. Le four est alors exactement plein ; il reste suffisamment de farine. Les tartes rapportent davantage par minute de temps de four que le pain : le Solveur cuit donc d'abord autant de tartes que de commandes, puis remplit le reste du four avec du pain.

Ce que le Solveur d'Euro-Office ne sait pas faire
- Uniquement des problèmes linéaires : la seule méthode est Simplex LP. Les méthodes GRG non linéaire et Évolutionnaire d'Excel ne sont pas disponibles ; Euro-Office indique lui-même qu'elles ne sont pas encore prises en charge. Votre cellule objectif et vos contraintes ne peuvent donc qu'additionner et multiplier par des nombres fixes. SI, MAX ou la multiplication de deux cellules variables entre elles ne fonctionnent pas.
- Impossible d'imposer des nombres entiers : une contrainte ne peut être que <=, = ou >=. Les options int (nombre entier) et bin (0 ou 1) d'Excel sont absentes. C'est pourquoi vous obtenez 55,83 pains. Arrondissez vous-même à l'entier inférieur (55) et vérifiez que toutes les contraintes sont toujours respectées ; en arrondissant vers le bas, c'est généralement le cas.

Pièges à éviter
- La cellule objectif doit être une formule qui dépend (via d'autres cellules) des cellules variables, et elle doit se trouver sur la feuille de calcul active.
- Si vous rouvrez le Solveur, l'objectif, les variables et les contraintes y figurent toujours. Avec Tout réinitialiser, vous repartez de zéro.
