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

Excel est un outil efficace lorsque vos informations sont complètes. Cependant, ne serait-ce pas bien si cela pouvait résoudre les variables inconnues ?

Avec Goal Seek et le complément Solver, c’est possible. De plus, nous vous montrerons comment. Continuez à lire pour un guide complet sur la façon de corriger une seule cellule avec Goal Seek ou une équation beaucoup plus difficile avec Solver.

Comment utiliser la recherche d’objectifs dans Excel

Goal Seek est déjà développé dans Excel. C’est sous le Données onglet, dans l’onglet Analyse de simulation menu:

Pour cet exemple, nous utiliserons un ensemble de nombres très basique. Nous avons 3 trimestres de chiffres de ventes ainsi qu’un objectif annuel. Nous pouvons utiliser Goal Seek pour déterminer quels doivent être les chiffres au quatrième trimestre pour atteindre l’objectif.

Comme vous pouvez le constater, le montant total des ventes actuelles est de 114 706 unités. Si nous voulons en proposer 250 000 d’ici la fin de l’année, combien devons-nous commercialiser au quatrième trimestre ? La recherche d’objectifs d’Excel nous en informera.

Voici comment utiliser Goal Seek, détaillé :

1. Cliquez sur Données > > Analyse de simulation > > Recherche d’objectif . La fenêtre de recherche d’objectif apparaîtra certainement.
2. Mettez la partie « montants à » de votre équation dans le Définir la cellule champ. C’est le nombre qu’Excel essaiera certainement de maximiser. Dans notre cas, il s’agit de l’ensemble de nos chiffres de vente dans la cellule A5.
3. Tapez votre valeur d’objectif directement dans le Valoir champ. Nous recherchons un total de 250 000 appareils vendus, nous mettrons donc « 250 000 » dans ce domaine.
4. Indiquez à Excel la variable à résoudre dans le En modifiant la cellule champ. Nous avons l’intention de voir ce que nos ventes doivent être au quatrième trimestre. Nous informerons donc Excel de résoudre la cellule D2. Il ressemblera à ceci quand il sera prêt à partir :
5. Frapper d’accord à adresser pour votre objectif. Quand ça a l’air bien, appuyez simplement sur d’accord . Excel vous permettra certainement de savoir quand Goal Seek a effectivement trouvé un remède.
6. Cliquez sur d’accord une fois de plus, et vous verrez la valeur qui corrige votre équation dans la cellule que vous avez choisie pour En modifiant la cellule .

Dans notre situation, l’option est de 135 294 systèmes. Naturellement, nous pouvons simplement avoir trouvé cela en soustrayant le montant total cumulé de l’objectif annuel. Mais Goal Seek peut également être utilisé sur une cellule qui contient déjà des données . Ce qui est plus utile.

Notez qu’Excel écrase nos informations précédentes. C’est une excellente idée de exécuter Goal Seek sur un double de vos informations . C’est aussi une bonne idée de noter sur vos informations dupliquées qu’elles ont été créées à l’aide de Goal Seek. Vous n’avez pas l’intention de le déconcerter pour obtenir des informations actuelles et précises.

Goal Seek est donc une fonctionnalité Excel utile, mais ce n’est pas si bon que ça. Vous ne pouvez l’utiliser que sur une seule cellule à chaque fois. Si vous avez l’intention d’utiliser la recherche d’objectifs d’Excel sur plusieurs cellules en même temps, vous aurez besoin d’un outil beaucoup plus efficace. Heureusement, un tel outil est fourni avec Excel. Regardons le complément Solveur.

Que fait le solveur d’Excel ?

En bref, Solver ressemble à un version multivariée de Goal Seek . Si vous vous demandiez exactement comment utiliser Goal Seek dans Excel pour plusieurs cellules à la fois, c’est tout. Il prend une variable d’objectif et ajuste une variété d’autres variables jusqu’à ce qu’il obtienne la réponse souhaitée.

Il peut s’adresser à une valeur optimale d’un nombre, à une valeur minimale d’un nombre ou à un nombre spécifique. De plus, il fonctionne avec des restrictions, 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 corriger plusieurs variables inconnues dans Excel. Pourtant, la recherche ainsi que l’utilisation n’est pas simple. Examinons comment remplir le complément Solver, puis explorons exactement comment utiliser Solver dans la version Microsoft 365 existante d’Excel.

Comment charger le complément Solver

Excel n’a pas de Solveur par défaut. C’est un complément, vous devez donc l’emballer au départ. Heureusement, il se trouve actuellement sur votre système informatique.

Se diriger vers Fichier > > (Plus… >>) Options > > Compléments . Après ce clic Va à côté de Gérer : Compléments Excel .

Si cette liste déroulante revendique autre chose que « Excel Add-Ins », vous devrez la modifier :

Dans la fenêtre d’accueil résultante, vous verrez quelques alternatives. Voir le paquet à côté de Complément Solveur est inspecté, ainsi que frappé d’accord .

Vous verrez actuellement le Solveur bouton dans le Analyse groupe de la Données languette:

Si vous avez déjà utilisé le Data Analysis Toolpak, vous verrez le bouton Data Analysis. Sinon, Solver apparaîtra certainement tout seul.

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

Comment utiliser le solveur dans Excel

Toute activité Solver comporte trois parties : l’objectif, les cellules variables et les contraintes. Nous allons parcourir chacune des actions.

1. Cliquez sur Données > > Solveur . Vous verrez la fenêtre d’accueil des paramètres du solveur répertoriée ci-dessous. (Si vous ne voyez pas le bouton Solveur, consultez la zone précédente pour savoir comment remplir le complément Solveur.)
2. Définissez l’objectif de votre cellule et indiquez également votre objectif à Excel. L’objectif se trouve en haut de la fenêtre d’accueil du Solveur, et il comporte 2 composants : la cellule d’objectif ainsi qu’une sélection d’utilisation optimale, de diminution ou d’une valeur spécifique. Si tu choisis Max Excel réajustera vos variables pour obtenir le plus grand nombre possible dans votre cellule objectif. Min est l’inverse : le solveur réduira le nombre d’objectifs. Valeur de vous permet de définir un numéro de détails à rechercher par le Solveur.
3. Choisissez les cellules variables qu’Excel peut modifier. Les cellules variables sont établies avec les En changeant les cellules variables champ. Cliquez sur la flèche à côté de la zone, puis cliquez et faites glisser pour sélectionner les cellules que Solver doit traiter. Gardez à l’esprit que ce sont toutes les cellules ça peut varier. Si vous ne voulez pas qu’une cellule soit modifiée, ne la sélectionnez pas.
4. Définissez des contraintes sur plusieurs variables ou sur des variables privées. En fin de compte, nous concernons les contraintes. C’est là que Solver est vraiment puissant. Plutôt que de changer l’une des cellules variables en n’importe quel type de nombre qu’elle souhaite, vous pouvez définir des contraintes qui doivent être satisfaites. Pour plus de détails, consultez la section sur la façon de définir les restrictions ci-dessous.
5. Une fois que chacune de ces informations reste en place, frappée Résoudre pour obtenir votre réponse. Excel mettra certainement à jour vos données pour inclure les nouvelles variables (c’est pourquoi nous vous recommandons de créer une copie de vos données dans un premier temps).

Vous pouvez également générer des enregistrements, que nous vérifierons brièvement dans notre instance Solver répertoriée ci-dessous.

Comment définir des contraintes dans le solveur

Vous pouvez informer Excel que la variable doit être supérieure à 200. Lors de la tentative de différentes valeurs de variables, Excel n’ira pas en dessous de 201 avec cette certaine variable.

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

Voici les pilotes facilement disponibles :

• < (supérieur ou égal à)
• entier (Doit être un entier)
• poubelle (devrait être 1 ou 0)
• Tous différents

Tous différents est un peu compliqué. Il définit que chaque cellule de la variété que vous choisissez pour Référence de cellule 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 vous retrouverez avec les chiffres 1, 2 et 3 (mais pas toujours dans cet ordre)

Enfin, incluez la valeur de la restriction.

Il est essentiel 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 choisir et indiquer au Solveur qu’elles doivent être supérieures ou égales à 11. Vous n’avez pas besoin d’inclure une contrainte pour chaque cellule.

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

Un exemple de solveur

Pour voir exactement comment chacun de ces éléments fonctionne, nous utiliserons le complément Solver pour effectuer un calcul rapide. Voici les données avec lesquelles nous commençons :

Dans celui-ci, nous avons 5 emplois différents, chacun payant un taux différent. Nous avons également la variété d’heures qu’un employé théorique a effectuées à chacune de ces tâches au cours d’une semaine offerte. Nous pouvons utiliser le complément Solver pour comprendre comment tirer pleinement parti de la rémunération complète tout en conservant des variables particulières dans certaines contraintes.

Voici les contraintes que nous utiliserons :

• Aucune tâche peut descendre en dessous de 4 heures.
• Le travail 4 doit être supérieur à 12h .
• Le travail 5 doit être moins de onze heures .
• Le nombre total d’heures travaillées doit être égal à 40 .

Il peut être utile d’écrire vos restrictions comme celle-ci avant d’utiliser Solver.

Voici comment nous configurerions certainement cela dans Solver :

Tout d’abord, notez que J’ai produit un duplicata du tableau donc nous n’écrasons pas l’original, qui a nos heures de fonctionnement actuelles.

Et 2ème, voyez que les valeurs des contraintes supérieures à et inférieures à sont un supérieur ou inférieur que ce que j’ai mentionné ci-dessus. Cela est dû au fait qu’il n’y a pas d’alternative supérieure ou inférieure à. Il y a juste supérieur ou égal à et inférieur ou égal à.

Frappons Résoudre ainsi que voir ce qui se passe.

Le solveur a trouvé un remède ! Comme vous pouvez le voir à gauche de la fenêtre ci-dessus, nos revenus ont augmenté de 130 $. Et toutes les contraintes ont effectivement été satisfaites.

Pour conserver les valeurs neuves, assurez-vous Conserver la solution du solveur est inspecté et également frappé d’accord .

Si vous souhaitez plus d’informations, vous pouvez choisir un enregistrement dans la partie droite de la fenêtre. Sélectionnez tous les rapports que vous désirez, indiquez à Excel si vous voulez qu’ils soient décrits (je le conseille), et également frappé d’accord .

Les enregistrements sont produits sur de nouvelles feuilles dans votre classeur et vous offrent des informations sur le processus suivi par le complément Solver pour obtenir votre réponse.

Dans notre situation, les rapports ne sont pas extrêmement intéressants, de même qu’il n’y a pas beaucoup de détails intéressants. Pourtant, si vous exécutez une formule Solver plus complexe, vous trouverez peut-être des détails de rapport utiles dans ces toutes nouvelles feuilles de travail. Cliquez simplement sur le + bouton sur le côté de tout type de rapport pour obtenir plus d’informations :

Options avancées du solveur

Si vous ne connaissez pas grand-chose aux données, vous pouvez ignorer les choix innovants de Solver et simplement les exécuter tels quels. Pourtant, si vous exécutez des calculs énormes et complexes, vous voudrez peut-être les considérer.

La plus évidente est la technique de résolution :

Vous pouvez choisir entre GRG non linéaire, Simplex LP, ainsi que Evolutionary. Excel donne une explication simple concernant le moment où vous devez utiliser chacun. Une bien meilleure explication nécessite une certaine compréhension des données Excel ainsi que de la régression.

Pour réajuster les configurations supplémentaires, appuyez simplement sur le Choix bouton. Vous pouvez informer Excel de l’optimalité des nombres entiers, définir des restrictions de temps d’estimation (avantageux pour les ensembles de données volumineux) et ajuster la manière dont les approches de résolution GRG et évolutionnaire procèdent à leurs calculs.

Encore une fois, si vous ne comprenez pas ce que tout cela suggère, ne vous inquiétez pas. Si vous avez besoin d’en savoir plus sur l’approche de résolution à utiliser, Ingénieur Excel a une excellente rédaction qui l’énonce pour vous. Si vous désirez une précision maximale, Evolutionary est probablement une excellente solution. Sachez simplement que cela prendra certainement beaucoup de temps.

Recherche d’objectifs et solveur : faire passer Excel au niveau supérieur

Maintenant que vous êtes à l’aise avec l’essentiel de l’adressage des variables non identifiées dans Excel, un tout nouveau monde d’estimation de feuille de calcul s’ouvre à vous. Goal Seek peut vous aider à gagner du temps en effectuant certains calculs beaucoup plus rapidement, et Solver ajoute une grande quantité de puissance aux capacités de calcul d’Excel.

C’est juste une question d’être à l’aise avec eux. Plus vous les utiliserez, plus ils deviendront utiles.