Microsoft Office Excel
uređuje Dragan Grbić

Matična strana sajta

Novo na sajtu   Mapa sajta   Beleške   Kontakt   Pretraga MSKB  Blog Praktikuma   RSS feed   P@W @Tw   Izvezi stranicu u PDF

Office Praktikum

Još o Excelu

Skoro svakodnevno slušamo

 

Angažujte Solver!

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

Da li koristite programski dodatak Solver ("Rešavač") u Excelu? Često imam običaj da kažem da izvedene (pivot) tabele čine jedan jedini dovoljan razlog za angažovanje Excela. Međutim, angažovanje internog sistema Solver je više od toga: može vam pomoći da uštedite novac u poslovanju! Naravno, to je nepobitan argument: predložiću vam da pružite priliku ovoj alatki koja predstavlja svojevrsni vrhunac moći Excela. Najzad, ovo je možda najprimereniji prilog u vreme kad svi pričaju o globalnoj ekonomskoj krizi: pridružujem se tom trendu uz tiho negodovanje što je Solver do sada bio prilično zapostavljen na ovim prostorima. Ali, tome sam pomalo i ja sam kriv...

Solver je sistem za najsloženije oblike What-If analize i problema linearne i nelinearne optimizacije u poslovnom ambijentu. Ideja ove alatke je u dostizanju optimalnih vrednosti na osnovu zadatih ograničenja (fiksne ulazne veličine, razne nepromenljive vrednosti, "uska grla") i niza parametarskih veličina koje se mogu menjati - to mogu biti vrednosti, ali i odabrane metode kojima će složeni matematički model biti podvrgnut. Time definišete model problema i nalažete dostizanje optimalne vrednosti (mogući maksimum, minimum ili unapred zadata vrednost). Ograničenje alatke je veće od realnih potreba većine korisnika: možete definisati do 200 ulaznih parametara i do 100 ograničenja. Rezultat može da se sačuva na rezultujućem radnom listu ili kao posebni scenario (Scenario Manager je posebna alatka, takođe vrlo korisna).

Solver nije inicijalno uključen, čak i ako je instaliran. Da biste ga angažovali, pristupićete dijalogu za kontrolu programskih dodataka i uključiti njegovo prisustvo. U Excelu 2003 otvorite dijalog Tools / Add-Ins (Alatke / Programski dodaci), pa uključite polje za potvrdu pored stavke Solver Add-In (Programski dodatak "Rešavač"). Stavka za pokretanje alatke će se potom naći u meniju Tools (Alati). U Excelu 2007, putanja je malo složenija: otvorite panel Office > Excel Options > Add-Ins (Office > Excel opcije > Programski dodaci), u padajućoj listi pri dnu odaberite Manage: Excel Add-Ins (Upravljanje: Programski dodaci za Excel) i kliknete na dugme Go (Idi) i, najzad, uključite stavku za Solver. Stavka za pokretanje će se naći na ribonu u grupi Data > Analysis (Podaci > Analysis; Solver nije lokalizovan). U slučaju da dijalog za uključivanje programskih dodataka ne sadrži stavku za Solver, moraćete da pokrenete instalacionu proceduru za svoju verziju sistema Microsoft Office (opcija Add or Remove Features), pa da naknadno instalirate programske dodatke za Excel. Uzgred, uz Solver dolazi i radna sveska sa zanimljivim primerima: potražite datoteku SOLVSAMP.XLS (nalazi se negde u strukturi foldera \Program Files\Microsoft Office\) i istražite mogućnosti.

Ali, da ne bismo komplikovali na početku, provešću vas kroz krajnje banalan, ali očigledan primer kako Solver funkcioniše: korak po korak, rešićemo dve jednačine sa dve nepoznate. Neka to budu jednačine

2x+y=0
x+y+1=0

Bez ljutnje: ovo je dovoljno jednostavno rešite i napamet, ali je sasvim prikladno za upoznavanje sa Solverom. Postavite bilo koja dva broja u B1 i B2 (pogledajte sliku). U B3 unesite formulu =2*B1+B2, a u B4 unesite =B1+B2+1. U B5 unesite formulu =B3+B4 - naizgled, ovo nema smisla, ali videćete u čemu je poenta. Pokrenite Solver.

Mnogo je lakše koristiti Solver nego što mislite: evo primera koji to dokazuje

Poenta alata je da konvergira vrednost jednog rezultata tako što će menjati ulazne vrednosti u zadatom opsegu u okviru ograničenja koja definišete. Zato valja postaviti zbir dve formule na nulu, jer to će biti ciljna vrednost za alatku: ono što nam zaista treba, a to su nule kao rezultati dve formule, postavićemo kao ograničenja.

Postupak je sledeći: u polje Set Target Cell stavite adresu B5 (kliknite na ćeliju) i odredite da želite nulu kao cilj - Equal to: Value of 0. Druge raspoložive opcije su maksimum (npr. profit) i minimum (troškovi). U polje By Changing Cells unesite (kucanjem ili obeležavanjem) opseg B1:B2; u realnim uslovima, ovde ćete moći da unesete i različite opsege, razdvajajući ih znakom tačka-zarez. Možete pokušati i klikom na dugme Guess - alatka će pretpostaviti ulazne veličine na osnovu šeme zavisnosti, ali to ne mora da odgovara stvarnom stanju (npr. ako su neke veličine u kalkulaciji nepromenljive), pa pripazite kad budete postavljali šemu za Solver.

Okvir Subject to the Constraints je kritičan, jer morate opisati realne kombinacije: na primer, neke vrednosti ne smeju da budu negativne ili moraju da ostanu celobrojne, neke ne smeju da pređu zadati maksimum, itd. Dok formirate logičke izraze kao ograničenja, dozvoljeno je da kao uslov postavljate konstante ili vrednosti u nekim drugim ćelijama. U ovom slučaju, klikom na Add ćemo odrediti da rezultati formula moraju da budu nula (ćelije B3 i B4); rekoh već, to je realni cilj. Dijalog je očigledan, verujem da nije potrebno objašnjenje, osim da naglasim da Int znači "mora da je ceo broj", a opciju Bin ću prepustiti inženjerima; oni će znati šta da učine sa tom opcijom.

Kada ste postavili oba ograničenja, Solver je spreman za akciju: kliknite na Solve i dobićete rezultate u polaznim ćelijama. Dijalog će vas obavestiti da je Solver našao rešenje i da su uslovi optimizacije zadovoljeni. Možete odlučiti da zadržite rešenje (Keep Solver Solution) ili da vratite polazne vrednosti (Restore...). U slučaju dubljih analiza, pogotovo kad je mreža ograničenja vrlo složena, možda ćete poželeti da usput sačinite jedan ili više izveštaja o izvedbi rezultata (Reports). Ako budete menjali apsolutne vrednosti ograničenja, tada svaki put možete dodati scenario radnom listu, pa kasnije izvesti uporednu analizu rezultata. Dalje odavde možete polako i sami: svakako se isplati da se iskušate u kreiranju modela za nalaženje maksimalne zarade, najmanjeg troška, tačnog sastava recepture...

Moguće je da Solver ne može da nađe rezultat (probajte jednačine x+y+1=0 i x+y+2=0) i tada će vas izvestiti o tome. U stvarnom svetu, možda je najbolje da krenete postavljajući šemu kalkulacije zasnovanu na nekom školskom primeru (npr. receptura neke mešavine) i varirajući vrednosti samostalno u okviru poznate prakse. Kada se uverite da postavljeni model funkcioniše, tek onda pozovite Solver u akciju. Valja da vas upozorim i na opcije rada alatke: u dijalogu Options, koji ćete pozvati klikom na dugme iz dijaloga Solver, moguće je postaviti mnoštvo parametara koji uslovljavaju metod konvergencije. Neću vas zamarati pojedinostima, jer ni sam ne razumem neke varijacije. Ali, preneću vam iskustvo svog kolege Slobodana Ljubišića, koji je iskusio problem tokom angažovanja alatke Solver u rešavanju optimizacije transporta. Iako je u ograničenjima naglasio da izmenljive ćelije moraju da ostanu celobrojne, stalno je dobijao vrednosti koje su imale distorziju na nekoj banalno niskoj decimali. Problem je trajao sve dok u opcijama rada alatke nije naložio da preciznost bude održavana na nivou 1E-46 (eksponencijalni oblik zapisa) i od tada je sve radilo kako valja.