L’add-in Excel Solver effectue une optimisation mathématique. Il est généralement utilisé pour ajuster des modèles complexes aux données ou pour trouver des solutions itératives à des problèmes. Par exemple, vous pouvez vouloir faire passer une courbe par certains points de données, en utilisant une équation. Le solveur peut trouver les constantes dans l’équation qui donnent le meilleur ajustement aux données. Une autre application est lorsqu’il est difficile de réorganiser un modèle pour que la sortie requise fasse l’objet d’une équation.
Contenu
Où se trouve Solver dans Excel ?
Le module complémentaire Solver est inclus dans Excel mais n’est pas toujours chargé dans le cadre d’une installation par défaut. Pour vérifier s’il est chargé, sélectionnez l’option DONNÉES et recherchez l’onglet Solveur dans l’icône Analyse section.
Si vous ne trouvez pas Solver sous l’onglet DATA, vous devrez alors charger le complément :
- Sélectionnez le DOSSIER et sélectionnez ensuite l’onglet Options.
- Dans le Options boîte de dialogue sélectionner Add-Ins à partir des onglets sur la gauche.
- En bas de la fenêtre, sélectionnez Compléments Excel de la Gérer et sélectionnez Allez…
- Cochez la case située à côté de Solveur et sélectionnez OK.
- Le Solveur devrait maintenant apparaître sur le DONNÉES onglet. Vous êtes prêt à utiliser Solver.
Utilisation du solveur dans Excel
Commençons par un exemple simple pour comprendre ce que fait le Solveur. Imaginez que nous voulions savoir quel rayon donnera un cercle d’une surface de 50 unités carrées. Nous connaissons l’équation pour l’aire d’un cercle (A=pi r2). Nous pourrions, bien sûr, réarranger cette équation pour donner le rayon requis pour une zone donnée, mais pour l’exemple, prétendons que nous ne savons pas comment faire.
Créez une feuille de calcul avec le rayon en B1 et calculer la superficie en B2 en utilisant l’équation =pi()*B1^2.
Nous pourrions ajuster manuellement la valeur en B1 jusqu’à B2 montre une valeur assez proche de 50. Selon la précision dont nous avons besoin, il peut s’agir d’une approche pratique. Toutefois, si nous devons être très précis, il faudra beaucoup de temps pour effectuer les ajustements nécessaires. En fait, c’est essentiellement ce que fait Solver. Il ajuste les valeurs dans certaines cellules et vérifie la valeur dans une cellule cible :
- Sélectionnez DONNÉES onglet et Solveurpour charger le Paramètres du solveur boîte de dialogue
-
Fixer l’objectif pour être la Zone, B2. C’est cette valeur qui sera vérifiée, en ajustant d’autres cellules jusqu’à ce que celle-ci atteigne la valeur correcte.
-
- Sélectionnez le bouton pour Valeur de : et fixer une valeur de 50. C’est la valeur que B2 devrait atteindre.
- Dans l’encadré intitulé En changeant les cellules variables : entrez dans la cellule contenant le rayon, B1.
- Laissez les autres options telles qu’elles sont par défaut et sélectionnez Résoudre. L’optimisation est effectuée, la valeur de B1 est ajustée jusqu’à ce que B2 soit à 50 et la Résultats du solveur est affiché.
- Sélectionnez OK pour conserver la solution.
Cet exemple simple a montré comment fonctionne le solveur. Dans ce cas, nous aurions pu plus facilement obtenir la solution par d’autres moyens. Ensuite, nous examinerons quelques exemples où le solveur donne des solutions qu’il serait difficile de trouver autrement.
Adaptation d’un modèle complexe à l’aide du module complémentaire du solveur Excel
Excel a une fonction intégrée pour effectuer une régression linéaire, en traçant une ligne droite à travers un ensemble de données. De nombreuses fonctions non linéaires courantes peuvent être linéarisées, ce qui signifie que la régression linéaire peut être utilisée pour ajuster des fonctions telles que les exponentielles. Pour les fonctions plus complexes, le Solveur peut être utilisé pour effectuer une « minimisation des moindres carrés ». Dans cet exemple, nous allons envisager d’ajuster une équation de la forme ax^b+cx^d aux données indiquées ci-dessous.
Cela implique les étapes suivantes :
- Arrangez l’ensemble de données avec les valeurs x dans la colonne A et les valeurs y dans la colonne B.
- Créez les 4 valeurs de coefficient (a, b, c et d) quelque part sur la feuille de calcul, on peut leur donner des valeurs de départ arbitraires.
- Créez une colonne de valeurs Y ajustées, en utilisant une équation de la forme ax^b+cx^d qui fait référence aux coefficients créés à l’étape 2 et aux valeurs x de la colonne A. Notez que pour copier la formule dans la colonne, les références aux coefficients doivent être absolues tandis que les références aux valeurs x doivent être relatives.
- Bien que ce ne soit pas essentiel, vous pouvez obtenir une indication visuelle de la qualité de l’ajustement de l’équation en traçant les deux colonnes y par rapport aux valeurs x sur un seul diagramme de dispersion XY. Il est judicieux d’utiliser des marqueurs pour les points de données originaux, puisqu’il s’agit de valeurs discrètes avec bruit, et d’utiliser une ligne pour l’équation ajustée.
- Ensuite, nous devons trouver un moyen de quantifier la différence entre les données et notre équation ajustée. La façon standard de le faire est de calculer la somme des carrés des différences. Dans une troisième colonne, pour chaque ligne, la valeur originale des données pour Y est soustraite de la valeur de l’équation ajustée, et le résultat est mis au carré. Ainsi, dans D2la valeur est donnée par =(C2-B2)^2. La somme de tous ces carrés est alors calculée. Comme les valeurs sont au carré, elles ne peuvent être que positives.
- Vous êtes maintenant prêt à effectuer l’optimisation à l’aide de Solver. Quatre coefficients doivent être ajustés (a, b, c et d). Vous avez également une seule valeur objective à minimiser, la somme des carrés des différences. Lancez le solveur, comme ci-dessus, et réglez les paramètres du solveur pour faire référence à ces valeurs, comme indiqué ci-dessous.
- Décochez l’option pour Rendre les variables non contraignantes non négativesSi l’on ne tient pas compte de l’évolution de la situation, cela obligerait tous les coefficients à prendre des valeurs positives.
- Sélectionnez « Résoudre » et examinez les résultats. Le tableau s’actualisera et donnera une bonne indication de la qualité de l’ajustement. Si le solveur ne produit pas un bon ajustement à la première tentative, vous pouvez essayer de le relancer. Si l’ajustement s’est amélioré, essayez de le résoudre à partir des valeurs actuelles. Sinon, vous pouvez essayer d’améliorer manuellement l’ajustement avant de le résoudre.
- Une fois qu’un bon ajustement a été obtenu, vous pouvez quitter le solveur.
Résoudre un modèle de façon itérative
Il existe parfois une équation relativement simple qui donne un résultat en termes d’un certain intrant. Cependant, lorsque nous essayons d’inverser le problème, il n’est pas possible de trouver une solution simple. Par exemple, la puissance consommée par un véhicule est approximativement donnée par P = av + bv^3 où v est la vitesse, a est un coefficient pour la résistance au roulement et b est un coefficient pour la traînée aérodynamique. Bien que cette équation soit assez simple, il n’est pas facile de la réarranger pour obtenir une équation de la vitesse que le véhicule atteindra pour une puissance absorbée donnée. Nous pouvons cependant utiliser Solver pour trouver cette vitesse de façon itérative. Par exemple, trouver la vitesse atteinte avec une puissance absorbée de 740 W.
- Créez une simple feuille de calcul avec la vitesse, les coefficients a et b, et la puissance calculée à partir de ceux-ci.
- Lancez le Solveur et entrez dans le pouvoir, B5comme objectif. Fixer une valeur objective de 740 et sélectionnez la vitesse, B2comme les cellules variables à changer. Sélectionnez résoudre pour amorcer la solution.
- Le solveur ajuste la valeur de la vitesse jusqu’à ce que la puissance soit très proche de 740, fournissant ainsi la vitesse dont nous avons besoin.
- La résolution de modèles de cette manière peut souvent être plus rapide et moins sujette aux erreurs que l’inversion de modèles complexes.
Il peut être assez difficile de comprendre les différentes options disponibles dans le solveur. Si vous avez des difficultés à obtenir une solution raisonnable, il est souvent utile d’appliquer des conditions limites aux cellules modifiables. Il s’agit de valeurs limites au-delà desquelles elles ne doivent pas être ajustées. Par exemple, dans l’exemple précédent, la vitesse ne devrait pas être inférieure à zéro et il serait également possible de fixer une limite supérieure. Il s’agirait d’une vitesse que le véhicule ne peut pas dépasser. Si vous pouvez définir des limites pour les cellules variables modifiables, cela permet également d’améliorer le fonctionnement d’autres options plus avancées, telles que le multi-démarrage. Cela permet d’appliquer plusieurs solutions différentes, en partant de valeurs initiales différentes pour les variables.
Le choix de la méthode de résolution peut également être difficile. Le Simplex LP ne convient qu’aux modèles linéaires, si le problème n’est pas linéaire, il échouera avec un message indiquant que cette condition n’a pas été remplie. Les deux autres méthodes sont toutes deux adaptées aux méthodes non linéaires. La méthode GRG non linéaire est la plus rapide, mais sa solution peut être très dépendante des conditions initiales de départ. Elle a la souplesse de ne pas nécessiter de limites pour les variables. Le solveur évolutionnaire est souvent le plus fiable, mais il exige que toutes les variables aient des limites supérieures et inférieures, ce qui peut être difficile à déterminer à l’avance.
L’add-in Excel Solver est un outil très puissant qui peut être appliqué à de nombreux problèmes pratiques. Pour accéder pleinement à la puissance d’Excel, essayez de combiner Solver avec des macros Excel.
