Programmation linéaire Excel (Table des matières)
- Introduction à la programmation linéaire dans Excel
- Méthodes pour résoudre la programmation linéaire via Excel Solver
Introduction à la programmation linéaire dans Excel
La programmation linéaire est la plus importante ainsi qu'un aspect fascinant des mathématiques appliquées qui aide à l'optimisation des ressources (minimiser les pertes ou maximiser le profit avec des ressources données). Si nous avons des contraintes et la fonction objectif bien définie, nous pouvons utiliser le système pour prédire une solution optimale pour un problème donné. Dans Excel, nous avons Excel Solver qui nous aide à résoudre les problèmes de programmation linéaire aka LPP. Nous verrons dans cet article comment utiliser Excel Solver pour optimiser les ressources associées aux problèmes métier à l'aide de la programmation linéaire.
Commençons par le commencement. Voyons comment nous pouvons activer Excel Solver (un composant clé de LPP sous Excel).
Méthodes pour résoudre la programmation linéaire via Excel Solver
Voyons comment utiliser la programmation linéaire via le solveur Excel avec certaines méthodes.
Vous pouvez télécharger ce modèle Excel de programmation linéaire ici - Modèle Excel de programmation linéaire
Méthode n ° 1 - Activation du solveur sous Microsoft Excel
Dans Microsoft Excel, nous pouvons trouver le solveur sous l'onglet Données qui se trouve sur le ruban Excel placé dans la partie supérieure, comme indiqué ci-dessous:
Si vous ne pouvez pas voir cet utilitaire là-bas, vous devez l'activer via les options Excel. Suivez les étapes ci-dessous pour activer le solveur sous Excel.
Étape 1: Naviguez vers le menu Fichier et cliquez sur Options, qui est la dernière chose sur cette liste.
Étape 2: Une nouvelle fenêtre apparaîtra intitulée Options Excel. Cliquez sur Compléments dans la liste des options présentes sur le côté gauche de la fenêtre.
Étape 3: Sous la section Gérer au bas de la fenêtre, sélectionnez Compléments Excel dans la liste déroulante et cliquez sur le bouton Aller… placé à côté.
Étape 4: Dès que vous cliquez sur le bouton Aller…, vous pourrez voir la liste de tous les compléments disponibles sous Excel dans une nouvelle fenêtre. Cochez pour sélectionner le complément Solveur afin de pouvoir l'utiliser sous l'onglet Données pour résoudre les équations. Cliquez sur le bouton OK après avoir sélectionné le complément Solveur.
De cette façon, vous pouvez activer le solveur Excel sous Microsoft Excel.
Méthode n ° 2 - Résolution d'un problème de programmation linéaire à l'aide d'Excel Solver
Maintenant, nous allons essayer de résoudre le problème de programmation linéaire en utilisant l'outil Excel Solver.
Exemple: Une usine chimique produit deux produits, à savoir A et B. Ces deux produits ont besoin de matières premières comme indiqué ci-dessous: Le produit A a besoin de trois types de matières premières - Material_1 20KG, Material_2 30KG, Material_3 comme 5 KG. Sur des lignes similaires, le produit B nécessite 10 kg de matériau_1, 30 kg de matériau_2 et 10 kg de matériau_3. Le fabricant requiert un minimum de 460KG ou Material_1, 960KG de Material_2 et 220KG de Material_3. Si le coût unitaire du produit A est de 30 $ et le coût du produit B de 35 $, combien de produits le fabricant doit-il mélanger pour répondre aux exigences minimales en matière de matériaux au coût le plus bas possible? Utilisons les informations fournies dans cet exemple pour modéliser les équations.
Étape 1: Nous pouvons voir toutes les contraintes d'équation que nous pouvons former en utilisant les informations fournies dans l'exemple ci-dessus.
Étape 2: utilisez ces équations pour ajouter les contraintes cellule par cellule sous Excel sur A2: C8 de la feuille donnée. Voir la capture d'écran ci-dessous:
Étape 3: Maintenant, nous devons utiliser la formule Quantité * Par coût unitaire et la résumer pour les deux produits afin d'obtenir les besoins réels en matériaux. Vous pouvez le voir sous la colonne D pour toutes les cellules qui contiennent des contraintes B3, B4, C3). Veuillez voir la capture d'écran ci-dessous:
Si vous regardez de plus près cette formule, nous avons utilisé B3 et C3 en tant que membres fixes pour chaque formule dans les différentes cellules de la colonne D.En effet, B3 et C3 sont les cellules désignant les quantités pour le produit A et le produit B respectivement. Ces quantités apparaîtront une fois le système d'équations résolu à l'aide d'Excel Solver.
Étape 4: Cliquez sur l'onglet Données, puis sur Solveur qui est présent dans la section Analyser de l'onglet.
Étape 5: Une fois que vous avez cliqué sur Solver, un nouvel onglet nommé «Solver Parameter» s'ouvrira sous lequel vous devez définir les paramètres pour cet ensemble d'équations à résoudre.
Étape 6: La première chose que nous devons identifier est de fixer un objectif: étant donné que notre objectif est de déterminer le coût total impliqué afin qu'il puisse être minimisé, définissez-le sur D4.
Étape 7: Étant donné que nous devons minimiser le coût avec la production la plus élevée possible, définissez le paramètre suivant sur Min. Vous pouvez le faire en cliquant sur le bouton radio Min.
Étape 8: sous En changeant les cellules variables:, nous devons mentionner B3 et C3 car ces cellules seront respectivement celles contenant les quantités pour le produit A et le produit B une fois le problème résolu.
Étape 9: Ajoutez maintenant les contraintes. Cliquez sur le bouton Ajouter sous la section Sujet aux contraintes: et cela ouvrira une nouvelle fenêtre pour ajouter des contraintes. Sous cette fenêtre - B3: C3 comme référence de cellule, > = et 0 comme contraintes. C'est ce que nous faisons, puisque la contrainte de base dans tout LPP est que, X et Y doivent être supérieurs à zéro.
Étape 10: Cliquez à nouveau sur le bouton Ajouter et utilisez cette fois B3: C3 comme référence de cellule et F6: F8 comme contraintes avec une inégalité comme> =. Cliquez sur le bouton OK pour ajouter cette contrainte également sous le solveur.
Le solveur possède désormais tous les paramètres nécessaires pour résoudre cet ensemble d'équations linéaires et il se présente comme suit:
Étape 11: Maintenant, cliquez sur le bouton Résoudre en bas de la fenêtre pour résoudre cette équation linéaire et arriver à la solution optimale.
Dès que nous cliquons sur le bouton résoudre, le système commence à rechercher la solution optimale pour le problème que nous avons fourni et nous obtenons les valeurs pour B3, C3 à l'aide desquelles nous obtenons également les valeurs dans la colonne F pour F4, F6: F8. Quels sont les coûts optimaux et les valeurs des matériaux qui peuvent être utilisés pour le produit A et le produit B.
Cette solution nous informe que, si nous devons minimiser le coût de production du produit A et du produit B avec une utilisation optimale de Material_1, Material_2 et Material_3, nous devons produire 14 quantités de produit A et 18 quantités de produit B.
C'est de cet article. Terminons les choses avec quelques points à retenir:
Choses à retenir sur la programmation linéaire dans Excel
- Il est obligatoire de résoudre les problèmes de programmation linéaire à l'aide d'Excel Solver. Il n'y a pas d'autre méthode que nous pouvons utiliser pour cela.
- Nous devons toujours avoir des contraintes et des variables d'objet à définir avec nous.
- Si le solveur n'est pas activé, vous pouvez l'activer dans les options du complément Excel.
Articles recommandés
Ceci est un guide de programmation linéaire dans Excel. Nous discutons ici Comment utiliser la programmation linéaire dans Excel avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -
- Interpoler dans Excel
- Programmation dans Excel
- Déplacement de colonnes dans Excel
- Matrice inverse dans Excel