Un guide complet sur l’utilisation de Solver dans Excel pour résoudre des problèmes

Microsoft Excel est doté de diverses fonctionnalités qui simplifient le processus de calcul et de résolution d’équations, améliorant ainsi la productivité. L’une de ces fonctionnalités est l’outil Solveur, qui présente des similitudes avec la fonction de recherche d’objectif.

Généralement utilisé pour l’analyse de type « What-if », Solver permet aux utilisateurs de déterminer la valeur d’une cellule particulière sous plusieurs contraintes. Il peut être utilisé pour déterminer une valeur spécifique, un seuil minimum ou une limite maximum pour un nombre. Bien qu’il ne puisse pas résoudre tous les problèmes, Solver est une ressource inestimable pour les scénarios d’optimisation où trouver la meilleure décision possible est crucial.

Cet outil fonctionne en ajustant les valeurs de cellules spécifiques appelées variables de décision dans une feuille de calcul pour identifier la valeur maximale ou minimale d’une autre cellule, appelée cellule objective. Solver est applicable à divers types de programmation, notamment la programmation linéaire et non linéaire, la programmation en nombres entiers et les tâches de recherche d’objectifs.

Les applications typiques de Solver incluent la minimisation des dépenses de transport, l’élaboration d’horaires de travail optimaux, l’établissement du meilleur budget pour les initiatives publicitaires ou la maximisation du retour sur investissement, pour n’en citer que quelques-unes.

Activation du Solveur dans Excel

Pour commencer à utiliser Solver, vous devez d’abord activer ce module complémentaire, car il n’est pas activé par défaut comme la fonction Goal Seek. Heureusement, le processus est assez simple.

Commencez par sélectionner le menu Fichier en haut de l’écran, puis cliquez sur « Options ».

Ensuite, cliquez sur « Compléments » situé sur le côté gauche de la fenêtre Options.

Maintenant, sélectionnez « Compléments Excel » dans le menu déroulant « Gérer » en bas et cliquez sur « Aller ».

Dans la boîte de dialogue suivante, cochez la case à côté de « Solver Add-in » pour l’activer, puis cliquez sur « OK ».

Vous devriez maintenant voir Solver disponible lorsque vous cliquez sur l’onglet « Données » dans Excel.

Composants clés du solveur

Avant que Solver puisse identifier la valeur optimale pour un problème, trois composants principaux doivent être établis :

Cellule Objectif : Cette cellule contient la formule qui représente le but ou la cible du problème, qu’il s’agisse de minimiser, de maximiser ou d’atteindre une valeur spécifique.
Cellules variables : ces cellules contiennent les variables que Solver ajustera pour atteindre l’objectif. Un maximum de 200 cellules variables peuvent être désignées dans Solver.
Contraintes : Les contraintes sont les paramètres dans lesquels Solver doit opérer pour obtenir le résultat souhaité. Elles définissent les conditions qui doivent être satisfaites lors de la détermination des valeurs requises.

Application du solveur

Une fois que Solver a été ajouté à Excel, vous pouvez procéder à son utilisation. Dans cet exemple, nous utiliserons Solver pour calculer le bénéfice d’une entreprise de fabrication de palettes en fonction de valeurs de ressources connues, telles que les ressources nécessaires par palette ainsi que la disponibilité de différents types de palettes.

Les cellules B3 à E3 répertorient les différents types de palettes que l’entreprise doit produire. La ligne directement en dessous représente le nombre de palettes à fabriquer pour chaque type, initialisé à zéro. La ligne suivante détaille le bénéfice associé à chaque type de palette. Notre objectif est de déterminer le nombre de palettes à fabriquer pour chaque type, le bénéfice total étant affiché dans la cellule F5. Les contraintes ici sont les ressources disponibles, qui dictent le nombre de palettes que l’entreprise peut produire de manière réalisable.

Pour commencer, cliquez sur « Solveur » situé dans le coin supérieur droit, ce qui fera apparaître la boîte de dialogue Solveur. Saisissez un nom ou une référence de cellule pour la cellule d’objectif, en vous assurant qu’elle contient une formule. Dans ce scénario, la cellule F5 sert de fonction d’objectif, qui donne le bénéfice total pour tous les types de palettes combinés, en tenant compte à la fois des ressources disponibles et des palettes à produire.

Dans le champ « En modifiant les cellules variables », sélectionnez la plage B4:E4 soit en faisant glisser votre souris, soit en saisissant directement les noms des cellules. Ces cellules représentent le nombre de palettes par type et sont actuellement définies sur zéro. Le solveur ajustera ces valeurs pendant l’exécution.

Cliquez ensuite sur le bouton « Ajouter » pour introduire des contraintes. Solver calculera le nombre de palettes que l’entreprise peut fabriquer en fonction de la disponibilité des matériaux, comme la colle, le pressage, les copeaux de pin et de chêne. Vous observerez que les valeurs de la colonne « Utilisé », actuellement à zéro, changent lorsque vous exécutez Solver.

Tapez F8:F11 pour la « Référence de cellule », qui correspond à la colonne « Utilisé », et G8:G11 pour la colonne « Disponible » dans le champ Contrainte. Assurez-vous que la relation est définie <=par défaut, indiquant que les valeurs de la colonne Utilisé doivent être inférieures ou égales à celles de la colonne Disponible.

Après avoir saisi toutes les variables et contraintes, cliquez à nouveau sur « Ajouter » dans la boîte de dialogue « Ajouter une contrainte », puis fermez-la. Vous remarquerez également que l’option « Rendre les variables sans contrainte non négatives » est activée par défaut dans la boîte de dialogue Paramètres du solveur, garantissant que toutes les variables restent non négatives même si aucune contrainte spécifique n’est définie.

Après avoir complété les entrées dans la boîte de dialogue Paramètres du solveur, cliquez sur le bouton « Résoudre » et attendez qu’Excel fournisse les résultats.

Une fois les résultats générés, la boîte de dialogue Résultats du solveur apparaît, révélant les nouvelles valeurs dans les cellules B4 à E4. Gardez à l’esprit que le solveur modifie vos données ; si vous préférez revenir aux valeurs d’origine, vous pouvez sélectionner l’option « Restaurer les valeurs d’origine ». Après avoir décidé de conserver la solution ou de revenir aux données d’origine, assurez-vous que l’option « Réponse » est cochée à droite, puis cliquez sur « OK » pour quitter la boîte de dialogue.

Si vous choisissez de conserver la nouvelle solution, elle sera reflétée dans votre feuille de calcul à la fermeture de la boîte de dialogue Solveur. La production de l’entreprise comprendra 23 palettes Tahoe, 15 palettes Pacific, 39 palettes Savannah et aucune des palettes Aspen, qui seront indiquées dans la ligne Palettes de B4 à D4. De plus, la cellule de profit total sera mise à jour de zéro à 58 800 $.

Considérations importantes

Tout comme la fonction de recherche d’objectif d’Excel, Solver nécessite que vous préétablissiez les formules nécessaires pour qu’il fonctionne correctement.
Vous pouvez influencer la méthode de résolution de problèmes en sélectionnant le bouton « Options » dans la boîte de dialogue Paramètres du solveur, où vous pouvez spécifier des valeurs pour « Toutes les méthodes », « GRG non linéaire » et « Évolutionnaire ».
De plus, Solver vous permet d’enregistrer et de charger des modèles pour une utilisation ultérieure. Lors du chargement de modèles existants, veillez à saisir la référence de l’ensemble des cellules pertinentes pour le problème en question.
Il est conseillé de travailler avec une copie de vos données lors de l’utilisation de Solver, car il modifie les données d’origine une fois exécuté, et la récupération de ces données peut ne pas être possible une fois les modifications apportées.

Source