Exploiter le solveur

D’ordinaire, dans une feuille de calcul, vous tapez des nombres, puis une formule les utilisant, et Excel calcule le résultat. Mais vous voudriez faire l’inverse : qu’Excel calcule les chiffres qui, avec une formule donnée,
permettent d’obtenir un résultat précis… Pour cela, vous pouvez utiliser le Solveur d’Excel, qui fait varier les valeurs des cellules que vous lui indiquez jusqu’à atteindre le résultat souhaité ; ou sa version simplifiée, l’outil Valeur
Cible, si vous ne recherchez qu’une seule valeur. Afin de conserver dans votre feuille les valeurs initiales et celles que le solveur aura calculées, vous créerez des scénarios. Attention : les outils suivants demandent une bonne maîtrise
d’Excel.

1. Posez le problème

Pour montrer le fonctionnement du solveur, nous prendrons l’exemple illustré sur la capture ci-contre (voir écran). Vous passez deux commandes d’articles de papeterie. Le prix unitaire de chaque article, constant, figure
dans le champ B12:E12 (les valeurs en bleu foncé). Le total des deux commandes est en F9 (la cellule encadrée). Vous voulez ramener ce total de 2 403,30 euros à 2 200 euros. Nous allons
passer en revue les différents moyens d’y parvenir.

2. Recherchez une seule valeur

Pour atteindre un total de 2 200 euros, vous allez d’abord demander à Excel de ne faire varier qu’une cellule, par exemple D7 (le nombre de classeurs achetés dans la seconde commande). Pour une seule
cellule, vous pouvez utiliser une version ‘ allégée ‘ du solveur, la fonction Valeur Cible. Déroulez le menu Outils, Valeur cible. Dans la fenêtre qui apparaît, vous devez saisir trois
informations. La Cellule à définir, qui doit obligatoirement contenir une formule, est celle dont vous attendez un résultat donné. Ici, c’est la cellule F9. Tapez F9, ou cliquez dans le champ puis sur
la cellule F9 (Excel affiche $F$9, c’est normal). Dans le champ Valeur à atteindre, tapez 2 200. Enfin, la Cellule à modifier, qui contient la valeur recherchée, doit
impérativement être utilisée par la formule (directement ou indirectement). Tapez ici D7 (voir écran du haut). Enfin cliquez sur OK. Excel ouvre une fenêtre pour vous informer qu’il a trouvé une valeur (voir écran du
bas). Cliquez sur OK pour écrire le résultat (24,175) dans la feuille ou sur Annuler pour conserver la valeur actuelle. Cet exemple vous montre les limites des outils de résolution d’équation : dans
la réalité, le nombre de classeurs achetés est évidemment une valeur entière, mais Excel ignore cette contrainte et donne le résultat exact. Concrètement, il faudra acheter 24 classeurs (l’entier immédiatement inférieur) dans la seconde commande
pour faire descendre le total en dessous de 2 200 euros.

3. Installez le solveur

Nous allons maintenant compliquer le problème : pour obtenir votre total de 2 200 euros, vous acceptez de faire varier plusieurs cellules, par exemple les quantités d’articles commandés. Pour cela, vous devez employer le
Solveur. Parfois, cet outil n’est pas installé dans Excel. Pour le vérifier, déroulez le menu Outils. Si la commande Solveur ne s’y trouve pas, déroulez Outils, Macros complémentaires, cochez l’option Complément
Solver (voir écran). Cliquez sur OK, puis relancez Excel.

4. Conservez les valeurs d’origine

Pour atteindre l’objectif que vous allez lui fixer, le solveur va modifier certaines valeurs du tableau. Pour conserver les valeurs d’origine, mieux vaut créer des scénarios. Cette étape n’est pas obligatoire, mais elle permettra de
suivre le travail du solveur. Dans notre exemple, nous allons demander au solveur, toujours pour atteindre un total de 2 200 euros, de modifier les cellules des champs B3:E3 et B7:E7 (les quantités d’articles pour
chacune des deux commandes). Commencez par taper 75 dans la cellule D7, pour retrouver les valeurs d’origine. Sélectionnez le champ B3:E3, appuyez sur la touche Ctrl puis, en
la maintenant enfoncée, sélectionnez le champ B7:E7. Déroulez le menu Outils, Gestionnaire de scénarios. Dans la fenêtre qui s’affiche, cliquez sur Ajouter. Dans le champ Nom du scénario, tapez
Valeurs initiales et validez par OK. La fenêtre suivante vous présente la liste des contenus des cellules que vous avez choisies (voir écran). Validez encore par OK puis
cliquez sur Fermer pour quitter le Gestionnaire de scénarios.

5. Rétablissez les valeurs d’origine

Désormais, pour revenir à tout moment aux valeurs initiales de votre tableau, il suffit de dérouler le menu Outils, Gestionnaire de scénarios, de double-cliquer sur Valeurs initiales puis de cliquer sur
Fermer.

6. Lancez le solveur

Déroulez le menu Outils, Solveur. La fenêtre qui apparaît présente des similitudes avec l’outil Valeur cible. Dans le champ Cellule cible à définir, tapez F9. En dessous,
cochez l’option Valeur et saisissez 2 200 dans le champ voisin. Dans le champ Cellules variables, tapez B3:E3;B7:E7 (le point-virgule, dans n’importe
quelle formule, vous permet de spécifier deux champs distincts, (voir écran du haut). Enfin, cliquez sur Résoudre. Excel vous informe qu’il a trouvé une solution (voir écran du bas). Vous allez maintenant
enregistrer cette solution dans un nouveau scénario. Attention : ne cliquez pas sur le bouton Enregistrer le scénario car cette fonction souffre d’une anomalie. Cochez Garder la solution du tableur et cliquez sur
OK.

7. Créez un scénario avec le résultat

Pour créer un scénario avec ces résultats ?” sans erreur, cette fois ?” sélectionnez les champs B3:E3 et B7:E7, déroulez Outils, Gestionnaire de scénarios, et cliquez sur
Ajouter. Dans le champ Nom du scénario, tapez Calcul sans contraintes, cliquez deux fois sur OK puis sur Fermer pour quitter le Gestionnaire de
scénarios.

8. Fixez des contraintes

A l’étape précédente, vous avez laissé Excel modifier librement les huit cellules variables. Vous allez maintenant ajouter des contraintes. Par exemple, vous voulez que les quantités de la seconde commande soient toutes plus élevées
que celles de la première. Il faudra donc que la cellule B3 soit inférieure à B7, la cellule C3 à C7, et ainsi de suite. Revenez à votre tableau d’origine (voir étape 5) puis déroulez
Outils, Solveur et cliquez sur Ajouter. Tapez B3 dans le premier champ, B7 dans le dernier et sélectionnez <= dans la liste déroulante du milieu (voir
écran), avant de cliquer sur OK. De la même façon, ajoutez les contraintes C3 <= C7, D3 <= D7 et E3 <= E7 (ne tenez pas compte des caractères $). Cliquez maintenant sur Résoudre. Cochez
Garder la solution du tableur et cliquez sur OK. Et, comme à l’étape 7, créez un scénario avec les nouvelles valeurs que vous nommerez Calcul avec contraintes.

9. Affichez la synthèse des scénarios

Pour faire apparaître simultanément les nombres originels et ceux obtenus lors des deux utilisations du solveur, déroulez le menu Outils, Gestionnaire de scénarios. Vos trois scénarios apparaissent. Cliquez sur
Synthèse puis sur OK. Excel ajoute une feuille à votre classeur et y affiche les valeurs de chaque scénario (voir écran).