Goal Seek ir viens no Excel What-ja analīzes rīkiem, kas palīdz atrast pareizo formulas ievades vērtību, lai iegūtu vēlamo rezultātu. Tas parāda, kā viena formulas vērtība ietekmē citu vērtību. Citiem vārdiem sakot, ja jums ir mērķa vērtība, kuru vēlaties sasniegt, varat izmantot mērķa meklēšanu, lai atrastu pareizo ievades vērtību tās iegūšanai.
Piemēram, pieņemsim, ka jūs kādā priekšmetā kopā ieguvāt 75 atzīmes un jums ir nepieciešams vismaz 90, lai šajā priekšmetā iegūtu S atzīmi. Par laimi, jums ir pēdējais pārbaudījums, kas varētu palīdzēt palielināt vidējo punktu skaitu. Varat izmantot Goal Seek, lai noskaidrotu, cik daudz punktu jums ir nepieciešams pēdējā pārbaudē, lai iegūtu S atzīmi.
Goal Seek izmanto izmēģinājumu un kļūdu metodi, lai izsekotu problēmai, ievadot minējumus, līdz tiek sasniegts pareizais rezultāts. Goal Seek var atrast labāko formulas ievades vērtību dažu sekunžu laikā, kas būtu prasījis ilgu laiku, lai izdomātu manuāli. Šajā rakstā ar dažiem piemēriem parādīsim, kā programmā Excel izmantot rīku Goal Seek.
Mērķa meklēšanas funkcijas sastāvdaļas
Mērķa meklēšanas funkcija sastāv no trim parametriem, proti:
- Iestatīt šūnu – Šī ir šūna, kurā tiek ievadīta formula. Tas norāda šūnu, kurā vēlaties iegūt vēlamo izvadi.
- Novērtēt – Šī ir mērķa / vēlamā vērtība, ko vēlaties mērķa meklēšanas darbības rezultātā.
- Mainot šūnu – Tas norāda šūnu, kuras vērtība ir jāpielāgo, lai iegūtu vēlamo izvadi.
Kā izmantot mērķa meklēšanu programmā Excel: 1. piemērs
Lai parādītu, kā tas darbojas vienkāršā piemērā, iedomājieties, ka vadāt augļu stendu. Tālāk esošajā ekrānuzņēmumā šūnā B11 (tālāk) tiek parādīts, cik izmaksāja augļu iegāde jūsu stendam, un šūna B12 parāda jūsu kopējos ieņēmumus no šo augļu pārdošanas. Un šūna B13 parāda jūsu peļņas procentuālo daļu (20%).
Ja vēlaties palielināt savu peļņu līdz “30%, bet nevarat palielināt ieguldījumus, tad, lai gūtu peļņu, jums ir jāpalielina ieņēmumi. Bet uz cik? Mērķa meklēšana palīdzēs jums to atrast.
Lai aprēķinātu peļņas procentus, šūnā B13 izmantojiet šādu formulu:
=(B12-B11)/B12Tagad jūs vēlaties aprēķināt peļņas normu, lai jūsu peļņas procents būtu 30%. To var izdarīt, izmantojot Goal Seek programmā Excel.
Pirmā lieta, kas jādara, ir atlasīt šūnu, kuras vērtību vēlaties pielāgot. Katru reizi, kad izmantojat mērķa meklēšanu, ir jāatlasa šūna, kurā ir formula vai funkcija. Mūsu gadījumā mēs atlasīsim šūnu B13, jo tajā ir formula procentu aprēķināšanai.
Pēc tam dodieties uz cilni “Dati”, Prognožu grupā noklikšķiniet uz pogas “Ko darīt, ja analīze” un atlasiet “Mērķa meklēšana”.
Tiks parādīts dialoglodziņš Mērķa meklēšana ar 3 laukiem:
- Iestatīt šūnu – Ievadiet šūnas atsauci, kas satur formulu (B13). Šī ir šūna, kurai būs vēlamā izvade.
- Novērtēt – Ievadiet vēlamo rezultātu, ko mēģināt sasniegt (30%).
- Mainot šūnu – Ievietojiet šūnas atsauci ievades vērtībai, kuru vēlaties mainīt (B12), lai iegūtu vēlamo rezultātu. Vienkārši noklikšķiniet uz šūnas vai manuāli ievadiet šūnas atsauci. Atlasot šūnu, programma Excel pievienos zīmi “$” pirms kolonnas burta un rindas numura, lai padarītu to par absolūtu šūnu.
Kad esat pabeidzis, noklikšķiniet uz Labi, lai to pārbaudītu.
Pēc tam tiks parādīts dialoglodziņš "Mērķa meklēšanas statuss" un informēs jūs, ja ir atrasts kāds risinājums, kā parādīts tālāk. Ja risinājums ir atrasts, ievades vērtība “mainīgajā šūnā (B12)” tiks pielāgota jaunai vērtībai. Tas ir tas, ko mēs vēlamies. Tātad šajā piemērā analīze noteica, ka, lai jūs sasniegtu 30% peļņas mērķi, jums jāsasniedz 1635,5 ASV dolāru (B12) ieņēmumi.
Noklikšķiniet uz “OK”, un programma Excel mainīs šūnu vērtības vai noklikšķiniet uz “Atcelt”, lai atmestu risinājumu un atjaunotu sākotnējo vērtību.
Ievades vērtība (1635,5) šūnā B12 ir tā, ko mēs noskaidrojām, izmantojot Goal Seek, lai sasniegtu savu mērķi (30%).
Lietas, kas jāatceras, izmantojot Goal Seek
- Iestatītajā šūnā vienmēr ir jābūt formulai, kas ir atkarīga no šūnas “Mainot šūnu”.
- Mērķa meklēšanu vienlaikus var izmantot tikai vienai šūnas ievades vērtībai
- Laukā “Mainot šūnu” ir jāietver vērtība, nevis formula.
- Ja Mērķa meklēšana nevar atrast pareizo risinājumu, tas parāda tuvāko vērtību, ko tas var radīt, un norāda, ka ziņojums “Mērķa meklēšana, iespējams, nav atradis risinājumu”.
Ko darīt, ja Excel Goal Seek nedarbojas
Tomēr, ja esat pārliecināts, ka ir risinājums, varat mēģināt novērst šo problēmu.
Pārbaudiet Goal Seek parametrus un vērtības
Ir jāpārbauda divas lietas: vispirms pārbaudiet, vai parametrs “Iestatīt šūnu” attiecas uz formulas šūnu. Otrkārt, pārliecinieties, vai formulas šūna (Iestatīt šūnu) tieši vai netieši ir atkarīga no mainīgās šūnas.
Iterācijas iestatījumu pielāgošana
Excel iestatījumos varat mainīt iespējamo Excel mēģinājumu skaitu, lai atrastu pareizo risinājumu, kā arī tā precizitāti.
Lai mainītu iterācijas aprēķinu iestatījumus, ciļņu sarakstā noklikšķiniet uz "Fails". Pēc tam apakšā noklikšķiniet uz "Opcijas".
Excel opciju logā noklikšķiniet uz "Formulas" kreisajā rūtī.
Sadaļā “Aprēķinu opcijas” mainiet šos iestatījumus :
- Maksimālās iterācijas – norāda iespējamo risinājumu skaitu, ko Excel aprēķinās; jo lielāks skaitlis, jo vairāk iterāciju tas var veikt. Piemēram, ja iestatāt “Maksimālās iterācijas” uz 150, programma Excel izmēģina 150 iespējamos risinājumus.
- Maksimālās izmaiņas – norāda rezultātu precizitāti; mazāks skaitlis nodrošina lielāku precizitāti. Piemēram, ja jūsu ievades šūnas vērtība ir vienāda ar “0”, bet Goal Seek pārstāj rēķināt pie “0,001”, mainot šo vērtību uz “0,0001”, problēma jāatrisina.
Palieliniet vērtību “Maksimālās iterācijas”, ja vēlaties, lai programma Excel pārbaudītu vairāk iespējamo risinājumu, un samaziniet vērtību “Maksimālās izmaiņas”, ja vēlaties iegūt precīzāku rezultātu.
Tālāk esošajā ekrānuzņēmumā ir parādīta noklusējuma vērtība:
Nav cirkulāru norāžu
Ja formula atsaucas uz savu šūnu, to sauc par apļveida atsauci. Ja vēlaties, lai Excel Goal Search darbotos pareizi, formulās nevajadzētu izmantot apļveida atsauci.
Excel mērķa meklēšanas 2. piemērs
Pieņemsim, ka jūs no kāda aizņematies naudu “25 000 $”. Viņi aizdod jums naudu ar procentu likmi 7% mēnesī uz 20 mēnešiem, kas veido 1327 USD atmaksu mēnesī. Bet jūs varat atļauties maksāt tikai '$1000' mēnesī 20 mēnešus ar 7% procentiem. Goal Seek var palīdzēt atrast aizdevuma summu, kas nodrošina ikmēneša maksājumu (EMI) 1000 ASV dolāru apmērā.
Ievadiet trīs ievades mainīgos, kas jums būs nepieciešami, lai aprēķinātu PMT aprēķinu, t.i., procentu likmi 7%, termiņu 20 mēneši un pamatsummu 25 000 USD.
Šūnā B5 ievadiet šādu PMT formulu, kas dos jums EMI summu 1327,97 USD.
PMT funkcijas sintakse:
=PMT (procentu likme/12, termiņš, pamatsumma)Formula:
=PMT(B3/12,B4,-B2)
Atlasiet šūnu B5 (formulas šūna) un dodieties uz Dati -> Ko darīt, ja analīze -> Mērķa meklēšana.
Logā “Mērķa meklēšana” izmantojiet šos parametrus:
- Iestatīt šūnu – B5 (šūna, kurā ir formula, kas aprēķina EMI)
- Novērtēt - 1000 (formulas rezultāts/mērķis, kuru meklējat)
- Mainot šūnu – B2 (šī ir aizdevuma summa, kuru vēlaties mainīt, lai sasniegtu mērķa vērtību)
Pēc tam nospiediet “OK”, lai saglabātu atrasto risinājumu, vai “Atcelt”, lai to atmestu.
Analīze parāda, ka maksimālā naudas summa, ko varat aizņemties, ir USD 18825, ja vēlaties pārsniegt savu budžetu.
Tādā veidā jūs izmantojat Goal Seek programmā Excel.