Qu’est-ce que le Solveur Excel ?

Qu’est-ce que le Solveur Excel ?

Le complément Excel Solver effectue des optimisations mathématiques. Ceci est souvent utilisé pour ajuster des modèles complexes à des données ou pour trouver des solutions itératives à des problèmes. Par exemple, vous pouvez utiliser une équation pour ajuster une courbe à travers certains points de données. Le solveur peut trouver des constantes dans les équations qui correspondent le mieux aux données. Une autre application est qu’il est difficile de réorganiser le modèle de sorte que la sortie souhaitée devienne le sujet de l’équation.

Où est le solveur dans Excel ?

Le complément Solveur est inclus avec Excel, mais n’est pas toujours chargé dans le cadre de l’installation par défaut.Pour vérifier s’il est chargé, choisissez Les données onglet et trouver solveur icône dans analyser partie.

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

1. choisir document onglet, puis sélectionnez Choix.

2. à l’intérieur Choix sélection de la boîte de dialogue Modules complémentaires Onglets à partir de la gauche.

3. En bas de la fenêtre, sélectionnez Complément Excel de faire en sorte dérouler et sélectionner aller…

4. Cochez la case à côté de Plug-in de solveur et sélectionnez D’ACCORD.

5. Cette solveur La commande devrait maintenant apparaître dans Les données Étiquette. Vous êtes prêt à utiliser le Solveur.

Utilisation du solveur dans Excel

Commençons par un exemple simple pour comprendre ce que fait le Solver. Imaginons que nous voulions connaître le rayon d’un cercle d’une aire de 50 unités carrées. On connaît l’équation d’aire d’un cercle (A=pi r2). Bien sûr, nous pourrions réorganiser cette équation pour donner le rayon requis pour une zone donnée, mais à titre d’exemple, supposons que nous ne sachions pas comment faire cela.

Créer un rayon de B1 et calculer l’aire B2 utiliser l’équation =pi()*B1^2.

Nous pouvons ajuster manuellement la valeur B1 jusqu’à B2 Affichez une valeur suffisamment proche de 50. Selon la précision dont nous avons besoin, 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 Solver fait. Il ajuste la valeur dans certaines cellules et vérifie la valeur dans la cellule cible :

1. choisir Les données onglet et solveurcharger Paramètres du solveur dialogue

2. fixer un objectif les cellules sont des régions, B2C’est la valeur qui sera vérifiée, ajustez les autres cellules jusqu’à ce que cette valeur atteigne la valeur correcte.

3. bouton de sélection la valeur de: et définissez la valeur sur 50. C’est la valeur que B2 devrait atteindre.

4. dans le titre En changeant la cellule variable : Entrez la cellule contenant le rayon, B1.

5. Laissez les autres options par défaut, puis sélectionnez résoudrePour optimiser, ajustez la valeur de B1 jusqu’à ce que B2 soit 50, Résultats du solveur Afficher la conversation.

6. choisir D’ACCORD Gardez la solution.

Cet exemple simple montre comment fonctionne le solveur. Dans ce cas, nous pouvons obtenir la solution plus facilement par d’autres moyens. Ensuite, nous examinerons quelques exemples où Solver fournit des solutions difficiles à trouver autrement.

Ajustement de modèles complexes à l’aide du complément Excel Solver

Excel a des fonctions intégrées qui effectuent une régression linéaire, ajustant une ligne à travers un ensemble de données. De nombreuses fonctions non linéaires courantes peuvent être linéarisées, ce qui signifie que des fonctions telles que les exponentielles peuvent être ajustées à l’aide de la régression linéaire. Pour des fonctions plus complexes, Solver peut être utilisé pour effectuer des « moindres carrés ».Dans cet exemple, nous considérerons l’ajustement d’une équation de la forme hache ^ b + cx ^ d aux données indiquées ci-dessous.

Cela implique les étapes suivantes :

1. Organisez l’ensemble de données par les valeurs x dans la colonne A et les valeurs y dans la colonne B.

2. Créez 4 valeurs de coefficient (a, b, c et d) quelque part dans la feuille de calcul, pour lesquelles vous pouvez spécifier n’importe quelle valeur de départ.

3. Créez une colonne de valeurs Y ajustées, en faisant référence aux coefficients créés à l’étape 2 et aux valeurs x dans la colonne A à l’aide d’une équation de la forme ax^b+cx^d. Notez que pour copier la formule dans une colonne, la référence au coefficient doit être absolue et la référence à la valeur x doit être relative.

4. Bien que cela ne soit pas obligatoire, vous pouvez visualiser l’adéquation 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 brutes, 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 l’équation ajustée. La façon standard de le faire est de calculer la somme des différences au carré. Dans la troisième colonne, pour chaque ligne, soustrayez la valeur de données d’origine de Y de la valeur de l’équation ajustée et mettez le résultat au carré.Ainsi, dans D2la valeur est donnée par =(C2-B2)^2Calculez ensuite la somme de toutes ces valeurs au carré. Ces valeurs étant carrées, elles ne peuvent être que positives.

6. Vous êtes maintenant prêt à effectuer l’optimisation à l’aide du Solveur. Il y a quatre coefficients à régler (a, b, c et d). Vous avez également une valeur cible à minimiser, qui est la somme des différences au carré. Démarrez 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 cette option Rendre la variable sans contrainte non négativece qui force tous les coefficients à prendre des valeurs positives.

8. choisir résoudre et voir les résultats. Le graphique sera mis à jour avec une bonne indication de l’ajustement. Si le solveur ne convient pas du premier coup, vous pouvez réessayer de l’exécuter. Si l’ajustement s’améliore, essayez de résoudre à partir de la valeur actuelle. Sinon, vous pouvez essayer d’améliorer l’ajustement manuellement avant de résoudre.

9. Après avoir obtenu un bon ajustement, vous pouvez quitter le solveur.

Résoudre itérativement le modèle

Parfois, il existe une équation relativement simple qui donne une sortie basée sur une entrée. Cependant, lorsqu’on essaie d’inverser le problème, il est impossible de trouver une solution simple.Par exemple, la puissance consommée par le véhicule est d’environ P = moy + bv^3 où v est la vitesse, a est le coefficient de résistance au roulement et b est le coefficient de traînée aérodynamique. Bien qu’il s’agisse d’une équation très simple, il n’est pas facile de réorganiser l’équation pour donner la vitesse qu’un véhicule atteindra pour une puissance absorbée donnée. Cependant, nous pouvons trouver cette vitesse de manière itérative en utilisant Solver. Par exemple, trouvez la vitesse atteinte avec une puissance d’entrée de 740 W.

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

2. Démarrez le solveur et entrez l’alimentation, B5, comme cible.définir une valeur cible 740 et sélectionnez la vitesse, B2, en tant que cellule variable à modifier.choisir résoudre Démarrez la solution.

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

4. Résoudre un modèle de cette manière est généralement plus rapide et moins sujet aux erreurs que d’inverser un modèle complexe.

Comprendre les différentes options disponibles dans le solveur peut être très difficile. L’application de conditions aux limites à des éléments variables est souvent utile si vous rencontrez des difficultés pour obtenir une solution raisonnable. Ce sont les limites qui ne doivent pas être ajustées. Par exemple, dans l’exemple précédent, la vitesse ne doit pas être inférieure à zéro et une limite supérieure peut également être définie. Ce sera une vitesse dont vous êtes sûr que le véhicule ne peut pas aller plus vite que cela. Cela améliore également le fonctionnement d’autres options plus avancées si vous pouvez définir des limites pour les cellules variables variables, telles que multistart. Cela exécutera de nombreuses solutions différentes, en commençant par différentes valeurs initiales pour les variables.

Le choix d’une méthode de résolution peut également être difficile. Simplex LP ne fonctionne qu’avec des modèles linéaires, si le problème n’est pas linéaire, il échouera avec un message indiquant que cette condition n’est pas remplie. Les deux autres méthodes conviennent 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 avoir besoin de définir des limites variables. Le solveur évolutionnaire est généralement le plus fiable, mais il nécessite des bornes supérieures et inférieures pour toutes les variables, ce qui peut être difficile à calculer à l’avance.

Le complément Excel Solver est un outil très puissant qui peut être appliqué à de nombreux problèmes pratiques. Pour tirer pleinement parti de la puissance d’Excel, essayez d’utiliser le Solveur avec des macros Excel.

Merci de nous en informer!

Recevez les dernières nouvelles technologiques publiées quotidiennement

abonnement

Dites-nous pourquoi !

D’autres détails ne sont pas assez difficiles à comprendre