INTRODUCTION

Excel inclut un solveur de programmation mathématique conçu par Frontline Systems pour Microsoft. Ce solveur permet de définir et résoudre des problèmes d’optimisation linéaires ou non-linéaires. Les variables peuvent être réelles, entières ou binaires. La fonction-objectif facultative peut être minimisée, maximisée ou atteindre une valeur-cible.
Il faut savoir que ce solveur est bridé à 200 variables. Le nombre de contraintes est illimité pour les PL, mais limité à 100 pour les PNL, sauf pour les contraintes simples de bornes sur les variables, comme x ≤ 3. Frontline Systems vend des versions non limitées qui se substituent à la version bridée offerte avec Excel, voir leur site web http://www.frontsys.com.
Un historique et des détails sur le fonctionnement interne figurent dans l’article :
D. Fylstra, L. Lasdon, J. Watson, A. Waren, Design and use of the Microsoft Excel solver, Interfaces, volume 28, n° 5, pages 29-55, 1998.
Le solveur n’est pas toujours présent dans les menus. Vérifiez dans le menu Outils si le choix Solveur est visible. Sinon, il faut l’installer avec le choix Macros complémentaires. Ensuite, un clic sur Outils/Solveur fait apparaître la boîte Paramètres du solveur :
Cette boite permet de définir les cellules de la feuille Excel qui correspondent à la fonction objectif (Cellule-cible à définir), aux variables (Cellules variables) et aux contraintes (Contraintes). On peut choisir le type d’optimisation : maximiser (Max), minimiser (Min) ou trouver une solution de coût égal à une valeur donnée (Valeur). Il est possible de choisir d’autres paramètres de résolution avec le bouton Options. En cliquant sur Résoudre, les valeurs des variables et de la fonction-objectif apparaissent dans les cellules correspondantes.

PARAMETRES DU SOLVEUR

La cellule-cible, unique, doit contenir une formule qui lie, directement ou indirectement, les variables pour définir la fonction-objectif. Elle est facultative : si elle est absente, le solveur cherche une solution vérifiant toutes les contraintes, ce qui permet de résoudre des systèmes d’équations linéaires ou non-linéaires.
Les cellules variables sont définies par une liste de cellules ou de plages, séparées par des points-virgules, par exemple B1:B4; C1:C4; E8. Elles ne doivent pas contenir de formules et il est conseillé de les initialiser à zéro.
Les contraintes sont du type {cellule | plage} opérateur {constante numérique | cellule | plage | formule}, les accolades avec barres verticales désignant des choix exclusifs. L’opérateur est =, <=, >=, ent ou bin. Rappelons qu’il ne peut pas y avoir d’opérateurs de différence ou d’inégalité stricte en programmation mathématique, à cause des problèmes de précision. Pour une plage de n cellules, une contrainte du type plage opérateur {constante | cellule | formule} équivaut à n contraintes dans lesquelles chaque cellule de la plage est comparée au second membre. Une contrainte peut avoir deux plages de même dimension : elle équivaut à n comparaisons entre les cellules de même rang des deux plages.
B4 <= 6 B4 inférieure ou égale à 6
B4:B5 <= C5 B4 et B5 inférieures à C5
B4:B5 = C6:C7 B4 inférieure ou égale à C6 et B5 inférieure ou égale à C7
B4 <= 3*SIN(C4)+2 B4 inférieure ou égale à 3.sin (C4) + 2
L’opérateur bin permet de spécifier qu’une cellule ou une plage doivent être binaires.
L’opérateur ent sert à définir des cellules ou plages entières. Les cellules concernées doivent évidemment être des variables. Pour ces deux opérateurs, le solveur met par défaut binaire et entier dans les seconds membres. Pour ent, on peut cependant préciser une valeur maximale.
B4 bin binaire B4 est binaire (le mot binaire est ajouté par défaut)
B4:B5 ent entier B4 et B5 sont entières (le mot entier est ajouté par défaut)
B4:B5 ent 6 B4 et B5 sont entières et inférieures ou égales à 6.
En cliquant sur le bouton Options du solveur, on peut spécifier que les variables sont positives ou nulles par défaut et dans ce cas on n’a pas besoin de taper les contraintes de positivité. Une variable peut aussi intervenir dans plusieurs contraintes :
B4 ent 6 B4 est un entier inférieur ou égal à 6
B4 >= 2 et supérieur ou égal à 2
On voit que le solveur n’autorise des formules que dans le second membre des contraintes.
Les formules pour les premiers membres des contraintes et la fonction-objectif doivent être définies dans des cellules Excel et lier des variables entre elles. Le solveur se contente donc d’appeler Excel quand il veut récupérer les valeurs de ces formules. Une conséquence est qu’il ne peut pas savoir si un modèle est linéaire. On doit le lui dire dans les options, en cochant la case Supposé linéaire. Sinon, le solveur utilisera par défaut un algorithme très lourd pour le cas non-linéaire!
La possibilité d’utiliser des plages dans les contraintes est très puissante : elle permet de créer des blocs de contraintes similaires au lieu d’entrer une contrainte séparée pour chaque cellule des plages. Mais pour cela, il faut se plier à la logique de présentation d’Excel (feuilles rectangulaires de cellules), ce qui est souvent contraignant.
Il faut donc toujours bien réfléchir à la disposition de votre PL (objectif, contraintes, variables, formules), en regroupant si possible les composants en plages, pour faciliter la définition du modèle avec le solveur et avoir une présentation plus lisible et agréable!
La démarche de conception et les principales fonctions du solveur vont être illustrées sur un problème de production de ciments. En cas de difficultés, consultez l’aide générale d’Excel, mot-clé Solveur, et les aides disponibles dans la boite Solveur et son sous-menu Options. Le rôle des différents paramètres du sous-menu Options est expliqué en section VII. Un classeur SOLVSAMP.XLS fourni avec Excel contient d’autres exemples utiles de modèles.

PRODUCTION DE CIMENTS – VERSION SIMPLE

Une usine produit 2 ciments rapportant 50$ et 70$/t. Pour 1 t de ciment 1, il faut 40 mn de four et 20 mn de broyage. Pour 1 t de ciment 2, 30 mn et 30 mn. Four et broyeur sont disponibles 6h et 8h par jour. Quelles quantités faire chaque jour pour maximiser le bénéfice ?
Ce problème peut se modéliser avec un PL avec deux variables continues : (voir tutoriel complet sur clicours.com)

…

Si le lien ne fonctionne pas correctement, veuillez nous contacter (mentionner le lien dans votre message)
Cours bureautique Excel (143.71 KB)  (Cours PDF)