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 ne serait-ce pas bien si cela pouvait résoudre les 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'objectif dans Excel

Goal Seek est déjà intégré à Excel. C'est sous le Données onglet, dans le Analyse de simulation menu:

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 en vendre au quatrième trimestre ? La recherche d'objectifs d'Excel nous le dira.

Voici comment utiliser la recherche d'objectif, étape par étape :

Cliquez sur Données > Analyse de simulation > Recherche d'objectifs . Vous verrez cette fenêtre :
Mettez la partie « égale » 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.
Tapez la valeur de votre objectif dans le Évaluer champ. Nous recherchons un total de 250 000 unités vendues, nous mettrons donc « 250 000 » dans ce champ.
Dites à Excel quelle variable résoudre dans le En changeant de cellule champ. Nous voulons voir quelles devraient être nos ventes au quatrième trimestre. Nous allons donc dire à Excel de résoudre la cellule D2. Cela ressemblera à ceci quand il sera prêt à partir :
Frappé d'accord à résoudre pour votre objectif. Quand ça a l'air bien, il suffit de frapper d'accord . Excel vous informera lorsque Goal Seek aura trouvé une solution.
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 .

Dans notre cas, la solution est de 135 294 unités. Bien sûr, nous aurions pu le trouver en soustrayant le total cumulé de l'objectif annuel. Mais Goal Seek peut également être utilisé sur une cellule qui 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 aussi 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.

Instagram informe-t-il lorsque vous capturez un dm

Donc, la recherche de but est un fonctionnalité Excel utile , mais ce n'est pas si impressionnant que ça. Jetons un coup d'œil à un outil bien 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 souhaitée.

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.

Jetons un coup d'œil au chargement du complément Solver, puis voyons comment utiliser Solver dans Excel 2016.

Comment charger le complément du solveur

Excel n'a pas de solveur par défaut. C'est un complément donc, comme d'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 dit autre chose que 'Excel Add-Ins', vous devrez la modifier :

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

Vous verrez maintenant le Solveur bouton dans le Une analyse groupe de la Données languette:

Si vous avez déjà utilisé le Boîte à outils d'analyse de données , 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 le solveur dans Excel

Il y a trois parties à toute action Solver : l'objectif, les cellules variables et les contraintes. Nous allons parcourir chacune des étapes.

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.)
Définissez votre objectif de cellule et indiquez votre objectif à Excel. L'objectif se trouve en haut de la fenêtre du Solveur et comporte deux parties : la cellule de l'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 objectif. Min est le contraire : le solveur minimisera le nombre d'objectifs. Valeur de vous permet de spécifier un numéro spécifique que le solveur doit rechercher.
Choisissez les cellules variables qu'Excel peut modifier. Les cellules variables sont définies avec le En modifiant les cellules variables champ. Cliquez sur la flèche en regard du champ, puis cliquez et faites glisser pour sélectionner les cellules avec lesquelles Solver doit fonctionner. Notez que ce sont toutes les cellules cela peut varier. Si vous ne voulez pas qu'une cellule change, ne la sélectionnez pas.
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 n'importe quel nombre, vous pouvez spécifier des contraintes qui doivent être respectées. Pour plus de détails, voir la section sur la façon de définir des contraintes ci-dessous.
Une fois toutes ces informations en place, appuyez sur Résoudre pour avoir 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 de solveur ci-dessous.

Comment définir des contraintes dans le solveur

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

Pour ajouter une contrainte, cliquez sur le Ajouter bouton à 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.

Voici les opérateurs disponibles :

<= (inférieur ou égal à)
= (égal à)
=> (Plus grand ou égal à)
entier (Doit être un entier)
un m (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 numéro 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 vous retrouverez avec les nombres 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 la référence de cellule. Si vous souhaitez que six variables aient des valeurs supérieures à 10, par exemple, vous pouvez toutes les sélectionner et indiquer au Solveur qu'elles doivent être supérieures ou égales à 11. Vous n'avez pas besoin d'ajouter de 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 voulez que vos variables deviennent négatives, décochez cette case.

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 :

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 conservant certaines variables dans certaines contraintes.

Voici les contraintes que nous utiliserons :

Pas d'emploi peut descendre en dessous de quatre heures.
L'emploi 2 doit être plus de huit heures .
L'emploi 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 celle-ci avant d'utiliser Solver.

Voici comment nous avons configuré cela dans Solver :

Tout d'abord, notez que J'ai créé une copie du tableau nous n'écrasons donc pas l'original, qui contient nos heures de travail actuelles.

Et deuxièmement, voyez que les valeurs dans les contraintes supérieur à et inférieur à 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 voir ce qui se passe.

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.

connecter la tablette samsung à la télévision via usb

Pour conserver les nouvelles valeurs, assurez-vous Conserver la solution du solveur est coché 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 donnent des informations sur le processus suivi par le complément Solver pour obtenir votre réponse.

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

Options avancées du solveur

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

La plus évidente est la méthode de résolution :

Vous pouvez choisir entre GRG Nonlinear, 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 le bouton Options bouton. Vous pouvez informer Excel de l'optimalité entière, définir des contraintes de temps de calcul (utiles pour les ensembles de données volumineux) et ajuster la manière dont les méthodes de résolution GRG et évolutionnaire effectuent leurs calculs.

Encore une fois, si vous ne savez pas ce que tout 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'explique . Si vous voulez une précision maximale, Evolutionary est probablement une bonne solution. Sachez simplement que cela prendra beaucoup de temps.

Recherche et résolution d'objectifs : faire passer Excel au niveau supérieur

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

Goal Seek peut vous aider à gagner du temps en effectuant certains calculs plus rapidement, et Solver ajoute une énorme quantité de puissance à Les capacités de calcul d'Excel .

C'est juste une question de se sentir à l'aise avec eux. Plus vous les utiliserez, plus ils deviendront 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!

Partager Partager Tweeter E-mail 5 astuces pour booster vos machines Linux VirtualBox

Vous en avez assez des mauvaises performances offertes par les machines virtuelles ? Voici ce que vous devez faire pour améliorer les performances de votre VirtualBox.

Lire la suite Rubriques connexes

Productivité
Tableur
Microsoft Excel
Conseils Microsoft Office

A propos de l'auteur Puis Albright(506 articles publiés)

Dann est un consultant en stratégie de contenu et marketing qui aide les entreprises à générer de la demande et des prospects. Il blogue également sur la stratégie et le marketing de contenu sur dannalbright.com.

Plus de Dann Albright

Abonnez-vous à notre newsletter

Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !

Cliquez ici pour vous abonner