Solveur Excel : optimisation des résultats, ajout de contraintes et enregistrement de solutions sous forme de scénarios

Vous configurez votre modèle de solveur Excel à l'aide de la boîte de dialogue Paramètres du solveur. Vous utilisez la zone Définir l'objectif pour spécifier la cellule d'objectif et vous utilisez le groupe À pour indiquer au Solveur Excel ce que vous voulez de la cellule d'objectif : la valeur maximale possible ; la valeur minimale possible ; ou une valeur spécifique. Enfin, vous utilisez la zone En modifiant les cellules variables pour spécifier les cellules que Solver peut utiliser pour insérer des valeurs afin d'optimiser le résultat.

Optimisation des résultats d'Excel Solver

Lorsque le solveur trouve une solution, vous pouvez choisir soit Conserver la solution du solveur, soit Restaurer les valeurs d'origine. Si vous choisissez Conserver la solution du solveur, Excel modifie définitivement la feuille de calcul. Vous ne pouvez pas annuler les modifications.

Avec votre modèle de feuille de calcul prêt pour Solver, voici les étapes à suivre pour trouver un résultat optimal pour votre modèle à l'aide de Solver :

Choisissez Données → Solveur.
Excel ouvre la boîte de dialogue Paramètres du solveur.

Dans la zone Définir l'objectif, entrez l'adresse de la cellule d'objectif de votre modèle.
Notez que si vous cliquez sur la cellule pour la saisir, Solver entre automatiquement une adresse de cellule absolue (par exemple, $B$14 au lieu de B14). Le solveur fonctionne bien dans tous les cas.

Dans le groupe À, sélectionnez une option :

• Max : renvoie la valeur maximale possible.
• Min : renvoie la valeur minimale possible.
• Valeur de : saisissez un nombre pour définir la cellule d'objectif sur ce nombre.

  Pour l'exemple de modèle, Value Of a été sélectionné et 0 entré dans la zone de texte.

Dans la zone En modifiant les cellules variables, entrez les adresses des cellules que vous souhaitez que le Solveur modifie pendant qu'il recherche une solution.
Dans l'exemple, les cellules changeantes sont B4 et C4. L'image suivante montre la boîte de dialogue Paramètres du solveur terminée.

La boîte de dialogue Paramètres du solveur terminée.

Cliquez sur Résoudre.
Le solveur se met au travail. Au fur et à mesure que Solver résout le problème, les boîtes de dialogue Afficher la solution d'essai peuvent s'afficher une ou plusieurs fois.

Dans n'importe quelle boîte de dialogue Afficher la solution d'essai qui s'affiche, cliquez sur Continuer pour faire avancer les choses.
Une fois l'optimisation terminée, Excel affiche la boîte de dialogue Résultats du solveur.

La boîte de dialogue Résultats du solveur et la solution au problème du seuil de rentabilité.

Sélectionnez l'option Conserver la solution du solveur.
Si vous ne souhaitez pas accepter le résultat, sélectionnez plutôt l'option Restaurer les valeurs d'origine.

Cliquez sur OK.

Vous pouvez demander à Solver d'afficher un ou plusieurs rapports qui vous donnent des informations supplémentaires sur les résultats. Dans la boîte de dialogue Résultats du solveur, utilisez la liste Rapports pour sélectionner chaque rapport que vous souhaitez afficher :

• Réponse : affiche des informations sur la cellule objectif, les cellules variables et les contraintes du modèle. Pour la cellule objectif et les cellules variables, Solver affiche les valeurs d'origine et finale.
• Sensibilité : tente de montrer à quel point une solution est sensible aux modifications des formules du modèle. La présentation du rapport de sensibilité dépend du type de modèle que vous utilisez.
• Limites : affiche la cellule d'objectif et sa valeur, ainsi que les cellules variables et leurs adresses, noms et valeurs.

Excel Solver peut utiliser plusieurs méthodes de résolution. Dans la boîte de dialogue Paramètres du solveur, utilisez la liste Sélectionner une méthode de résolution pour sélectionner l'un des éléments suivants :

• Simplex LP : à utiliser si votre modèle de feuille de calcul est linéaire. Dans les termes les plus simples possibles, un modèle linéaire est un modèle dans lequel les variables ne sont élevées à aucune puissance et aucune des fonctions dites transcendantes - telles que SIN et COS - n'est utilisée.
• GRG non linéaire : à utiliser si votre modèle de feuille de calcul est non linéaire et lisse. En termes généraux, un modèle lisse est un modèle dans lequel un graphique de l'équation utilisée ne montre pas d'arêtes vives ou de cassures.
• Évolutif : à utiliser si votre modèle de feuille de calcul est non linéaire et non lisse.

Avez-vous à vous soucier de tout cela? Presque certainement pas. Excel Solver utilise par défaut GRG Nonlinear, et cela devrait fonctionner pour presque tout ce que vous faites avec Solver.

Ajout de contraintes à Excel Solver

Le monde réel impose des restrictions et des conditions aux formules. Une usine peut avoir une capacité maximale de 10 000 unités par jour, le nombre d'employés dans une entreprise ne peut pas être un nombre négatif et vos frais de publicité peuvent être limités à 10 % des dépenses totales.

De même, supposons que vous exécutiez une analyse de rentabilité sur deux produits. Si vous exécutez l'optimisation sans aucune restriction, Solver peut atteindre un profit total de 0 en définissant un produit sur une légère perte et l'autre sur un léger profit, où la perte et le profit s'annulent. En fait, si vous regardez de près l'image précédente, c'est exactement ce que Solver a fait. Pour obtenir une véritable solution d'équilibre, vous préférerez peut-être que les deux valeurs de profit du produit soient égales à 0.

De telles restrictions et conditions sont des exemples de ce que Solver appelle des contraintes. L'ajout de contraintes indique au Solveur de trouver une solution pour que ces conditions ne soient pas violées.

Voici comment exécuter Solver avec des contraintes ajoutées à l'optimisation :

Choisissez Données → Solveur.
Excel ouvre la boîte de dialogue Paramètres du solveur.

Utilisez la zone Définir l'objectif, le groupe À et la zone En modifiant les cellules variables pour configurer le Solveur comme décrit ci-dessus.

Cliquez sur Ajouter.
Excel affiche la boîte de dialogue Ajouter une contrainte.

Dans la zone Référence de cellule, entrez l'adresse de la cellule que vous souhaitez contraindre.
Vous pouvez taper l'adresse ou sélectionner la cellule sur la feuille de calcul.

Dans la liste déroulante, sélectionnez l'opérateur que vous souhaitez utiliser.
La plupart du temps, vous utilisez un opérateur de comparaison, tel que égal à (=) ou supérieur à (>). Utilisez l'opérateur int (entier) lorsque vous avez besoin qu'une contrainte, telle que le nombre total d'employés, soit une valeur entière au lieu d'un nombre réel (c'est-à-dire un nombre avec une composante décimale ; vous ne pouvez pas avoir 10,5 employés !). Utilisez l'opérateur bin (binaire) lorsque vous avez une contrainte qui doit être VRAI ou FAUX (ou 1 ou 0).

If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.
This image shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

The completed Add Constraint dialog box.

To specify more constraints, click Add and repeat Steps 4 through 6, as needed.

For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.

Click OK.

Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.

Click Solve.

Dans n'importe quelle boîte de dialogue Afficher la solution d'essai qui s'affiche, cliquez sur Continuer pour faire avancer les choses.
L'image ci-dessous montre l'exemple de solution d'équilibre avec les contraintes ajoutées. Notez que non seulement la cellule Profit total (B14) est définie sur 0, mais aussi les deux cellules Profit produit (B12 et C12).

La boîte de dialogue Résultats du solveur et la solution finale au problème du seuil de rentabilité.

Sélectionnez l'option Conserver la solution du solveur.
Si vous ne souhaitez pas accepter le résultat, sélectionnez plutôt l'option Restaurer les valeurs d'origine.

Cliquez sur OK.

Vous pouvez ajouter un maximum de 100 contraintes. De plus, si vous devez modifier une contrainte avant de commencer la résolution, sélectionnez la contrainte dans la zone de liste Soumis aux contraintes, cliquez sur Modifier, puis effectuez vos ajustements dans la boîte de dialogue Modifier la contrainte qui s'affiche. Si vous souhaitez supprimer une contrainte dont vous n'avez plus besoin, sélectionnez la contrainte, puis cliquez sur Supprimer.

Enregistrer une solution Excel Solver en tant que scénario

Chaque fois que vous avez un modèle de feuille de calcul qui utilise un ensemble cohérent de valeurs d'entrée - appelées cellules changeantes - vous avez ce qu'Excel appelle un scénario. Avec Solver, ces cellules changeantes sont ses cellules variables, donc une solution Solver équivaut à une sorte de scénario dans Excel . Cependant, Solver ne vous offre pas un moyen simple d'enregistrer et de réexécuter une solution particulière. Pour contourner ce problème, vous pouvez enregistrer une solution en tant que scénario que vous pourrez ensuite rappeler ultérieurement à l'aide de la fonctionnalité Gestionnaire de scénarios d'Excel.

Suivez ces étapes pour enregistrer une solution Solver en tant que scénario :

Choisissez Données → Solveur.
Excel ouvre la boîte de dialogue Paramètres du solveur.

Utilisez la zone Définir l'objectif, le groupe À, la zone En modifiant les cellules variables et la liste Sujet aux contraintes pour configurer le Solveur comme décrit ci-dessus.

Cliquez sur Résoudre.

Chaque fois que la boîte de dialogue Afficher la solution d'essai apparaît, choisissez Continuer.
Une fois l'optimisation terminée, Excel affiche la boîte de dialogue Résultats du solveur.

Cliquez sur Enregistrer le scénario.
Excel affiche la boîte de dialogue Enregistrer le scénario.

Dans la boîte de dialogue Nom du scénario, saisissez un nom pour le scénario, puis cliquez sur OK.
Excel vous renvoie à la boîte de dialogue Résultats du solveur.

Sélectionnez l'option Conserver la solution du solveur.
Si vous ne souhaitez pas accepter le résultat, sélectionnez plutôt l'option Restaurer les valeurs d'origine.

Cliquez sur OK.