Comment résoudre des variables inconnues à l’aide de la recherche d’objectifs et du solveur d’Excel

Comment résoudre des variables inconnues à l’aide de la recherche d’objectifs et du solveur d’Excel

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

Mais ne serait-ce pas bien si vous le pouviez ? Résoudre les variables inconnues?

Avec les plugins Goal Seek et Solver, c’est possible. 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 des équations plus complexes avec Solver.

Comment utiliser la recherche ciblée dans Excel

Goal Seek est déjà intégré à Excel.c’est en dessous Les données onglet, dans analyse de simulation menu:

Pour cet exemple, nous utiliserons un ensemble de nombres très simple. Nous avons des chiffres de ventes aux trois quarts et des objectifs annuels. Nous pouvons utiliser Goal Seek pour déterminer quels chiffres sont nécessaires au quatrième trimestre pour atteindre la cible.

Comme vous pouvez le constater, le total actuel des ventes est de 114 706 unités. Si nous voulons en vendre 250 000 d’ici la fin de l’année, combien devons-nous en vendre au quatrième trimestre ? La recherche d’objectif 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 :
2. Mettez la partie « égale » de l’équation dans cellule placer. C’est le nombre qu’Excel va essayer d’optimiser. Dans notre cas, c’est la somme de nos chiffres de ventes dans la cellule B5.
3. exister attacher une grande importance à placer. Nous recherchons un total de 250 000 unités vendues, nous entrerons donc « 250 000 » dans ce champ.
4. dire à Excel de en changeant de cellule placer. Nous voulons voir combien de ventes nous devons atteindre au quatrième trimestre. Par conséquent, nous dirons à Excel de résoudre la cellule D2. Une fois prêt, ça ressemble à ça :
5. battre d’accord résoudre votre objectif.Quand ça a l’air bien, il suffit de cliquer d’accordExcel vous avertit lorsque Goal Seek trouve une solution.
6. Cliquez sur d’accord Encore une fois, vous verrez la valeur de l’équation résolue dans la cellule que vous avez sélectionnée en changeant de cellule.

Faire une vidéo du jour

Dans notre cas, la solution est de 135 294 unités. Bien sûr, nous pouvons le savoir en soustrayant le total cumulé de l’objectif annuel.Mais Goal Seek peut aussi être utilisé sur une cellule Il y a déjà des données là-dedansC’est plus utile.

Notez qu’Excel écrase nos données précédentes.C’est une bonne idée Exécutez la recherche d’objectifs sur votre copie de donnéesC’est aussi une bonne idée de consigner les données de réplication générées à l’aide de Goal Seek. Vous ne voulez pas le confondre avec des données précises actuelles.

Goal Seek est donc une fonctionnalité Excel utile, mais ce n’est pas si impressionnant. Intéressons-nous à un outil plus intéressant : le plugin Solver.

Que fait le solveur d’Excel ?

En bref, Solver est comme un Plusieurs versions de la recherche d’objectifsIl prend une variable cible et en ajuste de nombreuses autres jusqu’à ce que vous obteniez la réponse souhaitée.

Il peut résoudre des nombres maximaux, des nombres minimaux ou des nombres exacts.

Cela fonctionne avec des contraintes, donc si une variable ne peut pas être modifiée, ou ne peut être modifiée 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 facile.

Jetons un coup d’œil au chargement du complément Solver, puis passons à 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.

aller à Fichier > Options > Compléments. puis clique va proche Administration : compléments Excel.

Si ce menu déroulant affiche autre chose que « Excel Add-ins », vous devez le modifier :

Dans la fenêtre résultante, vous verrez quelques options.Assurez-vous que la case à côté de Plug-in de solveur est coché, puis cliquez sur d’accord.

tu verras maintenant solveur bouton dans analyser grouper Les données Étiqueter:

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

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

Comment utiliser le solveur dans Excel

Toute opération Solver comporte trois parties : une cible, une cellule variable et une contrainte. Nous allons parcourir chaque étape étape par étape.

1. Cliquez sur Données > SolveursVous 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 plug-in du solveur.)
2. Définissez votre cible de cellule et indiquez à Excel votre cible. La cible est située en haut de la fenêtre du Solveur et comporte deux parties : l’unité cible et le choix de maximiser, minimiser ou une valeur spécifique.si tu choisis maximumExcel ajustera vos variables pour obtenir autant de nombres que possible dans votre cellule cible. sensible Au contraire : le solveur minimisera le nombre d’objectifs. la valeur de Vous permet de spécifier un nombre spécifique que le Solveur recherche.
3. Sélectionnez les cellules variables qu’Excel peut modifier.La cellule variable est définie sur En changeant la cellule variable placer. Cliquez sur la flèche à côté du champ, puis cliquez et faites glisser pour sélectionner la cellule que le solveur doit utiliser.Notez que ce sont toutes les cellules Cela peut varier. Si vous ne souhaitez pas modifier une cellule, ne la sélectionnez pas.
4. Définissez des contraintes sur des variables multiples ou uniques. Enfin, regardons les contraintes. C’est là que Solver brille vraiment. Au lieu de changer n’importe quelle cellule variable en le nombre que vous voulez, vous pouvez spécifier des contraintes qui doivent être satisfaites. Voir la section ci-dessous sur la façon de définir des contraintes pour plus de détails.
5. Une fois toutes ces informations en place, cliquez sur résoudre obtenir votre réponse. Excel mettra à jour vos données pour inclure les nouvelles variables (c’est pourquoi nous vous recommandons de créer une copie des données en premier lieu).

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

Comment définir des contraintes dans le solveur

Vous pouvez indiquer à Excel qu’une variable doit être supérieure à 200. Lorsque vous essayez différentes valeurs de variable, Excel n’utilisera pas cette variable particulière en dessous de 201.

Pour ajouter des contraintes, cliquez sur Ajouter à à côté de la liste des contraintes. Vous obtiendrez une nouvelle fenêtre.choisir de référence de cellule champ et choisissez un opérateur.

Les opérateurs suivants sont disponibles :

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

tous différents Un peu déroutant.Il spécifie chaque cellule de la plage que vous sélectionnez référence de cellule Doit être un numéro différent. Mais il précise aussi qu’ils doivent être compris entre 1 et le nombre de cellules. Donc, si vous avez trois cellules, vous vous retrouverez avec les numéros 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 cellulaire. Par exemple, si vous souhaitez que six variables aient des valeurs supérieures à 10, vous pouvez toutes les sélectionner et indiquer au Solveur qu’elles doivent être supérieures ou égales à 11. Il n’est pas nécessaire d’ajouter des contraintes à chaque cellule.

Vous pouvez également utiliser les cases à cocher dans la fenêtre principale du Solveur pour vous assurer que toutes les valeurs pour lesquelles vous ne spécifiez pas de contraintes sont non négatives. Décochez cette case si vous souhaitez que la variable soit négative.

Exemple de solveur

Pour voir comment tout cela fonctionne, nous utiliserons le plugin Solver pour des calculs rapides. Voici les données avec lesquelles nous avons commencé :

Dans celui-ci, nous avons cinq emplois différents, chacun payant un taux différent. Nous avons également un travailleur théorique pour le nombre d’heures dans ces emplois dans une semaine donnée. Nous pouvons utiliser le plugin Solver pour savoir comment maximiser la compensation totale tout en gardant certaines variables dans certaines contraintes.

Voici les contraintes que nous utiliserons :

• pas de travail Probablement moins de quatre heures.
• Le devoir 2 doit être plus de huit heures.
• Le devoir 5 doit être moins de onze heures.
• Le nombre total d’heures de travail doit être égal à 40.

Il peut être utile d’écrire ces contraintes avant d’utiliser le solveur.

Voici comment nous l’avons configuré dans Solver :

Tout d’abord, veuillez noter J’ai créé une copie du formulaire Nous n’écraserons donc pas le fichier d’origine contenant nos heures de travail actuelles.

Deuxièmement, vu que les valeurs dans les contraintes supérieur à et inférieur à sont haut et bas que ce que j’ai mentionné ci-dessus. C’est parce qu’il n’y a pas d’option supérieure ou inférieure à. Uniquement supérieur ou égal à et inférieur ou égal à.

frappons résoudre Voyez ce qui se passe.

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

Pour conserver la nouvelle valeur, assurez-vous Conserver les solutions du solveur vérifié et frappé d’accord.

Toutefois, si vous souhaitez en savoir plus, vous pouvez sélectionner un rapport dans la partie droite de la fenêtre.Sélectionnez tous les rapports que vous souhaitez, indiquez à Excel si vous souhaitez les décrire (ce que je recommande), puis cliquez sur d’accord.

Ces rapports sont générés sur une nouvelle feuille de votre classeur et vous fournissent des informations sur le processus suivi par le complément Solveur pour obtenir vos réponses.

Dans notre cas, les rapports n’étaient pas très excitants et il n’y avait pas beaucoup d’informations intéressantes. Toutefois, si vous exécutez des équations Solver plus complexes, vous trouverez peut-être des informations de rapport utiles dans ces nouvelles feuilles de calcul.cliquez + Boutons sur le côté de n’importe quel rapport pour 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. Cependant, 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 non linéaire, Simplex LP et évolutionnaire. Excel fournit une explication simple du moment où chacun doit être utilisé. Une meilleure interprétation nécessite une certaine connaissance des statistiques et de la régression.

Pour régler d’autres paramètres, cliquez simplement sur Choix bouton. Vous pouvez indiquer l’optimalité entière d’Excel, définir des limites de temps de calcul (utiles pour les ensembles de données volumineux) et ajuster la façon dont GRG et les solveurs évolutifs effectuent les calculs.

Encore une fois, ne vous inquiétez pas si vous ne savez pas ce que cela signifie. Si vous souhaitez en savoir plus sur la méthode de solution à utiliser, Engineer Excel a un bon article expliquant tout cela. Si vous voulez une précision maximale, Evolutionary peut être un bon choix. Notez que cela prendra beaucoup de temps.

Recherche d’objectifs et solveur : faites 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 calculs de feuilles 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 à la puissance de calcul d’Excel.

Il s’agit juste de les mettre à l’aise. Plus vous les utilisez, plus ils deviennent utiles.

Utilisez-vous Goal Seek ou Solver dans vos feuilles de calcul ? Quels autres conseils pouvez-vous donner pour obtenir la meilleure réponse à partir de cela ? Partagez votre opinion dans les commentaires ci-dessous!

…