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 s'il 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.
Goal Seek est déjà intégré à Excel. C'est sous les Données onglet, dans l'Analyse de simulation menus :
Pour cet exemple, nous utiliserons un ensemble de nombres très simple. Nous avons trois quarts du chiffre d'affaires et un objectif annuel. Nous pouvons utiliser la recherche d'objectifs pour déterminer quels doivent être les chiffres au quatrième trimestre pour atteindre l'objectif.
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'objectifs d'Excel nous le dira.
Voici comment utiliser Goal Seek, étape par étape :
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 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 d'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 Goal Seek. Vous ne voulez pas le confondre avec des données actuelles et précises.
Goal Seek est donc une fonctionnalité Excel utile, mais ce n'est pas si impressionnant. Intéressons-nous à un outil bien plus intéressant :le complément Solveur.
En bref, Solver est comme une 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 dans les limites 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 Solveur, puis passons à l'utilisation du Solveur dans Excel 2016.
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.
Allez dans Fichier > Options > Compléments . Cliquez ensuite sur Aller à côté de Gérer :Compléments Excel .
Si ce menu déroulant indique autre chose que "Excel Add-Ins", vous devrez le modifier :
Dans la fenêtre résultante, vous verrez quelques options. Assurez-vous que la case à côté de Solver Add-In est cochée, puis cliquez sur OK .
Vous verrez maintenant le Solveur bouton dans l'Analyse groupe des Données onglet :
Si vous avez déjà utilisé le Data Analysis Toolpak, vous verrez le bouton Data Analysis. Sinon, le Solveur apparaîtra tout seul.
Maintenant que vous avez chargé le complément, voyons comment l'utiliser.
Toute action Solver comporte trois parties :l'objectif, les cellules variables et les contraintes. Nous allons parcourir chacune des étapes.
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 à rechercher par le Solveur. 
Vous pouvez également générer des rapports, que nous examinerons brièvement dans notre exemple de Solveur ci-dessous.
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 Ajouter à côté de la liste des contraintes. Vous obtiendrez une nouvelle fenêtre. Sélectionnez la cellule (ou les cellules) à contraindre dans la Référence de cellule champ, puis choisissez un opérateur.
Voici les opérateurs disponibles :
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 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 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 indiquer au Solveur 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 sont non négatives. Si vous voulez que vos variables deviennent négatives, décochez cette case.
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 :
Dans celui-ci, nous 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 savoir comment maximiser le salaire total tout en maintenant certaines variables dans certaines contraintes.
Voici les contraintes que nous utiliserons :
Il peut être utile d'écrire vos contraintes comme ceci avant d'utiliser le Solveur.
Voici comment nous le configurerions dans le Solveur :
Tout d'abord, notez que j'ai créé une copie du tableau afin de ne pas écraser l'original, qui contient nos heures de travail actuelles.
Et deuxièmement, vérifiez que les valeurs des 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 à.
Appuyons sur Résoudre et voyez 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.
Pour conserver les nouvelles valeurs, assurez-vous de Conserver la solution du solveur est coché et appuyez sur OK .
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 souhaitez, indiquez à Excel si vous souhaitez qu'ils soient décrits (je le recommande) et appuyez sur OK .
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 Solveur 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. 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 + bouton sur le côté de n'importe quel rapport pour obtenir plus d'informations :
Si vous ne connaissez pas grand-chose aux statistiques, vous pouvez ignorer les options avancées de Solver et simplement 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 non linéaire, Simplex LP et évolutionnaire. 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 les Options bouton. Vous pouvez indiquer à Excel l'optimalité des nombres entiers, 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 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.
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 puissance aux capacités de calcul d'Excel.
C'est juste une question d'être à l'aise avec eux. Plus vous les utilisez, plus ils deviendront utiles.
Utilisez-vous Goal Seek ou Solver dans vos feuilles de calcul ? Quels autres conseils pouvez-vous donner pour en tirer les meilleures réponses ? Partagez vos impressions dans les commentaires ci-dessous !
