Utilisation du Solveur sous Excel

Par Jean-Philippe ANDRÉ

Date de publication : 7 décembre 2012

Que l'on soit adepte de la résolution de problèmes complexes ou simple néophyte qui
cherche à résoudre des équations linéaires, il est intéressant de voir que le solveur mis à
disposition dans Excel est riche de fonctionnalités. Commentez
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

I - Introduction..............................................................................................................................................................3
II - Utilité d'un solveur..................................................................................................................................................3
III - Ajout du module solveur dans Excel.................................................................................................................... 3
III-a - Version 97-2003........................................................................................................................................... 3
III-b - Version 2007-2010....................................................................................................................................... 4
IV - Utilisation du Solveur............................................................................................................................................5
IV-a - Cellule Cible................................................................................................................................................. 6
IV-b - Objectif de solution...................................................................................................................................... 6
IV-c - Cellules variables......................................................................................................................................... 7
IV-d - Contraintes................................................................................................................................................... 7
IV-e - Résoudre...................................................................................................................................................... 8
V - Options du Solveur................................................................................................................................................9
V-a - Temps max....................................................................................................................................................9
V-b - Itérations........................................................................................................................................................9
V-c - Précision...................................................................................................................................................... 10
V-d - Tolérance..................................................................................................................................................... 10
V-e - Convergence............................................................................................................................................... 10
V-f - Modèle supposé linéaire.............................................................................................................................. 10
V-g - Supposé non-négatif................................................................................................................................... 10
V-h - Echelle automatique....................................................................................................................................10
V-i - Afficher le résultat des itérations..................................................................................................................10
V-j - Estimations................................................................................................................................................... 11
V-j-i - Tangente (Linéaire sous Excel 97)....................................................................................................... 11
V-j-ii - Quadratique..........................................................................................................................................11
V-k - Dérivées.......................................................................................................................................................11
V-k-i - A droite.................................................................................................................................................11
V-k-ii - Centrée................................................................................................................................................11
V-l - Recherche.................................................................................................................................................... 11
V-l-i - Newton.................................................................................................................................................. 11
V-l-ii - Gradient conjugué................................................................................................................................11
VI - Rapport des réponses........................................................................................................................................ 11
VI-a - Rapport des réponses................................................................................................................................12
VI-b - Rapport de Sensibilité................................................................................................................................13
VI-c - Rapport des Limites................................................................................................................................... 14
VII - Utilisation du Solveur en VBA........................................................................................................................... 15
VII-a - SolverAdd.................................................................................................................................................. 15
VII-b - SolverChange............................................................................................................................................15
VII-c - SolverDelete.............................................................................................................................................. 16
VII-d - SolverFinish...............................................................................................................................................16
VII-e - SolverFinishDialog.....................................................................................................................................16
VII-f - SolverGet....................................................................................................................................................16
VII-g - SolverLoad................................................................................................................................................ 17
VII-h - SolverOk....................................................................................................................................................17
VII-i - SolverOkDialog...........................................................................................................................................17
VII-j - SolverOptions............................................................................................................................................. 17
VII-k - SolverReset............................................................................................................................................... 18
VII-l - SolverSave................................................................................................................................................. 18
VII-m - SolverSolve.............................................................................................................................................. 18
VII-n - Exemple d'utilisation..................................................................................................................................19
VIII - Exemple 2 : Optimisation de portefeuille..........................................................................................................19
IX - Fichier démo....................................................................................................................................................... 19
X - Conclusion........................................................................................................................................................... 19
XI - Webographie....................................................................................................................................................... 20
XII - Remerciements.................................................................................................................................................. 20

-2-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

I - Introduction

En voyant à nombreuses reprises des sujets en lien avec le solveur dans les forums Excel de [Link], j'ai
souhaité faire quelques recherches de mon côté pour me permettre de découvrir à titre individuel ce qu'est le solveur,
et d'en faire une analyse plus poussée. Voici quelques éléments qui permettront à vous visiteurs, d'en apprendre
plus sur cet outil bien souvent oublié sous Excel !

II - Utilité d'un solveur

Lors des cours de mathématiques en primaire et les premiers cheveux arrachés devant les premières équations
avec inconnues, que n'aurions-nous pas donner pour avoir un logiciel qui résout pour nous ces lignes de chiffres et
de lettres pour nous… Et plus tard on tombe sur des équations de second degré, et les obstinés poussent le vice
à chercher des optimisations de systèmes, des équations complexes dont on veut trouver les solutions. Le solveur
d'Excel est un module de calcul mathématique qui permet ce genre de chose. On lui fixe un objectif de résultat, en lui
indiquant les contraintes et variables de l'énoncé. Il est alors chargé de calculer de façon automatique les possibilités
existantes et d'en faire émerger la solution exacte (voire la plus proche) du problème posé.

III - Ajout du module solveur dans Excel

III-a - Version 97-2003

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

Outils>Macros Complémentaire

On coche donc le Complément Solveur.

-3-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

Il apparaitra alors dans le menu Outils

III-b - Version 2007-2010

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

-4-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

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

IV - Utilisation du Solveur

On se situe dans un cas simple d'exercice de mathématiques. On a des poulets et des lapins dans une basse-cour.
On connait le nombre d'animaux, mais on ne voit que le nombre de pattes ! Il s'agit donc de savoir combien de poulets
et de lapins se déplacent dans notre basse-cour. L'énoncé nous indique qu'on a 10 animaux et 34 pattes visibles !
On crée donc un petit tableau avec les informations importantes :
Le nombre d'animaux

Et le nombre total de pattes visibles (2 pour un poulet, 4 pour un lapin)

-5-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

Une fois ces éléments posés, on passe à la résolution de l'exercice !

IV-a - Cellule Cible

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.

IV-b - Objectif de solution

On cherche à fixer comme Valeur notre cible 34.

-6-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

IV-c - Cellules variables

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

IV-d - Contraintes

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

Contrainte Signification
<= Valeur de cellule
inférieure ou
égale à la
contrainte
= Valeur de cellule
égale à la
contrainte
>= Valeur de cellule
supérieure
ou égale à la
contrainte
ent Valeur de la
cellule doit être
un entier
bin Valeur de la
cellule doit être 0
ou 1

-7-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

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 :

IV-e - Résoudre

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.

-8-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

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

V - Options du Solveur

Lorsqu'on clique sur le bouton Options du solveur, on accède à un certain nombre de caractéristiques du solveur
en lui-même.

V-a - Temps max

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.

V-b - Itérations

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.

-9-
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

V-c - Précision

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.

V-d - Tolérance

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.

V-e - Convergence

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.

V-f - Modèle supposé linéaire

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.

V-g - Supposé non-négatif

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.

V-h - Echelle automatique

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.

V-i - Afficher le résultat des itérations

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.

- 10 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

V-j - Estimations

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)

Il s'agit d'une approximation linéaire de la fonction à optimiser. On retrouve la représentation graphique d'une droite
pour le système.

V-j-ii - Quadratique

Si le problème se représente avec une courbe, l'estimation sera alors quadratique. Cette solution permet notamment
de diminuer le nombre de pas à chaque recherche.

V-k - Dérivées

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

Utilisez cette option pour la plupart des problèmes dans lesquels les valeurs de contrainte changent assez lentement.

V-k-ii - Centrée

Utilisez cette option pour les problèmes dans lesquels les contraintes changent rapidement, particulièrement près
des limites. Bien que cette option nécessite davantage de calculs, elle peut s'avérer utile lorsque le Solveur renvoie
un message indiquant qu'il n'est pas en mesure d'améliorer la solution.

V-l - Recherche

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

Il est question de calculer les dérivées successives de la droite qui représente le problème. C'est la méthode par
défaut. Les plus doués en mathématiques iront voir les détails sur les approximations de la Hessienne.

V-l-ii - Gradient conjugué

Nécessite moins de mémoire que la méthode Newton, mais requiert généralement un plus grand nombre d'itérations
pour atteindre un niveau de précision particulier. Utilisez cette option pour résoudre un problème important avec une
quantité de mémoire limitée ou lorsque l'examen des itérations révèle une progression lente entre chaque étape.

VI - Rapport des réponses

Une fois que le solveur a trouvé une solution, il est possible d'afficher 3 types de rapports de réponses. Ces rapports
ont pour but de stocker les informations sur la ou les résolutions successives du solveur.

- 11 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

VI-a - Rapport des réponses

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.

- 12 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

• la marge : si un écart entre ce qui a été fixé dans les contraintes et le résultat existe ou non.

VI-b - Rapport de Sensibilité

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

- 13 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

• 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.

VI-c - Rapport des Limites

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

- 14 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

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.

VII - Utilisation du Solveur en VBA

L'utilisation du solveur sous sa forme graphique peut aussi se faire en automatique, avec le code VBA notamment.
Les principales fonctions dans l'ordre alphabétique :

VII-a - SolverAdd

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

Contrainte
Valeur
<= 1
= 2
>= 3
ent 4
bin 5

Dans notre cas " = ", donc 2.

FormulaText correspond à la valeur de la contrainte, ici 10.

VII-b - SolverChange

Cette fonction change les contraintes.

- 15 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

'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.

VII-c - SolverDelete

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.

VII-d - SolverFinish

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).

VII-e - SolverFinishDialog

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

VII-f - SolverGet

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.

TypeNum Retour
1 Référence de la cellule Cible, ou la
valeur #N/A dans le cas où le solveur

- 16 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

n'est pas utilisé sur la page (active ou

spécifiée en second paramètre)
2 Valeur de l'objectif à atteindre (1 pour
Maximum, 2 pour Minimum, 3 pour une
Valeur)
3 Valeur cible (cas où l'objectif est une
valeur fixée)
4 Référence de la cellule ou de la plage
de cellules variable
5 Nombre de contraintes
6 Tableau des cellules sur lesquelles sont
imposées des contraintes (sous forme
de texte)
7 Tableau des contraintes (1 pour <=, 2
pour =, 3 pour >=, 4 pour int et 5 pour
bin)
8 Tableaux des valeurs des contraintes
(sous forme de texte)
13 Vrai si « Modèle supposé linéaire » est
coché, Faux sinon
20 Vrai si « Supposé non négatif » est
coché, Faux sinon

Exemple :

?SolverGet (3, "Solveur")

40

VII-g - SolverLoad

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 "

VII-h - SolverOk

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"

VII-i - SolverOkDialog

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

VII-j - SolverOptions

Fonction qui permet de définir les options du solveur pour la résolution.

- 17 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

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

VII-k - SolverReset

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

VII-l - SolverSave

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.

VII-m - SolverSolve

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.

- 18 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

VII-n - Exemple d'utilisation

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

VIII - Exemple 2 : Optimisation de portefeuille

Dans certains cas de figure, on peut être amené à fixer des objectifs qui ne seront pas exactement atteints.
En utilisant le solveur pour chercher la frontière efficiente d'un portefeuille d'actifs (recherche du meilleur
investissement possible en tenant compte du risque couru). Il s'agit donc de fixer le risque (variance de portefeuille)
et de maximiser le rendement. Au final on obtient un graphe avec les portefeuilles optimaux par niveau de risque !
On utilise dans un premier temps le solveur pour trouver la variance minimale du portefeuille. Une fois celle-ci trouvée,
on effectue des paliers de 0.05%.

Sub Optimisation_PTF()
Dim i As Integer
For i = 25 To 43
Worksheets("Optimisation").Activate
SolverReset
SolverOk SetCell:="$K$18", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$15:$L$15", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$K$19", Relation:=2, FormulaText:="$I$" & i
SolverAdd CellRef:="$M$15", Relation:=2, FormulaText:="1"
SolverOptions AssumeNonNeg:=True
SolverSolve userfinish:=True
Range("J" & i).Value = Range("K19").Value
Range("K" & i).Value = Range("K18").Value
Next i
End Sub

IX - Fichier démo

Fichier démo

X - Conclusion

L'outil solveur peut être d'un grand aide pour les personnes qui souhaitent résoudre des équations sans avoir à
effectuer un grand nombre de calculs manuellement ou par le code. On peut constater que parfois le solveur n'apporte
qu'une réponse approximative, mais qui peut convenir selon le degré d'exactitude visé par l'utilisateur.

- 19 -
[Link]
 Utilisation du Solveur sous Excel par Jean-Philippe ANDRÉ

XI - Webographie

[Link]
%C3%A0-l-aide-du-solveur-5d1a388f-079d-43ac-a7eb-f63e45925040, page de support de Microsoft sur "
Comment faire pour créer des macros Visual Basic à l'aide du solveur Excel dans Excel 97 "

XII - Remerciements

Je tiens à remercier l'équipe de [Link] pour la qualité du site, à ceux qui ont participé à la relecture de cet
article, et de tous ceux qui contribuent à l'entraide autour du développement dans le cadre personnel et professionnel.

- 20 -
[Link]