Utilisation du Solveur sous Excel

Pour utiliser le solveur, il faut dans un premier temps l'ajouter parmi les macros complémentaires dans le menu Outils.

On coche donc le Complément Solveur.

Il apparaitra alors dans le menu Outils

Les modules sont situés dans la partie Options Excel dans le Menu Office>Compléments (Add-ins).

Dans l'onglet Compléments, dans la zone Gérer, sélectionner Compléments Excel>Macros complémentaires disponibles>Complément Solveur. Le solveur apparaitra dans l'onglet Données >Analyse > Solveur

On cherche à fixer comme objectif à Excel les 34 pattes visibles.
On sélectionne la Cellule D3, qui contient la formule de calcul des pattes visibles.

On cherche à fixer comme Valeur notre cible 34.

Les cellules sur lesquelles Excel va pouvoir tester toutes les valeurs sont celles des nombres d'animaux B2 et C2.

On fixe ici les règles de logiques auxquelles sont soumises les cellules de notre tableau. On a notamment le fait que les nombres de poulets et de lapins sont des entiers (ouf !). Pour ajouter une contrainte, on clique donc sur le bouton Ajouter. On indique que les cellules B2 et C2 seront des entiers !
Les contraintes peuvent être de 5 types différents

On valide en cliquant sur OK.
D'autre part, il est évident que les lapins et les poulets existent, donc on a des valeurs positives !

De la même manière pour la cellule C2.
Enfin, on indique que le nombre total d'animaux doit être de 10.

On arrive donc à l'interface suivante :

On résout l'équation en cliquant alors sur Résoudre.
Si le solveur trouve une solution, il propose soit de garder les valeurs de solution ou bien de récupérer les valeurs d'origine du problème.

Excel nous trouve la solution : 3 poulets et 7 lapins.

Il s'agit tout simplement du temps maximum que le solveur passera à chercher une solution. La valeur maximale du temps de traitement étant 32 767. La valeur par défaut 100 convient parfaitement à la plupart des problèmes simples.

Il s'agit du nombre de calculs intermédiaires maximum que le solveur testera s'il ne trouve pas la solution. La valeur maximale du temps de traitement étant 32 767. La valeur par défaut 100 convient parfaitement à la plupart des problèmes simples.

Cette option correspond à la précision des résultats intermédiaires obtenus et les contraintes qui ont été fixées dans le cadre de l'énoncé. Le nombre décimal sera compris entre 0 et 1, une valeur de 0.001 sera plus précise qu'une valeur de 0.01. Plus le nombre de décimales sera grand, plus le niveau de précision sera donc élevé. La précision sera aussi à considérer pour les grandes valeurs. Si vous calculez avec des milliers ou des millions, pensez à cocher l'échelle automatique.

Cette option ne s'applique que dans le cas où des contraintes sur des nombres entiers existent. Elle est similaire à la précision pour les nombres entiers.

Cette option n'existe pas en version Excel 97, mais est disponible pour les versions 2003 et suivantes. Il s'agit cette fois de définir dans quelle limite la valeur de la cellule cible correspond au critère de valeur. Plus la convergence aura une valeur faible (entre 0 et 1), plus le résultat obtenu sera proche de l'objectif. Toutefois, cela peut impliquer un allongement de la durée de résolution.

A cocher seulement si le système d'équation est linéaire. NB : un problème linéaire correspondrait à une droite sur un graphe. Les problèmes simples utilisant les opérations d'addition, de soustraction ont des solutions linéaires (il existe une relation de proportion entre les variables). Lorsqu'on commence à multiplier les variables entre elles, on perd cette proportionnalité, et le problème devient non linéaire, on aura alors une courbe comme graphe. En cas de problème linéaire, le fait de cocher la case améliore le temps de résolution.

Cette option n'existe pas en version Excel 97, impose au Solveur une limite inférieure égale à 0 (zéro) sur toutes les cellules variables pour lesquelles vous n'avez pas défini une limite inférieure dans la zone Contrainte de la boîte de dialogue Ajouter une contrainte.
Dans notre exemple, nous avions placé des contraintes sur les cellules B2 et C2, mais nous aurions pu cocher cette option, sans ajouter les deux contraintes. Le temps de traitement est optimisé si l'option " supposé non-négatif " est cochée.

Activez cette case à cocher pour appliquer la mise à l'échelle automatique lorsque l'écart est important entre les entrées et les sorties, par exemple, en cas de maximisation du pourcentage du bénéfice obtenu à la suite d'investissements exprimés en millions.

Si vous souhaitez voir le résultat de chacune des itérations, en cochant cette case, vous obtiendrez une interruption du Solveur à la fin de chaque calcul intermédiaire.

On indique ici si la solution sera de type linéaire (du type y = ax + b) ou bien si une puissance supérieure ou égale à 2 sera présente (du type y = ax² + bx + c). La solution Tangente est plus lente.

V-j-i. Tangente (Linéaire sous Excel 97)▲

V-j-ii. Quadratique▲

Pour estimer les dérivées partielles sur les fonctions d'objectif et de contraintes, on peut utiliser deux méthodes ici.

V-k-i. A droite▲

V-k-ii. Centrée▲

Une option qu'on ne changera que dans le cas de problèmes complexes, nécessitant des contraintes de type mémoire. Dans le cadre d'une utilisation non poussée du solveur, la valeur par défaut Newton fera l'affaire.

V-l-i. Newton▲

V-l-ii. Gradient conjugué▲

On retrouve dans ce rapport les informations sous la forme suivante :

La version d'Excel (2003 donc 11.0 dans l'exemple).
La feuille sur laquelle le solveur a été effectué (chaque feuille peut avoir son propre solveur.)
Date du rapport : date et heure du rapport (en heure système).
Viennent ensuite les informations sur les conditions du problème :
Cellule Cible

• les valeurs des cellules adjacentes
• la valeur initiale de la cellule (ici 0, pas de valeur en fait)
• la valeur finale

Cellules Variables

• les valeurs des cellules adjacentes
• les valeurs initiales des cellules (ici 0, pas de valeur en fait)
• les valeurs finales

Contraintes

• La cellule soumise à la contrainte
• les valeurs des cellules adjacentes
• la valeur finale de la cellule
• la formule de contrainte
• l'état : lié ou non lié. Pour chaque contrainte la colonne 'état' renseigne sur la saturation ou non de la contrainte à l'optimum. L'état 'non lié' signifie que la contrainte est non saturée; l'état 'lié' signifie au contraire que la contrainte est saturée.
• la marge : si un écart entre ce qui a été fixé dans les contraintes et le résultat existe ou non.

Attention : Ce rapport n'existe que si aucune contrainte de type Entier n'existe.

On retrouve dans ce rapport les informations sous la forme suivante :

Les informations souffrent d'une traduction mot à mot, avec des noms " inversés ".
La version d'Excel (2003 donc 11.0 dans l'exemple).
La feuille sur laquelle le solveur a été effectué (chaque feuille peut avoir son propre solveur.)
Date du rapport : date et heure du rapport (en heure système).
Viennent ensuite les informations sur les conditions du problème :
Cellules Variables

• les valeurs des cellules adjacentes
• les valeurs finales
• Gradient réduit : correspond à l'augmentation de la cellule Cible induite en cas d'augmentation d'une unité de la cellule variable

Contraintes

• la cellule soumise à la contrainte
• les valeurs des cellules adjacentes
• la valeur finale de la cellule
• Multiplicateur de Lagrange : correspond à l'augmentation de la cellule Cible induite en cas d'augmentation d'une unité de la contrainte.

Si on coche l'option " Modèle supposé linéaire ", une autre série de données est indiquée dans le rapport de sensibilité.

On y voit cette fois-ci d'autres éléments sur les cellules variables et contraintes.
Cellules Variables

• Coût Réduit : remplace le Gradient Réduit, même mode de calcul
• Coefficient Objectif : mesure la relation de proportionnalité relative existant entre la cellule variable et la cellule cible.
• Augmentation Admissible : Il s'agit de la variation du coefficient objectif qui est admissible sans que cela ait pour impact une augmentation de la valeur cible des cellules.
• Réduction Admissible : Il s'agit de la variation du coefficient objectif qui est admissible sans que cela ait pour impact une diminution de la valeur cible des cellules.

NB : ajouter des contraintes aux cellules variables d'être supérieures ou égales à 0 permet d'éviter d'avoir des valeurs très grande dans les cellules pour les augmentations et réductions admissibles (1E+30).
Contraintes

• Coût Ombre (Shadow Price), une mauvaise traduction Office, le terme plus correct serait Prix Fictif : remplace le multiplicateur de Lagrange
• Contrainte à droite (en fait le côté droit de la contrainte) : simplement la valeur de la contrainte
• Augmentation Admissible : Il s'agit de la variation du coefficient objectif qui est admissible sans que cela ait pour impact une augmentation de la valeur des cellules variables.
• Réduction Admissible : Il s'agit de la variation du coefficient objectif qui est admissible sans que cela ait pour impact une diminution de la valeur des cellules variables.

C'est le rapport qui indique les limites possibles des cellules variables en redonnant les valeurs de cellule cible.

On a donc la cellule cible et sa valeur cible.
En dessous sont données les cellules variables avec leur valeur finale, suivies des limites inférieure et supérieure. Ces limites correspondent à la plus petite (grande) valeur que peut prendre la cellule variable, les autres valeurs de cellules étant fixes et dont les résultats respectent encore les contraintes fixées.
Les colonnes Résultat cible correspondant aux valeurs atteinte par la cellule cible dans le cas où ces valeurs limites sont atteintes.

Cette fonction sert à ajouter des contraintes à la résolution.

'on ajoute la contrainte sur le nombre d'animaux total
SolverAdd CellRef:="$D$2", Relation:=2, FormulaText:="10"

CellRef correspond à la cellule ou plage de cellules sur laquelle des contraintes sont imposées. Ici la cellule D2 (nombre total d'animaux)
Relation prend la valeur selon le tableau suivant

Dans notre cas " = ", donc 2.
FormulaText correspond à la valeur de la contrainte, ici 10.

Cette fonction change les contraintes.

'on change la contrainte de la cellule D2, passant le nombre total d'animaux à 8
    SolverChange CellRef:="$D$2", Relation:=2, FormulaText:="8"

De la même façon que SolverAdd, la fonction prend en paramètre les nouvelles contraintes.

Cette fonction supprime les contraintes.

'on supprime la contrainte de la cellule D2, passant le nombre total d'animaux à 8
    SolverDelete CellRef:="$D$2", Relation:=2, FormulaText:="8"

De la même façon que SolverAdd, la fonction prend en paramètre les contraintes qui sont supprimées.

Cette fonction indique que faire des résultats une fois la résolution effectuée.

'on exécute la résolution et on stocke les valeurs de solutions et générer les rapports de réponse et sensibilité
SolverFinish KeepFinal :=1, ReportArray := Array(1,3)

Le paramètre KeepFinal prend la valeur 1 si on garde les valeurs de résolution finale, 2 si l'on souhaite récupérer les valeurs d'origine sans garder la solution.
ReportArray spécifie un tableau des rapports que l'on souhaite créer en fin de résolution (1 pour le rapport de réponse, 2 pour le rapport de sensibilité, 3 pour le rapport de limites).

La fonction SolverFinishDialog est identique à SolverFinish, elle affiche en pus la boîte de dialogue " Résultats du Solveur ".

Permet de récupérer les informations relatives aux paramètres du solveur sur une page en particulier. La valeur du paramètre passé en premier (TypeNum) permet de préciser quelle information on souhaite récupérer.

Exemple :

?SolverGet (3, "Solveur")
40

Il est possible de charger les données d'un problème dans une plage de cellules (voir SolverSave pour l'enregistrement).

'charge les données du solveur depuis la plage de cellules G1 :G4
SolverLoad "Solveur !G1 :G4 "

Cette fonction permet de préciser les objectifs de résolution du solveur.

'ligne générale :
'   SetCell correspond à la cellule cible
'   MaxMinVal donne l'objectif (Max = 1, Min = 2, Val = 3
'   ValueOf est utilisé car MaxMinVal vaut 3
'   ByChange précise les cellules variables
    SolverOk SetCell:="$D$3", MaxMinVal:=3, ValueOf:="34", ByChange:="$B$2:$C$2"

Même fonction que SolverOk, mais affiche en plus la fenêtre solveur.

Fonction qui permet de définir les options du solveur pour la résolution.
Les paramètres sont :

• MaxTime, correspond à l'option Temps max (numérique entre 1 et 32 767)
• Iterations, correspond à l'option Itérations (numérique entre 1 et 32 767)
• Precision, correspond à l'option Précision (numérique entre 0 et 1)
• - AssumeLinear, correspond à l'option Modèle supposé linéaire (booléen)
• - StepThru, correspond à l'option Afficher le résultat des itérations (booléen)
• - Estimates, correspond à l'option Estimations (1 pour tangente, 2 pour quadratique)
• - Derivatives, correspond à l'option Dérivées (1 pour à droite, 2 pour centrée)
• - SearchOption, correspond à l'option Recherche (1 pour Newton, 2 pour gradient conjugué)
• - IntTolerance, correspond à l'option Tolérance (numérique entre 0 et 1)
• - Scaling, correspond à l'option Echelle automatique (booléen)
• - Convergence, correspond à l'option Convergence (numérique entre 0 et 1)
• - AssumeNonNeg, correspond à l'option Supposé non négatif (booléen)

'Les options du solveur restent inchangées, si ce n'est l'option "supposé non négatif"
    SolverOptions AssumeNonNeg:=True

La fonction permet de réinitialiser les conditions du solveur.

La fonction permet de sauvegarder les éléments nécessaires au solveur pour effectuer sa résolution.
En première cellule il enregistrera l'objectif de résolution (dans notre exemple)

=$D$3=34

Dans la seconde cellule, la liste des cellules variables (contenues dans une fonction NB)

=NB($B$2 :$C$2)

Dans les cellules suivantes, on retrouve les contraintes :

=$D$2=10 'somme des animaux dans la basse cours
=$B$2:$C$2=ENT($B$2:$C$2) 'les cellules variables B2 et C2 prennent des valeurs entières

Et enfin les options :

={100;100;0.000001;0.05;FAUX;FAUX;FAUX;1;1;1;0.0001;VRAI}
Paramètres dans le même ordre que la fonction SolverOptions.

La fonction SolverSolve résout le modèle en utilisant les paramètres que vous avez spécifié avec la fonction SolverOK.
Les paramètres possibles de la fonction sont :

• UserFinish : Indique si vous souhaitez laisser la main pour la fin de la résolution
• ShowRef correspond à la macro qui est appelée lors de la fin d'un calcul intermédiaire (cet argument ne doit être utilisé que lorsque le paramètre StepThru a été mis à Vrai (True) dans la fonction SolverOptions.

En prenant en exemple l'énoncé plus haut, on retrouve le code suivant :

Sub Resolution()
    'on réinitialise les conditions du Solveur
    SolverReset
'ligne générale :
'   SetCell correspond à la cellule cible
'   MaxMinVal donne l'objectif (Max = 1, Min = 2, Val = 3
'   ValueOf est utilisé car MaxMinVal vaut 3
'   ByChange précise les cellules variables
    SolverOk SetCell:="$D$3", MaxMinVal:=3, ValueOf:="34", ByChange:="$B$2:$C$2"
'on ajoute la contrainte sur le nombre d'animaux total
    SolverAdd CellRef:="$D$2", Relation:=2, FormulaText:="10"
'puis celle sur les valeurs entières
    SolverAdd CellRef:="$B$2:$C$2", Relation:=4, FormulaText:="entier"
'Les options du solveur restent inchangée, si ce n'est l'option "supposé non négatif"
    SolverOptions AssumeNonNeg:=True
'on lance la résolution
    SolverSolve UserFinish:=True
End Sub