Comment utiliser le solveur Excel 2019

Bien que les commandes Tableau de données et Recherche d'objectifs d' Excel fonctionnent parfaitement pour des problèmes simples qui nécessitent de déterminer la relation directe entre les entrées et les résultats dans une formule, vous devez utiliser le complément Solver pour traiter des problèmes plus complexes. Par exemple, utilisez le Solver pour trouver la meilleure solution lorsque vous devez modifier plusieurs valeurs d'entrée dans votre modèle Excel et que vous devez imposer des contraintes sur ces valeurs et/ou la valeur de sortie.

Le complément Solver fonctionne en appliquant des méthodes itératives pour trouver la « meilleure » solution en fonction des entrées, de la solution souhaitée et des contraintes que vous imposez. À chaque itération, le programme applique une méthode d'essais et d'erreurs (basée sur l'utilisation d'équations et d'inéquations linéaires ou non linéaires) qui tente de se rapprocher de la solution optimale.

Lorsque vous utilisez le complément Solver, gardez à l'esprit que de nombreux problèmes, en particulier les plus complexes, ont de nombreuses solutions. Bien que le solveur renvoie la solution optimale, compte tenu des valeurs de départ, des variables qui peuvent changer et des contraintes que vous définissez, cette solution n'est souvent pas la seule possible et, en fait, peut ne pas être la meilleure solution pour vous. Pour être sûr de trouver la meilleure solution, vous pouvez exécuter le solveur plusieurs fois, en ajustant les valeurs initiales à chaque fois que vous résolvez le problème.

Lors de la configuration du problème pour le complément Solver dans votre feuille de calcul Excel, définissez les éléments suivants :

• Cellule objectif : la cellule cible de votre feuille de calcul dont la valeur doit être maximisée, minimisée ou amenée à atteindre une valeur particulière. Notez que cette cellule doit contenir une formule.
• Cellules variables : les cellules changeantes de votre feuille de calcul dont les valeurs doivent être ajustées jusqu'à ce que la réponse soit trouvée.
• Cellules de contrainte : les cellules qui contiennent les limites que vous imposez aux valeurs changeantes dans les cellules variables et/ou la cellule cible dans la cellule objectif.

Une fois que vous avez fini de définir le problème avec ces paramètres et que le complément Solver a résolu le problème, le programme renvoie la solution optimale en modifiant les valeurs dans votre feuille de calcul. À ce stade, vous pouvez choisir de conserver les modifications dans la feuille de calcul ou de restaurer les valeurs d'origine dans la feuille de calcul. Vous pouvez également enregistrer la solution en tant que scénario à afficher ultérieurement avant de restaurer les valeurs d'origine.

Vous pouvez utiliser le complément Solver avec le gestionnaire de scénarios pour vous aider à configurer un problème à résoudre ou pour enregistrer une solution afin de pouvoir l'afficher à une date ultérieure. Les cellules changeantes que vous définissez pour le gestionnaire de scénarios sont automatiquement récupérées et utilisées par le solveur lorsque vous sélectionnez cette commande, et vice versa. Vous pouvez également enregistrer la solution du solveur à un problème en tant que scénario (en cliquant sur le bouton Enregistrer le scénario dans la boîte de dialogue Solveur) que vous pouvez ensuite afficher avec le gestionnaire de scénarios.

Configuration et définition du problème dans Excel 2019

La première étape de la configuration d'un problème sur lequel le solveur doit travailler consiste à créer le modèle de feuille de calcul pour lequel vous définirez la cellule d'objectif, les cellules de variables et les cellules de contrainte.

Gardez à l'esprit que le solveur est un utilitaire complémentaire. Cela signifie qu'avant de pouvoir l'utiliser, vous devez vous assurer que le programme complémentaire Solveur est toujours chargé, comme indiqué par l'apparition du bouton Solveur dans le groupe Analyse à la fin de l'onglet Données sur le ruban. Si ce bouton est manquant, vous pouvez charger Solver en ouvrant l'onglet Compléments de la boîte de dialogue Options Excel (Alt+FTAA), puis en cliquant sur le bouton Aller après vous être assuré que les compléments Excel sont affichés dans la liste déroulante Gérer. zone de liste à sa gauche immédiate. Ensuite, cochez la case Complément Solveur dans la boîte de dialogue Compléments pour y mettre une coche avant de cliquer sur OK pour fermer la boîte de dialogue et recharger le complément.

Pour définir et résoudre un problème avec le complément Solver après avoir chargé le complément et créé votre modèle de feuille de calcul, procédez comme suit :

Cliquez sur le bouton de commande Solveur dans le groupe Analyser à la fin de l'onglet Données du ruban.

Excel ouvre la boîte de dialogue Paramètres du solveur.

Spécification des paramètres à appliquer au modèle dans la boîte de dialogue Paramètres du solveur.

Cliquez sur la cellule cible dans la feuille de calcul ou entrez sa référence de cellule ou son nom de plage dans la zone de texte Définir l'objectif.

Ensuite, vous devez sélectionner le paramètre À. Cliquez sur le bouton d'option Max lorsque vous souhaitez que la valeur de la cellule cible soit aussi grande que possible. Cliquez sur le bouton d'option Min lorsque vous souhaitez que la valeur de la cellule cible soit aussi petite que possible. Cliquez sur le bouton d'option Valeur de, puis entrez une valeur dans la zone de texte associée lorsque vous souhaitez que la valeur de la cellule cible atteigne une valeur particulière.

Cliquez sur la case d'option appropriée dans la section À de la boîte de dialogue. Si vous sélectionnez le bouton d'option Valeur de, entrez la valeur à faire correspondre dans la zone de texte associée.

Ensuite, désignez les cellules variables, c'est-à-dire celles que le Solveur peut modifier pour atteindre votre objectif Égal à.

Cliquez sur la zone de texte En modifiant les cellules variables, puis sélectionnez les cellules à modifier dans la feuille de calcul ou entrez leurs références de cellule ou leur nom de plage dans la zone de texte.

N'oubliez pas que pour sélectionner des cellules non adjacentes dans la feuille de calcul, vous devez maintenir la touche Ctrl enfoncée pendant que vous cliquez sur chaque cellule de la sélection. Pour qu'Excel choisisse les cellules changeantes pour vous en fonction de la cellule cible que vous avez sélectionnée, cliquez sur le bouton Deviner à droite de cette zone de texte.

Avant que Solveur ajuste votre modèle, vous pouvez ajouter des contraintes pour la cellule cible ou l'une des cellules changeantes qui déterminent ses limites lors de l'ajustement des valeurs.

(Facultatif) Cliquez sur le bouton Ajouter à droite de la zone de liste Sujet aux contraintes dans la boîte de dialogue Paramètres du solveur.

Cette action ouvre la boîte de dialogue Ajouter une contrainte. Lors de la définition d'une contrainte, choisissez la cellule dont vous souhaitez contraindre la valeur ou sélectionnez la cellule dans la feuille de calcul ou entrez sa référence de cellule dans la zone de texte Référence de cellule. Sélectionnez ensuite la relation (=, <=,>=, ou int pour entier ou bin pour binaire) dans la liste déroulante à droite et (sauf si vous avez choisi int ou bin ) entrez la valeur ou la référence de cellule appropriée dans le champ Zone de texte de contrainte.

To continue adding constraints for other cells used by the Solver, click the Add button to add the constraint and clear the text boxes in the Add Constraint dialog box. Then, repeat Step 5 to add a new constraint. After you finish defining constraints for the target cell and changing values in the model, click OK to close the Add Constraint dialog box and return to the Solver Parameters dialog box (which now lists your constraints in the Subject to the Constraints list box).

(Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7.

(Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.

Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.

Solving the problem with Excel’s Solver

When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.

When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.

The Solver Results dialog box showing that Solver found a solution to the problem.

Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.

Changing Excel’s Solver options

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.

When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.

Saving and loading a model problem in Excel 2019

The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.

Lorsque vous cliquez sur le bouton Charger/Enregistrer, Excel ouvre la boîte de dialogue Charger/Enregistrer le modèle, contenant une zone de texte Sélectionner la zone de modèle. Cette zone de texte contient les références de cellule pour une plage suffisamment grande pour contenir tous les paramètres du problème, en commençant par la cellule active. Pour enregistrer les paramètres du problème dans cette plage, cliquez sur OK. Si cette plage comprend des cellules avec des données existantes, vous devez modifier la référence de cellule dans cette zone de texte avant de cliquer sur OK pour empêcher Excel de remplacer les données existantes.

Après avoir cliqué sur OK, Excel copie les paramètres du problème dans la plage spécifiée. Ces valeurs sont ensuite enregistrées dans la feuille de calcul la prochaine fois que vous enregistrez le classeur. Pour réutiliser ces paramètres de problème lors de la résolution d'un problème, il vous suffit d'ouvrir la boîte de dialogue Options du solveur, de cliquer sur le bouton Charger/Enregistrer pour ouvrir la boîte de dialogue Charger/Enregistrer le modèle, de cliquer sur le bouton Charger, puis de sélectionner la plage contenant le paramètres du problème. Lorsque vous cliquez sur OK dans la boîte de dialogue Charger le modèle, Excel charge les paramètres de cette plage de cellules dans les zones de texte appropriées de la boîte de dialogue Paramètres du solveur. Vous pouvez ensuite fermer la boîte de dialogue Options du solveur en cliquant sur OK et vous pouvez résoudre le problème en utilisant ces paramètres en cliquant sur le bouton de commande Résoudre.

N'oubliez pas que vous pouvez utiliser le bouton Réinitialiser tout chaque fois que vous souhaitez effacer tous les paramètres définis pour le problème précédent et remettre les options du Solveur à leurs valeurs par défaut.

Création de rapports de solveur dans Excel 2019

Vous pouvez créer trois types de rapports différents avec le solveur :

• Rapport de réponse : répertorie la cellule cible et les cellules changeantes avec leurs valeurs d'origine et finale, ainsi que les contraintes utilisées pour résoudre le problème.
• Rapport de sensibilité : indique la sensibilité d'une solution optimale aux modifications des formules qui calculent la cellule cible et les contraintes. Le rapport montre les cellules changeantes avec leurs valeurs finales et le gradient réduit pour chaque cellule. (Le gradient réduit mesure l'objectif par unité d'augmentation dans la cellule changeante.) Si vous avez défini des contraintes, le rapport de sensibilité les répertorie avec leurs valeurs finales et le multiplicateur de Lagrange pour chaque contrainte. (Le multiplicateur de Lagrange mesure l'objectif par unité d'augmentation qui apparaît dans la partie droite de l'équation de contrainte.)
• Rapport sur les limites : affiche la cellule cible et les cellules changeantes avec leurs valeurs, leurs limites inférieures et supérieures et leurs résultats cibles. La limite inférieure représente la valeur la plus basse qu'une cellule changeante peut avoir tout en fixant les valeurs de toutes les autres cellules et en satisfaisant toujours les contraintes. La limite supérieure représente la valeur la plus élevée qui fera cela.

Excel place chaque rapport que vous générez pour un problème de solveur dans une feuille de calcul distincte du classeur. Pour générer un (ou tous) de ces rapports, sélectionnez le type de rapport (Réponse, Sensibilité ou Limites) dans la zone de liste Rapports de la boîte de dialogue Résultats du solveur. Pour sélectionner plusieurs rapports, cliquez simplement sur le nom du rapport.

Lorsque vous cliquez sur OK pour fermer la boîte de dialogue Résultats du solveur (après avoir choisi entre les options Conserver la solution du solveur et Restaurer les valeurs d'origine), Excel génère le ou les rapports que vous avez sélectionnés dans une nouvelle feuille de calcul qu'il ajoute au début du classeur . (Les onglets de la feuille de rapport sont nommés par type de rapport, comme dans le rapport de réponse 1, le rapport de sensibilité 1 et le rapport de limites 1. )