Comment utiliser la recherche d’objectifs et le solveur d’Excel pour résoudre des variables inconnues

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

Mais ce ne serait pas bien si ça pouvait résoudre des variables inconnues?

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

Comment utiliser la recherche d’objectifs dans Excel

La recherche d’objectifs est déjà intégrée à Excel. C’est sous la Les données dans l’onglet Analyse de simulation menu:

excel objectif chercher résoudre des variables inconnues

Pour cet exemple, nous utiliserons un ensemble de nombres très simple. Nous avons les trois quarts des chiffres de vente 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.

excel objectif chercher résoudre des variables inconnues

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? La recherche d’objectifs d’Excel nous le dira.

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

  1. Cliquez sur Données> Analyse de simulation> Recherche d’objectifs. Vous verrez cette fenêtre:
    excel objectif chercher résoudre des variables inconnues
  2. Mettez la partie «égal» de votre équation dans le Définir la cellule champ. C’est le nombre qu’Excel tentera d’optimiser. Dans notre cas, il s’agit du total cumulé de nos chiffres de vente dans la cellule B5.
  3. Saisissez la valeur de votre objectif dans le Évaluer champ. Nous recherchons un total de 250 000 unités vendues, nous allons donc mettre “250 000” dans ce champ.
  4. Dites à Excel quelle variable résoudre dans le En changeant de cellule champ. Nous voulons voir ce que doivent être nos ventes au quatrième trimestre. Nous allons donc demander à Excel de résoudre la cellule D2. Cela ressemblera à ceci quand il sera prêt à fonctionner:
    excel objectif chercher résoudre des variables inconnues
  5. Frappé D’accord à résoudre pour votre objectif. Quand ça a l’air bien, il suffit de frapper D’accord. Excel vous avertira lorsque Goal Seek a trouvé une solution.
    excel objectif chercher résoudre des variables inconnues
  6. Cliquez sur D’accord à nouveau et vous verrez la valeur qui résout votre équation dans la cellule que vous avez choisie pour En changeant de cellule.
    excel objectif chercher résoudre des variables inconnues

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 contient 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 de 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 la recherche d’objectifs. Vous ne voulez pas le confondre avec des données actuelles et précises.

La recherche d’objectifs est donc une fonctionnalité Excel utile, mais elle n’est pas si impressionnante. Jetons un œil à un outil beaucoup plus intéressant: le complément Solver.

Que fait le solveur d’Excel?

En bref, Solver est comme un 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 souhaitez.

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

Et cela fonctionne avec des 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 le trouver et l’utiliser n’est pas simple.

Voyons comment charger le complément Solver, puis expliquons comment utiliser Solver dans Excel 2016.

Comment charger le complément Solver

Excel n’a pas de solveur par défaut. C’est un complément, comme pour les autres fonctionnalités Excel puissantes, vous devez d’abord le charger. Heureusement, il est déjà sur votre ordinateur.

Se diriger vers Fichier> Options> Compléments. Cliquez ensuite sur Aller à côté de Gérer: compléments Excel.

Si cette liste déroulante indique autre chose que “Compléments Excel”, vous devrez le modifier:

excel objectif chercher résoudre des variables inconnues

Dans la fenêtre résultante, vous verrez quelques options. Assurez-vous que la case à côté de Complément Solveur est vérifié, et appuyez sur D’accord.

excel objectif chercher résoudre des variables inconnues

Vous verrez maintenant le Solveur bouton dans le Une analyse groupe des Les données languette:

En relation :  Les meilleurs modèles PowerPoint pour les présentations éducatives

excel objectif chercher résoudre des variables inconnues

Si vous avez déjà utilisé le Data Analysis Toolpak, vous verrez le bouton Data Analysis. Sinon, le solveur apparaîtra de lui-même.

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

Comment utiliser le solveur dans Excel

Chaque action du solveur comprend 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, consultez la section précédente sur la façon de charger le complément Solveur.)
    excel objectif chercher résoudre des variables inconnues
  2. Définissez votre objectif de cellule et dites à Excel votre objectif. L’objectif est en haut de la fenêtre du solveur et il se compose de deux parties: la cellule d’objectif et un choix de maximisation, de réduction ou d’une valeur spécifique.
    excel objectif chercher résoudre des variables inconnues

    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: le solveur minimisera le nombre d’objectifs. Valeur de vous permet de spécifier un nombre spécifique que Solver doit rechercher.

  3. Choisissez les cellules variables qu’Excel peut modifier. Les cellules variables sont définies avec le En changeant les cellules variables champ. Cliquez sur la flèche à côté du champ, puis cliquez et faites glisser pour sélectionner les cellules avec lesquelles Solver doit travailler. Notez que ce sont toutes les cellules cela peut varier. Si vous ne voulez pas qu’une cellule change, ne la sélectionnez pas.
    excel objectif chercher résoudre des variables inconnues
  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 remplacer l’une des cellules variables par le nombre souhaité, vous pouvez spécifier les contraintes à respecter. Pour plus de détails, consultez 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 examinerons brièvement dans notre exemple de solveur ci-dessous.

Comment définir des contraintes dans le solveur

Vous pourriez dire à Excel qu’une variable doit être supérieure à 200. Lorsque vous essayez différentes valeurs de variable, Excel ne passera pas en dessous de 201 avec cette variable particulière.

excel objectif chercher résoudre des variables inconnues

Pour ajouter une contrainte, cliquez sur le Ajouter à côté de la liste des contraintes. Vous obtiendrez une nouvelle fenêtre. Sélectionnez la ou les cellules à contraindre dans le Référence de cellule champ, puis choisissez un opérateur.

excel objectif chercher résoudre des variables inconnues

Voici les opérateurs disponibles:

  • <= (inférieur ou égal à)
  • = (égal à)
  • => (Plus grand ou égal à)
  • int (Doit être un entier)
  • poubelle (doit être 1 ou 0)
  • Tous différents

Tous différents est un peu déroutant. Il spécifie que chaque cellule de la plage que vous sélectionnez pour Référence de cellule doit être un nombre différent. Mais elle précise également qu’elles doivent être comprises entre 1 et le nombre de cellules. Donc, si vous avez trois cellules, vous vous retrouverez avec les chiffres 1, 2 et 3 (mais pas nécessairement dans cet ordre)

Enfin, ajoutez la valeur de la contrainte.

Il est important de se rappeler que vous pouvez sélectionner plusieurs cellules pour référence de cellule. Si vous voulez que six variables aient des valeurs supérieures à 10, par exemple, vous pouvez toutes les sélectionner et dire à Solver qu’elles doivent être supérieures ou égales à 11. Vous n’avez pas besoin 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 ne sont pas négatives. Si vous souhaitez que vos variables deviennent négatives, décochez cette case.

excel objectif chercher résoudre des variables inconnues

Un exemple de solveur

Pour voir comment tout cela fonctionne, nous allons utiliser le complément Solver pour effectuer un calcul rapide. Voici les données avec lesquelles nous commençons:

excel objectif chercher résoudre des variables inconnues

Nous y avons cinq emplois différents, chacun payant un taux différent. Nous avons également le nombre d’heures qu’un travailleur théorique a travaillé à chacun de ces emplois au cours d’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 allons utiliser:

  • Pas d’emplois peut tomber en dessous de quatre heures.
  • Le travail 2 doit être plus de huit heures.
  • Le travail 5 doit être moins de onze heures.
  • Le total des heures travaillées doit être égal à 40.

Il peut être utile d’écrire vos contraintes comme ceci avant d’utiliser le solveur.

Voici comment nous avons configuré cela dans Solver:

excel objectif chercher résoudre des variables inconnues

Tout d’abord, notez que J’ai créé une copie du tableau nous ne remplaçons donc pas l’original, qui contient nos heures de travail actuelles.

Et deuxièmement, voyez que les valeurs dans les contraintes supérieures à et inférieures à sont un supérieur ou inférieur que ce que j’ai mentionné ci-dessus. C’est parce qu’il n’y a pas d’options supérieures ou inférieures à. Il n’y a que supérieur ou égal à et inférieur ou égal à.

Frappons Résoudre et voyez ce qui se passe.

excel objectif chercher résoudre des variables inconnues

Le solveur 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 Conserver la solution du solveur est vérifié et frappé D’accord.

Si vous souhaitez plus d’informations, vous pouvez sélectionner un rapport dans la partie droite de la fenêtre. Sélectionnez tous les rapports que vous voulez, dites à Excel si vous voulez qu’ils soient décrits (je le recommande) et appuyez sur D’accord.

Les rapports sont générés sur de nouvelles feuilles dans votre classeur et vous fournissent 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 solveur plus compliquée, vous pouvez trouver des informations de rapport utiles dans ces nouvelles feuilles de calcul. Cliquez simplement sur le + bouton sur le côté de tout rapport pour obtenir plus d’informations:

excel objectif chercher résoudre des variables inconnues

Options avancées du solveur

Si vous ne savez pas grand-chose sur les statistiques, vous pouvez ignorer les options avancées du Solveur et 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:

excel objectif chercher résoudre des variables inconnues

Vous pouvez choisir entre GRG Non linéaire, Simplex LP et Evolutionary. Excel fournit une explication simple concernant le moment où vous devez utiliser chacun d’eux. Une meilleure explication nécessite une certaine connaissance des statistiques et de la régression.

Pour ajuster des paramètres supplémentaires, appuyez simplement sur la touche Les options bouton. Vous pouvez indiquer à Excel l’optimalité entière, définir des contraintes de temps de calcul (utiles pour les ensembles de données massifs) et ajuster la façon dont les méthodes de résolution GRG et évolutionnaire effectuent leurs calculs.

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

Recherche d’objectif et solveur: amener Excel au niveau supérieur

Maintenant que vous maîtrisez les bases de la résolution des variables inconnues dans Excel, un tout nouveau monde de calcul de feuille de calcul s’offre à vous.

La recherche d’objectifs peut vous aider à gagner du temps en effectuant certains calculs plus rapidement, et le solveur ajoute une énorme quantité de puissance aux capacités de calcul d’Excel.

C’est juste une question de se familiariser avec eux. Plus vous les utilisez, plus ils deviennent utiles.

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

Moyens Staff
Moyens I/O Staff vous a motivé, donner des conseils sur la technologie, le développement personnel, le style de vie et des stratégies qui vous aider.