Le solveur

Le solveur: un outil puissant pour faire résoudre à excel par tatonnement des problèmes complexes d'optimisation de solution.

Vous pouvez télécharger un classeur exemple associé à ce tutoriel.

    Introduction

    Si dans la cellule A1 nous écrivons la valeur 2 et dans la cellule B1 nous écrivons = 2*A1, nous obtenons 4. Connaissant la formule en B2 et la valeur du résultat (4) il est aisé de trouver la valeur écrite en A1. Cela peut devenir un peu plus compliqué si la fonction liant A1 et B1 n'est pas linéaire, ou si B1 est lié à plus d'une cellule qui varient indépendamment. Dans ces situations, le solveur est un outil d'aide à la décision précieux, même si hélas, il n'est pas capable de trouver des solutions... inexistantes !

    Nous allons voir quelques exemples de situations où le solveur peut être utile, comment organiser les données pour les entrer dans le solveur et les limites de cet outil.

    Exemples d'utilisation du solveur.

    Le solveur peut vous aider à répondre à différents types de questions mais qui ont toutes en commun que la cellule contenant le résultat (la cellule cible) doit contenir une valeur numérique (pas du texte donc) issue d'une formule contenant des références vers d'autres cellules (les cellules variables). En procédant par itérations successives, le solveur fait varier les valeurs cellules variables jusqu'à trouver une solution qui réponde aux critères imposés. On peut ainsi rechercher les valeurs des cellules variables qui vont produire la plus forte valeur possible de la cellule cible (max), ou au contraire la plus faible (min) ou encore qui font tendre la cellule cible vers une valeur imposée. Le classeur exemple traite de quelques situations type :
    • Comment trouver le minimum d'une fonction mathématique sur un intervalle donné ?
    • Quel est le nombre minimum d'infirmières qui doivent être de garde ce week-end ?
    • Connaissant ma capacité maximale de remboursement mensuel, quelles conditions de prêts (taux, durée) sont compatibles avec celle-ci ?
    • Quel est l'impact de la hausse du cot des carburants sur le choix du lieu d'implantation d'une nouvelle usine de production ?
    • comment trouver le nombre (entier) d'actions à émettre pour réaliser une augmentation de capital ? Exemple d'utilisation pour trouver une valeur approchée.

    Le solveur ne peut pas renvoyer une valeur de texte (oui/non par exemple). Mais on peut lui demander de renvoyer une valeur entière et imposer comme bornes 0 et 1. Ceci limite à 2 le nombre de réponses possibles du solvuer et permet de contourner cette limitation.

    Attention, on ne peut pas directement demander à ce que la cible soit le plus proche possible d'une valeur indiquée. Pour obtenir ce résultat, il faut jouer soit sur les options du solveur (flèche rouge) soit choisir de faire tendre la valeur cible vers la plus grande ou la plus petite valeur possible et restreindre ces possibilités en imposant des contraintes additionnelles sur la cellule cible.

    Où trouver le solveur ?

    Dans excel 2007, l'accès au solveur se fait par l'onglet Données



    Si vous ne voyez pas cet outil, c'est que la macro complémentaire solveur n'a pas été activée. Pour cela, dans les options d'excel, compléments, cherchez le dans la liste des compléments inactifs, dans le bas de la fenêtre, choisissez atteindre. Dans la fenêtre qui s'ouvre, cochez le solveur.




    Dans excel 2003 et versions antérieures, le solveur est disponible par le menu outils





    Si vous le voyez pas c'est qu'il n'est pas installé. Dans ce cas, dans le menu Outils, cliquez sur Macros complémentaires. Dans la zone Macros complémentaires disponibles, activez la case à cocher en regard de Complément Solveur, puis cliquez sur OK.

    Si le Complément Solveur n'est pas répertorié, cliquez sur Parcourir pour le localiser.

    Si un message indiquant que le complément Solveur n'est pas installé sur votre ordinateur s'affiche, cliquez sur Oui pour l'installer. Dans la barre de menus, cliquez sur Outils. Lorsque vous chargez le complément Solveur, la commande Solveur est ajoutée au menu Outils.

    Le solveur n'existe pas dans la version 2008 pour Macintosh :-(. Espérons que les prochaines versions d'excel pour Mac verront revenir le VBA et les outils associés.

    Quelques définitions

    • Le solveur : c'est une macro complémentaire d'excel, livrée avec le logiciel mais pas nécessairement installée (voir ci-dessus).

    • Cellules cibleC'est elle qui contient la valeur que l'on veut obtenir. Elle DOIT contenir une formule dépendante des
    • Cellules variables qui comme leur nom l'indiquent vont être modifiées progressivement par le solveur pour tenter de trouver une solutionsatisfaisant aux
    • Contraintes que vous allez imposer au solveur. Ces contraintes ne peuvent s'appliquer que les cellules variables ou la cellule cible. Mais elles peuvent faire référence à d'autres cellules. Par exemple on peut imposer qu'une cellule variable prenne des valeurs comprises entre deux bornes indiquées dans d'autres cellules de la feuille de calcul.

    Par convention dans le classeur exemple, les cellules cibles sont en jaune, les cellules variables sont en bleu et les cellules utilisées comme contraintes sont en vert.

    Les paramètres

    La cellule cible

    Vous avez trois choix possibles pour la cellule cible : maximum, minimum et valeur.

    Si vous cochez la case maximum, vous demandez à excel de trouver la combinaison de cellules variables qui renvoie la valeur la plus élevée possible de la cellule cible. Cette option est donc celle retenue lorsque vous cherchez à optimiser des conditions pour obtenir par exemple le meilleur revenu.

    De façon symétrique, la case min permet de trouver la combinaison de valeurs qui génère la plus petite valeur possible pour la cellule cible

    Et enfin, vous l'auriez deviné, en cochant valeur et en tapant la valeur cible à atteindre, excel va essayer de trouver une solution compatible. Cette option est par exemple celle que vous retiendrez pour résoudre une équation.

    Les cellules variables

    Vous pouvez indiquer un grand nombre de cellules variables, jusqu'à 200 théoriquement. La limite ici est en fait souvent la capacité de calcul (mémoire) d'excel. Celle-ci dépend avant tout de la configuration de votre ordinateur.

    Pour indiquer quelles sont les cellules variables, vous pouvez les sélectionner à la souris ou les taper. Séparez les différentes cellules ou plages de cellules par des points virgules.

    Attention, si vos cellules variables contiennent des formules les reliant à d'autres cellules, ces formules seront effacées lorsque vous lancerez le solveur. IL n'y aura pas de message d'erreur, et cette situation peut être utile pour calculer une valeur de départ, mais si vous souhaitez revenir à la situation initiale vous devrez retaper vos formules. Pour éviter cela, sélectionnez plutôt comme cellules variables celles qui ne contiennent que des valeurs numériques (les cellules parentales).

    Les contraintes

    Le nombre maximum de contraintes est de 500. Il est illimité si vous travaillez avec un modèle linéaire (voir le paragraphe options). Là encore, la vraie limite est souvent celle de la puissance de calcul de votre machine. La nature des contrainte influence grandement le temps de résolution du solveur : si vous demandez par exemple A1>0 ou A1=4, ces contraintes sont peu gourmandes en ressources. Demandez A1=2+2 est plus gourmand, excel devant évaluer d'abord le côté droit de l'égalité. Vous ne pouvez pas utiliser plus de 100 contraintes contenant autre chose que des constantes.

    Vous pouvez pour le contraintes choisir dans la liste ent qui impose que la cellule référencée contienne un nombre entier. Inutile donc de créer une formule pour cela !

    Les options

    Les paramètres par défaut du solveur, accessibles par la boîte de dialogue principale du solveur, sont en général suffisants pour résoudre des problèmes simples. Par défaut, il est programmé pour s'interrompre si une solution n'est pas trouvée dans les 100 secondes ou au bout de 100 itérations





    Vous pouvez ajuster ces deux paramètres (valeurs maxi : 32767).

    Précision et tolérance

    La précision est la différence autorisée entre la valeur imposée comme contrainte et la valeur renvoyée. Autrement dit, si vous inscrivez comme contrainte A1=0, les valeurs de A1 comprises entre 0-précision et 0+précision seront acceptées. Plus il y a de décimales, plus la contrainte est forte. Cependant au delà de 1E-8, vous ne verrez pas la différence entre les résultats renvoyés par excel. Si vous cherchez à optimiser des valeurs pour des mesures en micromètres ou en milliards d'euros, utilisez l'option échelle automatique.

    La tolérance est une notion proche de la précision. Cette option n'est utilisable que si une de voscontraintes est d'avoir un nombre entier. Elle exprime en pourcentage la précision sur cette contrainte. Le pourcentage par défaut, 5% est élevé, surtout par comparaison avec la valeur de la précision. Cette valeur est souvent suffisante ou en tous cas utile pour trouver une première solution approchée qu'on pourra éventuellement réutiliser par la suite en modifiant la précision ou la tolérance demandée au solveur.

    Echelle automatique

    Dans certains cas, les échelles de valeurs entre la valeur cible et les cellules variables sont très différentes. C'est la cas notamment si vous avez des valeurs en pourcentages dans certaines cellules, des euros par exemple dans d'autres cellules et que l'ordre de grandeur attendu pour la valeur cible est en millions d'euros. Dans ce genre de situation, du fait des arrondis à gérer, il se peut qu'excel ne trouve pas de solution. Si vous cochez l'option échelle automatique, excel tente d'harmoniser de façon interne les ordres de grandeur avant d'effectuer les itérations. Ceci permet généralement de trouver une solution. Mais le solveur travaille de façon plus efficace si vous-même, vous exprimez vos valeurs de départ de telle sorte qu'elles ne diffèrent pas entre elles de plus d'un facteur 1000 à 10000.

    Modèle supposé linéaire

    Vous pouvez accéler la recherche de solutions en modifiant certains paramètres. En particulier, si vous savez que vos données sont liées par une relation linéaire entre les cellules variables et la cellule cible, cochez la case modèle supposé linéaire. Si le modèle n'est pas linéaire, excel vous renverra une valeur d'erreur. On trouve dans un problème linéaire des opérations arithmétiques simples comme : l'addition , la soustraction , les fonctions intégrées SOMME() , TENDANCE() ou PREVISION(). Si vous avez une relation du type cible= variable1*variable2 ou toute autre fonction qui sur un graphe se représente par autre chose qu'une droite alors vous ne pouvez pas utiliser le modèle linéaire.

    Supposé non-négatif

    Si vous savez que la valeur cible doit renvoyer une valeur positive, alors cochez cette case.

    Afficher le résultat des itérations

    Interrompt le solveur et affiche les résultats produits par chaque itération . Cette option est utile lorsque vous voyez une solution évidente que le solveur ne trouve pas. Cela vous permet de voir d'où part le solveur et dans quelle sens il fait ses itérations. Voir conseil et dépannage

    Enregistrer et charger un modèle

    Vous permet d'enregistrer les paramètres du solveur : emplacement des cellules cibles, variables, contraintes de façon à récupérer ces données si vous utilisez par exemple plusieurs fois le solveur sur une même feuille. Les autres paramètres optionnels ne sont pas abordés ici. Je vous invite si vous le souhaitez à lire (en anglais) les paragraphes correspondant sur le site solver.com Ce site est extrêmement complet sur le sujet et sur tout ce qui touche au solveur, ce qui est normal vu que ce sont eux qui ont conçu cet outil !

    Expression des résultats

    Le solveur peut renvoyer 13 types de messages de résultat, annonçant qu'excel a trouvé une solution ou qu'il a échoué. Le solveur démarre les itérations avec les valeurs que vous lui fournissez et s'arrête lorsqu'il trouve une solution. Ces conditions de départ influent grandement sur les solutions proposées. Le solveur n'est qu'une aide à la résolution de problèmes, il vous appartient de poser les bonnes contraintes et valeurs de départ et d'analyser les résultats. Lorsqu'il y a de nombreuses solutions possibles, il est en général nécessaire de faire tourner plusieurs fois le solveur en faisant varier les paramètres pour trouver la solution qui vous convient le mieux.

    Solution trouvée

    Attention, dans de nombreux cas, le problème posé au solveur admet plusieurs solutions. Le solveur vous propose la première de celles qui répond aux critères imposés.

    Le solveur génère plusieurs types de solutions:

    • Solution compatible : Toutes les contraintes du solveur sont satisfaites. Le solveur cherche la première solution compatible puis tente alors de l'optimiser, pour maximiser la valeur cible si l'option max est cochée par exemple.
    • Une solution optimale est une solution compatble optimisée.
    • Une solution est localement optimale si aucune autre solution dans le voisinage ne répond mieux aux contraintes du solveur. Si vous prenez l'exemple d'une fonction sinusoïdale, une solution localement optimale résoud l'équation aux abords d'un pic ou d'une vallée de la fonction.
    • Une solution globalement optimale, celle que l'on souhaite idéalement trouver, résoud l'équation. Le solveur cherche en priorité cette solution. Mais il se peut qu'elle n'existe pas, ou que la résolution de l'équation dépasse les capacités de calcul de votre PC. Dans ce cas, il peut tout de même être intéressant de connaître ces solutions localement compatibles ou une solution qui se rapproche de vos contraintes.

    La capacité du solveur à résoudre l'équation dépend essentiellement de la complexité de la relation entre la cible, les variables et les contraintes.

    Deux messages particuliers :

    • Le solveur a convergé vers une solution. Cela signifie que les itérations génèrent des solutions qui sont très peu différentes l'une de l'autre. La solution proposée est proche de celle recherchée mais ne répond pas complètement aux critères. Ce message n'apparait que si vous travaillez avec un modèle non linéaire.
    • Les valeurs de la cellule cible ne convergent pas. Si votre fonction est de type ax+b et que vous n'imposez pas de limites à vos variables, il n'y a pas non plus de limite et donc de solution si vous cochez valeur max ou valeur min pour la cellule cible.

    Les autres messages renvoyés sont plus explicites.

    Que faire si le solveur ne trouve pas de solution ?

    • La première chose c'est de vérifier que les contraintes que vous avez inmposées ne sont pas redondantes ou incompatibles entre elles. Vérifiez aussi qu'elles ne génèrent pas de valeur d'erreur dans votre formule pour une valeur particulière. Ceci est particulièrement important si vous utilisez notamment les logarithmes ou autres fonctions de ce genre.
    • Vérifiez si vous avez le droit ou pas de cocher la case modèle supposé linéaire. Il impose un changement dans la méthode de résolution du solveur et peut conduire à des résultats erronés s'il n'est pas utilisé à bon escient.
    • Changez les valeurs de départ. Il se peut qu'il existe une solution mais avec des valeurs de départ très éloignées de celles que vous avez indiquées
    • Diminuez les contraintes, notamment les bornes d'étude de vos fonctions
    • Vérifiez que votre modèle est continu : le solveur ne peut pas travailler avec une fonction discontinue sur certains intervalles de valeurs.

    Plus d'infos sur les messages renvoyés la page (en anglais) du solver.com


Ce mois-ci sur Excelabo

- Pas de nouvelle page.
- 8 pages modifiées.