Office
Se connecter

À l’aide du solveur pour déterminer la combinaison optimale de produits

Remarque :  Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Cet article traite de l’aide du solveur, un complément Microsoft Excel vous pouvez utiliser pour effectuer une analyse what-if pour déterminer une gamme optimale de produits.

Comment puis-je déterminer la combinaison produit mensuel qui optimise la rentabilité ?

Les entreprises doivent souvent déterminer la quantité de chaque produit pour générer sur une base mensuelle. Dans sa forme la plus simple, le problème de gammes de produits implique comment déterminer la quantité de chaque produit qui doit être produit au cours d’un mois pour optimiser les bénéfices. Assortiment de produits doit généralement respecter les contraintes suivantes :

  • Assortiment de produits Impossible d’utiliser plus de ressources que sont disponibles.

  • Il existe une demande limitée pour chaque produit. Nous ne pouvons pas fournir plus d’un produit au cours d’un mois à la demande détermine, parce que la production excédentaires est perdue (par exemple, un médicaments périssables).

Nous allons maintenant résoudre le problème de combinaison produit l’exemple suivant. Vous pouvez trouver la solution à ce problème dans le fichier Prodmix.xlsx, indiqué dans la Figure 27-1.

Image représentant un livre
Figure 27-1, la combinaison de produit

Imaginons que nous travaillons société médicaments produisant six différents produits dans son usine. Fabrication de chaque produit requiert travaillées et matière. Ligne 4 Figure 27-1 indique les heures de travail pour générer un livre de chaque produit et ligne 5 affiche les livres de matière nécessaires pour produire un livre de chaque produit. Par exemple, produire un livre de produit 1 nécessite six heures de travail et 3,2 kg de matière. Pour chaque médicaments, le prix dièse est indiquée à la ligne 6, le coût unitaire par dièse est indiquée à la ligne 7 et la marge sur variable par dièse est indiquée à la ligne 9. Par exemple, produit 2 vend 11,00 euros par dièse, implique un coût unitaire de 5,70 par dièse et contribue bénéficiaire de 5,30 par dièse. À la demande de mois pour chaque médicaments est indiquée à la ligne 8. Par exemple, à la demande pour le produit 3 est livres 1041. Ce mois-ci, 4 500 heures de travail et 1 600 livres de matière première sont disponibles. Comment cette société peut-elle maximiser ses profits mensuels ?

Si nous auraient rien sur le solveur Excel, nous serait attaquer ce problème en créant une feuille de calcul pour effectuer le suivi de l’utilisation des bénéfices et des ressources associées à la combinaison de produit. Puis nous devons utiliser la version d’évaluation et d’erreurs pour faire varier la combinaison de produit pour optimiser les profits sans utiliser plus travaillées ou matière qu’il n’est disponible et sans produire médicaments supérieure à la demande. Nous utilisons du solveur dans ce processus uniquement à la version d’évaluation et d’erreur de partage. Le Solveur est principalement un moteur d’optimisation infaillible la recherche de la version d’évaluation et d’erreur.

Une clé pour résoudre le problème de combinaison produit consiste à calculer efficacement l’utilisation des ressources et profits associés à toute la gamme de produits donnée. Un outil important que nous pouvons utiliser pour effectuer ce calcul est la fonction SOMMEPROD. La fonction SOMMEPROD Multiplie les valeurs correspondantes dans les plages de cellules et renvoie la somme de ces valeurs. Chaque plage de cellules utilisée dans une évaluation SOMMEPROD doit avoir les mêmes dimensions, ce qui signifie que vous pouvez utiliser la fonction SOMMEPROD avec deux lignes ou deux colonnes, mais pas avec une colonne et une seule ligne.

Comme l’illustre comment nous pouvons utiliser la fonction SOMMEPROD dans notre produit mélanger exemple, essayons de calculer l’utilisation des ressources. Notre utilisation de travail est calculée par

*(Drug 1 pounds produced) (main de œuvre par dièse de médicaments 1) +
(main de œuvre par dièse de médicaments 2) * (2 livres produites de médicaments) +...
(Main de œuvre par dièse de médicaments 6) * (6 livres produites de médicaments)

Nous pouvons calculer l’utilisation de travail de manière plus complexe comme D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. De même, l’utilisation de matières premières pu être calculée comme D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Toutefois, la saisie de ces formules dans une feuille de calcul pour les six produits est beaucoup de temps. Imaginons que la durée nécessaire si vous utilisiez une société qui a produit, par exemple, 50 produits dans son usine. Une manière pour calculer le travail et l’utilisation de matières premières beaucoup plus simple consiste à copier à partir de D14 à D15 la formule SUMPRODUCT($D$2:$I$2,D4:I4). Cette formule calcule D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (qui est notre utilisation travaillées) mais est beaucoup plus facile à saisir ! Notez que j’utilise le signe $ avec la plage D2 : I2 afin que lorsque je copie la formule je capturer toujours la combinaison de produit à partir de la ligne 2. La formule dans la cellule D15 calcule l’utilisation de matières premières.

De la même manière, profits est déterminé par

(1 profit par dièse de médicaments) * (produites livres médicaments 1) +
(profit médicaments 2 par dièse) * (2 livres produites de médicaments) +...
(6 médicaments profit par dièse) * (6 livres produites de médicaments)

Profits sont facilement calculés dans la cellule D12 avec la formule SUMPRODUCT(D9:I9,$D$2:$I$2).

Nous pouvons à présent identifier les trois composants de notre gamme de produits modèle du solveur.

  • Cellule cible. Notre objectif est d’optimiser vos bénéfices (calculés dans la cellule D12).

  • Cellules variables. Le nombre de livres produit de chaque produit (figurant dans la plage de cellules D2 : I2)

  • Contraintes. Nous avons créé les contraintes suivantes :

    • N’utilisez pas plus de travail ou de matière qu’il n’est disponible. Autrement dit, les valeurs de cellules D14 : D15 (les ressources utilisées) doivent être inférieure ou égale aux valeurs des cellules F14 : F15 (ressources disponibles).

    • Ne produisent pas plus d’un médicaments supérieur à la demande. Autrement dit, les valeurs dans les cellules D2 : I2 (livres produits de chaque médicaments) doivent être inférieure ou égale à la demande pour chaque médicaments (répertoriées dans les cellules D8 : I8).

    • Nous ne pouvons pas produire une quantité négative de médicaments.

Je vais vous montrer comment entrer la cellule cible, cellules variables et les contraintes dans le solveur. Il vous souhaitez est puis cliquez sur le bouton résoudre pour rechercher une gamme de produits capable de maximiser vos bénéfices !

Pour commencer, cliquez sur l’onglet données, puis dans le groupe analyse, cliquez sur Solveur.

Remarque : Comme expliqué au chapitre 26, « Une Introduction à l’optimisation avec Excel du solveur, » Solveur est installé en cliquant sur le bouton Microsoft Office, puis Options Excel, suivi de compléments. Dans la liste gérer, cliquez sur Compléments Excel, case à cocher complément Solver, puis cliquez sur OK.

La boîte de dialogue Paramètres du Solveur apparaît, comme illustré dans la Figure 27-2.

Image représentant un livre
Boîte de dialogue Paramètres du solveur la figure 27-2

Cliquez sur la zone cellule cible à définir, puis sélectionnez cellule des profits (cellule D12). Cliquez sur la zone cellules variables, puis pointez à la plage D2 : I2, qui contient les livres produits de chaque médicaments. La boîte de dialogue doit maintenant ressembler Figure 27-3.

Image représentant un livre
Boîte de dialogue Paramètres du solveur la figure 27-3 avec la cellule cible et les cellules variables définies

Nous sommes maintenant prêts à ajouter des contraintes au modèle. Cliquez sur le bouton Ajouter. Vous voyez la boîte de dialogue Ajouter une contrainte illustrée dans la Figure 27-4.

Image représentant un livre
Boîte de dialogue Ajouter une contrainte The figure 27-4

Pour ajouter les contraintes de l’utilisation des ressources, cliquez sur la zone référence de cellule et puis sélectionnez la plage D14 : D15. Sélectionnez < = à partir de la liste du milieu. Cliquez sur la zone contrainte, puis sélectionnez la plage de cellules F14 : F15. La boîte de dialogue Ajouter une contrainte doit maintenant ressembler comme Figure 27-5.

Image représentant un livre
Boîte de dialogue Ajouter une contrainte The figure 27-5 avec les contraintes de l’utilisation des ressources saisies

Nous avons désormais la garantie que lorsque le solveur tente de valeurs différentes pour la modification des cellules, seules les combinaisons qui remplissent les D14 < = F14 (travail utilisée est inférieure ou égale à travail disponible) et D15 < = F15 (matière première utilisée est inférieure ou égale à matière première disponible) sera considérée. Cliquez sur Ajouter pour entrer les contraintes de demande. Renseignez la boîte de dialogue Ajouter une contrainte, comme illustré dans la Figure 27-6.

Image représentant un livre
Boîte de dialogue Ajouter une contrainte The figure 27-6 avec les contraintes de demande saisies

Ajout de ces contraintes garantit que lorsque le solveur tente de combinaisons différentes pour les valeurs des cellules variables, seules les combinaisons qui satisfont les paramètres suivants sont considérés :

  • D2 < = D8 (la quantité produite de médicaments 1 est inférieure ou égale à la demande de médicaments 1)

  • E2 < = E8 (la quantité de produit de 2 médicaments est inférieure ou égale à la demande de médicaments 2)

  • F2 < = F8 (la quantité produite de 3 médicaments apportées est inférieure ou égale à la demande de médicaments 3)

  • G2 < = G8 (la quantité produite de médicaments 4 apportées est inférieure ou égale à la demande de médicaments 4)

  • H2 < = H8 (la quantité produite de médicaments 5 apportées est inférieure ou égale à la demande de médicaments 5)

  • I2 < = I8 (la quantité produite de médicaments 6 apportées est inférieure ou égale à la demande de médicaments 6)

Cliquez sur OK dans la boîte de dialogue Ajouter une contrainte. La fenêtre du solveur doit se présenter comme Figure 27-7.

Image représentant un livre
Figure 27-7 la boîte de dialogue Paramètres du solveur finale pour le problème de gamme de produits

Nous Entrez la contrainte cellules variables doivent être non négatives dans la boîte de dialogue Options du solveur. Cliquez sur le bouton Options dans la boîte de dialogue Paramètres du solveur. Cochez la case modèle supposé linéaire et la zone part du principe Non négatif, comme illustré Figure 27-8 sur la page suivante. Cliquez sur OK.

Image représentant un livre
Options du solveur figure 27-8

Cochez la case Non négatif part du principe garantit que le Solveur tient compte seules les combinaisons de changement de cellules dans lesquelles chaque cellule variable admet une valeur négative. Nous coché la case modèle supposé linéaire parce que le produit associer le problème est un type spécial de problème du solveur appelé modèle linéaire. Pour l’essentiel, un modèle du solveur est linéaire dans les conditions suivantes :

  • La cellule cible est calculée en additionnant les termes du formulaire (modification cellule.

  • Chaque contrainte satisfait les « exigences du modèle linéaire ». Cela signifie que chaque contrainte est évaluée en ajoutant ensemble les conditions du formulaire (modification cellule et en comparant les sommes à une constante.

Pourquoi ce problème du solveur est linéaire ? Notre cellule cible (profits) est calculée comme étant

(1 profit par dièse de médicaments) * (produites livres médicaments 1) +
(profit médicaments 2 par dièse) * (2 livres produites de médicaments) +...
(6 médicaments profit par dièse) * (6 livres produites de médicaments)

Ce calcul suit un modèle dans lequel la valeur de la cellule cible est extraite en ajoutant ensemble les conditions du formulaire (modification cellule.

Notre contrainte de travail est évaluée en comparant la valeur dérivée de (main de œuvre par dièse de médicaments 1) * (produites livres médicaments 1) + (main de œuvre par dièse de médicaments 2) *(Drug 2 pounds produced) +... (Temps noused par dièse de médicaments 6) * (6 livres produites de médicaments) pour le travail disponible.

Par conséquent, la contrainte de travail est évaluée en ajoutant ensemble les conditions du formulaire (modification cellule et en comparant les sommes à une constante. La contrainte de travail et la contrainte matière répondre aux exigences du modèle linéaire.

Nos contraintes de demande prennent la forme

(Médicaments 1 produite) < = (demande médicaments 1)
(2 médicaments produite) < = (demande médicaments 2)
§
(6 médicaments produites) < = (demande médicaments 6)

Chaque contrainte de demande satisfait également les exigences du modèle linéaire, car chaque est évaluée en ajoutant ensemble les conditions du formulaire (modification cellule et en comparant les sommes à une constante.

Ayant justifié que notre modèle de combinaison de produit est un modèle linéaire, pourquoi devons nous soucier ?

  • Si un modèle du solveur est linéaire et que nous sélectionnons modèle supposé linéaire, le Solveur est certain de trouver la solution optimale pour le modèle du solveur. Si un modèle du solveur n’est pas linéaire, le Solveur peut ou peut ne pas trouve la meilleure solution.

  • Si un modèle du solveur est linéaire et nous sélectionnez Modèle supposé linéaire, le solveur utilise un algorithme très efficace (méthode simplex) pour rechercher la solution optimale du modèle. Si un modèle du solveur est linéaire et que nous ne sélectionnez pas de modèle supposé linéaire, le solveur utilise un algorithme très inefficace (méthode GRG2) et peut avoir des difficultés à trouver la solution optimale du modèle.

Après avoir cliqué sur OK dans la boîte de dialogue Options du solveur, nous revenir à la boîte de dialogue Solveur principale, indiquée précédemment Figure 27-7. Lorsque vous cliquez sur résoudre, le Solveur calcule une solution optimale (le cas échéant) pour notre modèle de combinaison de produit. J’ai indiqué dans chapitre 26, une solution optimale pour le modèle de combinaison produit serait un ensemble de la modification des valeurs de cellule (livres produits de chaque médicaments) qui optimise la marge sur le jeu de toutes les solutions réalisables. Là encore, une solution réalisable est un ensemble de la modification des valeurs de cellules répondant à toutes les contraintes. Valeurs des cellules variables montre la Figure 27-9 sont une solution réalisable car tous les niveaux de production sont non négatifs, niveaux de production ne dépassent pas à la demande et utilisation des ressources ne dépasse pas les ressources disponibles.

Image représentant un livre
Solution réalisable figure 27-9 sur le produit gamme problème conforme aux contraintes.

Valeurs des cellules variables montre la Figure 27-10 sur la page suivante constituent une solution impossible pour les raisons suivantes :

  • Nous produisent plus de 5 médicaments à la demande de celui-ci.

  • Nous utilisons travaillées plus que celle disponible.

  • Nous utilisons matière plus que celle disponible.

Image représentant un livre
Figure 27-10 une solution impossible à problème de gamme de produit ne peut pas contenir les contraintes définis.

Après avoir cliqué sur résoudre, le Solveur recherche rapidement la solution optimale décrite dans la Figure 27-11. Vous devez sélectionner garder la Solution du solveur pour conserver les valeurs de la solution optimale dans la feuille de calcul.

Image représentant un livre
Figure 27-11 la solution optimale au problème de gamme de produit

Notre société médicaments peut maximiser ses profits mensuels à un niveau de la hauteur de 6 625,20 $ en produisant 596,67 livres de médicaments 4, 1084 livres de médicaments 5 et aucun les autres médicaments ! Nous ne pouvons pas déterminer si nous pouvons obtenir le bénéfice maximal de hauteur de 6 625,20 $ par d’autres moyens. Nous pouvons n’oubliez pas d’est que nos ressources limitées et à la demande, ne soit aucun moyen d’effectuer plus de $6,627.20 ce mois.

Supposons qu’à la demande pour chaque produit doit être remplie. (Voir la feuille de calcul Solution réalisable non dans le fichier Prodmix.xlsx.) Nous avons alors modifier nos contraintes de demande à partir de D2 : I2 < = D8 : I8 à D2 : I2 > = D8 : I8. Pour ce faire, ouvrez le solveur, sélectionnez la D2 : I2 < = D8 : I8 contrainte, puis cliquez sur Modifier. La boîte de dialogue Modifier une contrainte montre la Figure 27-12, s’affiche.

Image représentant un livre
Boîte de dialogue Modifier une contrainte The figure 27-12

Sélectionnez > =, puis cliquez sur OK. Nous avons désormais la garantie que le Solveur tient compte uniquement les valeurs de cellules qui répondent à toutes les demandes de modification. Lorsque vous cliquez sur résoudre, vous verrez le message « Solveur ne peut pas trouver une solution réalisable ». Ce message n’est pas signifie que nous avons fait une erreur dans notre modèle, mais plutôt qu’avec nos ressources limitées, nous ne pouvons pas répondre à la demande pour tous les produits. Le Solveur nous signale simplement que si nous voulons répondre à la demande pour chaque produit, nous devons ajouter plus de travail, plus matières premières ou les deux.

Voyons que se passe-t-il si nous autoriser à la demande illimitée pour chaque produit et nous autoriser les quantités négatives à produire pour chaque médicaments. (Vous pouvez voir ce problème du solveur sur la feuille de calcul Définir valeurs ne convergent pas dans le fichier Prodmix.xlsx.) Pour trouver la solution optimale dans ce cas, ouvrez le solveur, cliquez sur le bouton Options et désactivez la case part du principe Non négatif. Dans la boîte de dialogue Paramètres du solveur, sélectionnez la contrainte de demande D2 : I2 < = D8 : I8, puis cliquez sur Supprimer pour supprimer la contrainte. Lorsque vous cliquez sur résoudre, le Solveur renvoie le message « Valeurs de la cellule définie ne convergent pas ». Ce message signifie que si la cellule cible doit être agrandie (comme dans notre exemple), il existe des solutions réalisables avec des valeurs de cellule cible arbitraire très élevé. (Si la cellule cible doit être réduit, le message « Définir cellule valeurs ne convergent pas » signifie des solutions réalisables avec des valeurs de cellule cible arbitraire petite.) Dans notre situation, en permettant de production négative pour un médicaments, nous en vigueur « créer » ressources qui peuvent être utilisées pour produire arbitraire de grandes quantités d’autres médicaments. Étant donné notre demande illimitée, cela permet de réaliser des bénéfices illimités. Dans une situation réelle, nous ne pouvons pas apporter une quantité illimitée d’argent. En bref, si vous voyez « Définir les valeurs ne convergent pas », votre modèle dispose d’un message d’erreur.

  1. Supposons que notre société médicaments peut acheter jusqu'à 500 heures de travail en $1 plus par heure que les coûts de main en cours. Comment nous pouvons pour optimiser vos bénéfices ?

  2. À une usine de fabrication d’une puce, quatre techniciens (A, B, C et D) produisent trois produits (produits 1, 2 et 3). Ce mois-ci, le fabricant peut vendre 80 unités du produit 1, 50 unités du produit 2 et au maximum 50 unités du produit 3. Technicien A peut rendre uniquement les produits 1 et 3. Technicien B peut rendre uniquement les produits 1 et 2. Technicien C possibles uniquement produit 3. Technicien D peut rendre uniquement produit 2. Pour chaque unité produite, les produits participent le résultat suivant : produit 1, $6 ; Produit 2, $7 ; et produit 3, 10 $. Le temps (en heures) chaque technicien doit fabrication d’un produit se présente comme suit :

    Produit

    Technicien A

    Technicien B

    Technicien C

    Technicien D

    1

    2

    2,5

    Ne peut pas faire

    Ne peut pas faire

    2

    Ne peut pas faire

    3

    Ne peut pas faire

    3,5

    3

    3

    Ne peut pas faire

    4

    Ne peut pas faire

  3. Chaque technicien peut effectuer jusqu'à 120 heures par mois. Comment le fabricant peut-il maximiser ses profits mensuels ? Supposons qu'un nombre décimal d’unités pouvant être produit.

  4. Un ordinateur usine de fabrication produit souris, claviers et manettes de jeux vidéo. Le résultat par unité, l’utilisation de travail par unité, demande mensuelle et l’utilisation de temps machine par unité sont corrects dans le tableau suivant :

    Souris

    Claviers

    Joysticks

    Marge/unité

    $8

    $11

    $9

    L’utilisation de travail/unité

    heure.2

    heure.3

    heure.24

    Machine/unité de temps

    heure.04

    heure.055

    heure.04

    Demande mensuelle

    15 000

    27 000

    11 000

  5. Chaque mois, un total de 13 000 heures travaillées et 3 000 heures d’heure de l’ordinateur sont disponibles. Comment le fabricant peut-il maximiser sa contribution bénéfice mensuel à partir de l’installation ?

  6. Résoudre notre exemple médicaments en supposant qu’une demande minimale de 200 unités pour chaque médicaments doit être remplie.

  7. Jason rend boucles d’oreilles, colliers et bracelets losange. Il souhaite travailler 160 heures par mois au maximum. Il a 800 Jason. La marge, l’heure de travail et Jason obligé pour chaque produit est indiquées ci-dessous. Si la demande pour chaque produit est illimitée, comment Jason peut-il maximiser pas de son résultat ?

    Produit

    Profit par unité

    Heures de travail par unité

    Jason unitaire

    BRACELET

    300 €

    .35

    1.2

    COLLIER

    200 $

    .15

    .75

    Boucles d’oreilles

    100 €

    0,05

    0,5

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×