Goal Seek jedan je od Excelovih alata za analizu Što-ako koji vam pomaže pronaći ispravnu ulaznu vrijednost formule kako biste dobili željeni izlaz. Pokazuje kako jedna vrijednost u formuli utječe na drugu. Drugim riječima, ako imate ciljnu vrijednost koju želite postići, možete upotrijebiti traženje cilja kako biste pronašli pravu ulaznu vrijednost za dobivanje iste.
Na primjer, recimo da ste postigli ukupno 75 bodova iz predmeta i trebate najmanje 90 da biste dobili ocjenu S iz tog predmeta. Srećom, imate još jedan posljednji test koji bi vam mogao pomoći da podignete svoj prosječni rezultat. Možete koristiti Goal Seek da shvatite koliko bodova trebate na tom završnom testu da biste dobili ocjenu S.
Goal Seek koristi metodu pokušaja i pogreške za vraćanje problema unatrag unošenjem nagađanja dok ne dođe do pravog rezultata. Goal Seek može pronaći najbolju ulaznu vrijednost za formulu u nekoliko sekundi za koju bi trebalo dugo vremena da se shvati ručno. U ovom članku pokazat ćemo vam kako koristiti alat za traženje cilja u Excelu s nekim primjerima.
Komponente funkcije traženja cilja
Funkcija traženja cilja sastoji se od tri parametra, tj.:
- Postavite ćeliju – Ovo je ćelija u koju se unosi formula. Određuje ćeliju u kojoj želite željeni rezultat.
- Cijeniti – Ovo je ciljna/željena vrijednost koju želite kao rezultat operacije traženja cilja.
- Promjenom ćelije – Ovo određuje ćeliju čiju vrijednost treba prilagoditi kako bi se dobio željeni rezultat.
Kako koristiti traženje cilja u Excelu: primjer 1
Kako biste demonstrirali kako to radi na jednostavnom primjeru, zamislite da vodite štand s voćem. Na snimci zaslona ispod, ćelija B11 (ispod) prikazuje koliko vas je koštala kupnja voća za vaš štand, a ćelija B12 prikazuje vaš ukupan prihod od prodaje tog voća. A ćelija B13 prikazuje postotak vašeg profita (20%).
Ako želite povećati svoju zaradu na '30%', ali ne možete povećati svoje ulaganje, stoga morate povećati prihod da biste ostvarili profit. Ali na koliko? Traženje cilja pomoći će vam da ga pronađete.
Za izračunavanje postotka dobiti koristite sljedeću formulu u ćeliji B13:
=(B12-B11)/B12Sada želite izračunati profitnu maržu tako da vaš postotak dobiti bude 30%. To možete učiniti pomoću Goal Seek u Excelu.
Prvo što trebate učiniti je odabrati ćeliju čiju vrijednost želite prilagoditi. Svaki put kada koristite traženje cilja, morate odabrati ćeliju koja sadrži formulu ili funkciju. U našem slučaju, odabrat ćemo ćeliju B13 jer sadrži formulu za izračunavanje postotka.
Zatim idite na karticu "Podaci", kliknite gumb "Što ako analiza" u grupi Prognoza i odaberite "Traženje cilja".
Pojavit će se dijaloški okvir Traženje cilja s 3 polja:
- Postavite ćeliju – Unesite referencu ćelije koja sadrži formulu (B13). Ovo je ćelija koja će imati željeni izlaz.
- Cijeniti – Unesite željeni rezultat koji pokušavate postići (30%).
- Promjenom ćelije – Umetnite referencu ćelije za ulaznu vrijednost koju želite promijeniti (B12) kako biste došli do željenog rezultata. Jednostavno kliknite na ćeliju ili ručno unesite referencu ćelije. Kada odaberete ćeliju, Excel će dodati znak ‘$’ ispred slova stupca i broja retka kako bi postala apsolutna ćelija.
Kada završite, kliknite "U redu" da biste ga testirali.
Tada će se pojaviti dijaloški okvir "Status traženja cilja" i obavijestiti vas je li pronađeno neko rješenje kao što je prikazano u nastavku. Ako je rješenje pronađeno, ulazna vrijednost u 'promjenjivoj ćeliji (B12)' bit će podešena na novu vrijednost. To je ono što želimo. Dakle, u ovom primjeru, analiza je utvrdila da, kako biste postigli svoj cilj od 30% dobiti, trebate ostvariti prihod od 1635,5 USD (B12).
Kliknite "U redu" i Excel će promijeniti vrijednosti ćelije ili kliknite "Odustani" da biste odbacili rješenje i vratili izvornu vrijednost.
Ulazna vrijednost (1635,5) u ćeliji B12 je ono što smo saznali korištenjem Goal Seek-a kako bismo postigli svoj cilj (30%).
Stvari koje treba zapamtiti kada koristite Goal Seek
- Postavljena ćelija uvijek mora sadržavati formulu koja ovisi o ćeliji 'Promjenom ćelije'.
- Traženje cilja možete koristiti samo na ulaznoj vrijednosti jedne ćelije odjednom
- "Promjenom ćelije" mora sadržavati vrijednost, a ne formulu.
- Ako Traženje cilja ne može pronaći ispravno rješenje, prikazuje najbližu vrijednost koju može proizvesti i govori vam da poruka "Traženje cilja možda nije pronašla rješenje".
Što učiniti kada Excel traženje cilja ne radi
Međutim, ako ste sigurni da postoji rješenje, postoji nekoliko stvari koje možete pokušati riješiti ovaj problem.
Provjerite parametre i vrijednosti traženja cilja
Postoje dvije stvari koje biste trebali provjeriti: prvo provjerite odnosi li se parametar "Postavi ćeliju" na ćeliju formule. Drugo, provjerite ovisi li ćelija formule (Set cell) izravno ili neizravno o ćeliji koja se mijenja.
Podešavanje postavki ponavljanja
U postavkama programa Excel možete promijeniti broj mogućih pokušaja koje Excel može učiniti da pronađe pravo rješenje kao i njegovu točnost.
Da biste promijenili postavke izračuna iteracije, kliknite "Datoteka" s popisa kartica. Zatim kliknite na "Opcije" pri dnu.
U prozoru s opcijama programa Excel kliknite "Formule" na lijevom oknu.
U odjeljku "Opcije izračuna" promijenite ove postavke:
- Maksimalne iteracije – označava broj mogućih rješenja koje će Excel izračunati; što je broj veći to može izvesti više iteracija. Na primjer, ako postavite "Maksimalne iteracije" na 150, Excel će testirati 150 mogućih rješenja.
- Maksimalna promjena – ukazuje na točnost rezultata; manji broj daje veću točnost. Na primjer, ako je vrijednost vaše ulazne ćelije jednaka '0', ali traženje cilja prestane izračunavati na '0,001', promjena ovog u '0,0001' trebala bi riješiti problem.
Povećajte vrijednost "Maksimalne iteracije" ako želite da Excel testira više mogućih rješenja i smanjite vrijednost "Maksimalna promjena" ako želite točniji rezultat.
Snimka zaslona ispod prikazuje zadanu vrijednost:
Nema kružnih referenci
Kada se formula referira natrag na vlastitu ćeliju, naziva se kružna referenca. Ako želite da Excel Goal Seek radi ispravno, formule ne bi trebale koristiti kružnu referencu.
Primjer traženja cilja u Excelu 2
Recimo da od nekoga posuđujete novac od '25 000 dolara'. Oni vam posuđuju novac uz kamatu od 7% mjesečno na period od 20 mjeseci, što čini otplatu od '1327$' mjesečno. Ali možete si priuštiti samo plaćanje '1000 dolara' mjesečno tijekom 20 mjeseci uz kamatu od 7%. Goal Seek vam može pomoći da pronađete iznos zajma koji proizvodi mjesečnu uplatu (EMI) od '1000 USD'.
Unesite tri ulazne varijable koje će vam trebati da izračunate PMT izračun, tj. kamatnu stopu od 7%, rok od 20 mjeseci i iznos glavnice od 25.000 USD.
Unesite sljedeću PMT formulu u ćeliju B5 koja će vam dati iznos EMI od 1.327,97 USD.
Sintaksa PMT funkcije:
=PMT(kamatna stopa/12, rok, glavnica)formula:
=PMT(B3/12,B4,-B2)
Odaberite ćeliju B5 (ćelija formule) i idite na Podaci –> Što ako Analiza –> Traženje cilja.
Koristite ove parametre u prozoru "Traženje cilja":
- Postavite ćeliju – B5 (ćelija koja sadrži formulu koja izračunava EMI)
- Cijeniti – 1000 (formula rezultat/cilj koji tražite)
- Promjenom ćelije – B2 (ovo je iznos zajma koji želite promijeniti da biste postigli ciljnu vrijednost)
Zatim pritisnite "U redu" da zadržite pronađeno rješenje ili "Odustani" da ga odbacite.
Analiza vam govori da je maksimalni iznos novca koji možete posuditi iznosi 18825 USD ako želite premašiti svoj proračun.
Tako koristite traženje cilja u Excelu.