Prilikom rada s proračunskim tablicama, vrlo je uobičajeno znati željeni rezultat, ali nemati pojma koji broj unijeti u ćeliju da bi se on postigao. Umjesto ručnog isprobavanja vrijednosti poput igre pogađanja, možete pustiti Excel i druge programe za proračunske tablice da obave posao umjesto vas pomoću alata za traženje cilja.
Ova funkcija je kao da kažete tabeli: " Želim ovaj konačni rezultat ; pronađite vrijednost koju ova ćelija treba da ima da bi ga postigla." To je vrlo praktičan način za rješavanje jednačina s jednom nepoznatom, primjenjiv na scenarije iz stvarnog svijeta kao što su krediti, ocjene ispita, profit, budžeti ili poslovne analize.
Šta je tačno traženje cilja u tabelama?
Alat za traženje cilja dio je funkcija analize "Šta ako..." uključenih u Excel i druge uređivače poput ONLYOFFICE-a. Za razliku od običnih formula, koje izračunavaju rezultat iz ulaznih podataka, traženje cilja funkcionira obrnuto : vi odredite željeni rezultat, a proračunska tablica izračunava vrijednost koju ćelija mora imati da bi formula ispunila taj uvjet.
Drugim riječima, omogućava postojećoj formuli da usvoji određenu vrijednost automatskim podešavanjem jedne varijable. Ovo izbjegava tipičnu metodu pokušaja i grešaka , gdje mijenjate broj, gledate rezultat, ponovo ga mijenjate i tako dalje dok ne dobijete tačan rezultat.
Ključni koncepti: ciljna ćelija i ćelija koja se mijenja
Da biste koristili Goal Seek bez zabune, važno je razumjeti dva osnovna elementa: ciljnu ćeliju i ćeliju koja se mijenja . Dobro razumijevanje ova dva koncepta uštedjet će vam mnogo problema.
Ciljna ćelija je ona koja sadrži formulu čiji rezultat želite postaviti . To nije bilo koja ćelija; ona uvijek mora sadržavati formulu: prosjek, financijsku funkciju, SUMRPROIZVOD, izračun profita itd. To je mjesto gdje želite da se pojavi konačna vrijednost koju odaberete, kao što je minimalni prosjek ocjena, mjesečna uplata, željeni profit ili određena kamatna stopa.
Ćelija koja se mijenja je nepoznata ćelija , ćelija čiju će vrijednost alat promijeniti kako bi postigao traženi rezultat. Ova ćelija mora biti dio formule u ciljnoj ćeliji; ako formula ne referencira nju, alat za traženje cilja neće imati načina da utiče na rezultat.
Konačno, morat ćete odrediti željenu vrijednost , što je specifičan broj koji želite postići u ciljnoj ćeliji: konačna ocjena 50, prosjek 70, vjerovatnoća -900, profit od 50.000 itd. Vi određujete ovu vrijednost i ona je fokus cijele analize.
Kako koristiti Goal Seek: detaljan pregled
Prije nego što se uđe u konkretne primjere, vrijedi pogledati opći tok , jer se on ponavlja gotovo isto u bilo kojoj situaciji, kako u Excelu, tako i u ONLYOFFICE-u i drugim kompatibilnim editorima.
Općenito govoreći, tipična upotreba alatke Goal Seek slijedi ovaj redoslijed: prvo postavite list i formule , zatim otvorite alatku, definirate ciljnu ćeliju, vrijednost koju treba dostići i ćeliju koja će se promijeniti, a zatim pustite program da pronađe rješenje.
Standardna ruta bi bila:
- Pripremite podatkeU ćelije unosite vrijednosti koje već znate (iznos kredita, broj mjeseci, postojeće zadužnice, troškovi, cijene itd.), a po potrebi možete uklonite duplikate.
- Kreirajte formulu za rezultatU ciljnu ćeliju upisujete formulu koja izračunava rezultat koji želite kontrolirati (prosjek, PAY/PMT, profit, SUMPRODUCT, itd.).
- Otvori pretragu ciljevaNa kartici Podaci, unutar grupe Analiza hipoteze ili Analiza rezultata, možete pristupiti Traženju cilja.
- Popunite tri parametraOdređujete ciljnu ćeliju, vrijednost koju treba postići i ćeliju koja se mijenja.
- Pokreni i prihvatiProgram izračunava sukcesivne aproksimacije dok se ne pronađe rješenje, prikazuje vam okvir sa statusom pretrage, a vi možete prihvatiti da nove vrijednosti ostanu u listu.
Ovaj obrazac se ponavlja u svim scenarijima: jedine stvari koje se mijenjaju su uključene formule i koju ćeliju odlučite koristiti kao nepoznatu.
Pronalaženje cilja pomoću ocjena: Znanje koju ocjenu trebate dobiti
Još jedna vrlo uobičajena upotreba alata Goal Seek je izračunavanje ocjene koja vam je potrebna na predstojećem ispitu da biste postigli određeni konačni prosjek. Ovo je vrlo poučan, jednostavan i savršen primjer za razumijevanje kako alat funkcionira obrnuto.
Ideja je jednostavna: već imate neke ocjene, nedostaje vam još jedan test i želite znati koji minimalni rezultat vam je potreban da bi vaša konačna prosječna ocjena dostigla određenu vrijednost, bez obzira da li je prosjek prost ili ponderiran.
Prosječna vrijednost nekoliko ocjena
Zamislite da imate četiri testa iz nekog predmeta. Završili ste tri, imate njihove ocjene i preostaje vam samo završni ispit. Znate da vam je potrebna konačna ocjena 50 da biste položili kurs i želite da vam Excel kaže koji rezultat trebate dobiti na tom posljednjem ispitu.
Prvo, kreirajte tabelu sa poznatim ocjenama i ostavite jednu ćeliju praznu za nedostajuću ocjenu. U drugoj ćeliji izračunajte konačnu ocjenu kao jednostavan prosjek, na primjer sa =AVERAGE(raspon_ocjena) ili =AVERAGE ako koristite funkciju English. Ta ćelija sa prosjekom će biti vaša ciljna ćelija.
Zatim otvorite Traženje cilja iz podataka > Analiza šta-ako > Traženje cilja i konfigurirajte parametre. U polju Postavi ćeliju odaberite ćeliju koja sadrži prosjek (na primjer, C6). Zatim, u polje Do vrijednosti unesite cilj, koji bi u ovom slučaju bio 50. U polju Promjenjiva ćelija odaberite ćeliju koja sadrži izvanrednu ocjenu, na primjer, C5, koju će Excel automatski prilagoditi.
Nakon što kliknete na OK, alat izračunava potrebnu ocjenu ispita kako bi se postigao prosjek od tačno 50. Excel će u polju za status pretrage prikazati da li je pronađeno rješenje i, ako jeste, u ćeliju za ocjenu na čekanju će napisati minimalnu potrebnu ocjenu, na primjer 60%.
Ponderirane ocjene korištenjem funkcije SUMPRODUCT
Ako želite ići korak dalje, možete primijeniti Goal Seek na ponderirani sistem ocjenjivanja , gdje svaka procjena ima drugačiju težinu (na primjer, zadaci, kvizovi, završni ispit itd.). U ovom slučaju, konačna ocjena nije jednostavan prosjek, već ponderirani prosjek.
To se obično radi pomoću funkcije SUMPRODUCT , koja množi svaku ocjenu s njenom težinom i sumira proizvode. Dijeljenjem sa zbirom težina dobija se konačna ponderirana prosječna ocjena. Ovo je formula koju ćete postaviti u ciljnu ćeliju.
Postupak bi bio sljedeći: u jednoj koloni navodite različite aktivnosti (test 1, test 2, zadatak, ispit itd.), u drugoj koloni ocjene, a u trećoj procente ili pondere. Formula za konačnu ocjenu mogla bi biti nešto poput =SUMPRODUCT(raspon_ocjena;raspon_pondera)/SUM(raspon_pondera) . Ćelija koja sadrži SUMPRODUCT i SUM bit će ona koju koristite kao cilj.
Kada otvorite opciju Goal Seek (Traženje cilja), u ćeliji Set (Postavi ćeliju) birate ćeliju koja sadrži ponderiranu konačnu ocjenu; u ćeliji With (Sa vrijednošću) unosite minimum koji želite postići (na primjer, 50); a u ćeliji Changing (Promjena) ponovo navodite ocjenu ispita , što je jedina nepoznata vrijednost. Nakon pokretanja pretrage, program će vam reći koju ocjenu završni ispit mora imati, na primjer, 57,5% , tako da ukupni prosjek dostigne 50.
Korištenje funkcije "Goal Seek" u ONLYOFFICE Docs 8.0
Excel nije jedina kompanija koja uključuje ovaj alat; urednici poput ONLYOFFICE Docs-a su također dodali funkciju Goal Seek u svoje proračunske tablice, sa vrlo sličnom funkcionalnošću, idealnom ako radite u kolaborativnim ili cloud okruženjima.
Tok rada u ONLYOFFICE-u je praktično isti: prvo unosite svoje podatke i formulu koja povezuje te podatke s rezultatom koji želite pratiti, a možete koristiti i korisne funkcije poput automatskog dovršavanja . Na primjer, ako želite izračunati minimalnu prolaznu ocjenu, možete unijeti ocjene u rasponu i koristiti funkciju =AVERAGE(B2:B5) u ćeliji rezultata, recimo B7. Ta ćelija, B7, bit će ciljna ćelija.
Zatim idite na karticu Podaci i kliknite na ikonu Traženje cilja . Otvara se dijaloški okvir s tri polja vrlo slična onima u Excelu. U polje Postavi ćeliju odaberite ćeliju koja sadrži formulu (B7); u polje Vrijednost unesite željeni prosjek (na primjer, 70); i u polje Kada se ćelija promijeni odaberite ćeliju koja sadrži nedostajuću ocjenu (na primjer, B5), a to je ona koju će proračunska tablica ažurirati.
Kada kliknete na OK, ONLYOFFICE izračunava vrijednost koju nepoznata ocjena mora imati da bi prosjek dostigao 70. Prozor statusa traženja cilja prikazat će rezultat; ako ga prihvatite, relevantne ćelije će biti ažurirane. U prikazanom primjeru, sistem bi mogao vratiti da vam je potrebno 85 na posljednjem ispitu da biste postigli taj minimalni prosjek.
Isti mehanizam se primjenjuje i na druge slučajeve: amortizaciju kredita, planiranje investicija, fiksne budžete gdje želite prilagoditi određenu kategoriju ili analizu rentabilnosti u prodaji. Sve dok imate jednu nepoznatu varijablu i formulu koja je povezuje s vašim ciljem, Goal Seek je savršeno primjenjiv.
Kada koristiti Goal Seek, a kada preći na Solver
Iako je Goal Seek nevjerovatno koristan, ima ključno ograničenje: omogućava vam da prilagodite samo jednu varijabilnu ćeliju da biste postigli rezultat. Ako vaš problem uključuje više nepoznanica ili složenih ograničenja, neće biti dovoljan i morat ćete koristiti naprednije alate poput Solvera.
Solver je dodatak za Excel (i drugi paketi) koji proširuje analizu "Šta ako" omogućavajući vam maksimiziranje ili minimiziranje ciljne ćelije, istovremeno mijenjanje više ćelija i dodavanje ograničenja (minimumi, maksimumi, odnosi ćelija itd.). Idealan je za optimizaciju profita, alokaciju resursa ili linearno programiranje.
Na primjer, ako želite saznati koje ocjene trebate dobiti na dva različita ispita (engleski i hemija) da biste dobili prosjek od 84, "Goal Seek" neće raditi jer imate dvije varijable. "Solver" vam omogućava da postavite ćeliju prosjeka (s vrijednošću 84) kao cilj, promijenite ćelije za oba predmeta i pronađete moguću kombinaciju, kao što je dobijanje 87 u svakom.
U poslovnom scenariju, možete koristiti Solver za maksimiziranje profita u više servisnih linija, gdje morate istovremeno ispuniti minimalne ciljeve prodaje za jednu uslugu, maksimalne ciljeve prodaje za drugu i ukupno ograničenje prodaje. U tim slučajevima, postavili biste ukupni profit kao ciljnu ćeliju, odabrali prodane količine kao varijable i definirali ograničenja, dopuštajući Solveru da pronađe optimalnu kombinaciju.
Traženje cilja je savršeno za probleme s jednom varijablom (jedna ćelija koja se mijenja) i relativno jednostavne formule, dok Solver preuzima ulogu kada postoji više varijabli i istovremena ograničenja.
Uobičajeni problemi: Zašto traženje cilja možda ne funkcionira dobro
Iako alat obično radi vrlo pouzdano, postoje slučajevi kada se čini da Goal Seek "ne uspijeva" ili ne uspijeva pronaći rješenje. Problem obično nije u samom programu, već u postavkama ili formuli.
Jedna od prvih stvari koje treba provjeriti su postavke iteracije radne knjige . Excel, na primjer, ograničava maksimalni broj iteracija i maksimalnu dozvoljenu promjenu između aproksimacija kako bi se spriječili beskonačni proračuni. Ako je broj iteracija prenizak ili je maksimalna promjena prevelika, alat se može prerano zaustaviti ili neće postići očekivanu tačnost.
U Excelu možete pregledati ove parametre pod Datoteka > Opcije > Formule . Tamo možete povećati maksimalni broj iteracija kako biste omogućili više pokušaja pretraživanja ili smanjiti maksimalnu promjenu kako biste dobili preciznost. Podešavanje ovih vrijednosti često poboljšava rezultate kod složenijih problema.
Još jedna vrlo česta greška je da formula ciljne ćelije ne uključuje ćeliju koju želite promijeniti. Ako ćelija koja se mijenja nije dio formule, bez obzira koliko je tabela rotira, to neće imati nikakvog utjecaja na rezultat. Također biste trebali provjeriti je li formula dobro napisana i ne stvara kružne reference , jer to može blokirati izračun ili uzrokovati neuspjeh traženja cilja.
Savladavanje traženja ciljeva u Excelu i drugim proračunskim tablicama brz je način za prelazak s grubih proračuna na mnogo ozbiljniju numeričku analizu bez upuštanja u naprednu matematiku: jednostavno shvatite formulu za rezultat, odlučite koju varijablu želite prilagoditi i koristite alat da obavi težak posao umjesto vas, bilo da se radi o vašim ličnim finansijama, vašem studiju ili svakodnevnom upravljanju vašim poslovanjem.
