Excel Solveur : Optimisez les décisions complexes impliquant de multiples variables

L'analyse de scénarios vous permet de tester différents scénarios afin d'évaluer leur influence sur vos résultats. Il vous offre un environnement sécurisé pour tester différentes variables, telles que les coûts ou les capacités, afin de déterminer la solution la plus efficace. Cette approche est essentielle pour toute personne chargée de gérer des ressources, de planifier des calendriers ou de prendre des décisions commerciales stratégiques.

Les essais manuels par tâtonnements prennent des heures et aboutissent souvent à une solution qui n'est pas optimale. Solver assure l'optimalité mathématique en quelques secondes. C'est la différence entre deviner comment répartir les ressources et savoir que vous prenez la meilleure décision possible.

Ce tutoriel vous guide à travers Excel Solveur, la fonctionnalité avancée conçue pour ces problèmes d'optimisation. Je vais vous démontrer comment configurer votre feuille de calcul, exécuter l'analyse et interpréter les résultats à l'aide d'exemples concrets.

Comprendre Excel Solveur : Concepts fondamentaux

Avant de vous présenter l'utilisation de Solver, permettez-moi de vous expliquer les trois éléments fondamentaux de tout problème d'optimisation. Ce ne sont pas seulement des mots à la mode, ce sont les éléments qui permettent à Solver de fonctionner.

Cellule objective, variables de décision et contraintes

Considérez l' objectivede la cellule comme votre objectif. Il s'agit de la formule que vous souhaitez maximiser ou minimiser, telle que le bénéfice total ou le coût total. Il doit s'agir d'une formule, et non d'un simple nombre, car Solver doit pouvoir observer comment elle évolue lorsque vous modifiez certains paramètres.

Les variables décisionnelles sont ce que vous essayez de déterminer. Combien de chaises devrions-nous fabriquer ? Quel budget devrions-nous consacrer à chaque canal marketing ? Je commence toujours ces calculs à zéro, ce qui permet de voir facilement ce qui a changé après l'exécution du Solveur.

Les contraintes constituent vos limites et vos règles. Il n'est pas possible de dépasser votre budget. Il n'est pas possible de produire des quantités négatives. Certains projets nécessitent des niveaux d'investissement minimaux. Ces contraintes transforment votre problème mathématique en une situation réaliste qui reflète les limites réelles de l'entreprise.

Voici comment cela fonctionne : Solver ajuste vos variables de décision afin d'optimiser votre objectif tout en veillant à ce que toutes les contraintes soient respectées. Si vous avez déjà effectué des recherches opérationnelles, cela vous semblera familier. Si ce n'est pas le cas, considérez cela comme une approche intelligente d'essais et d'erreurs avec des garanties.

Comparaison avec la recherche d'objectif

On me pose souvent cette question. La recherche d'objectif est plus simple. Il modifie une cellule afin d'atteindre une cible spécifique. Utilisez cette fonction lorsque vous connaissez la réponse souhaitée et que vous avez simplement besoin de trouver l'entrée qui vous y mènera.

Solver est différent. Il identifie la solution optimale lorsque vous devez prendre plusieurs décisions et respecter certaines contraintes. LeSolveur est utilisé lorsquelorsque vous avez besoin de maximiser ou de minimiser quelque chose tout en gérant plusieurs variables.

Ma règle générale : si vous pouvez indiquer précisément le nombre souhaité, veuillez utiliser la fonction Recherche. Si vous vous demandez « quelle est la meilleure façon de procéder ? », veuillez utiliser Solveur.

Chargement et activation du complément Solveur

Solver est fourni avec Excel, mais il est nécessaire de l'activer au préalable. Veuillez ne pas vous inquiéter, cela ne prendra pas plus d'une minute.

Étapes d'installation pour Windows et Mac

Pour Windows :

1. Fichier > Options > Compléments
2. Veuillez sélectionner « Compléments Excel » dans le menu déroulant « Gérer », puis cliquer sur « OK ».
3. Vérifier Complément Solveur, puis cliquez sur OK.
4. Veuillez vous rendre sur le site onglet Données et localisez Solveur dans la section Analyser .

Pour Mac :

1. Outils > Excel Compléments
2. Vérifier Complément Solveur, puis cliquez sur OK

Si vous utilisez Excel Online, j'ai une mauvaise nouvelle. Il ne prend pas en charge Solver. Vous aurez besoin de la version bureau (2016 ou ultérieure).

Configuration

Une fois activé, vous pouvez accéder à Solveur via l'onglet Données > bouton Solveur. Les paramètres par défaut sont adéquats pour la plupart des situations, il n'est donc pas nécessaire de trop réfléchir à cette partie.

Cela dit, deux paramètres sont importants si vous rencontrez des difficultés. La précision de l' e contrôle le degré de rigueur avec lequel le solveur satisfait les contraintes. Je serre la vis pour les modèles financiers où chaque centime compte. de convergence indique à Solver quand il doit cesser d'essayer d'améliorer la solution. Je réduis cette valeur si Solver s'arrête prématurément avec une réponse qui n'est pas tout à fait optimale.

Vous pouvez trouver ces deux éléments dans la boîte de dialogue Paramètres du solveur > bouton Options, mais en réalité, je les modifie rarement.

Définition de votre problème : Modèle de formulation dans Excel

Une feuille de calcul claire facilite la configuration du Solveur.

Permettez-moi de vous présenter un exemple : un fabricant de meubles qui produit des chaises et des tableaux. Les chaises génèrent un bénéfice de 60 $ (nécessitant 4 heures de travail et 2 unités de bois). Les tableaux génèrent un bénéfice de 75 $ (nécessitant 6 heures de travail et 2 unités de bois). Nous disposons de 240 heures de travail et de 100 unités de bois. Combien devrions-nous en fabriquer de chaque type ?

Définition du problème et configuration du tableur

J'organise chaque modèle Solver de la même manière. Quatre sections, systématiquement :

1. Données d'entrée (informations sur les produits, limites des ressources)
2. Variables de décision (quantités à produire)
3. Fonction objective (profit total)
4. Calculs de contraintes (ressources utilisées par rapport aux ressources disponibles)

Modèle Solver complet avec les quatre sections. Image fournie par l'auteur.

Cette disposition rend tout transparent. Toute personne qui examine votre modèle peut immédiatement voir ce que vous optimisez et les contraintes auxquelles vous êtes soumis.

À présent, je crée systématiquement ce que l'on appelle des plages nommées pour les cellules importantes. Au lieu d'écrire =B8*B3+B9*B4, je peux écrire =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Beaucoup plus facile à comprendre six mois plus tard, lorsque vous avez oublié la signification de ces références de cellules.

Les plages nommées facilitent la lecture des formules. Image fournie par l'auteur.

Pour les créer : sélectionnez les cellules, cliquez sur la zone Nom, saisissez un nom, puis appuyez sur Entrée. Cela ne prend que deux secondes et vous évitera des complications ultérieures.

Variables de décision et fonction objectif

Je commence les variables de décision à zéro. Cela facilite les comparaisons. Certaines personnes préfèrent commencer par des valeurs estimées, ce qui est tout à fait acceptable, mais zéro me convient parfaitement.

La fonction objectif doit être une formule. Pour notre exemple de mobilier, j'utilise :

=SUMPRODUCT(B8:B9,B3:B4)

Ceci calcule : (Chaises × 60 $) + (Tableaux × 75 $)

Multiplication et addition simples. C'est tout ce dont vous avez besoin pour la plupart des problèmes commerciaux.

Contraintes

Les contraintes sont les éléments qui permettent de traduire les limites réelles de l'entreprise en termes mathématiques. Notre fabricant de meubles est confronté à deux contraintes en matière de ressources :

• Travail : =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Bois : =SUMPRODUCT(B8:B9,D3:D4) <= 100

Et voici une leçon que j'ai apprise à mes dépens : il est important de toujours ajouter des contraintes de non-négativité (B8:B9 >= 0). Sans ces informations, Solver pourrait suggérer la fabrication de chaises négatives, ce qui n'est évidemment pas possible dans la réalité.

J'ajoute également une colonne d'état avec des formules d'=IF() telles que =IF(B16<=C16,"OK","EXCEEDED") . Cela me fournit un retour visuel immédiat avant même de lancer Solver. Les voyants verts indiquent que tout est en ordre, tandis que les voyants rouges signalent un problème avec votre configuration.

Étape par étape : Comment utiliser Solveur dans Excel

Très bien, le modèle est construit. Je vais maintenant vous expliquer comment utiliser Solver. Cette étape prend environ 30 secondes une fois que vous maîtrisez la procédure.

Utilisation de la boîte de dialogue Paramètres du solveur

Veuillez ouvrir le solveur en cliquant sur l'onglet Données > bouton Solveur.

Boîte de dialogue du solveur avant la configuration. Image fournie par l'auteur.

Voici ce que vous devez remplir :

Définir l'objectif: Veuillez cliquer dans ce champ, puis cliquer sur la cellule B12 (notre cellule Total Profit).

À l': Veuillez sélectionner l'option «Max » car nous souhaitons maximiser les bénéfices.

En modifiant les cellules variables: Veuillez sélectionnerles cellules B8:B9 (les quantités que nous déterminons).

Sous réserve des contraintes: Veuillez cliquer sur «Ajouter une contrainte » ( ) pour chaque contrainte :

• Travail : $B$16 <= $C$16

• Bois : $B$17 <= $C$17

• Non-négativité : $B$8:$B$9 >= 0

Veuillez sélectionner une méthode de résolution: Veuillez sélectionner Simplex LP. Je vous expliquerai les autres méthodes ultérieurement, mais pour les problèmes linéaires comme celui-ci, la méthode Simplex LP est la plus appropriée.

Solveur configuré et prêt à résoudre. Image fournie par l'auteur.

Résolution du modèle

Avant de cliquer sur « Résoudre », veuillez vérifier rapidement vos valeurs actuelles. Tout devrait être à zéro.

État initial avec des zéros partout. Image fournie par l'auteur.

Veuillez maintenant cliquer sur «Résoudre l' ». Pour des problèmes simples comme les nôtres, cela ne prend que quelques secondes.

Succès ! Le solveur a identifié la solution optimale. Image fournie par l'auteur.

Lorsque la boîte de dialogue Résultats du solveur s'affiche, le message « Le solveur a trouvé une solution » devrait apparaître. Veuillez cocher les cases «Réponse del' » et «Sensibilité de l' » sous « Rapports ». Ces éléments vous fournissent une analyse détaillée que nous examinerons ultérieurement. Veuillez ensuite cliquer sur OK.

La solution optimale : Trente chaises et vingt tableaux. Image fournie par l'auteur.

Et voilà. La solution indique que nous devrions fabriquer 30 chaises et 20 tableaux pour réaliser un bénéfice de 3 300 $. Veuillez noter que les deux contraintes indiquent « OK », ce qui signifie que nous utilisons nos ressources de manière efficace sans dépasser les limites.

Récapitulatif du flux de travail

Voici la procédure que j'applique systématiquement :

1. Construisez le modèle (entrées, variables, objectif, contraintes).
2. Configurer le solveur (définir les paramètres à optimiser et à ajuster)
3. Veuillez cliquer sur « Résoudre » et patienter.
4. Vérifiez si la solution est pertinente sur le plan commercial.
5. Veuillez examiner les rapports pour obtenir des informations plus détaillées.

Cette dernière étape est importante. Le fait que Solver ait trouvé une réponse ne signifie pas nécessairement qu'il s'agit de la solution adéquate pour votre entreprise. Veuillez toujours vérifier la validité des résultats.

Méthodes de résolution, paramètres et options avancées

Solver vous propose trois algorithmes différents. Choisir le bon modèle fait une différence considérable en termes de rapidité et de précision.

Aperçu des méthodes de résolution

Trois méthodes de résolution pour différents types de problèmes. Image fournie par l'auteur.

Simplex LP

Simplex LP est le produit que j'utilise dans 80 % des cas. Il s'agit de problèmes linéaires où tout se résume à des additions et des multiplications. Gamme de produits ? Répartition budgétaire ? Planification des ressources ? Simplex LP. C'est rapide et cela vous garantit d'obtenir la meilleure réponse.

GRG Non linéaire

GRG Nonlinear est utilisé lorsque vos formules contiennent des courbes, des carrés ou des exponentielles. Considérez les modèles de tarification où la demande diminue à mesure que le prix augmente, mais pas de manière linéaire. Ou optimisation de portefeuille avec calculs de variance. Il identifie des optimums locaux, qui ne sont peut-être pas les meilleurs possibles, mais qui sont généralement satisfaisants.

Évolutionnaire

L'option évolutive est l'option de la force brute. Veuillez l'utiliser lorsque vos formules contiennent des contraintes entières (uniquement des unités entières), des décisions binaires (oui ou non) ou des instructions d'=IF(). Il est plus lent car il teste un grand nombre de solutions aléatoires et ne conserve que les plus efficaces. , cela fonctionne lorsque les autres méthodes échouent.

Ma règle de décision est simple : commencer par Simplex LP. Si vos formules contiennent des exposants ou des produits de variables, veuillez passer à GRG Nonlinear. Si vous avez besoin de variables entières ou binaires, optez pour Evolutionary.

Options et paramètres du solveur

Veuillez cliquer sur Options dans la boîte de dialogue Solve pour accéder aux paramètres avancés. Je m'en occupe rarement, mais voici ce qui est important :

Limitez la durée d'exécution du solveur à l'aide de l'. La valeur par défaut est de 100 secondes, ce qui est largement suffisant pour la plupart des situations. Je l'augmente pour les modèles de très grande taille qui nécessitent davantage de temps de réflexion.

Limiter le nombre de tentatives effectuées par Solver avec Iterations. Si cette limite est atteinte sans résolution, veuillez l'augmenter.

Contrôlez le niveau de rigueur du solveur en matière de contraintes à l'aide de l'option Précision. Je le serre pour les modèles financiers où chaque centime compte, mais sinon, je le laisse tel quel.

Génération de rapports et analyse de sensibilité

Vous vous souvenez lorsque je vous ai demandé de vérifier ces cases de rapport ? Voici ce que vous recevrez.

Le rapport de réponse documente la solution. Image fournie par l'auteur.

Le rapport de réponse constitue votre trace écrite. Il indique les modifications apportées : les variables de décision sont passées de 0 à 30 chaises et 20 tableaux, et le bénéfice est passé de 0 $ à 3 300 $. Il vous indique également quelles contraintes sont contraignantes (pleinement utilisées) et lesquelles ne le sont pas (ont une marge). Dans notre cas, la main-d'œuvre et le bois sont contraignants, ce qui signifie que nous utilisons toute la capacité disponible.

Dans quelle mesure les intrants peuvent-ils évoluer avant que la solution ne change ? Image fournie par l'auteur.

Le rapport de sensibilité est particulièrement intéressant. Les colonnes «Augmentation/diminution admissible » de l'outil d'évaluation des besoins en eau ( ) indiquent dans quelle mesure vos intrants peuvent varier avant qu'une solution différente ne soit nécessaire. Des fourchettes larges indiquent que votre réponse est fiable. Des fourchettes étroites signifient que de petits changements peuvent bouleverser la situation.

Les prix fictifs indiquent la valeur des ressources. Image fournie par l'auteur.

Cependant, l'information la plus précieuse se trouve dans le prix de l'ombre. Cela vous indique la valeur d'une unité supplémentaire d'une ressource. Si le coût implicite de la main-d'œuvre est de 7,5 $, chaque heure de travail supplémentaire ajoute 7,5 $ au bénéfice. Par conséquent, si vous avez la possibilité d'embaucher du personnel temporaire à 5 $ de l'heure, n'hésitez pas. À 10 $ de l'heure, je préfère passer mon tour.

Importance de l'analyse de sensibilité

J'utilise ces rapports pour répondre à trois questions. Dans quelles ressources devrais-je investir ? Ma solution est-elle vulnérable ? Que se passe-t-il si mes prévisions sont erronées ? Les rapports fournissent des réponses claires à ces trois questions.

Tout d'abord, j'examine les prix fictifs afin de déterminer dans quelles ressources je devrais investir.. Un prix d'ombre élevé indique une valeur élevée.

Ensuite, je vérifie les plages autorisées pour déterminer si ma solution est fragile. Si le bénéfice ne peut varier que de 1 $ avant que la solution ne change, vous êtes en situation précaire. Si cela peut varier de 50 dollars, cela vous convient.

Enfin, je me demande : que se passerait-il si mes prévisions s'avéraient erronées ? Les plages autorisées vous indiquent l'écart maximal autorisé avant de devoir procéder à un nouveau calcul.

Mon conseil : veuillez toujours vérifier les prix indicatifs avant d'approuver un budget. Si une ressource affiche un prix fictif de zéro, cela indique que vous disposez d'une capacité excédentaire. Investir davantage dans ce domaine serait un gaspillage. Investissez vos fonds là où les prix de l'ombre sont les plus élevés.

Dépannage et résolution des erreurs

Les messages d'erreur du solveur peuvent sembler inquiétants, mais ils sont généralement faciles à résoudre. Voici ceux que je rencontre le plus fréquemment.

Erreurs et messages courants

Le solveur n'a pas pu trouver de solution viable.

Cela signifie que vos contraintes sont en conflit les unes avec les autres.

 Aucune solution ne répond à toutes les contraintes. Image fournie par l'auteur.

Cela se produit généralement lorsque vous demandez quelque chose d'irréalisable, comme essayer d'atteindre un objectif de profit que les ressources disponibles ne permettent tout simplement pas d'atteindre. La solution consiste à supprimer les contraintes une par une jusqu'à ce que Solver fonctionne ; ce processus permet d'identifier précisément quelle contrainte est à l'origine du conflit.

Les valeurs des cellules définies ne convergent pas.

Cela signifie que votre objectif peut évoluer indéfiniment. Il vous manque une limite supérieure quelque part. Les entreprises réelles ont toujours des limites, veuillez les ajouter.

Le solveur n'est pas en mesure d'améliorer la solution actuelle.

Cela se produit généralement avec des problèmes non linéaires. Veuillez essayer différentes valeurs de départ ou optez pour la méthode évolutive. Parfois, un optimum local est suffisant.

Erreurs de formulation du modèle

Références circulaires

Cela se produit lorsque la cellule A dépend de la cellule B, qui dépend elle-même de la cellule A. Excel vous avertira de cette situation. Veuillez utiliser l'onglet Formules > Suivre les précédents pour identifier la boucle et la supprimer.

Contraintes sur les entiers

Ajout de contraintes entières pour les nombres entiers. Image fournie par l'auteur.

Limites des solveurs et solutions alternatives pour les problèmes à grande échelle

Excel Solver est un outil remarquable, mais il présente certaines limites qu'il est important de connaître.

Comprendre les limites du solveur

Le solveur standard peut traiter jusqu'à 200 variables de décision et 100 contraintes. Cela peut sembler considérable jusqu'à ce que vous essayiez de planifier les horaires de 200 employés sur 50 créneaux horaires. Cela représente 10 000 variables, ce qui dépasse largement les capacités de Solver.

J'utilise Solver pour les plans de production mensuels, les budgets trimestriels et la sélection des projets. C'est idéal pour les décisions tactiques. Cependant, il n'est pas conçu pour les problèmes à l'échelle de l'entreprise, tels que les réseaux de chaînes d'approvisionnement comptant des milliers de sites.

Solutions pour les problèmes complexes

OpenSolver

OpenSolver est gratuit et élimine les contraintes liées à l'. Il gère autant de variables que la mémoire de votre ordinateur le permet, utilise la même interface qu'Excel Solver et peut être téléchargé depuis OpenSolver.org. Je le recommande lorsque vous dépassez les capacités du Solveur standard.

Frontline Solver Premium

Frontline Solver est payant (entre 300 et 1 500 dollars par an), mais prend en charge des milliers de variables grâce à des algorithmes plus performants. C'est ce que j'utiliserais si j'exerçais cette activité à titre professionnel à temps plein.

Logiciel autonome

Des outils tels que CPLEX ou Gurobi permettent de gérer des millions de variables, mais nécessitent des connaissances en programmation et un investissement important. Il s'agit de questions relatives au niveau de l'entreprise.

À mon avis, pour la plupart des analystes commerciaux, Excel Solver couvre 95 % des besoins. Si vous atteignez ses limites, c'est en réalité un signe positif, cela signifie que vous effectuez un travail suffisamment sophistiqué pour justifier l'utilisation d'outils plus performants.

Conclusion

Nous avons abordé de nombreux sujets, de la configuration de base à l'interprétation des prix fictifs. 

Désormais, au lieu de débattre des opinions dans une salle de réunion, vous pouvez afficher les chiffres à l'écran. Voici le plan mathématiquement optimal. Cela modifie la conversation. Vous cessez de deviner et commencez à prouver.

N'hésitez pas à l'essayer lors de votre prochain problème complexe, ce conflit d'horaires ou cette contrainte budgétaire que vous avez évité jusqu'à présent. Il se peut que la réponse parfaite se trouvait dans votre feuille de calcul depuis le début.

Et si vous souhaitez acquérir davantage de techniques pour impressionner votre supérieur, suivez notre formation  cours sur l'analyse de données dans Excel pour continuer à vous perfectionner.