Comment créer des macros Visual Basic à l'aide du solveur Excel dans Excel 97

Traductions disponibles Traductions disponibles
Numéro d'article: 843304 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article décrit comment utiliser le solveur de Microsoft Excel dans Microsoft Excel 97 pour créer des macros Microsoft Visual Basic. Le solveur de Microsoft Excel est un complément Microsoft Excel.

En outre, cet article contient des informations sur la création de macros, la création d'une macro et comment travailler avec les contraintes d'une macro. Cet article décrit également l'algorithme et méthodes utilisés par le solveur de Microsoft Excel. La liste suivante donne tous les sujets abordés dans l'article.

INTRODUCTION

Cet article contient des informations sur le solveur de Microsoft Excel.

Plus d'informations

Description du Solveur Microsoft Excel

Le solveur de Microsoft Excel est qu'un solveur de Microsoft Excel macro complémentaire Microsoft Excel vous aide à déterminer la valeur optimale pour une formule dans une cellule cible particulière sur une feuille de calcul Microsoft Excel. Microsoft Excel adapte les valeurs d'autres cellules qui sont liées à la cellule cible à l'aide d'une équation. Après avoir créer une équation et définir un ensemble de contraintes pour les variables ou les paramètres de l'équation, le solveur de Microsoft Excel essaie de différentes solutions pour obtenir un résultat satisfaisant toutes les contraintes. Le solveur de Microsoft Excel utilise les éléments suivants pour « résoudre » une équation :
  • L'objectif est de cellule cible - la cellule cible. Il s'agit de la cellule dans le modèle de feuille de calcul qui va être réduite, agrandie ou définir à une certaine valeur.
  • La modification de cellules - cellules variables sont les variables de la décision. Ces cellules affectent la valeur de la cellule cible. Ces cellules sont modifiées par le solveur de Microsoft Excel pour trouver la solution optimale de la cellule cible.
  • Contraintes : les contraintes sont des restrictions sur le contenu des cellules. Par exemple, une cellule dans un modèle de feuille de calcul peut être limitée à des valeurs entières, pendant une autre cellule peut être limitée à être inférieure à une valeur donnée.
Vous pouvez automatiser la création et la manipulation des modèles du solveur de Microsoft Excel à l'aide d'un de Visual Basic pour Applications Microsoft macro (VBA). Cet article décrit comment utiliser le langage de macro VBA pour utiliser les fonctions du solveur de Microsoft Excel dans Microsoft Excel 97. Cet article suppose que vous êtes familiarisé avec le langage VBA et Microsoft Visual Basic Editor pour Microsoft Excel 97. Les exemples suivants sont utilisés dans cet article sont disponibles pour téléchargement sur le site Web de Microsoft à l'adresse suivante :
http://download.Microsoft.com/download/excel97win/solverex/1.0/WIN98Me/en-us/SolverEx.exe
Remarque : Vous pouvez également utiliser les macros et les exemples décrits dans cet article dans Microsoft Excel, versions 5.0 et 7.0.

Comment utiliser les fonctions du solveur de Microsoft Excel dans une macro VBA

Pour utiliser les fonctions de complément Solveur de Microsoft Excel dans une macro VBA, vous devez référencer le complément à partir du projet VBA du classeur qui contient les macros. Si vous ne référencez pas la macro complémentaire Solveur de Microsoft Excel, vous recevrez l'erreur de compilation suivante lorsque vous essayez d'exécuter la macro :
Erreur de compilation : Sub ou Function non définie.
Pour faire référence à la macro complémentaire Solveur de Microsoft Excel pour les macros dans votre classeur, procédez comme suit :
  1. Ouvrez votre classeur.
  2. Dans le menu Outils , pointez surMacro, puis cliquez sur Visual Basic Editor.
  3. Dans le menu Outils , cliquez surréférences.
  4. Dans la liste Références disponibles , cliquez sur la case à cocher Solver.xls poursélectionner, puis cliquez sur OK.

    Remarque : Si vous ne voyez pas Solver.xls dans la liste Références disponibles, cliquez sur Parcourir. Dans la boîte de dialogue AddReference , recherchez et sélectionnez le fichier Solver, puis cliquez sur Ouvrir. Le fichier Solver se trouve généralement dans le sous-dossier de Office\Office\Library\Solver theC:\Program Files\Microsoft.
Vous êtes maintenant prêt à utiliser les fonctions du solveur de Microsoft Excel dans une macro VBA.

La création d'une macro VBA qui crée et résout un simple modèle Solveur de Microsoft Excel

Bien que le solveur de Microsoft Excel offre de nombreuses fonctions, les trois fonctions suivantes sont des éléments fondamentale pour la création et à la résolution d'une :
  • La fonction SolverOK
  • La fonction SolverSolve
  • La fonction SolverFinish

La fonction SolverOK

La fonction SolverOK définit un modèle élémentaire de solveur de Microsoft Excel. La fonction SolverOK est généralement la première fonction que vous utiliserez pour créer votre modèle Solveur de Microsoft Excel. La fonction SolverOK équivaut à cliquer dans le menu Outils sur Solveur , puis en spécifiant les options dans la boîte de dialogue Paramètres du solveur . Voici la syntaxe de la fonction SolverOK :
SolverOK (arguments SetCell, MaxMinVal, ValueOf, ByChange)
Les informations suivantes décrivent la syntaxe de la fonction SolverOK :
  • Arguments SetCell spécifie la cellule cible.
  • MaxMinVal correspond à si vous voulez résoudre la cellule cible pour une valeur maximale (1), une valeur minimale (2) ou une valeur spécifique (3).
  • ValueOf spécifie la valeur à laquelle la cellule cible est mis en correspondance. Si vous définissez MaxMinVal à 3, vous devez spécifier cet argument. Si vous définissez MaxMinVal à 1 ou 2, vous pouvez omettre cet argument.
  • ByChange spécifie la cellule ou la plage de cellules qui sera modifiée.
Figure 1 associe les arguments de la fonction SolverOK avec les paramètres dans la boîte de dialogue Paramètres du solveur .

La figure 1. Paramètres qui sont associés les arguments SolverOK

Réduire cette imageAgrandir cette image
 La figure 1. Paramètres qui sont associés les arguments SolverOK


La fonction SolverSolve

La fonction SolverSolve résoudre le modèle en utilisant les paramètres que vous avez spécifié avec la fonction SolverOK . L'exécution de la fonction SolverSolve équivaut à cliquer sur résoudre dans le paramètres du solveur boîte de dialogue. Voici la syntaxe de la fonction SolverSolve :
SolverSolve (UserFinish, ShowRef)
Les informations suivantes décrivent la syntaxe de la fonction SolverSolve :
  • UserFinish indique si vous souhaitez que l'utilisateur pour terminer la résolution du modèle.

    Pour renvoyer les résultats sans afficher la les résultats du solveur boîte de dialogue, définissez cet argument sur TRUE. Pour renvoyer les résultats et afficher la boîte de dialogue Résultat du solveur , définissez cet argument False
  • ShowRef identifie la macro qui est appelée lorsque le solveur de Microsoft Excel renvoie une solution intermédiaire.

    L'argument ShowRef doit être utilisé uniquement lorsque TRUE est passée à l'argument StepThru de la fonction SolverOptions .

La fonction SolverFinish

La fonction SolverFinish indique quoi faire avec les résultats et quel type de rapport créer une fois le processus de résolution terminé. Voici la syntaxe de la fonction SolverFinish :
SolverFinish (KeepFinal, ReportArray)
Les informations suivantes décrivent la syntaxe de la fonction SolverFinish :
  • KeepFinal indique quoi faire avec le résultat final. Si KeepFinal a la valeur 1, les valeurs de la solution finale sont conservées dans les cellules variables, remplaçant ainsi les valeurs. Si l'argument KeepFinal a 2, les valeurs de la résolution finale sont annulées, et les valeurs précédentes sont rétablies.
  • ReportArray spécifie un tableau qui indique le type de rapport, que Microsoft Excel crée lorsque la solution est atteinte. Si ReportArray est définie sur 1, Microsoft Excel crée un rapport des réponses. Si la valeur 2, Microsoft Excel crée un rapport de sensibilité et si la valeur 3 Microsoft Excel crée un rapport des limites. Pour plus d'informations sur ces rapports, reportez-vous à la section"Comment générer des rapports pour les solutions« section.
La figure 2. Options de résultats du solveur de Microsoft Excel qui sont associées à des arguments SolverFinish

Réduire cette imageAgrandir cette image
 La figure 2. Options de résultats du solveur qui sont associées à des arguments SolverFinish


Cet article décrit comment créer de façon interactive un simple modèle Solveur de Microsoft Excel. La première étape consiste à créer votre feuille de calcul pour le modèle. La feuille de calcul contient des cellules de données et d'au moins une cellule contenant une formule. Cette formule dépend d'autres cellules de la feuille de calcul. Après avoir configuré votre feuille de calcul, cliquez sur Solveur dans le menu Outils . Dans la paramètres du solveur boîte de dialogue, spécifiez la cellule cible, la valeur à résoudre pour la plage de cellules qui sera modifiée et les contraintes. Cliquez sur résoudre pour démarrer le processus de résolution. Une fois que le solveur de Microsoft Excel a trouvé une solution, les résultats s'affichent dans votre feuille de calcul, et le solveur de Microsoft Excel affiche une boîte de message vous demandant si vous souhaitez conserver le résultat final ou si vous souhaitez les ignorer. Lorsque vous cliquez sur une de ces options, le solveur de Microsoft Excel se termine.

La figure 3 illustre un modèle simple que vous pouvez créer à l'aide de ces étapes.

La figure 3. Un modèle simple : modèle de la racine carrée

Réduire cette imageAgrandir cette image
 La figure 3. Un modèle simple : modèle de la racine carrée


Dans cet exemple, modifier la cellule A1, qui contient la formule, = A1 ^ 2, une valeur qui rendra la cellule A2 est égale à la valeur 50. En d'autres termes, de trouver la racine carrée de 50. Il n'y a pas de contraintes dans le modèle racine carrée. La macro Find_Square_Root accomplit les tâches suivantes :
  • Il définit un modèle qui permettra de résoudre la valeur de la cellule A2 pour une valeur de 50 bychanging la valeur de la cellule A1.
  • Il a résolu le modèle.
  • Il enregistre le résultat final de la feuille de calcul de withoutdisplaying la boîte de dialogue Résultat du solveur .
Cette macro simple crée un modèle du solveur de Microsoft Excel et a résolu sans aucune intervention de l'utilisateur. Le code suivant décrit la macro Find_Square_Root :
    Sub Find_Square_Root()

    ' Set up the parameters for the model.
    ' Set the target cell A2 to a value of 50 by changing cell A1.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
         ByChange:=Range("A1")

    ' Solve the model but do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub
La macro Find_Square_Root2 , est une version modifiée de la macro Find_Square_Root . Si vous utilisez la fonction InputBox , la macro Find_Square_Root2 vous réclame la valeur que vous voulez résoudre pour la cellule cible. Une fois que vous entrez une valeur, la macro Find_Square_Root2 définit ce paramètre comme valeur de l'argumentvalueofSolverOK, a résolu le problème, enregistre les résultats dans la variable racine carrée, puis ignore la solution et restaure la valeur dans la feuille de calcul à son état d'origine. En fait, la macro Find_Square_Root2 illustre comment vous pouvez enregistrer les résultats dans une ou plusieurs variables et vous restaurerez ensuite les cellules à modifier leur valeur d'origine.

Le code suivant décrit la macro Find_Square_Root2 :
    Sub Find_Square_Root2()

    Dim val
    Dim sqroot

    ' Request the value for which you want to obtain the square root.
    val = Application.InputBox( _
         prompt:="Please enter the value for which you want " & _
         "to find the square root:", Type:=1)

    ' Set up the parameters for the model.
    SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
         ByChange:=Range("A1")

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Save the value of cell A1 (the changing cell) before you discard 
    ' the results.
    sqroot = Range("a1")

    ' Finish and discard the results.
    SolverFinish KeepFinal:=2

    ' Show the result in a message box.
    MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

    End Sub

Comment générer des rapports pour les solutions

Le solveur de Microsoft Excel propose plusieurs types de rapports qui décrivent comment les résultats modifiés et comment fermer les contraintes fournies à leurs valeurs critiques. Chaque rapport est placé sur une feuille séparée de votre classeur. Ces suivant sont les types de rapports qui offre le solveur de Microsoft Excel :
  • Rapport des réponses - l'état des réponses répertorie la cellule cible et les cellules variables avec leurs valeurs d'origine et finales correspondantes, les contraintes et les informations concernant les contraintes.
  • Rapport de la sensibilité - le rapport de la sensibilité fournit des informations sur le degré de sensibilité de la solution aux modifications mineures apportées à la formule de la cellule cible.
  • Rapport des limites - le rapport des limites répertorie la cellule cible et les cellules variables avec leurs valeurs respectives, les limites inférieure et supérieure et les valeurs cibles.
Pour créer des rapports pour vos modèles, spécifiez un tableau de valeurs de l'argument ReportArray de la fonction SolverFinish . Pour plus d'informations sur l'argument ReportArray , consultez le "SolverFinish (KeepFinal, ReportArray) « section. Par exemple, si vous souhaitez générer un rapport des limites pour le modèle de la macro Find_Square_Root2 crée et résout, modifiez la fonction SolverFinish dans la macro afin qu'elle ressemble à l'exemple de code suivant :
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Pour générer plusieurs rapports, modifiez la fonction SolverFinish afin qu'elle ressemble à l'exemple de code suivant :
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

Comment faire pour utiliser les fonctions de Microsoft ExcelSolver dans une macro en boucle

Dans de nombreuses situations, il est judicieux de faire résoudre la cellule cible pour plusieurs valeurs de solveur de Microsoft Excel. Vous pouvez généralement cela à l'aide d'une des structures de boucles disponibles dans VBA.

La macro Create_Square_Root_Table illustre le fonctionnement du solveur de Microsoft Excel dans une macro en boucle. La macro Create_Square_Root_Table crée une table dans une nouvelle feuille de calcul. Il insère les numéros un par le biais de dix et la racine carrée correspondante de chaque numéro. La macro Create_Square_Root_Table crée la table à l'aide d'une boucle For pour parcourir les numéros 1 à 10 et de résoudre la cellule cible dans le modèle racine carrée pour une valeur qui correspond au nombre de l'itération. Le code suivant décrit la macro Create_Square_Root_Table :
    Sub Create_Square_Root_Table()

    ' Add a new worksheet to the workbook.
    Set w = Worksheets.Add

    ' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
    w.Range("C1").Value = 2
    w.Range("C2").Formula = "=C1^2"

    ' A loop that will make 10 iterations, starting with the number 1, 
    ' and finishing at the number 10.
    For i = 1 To 10

        ' Set the Solver parameters that indicate that Solver should
        ' solve the cell C2 for the value of i (where i is the number
        ' of the iteration) by changing cell C1.
        SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
            MaxMinVal:=3, ValueOf:=i

        ' Do not display the Solver Results dialog box.
        SolverSolve UserFinish:=True

        ' Save the value of i in column A and the results of the 
        ' changing cell in column B.
        w.Cells(i, 1) = i
        w.Cells(i, 2) = Range("C1")

        ' Finish and discard the final results.
        SolverFinish KeepFinal:=2

    Next

    ' Clear the range C1:C2
    w.Range("C1:C2").Clear

    End Sub

La macro Create_Square_Root_Table génère le tableau illustré dans la Figure 4.

La figure 4. Sortie qui est généré par la macro Create_Square_Root_Table

Réduire cette imageAgrandir cette image
La figure 4. Sortie qui est généré par la macro Create Square Root Table


L'utilisation de contraintes

Une contrainte est une restriction sur le contenu d'une ou plusieurs cellules. Un modèle peut avoir une ou plusieurs contraintes. Le jeu de restrictions est un ensemble des inégalités ou un ensemble d'égalités qui supprimer certaines combinaisons de valeurs pour les variables de la décision de la solution. Par exemple, une contrainte peut exiger qu'une seule cellule est supérieure à zéro et qu'une autre cellule contient uniquement une valeur entière.

Le modèle racine carrée dont nous avons parlé jusqu'à présent est un modèle simple qui ne contient-elle pas toutes les contraintes. La figure 5 illustre un modèle qui utilise les contraintes. L'objectif de ce modèle est de trouver la combinaison optimale de produits pour un bénéfice maximal.

La figure 5. Produit mélanger à réduire les pertes et profits

Réduire cette imageAgrandir cette image
La figure 5. Produit mélanger à réduire les pertes et profits


Par exemple, si une société fabrique des téléviseurs, des chaînes stéréo et des haut-parleurs, et utilise un stock de pièces commune de blocs d'alimentation, haut-parleurs et ainsi de suite. Les parties sont dans l'approvisionnement limité. Votre objectif est de déterminer la combinaison de produits à générer plus rentable. Le profit par unité diminue avec volume car tarifs supplémentaires sont requis pour charger la chaîne de distribution. L'exposant de la baisse de la rentabilité est 0.9. Cette puissance est utilisé pour calculer le profit par produit dans la plage G11:I11.

Votre objectif consiste à trouver le bénéfice maximal (cellule G14). Les valeurs que vous allez changer pour trouver le bénéfice maximal sont le nombre d'unités que vous générez. La plage G9:G11 représente les cellules variables de ce modèle. Votre seule contrainte est que le nombre de parties que vous utilisez ne peut pas dépasser le nombre de parties que vous avez en main. Le solveur de Microsoft Excel, cette contrainte s'affiche sous la forme E3:E7<=B3:B7. if="" you="" were="" to="" build="" this="" microsoft="" excel="" solver="" model="" interactively,="" the="" microsoft="" excel="" solver="" parameters="" would="" look="" similar="" to="" those="" that="" are="" in="" figure="" 6.=""></=B3:B7.>

La figure 6. Paramètres du solveur de Microsoft Excel pour la gamme de produits avec modèle diminue la marge de Profit

Réduire cette imageAgrandir cette image
La figure 6. Paramètres du solveur de Microsoft Excel pour la gamme de produits avec modèle diminue la marge de Profit


Pour créer et résoudre l'assortiment de produits avec modèle diminue la marge de Profit, vous allez utiliser une nouvelle fonction, la fonction SolverAdd , en plus des fonctions VBA de solveur de Microsoft Excel qui ont été décrits précédemment. La fonction SolverAdd ajoute la contrainte au modèle. L'exécution de la fonction SolverAdd revient à cliquer sur le Ajouter bouton dans la boîte de dialogue Paramètres du solveur . La fonction SolverAdd a la syntaxe suivante :
SolverAdd (CellRef, Relation, FormulaText)
Les informations suivantes décrivent la syntaxe de la fonction SolverAdd :
  • CellRef fait référence à une ou plusieurs cellules qui forment le côté gauche de la contrainte.
  • La relation est la relation arithmétique entre la côtés gauche et les droite d'une contrainte.
  • Relation peut être une valeur comprise entre 1 et 5 dans l'exemple suivant :
    • La valeur 1 est inférieur ou égal à)<>
    • La vaue 2 est égal à (=).
    • La valeur 3 est supérieur ou égal à (> =).
    • La valeur 4 est un nombre entier.
    • La valeur 5 est le fichier binaire (une valeur de zéro ou un).
  • FormulaText fait référence à une ou plusieurs cellules qui forment la partie droite de la zone de constraint.* *
** Lorsque vous spécifiez une plage de cellules de l'argument FormulaText de la fonction SolverAdd , notez si la référence est relatif ou absolu. En règle générale, vous devez spécifier une référence absolue de l'argument FormulaText . Toutefois, si vous spécifiez des références relatives pour l'argument FormulaText , sachez que la référence sera fonction de la cellule cible et pas la cellule active.

Remarque : Dans Microsoft Excel, versions 5.0 et 7.0, utilisez la notation R1C1 lorsque vous spécifiez une cellule ou une plage de cellules avec l'argument FormulaText . En revanche, dans Microsoft Excel 97, utilisez la notation de style A1 pour spécifier l'argument FormulaText .

La figure 7. Champs qui sont associés les arguments SolverAdd

Réduire cette imageAgrandir cette image
 La figure 7. Champs qui sont associés les arguments SolverAdd


La macro Maximum_Profit qui génère un modèle de la gamme de produits avec modèle de baisse de la rentabilité. Cette macro s'exécute les fonctions ou les arguments suivants :

  • La fonction SolverOK définit la cellule cible pour un andspecifies de valeur maximale les cellules à modifier.
  • La fonction SolverAdd ajoute la contrainte au modèle.
  • La fonction SolverSolve trouve une solution sans afficher la boîte de dialogueRésultat du solveur .
  • La fonction SolverFinish renvoie le résultat final de la feuille de calcul.
Le code suivant décrit la macro Maximum_Profit :
    Sub Maximum_Profit()

    ' Set up the parameters for the model.
    ' Determine the maximum value for the sum of profits in cell G14
    ' by changing the number of units to build in cells G9:I9.
    Solverok setcell:=Range("G14"), maxminval:=1, _
        bychange:=Range("G9:I9")

    ' Add the constraint for the model. The only constraint is that the
    ' number of parts used does not exceed the parts on hand-- 
    ' E3:E7<=B3:B7
    SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"

    ' Do not display the Solver Results dialog box.
    SolverSolve UserFinish:=True

    ' Finish and keep the final results.
    SolverFinish KeepFinal:=1

    End Sub

Remarque : Dans Microsoft Excel, versions 5.0 et 7.0, utilisez la notation R1C1 lorsque vous spécifiez une cellule ou une plage de cellules avec l'argument FormulaText . En revanche, dans Microsoft Excel 97, utilisez la notation de style A1 pour spécifier l'argument FormulaText .

Lorsque vous exécutez la macro Maximum_Profit , solveur de Microsoft Excel va trouver une solution de génération de 160 téléviseurs, 200 chaînes stéréo et 80 haut-parleurs pour un bénéfice maximal de 14,917 dollars.

Comment modifier et supprimer des contraintes

Les contraintes de votre modèle peuvent être changées ou supprimées par programme. Les contraintes sont identifiés par leurs arguments CellRef et Relation .

Pour modifier par programmation une contrainte existante, utilisez la fonction SolverChange . Voici la syntaxe de la fonction SolverChange :
SolverChange (CellRef, Relation, FormulaText)
Notez que les arguments de la fonction SolverChange sont identiques à celles que vous utilisez avec la fonction SolverAdd .

Si vous souhaitez modifier la contrainte de la gamme de produits avec modèle de baisse de la rentabilité, vous utiliserez la fonction SolverChange . Par exemple, la contrainte qui est spécifiée est actuellement que le nombre de pièces utilisées est inférieur ou égal au nombre d'articles en stock (E3:E7<= b3:b7).="" if="" you="" want="" to="" change="" this="" constraint="" so="" that="" the="" number="" of="" parts="" used="" is="" less="" than="" or="" equal="" to="" the="" number="" of="" parts="" projected="" (number="" of="" parts="" on="" hand="" plus="" number="" of="" parts="" ordered).="" this="" new="" constraint="" would="" look="" like="" e3:e7=""></=><= d3:d7.="" the="" following="" macro="" would="" change="" the="" existing="" constraint=""></=><=B3:B7 to="" e3:e7=""></=B3:B7><= d3:d7="" and="" solve="" for="" a="" solution.=""></=>

Le code suivant décrit la macro Change_Constraint_and_Solve :
    Sub Change_Constraint_and_Solve()

    ' Change the constraint.
    SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
       FormulaText:="$D$3:$D$7"

    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False

    End Sub

Parce que les contraintes sont identifiées par les arguments CellRef et Relation d'arguments, vous pouvez uniquement modifier l'argument FormulaText pour la contrainte à l'aide de la fonction SolverChange . Si les arguments CellRef et les valeurs de Relation ne correspondent pas une contrainte existante, vous devez supprimer la contrainte et puis ajouter la contrainte modifiée. Pour supprimer une contrainte, utilisez la fonction SolverDelete . Voici la syntaxe de la fonction SolverDelete :

SolverDelete (CellRef, Relation, FormulaText)

Notez que les arguments de la fonction SolverDelete sont identiques à ceux que vous utilisez avec les fonctions SolverChange et la SolverAdd .

La macro suivante illustre comment faire pour supprimer et ajouter une contrainte. Dans cet exemple, la macro Change_Constraint_and_Solve2 supprime la contrainte E3:E7<=B3:B7 from="" the="" product="" mix="" with="" diminishing="" returns="" model="" and="" adds="" a="" new="" constraint.="" the="" new="" constraint="" is="" just="" a="" modification="" of="" the="" original="" constraint,="" where="" the="" left="" and="" right="" sides="" of="" the="" constraint="" are="" reversed.=""></=B3:B7>

Le code suivant décrit la macro Change_Constraint_and_Solve2 :
    Sub Change_Constraint_and_Solve2()

    ' Reverse the left and right sides of the constraint...
    ' Delete the constraint E3:E7<=B3:B7 and add the
    ' constraint B3:B7>=E3:E7.
    SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
        FormulaText:="$B$3:$B$7"
    SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
        FormulaText:="$E$3:$E$7"
    
    ' Return the results and display the Solver Results dialog box.
    SolverSolve UserFinish:=False
    
    End Sub

Remarque : Dans Microsoft Excel, versions 5.0 et 7.0, utilisez la notation R1C1 lorsque vous spécifiez une cellule ou une plage de cellules avec l'argument FormulaText . En revanche, dans Microsoft Excel 97, utilisez la notation de style A1 pour spécifier l'argument FormulaText .

Comment faire pour charger et enregistrer vos modèles

Lorsque vous enregistrez votre classeur, les derniers paramètres que vous avez spécifié dans la boîte de dialogue Paramètres du solveur sont enregistrées avec le classeur. Par conséquent, lorsque vous ouvrez le classeur, les paramètres sont le même que lorsque votre dernier enregistrement du classeur.

Vous pouvez définir plusieurs problèmes pour une feuille de calcul. Chaque problème est composée de cellules et les contraintes que vous entrez dans le Paramètre du solveur et la Options du solveur les boîtes de dialogue. Dans la mesure où seul le dernier problème est enregistré avec la feuille de calcul, vous perdrez tous les autres problèmes, sauf si vous les enregistrez explicitement. Pour les enregistrer, cliquez sur Enregistrer le modèle dans la boîte de dialogue Options du solveur . De même, lorsque vous souhaitez restaurer les paramètres précédemment enregistrés, cliquez sur Charger un modèle dans la Options du solveur boîte de dialogue.

Modèles du solveur sont stockés dans une plage de cellules dans une feuille de calcul. La première cellule de la plage contient la formule de la cellule cible. La deuxième cellule de la plage contient la formule qui identifie les cellules à modifier dans le modèle. La dernière cellule de la plage contient un tableau qui représente les options définies dans la boîte de dialogue Options du solveur . Les cellules situées entre la deuxième cellule et la dernière cellule contient les formules qui représentent les contraintes dans le modèle.

La figure 8 illustre un modèle de planification des employés. Supposons que vous travaillez pour un petit producteur. Ce tableau indique le taux horaire de chaque employé de la paie, le nombre d'heures qu'ils soient planifiés et un nombre prévu d'unités que chaque salarié peut produire en une heure. Votre objectif est de répondre à un quota spécifique pour le nombre d'unités produites tout en réduisant le coût de la main-d'?uvre.

La figure 8. Mode de planification employé
l
Réduire cette imageAgrandir cette image
 La figure 8. Modèle de planification des employés


Deux facteurs supplémentaires (ou contraintes) que vous devez prendre en compte sont le nombre minimum/maximum d'heures un employé peut travailler et le nombre d'unités que vous avez l'intention de produire. Si une semaine spécifié, vous devez générer des 3975 unités et vous souhaitez que chaque employé de travailler entre 30 et 45 heures, les paramètres du solveur de Microsoft Excel devrait ressembler à celles décrites dans le tableau suivant :

Réduire ce tableauAgrandir ce tableau
ParamètrePlage de cellulesDescription
Cellule cible $D$ 12 Coût de la main-d'?uvre.
Cellules variables $C$ 2: $C$ 8 Heures travaillées par l'employé.
Contraintes $C$ 2: $C$ 8<=></=> Nombre maximal d'heures par employé est 45.
$C$ 2: $C$ 8 > = 30 Nombre minimal d'heures par employé est de 35.
$G 12 = 3975 Nombre d'unités est 3975.


Les objectifs définis sont à résoudre pour les coûts de main de ?uvre optimale sur une base hebdomadaire, enregistrez chaque modèle une fois par semaine et être en mesure de charger tout modèle hebdomadaire lorsque vous en avez besoin.

Dans une macro, les paramètres du solveur de Microsoft Excel pour un modèle peuvent être enregistrés et chargés en utilisant respectivement les SolverSave et les fonctions SolverLoad . Le SolverSave et les fonctions SolverLoad présentent la syntaxe suivante :

SolverSave (SaveArea)

SolverLoad (LoadArea)

Le SolverSave et les fonctions SolverLoad possèdent un seul argument, SaveArea et les arguments LoadArea respectivement. Ces arguments spécifient une plage dans une feuille de calcul où sont stockées les informations de modèle.

La macro New_Employee_Schedule suivante, montre comment créer, à résoudre et enregistrer un modèle basé sur l'entrée d'utilisateur. L'utilisateur est invité à fournir la date du modèle, le nombre d'unités à produire et le nombre minimal et maximal d'heures par employé. Ces données est ensuite utilisé pour créer le modèle. Le modèle est résolu, puis enregistré avec l'entrée d'utilisateur.

Le code suivant décrit la macro New_Employee_Schedule :
    Sub New_Employee_Schedule()
    
    ' Prompt the user for the date of the model, the units to produce,
    ' and the maximum and minimum number of hours per employee.
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model:", Type:=2)
    Units = Application.InputBox( _
       Prompt:="Projected Number of Units:", Type:=1)
    MaxHrs = Application.InputBox( _
       Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
    MinHrs = Application.InputBox( _
       Prompt:="Minimum Number of Hours Per Employee:", Type:=1)
    
    ' Clear any previous Solver settings.
    SolverReset
    
    ' Set the target cell, D12, to a minimum value by changing
    ' the range, C2:C8.
    SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
       ByChange:=Range("C2:C8")
    
    ' Add the constraint that number of hours worked <= MaxHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs
    
    ' Add the constraint that number of hours worked >=MinHrs.
    SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs
    
    ' Add the constraint that number of units produced = Units.
    SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    
    ' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
    ' in columns I:L.
    Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
       Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
    ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
       Units, MaxHrs, MinHrs)
    
    ' Save the model parameters to the range M:R in the worksheet.
    SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)
    
    End Sub
Remarque : Dans Microsoft Excel, versions 5.0 et 7.0, utilisez la notation R1C1 lorsque vous spécifiez une cellule ou une plage de cellules avec l'argument FormulaText . En revanche, dans Microsoft Excel 97, utilisez la notation de style A1 pour spécifier l'argument FormulaText .

La figure 9 illustre la façon dont les informations de modèle enregistré s'affiche sur la feuille de calcul.

La figure 9. Informations sur le modèle sont enregistrées par la macro New_Employee_Schedule

Réduire cette imageAgrandir cette image
La figure 9. Informations sur le modèle sont enregistrées par la macro New Employee Schedule


La macro New_Employee_Schedule enregistre chaque nouveau modèle dans la feuille de calcul. La macro Load_Employee_Schedule peut charger un de ces modèles enregistrés. La macro demande à l'utilisateur pour le modèle à charger, puis recherche colonne I pour la date du modèle. Si la date de modèle est trouvée, la macro Load_Employee_Schedule charge le modèle correspondant, il résout et puis maintient le résultat final.

Le code suivant décrit la macro New_Employee_Schedule:
    Sub Load_Employee_Schedule()
    
    ' Prompt for the date of the model. 
    ModelDate = Application.InputBox( _
       Prompt:="Date of Model to Load:", Type:=2)
    
    ' Locate the date in column I.
    Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
    r = Application.Match(ModelDate, DateRange, 0)
    
    If IsError(r) Then
        ' Display a message if the model date is not found
        MsgBox "Cannot find a model with the date " & ModelDate
    Else
        ' If the model date is found, load the model into Solver,
        ' solve the model, and keep the final results.
        SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    End If
    
    End Sub
La macro New_Employee_Schedule présente la fonction SolverReset . La fonction SolverReset peut être utilisée pour supprimer toutes les sélections de cellules et les contraintes dans la boîte de dialogue Paramètres du solveur et pour réinitialiser tous les paramètres dans la fonction SolverReset ne comporte aucun argument.

Comment trouver des informations sur le solveur de Microsoft Excel

Les ressources suivantes fournissent des informations sur l'utilisation de la macro complémentaire Solveur de Microsoft Excel.

  • Pour plus d'aide sur les messages du solveur spécifiques, consultezFrontlineSystems.
  • Des modèles faciles à gérer, reportez-vous à la section pour obtenir des conseils sur la création de lisible,FrontlineSystems.
  • Pour plus d'informations sur le solveur limite pour les contraintes et cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    75714 Limites du solveur pour les contraintes
  • Qui contient des exemples qui utilisent la macro complémentaire Solveur de Microsoft Excel dans ne, consultez le fichier d'exemple Solvsamp.xls.
  • Voici l'emplacement par défaut du fichier qui est inclus withMicrosoft Excel 97 :
    \Program Files\MicrosoftOffice\Office\Examples\Solver\SolvSamp.xls
  • Voici l'emplacement par défaut du fichier qui est inclus withMicrosoft Excel 7.0 :
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Voici l'emplacement par défaut du fichier qui est inclus withMicrosoft Excel 5.0 :
    \Excel\Examples\Solver\SolvSamp.xls

Pour en savoir plus sur l'algorithme et méthodes utilisés par le solveur de Microsoft Excel

Le solveur de Microsoft Excel utilise le code d'optimisation non linéaire Generalized Reduced Gradient (GRG2) qui a été développé par Leon Lasdon, Université du Texas à Austin et Allan Waren, Cleveland State University.

Pour plus d'informations sur l'algorithme utilisé par le solveur de Microsoft Excel, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
82890 Le solveur utilise generalized réduite


Les problèmes linéaires et entiers utilisent la méthode simplex, avec des bornes sur les variables et que la méthode branch-and-bound, mis en ?uvre par John Watson et Dan Fylstra, Frontline Systems, Inc. Pour plus d'informations sur le processus de résolution interne utilisé par le solveur, contactez :

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288 
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com 


Sélections du code du programme Solveur Microsoft Excel sont copyright 1990, 1991, 1992 et 1995 par Frontline Systems, Inc. parties sont sous copyright 1989 d'Optimal Methods, Inc.

Remarque : Le complément Solveur de Microsoft Excel qui est décrite dans cet article est fourni « tel quel » et nous ne garantissons pas qu'il peut être utilisé dans toutes les situations. Bien que les techniciens du support technique Microsoft peuvent aider à l'installation et l'utilisation de ce complément, ils ne modifieront pas le complément pour fournir de nouvelles fonctionnalités.

Aucune garantie. Le logiciel est fourni « en tant que-est, "sans garantie d'aucune sorte et toute utilisation de ce logiciel produit est à vos propres risques.

Propriétés

Numéro d'article: 843304 - Dernière mise à jour: samedi 8 février 2014 - Version: 2.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft Excel 97 Standard
Mots-clés : 
kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo kbmt KB843304 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu d'une traduction automatique réalisée par un logiciel Microsoft et non par un traducteur professionnel. Cette traduction automatique a pu aussi être révisée par la communauté Microsoft grâce à la technologie Community Translation Framework (CTF). Pour en savoir plus sur cette technologie, veuillez consulter la page http://support.microsoft.com/gp/machine-translation-corrections/fr. Microsoft vous propose en effet des articles traduits par des professionnels, des articles issus de traductions automatiques et des articles issus de traductions automatiques révisées par la communauté Microsoft, de manière à ce que vous ayez accès à tous les articles de notre Base de connaissances dans votre langue. Il est important de noter que les articles issus de la traduction automatique, y compris ceux révisés par la communauté Microsoft, peuvent contenir des erreurs de vocabulaire, de syntaxe ou de grammaire. Microsoft ne pourra être tenu responsable des imprécisions, erreurs, ainsi que de tout dommage résultant d?une traduction incorrecte du contenu ou de son utilisation par les clients.
La version anglaise de cet article est la suivante: 843304
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.
Exclusion de responsabilité concernant les contenus obsolètes dans la Base de connaissances
Cet article concerne des produits pour lesquels Microsoft n'offre plus de support. Il est par conséquent fourni « en l'état » et ne sera plus mis à jour.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com