Qu'est-ce qu'Excel Solver ?

2024 Auteur: Abigail Brown | [email protected]. Dernière modifié: 2024-01-07 19:03

Le complément Excel Solver effectue une optimisation mathématique. Ceci est généralement utilisé pour ajuster des modèles complexes à des données ou pour trouver des solutions itératives à des problèmes. Par exemple, vous souhaiterez peut-être ajuster une courbe à certains points de données à l'aide d'une équation. Le solveur peut trouver les constantes de l'équation qui correspondent le mieux aux données. Une autre application est celle où il est difficile de réorganiser un modèle pour faire de la sortie requise le sujet d'une équation.

Où est le solveur dans Excel ?

Le complément Solveur est inclus avec Excel, mais il n'est pas toujours chargé dans le cadre d'une installation par défaut. Pour vérifier s'il est chargé, sélectionnez l'onglet DATA et recherchez l'icône Solver dans la section Analysis.

Si vous ne trouvez pas Solveur sous l'onglet DONNÉES, vous devrez charger le complément:

1. Sélectionnez l'onglet FILE puis sélectionnez Options.

   

2. Dans la boîte de dialogue Options, sélectionnez Add-Ins dans les onglets sur le côté gauche.

   

3. En bas de la fenêtre, sélectionnez Excel Add-ins dans le menu déroulant Manage et sélectionnez Go…

   

4. Cochez la case à côté de Solver Add-in et sélectionnez OK.

   

5. La commande Solver devrait maintenant apparaître sur l'onglet DATA. 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 voulons savoir quel rayon donnera un cercle d'une aire de 50 unités carrées. Nous connaissons l'équation de l'aire d'un cercle (A=pi r²). Nous pourrions, bien sûr, réorganiser cette équation pour donner le rayon requis pour une zone donnée, mais à titre d'exemple, supposons que nous ne savons pas comment faire cela.

Créez une feuille de calcul avec le rayon en B1 et calculez la surface en B2 en utilisant l'équation =pi()B1^2.

Nous pourrions ajuster manuellement la valeur dans B1 jusqu'à ce que B2 affiche une valeur suffisamment proche de 50. Selon la précision avec laquelle nous besoin d'être, cela pourrait être une approche pratique. Cependant, s'il faut être très précis, il faudra beaucoup de temps pour faire les ajustements nécessaires. En fait, c'est essentiellement ce que fait Solver. Il effectue des ajustements sur les valeurs de certaines cellules et vérifie la valeur dans une cellule cible:

1. Sélectionnez l'onglet DATA et Solver, pour charger la boîte de dialogue Solver Parameters

2. Définir l'Objectif cellule comme zone, B2. C'est la valeur qui sera vérifiée, en ajustant les autres cellules jusqu'à ce que celle-ci atteigne la valeur correcte.

   

3. Sélectionnez le bouton pour Valeur de: et définissez une valeur de 50. C'est la valeur que B2 doit atteindre.

   

4. Dans la case intitulée En changeant les cellules variables: entrez la cellule contenant le rayon, B1.

   

5. 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 boîte de dialogue Solver Results s'affiche.

   

6. Sélectionnez OK pour conserver la solution.

   

Cet exemple simple montre comment fonctionne le solveur. Dans ce cas, nous aurions pu obtenir plus facilement la solution par d'autres moyens. Ensuite, nous examinerons quelques exemples où Solver donne des solutions qu'il serait difficile de trouver autrement.

Ajustement d'un modèle complexe à l'aide du complément Excel Solver

Excel a une fonction intégrée pour effectuer une régression linéaire, en ajustant 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 des fonctions plus complexes, le solveur peut être utilisé pour effectuer une «minimisation des moindres carrés». Dans cet exemple, nous envisagerons d'ajuster une équation de la forme ax^b+cx^d aux données présentées ci-dessous.

Cela implique les étapes suivantes:

1. Organisez le jeu de données avec les valeurs x dans la colonne A et les valeurs y dans la colonne B.
2. 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.

3. 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 dans la colonne A. Notez que pour copier la formule vers le bas la colonne, les références aux coefficients doivent être absolues tandis que les références aux valeurs x doivent être relatives.

   

4. Bien que cela 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 nuage de points XY. Il est logique d'utiliser des marqueurs pour les points de données d'origine, car ce sont des valeurs discrètes avec du bruit, et d'utiliser une ligne pour l'équation ajustée.

   

5. Ensuite, nous avons besoin d'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 différences au carré. Dans une troisième colonne, pour chaque ligne, la valeur de données d'origine pour Y est soustraite de la valeur de l'équation ajustée et le résultat est élevé au carré. Ainsi, dans D2, la valeur est donnée par =(C2-B2)^2 La somme de toutes ces valeurs au carré est ensuite calculée. Comme les valeurs sont au carré, elles ne peuvent être que positives.

   

6. Vous êtes maintenant prêt à effectuer l'optimisation à l'aide du Solveur. Il y a quatre coefficients à ajuster (a, b, c et d). Vous avez également une seule valeur d'objectif à minimiser, la somme des différences au carré. Lancez le solveur, comme ci-dessus, et définissez les paramètres du solveur pour référencer ces valeurs, comme indiqué ci-dessous.

   

7. Décochez l'option Make Unconstrained Variables Non-Negative, cela forcerait tous les coefficients à prendre des valeurs positives.

   

8. Sélectionnez Résoudre et examinez les résultats. Le graphique sera mis à jour en donnant une bonne indication de la qualité de l'ajustement. Si le solveur ne produit pas un bon ajustement lors de la première tentative, vous pouvez essayer de l'exécuter à nouveau. Si l'ajustement s'est amélioré, essayez de résoudre à partir des valeurs actuelles. Sinon, vous pouvez essayer d'améliorer manuellement l'ajustement avant de résoudre.

   

9. Une fois qu'un bon ajustement a été obtenu, vous pouvez quitter le solveur.

Résoudre un modèle de manière itérative

Parfois, il y a une équation relativement simple qui donne une sortie en termes d'entrée. 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 de résistance au roulement et b est un coefficient de traînée aérodynamique. Bien qu'il s'agisse d'une équation assez simple, il n'est pas facile de la réorganiser pour donner une équation de la vitesse que le véhicule atteindra pour une puissance absorbée donnée. Nous pouvons cependant utiliser Solver pour trouver itérativement cette vitesse. Par exemple, trouvez la vitesse atteinte avec une puissance absorbée de 740 W.

1. Configurez une feuille de calcul simple avec la vitesse, les coefficients a et b, et la puissance calculée à partir de ceux-ci.

   

2. Lancez le solveur et entrez la puissance, B5, comme objectif. Définissez une valeur objectif de 740 et sélectionnez la vélocité, B2, comme cellules variables à modifier. Sélectionnez solve pour lancer la solution.

   

3. Le solveur ajuste la valeur de la vitesse jusqu'à ce que la puissance soit très proche de 740, fournissant la vitesse dont nous avons besoin.

   

4. 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.

Comprendre les différentes options disponibles dans le solveur peut être assez difficile. Si vous rencontrez des difficultés pour obtenir une solution sensée, il est souvent utile d'appliquer des conditions aux limites aux cellules variables. Ce sont des valeurs limites au-delà desquelles il ne faut pas les régler. Par exemple, dans l'exemple précédent, la vitesse ne doit pas être inférieure à zéro et il serait également possible de fixer une borne supérieure. Ce serait une vitesse à laquelle vous êtes presque sûr que le véhicule ne peut pas aller plus vite. Si vous êtes en mesure de définir des limites pour les cellules variables modifiables, cela améliore également le fonctionnement d'autres options plus avancées, telles que le multistart. Cela exécutera un certain nombre de solutions différentes, en commençant par différentes valeurs initiales pour les variables.

Choisir la méthode de résolution peut également être difficile. 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. GRG Non linéaire est le plus rapide mais sa solution peut dépendre fortement des conditions de démarrage initiales. Il a la flexibilité de ne pas nécessiter de variables pour avoir des limites définies. Le solveur évolutionnaire est souvent le plus fiable, mais il nécessite que toutes les variables aient des bornes supérieure et inférieure, ce qui peut être difficile à déterminer à l'avance.

Le complément 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.