Excel What-If Analysis offre une fonction de recherche d'objectifs. Vous pouvez utiliser la recherche d'objectifs pour trouver les valeurs d'entrée correctes pour conduire à un résultat donné. Si vous avez besoin de revenir sur une réponse, Goal Seek est la solution. Apprenez à utiliser la recherche d'objectifs dans Excel.
Vous souvenez-vous dans Insérer des fonctions et des paiements de prêt, lorsque je vous ai montré comment calculer un paiement de prêt à l'aide de la boîte de dialogue Insérer une fonction? Dans cet exemple, le paiement mensuel du prêt était de 493,54 $. Je ne l'ai pas mentionné à l'époque, mais mon budget mensuel pour les paiements de voiture est de 425 $.
Si vous avez à peu près le même âge que moi et que vous passiez vos étés à regarder la télévision, vous vous souviendrez peut-être d'un jeu télévisé fou appelé The Price Is Right. Bien avant Drew Carey, le vénérable Bob Barker distribuait des prix en utilisant une variété de jeux. Celui dont je me souviens est le jeu supérieur / inférieur. Bob vous donnerait la voiture si vous pouviez indiquer le prix de la voiture. Vous devineriez. Bob criait plus haut ou plus bas. Je pense que vous avez eu 20 secondes pour affiner vos estimations au prix exact.
Souvent, j'ai l'impression que ces étés à regarder Bob Barker m'ont appris à trouver des réponses dans Excel. Vous êtes-vous déjà retrouvé à brancher successivement des valeurs plus élevées et plus basses dans une cellule d'entrée, dans l'espoir d'arriver à une certaine réponse?
Un outil intégré à Excel qui effectue exactement cet ensemble d'étapes. Sous l'onglet Données, dans le groupe Outils de données, recherchez la liste déroulante Analyse des hypothèses et choisissez Recherche d'objectif.
Ci-dessous, vous essayez de définir le paiement dans B5 à 425 $ en changeant la cellule B1
La recherche d'objectifs trouve la bonne réponse en une seconde.
Notez que la formule en B5 reste intacte. La seule chose qui change est la valeur d'entrée saisie dans B1.
De plus, avec Goal Seek, vous êtes libre d'expérimenter la modification d'autres cellules d'entrée. Vous pouvez toujours obtenir le remboursement du prêt de 425 $ et la voiture de 25 995 $ si votre banquier vous offre un prêt de 71,3379 mois!
Merci à Jon Wittwer de Vertex42.com et à @BizNetSoftware pour avoir suggéré Goal Seek.
Regarder la vidéo
- Comment revenir à une réponse dans Excel
- Rechercher une fonction inverse pour PMT en utilisant fx et en recherchant PMT
- Essayez la méthode Price is Right - plus haut, plus bas, plus haut, plus bas
- Goal Seek est un Bob Barker automatisé
- Goal Seek fonctionne même avec le changement de terme
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2033 - Utilisation de la recherche d'objectifs pour revenir à une réponse
Je fais un podcast sur tout ce livre, cliquez sur ce «i» dans le coin supérieur droit pour accéder à la playlist!
Salut, bon retour sur le netcast, je suis Bill Jelen. Eh bien, nous revisitons le modèle que nous construisons dans l'épisode 2022, c'est celui où nous avons calculé un paiement de prêt, et nous avons utilisé le bouton Insérer une fonction pour comprendre la fonction PMT. Et mon problème est que c'était une voiture et que je ne voulais payer que 425 $ par mois, alors quelle valeur principale nous amènera à 425 $ par mois? Eh bien, il y a trois façons de résoudre ce problème, d'accord. La méthode n ° 1 est l'algèbre! Non, non, je suis désolé, avec tout le respect que je dois à tous les professeurs de mathématiques du secondaire, l'algèbre est quelque chose que j'ai l'intention de ne plus jamais utiliser après le lycée. Maintenant, celui-ci n'est pas dans le livre, mais c'est en fait un moyen de le résoudre. Changeons le modèle et essayons de trouver la fonction inverse, donc au lieu d'utiliser PMT, trouvons une fonction qui nous donnera le principal.Et si nous retournons dans Insérer une fonction, et recherchons réellement ce que nous recherchons, PMT, le deuxième élément est l'inverse, non? Et il est possible d'utiliser la fonction PV, donc taux / 12, Nper de nos périodes est de 60, et le paiement que je veux faire est négatif, car c'est de l'argent qui sort de la banque, et j'obtiens la réponse réelle là. Bien que je ferme les yeux, j'oublie cela, car personne ne se donne la peine d'utiliser l'algèbre ou la fonction inverse.de cette façon, j'oublie cela, car personne ne se donne la peine d'utiliser l'algèbre ou la fonction inverse.de cette façon, j'oublie cela, car personne ne se donne la peine d'utiliser l'algèbre ou la fonction inverse.
Voilà ce que nous faisons, nous jouons à un jeu que je connaissais très bien au collège, l'été est au collège! Je serais à la maison pendant la journée, je regarde une émission à la télévision américaine intitulée «The Price is Right», où un gars du nom de Bob Barker distribuait toutes sortes de prix fabuleux. Et vous souvenez-vous du jeu où il donnait une voiture si vous pouviez juste dire le prix de la voiture? Vous devineriez, et il criait plus haut ou plus bas! D'accord, donc ma première estimation était 25995, Bob dit Lower, donc je vais 20000, Bob dit Higher, maintenant voyez, je suis fan de ce jeu, je suis un aficionado de ce jeu. Bien sûr, ce que vous faites alors, allez juste au milieu, alors allez en quelque sorte à 23000 et Bob va dire Lower, puis vous le divisez à nouveau 21,5, et ainsi de suite. Vous voilà, Winner, mais c'est un chemin lent et lent, mais vous comprenez quoi 'se passe ici. Bien, si jamais vous regardez "Le prix est juste" ou si vous juste - nous devinons progressivement de plus en plus bas jusqu'à ce que nous obtenions le bon montant. Et merci à Chad Thomas pour son illustration de Bob Barker ici, vous savez, Bob Barker a pris sa retraite, il a été remplacé dans le jeu Par Drew Carey, mais il s'avère que Bob Barker a encore un travail, l'équipe d'Excel l'a embauché , il est de retour ici sur l'onglet Données, analyse What-If, et il vit dans la commande Goal Seek!l'équipe Excel l'a embauché, il est de retour ici sur l'onglet Données, analyse What-If, et il vit dans la commande Goal Seek!l'équipe Excel l'a embauché, il est de retour ici sur l'onglet Données, analyse What-If, et il vit dans la commande Goal Seek!
D'accord, nous allons donc définir la cellule B5 à 425 $ en modifiant la cellule B1, et lorsque je clique sur OK, toute la routine qui s'est produite sur la dernière feuille de calcul va se produire en moins d'une seconde. Avez-vous vu cela, 22384.9, et ils sont en fait beaucoup plus précis que moi, je suis arrivé à 22385, c'est en fait 22384.93425. Ce qui, au fait, est exactement la même réponse que celle que nous avons obtenue ici, mais beaucoup plus rapidement, et sans jamais avoir à essayer de comprendre la fonction inverse et de changer le modèle. C'est un excellent moyen de revenir dans une réponse pour trouver la bonne cellule d'entrée, et le modèle fonctionne toujours, si nous voulons venir ici et changer les choses jusqu'à 72 mois, vous verrez qu'il calculera. Maintenant, dans ce cas, nous essayions de déterminer le principal, le bon prix de la voiture, pour nous amener à 425 $,mais vous pouvez faire toutes sortes de choses folles.
Nous pouvons aller ici à l'analyse des hypothèses, à la recherche d'objectifs et dire: "Très bien, je veux payer 425 dollars par mois pour cette voiture, mais je veux le faire en changeant, disons, le terme." Cliquez sur OK, et ils constatent que vous avez besoin d'un prêt de 71,3379 mois pour obtenir cette réponse, alors, vous savez, soyez créatif, en ce qui concerne la cellule d'entrée que vous allez modifier. La recherche d'objectifs n'est que l'un des conseils de ce livre, de nombreux conseils, je vais diffuser le livre en entier, il faudra au moins le reste du mois d'octobre pour y arriver. Mais vous pouvez commander le livre entier maintenant, 25 $ en version imprimée, 10 $ un livre électronique, cliquez sur ce «i» dans le coin supérieur droit. D'accord, alors comment revenir à une réponse dans Excel? Premier choix: l'algèbre, je rejette ça! Deuxième choix: trouvez la fonction inverse de notre paiement en utilisant le bouton Fx juste là, recherchez le paiement, et la fonction inverse pourrait apparaître,certaines fonctions n'ont pas d'inverse. Ou la méthode «Le prix est juste», plus haut plus bas plus bas, mais la recherche d'objectifs est un moyen automatisé de faire la méthode «Le prix est juste», fonctionne même avec le changement de terme.
Très bien hey, je tiens à vous remercier d'être passé, nous vous reverrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2033.xlsx
