Kako zbrajati slučajne brojeve uz uvjet kao rezultat u Excelu 2007.
(kako prikazati ili obilježiti brojeve koji čine zbroj kao uvjet)

Ovaj interesantan primjer tutorijal SOLVER korak po korak, prikazat će vam kako možete postaviti nekakv broj kao uvjet (zbroj brojeva) i ujedno obilježiti sve brojeve u rasponu podataka (tablici) koji čine uvjetovani zbroj.

Za ovaj zadatak situacija je slijedeća:

1. U rasponu podataka A1:E1 (u tablici) nalaze se nekakvi random brojevi (raspon može biti i veći).
2. U ćeliji F1 nalazit će se "uvjet" tj. broj koji će biti zbroj nekih brojeva iz raspona podataka. Recimo da će to biti 1300
3. Korištenjem "funkcije" SOLVER  u ćeliji F1 za taj određeni broj, u rasponu podataka obojat će se sve ćelije brojeva koji daju zbroj uvjetovanog broja. Time smo označili brojeve koji daju zbroj koji smo zadali.

Dodavanje SOLVER Add-Ins u Excelu 2007

Kao prvo potrebno je dodati (aktivirati) SOLVER Add-Ins.
U Excelu 2007 to se radi preko Office button => Excel Options => Add-Ins
Ako vam se Solver Add-In nalazi u zoni kao na slici ispod tada je aktivan, no ako vam se nalazi u zoni "Inactive Application Add-Ins" tada ga selektirajte i preko gumba GO trebate dodati u gornju zonu "Active Application Add-Ins"

Nakon klika na gumb GO otvara vam se prozor za dodavanje raznih Add-Ins. Nas zanima SOLVER Add-In, no nećete pogriješiti ako dodate i ostale. (možda će vam trebati instalacijski CD?)

Nakon dodavanja SOLVERA Add-In u aktivnu zonu idemo na slijedeći korak. U red ispod naše tablice (raspon podataka) upišimo nulu (0) ispod svakog broja (to je binarni broj). U ćeliju F1 u kojoj ćemo definirati uvjet (zbroj 1300) preko Solvera, postavimo slijedeću formulu =A1*A2+B1*B2+C1*C2+D1*D2+E1*E2
Ova formula će dati zbroj određenih brojeva koje trenutno ne znamo a ovisit će o našem uvjetu. Ovdje već vidimo koji će to biti brojevi ali zamislite da je u jednom redu 150 ćelija? možete upotrijebiti maksimalno 200 ćelija u rasponu podataka

Sada idemo postaviti Conditional Formatting (Uvjetno oblikovanje) za sve ćelije u rasponu podataka. Dakle one ćelije koje će Excel uključiti kao pribrojnike trebat će biti istaknute (označene bojom) da vidimo koji su to brojevi koje Excel zbraja da bi postigao rezultat 1300 koji ćemo zadati u SOLVERU. Selektiramo cijeli raspon podataka pa potom postavimo uvjet , formulu (=A2=1) i oblikovanje tj. boju ćelije. Obratite pažnju na ovaj broj 1 u formuli. Taj broj 1 bit će poveznica između SOLVERA i našeg pribrojnika koji će Excel zbrajati da bi dobio rezultat koji smo zadali (1300). Svaki pribrojnik koji ispod sebe bude imao broj 1, bti će obojan bojom i uvršten u zbrajanje. Ako ne znate odrediti Conditional Formatting tada kliknite na link i proučite kako se radi uvjetno oblikovanje u Excelu 2007.

Sada smo spremni za postavljanje parametara u SOLVER-u. Selektirajte ćeliju F1 u koju ste postavili onu formulu (na slici žuto). Na kartici Data na kraju trebate imati naredbu za pozivanje Solvera (Rješavač), ako je nemate tada dodajte Solver Add-ins kako sam naveo na početku. Kliknite na naredbu Solver (Alat za rješavanje)

Sada vam se otvara dijalog prozor SOLVER Parameters. Na slici ispod uočite sve parametre koje je potrebno definirati za ispravan rad Solvera.

- Set Target Cell: je ćelija u kojoj će Solver dati rezultat a to je naša žuta ćelija F1
- Value of: znači da potavljamo vrijednost a ujedno tu vrijednost upišemo u polje pored. To je naš uvjetni zbroj 1300 koji trebamo dobiti a Conditional Formatting će nam obojati ćelije koje će dati taj zbroj. Solver će jednostavno pronaći sve brojeve koji ispod sebe imaju 1 pa ih zbrojiti i dati rezultat koji ovdje upišemo.
- By Changing Cells: ovo je red ispod našeg raspona podataka, a vrijednosti u njemu su promijenjive
- Subject to the Constraints: ovo su ograničenja koja postavljamo. U ovom slučaju postavljamo ograničenje da je "promjeniva vrijednost" u donjem redu raspona A2:E2 binarni broj. Za postavljanje ovog parametra trebate kliknuti na gumb Add.

Klikom na gumb Add pojavljuje nam se prozor u kojem možemo postaviti sve potrebne parametre. Za više detalja o parametrima koje možete postaviti ovdje pogledajte OVAJ link. U svakom slučaju ovdje za naš primjer postavite "binary" što znači da smo ograničili vrijednosti u donjem redu (rasponu podataka) na binearne brojeve "0" i "1". Dakle u donjem redu pojavljivat će nam se binarni brojevi nula (0) i jedan (1).

Nakon postavljenih parametara kliknite na gumb OK pa potom opet na glavnom dijalog prozoru na gumb Solve. Sada Vam se pojavio dijalog prozor na kojem odaberite prvu opciju (poigrajte se i sa drugom opcijom ;-) pa klik na OK.

I na kraju uočite ćelije koje su obojane sa Conditional Formatting u crveno. Zbroj vrijednosti u njima je 1300 tj. naš uvjet koji smo postavili u Solveru. Također uočite i binearne brojeve 1 ispod svake obojane ćelije, one određuju koje će se ćelije iznad zbrajati. Na dijalog prozoru "Solver Result" možemo birati opciju želimo li zadržati rezultat ili vratiti originalne vrijednosti kao i snimiti SCENARIJ

Ideja Peca. Datoteku iz ovog primjera solver.xls možete downladati OVDJE

PRIMJER 2.

Kako planirati prodaju - koliko moram prodati određenog proizvoda da bi postigao planiranu zaradu

U ovom primjeru pokazat ću kako možemo pomoću SOLVER-a napraviti malu kaklulaciju. Uzmimo za primjer da se bavimo uzgojem gljiva Bukovača i Šampinjona. U određenu seriju trebamo uložiti određenu svotu novaca. Nakon berbe gljiva zanima nas koliko kojih gljiva moramo prodati da bi postigli određenu zaradu a time možemo planirati i sadnju/prodaju.

Uočite na slici ispod naše proizvode, cijenu i stanje u proizvodnji (skladištu). Da bi zaradili 4000 kn potrebno je prodati 89,04 kg Bukovača po navedenoj cijeni kao i 72,55 kg Šampinjona po navedenoj cijeni. Ovime okvirno znamo kako rasporediti prodaju uz planiranu zaradu a da ne prelazimo ograničenu količinu stanja koju ćemo dobiti u proizvodnji. Uočite da i ovdje imamo pomoćne ćelije M3 i M5 u kojima se pokazuje traženi rezultat a koje se množe sa osnovnim ćelijama  (za razliku od prvog primjera gdje smo imali binarne brojeve)