Comment utiliser l'objectif de recherche et de résolution d'Excel pour résoudre des variables inconnues

Publicité

Excel est très performant quand vous avez toutes les données dont vous avez besoin pour vos calculs.

Mais ne serait-ce pas bien si cela pouvait résoudre des variables inconnues ?

Avec Goal Seek et le complément Solver, il le peut. Et nous allons vous montrer comment. Lisez la suite pour un guide complet sur la façon de résoudre pour une seule cellule avec Goal Seek ou une équation plus compliquée avec Solver.

Comment utiliser Goal Seek dans Excel

Goal Seek est déjà intégré dans Excel. C'est sous l'onglet Données, dans le menu Analyse d' hypothèses:

Pour cet exemple, nous utiliserons un ensemble de nombres très simple. Nous avons trois quarts de chiffre d'affaires et un objectif annuel. Nous pouvons utiliser Goal Seek pour déterminer ce que les chiffres doivent être au quatrième trimestre pour atteindre l'objectif.

Comme vous pouvez le voir, le total des ventes actuelles est de 114 706 unités. Si nous voulons vendre 250 000 d'ici la fin de l'année, combien devons-nous vendre au quatrième trimestre? L'objectif de Excel nous le dira.

Voici comment utiliser Goal Seek, étape par étape:

1. Cliquez sur Data> What-If Analysis> Goal Seek . Vous verrez cette fenêtre:
   
2. Mettez la partie "égale" de votre équation dans le champ Set Cell . C'est le nombre qu'Excel va essayer d'optimiser. Dans notre cas, c'est le total courant de nos chiffres de ventes dans la cellule B5.
3. Tapez votre valeur d'objectif dans le champ Valeur . Nous cherchons un total de 250 000 unités vendues, nous allons donc mettre "250 000" dans ce domaine.
4. Indiquez à Excel la variable à résoudre dans le champ En modifiant la cellule . Nous voulons voir ce que nos ventes au quatrième trimestre doivent être. Nous allons donc dire à Excel de résoudre pour la cellule D2. Cela ressemblera à ceci quand il est prêt à partir:
   
5. Hit OK pour résoudre pour votre objectif. Quand cela semble bon, appuyez simplement sur OK . Excel vous indiquera quand Goal Seek a trouvé une solution.
   
6. Cliquez à nouveau sur OK et vous verrez la valeur qui résout votre équation dans la cellule que vous avez choisie pour En changeant la cellule .
   

Dans notre cas, la solution est de 135 294 unités. Bien sûr, nous aurions pu trouver cela en soustrayant le total cumulé de l'objectif annuel. Mais Goal Seek peut également être utilisé sur une cellule contenant déjà des données . Et c'est plus utile.

Notez qu'Excel écrase nos données précédentes. C'est une bonne idée d' exécuter Goal Seek sur une copie de vos données . C'est également une bonne idée de noter sur vos données copiées qu'elles ont été générées à l'aide de Goal Seek. Vous ne voulez pas le confondre avec des données actuelles et précises.

Donc, la recherche d'objectifs est une fonction Excel utile 16 formules Excel qui vous aideront à résoudre des problèmes réels 16 Formules Excel qui vous aideront à résoudre des problèmes de la vie réelle Le bon outil est la moitié du travail. Excel peut résoudre les calculs et traiter les données plus rapidement que vous ne pouvez trouver votre calculatrice. Nous vous montrons les principales formules Excel et démontrons comment les utiliser. Lire la suite, mais ce n'est pas si impressionnant. Jetons un coup d'oeil à un outil beaucoup plus intéressant: le complément Solver.

Que fait le solveur d'Excel?

En bref, Solver est comme une version multivariée de Goal Seek . Il prend une variable d'objectif et ajuste un certain nombre d'autres variables jusqu'à ce qu'il obtienne la réponse que vous voulez.

Il peut résoudre pour une valeur maximale d'un nombre, une valeur minimale d'un nombre ou un nombre exact.

Et cela fonctionne dans les contraintes, donc si une variable ne peut pas être modifiée, ou ne peut varier que dans une plage spécifiée, Solver en tiendra compte.

C'est un excellent moyen de résoudre plusieurs variables inconnues dans Excel. Mais la trouver et l'utiliser n'est pas simple.

Jetons un coup d'oeil au chargement du complément Solver, puis passons à l'utilisation de Solver dans Excel 2016.

Comment charger le complément Solveur

Excel n'a pas Solver par défaut. C'est un add-in donc, comme d'autres puissantes fonctionnalités Excel Power Up Excel avec 10 Add-Ins pour traiter, analyser et visualiser les données comme un Pro Power Up Excel avec 10 Add-Ins pour traiter, analyser et visualiser des données comme un Pro Vanilla Excel est incroyable, mais vous pouvez le rendre encore plus puissant avec des compléments. Quelles que soient les données que vous devez traiter, il est probable que quelqu'un ait créé une application Excel pour cela. Voici une sélection. Lire la suite, vous devez d'abord le charger. Heureusement, c'est déjà sur votre ordinateur.

Allez dans Fichier> Options> Compléments . Ensuite, cliquez sur Go à côté de Manage: Excel Add-Ins .

Si cette liste déroulante indique autre chose que "Excel Add-Ins", vous devrez le modifier:

Dans la fenêtre qui s'affiche, vous verrez quelques options. Assurez-vous que la case à côté de Solveur Add-In est cochée, et cliquez sur OK .

Vous verrez maintenant le bouton Solveur dans le groupe Analyse de l'onglet Données :

Si vous avez déjà utilisé l'outil d'analyse des données Comment faire l'analyse des données de base dans Excel Comment faire l'analyse des données de base dans Excel Excel n'est pas destiné à l'analyse des données, mais il peut toujours gérer les statistiques. Nous allons vous montrer comment utiliser le module d'extension Data Analysis Toolpak pour exécuter des statistiques Excel. En savoir plus, vous verrez le bouton Analyse des données. Sinon, Solver apparaîtra tout seul.

Maintenant que vous avez chargé le complément, voyons comment l'utiliser.

Comment utiliser Solver dans Excel

L'action Solveur comporte trois parties: l'objectif, les cellules variables et les contraintes. Nous allons parcourir chacune des étapes.

1. Cliquez sur Données> Solveur . Vous verrez la fenêtre Paramètres du solveur ci-dessous. (Si vous ne voyez pas le bouton du solveur, reportez-vous à la section précédente sur la procédure de chargement du complément Solver.)
   
2. Définissez l'objectif de votre cellule et dites à Excel votre objectif. L'objectif est en haut de la fenêtre du Solveur, et il a deux parties: la cellule d'objectif et un choix de maximiser, minimiser, ou une valeur spécifique.
   
   Si vous sélectionnez Max, Excel ajustera vos variables pour obtenir le plus grand nombre possible dans votre cellule d'objectif. Min est le contraire: Solver minimisera le nombre d'objectifs. La valeur de vous permet de spécifier un nombre spécifique pour le solveur à rechercher.
3. Choisissez les cellules variables qu'Excel peut modifier. Les cellules variables sont définies avec le champ By Changing Variable Cells . Cliquez sur la flèche en regard du champ, puis cliquez et faites glisser pour sélectionner les cellules avec lesquelles Solver doit travailler. Notez que ce sont toutes les cellules qui peuvent varier. Si vous ne voulez pas qu'une cellule change, ne la sélectionnez pas.
   
4. Définissez des contraintes sur des variables multiples ou individuelles. Enfin, nous arrivons aux contraintes. C'est là que Solver est vraiment puissant. Au lieu de changer l'une des cellules variables en un nombre quelconque, vous pouvez spécifier les contraintes qui doivent être respectées. Pour plus de détails, voir la section sur la définition des contraintes ci-dessous.
5. Une fois toutes ces informations en place, appuyez sur Résoudre pour obtenir votre réponse. Excel mettra à jour vos données pour inclure les nouvelles variables (c'est pourquoi nous vous recommandons de créer d'abord une copie de vos données).

Vous pouvez également générer des rapports, que nous verrons brièvement dans notre exemple Solver ci-dessous.

Comment définir des contraintes dans Solver

Vous pouvez indiquer à Excel qu'une variable doit être supérieure à 200. Lors de l'essai de différentes valeurs de variables, Excel ne sera pas inférieur à 201 avec cette variable particulière.

Pour ajouter une contrainte, cliquez sur le bouton Ajouter en regard de la liste des contraintes. Vous aurez une nouvelle fenêtre. Sélectionnez la cellule (ou les cellules) à contraindre dans le champ Référence de cellule, puis choisissez un opérateur.

Voici les opérateurs disponibles:

• <= (inférieur ou égal à)
• = (égal à)
• => (supérieur ou égal à)
• int (doit être un entier)
• bin (doit être 1 ou 0)
• Tous différents

AllDifferent est un peu déroutant. Il spécifie que chaque cellule de la plage que vous sélectionnez pour Cell Reference doit être un nombre différent. Mais il précise également qu'ils doivent être compris entre 1 et le nombre de cellules. Donc, si vous avez trois cellules, vous finirez avec les numéros 1, 2 et 3 (mais pas nécessairement dans cet ordre)

Enfin, ajoutez la valeur pour la contrainte.

Il est important de se rappeler que vous pouvez sélectionner plusieurs cellules pour la référence de cellule. Si vous voulez que six variables aient des valeurs supérieures à 10, par exemple, vous pouvez les sélectionner toutes et indiquer à Solver qu'elles doivent être supérieures ou égales à 11. Il n'est pas nécessaire d'ajouter une contrainte pour chaque cellule.

Vous pouvez également utiliser la case à cocher dans la fenêtre principale du Solveur pour vous assurer que toutes les valeurs pour lesquelles vous n'avez pas spécifié de contraintes sont non négatives. Si vous voulez que vos variables deviennent négatives, décochez cette case.

Un exemple de solveur

Pour voir comment tout cela fonctionne, nous utiliserons le complément Solver pour faire un calcul rapide. Voici les données que nous commençons avec:

Dans celui-ci, nous avons cinq emplois différents, dont chacun paie un taux différent. Nous avons également le nombre d'heures qu'un travailleur théorique a travaillé à chacun de ces emplois dans une semaine donnée. Nous pouvons utiliser le complément Solver pour découvrir comment maximiser le salaire total tout en gardant certaines variables dans certaines contraintes.

Voici les contraintes que nous utiliserons:

• Aucun travail ne peut tomber en dessous de quatre heures.
• Le travail 2 doit être supérieur à huit heures .
• Le travail 5 doit être inférieur à onze heures .
• Le nombre total d'heures travaillées doit être égal à 40 .

Il peut être utile d'écrire vos contraintes de cette manière avant d'utiliser Solver.

Voici comment nous pourrions le configurer dans Solver:

Tout d'abord, notez que j'ai créé une copie de la table afin de ne pas remplacer celle d'origine, qui contient nos heures de travail actuelles.

Et deuxièmement, voyons que les valeurs des contraintes supérieures à et inférieures à sont supérieures ou inférieures à celles que j'ai mentionnées plus haut. C'est parce qu'il n'y a pas de plus-ou-moins-que des options. Il n'y a que plus grand que-ou-égal-à et moins-que-ou-égal-à.

Allons résoudre Solve et voir ce qui se passe.

Solver a trouvé une solution! Comme vous pouvez le voir à gauche de la fenêtre ci-dessus, nos revenus ont augmenté de 130 $. Et toutes les contraintes ont été respectées.

Pour conserver les nouvelles valeurs, assurez-vous que la solution Keep Solver est cochée et cliquez sur OK .

Si vous voulez plus d'informations, cependant, vous pouvez sélectionner un rapport sur le côté droit de la fenêtre. Sélectionnez tous les rapports que vous voulez, dites à Excel si vous voulez les décrire (je le recommande), et cliquez sur OK .

Les rapports sont générés sur de nouvelles feuilles dans votre classeur et vous donnent des informations sur le processus que le complément Solver a suivi pour obtenir votre réponse.

Dans notre cas, les rapports ne sont pas très excitants, et il n'y a pas beaucoup d'informations intéressantes là-bas. Mais si vous exécutez une équation de Solver plus compliquée, vous trouverez peut-être des informations de rapport utiles dans ces nouvelles feuilles de calcul. Cliquez simplement sur le bouton + sur le côté de n'importe quel rapport pour obtenir plus d'informations:

Options avancées du solveur

Si vous ne savez pas grand-chose sur les statistiques, vous pouvez ignorer les options avancées de Solver et simplement l'exécuter tel quel. Mais si vous exécutez des calculs volumineux et complexes, vous voudrez peut-être les examiner.

Le plus évident est la méthode de résolution:

Vous pouvez choisir entre GRG Non linéaire, Simplex LP et Evolutionary. Excel fournit une explication simple concernant le moment où vous devriez utiliser chacun d'eux. Une meilleure explication nécessite une certaine connaissance des statistiques Apprendre des statistiques gratuitement avec ces 6 ressources Apprendre des statistiques gratuitement avec ces 6 ressources Les statistiques ont la réputation d'un sujet difficile à comprendre. Mais apprendre de la bonne ressource vous aidera à comprendre les résultats du sondage, les rapports d'élection et les affectations de vos classes de statistiques en un rien de temps. Lire la suite et la régression.

Pour ajuster les paramètres supplémentaires, appuyez simplement sur le bouton Options . Vous pouvez expliquer à Excel l'optimalité des entiers, définir des contraintes de temps de calcul (utiles pour les jeux de données massifs) et ajuster la façon dont les méthodes de résolution GRG et Evolutionary vont effectuer leurs calculs.

Encore une fois, si vous ne savez pas ce que cela signifie, ne vous inquiétez pas. Si vous voulez en savoir plus sur la méthode de résolution à utiliser, Engineer Excel a un bon article qui vous l'offre. Si vous voulez une précision maximale, Evolutionary est probablement un bon moyen d'y aller. Sachez simplement que cela prendra du temps.

Recherche d'objectifs et résolution de problèmes: prendre Excel au premier plan

Maintenant que vous êtes à l'aise avec les bases de la résolution de variables inconnues dans Excel, un tout nouveau monde de calcul de feuille de calcul vous est ouvert.

Goal Seek peut vous aider à gagner du temps en faisant quelques calculs plus rapides, et Solver ajoute une énorme quantité de puissance aux capacités de calcul d'Excel Comment calculer les statistiques de base dans Excel: Guide d'un débutant Comment calculer les statistiques de base dans Excel: Guide d'un débutant fais des statistiques! Vous pouvez calculer les pourcentages, les moyennes, l'écart-type, les erreurs standard et les tests-T de l'étudiant. Lire la suite .

C'est juste une question de se mettre à l'aise avec eux. Plus vous les utilisez, plus ils deviendront utiles.

Utilisez-vous Goal Seek ou Solver dans vos feuilles de calcul? Quels autres conseils pouvez-vous fournir pour obtenir les meilleures réponses sur eux? Partagez votre opinion dans les commentaires ci-dessous!