Com s'utilitza la cerca d'objectius a Microsoft Excel per als escenaris de què passa?

Com s'utilitza la cerca d'objectius a Microsoft Excel per als escenaris de què passa?

L’anàlisi Què passa si l’Excel us permet veure com canviar una cel·la pot afectar la sortida d’una fórmula. Podeu utilitzar les eines d'Excel per a aquest propòsit per calcular l'efecte de canviar el valor d'una cel·la en una fórmula.





Excel té tres tipus d’eines d’anàlisi del que passa: gestor d’escenaris, cerca d’objectius i taula de dades. Amb la cerca d’objectius, podeu determinar quina entrada necessiteu per convertir la fórmula cap enrere a una sortida determinada.



La funció de cerca d’objectius a Excel és una prova i error, de manera que si no produeix el que voleu, Excel treballa millorant aquest valor fins que no ho faci.





Què són les fórmules de cerca d'objectius d'Excel?

Objectiu Seek in Excel es divideix bàsicament en tres components principals:

  1. Estableix la cel·la : La cel·la que voleu utilitzar com a objectiu.
  2. Valorar : El valor que voleu com a objectiu.
  3. Canviant de cel·la : La cel·la que voleu canviar per assolir el valor de l'objectiu.

Amb aquests tres paràmetres configurats, Excel intentarà millorar el valor de la cel·la que heu establert Canviant de cel·la fins a la cel·la que heu establert Estableix la cel·la assoleix el valor que heu determinat Valorar .



Si tot això sembla confús, els exemples següents us donaran una bona idea del funcionament de la cerca d’objectius.

Exemple 1 de cerca d'objectius

Per exemple, suposem que teniu dues cel·les (A & B) i una tercera cel·la que calcula la mitjana d’aquestes dues.



Ara suposem que teniu un valor estàtic per A i voleu augmentar el nivell mitjà canviant el valor per B. Mitjançant la cerca d'objectius, podeu calcular quin valor de B produirà la mitjana que desitgeu.

Relacionat: Consells per estalviar temps a Microsoft Excel



la millor aplicació de GPS per a Android 2014

Com s'utilitza la cerca d'objectius a Excel

  1. A Excel, feu clic a la cel·la C1 .
  2. A la barra de fórmules, introduïu el codi següent: =AVERAGE(A1:B1) Aquesta fórmula farà que Excel calculi la mitjana dels valors de les cel·les A1 i B1 i la sortida que a la cel·la C1 . Podeu assegurar-vos que la vostra fórmula funcioni introduint números a A1 i B1. C1 hauria de mostrar la seva mitjana.
  3. Per a aquest exemple, canvieu el valor del fitxer A1 cel·la a 16 .
  4. Seleccioneu la cel·la C1 , i després des de la cinta, aneu a Dades fitxa.
  5. Sota la Pestanya Dades , seleccioneu Anàlisi de què passa? i llavors Cercar objectius . Això obrirà la finestra de cerca d'objectius.
  6. A la finestra Cerca d'objectius, configureu el fitxer Estableix la cel·la a C1 . Aquesta serà la vostra cel·la objectiu. Si heu ressaltat C1 abans d’obrir la finestra de cerca d’objectius, s’establirà automàticament Estableix la cel·la .
  7. En Valorar , inseriu el valor de l'objectiu que vulgueu. Per a aquest exemple, volem que la nostra mitjana sigui 26, de manera que el valor de l'objectiu també serà 26.
  8. Finalment, al fitxer Canviant de cel·la , seleccioneu la cel·la que vulgueu canviar per assolir l'objectiu. En aquest exemple, aquesta serà una cel·la B2 .
  9. Feu clic a D'acord que l'objectiu Goal Seek funcioni amb la seva màgia.

Un cop feu clic a D'acord, apareixerà un diàleg que us informarà que Goal Seek ha trobat una solució.

El valor de la cel·la A1 canviarà a la solució, i això substituirà les dades anteriors. És una bona idea executar Goal Seek en una còpia del full de dades per evitar la pèrdua de les dades originals.

Relacionat: Fórmules boges d'Excel que fan coses increïbles

Exemple 2 de cerca d'objectius

La cerca d’objectius pot ser útil quan l’utilitzeu en escenaris de la vida real. Tot i això, per utilitzar l'objectiu de cerca al màxim de tot, haureu de tenir una fórmula adequada per començar.

Per a aquest exemple, prenem un petit cas bancari. Suposem que teniu un compte bancari que us proporciona un interès anual del 4% sobre els diners que teniu al vostre compte.

Mitjançant l’anàlisi de què passa si l’objectiu i la cerca d’objectius, podeu calcular quants diners heu de tenir al vostre compte per obtenir una determinada quantitat d’interessos mensuals.

Per a aquest exemple, suposem que volem obtenir 350 $ mensuals amb pagaments d’interessos. A continuació s’explica com podeu calcular-ho:

com es pot reduir la brillantor de Windows 10
  1. A la cel·la A1 tipus balanç .
  2. A la cel·la A2 tipus Tarifa anual .
  3. A la cel·la A4 tipus Benefici mensual .
  4. A la cel·la B2 tipus 4% .
  5. Seleccioneu la cel·la B4 i a la barra de fórmules, introduïu la fórmula següent: =B1*B2/12 El benefici mensual serà igual al saldo del compte multiplicat per la taxa anual i després dividit pel nombre de mesos d’un any, dotze mesos.
  6. Aneu al Dades , feu clic a Anàlisi de què passa? i després seleccioneu Cercar objectius .
  7. A la Cercar objectius finestra, escriviu B4 dins Estableix la cel·la .
  8. Escriviu 350 al fitxer Valorar cel·la. (Aquest és el benefici mensual que voleu obtenir)
  9. Tipus B1 dins Canviant de cel·la . (Això canviarà el saldo a un valor que generarà 350 dòlars mensuals)
  10. Feu clic a D'acord . El Estat de la cerca d'objectius apareixerà el diàleg.

Al quadre de diàleg Estat de la cerca d’objectius, veureu que la cerca d’objectius ha trobat una solució a la cel·la B4. A la cel·la B1, podeu veure la solució, que hauria de ser de 105.000.

Requisits de cerca d'objectius

Amb la cerca d’objectius, podeu veure que només pot canviar una cel·la per assolir el valor de l’objectiu. Això vol dir que Goal Seek no pot resoldre ni arribar a una solució si té més d’una variable.

A Excel, encara podeu resoldre més d’una variable, però haureu d’utilitzar una altra eina anomenada Solver. Podeu llegir més sobre Solver d'Excel llegint el nostre article a Com s'utilitza Solver d'Excel .

Limitacions de cerca d'objectius

Una fórmula de cerca d'objectius utilitza el procés de prova i millora per assolir el valor òptim, que pot plantejar alguns problemes quan la fórmula produeix un valor no definit. A continuació s’explica com podeu provar-ho vosaltres mateixos:

  1. A la cel·la A1 , escriviu 3 .
  2. Selecciona una cel·la C1 , a continuació, a la barra de fórmules introduïu la fórmula següent: =1/(A1-5) Aquesta fórmula es dividirà una per A1-5 i, si A1-5 és 0, el resultat serà un valor no definit.
  3. Aneu al Dades pestanya, feu clic a Anàlisi de què passa? i després seleccioneu Cercar objectius .
  4. En Estableix la cel·la , escriviu C1 .
  5. En Valorar , escriviu 1 .
  6. Finalment, a Canviant de cel·la tipus A1 . (Això canviarà el valor A1 per arribar a 1 a C1)
  7. Feu clic a D'acord .

El quadre de diàleg Estat de la cerca d’objectius us indicarà que potser no ha trobat cap solució i podeu comprovar que el valor A1 estigui lluny.

Ara, la solució a aquest objectiu de cerca seria simplement tenir 6 a A1. Això donarà 1/1, que és igual a 1. Però en un moment determinat durant el procés de prova i millora, Excel prova 5 en A1, que dóna 1/0, que no està definit, i matarà el procés.

Una manera d’eludir aquest problema de cerca d’objectius és tenir un valor inicial diferent, que pugui eludir allò no definit en el procés de prova i millora.

Com a exemple, si canvieu A1 per qualsevol nombre superior a 5 i repeteix els mateixos passos a la cerca d'objectius per a 1, hauríeu d'obtenir el resultat correcte.

com forçar el tancament d’un programa sense gestor de tasques

Feu els càlculs més eficients amb la cerca d’objectius

Què passa si l’anàlisi i la recerca d’objectius poden agilitzar els càlculs amb les vostres fórmules d’una manera òptima? Aprendre les fórmules adequades és un altre pas important per convertir-se en un mestre excel.

Compartir Compartir Tweet Correu electrònic 15 fórmules d'Excel que us ajudaran a resoldre problemes de la vida real

Excel no és només per a empreses. Aquí teniu diverses fórmules de Microsoft Excel que us ajudaran a resoldre problemes quotidians complexos.

Llegiu a continuació Temes relacionats
  • Productivitat
  • Full de càlcul
  • Microsoft Excel
  • Consells de Microsoft Office
  • Anàlisi de dades
Sobre l'autor Amir M. Intel·ligència(39 articles publicats)

Amir és un estudiant de farmàcia amb passió per la tecnologia i els jocs. Li agrada tocar música, conduir cotxes i escriure paraules.

Més d'Amir M. Bohlooli

Subscriu-te al nostre butlletí

Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.

Feu clic aquí per subscriure-us