Goal Seek és una de les eines d'anàlisi de què passa si d'Excel que us ajuda a trobar el valor d'entrada correcte d'una fórmula per obtenir la sortida desitjada. Mostra com un valor d'una fórmula afecta un altre. En altres paraules, si teniu un valor objectiu que voleu assolir, podeu utilitzar la cerca d'objectius per trobar el valor d'entrada adequat per aconseguir-lo.
Per exemple, suposem que heu obtingut un total de 75 punts en una assignatura i necessiteu almenys un 90 per obtenir una nota S en aquesta assignatura. Afortunadament, teniu una darrera prova que us pot ajudar a augmentar la vostra puntuació mitjana. Podeu utilitzar Goal Seek per esbrinar quanta puntuació necessiteu en aquesta prova final per obtenir una nota S.
Goal Seek utilitza un mètode d'assaig i error per fer un seguiment del problema introduint conjectures fins que arribi al resultat correcte. Goal Seek pot trobar el millor valor d'entrada per a la fórmula en qüestió de segons que hauria trigat molt de temps a esbrinar-se manualment. En aquest article, us mostrarem com utilitzar l'eina de recerca d'objectius a Excel amb alguns exemples.
Components de la funció de recerca d'objectius
La funció de recerca d'objectius consta de tres paràmetres, a saber:
- Estableix cel·la – Aquesta és la cel·la on s'introdueix la fórmula. Especifica la cel·la en la qual voleu la sortida desitjada.
- Valorar – Aquest és el valor objectiu/desitjat que voleu com a resultat de l'operació de recerca d'objectius.
- En canviar de cèl·lula – Això especifica la cel·la el valor de la qual s'ha d'ajustar per obtenir la sortida desitjada.
Com utilitzar la cerca d'objectius a Excel: exemple 1
Per demostrar com funciona en un exemple senzill, imagineu que teniu una parada de fruita. A la captura de pantalla següent, la cel·la B11 (a sota) mostra quant us ha costat comprar fruites per a la vostra parada i la cel·la B12 mostra els vostres ingressos totals per vendre aquestes fruites. I la cel·la B13 mostra el percentatge del vostre benefici (20%).
Si voleu augmentar els vostres beneficis al "30%", però no podeu augmentar la vostra inversió, per tant, heu d'augmentar els vostres ingressos per obtenir beneficis. Però a quant? La recerca d'objectius us ajudarà a trobar-la.
Feu servir la fórmula següent a la cel·la B13 per calcular el percentatge de beneficis:
=(B12-B11)/B12Ara, voleu calcular el marge de benefici de manera que el vostre percentatge de benefici sigui del 30%. Podeu fer-ho amb Goal Seek a Excel.
El primer que cal fer és seleccionar la cel·la el valor de la qual voleu ajustar. Cada vegada que utilitzeu la cerca d'objectius, heu de seleccionar una cel·la que contingui una fórmula o funció. En el nostre cas, seleccionarem la cel·la B13 perquè conté la fórmula per calcular el percentatge.
A continuació, aneu a la pestanya "Dades", feu clic al botó "Què passaria si Anàlisi" al grup Previsió i seleccioneu "Busca d'objectius".
El quadre de diàleg de cerca d'objectius apareixerà amb 3 camps:
- Estableix cel·la – Introduïu la referència de cel·la que conté la fórmula (B13). Aquesta és la cel·la que tindrà la sortida desitjada.
- Valorar – Introduïu el resultat desitjat que intenteu aconseguir (30%).
- En canviar de cèl·lula – Inseriu la referència de cel·la per al valor d'entrada que voleu canviar (B12) per tal d'arribar al resultat desitjat. Simplement feu clic a la cel·la o introduïu manualment la referència de la cel·la. Quan seleccioneu la cel·la, Excel afegirà el signe "$" abans de la lletra de la columna i el número de fila per convertir-la en una cel·la absoluta.
Quan hàgiu acabat, feu clic a "D'acord" per provar-ho.
A continuació, apareixerà un quadre de diàleg "Estat de cerca d'objectius" i us informarà si ha trobat alguna solució com es mostra a continuació. Si s'ha trobat una solució, el valor d'entrada a la "cel·la canviant (B12)" s'ajustarà a un nou valor. Això és el que volem. Així, en aquest exemple, l'anàlisi va determinar que, per assolir el vostre objectiu de beneficis del 30%, heu d'aconseguir uns ingressos de 1.635,5 dòlars (B12).
Feu clic a "D'acord" i Excel canviarà els valors de la cel·la o feu clic a "Cancel·la" per descartar la solució i restaurar el valor original.
El valor d'entrada (1635,5) a la cel·la B12 és el que vam descobrir mitjançant la cerca d'objectius per assolir el nostre objectiu (30%).
Coses a recordar quan utilitzeu Goal Seek
- El conjunt de cel·les sempre ha de contenir una fórmula que depèn de la cel·la "En canviar la cel·la".
- Només podeu utilitzar la cerca d'objectius en un sol valor d'entrada de cel·la alhora
- La "Cel·la canviant" ha de contenir un valor i no una fórmula.
- Si Goal Seek no pot trobar la solució correcta, mostra el valor més proper que pot produir i us indica que el missatge "Goal-Seeking pot no haver trobat una solució".
Què fer quan la recerca d'objectius d'Excel no funciona
Tanmateix, si esteu segur que hi ha una solució, hi ha algunes coses que podeu provar per solucionar aquest problema.
Comproveu els paràmetres i els valors de la cerca d'objectius
Hi ha dues coses que hauríeu de comprovar: primer, comproveu si el paràmetre "Estableix cel·la" fa referència a la cel·la de la fórmula. En segon lloc, assegureu-vos que la cel·la de fórmula (cel·la Set) depèn, directament o indirectament, de la cel·la canviant.
Ajust de la configuració d'iteració
A la configuració d'Excel, podeu canviar el nombre d'intents possibles que pot fer Excel per trobar la solució adequada, així com la seva precisió.
Per canviar la configuració del càlcul d'iteració, feu clic a "Fitxer" a la llista de pestanyes. A continuació, feu clic a "Opcions" a la part inferior.
A la finestra Opcions d'Excel, feu clic a "Fórmules" al panell esquerre.
A la secció "Opcions de càlcul", canvieu aquesta configuració:
- Màxim d'iteracions: indica el nombre de solucions possibles que calcularà excel; com més gran sigui el nombre, més iteracions pot realitzar. Per exemple, si configureu "Iteracions màximes" a 150, Excel provarà 150 solucions possibles.
- Canvi màxim: indica la precisió dels resultats; el nombre més petit dóna més precisió. Per exemple, si el valor de la cel·la d'entrada és igual a '0' però Goal Seek deixa de calcular-se a '0,001', canviar-ho a '0,0001' hauria de resoldre el problema.
Augmenteu el valor "Màxim d'iteracions" si voleu que Excel proveu més solucions possibles i reduïu el valor de "Canvi màxim" si voleu un resultat més precís.
La captura de pantalla següent mostra el valor predeterminat:
Sense referències circulars
Quan una fórmula fa referència a la seva pròpia cel·la, s'anomena referència circular. Si voleu que Excel Goal Seek funcioni correctament, les fórmules no haurien d'utilitzar referència circular.
Exemple 2 de recerca d'objectius d'Excel
Suposem que estàs demanant diners en préstec de "25.000 $" a algú. Et presten els diners a un tipus d'interès del 7% al mes durant un període de 20 mesos, cosa que suposa el reemborsament de '1327 $' al mes. Però només us podeu permetre el luxe de pagar "1.000 dòlars" al mes durant 20 mesos amb un interès del 7%. Goal Seek us pot ajudar a trobar l'import del préstec que produeix un pagament mensual (EMI) de "1000 $".
Introduïu les tres variables d'entrada que necessitareu per calcular el vostre càlcul PMT, és a dir, el tipus d'interès del 7%, el termini de 20 mesos i l'import principal de 25.000 dòlars.
Introduïu la següent fórmula PMT a la cel·la B5 que us donarà un import EMI de 1.327,97 $.
La sintaxi de la funció PMT:
=PMT(Tipus d'interès/12, termini, principal)La fórmula:
=PMT(B3/12;B4;-B2)
Seleccioneu la cel·la B5 (cel·la de fórmula) i aneu a Dades -> Què passa si Anàlisi -> Cerca d'objectius.
Utilitzeu aquests paràmetres a la finestra "Busca d'objectius":
- Estableix cel·la – B5 (la cel·la que conté la fórmula que calcula l'EMI)
- Valorar – 1000 (el resultat/objectiu de la fórmula que busqueu)
- En canviar de cèl·lula – B2 (és l'import del préstec que voleu canviar per assolir el valor objectiu)
A continuació, premeu "D'acord" per mantenir la solució trobada o "Cancel·la" per descartar-la.
L'anàlisi us indica que la quantitat màxima de diners que podeu demanar en préstec és de 18825 $ si voleu superar el vostre pressupost.
Així és com s'utilitza Goal Seek a Excel.