Excel 12

Utilizarea Solver-ului

CAPITOLUL 12 UTILIZAREA SOLVER-ULUI 12.1. Solver - ul Cu comanda Goal Seek se poate analiza varia\ia rezultatelor ]n func\ie de o singur[ celul[ de pornire. Multe foi de calcul sunt prea complexe pentru comanda Goal Seek. La unele modele pot fi necesare intr[ri multiple sau se pot impune restric\ii asupra unora dintre valorile de intrare sau asupra rezultatului. Analiza unui rezultat prin modificarea valorilor din una sau mai multe celule se poate face utiliz`nd facilitatea Solver din Excel. Pentru a putea fi folosit[ facilitatea Solver, ea trebuie mai ]nt`i instalat[. Pentru a realiza acest lucru se vor executa urm[torii pa=i: 1. Se aplic[ comanda Tools, Add-Ins. Pe ecran apare caseta Add-Ins (figura 12.1).

Figura 12.1 – caseta de dialog Add - Ins 2. Se marcheaz[ op\iunea Solver. 3. Se selectez[ butonul OK. Comanda Solver va aparea ]n meniul Tools. Cu Solver-ul se pot rezolva tot felul de probleme de programare liniar[ =i neliniar[: • Probleme de maximizare a c`=tigurilor • Probleme de transport • Probleme de amestec • Probleme de minimizare a costurilor Aceste tipuri de probleme vor fi tratate ]n detaliu ]n partea a doua – Modelarea deciziilor utiliz`nd foile de calcul. }n principiu problemele care pot fi rezolvate au un singur obiectiv, pentru unele variabile sunt specificate restric\ii, iar variabilele de intrare influen\eaz[, direct sau indirect, at`t restric\iile c`t =i valorile care trebuie optimizate. Pentru utilizarea Solver-ului se vor efectua urm[torii pa=i:

Modelarea deciziilor utilizând foile de calcul 1. Se configureaz[ foaia de calcul =i se stabile=te care sunt celulele care con\in variabilele de intrare =i rezultatul. 2. Se aplic[ comanda Tools, Solvers. Pe ecran apare caseta de dialog Solver Parameters (figura 12.2).

Figura 12.2 – caseta de dialog Solver Parameters }n caseta text Set Target Cell se specific[ celula care con\ine formula cu rezultatul care trebuie analizat (func\ia obiectiv). }n sec\iunea Equal To se specific[ ce se urm[re=te pentru func\ia obiectiv: • Dac[ trebuie maximizat[ se selecteaz[ op\iunea Max. • Dac[ trebuie minimizat[ se selecteaz[ optiunea Min. • Dac[ func\ia obiectiv trebuie s[ ating[ o anumit[ valoare se selecteaz[ op\iunea Value of, iar ]n caseta text al[turat[ se introduce valoarea respectiv[. }n caseta text By Changing Cells se vor indica celulele sau domeniul de celule pe care Solver-ul le va modifica pentru a ob\ine valoarea optim[. Caseta Subject to the Constraints va con\ine restric\iile problemei. Pentru a ad[uga o restric\ie se aplic[ un clic pe butonul Add. Pe ecran apare o nou[ caset[ de dialog, Add Constraint (figura 12.3) ]n care se pot introduce restric\iile problemei:

Figura 12.3 – caseta de dialog Add Constraint Pentru a stabili o restric\ie: • }n caseta text Cell Reference se va specifica celula care con\ine formula pe care se bazeaz[ restric\ia.

Utilizarea Solver-ului Se aplic[ un clic pe s[geata de derulare pentru a vedea lista cu operatori pentru restric\ii =i se selecteaz[ operatorul corespunz[tor. • }n ultima caset[ text se scrie valoarea restric\iei ce trebuie respectat[. • Se aplic[ un clic pe butonul Add pentru a introduce =i alte restric\ii. • Pentru a reveni ]n caseta de dialog Solver Parameters se aplic[ un clic pe butonul OK. Restric\iile definite vor fi afi=ate ]n caseta Subject to the Constraints. 4. Se aplic[ un clic pe butonul Solver. Solver-ul va ]ncepe efectuarea calculelor pentru solu\iile optime. Dup[ ce g[se=te o solu\ie, pe ecran apare caset[ de dialog Solver Results (figura 12.4). •

Figura 12.4 – caseta de dialog Solver Results Excel introduce solu\iile ]n foaia de calcul. Dac[ se alege op\iunea Keep Solver Results Excel va p[stra ]n foaia de calcul solu\ia calculat[. Dac[ se selecteaz[ op\iunea Restore Original Values, se va reveni la valorile ini\iale din foaia de calcul. Pentru sintetizarea rezultatelor g[site, Solver-ul permite generarea a trei tipuri de rapoarte: • Answer - ]n care sunt prezentate valorile ini\iale =i finale pentru celula rezultat =i celulele care con\in variabilele de intrare. • Sensitivity - ]n care este prezentat[ o analiz[ de senzitivitate a variabilelor de intrare (cum variaz[ rezultatul la diferite modific[ri ale variabilelor de intrare). • Limits - se specific[ ]ntre ce limite pot varia limitelele impuse ]n restric\ii, astfel ]ncât rezultatul final s[ nu se modifice. Mai multe detalii despre aceste rapoarte sunt prezentate ]n partea a-II-a – Modelarea deciziilor utiliz`nd foile de calcul. Pentru a crea un raport se selecteaz[ denumirea lui din lista Reports din caseta Solver Results. Pentru a selecta mai multe rapoarte din list[, se alege primul raport, se \ine ap[sat[ tasta =i se aplic[ un clic pe unul sau ambele rapoarte r[mase. Se aplic[ un clic pe butonul OK. Excel va crea fiecare raport ]ntr-o foaie de calcul separat[.

12.2. Modificarea configuraţiei Solver-ului Utilizatorul poate specifica tehnica utilizat[ de programul Solver pentru g[sirea r[spunsurilor, precizia r[spunsurilor =i perioada de lucru a programului Solver. Pentru a realiza acest lucru din caseta de dialog Solver Parameters se selecteaz[ butonul Options. Pe ecran apare caseta de dialog Solver Options (figura 12.5). Folosind op\iunile din aceast[ caset[ de dialog se poate stabili modulul ]n care va lucra Solver-ul. }n caseta Max Time se specific[ timpul maxim (]n secunde) pe care programul Solver poate s[ ]l foloseasc[ pentru g[sirea unei solu\ii.

Modelarea deciziilor utilizând foile de calcul }n caseta Iterations se specific[ num[rul maxim de itera\ii pe care le poate face Solver-ul. }n caseta Precision se specific[ c`t de apropiate trebuie s[ fie dou[ ]ncerc[ri de solu\ie, ]nainte de a declara g[sit[ cea mai bun[ solu\ie. }n caseta Tolerance se specific[ (]n procente) c`t de aproape de cea mai bun[ solu\ie trebuie s[ fie r[spunsul, atunci c`nd se lucreaz[ cu probleme cu numere ]ntregi. Stabilirea unei toleran\e mai mari poate m[ri considerabil viteza de calcul atunci c`nd se lucreaz[ cu probleme complexe cu numere ]ntregi Op\iunea Assume Linear Model configureaz[ Solver-ul s[ utilizeze o metod[ de programare liniar[ pentru g[sirea solu\iei. Dac[ foaia de calcul con\ine o problem[ neliniar[, apare un mesaj de avertisment. Op\iunea Show Iterations Results permite afi=area solu\iilor intermediare. Continuarea se face ap[s`nd pe butonul Continue, oprirea pe butonul Stop. Op\iunile Tangent sau Quadratic sunt metode adi\ionale folosite pentru g[sirea solu\iei. Se recomand[ utilizarea op\iunii Quadratic dac[ foaia de calcul con\ine formule complexe care sunt neliniare. Op\iunile din sec\iunea Derivatives permit specificarea metodei de derivare par\ial[ folosite. Op\iunile din sec\iunea Search permit specificarea metodei de c[utare folosit[.

Figura 12.5 – caseta de dialog Solver Options

12.3. Aplicaţie O companie are trei fabrici ]n localit[\ile A, B, C. Produsele realizate ]n aceste fabrici sunt distribuite din localit[\ile S =i P. Compania analizeaz[ posibilitatea de amplasare a unui nou depozit ]n localitatea R. Analizele efectuate au stabilit urm[toarele costuri de transport: Costul Transportului ]n depozitele din: Fabrici S P R A 10 14 8 B 12 10 12 C 8 12 10 Capacit[\ile de produc\ie la fabricile din localit[\ile A, B, C sunt de 20, 30 =i 40 unit[\i pe s[pt[m`n[. Se estimeaz[ c[ depozitul din R va absolvi 20 de unit[\i pe s[pt[m`n[, iar cele din S =i P, 40 respectiv 30 de unit[\i pe s[pt[m`n[.

Utilizarea Solver-ului S[ se determine modul de distribu\ie a produselor la depozite astfel ]nc`t costurile de transport s[ fie minime. Modelul va avea 9 variabile: x1 - num[rul de produse transportate de la fabrica A la depozitul S x2 - num[rul de produse transportate de la fabrica A la depozitul P x3 - num[rul de produse transportate de la fabrica A la depozitul R x4 - num[rul de produse transportate de la fabrica B la depozitul S x5 - num[rul de produse transportate de la fabrica B la depozitul P x6 - num[rul de produse transportate de la fabrica B la depozitul R x7 - num[rul de produse transportate de la fabrica C la depozitul S x8 - num[rul de produse transportate de la fabrica C la depozitul P x9 - num[rul de produse transportate de la fabrica C la depozitul R Restric\iile modelului sunt: 1. x1‡x2‡x3<=20 (Produc\ia din fabrica A sa nu dep[=easc[ capacitatea de productie) 2. x4‡x5‡x6<=30 (Produc\ia din fabrica B sa nu dep[=easc[ capacitatea de productie) 3. x7‡x8‡x9<=40 (Produc\ia din fabrica C sa nu dep[=easc[ capacitatea de productie) C) 4. x1‡x4‡x7>=20 (numarul de produse transportate în depozitul A sã fie mai mare decât cererea la depozitul respectiv) 5. x2‡x5‡x8 >=40 (numarul de produse transportate în depozitul B sã fie mai mare decât cererea la depozitul respectiv ) 6. x5‡x6‡x9 >=30 (numarul de produse transportate în depozitul C sã fie mai mare decât 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 în modul urmãtor (figura 12.6):

Figura 12.6

Modelarea deciziilor utilizând foile de calcul Celulele din meniul C3:E5 vor con\ine variabilele modelului. La ]nceput aceste celule se vor completa cu date aleatoare, s[ presupunem c[ toate variabilele sunt egale cu 1. }n urma rezolv[rii problemei ]n aceste celule se va ob\ine rezultatul. }n celulele B3, B4, B5 se va calcula produc\ia total[ realizat[ ]n fiecare fabric[. }n B3 se va introduce formula =SUM(C3:E3) =i se copiaz[ formula ]n B4 =i B5. }n celulele C6, D6, E6, se va calcula produc\ia depozitat[ ]n fiecare depozit. }n celula C6 se va introduce formula =SUM(C3:C5). Aceast[ formul[ se copiaz[ ]n D6 =i E6. Celulele B10, B11, B12 vor con\ine capacit[\ile de produc\ie la fabricile A, B, C. Celulele C13, D13, E13 vor con\ine cererea la cele 3 depozite. }n domeniul C10:E12 se vor introduce cheltuielile de transport pe unitatea de produs de la fiecare fabric[ la fiecare depozit. }n celulele C15, D15, E15 se calculeaz[ cheltuielile de transport la fiecare depozit. }n celula C15 se introduce formula=C3*C10+C4*C11+C5*C12 (Num[rul de produse transportate de la fabrica A la depozitul S * cheltuielile de transport + num[rul de produse transportate de la fabrica B la depozitul S * cu cheltuielile de transport + num[rul de produse transportate de la fabrica C la depozitul S * cheltuielile de transport.). Aceast[ formul[ se copiaz[ ]n celulele d15 =i E15. }n celula B15 se vor calcula cheltuielile cu transportul. }n aceast[ celul[ se va introduce formula = SUM( C15:E15). Restric\iile problemei pentru foaia de calcul proiectat[ vor fi: 1. B3<=B10 Produc\ia din fabrica A s[ nu dep[=easc[ capacitatea de produc\ie. 2. B4<=B11 Produc\ia din fabrica B s[ nu dep[=easc[ capacitatea de produc\ie 3. B5<=B12 Produc\ia din fabrica s[ nu dep[=easc[ capacitatea de produc\ie 4. C6>=C13 s[ nu existe rupere de stoc la depozitul S 5. D6>=D13 s[ nu existe rupere de stoc la depozitul P 6. E6>=E13 s[ nu existe rupere de stoc la depozitul R 7. C3:E5>=0 Toate variabilele s[ fie numere negative Dup[ ce foaia de calcul a fost configurat[, din meniul Tools se aplic[ comanda Solver. Obiectivul problemei este minimizarea cheltuielilor totale de transport. Aceast[ valoare este calculat[ ]n celula B15, deci ]n Set Target Cell se introduce B15. Func\ia trebuie minimizat[, deci se va alege op\iunea Min. Celulele care con\in variabilele de intrare sunt ]n domeniu C3:E5, deci ]n By Changing Cells se introduce C3:E5. }n caseta Subject to the Constrains sevor introduce restric\iile problemei: • Se selecteaz[ butonul Add. • }n caseta Cell Reference se introduce B3. • Din lista cu operatori se selecteaz[ <=. • }n caseta Constraint se introduce B10. • Se selecteaz[ butonul Add. • }n mod similar se introduc =i celelalte restrric\ii. }n final caseta Solver va fi completat[ ca ]n figura 12.7

Utilizarea Solver-ului

Figura 12.7 Se selecteaz[ butonul Options. }n caseta de dialog Solver Options se marcheaz[ op\iunile Assume Linear Model =i Assume Non Negative. Se selecteaz[ butonul OK. }n acest moment se poate selecta ]n caseta de dialog Solver Parameters butonul Solve. }n caseta Solver Results se selecteaz[ op\iunea Keep Solver Solutions =i cele trei rapoarte. Se selecteaz[ butonul OK. Solver-ul rezolv[ problema. Rezultatul ob\inut 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.