Tableur EXCEL/Solveur

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Présentation des termes : Solveur et Valeur Cible

Qu'est-ce que le Solveur ?

Le solveur est une fonction présente dans EXCEL. C'est un outil très puissant qui permet à la fois d'optimiser et d'allouer des ressources. Cet outil est souvent utilisé pour résoudre des équations. En effet, il permet de trouver le minimum, le maximum ou la valeur la plus proche d'une donnée tout en respectant les contraintes que l'on a émise. Le solveur a donc le pouvoir de donner la meilleure solution, c'est-à-dire l'optimum.

En règle générale, le solveur est utilisé lorsque l'on recherche la valeur optimale d'une cellule donnée (la fonction économique) par l'ajustement des valeurs d'autres cellules (les variables) en respectant des conditions limitées par des valeurs numériques (les contraintes).

• La programmation linéaire avec le Solveur

Un programme linéaire est un problème d'optimisation de la forme c'est-à-dire maximiser ou minimiser une expression linaire en les variables (X1, X2,...,Xn) sous des contraintes. Le Solveur d'EXCEL peut résoudre ces problèmes par l'algorithme simplexe. Le simplexe est une méthode de calcul basée sur la méthode de Gauss-Jordan pour la résolution de systèmes d'équations linéaires.

• La programmation non linéaire avec le Solveur

Lorsque le modèle n’est pas linéaire, le Solveur doit tenter dans ce cas, de trouver une solution par approximations successives appelées "itérations".

Qu'est-ce que la Valeur Cible ?

La Valeur Cible est une fonction d'EXCEL qui a le même objectif que le Solveur mais sans les contraintes. Cet outil est utilisé lorsqu'on veut qu'une cellule atteigne une valeur particulière. La cellule doit obligatoirement contenir une formule. La Valeur Cible a besoin de 3 paramètres pour fonctionner :

• la référence de la cellule pour laquelle on veut affecter la valeur particulière
• la valeur déterminée pour cette cellule
• la cellule variable qui doit être modifiée pour atteindre la valeur cible.

Procédures à suivre sur Excel

Comment utiliser le Solveur ?

Il est possible que la fonction "Solveur" ne soit pas activée dans Excel.

Pour activer le Solveur dans Excel 2007/2010, il faut faire la manipulation suivante :

Menu => Fichier => Options => Compléments. Dans les compléments, cliquez sur "Atteindre" dans "Gérer les compléments Excel". Dans la fenêtre qui s'affiche, cochez "Complément Solveur". L'option "Solveur" apparaît dorénavant dans le menu "Données".

Ensuite, il faut paramétrer le solveur :

• Dans la zone "Cellule cible à définir", il faut déterminer la cellule pour laquelle on souhaite maximiser ou minimiser, c'est-à-dire la fonction économique. Dans ce cas, plusieurs choix sont possibles : maximiser "max", minimiser "min" ou alors si l'on veut que la cellule cible se rapproche le plus d'une valeur, il faut choisir le bouton "valeur" et indiquer la valeur souhaitée dans la zone à droite du bouton.

Quelques remarques :

- pour aller plus vite, cliquer directement dans la cellule cible plutôt que de taper sa référence au clavier.

- la cellule cible doit contenir une formule dépendant directement ou indirectement des cellules variables définies dans la zone "cellules variables"*.

• Dans la zone "Cellules variables", il faut déterminer les cellules pour lesquelles les valeurs peuvent être modifiées par le solveur en prenant en compte les contraintes et pour que la cellule cible atteigne le résultat attendu.

Quelques remarques :

- pour aller plus vite, cliquer-glisser sur les cellules variables plutôt que de les saisir au clavier.

- il est possible que le solveur propose automatiquement les cellules variables par rapport à la valeur cible. Attention à bien contrôler que les cellules définies soient les bonnes.

- pour information, il est possible de spécifier jusqu'à 200 cellules variables

• Pour définir les contraintes, il faut s'aider des boutons "ajouter", "modifier" ou "supprimer" de la boîte de dialogue pour établir la liste des contraintes dans la zone "contraintes".

Quelques remarques :

- la cellule à laquelle l'étiquette "cellule" fait référence contient habituellement une formule qui dépend des cellules variables.

- le solveur gère jusqu'à 200 contraintes.

• Une fois tous les paramètres du solveur renseignés, appuyer sur le bouton "résoudre" ce qui permet de résoudre le problème et d'obtenir le résultat. Les résultats du solveur peuvent être gardés ou rétablis par les valeurs d'origines.
• Il est possible de créer le rapport du solveur en sélectionnant celui qui nous concernera. Le rapport sera présent en bas de l'écran. Il donne l'évolution des cellules variables et de la cellule cible et rappelle également les différentes valeurs des contraintes, leurs formules et dans quelle mesure elles ont été respectées. Lorsque l'état est "lié" cela signifie que la valeur finale de la cellule contenant une contrainte atteint la valeur maximum. Quand l'état est "non lié" cela correspond au fait que la contrainte est respectée mais la valeur finale de la cellule n'est pas égale à la valeur minimum ou maximum de la contrainte.

Comment utiliser la Valeur Cible ?

Pour utiliser la fonction "Valeur Cible", il faut faire la manipulation suivante :

Menu => Données => Analyse de scénarios => Valeur Cible

Exemple : comment résoudre un problème donné ?

Exemple Solveur

Un commerçant a vendu 500 produits. Chacun d'entre eux est vendu 25 €. Leur prix d'achat était de 15 €. Le commerçant souhaite augmenter sa marge jusqu'à 15 000 €. Au vu de la concurrence, il sait qu'il ne pourra pas vendre plus de 700 produits et qu'aucun concurrent ne les vend plus de 40 € pièce. Ses fournisseurs, eux, n'augmenteront pas leur prix au-dessus de 10 €. Le solveur va donc agir sur les variables colorées afin de modifier la marge.

Etape 1 :

Objectif à atteindre : sélection de la cellule affichant le montant de la marge

Valeur : 15000

Cellules variables : quantités vendues, prix de vente et prix d'achat unitaire.

Les contraintes : On va limiter la quantité vendue à 700 produits, le prix de vente unitaire à 40 € et le prix d'achat unitaire à 10 €. Pour ajouter une contrainte, cliquez sur "Ajouter".

Limitation du prix de vente unitaire :

- on sélectionne la cellule correspondant au prix de vente

- on fixe la limite inférieure ou égale à 40

- il faut ajouter les contraintes pour la quantité et le prix d'achat Pour la quantité, on veut que la valeur soit inférieure ou égale à 700. Pour le prix d'achat, il faut que la valeur soit égale ou inférieure à 10 €.

Une fois la saisie de toutes les contraintes, on clique sur "Résoudre". Le solveur propose alors une solution et vous demande si vous souhaitez conserver sa solution ou rétablir les valeurs d'origines. Vous pouvez, en cochant la case correspondante, retourner aux paramètres pour effectuer des modifications.

Voici les résultats donnés par le solveur pour cet exemple :

Exemple Valeur Cible

Un élève a besoin d'arriver à une moyenne de 14 pour pouvoir obtenir son examen. Sur 4 notes, il lui en manque une pour former sa moyenne générale. Il va essayer de calculer quelle note il doit obtenir pour avoir 14 de moyenne générale.

Attention, la cellule E3 qui comprend la note moyenne et qui devra atteindre 14, doit comporter une fonction, dans ce cas, il s'agit de la formule de la moyenne. Sinon la valeur cible ne pourra pas s'appliquer.

Cellule à définir : celle qui contient l'objectif à atteindre, en l’occurrence ici, c'est la note moyenne.

Valeur à atteindre : valeur que devra atteindre la note moyenne, ici, il faut obtenir 14.

Cellule à modifier : c'est la cellule qui permettra d'atteindre la moyenne de 14, nécessaire à l'élève pour valider son examen. Dans cet exemple, il s'agit de la note d'Histoire.

Une fois que toutes les données ont été saisies, il faut valider.

Une nouvelle fenêtre s'ouvre et dans la cellule D3, apparaît le résultat.

La note minimale nécessaire en Histoire doit être de 18, pour cet exemple.