Kako uporabljati iskanje cilja v Excelu

Iskanje cilja je eno od Excelovih orodij za analizo kaj če, ki vam pomaga najti pravilno vhodno vrednost formule, da dobite želeni rezultat. Pokaže, kako ena vrednost v formuli vpliva na drugo. Z drugimi besedami, če imate ciljno vrednost, ki jo želite doseči, lahko uporabite iskanje cilja, da poiščete pravo vhodno vrednost, da jo dobite.

Recimo, da ste pri predmetu dosegli skupno 75 točk in potrebujete vsaj 90, da dobite oceno S pri tem predmetu. Na srečo imate še zadnji test, ki vam lahko pomaga dvigniti povprečni rezultat. Z iskanjem cilja lahko ugotovite, koliko točk potrebujete na zadnjem testu, da dobite oceno S.

Goal Seek uporablja metodo poskusov in napak, da se odpravi težavi z vnosom ugibanj, dokler ne pride do pravega rezultata. Iskanje cilja lahko v nekaj sekundah poišče najboljšo vhodno vrednost za formulo, ki bi jo ročno ugotovila dolgo. V tem članku vam bomo z nekaj primeri pokazali, kako uporabljati orodje za iskanje cilja v Excelu.

Komponente funkcije iskanja cilja

Funkcija iskanja cilja je sestavljena iz treh parametrov, in sicer:

  • Nastavite celico – To je celica, v katero se vnese formula. Določa celico, v kateri želite želeni rezultat.
  • Ceniti – To je ciljna/želena vrednost, ki jo želite kot rezultat operacije iskanja cilja.
  • S menjavo celice – To določa celico, katere vrednost je treba prilagoditi, da dobimo želeni rezultat.

Kako uporabljati iskanje cilja v Excelu: primer 1

Če želite pokazati, kako deluje na preprostem primeru, si predstavljajte, da vodite stojnico s sadjem. Na spodnjem posnetku zaslona celica B11 (spodaj) prikazuje, koliko vas je stal nakup sadja za vašo stojnico, celica B12 pa vaš skupni prihodek od prodaje tega sadja. In celica B13 prikazuje odstotek vašega dobička (20%).

Če želite povečati svoj dobiček na '30%', vendar ne morete povečati svoje naložbe, morate povečati svoj prihodek, da pridobite dobiček. Toda na koliko? Iskanje cilja vam bo pomagalo najti.

Za izračun odstotka dobička uporabite naslednjo formulo v celici B13:

=(B12-B11)/B12

Zdaj želite izračunati stopnjo dobička tako, da je vaš odstotek dobička 30%. To lahko storite z iskanjem cilja v Excelu.

Prva stvar je, da izberete celico, katere vrednost želite prilagoditi. Vsakič, ko uporabite iskanje cilja, morate izbrati celico, ki vsebuje formulo ali funkcijo. V našem primeru bomo izbrali celico B13, ker vsebuje formulo za izračun odstotka.

Nato pojdite na zavihek »Podatki«, kliknite gumb »Kaj, če analiza« v skupini Napovedi in izberite »Iskanje cilja«.

Pojavi se pogovorno okno Iskanje cilja s 3 polji:

  • Nastavite celico – Vnesite sklic na celico, ki vsebuje formulo (B13). To je celica, ki bo imela želeni izhod.
  • Ceniti – Vnesite želeni rezultat, ki ga želite doseči (30%).
  • S menjavo celice – Vstavite referenco celice za vhodno vrednost, ki jo želite spremeniti (B12), da dosežete želeni rezultat. Preprosto kliknite na celico ali ročno vnesite sklic na celico. Ko izberete celico, bo Excel dodal znak '$' pred črko stolpca in številko vrstice, da bo postala absolutna celica.

Ko končate, kliknite »V redu«, da ga preizkusite.

Nato se prikaže pogovorno okno »Stanje iskanja cilja« in vas obvesti, če je našla rešitev, kot je prikazano spodaj. Če je bila rešitev najdena, se vnosna vrednost v "spreminjajoči se celici (B12)" prilagodi na novo vrednost. To je tisto, kar želimo. V tem primeru je analiza ugotovila, da morate za dosego cilja 30 % dobička doseči 1635,5 $ (B12).

Kliknite »V redu« in Excel bo spremenil vrednosti celice ali kliknite »Prekliči«, da zavržete rešitev in obnovite prvotno vrednost.

Vhodna vrednost (1635,5) v celici B12 je tisto, kar smo ugotovili z iskanjem cilja, da bi dosegli naš cilj (30 %).

Stvari, ki si jih morate zapomniti pri uporabi iskanja cilja

  • Nastavljena celica mora vedno vsebovati formulo, ki je odvisna od celice »S spremembo celice«.
  • Iskanje cilja lahko uporabite samo za eno vrednost vnosa celice hkrati
  • "S spremembo celice" mora vsebovati vrednost in ne formule.
  • Če Iskanje cilja ne najde prave rešitve, prikaže najbližjo vrednost, ki jo lahko ustvari, in vam pove, da sporočilo »Iskanje cilja morda ni našelo rešitve«.

Kaj storiti, ko Excelovo iskanje ciljev ne deluje

Če pa ste prepričani, da obstaja rešitev, lahko poskusite odpraviti to težavo z nekaj stvarmi.

Preverite parametre in vrednosti iskanja cilja

Preverite dve stvari: najprej preverite, ali se parameter »Nastavi celico« nanaša na celico formule. Drugič, poskrbite, da je celica formule (Set cell) neposredno ali posredno odvisna od spreminjajoče se celice.

Prilagajanje nastavitev ponovitve

V nastavitvah programa Excel lahko spremenite število možnih poskusov, ki jih lahko Excel naredi, da najde pravo rešitev, pa tudi njeno natančnost.

Če želite spremeniti nastavitve izračuna ponovitve, na seznamu zavihkov kliknite »Datoteka«. Nato kliknite »Možnosti« na dnu.

V oknu z možnostmi Excela kliknite »Formule« v levem podoknu.

V razdelku »Možnosti izračuna« spremenite te nastavitve:

  • Največje število ponovitev – označuje število možnih rešitev, ki jih bo izračunal excel; višje kot je število, več ponovitev lahko izvede. Na primer, če nastavite »Največje število ponovitev« na 150, Excel preizkusi 150 možnih rešitev.
  • Največja sprememba – označuje točnost rezultatov; manjše število daje večjo natančnost. Na primer, če je vrednost vaše vhodne celice enaka '0', vendar iskanje cilja preneha računati pri '0,001', bi morala sprememba te vrednosti v '0,0001' rešiti težavo.

Povečajte vrednost »Maksimalne ponovitve«, če želite, da Excel preizkusi več možnih rešitev, in zmanjšajte vrednost »Največja sprememba«, če želite natančnejši rezultat.

Spodnji posnetek zaslona prikazuje privzeto vrednost:

Brez krožnih referenc

Ko se formula sklicuje nazaj na svojo celico, se imenuje krožna referenca. Če želite, da Excelovo iskanje ciljev deluje pravilno, formule ne smejo uporabljati krožnega sklicevanja.

Primer iskanja cilja v Excelu 2

Recimo, da si od nekoga izposodite denar v višini '25000 $'. Posojajo vam denar po obrestni meri 7 % na mesec za obdobje 20 mesecev, kar pomeni odplačilo »1327 $« na mesec. Vendar si lahko privoščite samo 20 mesecev plačevanja »1000 $« na mesec s 7-odstotnimi obrestmi. Goal Seek vam lahko pomaga najti znesek posojila, ki povzroči mesečno plačilo (EMI) v višini '1000 $'.

Vnesite tri vhodne spremenljivke, ki jih boste potrebovali za izračun vašega PMT, to je obrestna mera 7 %, rok 20 mesecev in znesek glavnice 25.000 $.

V celico B5 vnesite naslednjo formulo PMT, ki vam bo dala znesek EMI v višini 1.327,97 USD.

Sintaksa funkcije PMT:

=PMT(obrestna mera/12, rok, glavnica)

Formula:

=PMT(B3/12,B4,-B2)

Izberite celico B5 (celica formule) in pojdite na Podatki –> Analiza –> Iskanje cilja.

V oknu »Iskanje cilja« uporabite te parametre:

  • Nastavite celico – B5 (celica, ki vsebuje formulo, ki izračuna EMI)
  • Ceniti – 1000 (formula rezultat/cilj, ki ga iščete)
  • S menjavo celice – B2 (to je znesek posojila, ki ga želite spremeniti, da dosežete ciljno vrednost)

Nato pritisnite »V redu«, da obdržite najdeno rešitev, ali »Prekliči«, da jo zavržete.

Analiza vam pove, da je največji znesek denarja, ki si ga lahko izposodite, 18825 $, če želite preseči svoj proračun.

Tako uporabljate iskanje cilja v Excelu.