Résolution LP avec Excel

Add-on

Pour résoudre le programme linéaire avec Excel, vous avez besoin du module Solveur. Dans l’onglet Fichier, cliquez sur Options. Sous Compléments, sélectionnez Complément Solveur et cliquez sur le bouton OK. Vérifiez le complément du solveur et cliquez sur OK. Si la manœuvre ne fonctionne pas, vous pouvez cliquer sur Solveur puis Atteindre.

Vous trouverez le solveur sur l’onglet Données dans le groupe Analyse, comme indiqué dans l’image suivante.

LP19

Ecrire un LP avec Excel

Pour formuler un problème de programmation linéaire, tenez compte des trois questions suivantes :

  1. Quelles sont les décisions à prendre ? Pour résoudre ce problème, Excel doit déterminer le montant de chaque produit à commander (vélos, cyclomoteurs et sièges enfants).
  2. Quelles sont les contraintes sur ces décisions ? Les contraintes ici sont que la quantité de capital et de stockage utilisée par les produits ne peut pas dépasser la quantité limitée de capital et de stockage (ressources) disponibles. Par exemple, chaque vélo utilise 300 unités de capital et 0,5 unité de stockage.
  3. Quelle est la mesure globale de la performance pour ces décisions ? La mesure globale de la performance est le bénéfice total des trois produits. L’objectif est donc de maximiser cette quantité.

Dans l’exemple suivant, les variables de décision sont en jaune, les contraintes en bleu clair et la fonction objectif en bleu foncé.

LP20

Sélectionnez chaque plage et sous l’onglet Formules, dans le groupe Noms définis, cliquez sur Définir un nom:

Range Name Cells
UnitProfit C4:E4
OrderSize C12:E12
ResourcesUsed G7:G8
ResourcesAvailable I7:I8
TotalProfit I12

Insérer la fonction SUMPRODUCT suivante (afin de créer des contraintes et des fonctions objectif) :

LP21

Résoudre le modèle

Pour trouver la solution optimale, exécutez les étapes suivantes (vous devez adapter chaque étape à votre modèle).

  • Sous l’onglet Données, dans le groupe Analyse, cliquez sur Solveur.
  • Entrez les paramètres du solveur.
  • Vous avez le choix de saisir les noms de plage ou de cliquer sur les cellules de la feuille de calcul. Entrez le bénéfice total pour l’objectif.
  • Cliquer sur Max.
  • Entrer le sens des inégalités pour les variables
  • Ajouter les contraintes selon ce procédé
  • Cochez la case « Définir les variables non contraintes non négatives » et sélectionnez « Simplex LP ».
  • Cliquer sur Résoudre

Le solveur remplit le tableau avec une solution optimale.

LP22

Publicités