Mark Lucas
18 
5257
1278
Excel est très capable lorsque vous avez toutes les données dont vous avez besoin pour vos calculs.
Mais ça 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 vous montrerons comment. Lisez la suite pour un guide complet sur la résolution d'une seule cellule avec Goal Seek ou d'une équation plus compliquée avec Solver..
Comment utiliser la recherche d'objectif dans Excel
La recherche d'objectif est déjà intégrée à Excel. C'est sous le Les données onglet, dans le Analyse hypothétique menu:
Pour cet exemple, nous utiliserons un ensemble de nombres très simple. Nous avons trois quarts des chiffres de vente et un objectif annuel. Nous pouvons utiliser Goal Seek pour déterminer quels chiffres doivent être au T4 pour atteindre l'objectif.
Comme vous pouvez le constater, le total des ventes actuelles s’élève à 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'objectifs d'Excel nous dira.
Voici comment utiliser Goal Seek, étape par étape:
- Cliquez sur Données> Analyse d'hypothèses> Recherche d'un objectif. Vous verrez cette fenêtre:
- Mettre le “équivaut à” une partie de votre équation dans le Définir la cellule champ. C'est le nombre qu'Excel va essayer d'optimiser. Dans notre cas, il s’agit du total cumulé de nos chiffres de vente dans la cellule B5..
- Tapez votre valeur d'objectif dans le Évaluer champ. Nous recherchons un total de 250 000 unités vendues, nous allons donc mettre “250 000” dans ce champ.
- Indiquez à Excel quelle variable résoudre dans le En changeant de cellule champ. 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. Ça va ressembler à ça quand c'est prêt:
- Frappé D'accord à résoudre pour votre objectif. Quand ça a l'air bien, il suffit de frapper D'accord. Excel vous laissera savoir quand Goal Seek aura trouvé une solution.
- Cliquez sur D'accord encore une fois 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 135 294 unités. Bien entendu, nous aurions pu le constater 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 que 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'objectif. Vous ne voulez pas le confondre pour des données actuelles et précises.
Ainsi, Goal Seek est une fonctionnalité utile dans Excel. 16 Formules Excel qui vous aideront à résoudre les problèmes réels. 16 Formules Excel qui vous aideront à résoudre les problèmes réels. Le bon outil représente 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 formules Excel clés et montrons comment les utiliser. , mais ce n’est pas si impressionnant. Jetons un coup d'œil à un outil beaucoup plus intéressant: le complément Solver.
Que fait le solveur d'Excel?
En bref, le solveur est comme un version multivariée de Goal Seek. Il faut 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 pour une valeur maximale d'un nombre, une valeur minimale d'un nombre ou un nombre exact.
Et cela fonctionne avec des contraintes. Ainsi, 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 passons à l'utilisation du solveur dans Excel 2016.
Comment charger le complément de solveur
Excel n'a pas de solveur par défaut. C'est un complément, donc, comme d'autres fonctionnalités puissantes d'Excel. Power Up Excel avec 10 compléments pour traiter, analyser et visualiser des données comme un pro Power Up Excel avec 10 compléments 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 a créé une application Excel pour cela. Voici une sélection. , vous devez le charger en premier. Heureusement, il est déjà sur votre ordinateur.
Se diriger vers Fichier> Options> Compléments. Puis cliquez sur Aller à côté de Gérer: Compléments Excel.
Si cette liste indique autre chose que “Compléments Excel,” vous devrez le changer:
Dans la fenêtre résultante, vous verrez quelques options. Assurez-vous que la case à côté de Add-In de solveur est cochée, et frappe D'accord.
Vous verrez maintenant le Solveur bouton dans le Une analyse groupe de Les données languette:
Si vous avez déjà utilisé l'outil d'analyse des données. Procédure d'analyse des données de base dans Excel Procédure d'analyse des données de base dans Excel Excel n'est pas destiné à l'analyse de données, mais il peut néanmoins gérer des statistiques. Nous allons vous montrer comment utiliser le complément Data Analysis Toolpak pour générer des statistiques Excel. , vous verrez le bouton d’analyse de données. Sinon, le solveur apparaîtra tout seul.
Maintenant que vous avez chargé le complément, voyons comment l'utiliser..
Comment utiliser le solveur dans Excel
Toute action du solveur comporte trois parties: 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, voir la section précédente sur la façon de charger le complément du solveur.)
- Définissez votre objectif de cellule et indiquez votre objectif à Excel. L'objectif se situe en haut de la fenêtre du solveur et comporte deux parties: la cellule objectif et le 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 nombre spécifique que Solver doit rechercher. - Choisissez les cellules variables que Excel peut modifier. Les cellules variables sont définies avec le En changeant 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 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.
- Définissez des contraintes sur des variables multiples ou individuelles. Enfin, nous arrivons aux contraintes. C’est là que le solveur est vraiment puissant. Au lieu de remplacer les cellules de la variable par le nombre souhaité, vous pouvez spécifier les contraintes à respecter. Pour plus de détails, voir la section sur la définition des contraintes ci-dessous..
- Une fois que toutes ces informations sont 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 pouvez indiquer à Excel qu'une variable doit être supérieure à 200. Lorsque vous essayez différentes valeurs de variable, Excel ne passe pas sous 201 avec cette variable particulière..
Pour ajouter une contrainte, cliquez sur le bouton Ajouter bouton en regard de la liste des contraintes. Vous aurez une nouvelle fenêtre. Sélectionnez la ou les cellules à contraindre dans la liste. 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 à)
- 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 sélectionnée 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 allez vous retrouver avec les nombres 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 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 indiquer à Solver 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 de la fenêtre principale du solveur pour vous assurer que toutes les valeurs pour lesquelles vous n'avez pas spécifié de contrainte 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 allons utiliser le complément Solver pour effectuer un calcul rapide. Voici les données avec lesquelles nous commençons:
Nous avons cinq emplois différents, chacun payant un taux différent. Nous avons également le nombre d'heures travaillées par un ouvrier théorique à chacun de ces emplois au cours d'une semaine donnée. Nous pouvons utiliser le complément Solver pour savoir comment maximiser le salaire total tout en maintenant certaines variables dans les limites de certaines contraintes..
Voici les contraintes que nous allons utiliser:
- Pas d'emplois peut tomber en dessous de quatre heures.
- Job 2 doit être plus de huit heures.
- Job 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 de la sorte avant d’utiliser Solver.
Voici comment nous avons configuré cela dans le solveur:
Tout d'abord, notez que J'ai créé une copie de la table de sorte que nous n'écrasons pas l'original, qui contient nos heures de travail actuelles.
Et deuxièmement, voyez que les valeurs dans les contraintes supérieure à et inférieure à sont un plus haut ou plus bas que ce que j'ai mentionné ci-dessus. C'est parce qu'il n'y a pas plus grand que ou moins que des options. Il n'y a que supérieur-ou-égal à et inférieur que-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, notre bénéfice a augmenté de 130 $. Et toutes les contraintes ont été respectées.
Pour conserver les nouvelles valeurs, assurez-vous que Garder la solution de solveur est vérifié et frappé D'accord.
Si vous souhaitez plus d'informations, vous pouvez toutefois sélectionner un rapport dans la partie droite de la fenêtre. Sélectionnez tous les rapports souhaités, indiquez à Excel si vous souhaitez les décrire (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 suivi par le complément Solver pour obtenir votre réponse..
Dans notre cas, les rapports ne sont pas très excitants et il n’ya pas beaucoup d’informations intéressantes. Mais si vous utilisez une équation de solveur plus complexe, vous pourrez trouver des informations utiles sur la création de rapports dans ces nouvelles feuilles de calcul. Il suffit de cliquer sur le + bouton sur le côté de tout rapport pour obtenir plus d'informations:
Options avancées du solveur
Si vous ne connaissez pas bien les statistiques, vous pouvez ignorer les options avancées de Solver et simplement l'exécuter tel quel. Mais si vous utilisez 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 Evolutionnaire. Excel fournit une explication simple concernant le moment où vous devriez utiliser chacun d'eux. Une meilleure explication nécessite des connaissances en statistiques Apprendre Statistiques gratuitement avec ces 6 ressources Apprendre Statistiques gratuitement avec ces 6 Ressources Les statistiques ont une réputation difficile à comprendre. Mais apprendre de la bonne ressource vous aidera à comprendre les résultats de l’enquête, les rapports d’élection et vos travaux de statistique en un rien de temps. et régression.
Pour ajuster des paramètres supplémentaires, appuyez simplement sur la Les options bouton. Vous pouvez expliquer à Excel l’optimalité des nombres entiers, définir des contraintes de temps de calcul (utile pour les jeux de données volumineux) et ajuster la façon dont les méthodes de résolution GRG et Évolutionnales effectuent leurs calculs..
Encore une fois, si vous ne savez pas ce que cela signifie, ne vous inquiétez pas. Si vous souhaitez en savoir plus sur la méthode de résolution à utiliser, Engineer Excel propose un bon article qui le décrit pour vous. Si vous voulez une précision maximale, Evolutionary est probablement une bonne solution. Sachez simplement que cela prendra beaucoup de temps.
Objectif à la recherche et au solveur: Excel au prochain niveau
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. Solver ajoute une puissance considérable aux capacités de calcul d'Excel. Comment calculer des statistiques de base dans Excel: Guide du débutant Comment calculer des statistiques de base dans Excel: Guide du débutant faire des statistiques! Vous pouvez calculer des pourcentages, des moyennes, des écarts types, des erreurs types et des tests t de Student. .
C'est juste une question d'être à 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 donner pour obtenir les meilleures réponses? Partagez votre opinion dans les commentaires ci-dessous!