Charger le Solver Add-in | Formulez le modèle | Trial and Error | Résoudre le modèle

Excel comprend un outil appelé résolveur qui utilise les techniques des opérations de recherche pour trouver des solutions optimales pour toutes sortes de problèmes de décision.

Charger le Solveur

Pour charger complément solveur, exécutez les étapes suivantes.

  1. Dans l’onglet Fichier, cliquez sur Options.

  2. Sous Add-ins, sélectionnez Solveur et cliquez sur le bouton Go.

Click Solver Add-in

  1. Vérifiez Solveur puis cliquez sur OK.

Check Solver Add-in

  1. Vous pouvez trouver le solveur sur l’onglet Données, dans le groupe Analyser.

Click Solver

qu’élaborer modèle

Le modèle que nous allons résoudre se présente comme suit dans Excel.

Formulate the Model

  1. Pour formuler ce modèle de programmation linéaire, répondez aux trois questions suivantes.

\une. Quelles sont les décisions à prendre? Pour ce problème, nous avons besoin d’Excel pour savoir combien à l’ordre de chaque produit (bicyclettes, cyclomoteurs et sièges pour enfants).

\ B. Quelles sont les contraintes qui pèsent sur ces décisions? Les contraintes sont ici que le montant du capital et de stockage utilisé par les produits ne peut pas dépasser la quantité limitée de capital et de stockage (ressources) disponibles.

Par exemple, chaque bicyclette utilise 300 unités de capital et 0,5 unité de stockage.

\ C. Quelle est la mesure globale de la performance de ces décisions? La mesure globale de la performance est le profit total des trois produits, de sorte que l’objectif est de maximiser cette quantité.

  1. Pour rendre le modèle plus facile à comprendre, nommez les plages suivantes.

Range Name

Cells

UnitProfit

C4:E4

OrderSize

C12:E12

ResourcesUsed

G7:G8

ResourcesAvailable

I7:I8

TotalProfit

I12

  1. Insérez les trois fonctions suivantes SOMMEPROD.

Sumproduct Functions

Explication: Le montant du capital utilisé correspond à la SUMPRODUCT de la gamme C7: E7 et OrderSize. La quantité de mémoire utilisée est égale à la SUMPRODUCT de la gamme C8: E8 et OrderSize. Bénéfice total est égal à la SUMPRODUCT de UnitProfit et OrderSize.

Trial and Error

Avec cette formulation, il devient facile d’analyser toute solution d’essai.

Par exemple, si on commande 20 vélos, 40 cyclomoteurs et 100 sièges pour enfants, le montant total des ressources utilisées ne dépasse pas le montant des ressources disponibles. Cette solution a un bénéfice total de 19000.

Trial Solution

Il est pas nécessaire d’utiliser tâtonnement. Nous allons décrire ensuite comment Excel Solver peut être utilisé pour trouver rapidement la solution optimale.

Résoudre le modèle

Pour trouver la solution optimale, exécutez les étapes suivantes.

  1. Dans l’onglet Données, dans le groupe Analyser, cliquez sur Solver.

Click Solver

Entrez les paramètres du solveur (lire). Le résultat devrait être compatible avec l’image ci-dessous.

Solver Parameters

Vous avez le choix de taper les noms de plage ou en cliquant sur les cellules dans la feuille de calcul.

  1. Entrez TotalProfit pour l’objectif.

  2. Cliquez sur Max.

  3. Entrez OrderSize pour le changement des cellules variables.

  4. Cliquez sur Ajouter pour saisir la contrainte suivante.

Add Constraint

  1. Cochez la case ‘Make Variables non sans contrainte négative’ et sélectionnez ‘Simplex LP’.

  2. Enfin, cliquez sur Résoudre.

Résultat:

Solver Results

La solution optimale:

Optimal Solution

Conclusion: il est optimal de l’ordre de 94 vélos et 54 cyclomoteurs. Cette solution donne le bénéfice maximum de 25600. Cette solution utilise toutes les ressources disponibles.