Lectia 16 Alte comenzi speciale (Cautarea rezultatului si Rezolvitor)

Scopul lectiei

Ce înseamna Cautare rezultat (Goal-Seek)

Operatiunea prin care se afla noua dobânda se numeste Cautare rezultat (Goal seek). De fapt am stabilit rezultatul la 150 lei (atâta vreau eu) si îi cer EXCEL-lui sa-mi spuna ce dobânda este necesara. Dobânda nu este decât o componenta a acestui rezultat pe lânga numarul de ani si suma depusa, adica este o variabila.

Cum se obtine rezultatul. (Tehnica valorii scop)

Exista un scop sau obiectiv pe care dorim sa-l realizam. Vreau 150 lei depozit final.

Exista niste parametri pe care mi fixez si doresc ca EXCEL-ul sa-mi spuna ce valoare vor trebuie sa aiba, pentru ca scopul sa fie realizat.

Depunem 100 lei si peste 5 ani dorim sa avem un depozit de 150 lei. Ce dobânda ar trebui sa ne acorde banca?

.

Fig 16.1 bis

Scopul este realizarea depozitului. Parametrul care depinde de scop si prin care actionam este dobânda anuala, pe care am dori sa ne-o acorde banca. Initial punem dobânda anuala 4%. În cele mai multe cazuri EXCEL-ul actioneaza numai asupra unui parametru: dobânda sau nr de ani (perioada) 11511q164l . Functia este tot FV si rezulta 121,67 lei. Nu ne satisface, dorim 150 lei. Procedam astfel:

Instrumente Cautare rezultat si obtinem Fig 16.2.

Fig 16.2

Lucrurile nu se limiteaza la atât! Limitez dobânda si cer sa-mi spuna perioada.

Daca nu pot mari dobânda si nici perioada, intervin la suma initiala. Asa merge!

Concluzie

În comanda Cautare rezultat referitoare numai la exemplul de mai sus, câmpul tinta este depozit final si la un depozit final dat se poate ajunge modificând NUMAI UNA din cele 3 componente:

Suma initiala

Dobânda anuala

Numarul de ani

Atâta poate face Cautare Rezultat si de multe ori este suficient.

Exista o metoda prin care se pot schimba toate componentele de mai sus sau numai o parte din ele. Este vorba de Rezolvitor (Solver) .

Deosebirea dintre Tabelare si Cautare Rezultat

Amândoua metodele fac parte din marea categorie de simulari.

În Tabelare se pleaca de la niste date primare si se obtin o serie de date agregate, se obtin mai multe rezultate, stai cu ele în fata si le analizezi.

Într-o Cautare Rezultat intra o serie de date elementare asupra carora s-au facut niste calcule. Îti fixezi un Scop adica una din acele date care este rezultatul unei formule (a unor calcule) si ceri EXCEL-ului sa actioneze asupra unui parametru (o singura celula) care intra în formula de calcul a Scopului.

În Cautare Rezultat se stabileste tocmai o data agregata, adica o Data Finala si se cauta o data primara care sa permita ajungerea prin calcul la data agregata, stabilita initial. Se parcurge oarecum un sens invers ca la Tabelare.

Ce înseamna Rezolvitor (Solver)

Probabil estea cea mai puternica comanda din EXCEL. Cu ajutorul acestei comenzi pot fi rezolvate:

Probleme de programare liniara

Probleme de obtinere a valorilor minime si maxime a unor functii

Programare în multimea numerelor întregi etc.

Sa ne oprim cu enumerarile care pot speria pe cei neinitiati, cu toate ca uneori denumirile operatiilor sunt foarte pompoase, dar efectuarea lor nu este complicata.

Exemplu: reluam o problema rezolvata prin Cautare rezultat

Sa punem în functie Rezolvitorul pentru ca problema devine mai complicata daca conditiile (restrictiile) sunt acestea:

Cum se lucreaza cu Rezolvitorul

Instrumente Rezolvitor Parametri rezolvitor

Acum si aici un pic de atentie!

Vom avea o singura celula tinta, a carei valoare o fixam de la bun început

si una sau mai multe celule ce vor fi modificate.

Modificarea lor nu se face oricum, decât daca nu le punem nici o conditie. Daca le punem conditii (restrictii) se vor modifica respectând aceste restrictii.

S-ar putea sa se gaseasca solutii sau sa nu se gaseasca!

Stabilirea parametrilor înseamna:

Stabilirea celulei tinta B5

Stabilirea celulelor ce se vor modifica B1, B2, B3

Stabilirea restrictiilor la care se vor supune celulele care se modifica Fig 16.10

Fig 16.7 (Stabilirea celulelor care vor suferi modificari)

Adaugam prima restrictie:

Fig 16.8

Facem clic pe celula B2 alegem din meniul ascuns semnele <= si în casuta Restrictie tastam 5%. Daca mai avem si o alta restrictie facem clic pe Adaugare si obtinem Fig 16.9 unde adaugam o alta restrictie. Daca nu mai avem nici o restrictie, facem clic pe OK si obtinem Fig 16.10.

Fig 16.9 (Adaugarea celei de a doua restrictii)

Fig 16.10 (B1 nu are restrictie B2 si B3 au restrictii)

Verificam Fig 16.10 si ne convingem ca sunt exact parametri doriti de noi si clic pe Rezolvare. Daca si numai daca, EXCEL-ul a putut rezolva apare Fig 16.11.

fig 16.11 (Rezolvitorul a gasit o solutie)

A ramas de explicat ce-i cu optiunea Ghicire (Guest în engleza.). Am copiat din Help:

"Ghiceste toate celulele fara formule la care face referire formula din caseta Stabilire celula tinta si plaseaza referintele în caseta Prin modificarea celulelor."

Am încercat sa vad ce înseamna. În Fig 16.13 se vede ca depozitul final este de 121,55 lei. Daca micsorez numarul de ani, daca nu dau voie dobânzii sa treaca de 5% si suma initiala o marginesc la 110 lei, e de asteptat sa nu existe solutii.

Evident ca apare Fig 16.14.

Fig 16.13 (O setare "fortata")

16.14 (Nu exista solutie)

Rezolvitorul se ofera sa pastreze solutia gasita. Pare un non-sens, pentru ca a spus ca este imposibil de gasit o solutie. Da! Este imposibil de gasit o solutie pentru un depozit final de 150 lei, dar se poate pastra solutia data de Rezolvitor, aceea ca depozitul final sa fie de numai 127,34 lei. Daca nu ne convine solutia data, avem Refacere valori initiale.

Putem "insista", prin apelare la "Ghicire". Asta înseamna ca cerem Rezolvitorului sa mai caute si alte celule modificabile, doar, doar, ajunge la valoarea celulei tinta de 150 lei.

Fig 16.15 (Apelam la Ghicire)

Am facut clic pe Ghicire si singura modificare vizibila în Fig 16.15 este aceea ca în ferestruica Prin modificarea celulelor, domeniul s-a marit la $B$1:$B$4, fiindca a intrat si celula rata_anuala, care pâna acum era zero.

Cu alte cuvinte Rezolvitorul "ghiceste" ce alte celule pot fi modificate, în afara celor cerute initial de mine. Daca si celula B4 poate fi de acum încolo modificata, când am facut clic pe Rezolvare a aparut o valoare în B4, semn ca trebuie depusa o suma anuala (6,85 lei) pentru ca peste 3 ani cu o dobânda anuala de 5%, depozitul final sa fie de 150 lei cum s-a dorit de la început. De unde la început s-a zis ca prin modificarea celulelor B1, B2, B3 sa se gaseasca solutii, acum prin modificarea a 4 celule se va gasi în mod sigur o solutie.

Atentie:

Aceasta a patra celula NU ARE RESTRICŢII.

Neavând restrictii se ajunge în mod sigur la o solutie!

Fig 16.16 (S-a gasit o solutie)

Formula din celula B5 (celula tinta) este cea de mai jos:

Rata anuala intra în aceasta formula si de aceea apar modificari în ea.

Fig 16.16 bis

Odata ce redenumirea a fost facuta, Parametrii Rezolvitor apar ca în Fig 16.17.

Este foarte clar care celule sunt supuse restrictiilor.

În toate cazurile Rezolvitorul anunta:

• "Solutie gasita de Rezolvitor. Sunt satisfacute toate restrictiile si conditiile de optimizare." Sau daca nu s-a putut gasi,

• Imposibil de gasit o solutie cu Rezolvitorul

Importanta folosirii redenumirilor în acest caz

Fig 16.17 (Efectul Redenumirii câmpurilor din tabel)

Fig 16.18 (Pastrarea solutiei)

Pastrarea solutiilor

În Fig 16.19 a aparut o Foaie de lucru noua. Raport raspuns 1 a carei continut este în Fig 16.20. Foaia care a aparut cu raspunsul. Numele  foii este dat de utilizator.   Singurul lucru important este ca arata de la ce s-a plecat, ca unde s-a ajuns se vede în tabelul nou obtinut în urma actionarii Rezolvitorului. Fig 16.19

Solutia gasita poate fi pastrata sub forma unui raspuns. Raspunsul este pastrat pe o Foaie de lucru separata Fig 16.19 si Fig 16.20

Fig 16.20 (Continutul partial al foii Raspuns 1, lipseste antetul)

Fig 16.20 nu ne da cine stie ce informatii! În alte cazuri când sunt probleme complexe intereseaza Intervalul de eroare.

Intervalul de eroare este de 11,72 în fond este diferenta 115 - 103,22.

Starea poate fi de 2 feluri:

• de legare când este atinsa marginea restrictiei
• fara legare  când nu este atinsa marginea restrictiei.

Ma opresc aici. Explicatii mai largi, presupune cunostinte de statistica matematica si din pacate în liceele noastre nu se face asa ceva.

Personal nu sunt de acord în acest context, cu termenul de Intervalul de eroare. Nu este cazul sa se vorbeasca de eroare! Eventual o "eroare" din partea utilizatorului care a pus o restrictie prea larga. A pus <= 115 când putea pune <= 104 si intervalul de eroare era 0. dar nu poti sti de dinainte!

Oare nu era mai corect sa se numeasca Intervalul de siguranta? Dar nu eu decid.