TABELAREA FUNCTIILOR

 Exemple de formule matriceale:, , . 

            Rezolvarea de probleme (comenzile Goal Seek si Solver)

Prin meniul Tools, programul Excel ofera comenzile Goal Seek (cautare rezultat) si Solver (rezolvare) cu ajutorul carora utilizatorul are instrumente puternice pentru rezolvarea  diverselor si complexelor probleme practice, unele fiind reprezentate de probleme matematice complicate (optimizari si ecuatii) :

probleme privind determinarea extremelor unor functii (maxime, minime) ;

probleme de programare liniara si neliniara ;

rezolvarea ecuatiilor si sistemelor de ecuatii ;

probleme diverse de matematica, fizica, chimie, economie, etc.

Observatie. Daca in meniul Tools nu este oferita comanda Solver, utilizatorul trebuie sa selecteze Tools /Add-Ins prin care se va deschide fereastra de dialog Add-Ins ce ofera lista derulanta Add-Ins available ce contine diverse comenzi (facilitati) oferite de programul Excel ce nu apar in meniul Tools, dar care pot fi inserate prin selectare si validare. Se va selecta si valida Solver Add-in (Tool for optimization and equation solving) si se va actiona butonul OK, astfel ca in meniul Tools va aparea comanda Solver

            Comanda Goal Seek (rezolvarea de ecuatii) 

            Folosind comanda Goal Seek, utilizatorul poate sa determine valoarea argumentului unei functii f(x) atunci cand este cunoscut rezultatul y returnat de functie. In formulare matematica inseamna ca daca este cunoscuta valoarea y returnata de functia data f(x), se poate determina valoarea argumentului x astfel ca y=f(x). De fapt, este rezolvata ecuatia f(x) = y , unde x este necunoscuta, y este cunoscut, iar functia f este data.   

            Exemplu. Daca f(x) = x² + sin(x), sa determinam o valoare x pentru care f(x)=75.

Pentru acest lucru, intr-o foaie de calcul, in celula B3 introducem o valoare oarecare pentru x, de exemplu 5, iar in celula B4 scriem formula ce reprezinta definitia functiei, adica « =B3*B3 + SIN(B3) », valoarea returnata y  va fi cea care apare in imaginea urmatoare. Pentru a determina o valoare a lui x cand y=75 astfel ca f(x 75, se va selecta Tools / Goal Seek care va afisa fereastra Goal Seek. In linia de introducere Set cell se tasteaza sau se indica cu mouse-ul referinta la celula care contine formula pentru functia f(x), iar in linia de introducere To value se tasteaza y pentru care se doreste determinarea lui x. In linia de introducere By changing cell se tasteaza sau se indica cu mouse-ul referinta la celula care va contine rezultatul pentru argumentul x al functiei date (valoarea celulei va fi modificata pana cand se va realiza relatia f(x y). Se va actiona butonul OK.

            Prin actionarea butonului OK, valoarea calculata a argumentului va inlocui valoarea initiala a argumentului in celula B3. Daca se opteaza pentru butonul Cancel, atunci se revine la valoarea originala a argumentului. In unele cazuri (depinde de complexitatea ecuatiei), daca timpul de cautare este ceva mai mare, se poate folosi comanda Step (executie « pas cu pas ») sau se poate folosi comanda Pause/Continue (intrerupere/continuare proces de cautare). In final, va fi afisata fereastra de dialog Goal Seek Status unde se afiseaza valoarea y dorita a functiei (Target value) si valoarea determinata pentru argumentul x (Current value).

Comanda  Solver (rezolvarea de probleme)

      Probleme diverse cu enunturi clare (extreme de functii, programare liniara si neliniara, ecuatii si sisteme, etc.) se pot rezolva prin comanda Tools / Solver, dar numai dupa ce problema respectiva a fost reprezentata (modelata) intr-o foaie de calcul.

Specificarea parametrilor problemei ce trebuie rezolvata se face in ferestra Solver Parameter oferita de comanda Solver :

            Operatiile (rezolvarile) realizate de comanda Solver sunt de tip optimizare (Max, Min ; maxime, minime) si de tip calcul direct (Value of).

Linia de introducere Set Target Cell trebuie sa contina referinta la o celula care memoreaza o formula care va returna o valoare optimizata sau calculata direct conform optiunilor privind natura operatiei (Equal To :) oferite de urmatoarele butoane :

        Max - determina valoarea maxima a valorii returnate de formula specificata ;

        Min - determina valoarea minima a valorii returnate de formula specificata ;

        Value of - determina valoarea exacta a actiunii formulei specificate fata de valoarea introdusa.

Argumentele functiei care se prelucreaza sunt specificate prin referinte in linia de introducere By Changing Cells. Valoarea extrema sau exacta a functiei prelucrate se obtine prin modificarea valorilor (valori de start) celulelor referentiate in acest camp. Celulele ale caror valori se modifica pot fi situate in regiuni compacte sau in regiuni distincte. Folosind butonul Guess, programul Excel analizeaza structura foii de calcul si incarca o regiune pe care o propune in linia By Changing Cells, utilizatorul avand posibilitatea s-o accepte sau nu.

            Subfereastra Subject to the Constraints permite specificarea eventualelor restrictii referitoare la celulele care se modifica (argumentele finale ale functiei). Numarul restrictiilor nu este limitat. Asupra listei de restrictii se poate actiona prin intermediul a trei butoane aflate in partea dreapta acestei subferestre :

        Add - adaugarea la lista a unei noi restrictii ; se va afisa ferestra de dialog Add Constraints ce ofera linia de introducere Cell Reference in care se va introduce o referinta la o celula pentru care se doreste o restrictie, si linia de introducere Constraint in care se va introduce fie o valoare, fie o referinta la o celula ; din lista ascunsa aferenta primei linii de introducere, se alege tipul operatorului de comparatie (<=, = , >=, int, bin) ; restrictia va fi adaugata daca se actioneaza butonul OK ; daca se doreste adaugarea mai multor restrictii se actioneaza butonul Add ; in final, se actioneaza butonul OK ;

        Change - modificarea unei restrictii din lista restrictiior ; dupa ce se selecteaza din lista restrictia dorita pentru a fi modificata, se efectueaza click, dupa care se  actioneaza butonul Change care afiseaza fereastra de dialog Change Constraint asemanatoare ferestrei Add Constraint ;

        Delete - stergerea unei restrictii din lista restrictiilor ; dupa ce se selecteaza din lista restrictia dorita pentru a fi eliminata, se efectueaza click, dupa care se actioneaza butonul Delete.

 Ferestra de dialog Solver Parameters ofera si urmatoarele cinci butoane :

Solve - lansarea in executie a procesului pentru determinarea rezultatului problemei reprezentate ; se va afisa fereastra de dialog Solver Results unde se va indica daca s-a gasit sau nu o solutie pentru problema data ; in cazul gasirii unei solutii (Solver found a solution) se poate valida butonul Keep Solver Solution pentru a inlocui valoarea de start cu solutia gasita, sau se poate valida butonul Restore Original Values pentru a inlocui valoarea de start cu solutia gasita ; daca se actioneaza butonul Save Scenario, atunci solutia gasita va fi memorata sub forma unui scenariu ce va avea un nume dat de utilizator si care ulterior poate fi prelucrat folosind comanda Scenarios din meniul Tools ; se poate opta pentru generarea diferitelor tipuri de rapoarte (Answer-rezultat, Sensitivity-sensibilitate, Limits-limite) ce pot fi selectate din lista derulanta Reports ;

Close - inchiderea ferestrei de dialog Solver Parameters ;

Options - afisarea unei ferestre de dialog pentru selectarea unor optiuni de lucru ;

Reset All - anularea tuturor actiunilor efectuate in fereastra Solver Parameters ;

Help - asistenta software.

Exemplu. Sa se determine maximul functiei f(x,y) = 7x + 5y, daca trebuie sa se satisfaca restrictiile : x e [0, 3000], y e [0, 5000],   x+y/9 e [0, 3000]

Intr-o foaie de calcul, in celula B3 care se va numi x se memoreaza o valoare initiala, de exemplu 0, in celula B4 care se va numi y se memoreaza o valoare initiala, de exemplu 0, iar in celula B5 se   scrie formula « =7*x+5*y ». In celula B7 se scrie formula « x+y/9 » pentru a impune restrictia din enunt. Se selecteaza comanda Tools / Solver , iar completarea ferestrei Solver Parameters se va face asa cum se vede in imagine. Dupa completare (se va utiliza butonul Add pentru introducerea restrictiilor) se actioneaza butonul Solve care gaseste rezultatul afisat corespunzator pentru x, y si f(x,y).

            Aplicatie. O companie vinde zilnic trei tipuri de produse notate prin p1, p2, p3 pentru care preturile unitare sunt 15000 lei, 18000 lei, 10000 lei, iar adaosurile comerciale corespunzatoare celor trei produse sunt 25%, 10% si 20%. Cantitatile minime vandute zilnic sunt evaluate la 250, 100, respectiv 120 bucati, iar cantitatile maxime vandute zilnic sunt evaluate la 800, 1000, respectiv 850 bucati. Tinand  seama de conditiile de depozitare, compania poate vinde zilnic cel mult 1900 bucati in total. Sa se determine ce cantitati din fiecare produs trebuie vandute pentru ca profitul total obtinut sa fie maxim.

            Intr-o foaie de calcul se introduc elementele necesare asa cum se poate vedea din imaginea de mai jos. Profitul pentru fiecare produs se calculeaza prin formula 

               P = x * y * z , unde x, y, z

reprezinta cantitatea vanduta, pretul unitar, respectiv adaosul comercial.

            Profitul total este suma profiturilor celor trei produse vandute. In celulele D4 :F4 se introduc cantitatile minime pentru fiecare produs in parte, iar in celulele D5 :F5 se introduc cantitatile maxime. In celulele D8 :F8 se introduc valori oarecare (de start) dar care sa verifice restrictiile impuse in enuntul problemei. Acestea vor fi valorile (argumentele) ce se vor modifica pentru determinarea valorii maxime a profitului (functiei).

            In celulele D9 :F9 se vor introduce preturile unitare, iar in celulele D10 :F10 se vor introduce adaosurile comerciale, evident pentru fiecare produs.

Formulele ce trebuie scrise sunt prezentate in tabelul urmator :

            Pentru calculul profitului total trebuie sa fie verificate restrictiile impuse in enuntul problemei :

        totalul cantitatii sa fie cel mult 1900 ( $D$14 <= 1900) ;

        cantitatile vandute din cele trei produse trebuie sa fie cuprinse intre cantitatile minime si maxime ( a se vedea imaginea precedenta).

Se selecteaza Tools / Solver si se completeaza conform celor de mai sus. Linia de introducere Set Target Cell se completeaza cu $D$13, iar linia de introducere By Changing Cells se completeaza cu $D$8 :$F$8 (regiune in care se vor modifica valorile in procesul rezolvarii).

      Pentru introducerea restrictiilor se va utiliza butonul Add.

      Dupa introducerea tuturor restrictiilor, se va actiona butonul Solve care determina gasirea solutiei, si anume in celula D13 se va obtine profitul maxim, iar in regiune D8 :F8 se vor gasi valorile cantitatilor pentru care profitul este maxim.