|
|
|
EXCEL - UTILIZAREA SOLVER-ULUI
Cuprins
Modificarea configuratiei Solver-ului
Aplicatie
Cu comada Goal Seek se poate analiza variatia rezultatelor in functie de o singura celula de pornire. Multe foi de calcul sunt prea complexe pentru comanda Goal Seek. La unele modele pot fi necesare intrari multiple sau se pot impune restrictii asupra unora dintre valorile de intrare sau asupra rezultatului. Analiza unui rezultat prin modificarea valorilor din una sau mai multe celule se poate face utilizand facilitatea Solver din Excel.
Pentru a putea fi folosita facilitatea Solver, ea trebuie mai intai instalata. Pentru a realiza acest lucru se vor executa urmatorii pasi:
1. Se aplica comanda Tools, Add-Ins. Pe ecran apare caseta Add-Ins (figura 12.1).
Figura 12.1 - caseta de dialog Add - Ins
2. Se marcheaza optiunea Solver.
3. Se selecteza butonul OK. Comanda Solver va aparea in meniul Tools.
Cu Solver-ul se pot rezolva tot felul de probleme de programare liniara si neliniara:
Probleme de maximizare a castigurilor
Probleme de transport
Probleme de amestec
Probleme de minimizare a costurilor
In principiu problemele care pot fi rezolvate au un singur obiectiv, pentru unele variabile sunt specificate restrictii, iar variabilele de intrare influenteaza direct sau indirect, atat restrictiile cat si valorile care trebuie optimizate.
Pentru utilizarea Solver-ului se vor efectua urmatorii pasi:
1. Se configureaza foaia de calcul si se stabileste care sunt celulele care contin variabilele de intrare si rezultatul.
2. Se aplica comanda Tools, Solvers. Pe ecran apare caseta de dialog Solver Parameters (figura 12.2).
Figura 12.2 - caseta de dialog Solver Parameters
In caseta text Set Target Cell se specifica celula care contine formula cu rezultatul care trebuie analizat (functia obiectiv). In sectiunea Equal To se specifica ce se urmareste pentru functia obiectiv:
Daca trebuie maximizata se selecteaza optiunea Max.
Daca trebuie minimizata se selecteaza optiunea Min.
Daca functia obiectiv trebuie sa atinga o anumita valoare se selecteaza optiunea Value of, iar in caseta text alaturata se introduce valoarea respectiva.
In caseta text By Changing Cells se vor indica celulele sau domeniul de celule pe care Solver-ul le va modifica pentru a obtine valoarea optima.
Caseta Subject to the Constraints va contine restrictiile problemei. Pentru a adauga o restrictie se aplica un clic pe butonul Add. Pe ecran apare o noua caseta de dialog, Add Constraint (figura 12.3) in care se pot introduce restrictiile problemei:
Figura 12.3 - caseta de dialog Add Constraint
Pentru a stabili o restrictie:
In caseta text Cell Reference se va specifica celula care contine formula pe care se bazeaza restrictia.
Se aplica un clic pe sageata de derulare pentru a vedea lista cu operatori pentru restrictii si se selecteaza operatorul corespunzator.
In ultima caseta text se scrie valoarea restrictiei ce trebuie respectata.
Se aplica un clic pe butonul Add pentru a introduce si alte restrictii.
Pentru a reveni in caseta de dialog Solver Parameters se aplica un clic pe butonul OK. Restrictiile definite vor fi afisate in caseta Subject to the Constraints.
3. Se aplica un clic pe butonul Solver. Solver-ul va incepe efectuarea calculelor pentru solutiile optime. Dupa ce gaseste o solutie, pe ecran apare caseta de dialog Solver Results (figura 12.4).
Figura 12.4 - caseta de dialog Solver Results
Excel introduce solutiile in foaia de calcul. Daca se alege optiunea Keep Solver Results Excel va pastra in foaia de calcul solutia calculata.
Daca se selecteaza optiunea Restore Original Values, se va reveni la valorile initiale din foaia de calcul. Pentru sintetizarea rezultatelor gasite, Solver-ul permite generarea a trei tipuri de rapoarte:
Answer - in care sunt prezentate valorile initiale si finale pentru celula rezultat si celulele care contin variabilele de intrare.
Sensitivity - in care este prezentata o analiza de senzitivitate a variabilelor de intrare (cum variaza rezultatul la diferite modificari ale variabilelor de intrare).
Limits - se specifica intre ce limite pot varia limitelele impuse in restrictii, astfel incat rezultatul final sa nu se modifice.
Pentru a crea un raport se selecteaza denumirea lui din lista Reports din caseta Solver Results. Pentru a selecta mai multe rapoarte din lista se alege primul raport, se tine apasata tasta <Ctrl> si se aplica un clic pe unul sau ambele rapoarte ramase. Se aplica un clic pe butonul OK. Excel va crea fiecare raport intr-o foaie de calcul separata.
Utilizatorul poate specifica tehnica utilizata de programul Solver pentru gasirea raspunsurilor, precizia raspunsurilor si perioada de lucru a programului Solver.
Pentru a realiza acest lucru din caseta de dialog Solver Parameters se selecteaza butonul Options. Pe ecran apare caseta de dialog Solver Options (figura 12.5). Folosind optiunile din aceasta caseta de dialog se poate stabili modulul in care va lucra Solver-ul.
In caseta Max Time se specifica timpul maxim (in secunde) pe care programul Solver poate sa il foloseasca pentru gasirea unei solutii.
In caseta Iterations se specifica numarul maxim de iteratii pe care le poate face Solver-ul.
In caseta Precision se specifica cat de apropiate trebuie sa fie doua incercari de solutie, inainte de a declara gasita cea mai buna solutie.
In caseta Tolerance se specifica (in procente) cat de aproape de cea mai buna solutie trebuie sa fie raspunsul, atunci cand se lucreaza cu probleme cu numere intregi. Stabilirea unei tolerante mai mari poate mari considerabil viteza de calcul atunci cand se lucreaza cu probleme complexe cu numere intregi
Optiunea Assume Linear Model configureaza Solver-ul sa utilizeze o metoda de programare liniara pentru gasirea solutiei. Daca foaia de calcul contine o problema neliniara apare un mesaj de avertisment.
Optiunea Show Iterations Results permite afisarea solutiilor intermediare. Continuarea se face apasand pe butonul Continue, oprirea pe butonul Stop.
Optiunile Tangent sau Quadratic sunt metode aditionale folosite pentru gasirea solutiei. Se recomanda utilizarea optiunii Quadratic daca foaia de calcul contine formule complexe care sunt neliniare.
Optiunile din sectiunea Derivatives permit specificarea metodei de derivare partiala folosite.
Optiunile din sectiunea Search permit specificarea metodei de cautare folosita.
Figura 12.5 - caseta de dialog Solver Options
O companie are trei fabrici in localitatile A, B, C. Produsele realizate in aceste fabrici sunt distribuite din localitatile S si P. Compania analizeaza posibilitatea de amplasare a unui nou depozit in localitatea R. Analizele efectuate au stabilit urmatoarele costuri de transport:
Costul transportului in depozitele din:
Fabrici
S
P
R
A
10
14
8
B
12
10
12
C
8
12
10
Capacitatile de productie la fabricile din localitatile A, B, C sunt de 20, 30 si 40 unitati pe saptamana.
Se estimeaza ca depozitul din R va absolvi 20 de unitati pe saptamana iar cele din S si P, 40 respectiv 30 de unitati pe saptamana.
Sa se determine modul de distributie a produselor la depozite astfel incat costurile de transport sa fie minime.
Modelul va avea 9 variabile:
x1 - numarul de produse transportate de la fabrica A la depozitul S
x2 - numarul de produse transportate de la fabrica A la depozitul P
x3 - numarul de produse transportate de la fabrica A la depozitul R
x4 - numarul de produse transportate de la fabrica B la depozitul S
x5 - numarul de produse transportate de la fabrica B la depozitul P
x6 - numarul de produse transportate de la fabrica B la depozitul R
x7 - numarul de produse transportate de la fabrica C la depozitul S
x8 - numarul de produse transportate de la fabrica C la depozitul P
x9 - numarul de produse transportate de la fabrica C la depozitul R
Restrictiile modelului sunt:
1. x1+x2+x3<=20 (Productia din fabrica A sa nu depaseasca capacitatea de productie)
2. x4+x5+x6<=30 (Productia din fabrica B sa nu depaseasca capacitatea de productie)
3. x7+x8+x9<=40 (Productia din fabrica C sa nu depaseasca capacitatea de productie) C)
4. x1+x4+x7<=20 (numarul de produse transportate in depozitul A sa fie mai mare decat cererea la depozitul respectiv)
5. x2+x5+x8>=40 (numarul de produse transportate in depozitul B sa fie mai mare decat cererea la depozitul respectiv )
6. x5+x6+x9>=30 (numarul de produse transportate in depozitul C sa fie mai mare decat cererea la depozitul respectiv )
Functia obiectiv a modelului este minimizarea cheltuielilor de transport. Cheltuielile de transport sunt egale cu 10x1+14x2+8x3+12x4+10x5+12x6+8x7+12x8+10x9
Pentru rezolvarea acestei probleme se va configura foaia de calcul in modul urmator (figura 12.6):
Figura 12.6
Celulele din meniul C3:E5 vor contine variabilele modelului. La inceput aceste celule se vor completa cu date aleatoare, sa presupunem ca toate variabilele sunt egale cu 1. In urma rezolvarii problemei in aceste celule se va obtine rezultatul.
In celulele B3, B4, B5 se va calcula productia totala realizata in fiecare fabrica.
In B3 se va introduce formula =SUM(C3:E3) si se copiaza formula in B4 si B5.
In celulele C6, D6, E6, se va calcula productia depozitata in fiecare depozit. In celula C6 se va introduce formula =SUM(C3:C5). Aceasta formula se copiaza in D6 si E6.
Celulele B10, B11, B12 vor contine capacitatile de productie la fabricile A, B, C.
Celulele C13, D13, E13 vor contine cererea la cele 3 depozite.
In domeniul C10:E12 se vor introduce cheltuielile de transport pe unitatea de produs de la fiecare fabrica la fiecare depozit.
In celulele C15, D15, E15 se calculeaza cheltuielile de transport la fiecare depozit. In celula C15 se introduce formula =C3*C10+C4*C11+C5*C12 (Numarul de produse transportate de la fabrica A la depozitul S * cheltuielile de transport + numarul de produse transportate de la fabrica B la depozitul S * cu cheltuielile de transport + numarul de produse transportate de la fabrica C la depozitul S * cheltuielile de transport.). Aceasta formula se copiaza in celulele d15 si E15.
In celula B15 se vor calcula cheltuielile cu transportul. In aceasta celula se va introduce formula = SUM( C15:E15).
Restrictiile problemei pentru foaia de calcul proiectata vor fi:
1. B3<=B10 Productia din fabrica A sa nu depaseasca capacitatea de productie.
2. B4<=B11 Productia din fabrica B sa nu depaseasca capacitatea de productie
3. B5<=B12 Productia din fabrica sa nu depaseasca capacitatea de productie
4. C6<=C13 sa nu existe rupere de stoc la depozitul S
5. D6<=D13 sa nu existe rupere de stoc la depozitul P
6. E6<=E13 sa nu existe rupere de stoc la depozitul R
7. C3:E5<=0 Toate variabilele sa fie numere negative
Dupa ce foaia de calcul a fost configurata din meniul Tools se aplica comanda Solver.
Obiectivul problemei este minimizarea cheltuielilor totale de transport. Aceasta valoare este calculata in celula B15, deci in Set Target Cell se introduce B15. Functia trebuie minimizata deci se va alege optiunea Min.
Celulele care contin variabilele de intrare sunt in domeniu C3:E5, deci in By Changing Cells se introduce C3:E5.
In caseta Subject to the Constrains sevor introduce restrictiile problemei:
Se selecteaza butonul Add.
In caseta Cell Reference se introduce B3.
Din lista cu operatori se selecteaza "s.
In caseta Constraint se introduce B10.
Se selecteaza butonul Add.
In mod similar se introduc si celelalte restrrictii.
In final caseta Solver va fi completata ca in figura 12.7
Figura 12.7
Se selecteaza butonul Options. In caseta de dialog Solver Options se marcheaza optiunile Assume Linear Model si Assume Non Negative. Se selecteaza butonul OK.
In acest moment se poate selecta in caseta de dialog Solver Parameters butonul Solve.
In caseta Solver Results se selecteaza optiunea Keep Solver Solutions si cele trei rapoarte. Se selecteaza butonul OK.
Solver-ul rezolva problema. Rezultatul obtinut este:se vor transporta 20 de produse de la fabrica A la depozitul R, 30 de produse de la fabrica B la depozitul P, 20 de produse de la fabrica C la depozitul S, 10 de produse de la fabrica C la depozitul P 10 de produse de la fabrica C la depozitul R.
