Manual pentru clasa a 11-a - manuale.edu.ro a XI-a/Tehnologia informatiei... · Lecþia 1...

Manual pentru clasa a 11-a

TEHNOLOGIAINFORMAÞIEI

ºi a COMUNICA IILORÞ

MARIANA PANÞIRU

filiera teoreticã / profil real / specializarea: þtiinþele naturii

TIC 3 (sisteme de gestiunea bazelor de date)

Manualul a fost aprobat prin Ordinul ministrului Educaþiei ºi Cercetãrii nr. 4446 din 19.06.2006 în urma evaluãrii calitative organizate de cãtre Consiliul Naþional pentru Evaluarea ºi Difuzarea Manualelor ºi este realizat în conformitate cu programa analiticã prin Ordin al ministrului Educaþiei ºi Cercetãrii nr. 3252 din 13.02.2006.

TEHNOLOGIA INFORMAÞIEI ªI COMUNICAÞIILOR – Manual pentru clasa a XI-aMariana PANÞIRU

Copyright © 2006, 2013 BIC ALL

Toate drepturile asupra prezentei ediþii aparþin Editurii BIC ALL.Nici o parte din acest volum nu poate fi copiatã fãrã permisiunea scrisã a editurii. Drepturile de distribuþie în strãinãtate aparþin în exclusivitate editurii.

ISBN 978-606-587-235-6

Referenþi: prof. gr. I Carmen Losonczy prof. gr. I Cornelia Ivasc Redactor: Mihai MãnãstireanuCoperta colecþiei: Alexandru NovacTehnoredactare: Radu Dobreci

Editura BIC ALL B-dul Timiºoara, nr. 58, sect. 6 Cod: 061317 – Bucureºti Tel.: 402 26 00 Fax: 402 26 10

Departamentul distribuþie: Tel.: 402 26 23; 402 26 25; 402 26 34Comenzi la: [email protected] URL: http://www.all.ro

3CUPRINS

Cuprins

Lecþia 1 Recapitularea cunoºtinþelor de Excel din clasa a X-a ..................................... 5

• Operaþii elementare asupra obiectelor Excel: registre, foi de calcul, linii, coloane, celule• Introducerea ºi editarea valorilor în celule • Formule simple • Adrese • Funcþiile IF,Sum, Count, Average • Formatarea foilor ºi a datelor • Afiºarea la imprimantã • GraficeExcel • Exerciþii suport – Test iniþial

Lecþia 2 Introducerea ºi validarea listelor de date Excel ............................................ 10

• Ce este o listã sau o bazã de date Excel • Facilitãþi pentru gestionarea listelor: extensiaautomatã a formatelor ºi formulelor • Validarea datelor • Vizualizarea ºi editarea datelorprin formulare • Sarcini de laborator • Test 1

Lecþia 3 Elemente de bazã ale programului Excel ....................................................... 22

• Sortarea datelor • Filtrarea datelor prin Autofilter • Filtrarea avansatã a datelor •Sarcini de laborator • Test 2

Lecþia 4 Funcþii .......................................................................................................... 32

• Funcþii pentru datele de tip text • Funcþii pentru date calendaristice • Funcþiinumerice • Functii statistice • Funcþii speciale pentru baze de date Excel • Sarcini delaborator • Test 3

Lecþia 5 Operaþii de cãutare în bazele de date Excel .................................................. 41

• Operaþia de cãutare ºi editare folosind formularul Excel • Funcþia de cãutare Vlookup •Sarcini de laborator • Test 4

Lecþia 6 Gruparea ºi totalizarea datelor ...................................................................... 48

• Subtotalizarea datelor • Tabele pivot • Folosirea câmpurilor calculate în tabele pivot• Reprezentarea graficã a situaþiilor centralizatoare tip matricial • Sarcini de laborator• Test 5

Lecþia 7 Probleme financiare ..................................................................................... 58

• Funcþii financiare pentru fluxuri constante de bani • Funcþii financiare pentru fluxurimonetare variate • Sarcini de laborator • Test 6

Lecþia 8 Analiza datelor ºi estimarea tendinþelor ........................................................ 67

• Analiza datelor prin construirea scenariilor: utilitarul Scenarios • Atingerea unuiobiectiv: utilitarul Goal Seek • Tabele de simulare construite cu Data Table •Previzionarea evoluþiei unui fenomen prin folosirea funcþiei Trendline ºi a graficelor •Sarcini de laborator • Test 7

4 TEHNOLOGIA INFORMAÞ IE I º I A COMUNICAÞ I ILOR • CLASA A XI-A

Lecþia 9 Gestiunea tabelelor organizate ca bazã de date în Word ............................... 78

• Folosirea formularelor în tabele Word • Sortare • Operaþii asupra câmpurilor prin FieldManager • Efectuarea calculelor • Referirea prin Bookmark • Prelucrarea datelor provenitedin surse externe • Baza de date Word pentru corespondenþã • Sarcini de laborator• Test 8

Lecþia 10 Problema securitãþii datelor în Excel ºi Word ................................................ 90

• Protecþia registrelor ºi a foilor de calcul • Protecþia documentelor Word • Sarcini delaborator

Lecþia 11 Macro-instrucþiuni ........................................................................................ 94

• Crearea unei macro-instrucþiuni • Execuþia • Asocierea macroului unui buton decomandã• Alte obiecte de interfaþã: Image, Label • Sarcini de laborator • Test 9

Lecþia 12 Numere aleatoare ºi simulãri ........................................................................ 99

• Funcþia Rand() • Probleme de simulare • Sarcini de laborator

Lecþia 13 Elemente de programere în Visual Basic ..................................................... 104

• Mediul de programare • Obiecte. Proprietãþi ºi metode • Afiºarea datelor prin Msgbox •Introducerea datelor prin inputbox • Instrucþiunile If, Select case, Do While • Sarcini delaborator •

Lecþia 14 Dezvoltarea aplicaþiilor cu baze de date ..................................................... 119

• Ce este ºi cum se realizeazã o aplicaþie informaticã • Etape • Studii de caz • Temepropuse pentru proiectele finale •

Rezolvãri .................................................................................................................. 135

5LECÞIA 1 • RECAPITULARE

Lecþia 1Recapitulare

�� Operaþii cu principalele obiecte Excel: registre, foi decalcul, celule

�� Formatarea datelor ºi a foilor de calcul, afiºarea laimprimantã

�� Operaþii elementare cu celule, adresarea relativã ºiabsolutã, formule grafice

1

2

3

4

5

6

7

8

9

10

11

12

13

Sarcinã

Creaþi un nou registru cu numele „test_initial.xls”

Creaþi trei foi de calcul cu numele „sem_1", „sem_2" ºi„an_ºcolar”

Grupaþi cele 3 foi de calcul ºi scrieþi în prima linie denumirilecoloanelor „numãr_curent’ ºi „nume_ºi_prenume”,„data_nasterii”, „bursa”

Realizaþi o serie personalizatã cu denumirile obiectelor destudiu: matematicã, informaticã, fizicã, lb. românã, etc.

Copiaþi seria cu denumirile obiectelor pe linia 1 a tuturor foilorde calcul

Pãstraþi gruparea foilor ºi introduceþi pe coloana„numar_curent” o serie numericã cu pasul 1 pânã la valoarea30 (presupunem cã în clasã nu sunt mai mult de 30 elevi)

Fixaþi pentru coloana „data_nasterii” formatul „zi numeluna an”

Formataþi coloana „bursã” la tipul monetar Lei întreg cu 2zecimale

Formataþi coloanele pentru mediile la obiecte la întreg fãrãzecimale.

Pe ultima coloanã puneþi ca titlu „medie” ºi formataþi la întregcu 2 zecimale

Puneti formula de calcul a mediei fiecãrui elev pe coloana„medie”. Copiaþi-o pe coloanã

Puneti formula pentru „bursã” astfel: dacã media este între 9-10 valoarea va fi 100 Lei, dacã este intre 7-8.99 valoarea va fi50 Lei, altfel va fi 0 Lei

La sfârºitul tabelului adãugaþi o linie ºi puneþi textul „medii peobiecte” în celulele corespunzãtoare numãrului curent, nume,data_nasterii pe care le concatenaþi (lipiþi). Centraþi textul.

Revedeþi

New file

Rename sheet

Grupare foi

Crearea seriipersonalizate

Introducere serie

Serii numerice

Format data

Format currency

Format number

Format number

Copiere FormulaAverage

Funcþiile IF, AND

Formatare celulede text


14

15

16

17

18

19

20

21

22

23

24

25

26

27

Scrieþi formulele pentru calcularea mediilor la fiecare obiect.Formataþi celulele respective la întreg cu 2 zecimale

Pe o nouã linie scrieþi textul „total burse” în celulelecorespunzãtoare numãrului curent, nume, data_nasterii pe carele concatenaþi. Centraþi textul. Puneþi formula pentru sumatotalã a burselor. Formatul este bun? Schimbaþi-l dacã enecesar!

Pregãtiþi foaia de calcul „an_ºcolar” punând pentru coloanabursã suma bursei din semestrul1+semestrul 2

Foaia de calcul „an_scolar” va avea în celulelecorespunzãtoare mediilor pe obiecte media aritmeticã cu 2zecimale a mediilor din cele douã semestre. Scrieþi formulele.Formatul pentru zona mediilor este corect? Faceþi corecþia.

Numiþi „abs_1" coloana cu absenþe din foaia „sem_1" ºi„abs_2" coloana cu absenþele elevilor din foaia de calcul„sem_2"

Plasaþi în foaia de calcul „an_ºcolar” o celulã cu totalulabsenþelor pe clasã calculat prin însumarea coloanelor „abs_1"ºi „abs_2"

Puneþi câte un comentariu la coloanele cu absenþe din foile decalcul „sem_1" ºi „sem_2" arãtând numele lor ºi la celula cutotalul acestora

Salvaþi cele 3 foi de calcul ca template!

Introduceþi numele a 10 colegi ºi data_naºterii. Observaþiprezenþa acestor date simultan în cele 3 foi de calcul.Observaþi funcþionarea formulelor.

Separaþi foile de calcul

Introduceþi valori pentru mediile pe obiecte în semestrul 1 ºiseparat în semestrul 2. Verificaþi funcþionarea formulelor. Ce seîntâmplã dacã nu sunt 30 elevi? Ar trebui schimbate formulele?De ce?

Pe foaia de calcul „an_ºcolar” inseraþi o coloanã„pondere_absenþe” în care veþi calcula ponderea numãrului deabsenþe ale fiecãrui elev în totalul absenþelor clasei.

Pe foaia de calcul „an_ºcolar” inseraþi o primã linie vidã.Puneþi în celula A1 valoarea unui dolar în lei.

Pe foaia de calcul „an_ºcolar” inseraþi o nouã coloanã dupãcoloana bursã cu valoarea bursei în dolari folosind în formulãadresarea absolutã a ratei de schimb.

AverageFormat cells

Sum

Lucru cu celuledin 2 foi decalcul; sum

Lucru cu celuledin 2 foi decalcul;average

Insert�name

sum

Insert�comment

Save as template

Introducere dateîn foi grupate

Degrupare foi

Introducere date

Percentage

Insert row

Adresareabsolutã


28

29

30

31

32

33

34

Faceþi câte o copie pentru fiecare foaie de date

Mutaþi toate copiile dupã cele trei foi de date ºi coloraþinumele foilor de calcul (copii) cu roºu

Pregãtiþi foaia de calcul „an_ºcolar(2)” pentru a fi listatã laimprimantã.a. ºtergeti coloana data_naºteriib. scrieþi cu WordArt titlul raportuluia. încadraþi tabelul într-un chenarb. folositi pentru liniile de total fonturi, dimensiuni, culori

diferite faþã de conþinutul raportuluic. anulaþi caroiajuld. puneþi o imagine în colþul stânga suse. puneti ca antet de paginã anul ºcolar ºi clasaf. repetaþi numãrul curent al elevului ºi pe foaia continuare a

mediilor (pentru cã numãrul de coloane este mare foaia sefragmenteazã vertical)

g. Dimensionaþi paginile astfel încât datele sã fie echilibratplasate în paginã. Previzionaþi.

Pregãtiþi o foaie – numitã „centralizator” – pentru tipãrirea laimprimantã a unui raport centralizator cu numele, data-naºterii,bursa, absentele ºi mediile fiecãrui elev – fãrã detalierea peobiecte.a. Copiaþi conþinuturile tabelelor din fiecare foaie de calcul

(“sem_1”, „sem_2', „an_ºcolar”) pe verticalã unul încontinuarea celuilalt, separate doar de o linie pe care scrieþiperioada (semestrul 1, semestrul 2 ºi anual) fãrã capul detabel.

b. Puneþi ca titlu „situaþia centralizatoare la clasa…”c. Formataþi noul tabel.d. Pentru cã raportul este mai lung decât dimensiunea paginii,

fixaþi marginile astfel încât fiecare paginã sã aibã tabeluldintr-o perioadã.

e. Puneþi ca antet fotografia clasei.

Pe foaia de calcul „an_ºcolar(2)” realizaþi graficul mediilorgenerale pe elev. Atenþie! La selectarea seriilor de date dinzone neadiacente veþi folosi coloana cu numele elevilor ºicoloana cu mediile generale.

Tipãriþi la imprimantã graficul mediilor generale pe obiecte –veþi folosi linia cu denumirile obiectelor ºi linia mediilorgenerale pe obiecte.

Afiºaþi sub formã graficã ponderea absenþelor fiecãrui elev întotal absenþe pe clasã

Copiere foaie

Mutare foiFormatsheet�tab color

Formatare foaie-delete column-word art-format cells-tools�gridlines-insert picture-background-print setup-header andfooter

-Copy-paste special-autoformat

Print setupHeader and footer-insert pictureView Print break

Chart-columnSerie pe coloaneneadiacente

Chart barSeria pe linii

Chart pie


35

36

37

38*

39*

40*

41*

42*

Desenaþi un grafic care sã ilustreze variaþia burselor ºi anumãrului de absenþe pe elev

Coloraþi cu roºu situaþiile în care un elev are mai mult de 40 deabsenþe anual

Grafice cu 2 seriide date

Formatarecondiþionatã

Înregistraþi temperaturile de dimineaþa, prânz ºi seara din mai multe zile. Pe o coloanãvor fi afiºate sub formã de imagini fenomenele atmosferice din ziua respectivã: ceaþã,senin, vânt, zãpadã, etc. folosiþi Clip art.Aflaþi numãrul de zile când temperaturile de dimineaþã au fost sub 0 grade; temperaturaminimã seara; temperaturile medii zilnice; temperatura medie a întregii perioade ºinumãrul de zile în care temperatura medie a zilei a fost sub cea medie a perioadei.Afiºaþi sub formã de grafic variaþia temperaturilor medii pe zile. Afiºaþi sub formã degrafic variaþia temperaturilor pentru dimineaþã, prânz ºi searã în perioada studiatã.

Aflaþi al 50-lea termen din ºirul Fibonacci. ªirul Fibonacci este o serie infinitã de numereîntregi 0, 1, 1, 2, 3, 5, 8, 13. Primele douã numere sunt 0 ºi 1, apoi fiecare numãr secalculeazã ca fiind suma celor douã numere precedente. Aflaþi de la al câtelea termense stabilizeazã raportul a doi termeni succesivi. Cât este valoarea raportului de aur?

Mama îmi spune sã o ajut! Are o reþetã pentru 4 persoane. Doreºte sã afle cât sãcumpere din fiecare produs dacã la masã vin 10 persoane. Dar dacã vin 20? Dar dacãreþeta ar fi pentru 6 persoane?Iatã reþeta: „Se taie rondele 400 g ºunculiþã fragedã de porc, 2 cepe (cca 200 g),700 g. cartofi fierþi, 5 roºii mari (cca ½ kg); varzã albã cam 400 g; 4 ardei graºi (la unkg intrã cam 6 !). Se amestecã bine cu sosul din 3 linguri oþet, 2 linguri ulei, sare,piper. Se presarã deasupra brânzã telemea (cam 200 g). Realizaþi o foaie de calculcare sã permitã afiºarea exactã a cantitãþilor necesare unui numãr oarecare de invitaþi.

O firmã de comercializare a maºinilor de lux a înregistrat cantitãþile vândute în ultimeledouã luni în regiunile Moldova, Muntenia ºi Oltenia, Maramureº etc. Doreºte sã obþinãdiferenþa cantitativã între cele douã luni ºi procentul de modificare a vânzãrilor de laprima lunã la urmãtoarea. Dacã vânzãrile au scãzut, se va afiºa diferenþa cu roºu. Deasemenea, doreºte sã afle ce procent din totalul vânzãrilor au fost vânzãrile lunii curenteîn fiecare regiune. Realizaþi foaia de calcul.

Creaþi o foaie de calcul pentru salariile minime din diferite sectoare de activitatepentru o þarã oarecare. Completaþi cu date fictive pentru salariile medii din învãþãmânt,cercetare, armatã, poliþie, sãnãtate, justiþie.a. Calculaþi salariul mediu.b. Aflaþi procentual ponderea salariului pentru fiecare ramurã faþã de salariul mediu

general.c. Adãugaþi o nouã coloanã unde veþi scrie valoarea salariului în dolari. Folosiþi celula

A1 pentru rata de schimb. Folosiþi adresarea absolutã. Daþi nume celulei ºi refaceþiformulele pentru calcularea salariului în dolari.

d. Daþi domeniului de celule unde sunt trecute salariile numele SAL1.

La firma noastrã orice angajat poate ieºi la pensie dacã a lucrat cel puþin 40 de anisau are peste 65 ani sau a lucrat cel puþin 30 de ani ºi are peste 60! Afiºaþi pensionariiposibili.


Verificarea noþiunilor de bazã Excel din clasa a X-a

1. Sã se creeze un registru de calcul cu numele Facturi.xls care sã conþinãdouã foi de calcul: Facturi ºi Situaþii. Pe foaia de calcul Facturi creaþi un tabelîncepând din linia 3 cu urmãtoarele coloane: data-facturii, numãrul-facturii, nume-furnizor, nume-produs, cantitate, unitate-de-mãsurã, preþ de achiziþie. Formataþicoloanele la tipul datelor ºtiind cã preþul este în lei cu douã zecimale iar dataeste în formatul „nume-luna, ziua, anul”, cantitatea este întreagã iar numãrul-facturii este de fapt text! Introduceþi 10 linii astfel incât sã fie diferite produse

2. Inseraþi o primã coloanã unde puneþi numãrul curent ca serie cu pasul 1. 0,5 pct

3. Adãugaþi coloana TVA, unde puneþi formula de calcul a valorii TVA pentrufiecare produs ca fiind cota-tva*preþul de achiziþie a fiecãrui produs. Cota tva vafi trecutã ca o valoare în celula A1. Folosiþi aceastã valoare în formulã.

4. Puneþi în celula A2 cota de adaos practicatã de societate ca fiind 30%.Numiþi celula „cota_adaos”.

5. Adãugaþi coloana adaos la tabelul iniþial ºi puneþi formula de calcul aadaosului ca fiind cota_adaos * preþul de achiziþie.

6. Adãugaþi coloana Preþ_de_vânzare ca fiind preþul de achiziþie + TVA +adaosul la fiecare produs.

7. Adaugati pe ultimele linii ale raportului o situaþie centralizatã unde veþicalcula:

a. Totalul valoric al intrãrilor la preþul de achiziþie b. Totalul valoric al intrãrilor la preþul de vânzare c. Totalul valorii tva d. Totalul valorii adaos

Observaþie. Adãugaþi coloane suplimentare unde sã calculaþi valoarea la fiecarelinie ºi sã însumaþi apoi pe coloanã. Coloanele de calcul le veþi ascunde.

8. Copiaþi doar valorile din tabelul facturilor în foaia de calcul Situaþie ºipregãtiþi foaia pentru un raport la imprimantã: puneþi titlu, chenare, culori.

9. Realizaþi graficul variaþiei preþului de achiziþie ºi de vânzare la fiecare produs.

Test iniþial

0,5 pct

0,5 pct

0,5 pct

1 pct

0,5 pct

1 pct

1 pct1 pct1 pct

0,5 pct

1 pct


Lecþia 2Introducerea ºi validarealistelor Excel

�� Ce este o listã de date?�� Validarea datelor�� Vizualizarea ºi editarea datelor prin formulare Excel

O listã de date Excel sau o bazã de date1 este o colecþie de date structurate care sereferã la un obiect distinct dintr-un domeniu dat. Fiecare listã va conþine acele atributecare pot sã caracterizeze obiectul respectiv în vederea memorãrii ºi prelucrãrilor specificeunor utilizatori. De exemplu, într-o listã necesarã examenului de bacalaureat vom reþinepe fiecare linie un elev cu CNP, numele, coala de provenienþã, anul când a absolvit clasaa 12-a, mediile la fiecare probã. O listã de date pentru dirigintele unei clase poate sãconþinã pentru fiecare elev numele pãrinþilor, adresa, hobby, data naºterii, evoluþia mediilorpe obiecte ºi ani de studiu, etc.

Lista de date Excel apare tot sub forma unui tabel cu linii ºi coloane. Numai cã fiecarelinie are o anumitã semnificaþie ºi, în general, fiecare linie are mãcar o valoare care oface unicã.

De obicei se pune o singurã listã pe o foaie de calcul, dar dacã sunt mai multe seseparã cu linii ºi coloane vide. Lista nu trebuie sã conþinã linii sau coloane vide!

Exemplu:La balcaniada de informaticã de la Iaºi participã elevi din mai multe þãri. Pentru bunadesfãºurare fixãm ca însoþitori ai musafirilor ghizi de-ai noºtri. Fiecare elev gazdãnumit ghid rãspunde de un musafir. Am întocmit douã tabele. Observaþi diferenþa!

Fig. 2.1. Exemplu de tabel care nu econsiderat listã în Excel

Fig. 2.2. O listã Excel ocupã o zonãcompactã de celule

1 Despre baze de date Access aþi învãþat în clasa a X-a. Revedeþi lecþiile!

11LECÞIA 2 • INTRODUCEREA º I VALIDAREA LISTELOR EXCEL

Coloanele într-o listã sunt numite câmpuri. Identificatorul coloanei trebuie sã fie dis-tinct. Numele coloanelor se mai numesc etichete ºi se plaseazã pe prima linie a bazei dedate. Spunem cã formeazã structura bazei de date. Liniile se numesc articole sau înregistrãriºi formeazã conþinutul bazei de date.

Dacã dorim sã vizualizãm etichetele sau numele câmpurilor, atunci putem folosi chenaresau culori, dar nu separãm structura de conþinutul listei.

Listele Excel beneficiazã de anumite facilitãþi, cum ar fi: extensia automatã a formatelorºi a formulelor, validarea datelor la introducerea lor sau la o editare, sortarea dupã unulsau mai multe atribute, filtrarea dupã unul sau mai multe criterii, gruparea ºi totalizareala nivelul fiecãrui grup de date etc.

2.1. Extensia automatã a formatelor în liste

O facilitate importantã a listelor este extinderea automatã a formatului unei coloanepreluând formatul comun a 3 celule (din ultimele 5) anterioare.

Exemplu:Avem de introdus într-un câmp preþul ºi formatãm doar primele 3celule ale coloanei la Currency cu douã zecimale ºi simbolul „lei”.Introducând în continuare valori pe coloanã, chiar dacã depãºimcele 3 celule, observãm cã se pãstreazã formatul.Acest lucru este foarte util mai ales când nu ºtim de la început câtde lungi vor fi coloanele, pentru a le formata de la bun început.Tehnica obiºnuitã de a formata întreaga coloanã este acceptatãnumai dacã pe coloanã nu sunt alte categorii de informaþii. Exceladuce ca facilitate preluarea formatului gãsit în cel puþin 3 din ultimele 5 celule încontinuare, pe mãsurã ce introducem datele!

Sarcina 1.Introduceþi valorile urmãtoare într-un câmp numit Preþ. Formataþi celulele

unde se gãsesc valorile 2,4,55 la întreg fãrã zecimale. Adãugaþi pe coloanã încontinuare valorile 5.78, 3.7457, 1,9. Ce se întâmplã? Ce format vor aveavalorile noi introduse? Întreg sau cu 2 zecimale?

R. întreg

Sarcina 2.Observaþi imaginea. Celulele A2, A4 ºi A6 sunt formatate la valori

monetare cu douã zecimale, iar celulele A3 ºi A5 la întreg. Ce formatva avea valoarea din celula A7? Dar A8?

Cum procedãm sã schimbãm formatul la numeric (întreg) pentrucelulele care urmeazã lui A7? Dar la text?

pret2.89

24

552.56


2.2. Extensia automatã a formulelor în liste

O altã facilitate a bazelor de date este extensia formulelor, dacã toate celuleleprecedente (cel puþin 4!) au aceeaºi formulã.

Exemplu:Avem de introdus ºi de calculat mediile anualeale mai multor elevi. Desigur, pentru primul vomscrie formula ºi o vom copia pe coloanã. Ce nefacem însã dacã nu avem date? Mesajul de eroareafiºat datorat lipsei de date poate sã neincomodeze. Unii proiectanþi ai foilor de calculfolosesc formatarea condiþionatã pentru ca eroareade acest tip sã fie invizibilã. O facilitate oferitã deExcel este ca, odatã formula repetatã pe 4 celuleanterioare ale coloanei, se copiazã automat înnoua linie atunci când introducem valorile de calcul, fãrã ca operatorul sã aibã grijãde acest lucru. ªi fãrã ca foaia de calcul sã fie invadatã de mesaje!

Sarcini de laborator1. Refaceþi exemplul nostru. Puneþi formula pe prima celulã ºi copiaþi-o doar pe 4

celule. Introduceþi valori în continuare pe cel puþin 6 rânduri. Observaþi refacerea automatãa formulei.

2. Construiþi un nou registru cu numele Balcaniadã, în care pe foaia de calcul „ghizi”proiectaþi structura unei baze de date cu urmãtoarele câmpuri: numele ghidului, vârsta,ºcoala, sex, sala de care rãspunde, numele musafirului, vârsta acestuia, þara, sex, disciplinade concurs, data_sosirii

2.3. Validarea datelor

Introducerea datelor trebuie sã se facã corect încã de la introducerea datelor. Nu avemde ce sã pierdem timp pentru a verifica corectitudinea datelor dupã ce acþiunea deintroducere sau editare a fost terminatã la toatã lista! De aceea se poate specifica oanumitã condiþie pe care trebuie sã o îndeplineascã valorile la intrare.

La introducerea unei valori eronate operatorul poate fi doar avertizat cã s-a produs oabatere faþã de valoarea consideratã bunã dar i se permite sã continue procesul de intrare.

Sunt situaþii, însã, în care operatorul trebuie oprit la aceeaºi celulã pânã la introducereavalorii corecte.

Fixarea sau anularea regulilor de validare, mesajele cãtre operator la introducere ºi/sau mesajele de eroare se stabilesc prin fereastrã de dialog deschisã de comandaData�Validation.

Anularea validãrii se face prin aceeaºi fereastrã de dialog prin butonul Clear.


Câteva observaþiiE necesar ca înainte de deschiderea ferestrei de dialog sã ne poziþionãm pe câmpul

unde dorim introducerea restricþiilor de editare.Dacã nu ºtim cât de lungã va fi coloana, o vom selecta în întregime ºi, dupã fixarea

condiþiilor de validare ºi verificarea lor, vom anula validarea pentru eticheta câmpuluiprin butonul Clear din fereastra Data�Validation.

Vom exemplifica câteva tipuri de validare pe o bazã de date despre ghizi construitã înfoaia numitã „Ghizi” din registrul „Balcaniada” cu structura: nume_ghid, vârstã, sex,locaþia, ºcoala, nume_musafir, þara, vârsta_musafir, sex, disciplina de concurs etc.

2.3.1. Validarea numerelor prin limitarea valorii

Exemplul 1

Pentru lista elevilor care suntghizi la Balcaniadã vârsta poatefi – de regulã – între 7 ºi 15 ani,dar se admit ºi excepþii. Decidacã un elev are vârsta de 16 ani,operatorul este cel care va hotãrîdacã acceptã sau nu persoanarespectivã. Observaþi în imaginemesajul afiºat atunci când amintrodus o valoare eronatã.Cum procedãm?

Pasul 1. Selectãm coloana B.

Pasul 2. Deschidem fereastra de validare cu Data�Validation.Pasul 3. La pagina Settings fixãm tipul de datã Decimal ºi operatorul Be-tween

Pasul 4. Introducem limita inferioarã 7 ºi limita superioarã 15.

Pasul 5. La pagina Error Alert alegem stilul alertei Warning ºi scriem textulmesajului afiºat în caz de eroare.

Pasul 6. Pentru cã prima celulã B1conþine numele coloanei vomanula validarea acestei celulepoziþionând cursorul pe aceasta ºiacþionând butonul Clear dinfereastra Data�Validation

Fig. 2.4.Fixareacondiþiei

Fig. 2.3. Validare cu avertizare

Fig. 2.5. Fixarea stilului ºi a mesajului


2.3.2. Validarea textelor prin selectarea unei valori (listã derulantã)

Exemplul 2În lista ghizilor pe care am construit-o în aceastã lecþie considerãm cã locaþia trebuiesã fie doar una din valorile „sala 1, sala 2, sala 3, parter, hol”. În acest caz nu se vapermite introducerea altei valori decât cea din listã! Operatorul va fi oprit din editare ºiforþat sã reintroducã valoare pânã când valoarea corectã (butonul Retry).Observaþi imaginea capturatã

Cum am procedat?Pasul 1. Am selectat coloana C

Pasul 2. Am deschis fereastraData�Validation

Pasul 3. Am fixat pentru valoa-rea coloanei tipul List ºi amconstruit lista direct în zona deeditare Source.

Pasul 4. Pentru situaþia în carevaloarea este eronatã am intro-dus oprirea pânã la introduce-rea valorii corecte prin stilulalertei: stop!

Fig. 2.6. Validare cu oprire

Fig. 2.7. Construirea unei liste de valori Fig. 2.8. Fixarea mesajului ºi a stilului pentrusituaþia de eroare

Exemplul 3Sã presupunem cã în baza de date a elevilor ghizi introducem un noucâmp Scoala. În aceastã coloanã validarea se va face prin preluareavalorilor dintr-o listã, dar se va accepta ºi o nouã valoare – de exempluun elev învaþã la o ºcoalã care nu a fost trecutã în listã!Sã presupunem cã avem deja scrise numele ºcolilor din Iaºi pe carele-am putea folosi. Sunt douã situaþii:a. valorile sunt în aceeaºi foaie de calcul cu baza de date pe caredorim sã o validãm, caz în care folosim referinþele la zonã


b. numele distincte ale ºcolilor din oraº se gãsesc pe altã foaie de calcul ºi atuncivom da nume coloanei – atenþie fãrã eticheta de câmp – ºi vom pune în câmpulSource numele zonei, precedat de semnul =.

Exemplul 4În registrul de lucru „Balcaniadã” avem obazã de date cu ghizii ºi elevii participanþi.Dorim sã introducem numele disciplinei lacare concureazã fiecare participant printr-olistã (dropdown list). ªtim cã în registrul„inspectorat” existã o bazã de date cu disci-pline de concurs, pe care am putea sã ofolosim.

Cum facem?Metoda nu diferã prea mult de folosirea pentrulista derulantã a unui domeniu cu nume,numai cã de astã datã va trebui sã fie o referintã externã.

Pasul 1. Deschidem registrul „balcaniadã” prin File�Open.

Pasul 2. Configurãm fereastra Excel astel încât ambele registre sã fie deschise simultanprin Windows�Arrange.

Pasul 3. Ne poziþionãm în foaia de calcul unde avem câmpul „Discipline_de_concurs”.Verificãm ca valorile pe care le vom folosi pentru lista de validare sã fie constituiteîntr-o listã Excel.

Fig. 2.9. Lista derulantã se aflã îndomeniul cu numele „ºcoli”

Fig. 2.10. Lista derulantã este undomeniu în aceeaºi foaie decalcul

Fig. 2.11. Lista derulantã cu valoridin alt registru


Pasul 4. Prin Insert�Name�Define vom da numele „disciplina”, iar în zona de editareRefers to… ºtergem referinþa existentã ºi prin conturare cu mouse-ul introducemreferinþele coloanei discipline_de_concurs din registrul „inspectorat” care este activ!

Pasul 5. În fereastra Define Name adãugãm noul nume.

Pasul 6. Selectãm registrul „balcaniada” ºi nepoziþionãm pe celula unde dorim introducereavalidãrii prin listã (derulantã).

Pasul 7. Deschidem fereastra de validare prinData�Validation introducem numele dome-niului (creat ca referinþã externã!) precedat desemnul =. Vezi captura alãturatã.

Atenþie! Nu uitãm ca la operaþiile de editare abazei „participanþi” registrul „inspectorat” sãfie deschis!

Sarcini1. Adãugaþi în baza de date „participanþi” o nouã coloanã pentru þara de origine a

fiecãrui musafir ºi fixaþi ca validare apartenenþa la o listã de valori. Construiþi lista devalidare prin:

a. introducerea directã a constantelor;b. preluarea valorilor din domeniul AA1:AA20 a aceleiaºi foi;c. preluarea valorilor în domeniul A1:A20 a altei foi de calcul;d. preluarea valorilor din alt registru constituit de voi.

2.3.3. Validarea prin compararea conþinuturilor celulelor vecine

Exemplul 5În baza de date „ghizi”introducem o nouã coloanãpentru câmpul vârstã_musa-fir alãturi de numele musa-firului.Vom fixa ca validare condi-þia ca vârsta ghidului sã fiemai mare decât cea a musa-firului!

Fig. 2.13. Validare cu informarea operatorului asupra valoriieronate

Fig. 2.12. Validare prin apartenenþã la olistã din alt registru


Cum procedãm?

Pasul 1. Pe prima valoare care trebuie validatã se introduce condiþia de validare,precedatã de semnul egal. Pentru cã vârsta primului musafir se aflã în celula H2, ovom compara cu vârsta ghidului din celula B2. Observaþi datele din baza de date ºiconstruirea condiþiei.

Pasul 2. Fixãm mesajul ºi stilul avertizãrii în caz de eroare

Pasul 3. Verificãm funcþionarea validãrii în celula H2

Pasul 4. Copiem validarea pe coloanã folosind comenzile Copy ºi Paste Special.

Observaþie: Odatã cu operaþia de copiere a validãrii în celula H3, expresia devineB3>H3, în celula H4 devine B4>H4 º.a.m.d. (datoritã folosirii adreselor relative!).

Sarcini de laborator

1. Creaþi o altã foaie de calcul în registrulBalcaniadã în care proiectaþi baza de date concurscu structura cod-participant ºi punctele obþinute înfiecare zi de concurs.

2. Validaþi punctajul obþinut în ziua 1 astfel încâtsã fie mai mare ca 1 ºi mai mic ca 100.

3. Validaþi punctajul obþinut în ziua 2 astfel încâtsã fie cel puþin egal cu cel din ziua 1, altfel celularãmânând fãrã conþinut.

4. În baza „ghizi” puneþi coloana Data-sosiriipe care sã o validaþi astfel încât valoarea introdusãsã fie mai micã decât data curentã primitã de lasistem.

Fig. 2.14. Validare prin referire relativãla o celulã vecinã

Fig. 2.15. Validarea unei date calendaristice ºifolosirea funcþiilor

Exemplu. În coloana Data_sosiri dorim validare astfelîncât valoarea introdusã sã fie mai micã decât datacurentã.Observaþi fereastra de validare. Operatorul este „lessthan” iar funcþia care întoarce data sistem este To-day().


2.3.4. Marcarea celulelor invalide

Am învãþat cã mecanismul de validare este declanºat la terminarea introducerii uneivalori într-o celulã. Operatorul este solicitat sã introducã valoarea corectã. Sã luãm îndiscuþie cazul în care validarea unei valori depinde de valoarea dintr-o celulã anterioarã.Ce se întâmplã, însã, dacã dupã ce s-a terminat acþiunea de introducere a valorii validatese modificã valoarea în celula de referinþã? Atunci mecanismul de validare nu mai estedeclanºat ºi rãmâne valoarea introdusã eronat.

Cum aflãm dacã sunt astfel de situaþii?Prin încercuirea cu roºu a celulelor care au valori eronate. Aceastã operaþie se realizeazã

prin Tools�Formula Auditing�Show Formula Auditing Toolbar ºi butonul Circle invalid Data.

Exemplul 6Avem de introdus valori numerice în coloanele K, L, M,cu urmãtoarele restricþii:a. Valorile din coloana K trebuie sã fie mai mici ca 20;b. Valorile din coloana L trebuie sã fie mai mari decâtcele în coloana K;c. Valorile din coloana M trebuie sã fie mai mari decâtsuma celorlalte douã.

Pasul 1. Am pus condiþiile de validare astfel:a. pentru celula K2 tipul este Decimal, operatorul este Less than, iar la maximum ampus valoarea 20;b. pentru celula L2 tipul este Custom ºi formula este =(L2>K2);c. pentru celula M2 tipul este Custom ºi condiþia este = (M2>(L2+K2).

Pasul 2. Am copiat celulele K2, L2 ºiM2 pe coloanele lor.

Pasul 3. Am introdus valori ºi am verificatvalidarea. În acest moment, datele suntcorecte.

Pasul 4. Modificãm valoarea K3=12 ºiK4=16. Dorim sã ºtim dacã validãrilemai sunt corecte!

Pasul 5. Deschidem Tools�Formula Audi-ting�Show Formula Auditing Toolbar ºiactivãm butonul Circle invalid DataObservãm cã datele incorecte sunt încer-cuite cu roºu!Fig. 2.16. Marcarea celulelor invalide prin Circle

Invalid Data de pe Formula Auditing Toolbar


Sarcini1. Adãugaþi la lista participanþilor din registrul Balcaniada coloana „bursã”, în format

Currency fãrã zecimale, cu valoarea monetarã $, care sã primeascã doar valori pozitive.2. Adãugaþi o coloanã „taxe_si_cheltuieli” cu acelaºi format, dar care sã primeascã –

de regulã – o valoare mai micã decât 1/2 din valoarea bursei.3. Introduceþi 5 valori ºi verificaþi funcþionarea validãrilor.4. Modificaþi acum pe coloana „burse” valoarea astfel încât, uneori, sã nu se mai

respecte condiþia din coloana „Taxe_si_cheltuieli”5. Marcaþi cu roºu celulele cu valori eronate!

2.3.5 Atenþionarea operatorului prin mesaje de întâmpinare

De multe ori operatorul nu ºtie ce valori sunt considerate corecte sau ce trebuie sãintroducã într-o celulã. De aceea sunt utile mesajele care se afiºeazã înaintea selectãriiunei celule.

De exemplu, dorim ca numele elevului sã fie scris cu majuscule ºi informãm despreacest lucru pe operator.

Fig. 2.17. Mesajul de întâmpinare se compune tot prin fereastra devalidare deschisã prin Data�Validation în pagina Input Message.Aveþi grijã ca sã fie activ comutatorul Show input.

2.4. Vizualizarea ºi editarea datelor din liste prin formulare Excel

Atunci când listele pe care le construim în Excel sunt foarte mari, cu multe coloane ºimulte linii, operatorul care doreºte sã adauge un nou articol nu mai vede nici denumireacâmpurilor (antetul este pe prima linie!) nici toate coloanele. O modalitate de a aduce înzona vizibilã datele îndepãrtate este tehnica Split. O altã modalitate este folosireaformularului Excel.

Formularul asociat listei se deschide prin meniul Data�Form.Un formular(form) prezintã doar un singur articol, având pentru fiecare câmp denumirea

ºi valoarea sa care poate fi vizualizatã2 ºi/sau editatã. Dacã numãrul de câmpuri este

2 Depinde de protecþia aplicatã la nivel de celulã, foaie, registru. vezi „Protecþia datelor”.


prea mare pentru a putea fi incluse înferestra formularului, putem sã nedeplasãm cu bara de deplasare verticalã.

Putem sã vedem datele prin deplasarecãtre începutul listei (butonul Find Prev)sau cãtre sfârºit (butonul Find Next).

Putem adãuga noi linii (butonul New)sau putem sã ºtergem linia curentã(butonul Delete).

Sarcini de laborator1. Deschideþi un formular pentru baza

de date „ghizi” ºi adãugaþi încã 4 noiarticole.

2. Verificaþi dacã validãrile funcþio-neazã ºi la nivelul formularului

3. Poziþionaþi-vã pe articolul 2 ºi ºtergeþi-l.4. Restauraþi datele. Se poate anula operaþia de ºtergere prin formular a unui articol?5. Poziþionaþi-vã pe primul articol ºi modificaþi numele ghidului.6. Deplasaþi-vã la articolul cu numãrul 3.7. Apãsaþi butonul Restore ºi revedeþi articolul 1. S-a pãstrat corecþia voastrã? Sau

s-a anulat?8. ProblemãLa firma Imobexpert, care se ocupã cu tranzacþii imobiliare, se înregistreazã ofertele

de vânzare ºi cererile de cumpãrare a apartamentelor, caselor, terenurilor. Pentru fiecareclient se înregistreazã ºi datele personale (nume, telefon, e-mail) pentru a putea fi contactatîn cazul în care au fost gãsite soluþii pentru cererea sa. Proiectaþi bazele de date imaginândcererile de informaþii. Ce informaþii ar solicita un client? Dar un ºef? Poate o listã cuofertele/cererile dintr-o zonã, ordonate dupã preþ, poate o listã a apartamentelor cu xnumãr de camere ºi cu preþul cel mai mic. Poate o listã a apartamentelor pe zone saudupã preþuri, separat vânzãrile ºi separat cererile de cumpãrare? O situaþie statisticã anumãrului de apartamente deja vândute ºi comisionul aferent pe luni/ani. O situaþie acererilor comparativ cu ofertele de terenuri etc.

Fig. 2.18. Formularul asociat listei penrtuvizualizarea ºi editarea datelor

Test 1Se va verifica nivelul cunoºtinþelor ºi al deprinderilor de operare cu tipuri de date

diferite, introducerea ºi validarea lor.


1. Creaþi un nou registru pe care sã-l numiþi „inspectorat”.În foaia de calcul cu numele „ºcoli” proiectaþi o listã cu informaþii despre ºcoli:

numele, adresa, data înfiinþãrii, tipul ºcolii, filierã, profil, specializare, numãrul de clase,de elevi, de profesori, dacã are cantinã, dacã are cãmin, valoarea de inventar a bunurilordin ºcoalã în lei.

Atenþie: proiectarea nu înseamnã ºi introducerea articolelor, ci doar fixarea numelorde câmp, a tipurilor ºi a validãrilor necesare. Nu cunoaºteþi de la început câte articoleveþi avea. De aceea veþi folosi facilitatea listelor Excel de a extinde formatele ºi formulele!

Formataþi câmpurile la tipurile de date corespunzãtoare.

2. Adãugaþi o coloanã cu numele „fond_investiþii” care va avea ca valoare numãrul deelevi înmulþit cu suma_pe_un_elev, acordatã de minister. Suma va fi depusã în celula A1 ºinumitã „Suma_pe_un_elev”. Coloana va fi de tip Currency cu douã zecimale exprimatã în $.

3. Proiectaþi restricþii de validare asociind ºi mesaje la întâlnirea situaþiei de eroareºtiind cã:

3.a numãrul de clase la o ºcoalã poate fi între 2 ºi 20;3.b. numãrul de elevi la o ºcoalã este peste 40 (obligatoriu);3.c numãrul de profesori sã fie mai mic decât numãrul de elevi.

4. Proiectaþi o validare a câmpului „tip ºcoalã’” prin apartenenþa la lista de valoriintrodusã direct: „ºcoalã generalã”, „liceu”, „colegiu”. Anunþaþi operatorul ce trebuie sãfacã la poziþionarea în celulã.

5. Proiectaþi o validare pentru „data_înfiinþãrii” ºcolii. Astfel ea poate fi mai micãdecât data curentã ºi mai mare decât „1 ian. 1700”. În caz de eroare se va cere acordulpentru acceptare sau nu.

6. Creaþi în Zona AA1:AA4 a foii de calcul o listã a filierelor cu urmãtoarele valori:„Teoretic”, „Tehnologic”, „Vocaþional”. Folosiþi lista construitã aici ca listã derulantãpentru introducerea valorilor în câmpul „filiera”

7. Creaþi în altã foaie de calcul (nouã) a aceluiaºi registru – numitã „constante”, olistã cu profile posibile pentru licee: „teoretic”, „tehnic”, „servicii”, „arte plastice”,„sportiv”, „militar”, „pedagogic”. Folosiþi aceastã listã derulantã pentru câmpul „Profil”.

8. Creaþi un nou registru cu numele „Specializãri” unde puneþi în foaia de calcul„Constante” o listã cu specializãrile posibile: „filologie”, „ºtiinte sociale”, „matematicã-informaticã”, „electronicã”, „turism”, „teatru”, „mecanicã” etc. Folosiþi aceastã listãca listã de derulare pentru câmpul „specializare” din baza de date a ºcolilor.

9. Introduceþi 10 articole folosind formularul. Schimbaþi datele astfel încât sã vizualizaþisituaþiile eronate prin încercuire cu roºu!

Se va puncta fiecare sarcinã cu 1 pct + 1 pct din oficiu.


Lecþia 3Sortarea ºi filtrarea listelor dedate Excel

�� Operaþia de sortare�� Filtrarea simplã�� Filtrarea prin condiþii complexe

3.1. Sortarea datelor

Operaþia de sortare se face prin schimbarea articolelor (liniilor) unei liste de dateastfel încât sã fie respectatã ordinea valorilor din câmpurile indicate drept chei.

Operaþia de sortare se poate face prin selectarea tabelului ºi comanda Data�Sort sauprin butoanele de sortare

Fereastra de dialog deschisã de comanda Data�Sort permite introducerea a maxim 3nume de câmp, fiecare punând valorile crescãtor sau descrescãtor. Dacã se folosesc maimulte chei, atunci se aranjeazã liniile dupã valorile primei chei. La aceeaºi valoare aprimei chei se aranjeazã dupã valorile din a doua cheie. Dacã se gãsesc valori egale ºi laa doua cheie atunci se aranjeazã liniile dupã valorile celei de-a treia chei, º.a.m.d. Dardacã avem nevoie de mai multe chei? Vom executa sortarea în mai multe etape pornindde la cheia cea mai puþin prioritarã.

Exemplul 1În baza de date a elevilor ghizi la balcaniadã dorim sã aranjãm ghizii pe sãli.

Pasul 1. Vom selecta tabelul.

Pasul 2. Vom deschide fereastra de sortare prin Data�Sort.

Pasul 3. Vom fixa cheia de sortare alegând din lista de atribute (nume de coloane)asociatã primei casete de editare. Vom fixa sensul ordonãrii.

Fig. 3.1. Fereastra Sortpermite fixarea cheilor ºi asensului ordonãrii

23LECÞIA 3 • SORTAREA ºi FILTRAREA LISTELOR DE DATE EXCEL

Exemplul 2Dorim o aranjare a elevilor ghizi pe locaþiile unde sunt repartizaþi, dar nu ne avantajeazãaranjarea acestor valori nici crescãtor nici descrescãtor, pentru cã dorim sã începemcu parterul, apoi cu sãlile 1,2,3 ºi 4 ºi apoi holul! Este în acest caz o sortare dupã oserie personalizatã de valori.Cum facem?Pasul 1. Adãugãm seria personalizatã prinfereastra Tools�Options�Custom list.

Pasul 2. Ne poziþionãm pe baza de date ºideschidem fereastra Data�Sort.

Pasul 3. Introducem în zona de editare acheii identificatorul “locaþie”.

Pasul 4. Prin butonul Option deschidemfereastra de dialog care permite selectareaseriei personalizate pe care dorim sã ofolosim.

Sarcini de laborator1. Sortaþi ghizii dupã vârstã, pentru fiecare vârstã, alfabetic.

2. Sortaþi ghizii dupã vârstã, începând cu fetele.3. Schimbaþi lista, începând cu bãieþii.

4. Afiºaþi lista participanþilor ordonatã pe þãri, vârstele musafirilor, locaþii, ºi alfabeticdupã numele musafirilor!

5. Afiºaþi lista ghizilor ordonatã dupã locaþii, dar punând mai întâi holul, apoi parterul,sãlile 1 ºi 3 ºi la urmã sãlile 2, 4

6. Puteþi schimba rapid ordonarea elevilor astfel încât sã se înceapã cu sãlile care au numãrpar, apoi cele cu numãr impar, apoi parterul ºi la urmã holul? Trebuie fãcutã o altã listã?

3.2. Filtrarea datelor

Filtrarea datelor din liste este operaþia prin care putem într-un mod uºor sã gãsim ºi sãlucrãm cu un subset de date. O listã filtratã afiºeazã doar articolele care îndeplinesc oanumitã condiþie.

Microsoft Excel dispune de douã metode de realizare a filtrãrii listelor:1. Autofilter – care presupune filtrarea prin selectarea valoriilor la nivelul unui sau mai

multor câmpuri

2. Advanced Filter – care foloseºte criterii mai complexe

Subsetul de date constã de fapt în ascunderea articolelor care nu îndeplinesc condiþia.Ele nu dispar din baza de date, nu se aranjeazã altfel. Subsetul de date poate fi editat,formatat, folosit ca sursã pentru grafice sau tipãrit fãrã a fi nevoie sã-l mutãm sau copiemîn altã zonã.

Fig. 3.2. Sortarea dupã o serie personalizatã


3.2.1. Filtrarea prin Autofilter

Se foloseºte meniul Data�Filter�Autofilter care asociazã fiecãrui câmp câte o listãce se deschide la apãsarea butonulul sãgeatã. Opþiuni ale filtrului:

� (All) = afiºeazã toate valorile câmpului

� (Top 10) = afiºeazã primele cele mai mici sau mai mari numere

� Opþiunea Custom permite fixarea unei condiþii compuse pentru valorile unui câmp

� Valorile unice din coloanã permit filtrarea liniilor care conþin una din aceste valori

Anularea filtrului dintr-o coloanã se face prin opþiunea (all).Anularea filtrãrii automate se face prin acelaºi meniu Data�Filter dezactivând opþiunea

Autofilter.

Exemplul 1Dorim sã obþinem doar elevii ghizi din sala 1.Cum facem?

Pasul 1. Ne poziþionãm în listã.

Pasul 2. Selectãm Data�Filter�Autofilter.

Pasul 3. Alegem valoarea “sala 1” pentrucoloana “locaþie”.

Exemplul 2Dorim ca lista sã conþinã doar bãieþii care stau la parter ºi au 10 ani. Cum procedãm?

Pasul 1. Selectãm Data�Filter�Autofilter.

Pasul 2. Vom deschide pe rând listele de filtru de la coloanele vârstã (pe valoarea“10”), locaþie (pe valoarea “parter”) ºi sex (pe valoarea “m”).

Exemplul 3Dorim sã selectãm doar copii cu vârstecuprinse între 10 ºi 14 ani.

Cum procedãm?Pasul 1. Ne poziþionãm pe tabel ºiactivãm Data�Filter�Autofilter.Pasul 2. Alegem opþiunea Custom dinlista asociatã coloanei vârstã.

Pasul 3. Fixãm condiþia alegând opera-torul ºi valoarea pentru fiecare termenal condiþiei compuse.

Fig. 3.3. Alegerea valorii pentru filtrareatabelului.

Fig. 3.4. Fereastra Custom Autofilter permite fixareaunei condiþii compuse.


Exemplul 4Dorim sã facem un nou tabel pe care sa-l afiºãm la imprimantã cu informaþiile despreelevii ghizi cei mai mici ca vârstã!

Pasul 1. Deschidem Data�Filter�Autofilter.

Pasul 2. La coloana Vârstã fixãm opþiunea Top10. care deschide o fereastrã de dialog.

Pasul 3. Alegem din lista Show opþiunea Bottom ºi numãrul de valori=1

Prin opþiunea (Top10.) putem selecta primele valori (Top) sau ultimele (Bottom) dinlista Show.

Pasul 4. Selectãm tabelul filtrat ºi-l copiem în altã locaþie. Îl formatãm ºi îl afiºãm laimprimantã.

Fig. 3.5. Prin opþiunea (Top10) putemselecta primele valori (Top) sau ultimele(Bottom) din lista Show.

Observaþii• Revenirea la toate datele din listã se faceprin Data�Filter�Show all.

• Calcule statistice pe lista filtratã se fac prinmeniul contextual asociat barei de stare

• Odatã cu filtrarea pe bara de stare se afiºeazãautomat numãrul de articole afiºate din totalularticolelor. Dacã dorim sã obþinem alte infor-maþii centralizate despre articolele obþinute prinfiltrare, cum ar fi suma, media, maximul etc.deschidem meniul contextual ataºat barei destare prin butonul drept al mouse-ului.

Sarcini de laboratora. Filtraþi ºi afiºaþi numai bãieþii ghizi cu vârsta de 10 ani.b. Afiºaþi numai fetele musafire care au ghizi bãieþi.c. Afiºaþi numai articolele care se referã la prima þarã din listã.d. Ghizii cu vârste cuprinse între 11 ºi 15 ani.e. Musafirii bãieþi cu vârste de peste 15 ani.f. Aflaþi câte eleve au peste 10 ani.g. Aflaþi vârsta media a bãieþilor musafiri.h. Aflaþi câþi ghizi au cea mai mare vârstã.i. Filtraþi tabelul ºi aflaþi care sunt elevii care provin de la licee (numele ºcolii începe

cu “lic”.).

Fig. 3.6. Calcule statistice pe lista filtratãse fac prin meniul contextual asociat bareide stare.


j. Afiºaþi elevii ghizi care nu provin de la licee ºi sunt bãieþi ºi au vârsta = 10 ani ºisunt pe hol!

k. Filtraþi astfel încât sã obþinem elevii cu cea mai micã vârstã ºi care sunt pe hol!l. Obþineþi cele mai în vârstã 3 fete.m. Afiºaþi elevii care au în câmpul scoala ºirul “nr.” care nu începe sau nu terminã

valoarea.

3.3. Filtrarea avansatã prin Data��Filter��Advanced Filter

Uneori este necesarã obþinerea unei filtrãri a datelor dintr-o listã Excel în altã zonã afoii de calcul active. Noul tabel poate sã nu aibã toate coloanele, poate avea o altã ordinea coloanelor ºi liniile vor fi cele care verificã o condiþie pusã de operator. Pentru aceastase foloseºte Data�Advanced Filter. Filtrarea avansatã foloseºte 3 zone:

a. zona bazei de date – identificatã prin adresare absolutã sau prin nume;

b. zona condiþiei – are cel puþin douã linii; prima conþine identificatorul de câmp, pecelelalte valori pentru a se forma condiþia de filtru;

c. zona rezultatului – poate fi în altã locaþie a aceleiaºi foi de calcul sau peste baza dedate, ca în Autofilter.

Exemplul 5Pentru baza de date cu ghizii ºi ghiduºele Balcaniadei dorim sã obþinem un tabelseparat numai cu numele, sexul ºi vârsta elevilor care rãspund de sala 1.Cum procedãm?Pasul 1. Organizãm foaia de calcul pentru a putea folosi filtrarea avansatã. Astfel,condiþia o punem în A10 (identificatorul de câmp) ºi A11 valoarea “sala 1”; Pentrurezultat copiem numele câmpurilor nume, sex, vârstã pe linie începând cu A13.

Pasul 2. Deschidem fereastra Data�Filter�Advanced Filter. indicãm domeniul decelule folosite ca sursã de intrare (list range) ca fiind domeniul $A$1:$D$7 prin tehnicapunctãrii domeniului. indicãm domeniul de celule unde am scris criteriul de filtrare(Criteria range) ca fiind domeniul $A$10:$A$11. Indicãm domeniul pentru rezultat(copy to) ca fiind $A$13:$C$19

Fig. 3.7. Fereastra de dialog Advanced Filtersolicitã introducerea referinþelor la zonelede construire, dacã se va face o selecþieunicã ascunzând duplicatele sau dacãrezultatul este în aceeaºi locaþie cu baza dedate sau nu.


Exemplul 6Am dori – tot în altã parte a foii de calcul – un tabel cu numele locurilor unde suntghizii de 10 ani!Cum procedãm?Pasul 1. În foaia de calcul pregãtim condiþia compusã scriind numele de coloanã peun rând ºi valoarea pe alt rând.

Pasul 2. Pregãtim locul unde vom obþine rezultatul scriind numele coloanei

Pasul 3. Deschidem Data�Advanced Filter.

Exemplul 7Dorim sã aflãm unde sunt fetele, la ce sãli sunt repartizate. Pentru aceastã sarcinã vatrebui ca fiecare salã sã fie afiºatã o singurã datã!

Pasul 1. Vom da nume celor 3 zone de filtrare. Astfel zona $A$1:$g$5 cu numeleconditie, zona $A$7:$G$60 cu numele baza_de_date ºi zona A$70:$A$90 cu numelerezultat.

Fig. 3.8. Observãm cum se formeazãcondiþia compusã:varsta = 10 ani ºi sex = “m” – pedomeniul $A$10:$B$11 ºi activareabutonului Unique din fereastra de dia-log!

Fig. 3.10. Organizarea foii decalcul pentru Advanced Filter:pe primele linii se punecondiþia care are acelaºinume de câmp cu baza dedate. Pentru selectare unicãfolosim comutatorul Uniquerecord Only. Zona pentrurezultat conþine doardomeniul $A$70:$A$90

Fig. 3.9. Proiectarea filtrului


Exemplul 7. Construirea condiþiei complexe în filtrarea avansatã

Fig. 3.11. Condiþia poate sã aibã mai multe valori pe ocoloanã. Se citeºte astfel: nume=”BEBE” saunume=”ViCA” sau nume=”MARiA”

b. Putem formula condiþii în care operatorul este <, <=,>,>=, <>, =.

Fig. 3.12. Condiþia poate sã aibã operator diferit de egal.Ex. Copii cu vârsta mai mare ca 12

c. Dacã dorim sã concatenãm condiþiile relaþionale prin operatorul “sau”, valorile sepun pe linii diferite.

Fig. 3.13. Condiþie compusã cu operatorul “sau”Ex. copii cu vârsta peste 12 ani sau bãieþii sau pe ceicare rãspund de “hol”.

d. Dacã dorim sã concatenãm expresiile relaþionale prin operatorul “si” se foloseºteaceeaºi linie pentru valori.

Fig. 3.14. Condiþie compusã cu operatorul “si”Afiºeazã bãieþii cu vârsta peste 12 ani care rãspund de“hol”.

e. În condiþie pot fi folosite caracterele de substituire ? care înlocuieºte un singurcaracter pe poziþia respectivã ºi * care înlocuieºte un grup de caractere.

Fig. 3.15. Condiþia poate sã conþinã caractere desubstituire ? sau *. Ex. Elevii din Sãli!

f. În condiþie pot fi folosite valori (referinþe, funcþii) din baza de date sau din afara eidacã pe prima linie punem un alt identificator decât numele de câmp din listã ºi pe adoua linie aceeaºi coloanã punem formula precedatã de semnul egal.

a. O condiþie compusã cu valori alternative ale aceluiaºi câmp se construieºte prindepunerea valorilor pe linii diferite în coloanã.


Exemplul 8Presupunem cã în tabela ghizi existã ºi informaþii despre musafiri. Astfel, fiecare ghideste gazda unui musafir pentru care vom înregistra numele, þara de unde vine, vârsta.Dorim sã aflãm care ghizi au aceeaºi vârstã cu a musafirului. Dacã primul copil arelinia 2 ºi vârsta este pe coloana B, iar vârsta musafirului este pe coloana G putemscrie condiþia = B2 = G2.Pe prima linie nu se scrie un nume de câmp, ci un mesaj.

Fig. 3.16. Folosirea în condiþia de filtrare (domeniul $H$1:$H$2) a uneiexpresii în care intervin celule vecine.

Exemplul 9Dorim sã aflãm care sunt musafirii care au varsta mai maredecât media generalã a vârstelor tuturor musafirilor. Vârstamusafirilor se gãseºte pe coloana G, începând din linia 2. Vomfolosi ca formulã = B2>Average($G$2:$G$100).

Exemplul 10Din lista ghizilor de la balcaniadã dorim sã extragem musafirii în altã parte a foii decalcul. Pentru fiecare avem nevoie de nume ºi þara de unde vine. Observaþi modul încare am pus condiþia. Pentru cã nu se face nici o filtrare – dorim toate articolele! – ampus o condiþie totdeauna adevãratã.AA2 =e2=e2.

Fig. 3.17. Obþinem o listã nefiltratã dacã valoarea condiþiei este TRUE.


Sfaturi practice

1. Pregãtiþi zona unde plasaþi condiþia la cel puþin 3 linii depãrtare de baza de date,copiind pur ºi simplu linia de structurã a bazei (linia cu numele câmpurilor).

2. Pe liniile urmãtoare puneþi valorile pentru filtru.

3. Este bine sã aduceþi atât condiþia, cât ºi tabelul rãspuns în zona vizibilã a ecranului.

4. Pentru comoditatea compunerii informaþiilor din fereastra Advanced Filter daþi nume(prin Insert�Name�Define) pentru condiþie, lista de date ºi rezultat selectând fiecaredomeniu cu tot cu structurã (adicã identificatorii de câmp).

5. Dacã dorim sã selectãm articolele unice va trebui sã activãm comutatorul Uniquerecords only.

Sarcini de laboratorA. Obþineþi în altã parte a foii de calcul – de exemplu începând de la A100 – urmãtoarele

situaþii:1. Numele tuturor musafirilor ºi vârsta.

2. Numele ghizilor, ale musafirilor ºi locul unde merg (locaþia) numai dacã sunt dinBulgaria sau Grecia.

3. Ghizii bãieþi care au aceeaºi vârstã cu a musafirilor lor.

4. Afiºaþi, în altã parte a foii de calcul Ghizi, musafirii români cu vârsta de 10 ani, într-olistã cuprinzând numele fiecãrui musafir ºi numele ghidului lui.

B. Proiectaþi o listã pentru evidenþa mijloacelor fixe dintr-o unitate ºcolarã reþinândnumãrul de unitãþi, nume, data intrãrii în inventar, valoare la intrare, durata normatã deviaþã, amortizare, locaþia, anul producerii, firma producãtoare.

Obþineþi:a) lista mijloacelor fixe care ar trebui scoase din inventar pentru ca durata normatã de

funcþionare a fost depãºitã;

b) cele mai mari (ca valoare) mijloace fixe din locaþia X;

c) lista mijloacelor fixe pe locaþie, pentru fiecare locaþie, dupã valoare ºi data producerii;

d) mijloacele fixe cu durata de funcþionare mai mare de d10 ani;

e) mijloacele fixe produse în þara X ºi achiziþionate dupã data y;

f) mijloacele care au valoare de inventar mai mare decât media aritmeticã a valorilor.

– durata de viaþã mai mare decât 5 ani;

– valoarea de inventar mai mare decât 1000.


Test 2

Nume_scoala G. Ibraileanu sc. Gen nr. 2 IASI M. Eminescu Iasi, lic. National lic. De informatica, Iasi, "Gr. Moisil"

Testul vizeazã verificarea cunoºtinþelor ºi a deprinderilor de a prelucra liste Excel prinsortare ºi filtrare.

Sarcini1. Aranjaþi lista ºcolilor descrescãtor, dupã fondul

de investiþii.

2. Aranjaþi lista ºcolilor pe profile, numãr de clase,alfabetic.

3. Obþineþi prin filtrare doar ºcolile din Paºcani!Copiaþi datele în altã foaie de calcul.

Obs. Numele localitãþii se gãseºte în adresã, nu neapãrat la început sau sfârºit!

4. Sã presupunem cã am completat greºit numele ºcolii, incluzând ºi localitatea „Iaºi”(la unele în faþã, la altele în spate, iar la altele chiar în interiorul ºirului).

Dorim sã facem corecþii manual, filtrând lista ºcolilor astfel încât sã obþinem primadatã ºcolile care au în faþa numelui localitatea „Iaºi”. Apoi ºcolile care au în spatelenumelui localitatea „Iaºi” ºi apoi pe cele care au în interior acest nume de localitate. Cemanevre faceþi?

5. Folosind filtrarea avansatã obþineþi în afara listei pe coloanele AA:AC numele ºcolii,profilul ºi nr_clase pentru acele ºcoli care:

a) sunt din profilul „liceu” ºi au mai mult de 5 clase

b) au cãmin sau cantinãc) au cãmin ºi cantinã ºi sunt din Iaºi

d) au 3 sau 5 sau 6 sau 9 clase

e) au ca sumã de investiþii o valoare mai mare decât media pe inspectorat a acestorvalori.

f) au prima literã a numelui „S”.

6. Folosind filtrarea avansatã obþineþi numai profilele ºcolilor din baza de date.

7. Afiºaþi în altã parte a foii de calcul numele tuturor ºcolilor ºi valoarea de investiþii.


Lecþia 4Funcþii

�� Funcþii cu texte sau ºiruri de caractere�� Funcþii numerice ºi de aproximare�� Funcþii cu date calendaristice�� Funcþii statistice pentru baze de date

4.1. Funcþii pentru texte

Uneori suntem nevoiþi sã prelucrãm informaþiile de tip text aflate în celulele foii decalcul. Un operator folosit pentru texte este &(ampersand) care realizeazã concatenareaºirurilor.

Exemplu „alb” & „astru” produce ºirul „albastru”

Câteva funcþii utile

1. Concatenate(sir1;sir2..) pentru concatenarea ºirurilor date ca parametri. Exemplu:concatenate(”alb”,”astru”) " „albastru”

2. Find(sir1;sir2;poz) sau Search(sir1;sir2; poz)Cautã sir1 în sir2 ºi întoarce poziþia

primei apariþii. Dacã aceasta nu se gãseºte,se afiºeazã mesajul de eroare. Cãutareapoate sã înceapã de la poziþia poz (implicit,poz este 1)

Funcþia Search nu este sensibilã latipul caracterelor din cele douã ºiruri. ªirulcãutat poate sã conþinã caracterele desubstituþie ? ºi *.

3. Len (sir) Întoarce lungimea ºirului. Exemplu Len(”alba”) �>> 4

4. Right(sir,nr) / Left (sir, nr) Întoarce primele / ultimele nr caractere din sir.Exemplu: Right(”albatros”;3)—�>> „alb”; Left(”albatros”;2)—�>> „os”

5. Mid(sir;poz;nr) extrage un subºir de nr caractere începând cu poz ExempluMid(”albatros”;3;2)�>>>”ba”

33LECÞIA 4 • FUNCÞ I I

6. Lower(sir) /Upper(sir)/ Proper(sir) realizeazã conversia la minuscule / majuscule /Prima literã a fiecãrui cuvânt devine majusculã

Exemple: Lower(”MaMa MiA”)�>>>”mama mia”; Proper(”maMa mia”)�>>>”MamaMia”

7. Text(numãr, ºablon) / Fixed(numãr, zecimale) realizeazã conversia unui numãr la ºir.ªablonul conþine simbolul diez ºi 0 pentru cifre (revedeþi Format Cells�Custom)

8. Value(ºir) realizeazã conversia unui ºir la o valoare numericã.

Exemplu: Value(”123.44”)�>>>numãrul 123.44

Exemplul 1Fie un text pentru adresã care cuprinde numele localitãþii, virgula, numele strãzii ºinumãrul. Aflaþi ºi separaþi informaþiile.

Exemplul 2Pentru fiecare meci de fotbal din Cupa României au fost înregistrate numele echipelor,locul, data de desfãºurare ºi scorul. Scorul este dat sub forma unui ºir. De exemplu „7-4” unde 7 este numãrul de goluri marcate de prima echipã, iar 4 este numãrul degoluri marcate de cea de-a doua echipã. Separaþi cele douã informaþii în alte celule(numerice!).

Sarcini de laborator1. Ce realizeazã formulele din exemplul urmãtor?


2. În foaia de calcul cu mecuri – de exemplu – numele echipelor au fost scrise neglijent:unele cu litere mari altele cu litere mici. Realizaþi corecþia astfel încât sã fie scrise pefiecare coloanã numele cu aceleaºi tipuri de caractere.

4.2. Funcþii pentru date calendaristice ºi timp

Excel are inclus un calendar care începe de la 1 ianuarie 1900 ºi se terminã la 31decembrie 2078. Fiecare zi are atribuit un numãr întreg. De exemplu numãrul 1 estepentru 1.01.1900 iar numãrul 366 este asociat datei de 31 decembrie 1900. Timpuldintr-o zi este reprezentat prin fracþii ale numãrului asociat zilei. O orã reprezintã 1/24 =0.041666; un minut =1/(24*60) iar o secundã =1/(24*60*60) dintr-o zi. De exemplumiezul nopþii (ora 24) este 0 iar miezul zilei este 0.5. Dacã avem un numãr cu zecimale,Excel îl considerã ca reprezentând data ºi momentul din zi. De exemplu numãrul 356785.5reprezintã 06.09.2001, ora 12:00 PM

Câteva funcþii utile1. Today() – întoarce data sistem. Exemplu: Today() � 22.06.2006

2. Now() – întoarce data ºi ora sistem. Exemplu: Now()�22.06.2006 12:06:053. Date(an,luna,zi) / Time(ora,minut,secundã) întoarce o datã calendaristicã / moment

de timp folosind numerele date ca parametri.

Exemplu: Date(1998,12,3)�3.12.1998; time(12,3,6)�12:03:06

4. Hour(n) / Minute(n / Second(n) întoarce ora/minutul/secunda din numãr.

Exemplu: hour(12.34)�8; minute(12.34)�9; Second(12.4)�36

Sarcini de laborator1*. La înscrierea pentru cules de cãpºuni în Spania am numãrat peste 100 de persoane.

Dacã durata predãrii unei cereri este de 2 minute, calculaþi cât voi aºtepta dacã mãhotãrãsc sã mã aºez la coadã! Dar dacã ar fi doar 76 persoane ºi durata servirii de 5minute?

2*. La o parcare se cere introducerea momentului sosirii, respectiv plecãrii maºinii, ºise calculeazã automat costul. Realizaþi o foaie de calcul pentru aceastã parcare. Preþulunei ore de staþionare este 0,6 RON (6000 lei vechi).

3*. Cu toþii aºteptãm vacanþa de varã. Calculaþi câte zile ne mai despart de ea! Atenþie:analizaþi problema gândindu-vã la toate posibilitãþile: dacã data curentã este 10 maiatunci aºteptaþi vacanþa anul acesta. Dar dacã este 10 octombrie? La ce vacanþã vãgândiþi? La cea de la anul viitor, nu? Cum veþi calcula?

4*. Calculaþi câþi ani, luni, zile, ore, minute, secunde sunt între douã momente detimp. De exemplu între 31 dec. 2009 ora 12:45 ºi 01 aug. 2006 ora 17:20.


4.3. Funcþii numerice

Câteva funcþii utile de aproximare sunt:

1. int(n) – întoarce partea întreagã prin trunchiere.

Exemplu: int(3.14)�3

2. Round(n,z) / Roundup(n,z), rounddown(n,z) – rotunjeºte numãrul n la a z-a zecimalãmatematic/în plus/ în minus.

Exemplu: Round(123.56,0)=124; Round(123.34;0)= 123Roundup(4.235;1)=4.3Rounddown(4.235;1=4.2

3. Mod(n1,n2) – restul împãrþirii întregi a lui n1 la n2. Exemplu: Mod(5,2)�1

Sarcini de laborator5*. Pe drumul de întoarcere dintr-o excursie un grup de copii (n) au vãzut un câmp cu

flori ºi l-au rugat pe ºoferul autocarului cu care calãtoreau sã opreascã pentru a culegecâteva flori, ca sã facã fiecare câte un buchet pentru mama lui.

Apoi s-au gândit sã punã la un loc toate florile ºi sã facã încã un buchet pentru doamnaeducatoare. Au început sã punã fiecare câte o floare în mod egal la toate buchetele. La unmoment dat, au constatat cã toate buchetele conþin acelaºi numãr de flori ºi cã a mairãmas un numãr de flori mai mic decât numãrul de buchete. S-au hotãrât sã punã acesteflori în buchetul pentru doamna educatoare.

Rezolvaþi problema folosind foaia de calcul Excel. Aflaþi în orice moment numãrultotal de flori, numãrul de buchete care vor trebui formate. Câte flori va conþine fiecarebuchet destinat unei mame? Dar al doamnei educatoare?

4.4 Funcþii statistice

Amintim câteva funcþii statistice studiate deja în clasa a X-a:

1. Sum(n1:n2) – realizeazã suma parametrilor

2. Average (n1,n2) – calculeazã media aritmeticã a valorilor

3. count(n1:n2) – numãrul de calcule în domeniul precizat ca parametru

4. min(n1,n2..) – valoarea minimã

5. max(n1,n2..) – valoarea minimã

6. countif(domeniu, valoare) – numãrã celulele din domeniu care conþin valori egalecu valoarea datã

7. sumif(domeniu-de-cãutare, valoare-cãutatã, domeniu-de-însumare) – realizeazãînsumarea valorilor din domeniul dat ca al treilea parametru pentru acele valori din domeniulde cãutare care sunt egale cu valoarea cãutatã


Exemplul 1Sã presupunem cã se înregistreazã meciurile de fotbal cu rezultatele lor în foaia decalcul cu structura urmãtoare:

a. Vrem sã aflãm:

1. câte meciuri au fost jucate de echipa „steaua" acas�

=countif(b5:b100, "steaua")

2. câte goluri au fost date? =sum(f5:f100)+sum(g5:g00) 3. la câte meciuri echipa aflat� în deplasare a înscris 4 goluri?

=countif(G5:g100,4)

4. care este num�rul minim de goluri înscrise de o echip� în deplasare?

=min(g5:g100)

5. care este num�rul maxim de goluri înscrise de o echip� „acas�"?

=max(f5:f100)

b. Vrem sã afiºãm un clasament al echipelor. ªtim cã pentru fiecare meci câºtigat seacordã 3 puncte, la egalitate se acordã 1 punct. Situaþia punctajelor ºi golaverajeloro vom face pentru fiecare meci în parte.

Pentru clasament va trebui sã însumãm punctajele ºi golaverajele pe echipe ºi sãsortãm descrescãtor în funcþie de punctaje ºi apoi de golaveraje.



6*. O firmã de consultanþã juridicã desfãºoarã diverse activitãþi. Managerul firmei doreºteo evidenþã clarã a numãrului de ore pe fiecare categorie în parte (consultanþe, documentare,analize, alte activitãþi) în fiecare zi a unei luni. De asemenea, acesta doreºte sã ºtie:

a. Câte ore au fost în total pentru fiecare categorie în cursul unei luni.

b. Câte ore s-a lucrat în fiecare zi.

c. Care este ziua când s-a lucrat cel mai mult?

d. Care este categoria la care s-a lucrat cel mai puþin?e. Câte ore se lucreazã în medie pentru fiecare categorie în parte pe lunã?

f. Care este raportul între numãrul de ore ocupate pe fiecare categorie ºi numãrul total deore.

g. În câte zile nu s-a lucrat deloc pentru documentare? Dar pentru analize, consultaþii etc.?

4.4.1. Funcþii statistice pentru baze de date Excel

Toate funcþiile pentru baze de date au numele precedat de litera „D” ºi folosesctabelul Excel organizat ca bazã de date. În acest caz putem folosi numele de câmp întreparametri, dar încadrat între ghilimele.

Lista Excel este definitã printr-un domeniu în care trebuie luat în considerare ºi primalinie a numelor de câmp.

Baza de date poate fi filtratã prin introducerea unei condiþii precizatã prin domeniu înformatul funcþiei. Formatul general al funcþiilor statistice pentru bazele de date Exceleste

Funcþie (baza-de-date, câmp-de-acþiune, condiþie-de-filtrare)

Sunt douã modalitãþi de a referi baza de date: prin domeniul de celule sau fie prinnumele ei acordat prin Insert�Name�Define. Este bine sã dãm un domeniu mai mare canumãr de linii pentru a nu fi necesar sã-l redenumim odatã cu adãugarea de noi articole.

Câmpul folosit în funcþie se poate referi prin numele sãu din structura bazei de dateîncadrat între ghilimele sau prin numãrul de ordine în structurã.

Condiþia se poate referi prin domeniul de celule sau prin numele dat.

Este comod pentru proiectant ca pe zona de început a foii sã punã condiþiacopiind structura bazei de date ºi, dacã mai este nevoie de alte câmpuri, acesteasã fie adãugate dupã structurã.

Vom explica funcþiile statistice prin exemple folosind urmãtoarea foaie de calcul,special proiectatã pentru a facilita înþelegerea variantelor de construire a funcþiilor.


Fie o bazã de date cu stocurile anumitor produse. Pentru fiecare produs am indicatnumele, categoria sa, stocul existent ºi preþul. Am pus stoc zero acolo unde produsullipseºte.

Criteriul de filtrare este comun tuturor funcþiilor din exemplele care urmeazã:

Produsele de „categorie=Electrice” ºi „Preþ>=200”

1. Funcþia DCOUNT – numãrã celulele care conþin numere,fãrã spaþii.

Exemplu: Vrem sã aflãm câte produse din categoria electrice cu preþ >=200Lei sunt înstoc.

Putem folosi:a. =DCOUNT(A6:F17,”cant_stoc”,A1:F2) �2 numãrã produsele fãrã spaþii în câmpul

cant_stoc.

b. =DCOUNT(stocuri,6,condiþie)�2 realizeazã acelaºi lucru dar foloseºte numelestocuri pentru baza de date (A6:F17), numarul coloanei cant_stoc este 6 iar pentru condiþiaplasatã pe domeniul A1:F2 am folosit un nume =”condiþie”. Dacã în celula F13 amavea spaþiu rezultatul s-ar schimba la 1 pentru cã funcþia nu numãrã ºi celulele blank.

c. =DCOUNT(stocuri,condiþie)�2 numãrã articolele care îndeplinesc condiþia fãrã acerceta valorile blank sau non blank de pe o anumitã coloanã.

2. Funcþia DSUM calculeazã suma pe o coloanã datã prin numãrul ei sau numele eipentru acele articole care verificã condiþia.

Exemplu: numãrul total de bucãþi din categoria electrice cu pretul >-200

=DSUM(stocuri,6,condiþie)�83 însumeazã cantitãþile existente în stoc

3. Funcþia DAVERAGE calculeazã media aritmeticã pe o coloanã datã prin numãrul eisau numele ei pentru acele articole care verificã condiþia.

Exemplu: care este preþul mediu al electricelor?

Foaia de calcul pentruoperaþiile de calculestatistice

Convenim sãnumimStocuri=A6:F17Conditie=A1:F2


=DAVERAGE(A6:G17,”pret_produs”,condiþie)�250 calculeazã preþul mediu alproduselor filtrate.

4. Funcþia DMAX /DMiN-extrage maximul sau minimul valorilor filtrateExemplu: Care este preþul maxim al produselor electrice peste 200 lei?

=DMAX(A6:G17,”pret_produs”,A1:F2)�300 calculeazã preþul maxim.

Exemplu. Care este stocul minim din produsele electrice cu preþ >200?

=DMiN(A6:G17,”cant_stoc”,A1:F2)�20 calculeazã stocul minim.

Sarcini de laborator1. Refaceþi foaia de calcul din lecþie.2. Pregãtiþi foaia pentru a se putea calcula, fãrã mari modificãri, aceleaºi informaþii,

dar pentru alte categorii de date. Veþi calcula numãrul de produse, suma cantitãþilor dinstoc, valoarea medie a preþurilor, preþul minim ºi maxim pentru:

a. produsele de mobilã cu stoc=100 ; b. produsele care au codul >5

c. produsele care au stocul zero; d. produsele care se vând la bucatã! e. produselede papetãrie sau de mobilã; f. produsele de papetãrie sau cu preþuri <100; g.produsele de mobilã cu preþuri<100 ºi (stocuri =0 sau stocuri =100); h. toateprodusele.

3. O societate comercialã înregistreazã contractele ºi facturile în douã foi de calculale aceluiaºi registru. Pentru contract se reþin urmãtoarele informaþii: data ºi numãrulcontractului, numele furnizorului ºi valoarea contractului.

Aflaþi:

a) diferenþa dintre valoarea totalã contractatã ºi valoarea totalã facturatã;

b) câte contracte au valoare mai micã de 500 unitãþi bãneºti;c) câte contracte nu au facturi;

d) câte contracte au fost îndeplinite (valoare contract = suma valorilor facturilorprimite);

e) realizaþi o situaþie a tuturor contractelor în care sã aparã alãturi de valoarea înlei a contractului în format numeric cu douã zecimale ºi valoarea scrisã îneuro ºi dolari la un curs de schimb variabil.

Test 3Testarea urmãreºte verificarea modului în care s-a înþeles funcþionarea condiþiei ºi a

principalelor funcþii statistice


1. Introduceþi foaia de calcul din imaginea urmãtoare într-un registru nou. Refaceþiformulele. Indicaþi într-o nouã coloanã asociatã fiecãrei funcþii ce se calculeazã în funcþie.

2. Copiaþi baza de date în altã foaie de calcul ºi aflaþi:

a. numãrul ºcolilor care au plan de investitii

b. valoarea totalã a planului de investitii pentru ºcolile cu profil „fizicã chimie”

c. numãrul de elevi de la ºcolile „V.Lupu” ºi „V.Alecsandri”

d. numãrul maxim de profesori dintr-o ºcoalã

e. numãrul de produse cu stoc zero

f. preþul minim ºi cel maxim

g. calculaþi pentru fiecare profil o situaþie statisticã cu numãrul total de claseh. calculaþti pentru fiecare profil o situaþie statisticã cu numãrul total de ºcoli

Fiecare funcþie de la sarcina 1 se va puncta cu 1 punct.

Fiecare cerinþã a sarcinii 2 se va puncta cu 0,5 puncte.

1 punct din oficiu.

41LECÞIA 5 • OPERAÞ I I DE CÃUTARE

Lecþia 5Operaþii de cãutare

�� Cãutarea în liste prin formulare Excel�� Cãutarea ºi editarea datelor prin Edit��Find/Replace�� Funcþia de cãutare VLOOKUP�� Funcþiile ISNA, ISERROR, ISBLANK

5.1. Cãutarea ºi editarea informaþiilor prin formularul asociat listei

Operaþia de cãutare ºi poziþionare pe articolele care îndeplinesc o anumitã condiþie sepoate face ºi prin formularul asociat listei, prin butonul Criteria. Formularul afiºeazã unarticol cu valori vide. Vom completa valorile pentru acele câmpuri folosite în condiþie.Dupã introducerea criteriului formularul va permite vizualizarea numai a liniilor careîndeplinesc condiþia. Anularea filtrãrii se face prin butonul Form. Putem folosi ºi caracterelede substituire.

De exemplu, dorim sã vedem ºi eventual sãmodificãm datele privind bãieþii în vârstã de 10 ani.Vom apãsa butonul Criteria ºi vom introduce valoarea10 corespunzãtoare câmpului vârstã ºi valoarea “m”pentru câmpul sex.

Revenim în formular prin butonul Form. Închide-rea formularului se face prin butonul Close.

Sarcini de laborator1. Folosiþi formularul pentru baza de date ghizi ºi afiºaþi doar elevii care sunt repartizaþi

la parter.

2. Afiºaþi doar elevii din sãli indiferent de salã!Indicaþie. Folosiþi caracterul de substituire ? sau *. Ex: „sala?” sau „sala*”

5.2. Cãutarea ºi editarea informaþiilor prin Edit��Find

O modalitate des folositã pentru cãutarea ºi poziþionarea cu sau fãrã corectarea uneivalori se face prin Edit�Find. Fereastra care se deschide permite selectarea operaþiuniiºi precizarea domeniului de cãutare: foaia de calcul curentã sau registrul curent. Se

Fig. 5.1. Formular asociat vârstei.


poate cãuta pe coloana sau linia anterior selectatã sau în tot registrul. Pentru o comandãFind All se scrie ºi un raport cu adresele celulelor gãsite, care permite o poziþionaredirectã ºi rapidã pe foaia de calcul ºi celulã.

Exemplu:Dorim sã cãutãm ghizii cu vârsta de 10 ani ºi am completat fereastra de dialog Edit�Findca în imaginea alãturatã. Butonul Find All a afiºat un inventar al celulelor unde s-agãsit valoarea.

5.3. Funcþia de cãutare VLOOKUP

Operaþiile de cãutare analizate pânã acum au folosit lista Excel ca tabel unde secautã ºi constante pentru cãutare. Dar ce ne facem când valoarea cãutatã este variabilãºi se gãseºte într-o listã, iar zona de cãutare este în altã listã? În plus, în urma cãutãriidorim nu o poziþionare pe linia unde se gãseºte valoarea, ci chiar o altã informaþie.

Vom folosi funcþia de cãutare Vlookup cu formatul general:

VLOOKUP(ce-se-cautã; unde-se-cautã; coloana-pentru-rãspuns; True/False),

unde <ce-se-cautã> reprezintã adresa celulei care conþine valoarea ce se cautã, <unde-se-cautã> reprezintã o listã organizatã pe cel puþin douã coloane, din care prima coloanãeste cea în care se cautã. Rãspunsul poate fi valoarea din coloana specificatã în parametrul3. Cãutarea poate fi exactã (parametrul al 4-lea este False) sau aproximativã (True).

Dacã este o cãutare exactã, atunci se foloseºte linia unde s-a gãsit valoarea cãutatã.Dacã este o cãutare aproximativã, atunci se foloseºte linia anterioarã celei pe care s-a

Fig. 5.4. Fereastra Find and Replace conþine celulele cãutate din toate foile registrului


gãsit o valoare mai mare. În acest ultim caz, domeniul de cãutare trebuie ordonat crescãtorpentru prima coloanã.

Sã urmãrim modul de lucru cu funcþia Vlookup prin exemple.

Exemplul 1.

Cãutare exactã– formatul funcþiei este VLOOKUP(ce-se-cautã, unde, ce se întoarce, FALSE)La un depozit en-gros se vând produse de mobilã pe baza unor comenzi fãcute declienþi. Aceºtia au la dispoziþie un catalog ºi comanda conþine doar codul produsuluiºi cantitatea. Depozitul trebuie sã înregistreze comenzile ºi sã calculeze valoareafiecãrei comenzi.

Analiza problemeiSunt douã categorii de informaþii de intrare. Pe de o parte, informaþii despre produse,cum ar fi codul, numele, preþul. Pe de altã parte, informaþii despre comenzi: numeleclientului, produsul ºi cantitatea solicitatã. Informaþia de ieºire este valoarea fiecãreicomenzi pentru care avem nevoie de preþul produsului – care se gãseºte în altã listã.Deci va trebui sã o cãutãm!Proiectarea foii de calcul:Lista cu produse se aflã în domeniul B2:D7, iar comenzile sunt în domeniul G3:I7.Dorim sã asociem preþul produsului de pe fiecare comandã ºtiind codul sãu.

Fig. 5.3. Cãutare exactã prin Vlookup. Organizarea foii de calcul

Rezolvare:Pasul 1. Proiectãm zona pentru baza de date cu produse. În prima coloanã se va gãsicodul, apoi numele ºi preþul fiecãrui produs. Formatãm la tipul dorit. Denumim domeniulB1:D1000=”produse”.

Pasul 2. Proiectãm zona de comenzi care va conþine pentru fiecare comandã codulprodusului cerut, clientul ºi cantitatea.

Pasul 3. Adãugãm o coloanã Preþ. Vom pune formula J3=Vlookup(G3;produse;3;False )


Cum se executã funcþia? Se cautã valoarea din G3 în prima coloanã din domeniul„produse”. Dacã se gãseºte, se întoarce preþul din coloana a doua. Dacã nu se gãseºtevaloarea cãutatã, atunci funcþia întoarce o eroare de tip #N/A. (nu existã valoridisponibile).Pasul 4. Verificãm cu date de test. Observãm mesajul de eroare dacã nu se gãseºtecodul.

Exemplul 2:

Cãutare exactã– formatul funcþiei este VLOOKUP(ce-se-cautã, unde, ce se întoarce, FALSE)Dorim o foaie de calcul pentru un micmagazin ce vinde telefoane celulare.Operatorul trebuie sã afle rapidinformaþii despre o persoanã plecândde la numãrul ei de telefon. Observaþiîn captura ecran o astfel de foaie înfigura alãturatã. Baza de date conþine,pe lângã numãrul de telefon alproprietarilor de telefoane mobile,numele ºi adresa acestora. În zonavizibilã se va putea introduce numãrulde telefon ºi automat se va afiºanumele persoanei – dacã numãrulexistã!

Cum se procedeazã?Pasul 1. Creãm lista cu informaþii despre proprietari, pe care am numit-o „telefoane”.Numerele de telefon nu sunt ordonate.

Pasul 2. Am plasat o valoare de test în zona A4 ca fiind numãrul de telefon pe care îlvom cãuta.

Pasul 3. Am pus formula în celula C4=Vlookup(A4; telefoane;2;False) ºi o copiem înrest – pe cel puþin 4 celule!

Pasul 4. Am verificat funcþionarea formulei, dând valori care se gãsesc în listã, dar ºiunele care nu se gãsesc, caz în care s-a afiºat un mesaj de eroare

DeciCãutarea exactã se solicitã prin ultimul parametru (False) atunci când domeniul de

valori unde se cautã nu este ordonat.

Cum funcþioneazã cãutarea exactã? Valoarea datã în funcþie este cãutatã pe primacoloanã în domeniul de cãutare pânã când:

a. se gãseºte o valoare egalã – caz în care funcþia se poziþioneazã pe linia acesteivalori ºi întoarce valoarea corespunzãtoare gãsitã în coloana 2 (adicã numele persoanei)

b. ajunge la sfârºitul domeniului de cãutare – caz în care întoarce mesajul de eroare #N/A

Fig. 5.4. Organizarea foii pentru cãutare exactã


Tratarea erorilor. Funcþile iSNA, iSERROR()Atunci când cãutarea eºueazã funcþia afiºeazã un mesaj standard #N/A. Estebine sã specificãm un mesaj adecvat. Pentru aceasta putem folosi funcþia

iSNA(), care întoarce True dacã prin evaluarea parametrului s-a obþinut o situaþie de eroaretip #N/A, sau funcþia iSERROR(), care trateazã orice caz de eroare.

Vom scrie deci formula pentru celula J3=

=iF( iSNA (Vlookup(G3;produse;3;False));”cod eronat”; Vlookup(G3; produse; 3; False ))

Sarcini de laborator1. Duplicaþi foaia de calcul „comenzi” ºi lucraþi urmãtoarele sarcini în noua foaie.

2. Asociaþi la lista comenzilor încã o coloanã în care sã obþineþi prin cãutare numeleprodusului comandat, luând pentru fiecare comandã codul produsului din prima coloanã.

3. Creaþi o zonã de cãutare pentru clienþi în care sã codificaþi clienþii ºi sã reþineþinumele ºi adresa ºi contul la bancã. Codul clientului sã fie un ºir.

4. ªtergeþi din coloana Client a bazei de date despre comenzi valorile text, formataþi laîntregi ºi puneþi coduri care se repetã, nu neapãrat ordonate.

5. Adãugaþi coloanele Nume_client ºi cont_la_bancã preluând prin cãutare valorilecorespunzãtoare codului de client pentru fiecare comandã în parte

Exemplul 3:

Cãutare aproximativã într-un interval de valori– formatul funcþiei este VLOOKUP(ce-se-cautã, unde, ce se întoarce, TRUE)La o editurã se acordã reducere în funcþie de numãrul de cãrþi comandate. Astfel,pentru comenzi mai mari ca 65 buc. se acordã 30%, între 10 ºi 30 buc. se acordã5%, iar între 30 ºi 50 reducerea este de 20%.

Dorim o foaie de calcul care sã înregistreze comanda ºi sã calculeze reducerea.

Observaþi în imaginea de mai jos imaginea foii de calcul.

Analiza problemeiAvem nevoie de o bazã de date despre comenzi unde sã reþinem datele despre fiecarecomandã ºi cantitatea solicitatã. O altã categorie de informaþii se referã la procentelede reducere ºi condiþiile acordãrii lor. Judecãm astfel: Dacã se comandã mai puþin de


10 nu vom avea reducere. Prima reducere, în valoare de 5%, este pentru 10 buc. Adoua valoare este reducerea de 10% pentru 30 buc. Deci între 10 ºi 29 buc. reducereaeste aceeaºi, de 5%.Trebuie sã ºtim cum funcþioneazã funcþia de cãutare VLOOKUP inexactã.

Funcþia cautã pe coloanã pânã când:

a. întâlneºte o valoare egalã – caz în care se opreºte ºi se poziþioneazã pe linia gãsitã;

b. întâneºte o valoare mai mare decât valoarea cãutatã – caz în care se poziþioneazãpe linia anterioarã ultimei valori;

c. ajunge la sfârºitul tabelului – caz în care se poziþioneazã pe ultima linie.

Proiectarea foii de calculPasul 1. Vom construi tabelul de cãutare punând numãrul de bucãþi limitã pentru fiecareprocent.Pasul 2. Proiectãm baza de date pentru comenzi (coloanele G:H).

Pasul 3. Pe coloana i punem formula de cãutare aproximativã i3=Vlookup(G3;reducere;2;true) ºi copiem pe coloanã.

Pasul 3. Punem valori de test ºi urmãrim funcþionarea formulei.

Sarcini de laborator1. ªtim cã dacã media este peste 9.50 elevului i se dã premiul 1, dacã este între

9.50 ºi 9 obþine premiul 2, iar dacã este de la 9 la 8.50 obþine premiul 3.

2. Adãugaþi coloana Premiu_var_if ºi puneþi formula de atribuire automatã a premiuluiprin funcþia if.

3. Adãugaþi coloana Premiu_var_lookup ºi folosiþi pentru calcularea premiului funcþiaVlookup.

4. Deschideþi registrul Balcaniadã. Separaþi în douã foi datele astfel încât sã aveþi:

a. în foaia de calcul Ghizi o bazã de date despre elevii ghizi cu: nume-ghid, vârsta,locaþia, ºcoala-de-unde-vine, sex, codul-musafirului de care se ocupã;

b. în foaia de calcul Participanþi o bazã de date cu toþi participanþii cu urmãtoarelecâmpuri: cod-participant, nume, þarã, vârstã, sex, disciplina de concurs, bursa,taxe_ºi_alte_cheltuieli. Observaþi cã legãtura dintre ele se face prin codulparticipantului.

5. Realizaþi o altã foaie de calcul, concatenând datele din cele douã baze. Veþi copiafoaia ghizi ºi veþi asocia fiecãrui musafir, prin funcþia Vlookup numele, þara, vârsta, sexul,disciplina de concurs.


Testul vizeazã evaluarea cunoºtinþelor ºi a deprinderilor de a cãuta valori în liste Excel

Deschideþi registrul „inspectorat” ºi poziþionaþi-vã în foaia de calcul „ºcoli.

1. Folositi Edit�Replace ºi modificaþi valoarea „da” la „DA” ºi „nu” la „NU” automatpentru toate liniile atât în coloana unde am reþinut dacã are cantinã, cât ºi în coloanapentru cãmin. Folosiþi Edit�Find ºi cãutaþi în foaia de calcul „ºcoli” toate articoleleunde numãrul de clase este egal cu 10.

2. Am dorit sã scriem cu roºu adresa ºi cu majuscule cuvântul Iaºi din adresã numaipentru ºcolile din aceastã localitate. Pentru aceasta am folosit comanda Edit�Find�Replace All. Urmãriþi fereastra de dialog. Credeþi cã am completat corect? Unde amgreºit? Cum pot sã repar?

Test 4

3. Creaþi o altã foaie de calcul cu numele Profesori în care sã reþineþi datele despreprofesori: numele, salariul de bazã, vechime (în ani), codul ºcolii unde lucreazã. Puneþivalori pentru 5 profesori, din care 3 sã lucreze la aceeaºi ºcoalã. Folosiþi pentru ºcoalã uncod numeric cuprins între 1 ºi 10 (0 dacã nu lucreazã!).

4. Completaþi prin cãutare într-o nouã coloanã numele ºcolii unde lucreazã, ºtiind cãcodul ºcolii este chiar numãrul curent din baza de date ªcoli.

5. Scrieþi în celulã mesajul „nu lucreazã” dacã nu se gãseºte codul ºcolii.

6. Completaþi tabelul „profesori” cu o nouã coloanã „profil” în care sã puneþi profilul ºcoliiunde este titular profesorul. Completaþi cu valori din baza de date ªcoli folosind funcþia Vlookup!

7. Sã se afiºeze pentru fiecare profesor sporul de vechime în muncã, ºtiind cã dacãvechimea este intre 5-10 ani se acordã 10% din salariu, între 11 ºi 20 ani se acordã 15%,între 21-25 de ani se acordã 20%, iar peste 26 de ani se dã 25% din salariul de bazã.

a. folosiþi IF ºi construiþi formula în coloana spor1;

b. folositi VLOOKUP ºi construiþi formula în coloana spor2.8. Formataþi coloana spor2 astfel încât profesorii care nu au spor sã aibã celulele goale.

Fiecare sarcinã va fi notatã cu câte 1 punct + 1 punct din oficiu.


Lecþia 6Gruparea ºi totalizareadatelor

�� Totalizare

�� Tabele pivot

6.1. Subtotalizarea datelor organizate în liste Excel

Gruparea articolelor unei liste în funcþie de valorile dintr-una sau mai multe coloane ºiobþinerea unor sume, medii, valori maxime sau minime pentru grup sunt operaþii realizateautomat de Excel prin meniul Data�Subtotal.

Fereastra de dialog Subtotal permite fixarea criteriului de grupare prin selectareacoloanei din lista At each change in, a funcþiei de totalizare din lista Use function, alocului unde se vor calcula valorile prin selectarea casetelor de validare corespunzãtoarecoloanelor din tabelul sursã (Add subtotal to). Fiecare grup de articole poate fi plasat pe onouã foaie dacã activãm comutatorul Page break between groups. De asemenea liniile detotalizare pot fi aºezate înaintea grupului sau dupã (Summary below data).

Aceeaºi fereastrã permite anularea subtotalizãrii prin butonul Remove all.

Exemplul 1.

Folosind baza de date a ghizilor ºi ghiduºelor din Iaºi dorim sã aflãm numãrul de copiide pe fiecare salã.

Fig. 6.1. Subtotalizarea datelor

49LECÞIA 6 • GRUPAREA ºI TOTALIZAREA DATELOR

Cum facem?Pasul 1. Selectãm tabelul.

Pasul 2. Ordonãm dupã câmpul Locaþie.

Pasul 3. Deschidem fereastra Subtotal prin Data�Subtotal.

Pasul 4. Fixãm criteriul de grupare alegând din lista At each change coloana „Locaþie”.

Pasul 5. Selectãm funcþia Count.

Pasul 6. Indicãm locul unde se va face numãrarea liniilor din grup ca fiind coloanavârsta. Tabelul se transformã ºi apar liniile de subtotal ºi de total general. Putem editamesajele introduse de Excel.

Important este cã aceste valori se actualizeazã automat odatã cu modificarea valorilordin grup.

Observaþi nivelurile de grupare de pe primele coloane ale foii de calcul.Putem obþine o formã centralizatã sau detaliatã dacã acþionãm butoanele deexpandare plasate înaintea tabelului prin care se afiºeazã sau se ascunddetaliile la nivelul dorit.

Sarcini de laborator1. În baza de date Ghizi obþineþi numãrul de elevi ghizi pe grupuri de vârste.

2. Copiaþi situaþia centralizatoare în altã foaie de calcul, formataþi astfel încât sã aibãtitlu, spaþii separatoare între subtotaluri, mesajele scrise în româneºte.

3. Obþineþi o situaþie centralizatoare a elevilor ghizi pe locaþii calculând vârsta mediepentru fiecare locaþie.

4. Obþineþi doar vârsta medie pe fiecare locaþie, fãrã detalii, în altã foaie de calcul.Formataþi-o în vederea tipãririi la imprimantã.

Exemplul 2.Obþinerea unei situaþii centralizate pe douã nivele de grupare

Dorim o situaþie centralizatã a numãrului deºcolari pe sãli, pentru fiecare salã separând fetelede bãieþi.

Cum am fãcut?Pasul 1. Am ordonat tabelul dupã douã chei:locaþie ºi sex.

Pasul 2. Am deschis Data�Subtotal ºi am pusgrup pentru locaþie ca în exemplul anterior. Amdezactivat butonul Summary below data.

Pasul 3. Am deschis din nou fereastra Subtotalpentru subgrup pentru coloana Sex.

Pasul 4. Am închis butonul de expandare la niveluldetaliilor.

Fig. 6.2. Butoane de expandare la nivelul grupului


Fig. 6.4. Fixarea datelor sursã

Pasul 5. Am formatat tabelul schimbând textele implicite, inserând linii separatoare,ascunzând coloanele neinteresante

Sarcini1. Obþineþi în foaia de calcul ghizi situaþia centralizatã a vârstei medii pe ºcoli ºi

pentru fiecare ºcoalã separat pentru fete ºi bãieþi.

2. Afiºaþi în altã foaie de calcul situaþia numãrului de musafiri pe þãri, pentru fiecaretarã, pe sexe ºi grupe de vârstã.

3. Modificaþi în situaþia centralizatoare unele valori (de exemplu schimbaþi vârsta unuimusafir sau þara) ºi vedeþi dacã se reflectã aceastã corecþie în rândurile centralizatoare.

4. Modificaþi situaþia în sensul introducerii unor noi linii sau ºtergerii unor linii. Seactualizeazã liniile de total?

6.2. Tabele pivot

Obþinerea unei situaþii centralizatoare de tip matricial având datele centralizate dindouã puncte de vedere se poate face prin asistentul Pivot Table Wizard. Apelatã prinopþiunea Data�Pivot Table, aplicaþia asistent ne va îndruma pas cu pas pentru realizareasituaþiei centralizatoare.

Exemplul 2:Dorim sã numãrãm copiii pe vârste ºi sex pentru fiecare salã sau total. Raportul poateapãrea sub forma prezentatã mai jos.

Vom explica modul de lucru cu tabele pivot rezolvând problema.

Pasul 1. Precizãm care este tabela de date sursã. De obicei, este o listã de dateExcel. Dacã se doreºte crearea unei tabele pivot din altã bazã de date – de exemplu otabelã Access – va trebui sã fiestabilitã comunicarea directã întreaplicaþiile Access ºi Excel!

Pasul 2. Se selecteazã porþiuneadin tabelã care va reprezenta sursade date.

Fig. 6.3. Situaþia????????? anumãrului decopii pe vârstã ºisex filtrate la nivel???????


Pasul 3. Ultima fereastrã de dialog a TablePivot Wizard ne permite sã alegemplasarea tabelei pivot în altã foaie decalcul sau în aceeaºi foaie unde suntdatele sursã.Prin butonul Layout se trece la proiec-tarea formei ºi conþinutului tabelei pivot, iar prin butonul Options se deschide paginacu opþiuni suplimentare (dacã se va face totalizarea automatã pe linii sau/ºi coloane,dacã se va actualizatabela odatã cudeschiderea foii decalcul care conþinedatele sursã, dacã serealizeazã unautoformat tabeleietc.)

Fig. 6.7. Pentru proiectarea tabelei pivotse executã Drag&Drop de la câmp la zonatabelei.

Fig. 6.8. Plasarea criteriilor degrupare pe linii ºi coloane. Filtruleste la nivel de paginã: locaþie.Operaþia este de numãrare.

Fig. 6.9. De obicei, în cazul în care câmpul plasateste nenumeric se asociazã funcþia Count, iar dacãeste numeric se asociazã funcþia Sum. Putemschimba funcþia prin dublu-clic pe funcþia din zonaData. Se deschide o fereastrã de dialog PivotTableField, de unde vom putea alege funcþia doritã.

Fig. 6.5. Fixarea domeniului pentru datele sursã

Fig. 6.6. ????? situaþiei pivot


Pagina Layout prezintã macheta tabelei. Zona Page va folosi la filtrarea datelor dintabela pivot. În zona Row vom plasa câmpul/câmpurile din tabela sursã pe care ledorim pe linii. Zona Column va cuprinde câmpul/câmpurile dorite pentru coloaneletabelei rezultat.Zona Data se referã la conþinutul tabelei (aflat la intersecþia liniilor cu coloanele)Poate sã fie valoarea unui câmp sau o funcþie.

Tot aici vom putea fixa formatul datelor afiºate prin butonul Number.Odatã terminatã proiectarea tabelei pivot, aceasta se deschide în aceeaºi foaie decalcul sau în alta ºi are asociate douã elemente de interfaþã foarte utile:

Prin operaþii detragere ºi plasare cumouse-ul putemschimba rapidconþinutul din tabelapivot, inversa liniilecu coloanele,adãuga/ºtergecriteriile de gruparepe linii/coloane.

1. Fereastra Pivot Table Field List conþine toate câmpurile selectate iniþial din tabelasursã.

2. Bara de butoane PivotTable permite modifi-carea tabelei pivot prin schimbarea aspectuluiºi/sau a conþinutului.

3. Meniul PivotTable conþine mai multe operaþii pe care le putem desfãºura asupratabelei pivot.

Astfel, putem cere sã se formateze tabela sub forma unuiraport, sã se creeze un grafic sau sã se apeleze aplicaþiaasistent.

Comanda Refresh data este utilã de fiecare datã când semodificã sursa pentru a actualiza tabela pivot.

Sarcini de laboratorFolosiþi registrul Balcaniada. Completaþi-l cu datele necesare rezolvãrii sarcinilor.1. Afiºaþi o situaþie matricialã a numãrului de musafiri pe þãri ºi vârste.

2. Modificaþi situaþia pivot prin adãugarea la nivel de paginã a câmpului sex ºi afiºaþi:

a. Numãrul de musafiri fete pe þãri ºi vârste.

b. Numãrul de bãieþi pentru fiecare þarã ºi vârstã.

Fig. 6.10. Fereastra Field List


3. Schimbaþi forma situaþiei punând pe linii sexul, pe coloane vârsta ºi la nivel depaginã þara. Ce obþineþi?

4. Realizaþi o tabelã pivot pentru vârsta medie a ghizilor pe ºcoli ºi locaþii.

5. Modificaþi raportul pentru a obþine vârsta medie a musafirilor. Se poate prin fereastraField?

6. Modificaþi datele din baza de date. Adãugaþi un elev, modificaþi vârstele sau locaþiile.Observaþi dacã aceste modificãri se reflectã automat în rapoartele fãcute. Ce facem ca sãle actualizãm?

7. Creaþi un raport cu sumele totale corespunzãtoare burselor pentru fiecare þarã ºidisciplinã de concurs. Dorim ca la sfârºitul raportului sã nu se calculeze totalul general,ci numai pe fiecare linie în parte.

6.3. Folosirea câmpurilor calculate în tabele pivot

Sã presupunem cã pentru participanþii la concurs organizatorii oferã o bursã, dar fiecareelev are de achitat ºi anumite taxe. Deci lista participanþilor va conþine câmpurile: nume,þarã, punctaj, bursã, taxe, clasã.

Se doreºte o situaþie matricialãa sumelor totale acordate dreptburse pe þãri ºi clase. Sã se adaugetotalul taxelor ºi diferenþa de platã.

Observaþi situaþia doritã.

Prin ce diferã tabelul centra-lizator de celelalte? Prin faptul cãîn zona de date avem 3 funcþii:sum(bursa); sum(taxe) ºi sum(rest-de-plata).

Implicit, aplicaþia asistent de cre-are a tabelelor pivot accepta osingurã funcþie totalizatoare. Pentrucelelalte câmpuri care se numesccâmpuri calculate (calculated fields)folosim Insert�Calculated fields.

Cum facem?Pasul 1. Vom proiecta o tabelã pivot

precizând câmpurile folosite pentru rânduri(þarã), coloane (clasã) ºi funcþia totalizatoare(sum(burse)).

Pasul 2. Ne poziþionãm pe foaia de calculunde am obþinut tabelul pivot ºi construim primul câmp calculat. Deschidem fereastra dedialog pentru inserare câmp calculat prin Insert" Calculated Fields. În lista Fields suntafiºate câmpurile bazei de date Excel. Putem sã le inserãm în formulã prin butonul InsertFields.


În caseta cu text Name vom da numediferit de numele de câmp, chiar dacãvaloarea este chiar cea din câmpul taxe.În caseta cu text Formula vom insera pre-cedat de semnul egal, câmpul taxe. Ob-servaþi formula. Apoi apãsãm butonul Add.

Fig. 6.12. Inserarea unui câmp nou cuvaloarea unui câmp existent. Dupãconstruirea formulei apãsãm butonul <add>ºi noul câmp calculat este automat trecut înField list.

Pasul 3. Adãugãm la tabela pivot câmpul diferenþãîntre bursã ºi taxe astfel:

a) Deschidem fereastra Insert�Calculated Fields.

b) Dãm un nume câmpului ºi o formulã de calculare.

c) Prin butonul <add> se adaugã câmpul calculat lalista de câmpuri ºi implicit în tabela pivot.

Observaþi FieldList extinsã cucâmpurilecalculate numitecmp_taxe ºicmp_diferente.

Observaþie. Dacã dorim inversarea funcþiilor în zona de date se poate folosi aplicaþiaasistent apelatã din meniul PivotTable.

Putem da alt aspect situaþiei matriciale folosind Format�Autoformat.

Lista formulelor afiºatã prin meniulPivotTable�Formulas�List Formulas.

Fig. 6.14. Meniul contextual Pivot Trash

Fig. 6.13.Inserarea unuicâmp nou cuvaloareacalculatã


6.4. Reprezentarea prin grafic a situaþiei matriciale prin Pivot Chart

Odatã proiectatã situaþia matricialã, putem sã alegem ca formã de vizualizare arezultatelor nu numai raportul sub formã tabelarã, ci ºi sub formã graficã. Acest lucru sepoate solicita prin butonul PivotChart de pe bara de butoane PivotTable sau din meniulcontextual. Se sugereazã un grafic ale cãror elemente le putem redefini prin meniul Chart.

Foarte important este cã avem posibilitatea filtrãrii datelor din grafic prin listele asociateliniilor, coloanelor ºi conþinutului (dacã avem mai multe funcþii în zona de date).

Situaþiile centralizatoare matriciale sau de tip tabelã pivot au câteva caracteristiciimportante:

1. Sursa de date poate fi un tabel Excel sau o bazã de date Access, sau alte surseexterne;

2. Pot fi rearanjate interactiv pentru a afiºa anumite aspecte necesare analizei datelor.Utilizatorul poate hotãrî ce funcþii se utilizeazã, ce vor conþine coloanele ºi liniile tabeleipivot, dacã se va calcula sau nu totalul pe linii/coloane/general.

3. Se poate aplica un filtru asupra datelor care vor fi vizualizate în tabela pivot.

4. Tabela pivot are câmpuri ce corespund, în general, câmpurilor din baza de datesursã, dar pot fi ºi câmpuri calculate (prin formule, funcþii). Tabelul se restructureazãautomat, interactiv, prin operaþii de schimbare a poziþiei câmpurilor.

5. Dacã datele din tabelul iniþial se modificã este necesarã actualizarea prin opþiunea

Refresh Data din meniul PivotTable sau butonul .

Fig. 6.14. Raportsub formã degrafic


Sarcini de laborator*Activitatea de comercializare a citricelor în cadrul societãþii „LACRIMA” se face

prin vânzãtori ambulanþi angajaþi. Fiecare vânzãtor trebuie sã comunice ce produs avândut, unde (localitatea), când (luna) ºi cât (valoarea). Se cere:

A. Sã se proiecteze o foaie de calcul pentru baza de date.

B. Formataþi foaia de calcul ºi stabiliþi reguli de validare. Se vor afiºa mesaje de informareºi de alertã în cazul introducerii eronate. Se va interzice introducerea unei valori eronatãdoar în cazul lunilor; în rest se poate accepta ºi o altã valoare decât cea din listã.

Se ºtie cã:

• vânzãtorii sunt Ionescu, Albulescu, Vasilescu ºi Marinescu;

• lunile sunt scrise sub formã prescurtatã Ian, Feb, Mar;

• localitãþile sunt iasi, Bacãu, Zalãu, Suceava, Cluj;

• produsele vândute sunt Banane, Porto, Lãmâi;

• valoarea vânzãrilor este cuprinsã în intervalul 1-1000.

C. Afiºaþi baza de date ordonatã pe luni, produse, localitãþi, persoane.

D. Aranjaþi datele astfel încât sã obþineþi o situaþie a vânzãrilor medii pe luni prinsubtotalizare.

E. Afiºaþi o situaþie centralizatoare a sumei totale obþinute prin vânzare pe localitãþi,pentru fiecare localitate calculând ca subtotal suma vânzãrilor pe sortimente de produse.

F. Aflaþi prin filtrare directã care sunt valorile vânzãrilor de banane în localitatea Iaºiîn luna ianuarie ºi ce vânzãtori le-au realizat.

G. Sã se obþinã un centralizator al vânzãrilor pe localitãþi ºi pe luni. Pentru fiecarelocalitate se va detalia cantitatea total vândutã pe produse. Se va folosi subtotalizarea.

H. Obþineþi un tabel pivot pentru selectarea lunii ºi afiºarea vânzãrilor totale pe persoaneºi localitãþi din luna respectivã.

I. obþineþi un tabel cu suma vânzãrilor realizate de fiecare persoanã sau toate la un locpe luni, localitãþi ºi produse.

J. Pentru fiecare vânzãtor se înregistreazã atât câºtigurile, cât ºi cheltuielile de desfacerea produselor în fiecare localitate ºilunã pentru fiecare tip de produs.Adãugaþi douã coloane ºi completaþi-le cu valori oarecare.

K. Afiºaþi o situaþie în care sãînsumaþi câºtigurile, costurile ºibeneficiile pentru fiecare persoanã/produs/localitate.

L. Selectaþi doar vânzãrile deportocale din luna ianuarie cu valoricuprinse între 500 ºi 1000.


M. Obþineþi o situaþie a vânzãrilor sub forma luna ºi valoareatotal vândutã. Atenþie! lunile apar în ordine cronologicã, nualfabeticã!

În plus, este necesarã formatarea tabelului.

N. Modificaþi datele din luna ianuarie adãugând o nouãlinie cu valoarea 300. Obþineþi acum acum tabelul ordonatdescrescãtor, dupã valoare!

Test 5

Testul urmãreºte evaluarea abilitãþilor de grupare ºi totalizare a datelor Excel.

1. Deschideþi registrul inspectorat. Verificaþi sã existe baza de date ªcoli care sã conþinãcel puþin câmpurile urmãtoare: cod, nume ºcoalã, localitate, tipul, filiera, profil, specializare,numãr de clase, numãr de elevi din care numãr de fete, valoare de inventar, fond de investiþii,daca are cãmin, dacã are cantinã etc. Adãugaþi eventual datele necesare rezolvãrii sarcinilorurmãtoare. Verificaþi ca sã existe cel puþin douã filiere, cu cel puþin trei profile diferite, cucel puþin 3 specializãri diferite. Se poate ca o anumitã specializare sã fie la mai multeprofile (ex. „Matematicã-informaticã” la profilul militar ºi la cel teoretic).

2. Aflaþi câþi elevi sunt la fiecare specializare în parte ºi total pe inspectorat. Afiºaþi înfoaia de calcul sit_1.

3. Desenaþi graficul numãrului de elevi pe specializãri (obþinut la sarcina anterioarã).

4. Aflaþi câte ºcoli sunt în total la fiecare profil, din care câte au cãmin ºi câte nufolosind subtotalizarea. Afiºaþi în foaia sit_2.

5. Afiºaþi sub o formã matricialã numãrul de clase din ºcoli pe profil (pe linie) ºilocalitãþi (pe coloanã) cu posibilitatea filtrãrii ºcolilor care au cãmin de cele care nu aucãmin.

6. Folosiþi tabela pivot creatã anterior ºi modificaþi forma astfel încât sã putem obþinenumãrul de elevi total pentru fiecare specializare ºi localitate, fãrã nici o filtrare.

7. Realizaþi un grafic pentru situaþia pivot obþinutã în sarcina anterioarã.8. Realizaþi o situaþie care sã cuprindã numãrul de elevi, numãrul de fete ºi numãrul

de bãieþi pe profiluri ºi pe localitãþi (atenþie, va trebui sã folosiþi câmpuri calculate!)

9. Realizaþi graficul situaþiei pivot obþinutã ca tabel în sarcina anterioarã ºi obþineþidoar graficul numãrului de bãieþi pe o localitate aleasã de voi.

Notã. Fiecare rãspuns se va nota cu 1 punct + 1 punct din oficiu.


Lecþia 7Probleme financiare

�� Funcþii financiare pentru fluxuri constante

�� Funcþii financiare pentru fluxuri variabile

Conceptul de bazã în problemele financiare este valoarea banilor în timp. Valoareaunei sume se schimbã în timp datoritã potenþialului câºtig de pe urma investirii sumei.Ne întrebãm adesea: Ce este mai valoros? 1 milion azi sau 1 milion peste 2 ani? Cumjudecãm? E clar cã este mai valoros azi un milion, pentru cã, dacã l-am depune o labancã (fãrã risc!) cu o dobândã de numai 3% pe an obþinem peste 1 an 1,030 mil. iarpeste 2 ani 1,060 mil.!

Deci o problemã ar fi sã dãm suma iniþialã pe care o investim, o dobândã ºi sã aflãmsuma viitoare. O altã problemã ar fi sã aflãm în cât timp am putea rambursa un împrumut,cunoscându-se comisionul sau dobânda plãtitã la fiecare termen, ratele plãtite fiind egale.

O altã problemã ar fi sã calculãm ce investiþie ar fi mai avantajoasã: sã depunem la obancã ºi sã plãtim rate constante sau sã investim la o firmã; unde achitãm sume variabile?

O modalitate de rezolvare ar fi simularea situaþiei cu ajutorul foilor de calcul parame-trizate, ca în lecþiile anterioare

O altã modalitate este folosirea funcþiilor financiare oferite de Excel. Sunt douãcategorii de funcþii:

a. pentru fluxuri constante de bani cum ar fi: PV (Present value – valoare curentã), FV(Future value – valoarea viitoare), PMT (payment – ratã), NPER (number of periods –numãr rate), RATE (rate 0 – dobândã).

b. pentru fluxuri inegale de plãþi cum ar fi IRR (internal rate of return) ºi NPV (netpresent value – valoare curentã netã).

7.1. Funcþiile financiare pentru fluxuri monetare constante

Formatul general este acelasi pentru toate funcþiile pentru flux constant folosite pentruo investiþie sau împrumut:

Funcþie (rate; nper; pmt;pv;[tip]unde

Rate = procentul dobânzii pe o perioadã de timp

59LECÞIA 7 • PROBLEME F INANCIARE

Nper = Numãrul total de perioade de timp de achitare a imprumutului sau depunere îninvestiþie

pmt = Plata (suma) periodicã

pv = Valoarea la începutul unei investiþii – sau împrumutul

fv = Valoarea la sfârºitul unei investiþii

tip = Tipul plãþii – ultimul parametru ia valoarea 1 pentru o platã la începutul perioadeiºi 0 pentru o platã la sfârºitul perioadei (implicit este 0 ºi poate lipsi!)

Dându-se oricare 4 dintre aceºti parametri, existã o funcþie care îl calculeazã pe al5-lea. De exemplu, pentru PMT (= suma periodicã) trebuie sã ºtim valoarea prezentã, ratadobânzii, numãrul de perioade ºi valoarea viitoare.

Sumele care reprezintã plãþi sunt introduse ca valori negative, iar sumele care reprezintãîncasãri se introduc ca valori pozitive.

1. FV(rata; nper; pmt;pv;[tip])calculeazã valoarea viitoare a unei investiþii bazatã pe depuneri regulate ºi constante

un numãr de perioade la o ratã a dobânzii fixã.

Rata = dobânda la banii depuºi pe o perioadã, nper = numãrul de perioade, pmt =suma ca ratã sau platã periodicã, pv = suma iniþialã depusã.

Exemplul 1.

Dispunem de un cont de economii în care depunem lunar câte 100 lei. Rata dobânzii labancã este 3% pe lunã. Cât vom avea în cont peste 10 luni dacã iniþial am depus 100 lei?

Funcþia este = FV(3%;10;-100,-100) = 1281 lei, din care noi am depus efectiv 100 +100*10 = 1100 lei, iar restul de 181 sunt sume prin capitalizarea dobânzii.

Sumele plãtite bãncii se trec în funcþia financiarã cu minus!

Nu e obligatoriu sã avemvaloarea iniþialã depusã.Exemplu FV(7%,10,-100)=

FV(7%,10,-100,0)

Observaþi în imagineacapturatã o foaie de calculpentru verificarea moduluide lucru cu funcþiile finan-ciare.

Am plecat de la formulelecunoscute din lecþiile ante-rioare. B7 = B2 B8 =suma_lunarã + B7 +(B7*rata_dobânzii) ºi amconstruit alãturi funcþia FV.

Fig. 7.1. Valoarea viitoare a unei depuneri regulate la bancã.


Fig. 7.2. Valoare prezentã

Sarcini1. Aflaþi cât voi obþine dacã banca la care am depus 500 lei are dobânda de 5% pe an

ºi eu am plãtit 10 luni? Nu am avut nici o depunere iniþialã.2. Am dat fratelui meu 100 $ cu promisiunea ca dupã un an sã-mi returneze 400 lei.

Oare am fãcut bine? Acum 100 $ valoreazã 350 lei dar am aflat cã dolarul va creºte cucel puþin 10%.

3. Observaþi foaia de calcul urmãtoare. Formulaþi o problemã pentru aceasta!

E F G H explicatii constante formula valoare rezultat 3 dobanda 9.60% 4 nr_rate 10 5 rata_periodica 0.00 lei

7 valoare viitoare ? =FV(F3,F4,F5,F8) 50,019.06 lei 8 valoare prezenta -20,000.00 lei

2. PV(rata;nper;pmt,[fv],[tip])

Calculeazã valoarea prezentã, actualizatã, a unei investiþii. Rata = dobânda la baniiinvestiþi, nper = numãrul de perioade, pmt = plata sau rata lunarã (fie o dãm, fie o primim)fv = valoarea pe care dorim sã o obþinem în viitor, tip = 0 dacã ratele se plãtesc laînceputul lunii sau –1 dacã ratele se plãtesc la sfârºitul perioadei.

Exemplul 2.

Dacã doresc sã obþin la bancã peste 10 luni 1281lei, care ar fi suma pe care ar trebui sã o depuniniþial? ªtim cã banca oferã 3 % pe lunã ºi lunarvoi depune 100 lei.

Este de fapt inversul problemei anterioare. Lafuncþia FV ºtiam cât depun iniþial ºi voiam sã aflucât voi obþine, la funcþia PV îmi propun, ºtiind cãtrebuie sã obþin o anumitã valoare peste o anumitãperioadã, sã aflu cât depun iniþial.

Funcþia folositã este PV(3%,10,-100,1281)

De ce trebuie sã punem rata-periodicã cu minus?Pentru cã este o platã cãtre bancã, deci se scotbani din buzunar.Suma viitoare pe care dorim sã o obþinem ne va fidatã de bancã, deci va fi trecutã cu plus.

Funcþia PV întoarce o valoare pe care trebuie (ACUM) sã o depunem (o vom scoate dinbuzunar!), deci ea apare ca numãr negativ.


Sarcini de laborator1. Aflaþi cât ar trebui sã am iniþial dacã lunar nu pot plãti nimic!

2. Aflaþi, dacã numãrul de rate este 20 ºi dobânda de la bancã creºte la 5%, cât vatrebui sã depun iniþial la o sumã aºteptatã de 5000 Lei, fãrã sã mai plãtesc lunar ceva.

Exemplul 3

Mihai are nevoie de un concediu de 2 ani undeva la munte pentru a-ºi redacta romanulla care viseazã. El a estimat cã pe lunã i-ar fi necesar 2000 lei. Banca la care are contMihai practicã o dobândã de 24% pe an. Cât ar trebui sã depunã ACUM în cont Mihaipentru ca sã poatã pleca în concediu ºi sã primeascã la începutul fiecãrei luni sumadoritã?

Unitatea de timp este luna. Dobânda bãncii trebuie calculatã la aceeaºi unitate detimp, deci vom pune dobânda =24%/12. Durata este 18 luni.

Rata lunarã este interpretatã ca o platã periodicã a bãncii cãtre Mihai, deci vom pune+2000. Valoarea viitoare este ceea ce s-ar afla în cont la terminarea perioadei deci 0.Vom folosi PV(24%/12, 24,2000,0,0)


1. Aflaþi cât va trebui sã plãteascã Mihai dacã dobânda este de 17% anual ºi dorestesã primeascã câte 1500 lei la finele fiecãrei luni timp de 1 an ºi jumãtate ºi sã mai aibãºi în cont la revenire suma de 3000 lei!

2. Observaþi foaia de calcul urmãtoare. Formulaþi o problemã pentru ea.

E F G H explicatii constante formula valoare rezultat 3 dobanda 9.60% 4 nr_rate 10 5 rata_periodica 0.00 lei

6 7 valoare viitoare 50,000.00 lei 8 valoare prezenta ? =PV(F3,F4,F5,F7) -19,992.38 lei

Valoarea prezentã esteceea ce Mihai ar trebui sãplãteascã acum bãncii –deci este cu minus.


3. RATE(nr-per; pmt; pv; [fv]; [tip];[procent])calculeazã dobânda bancarã pentru o anumitã platã (pv) rambursatã în perioade egale

de timp. Fv este valoarea ce va rãmâne dupã efectuarea tuturor plãþilor; tip are aceeºisemnificaþie ca la celelalte funcþii, iar procent sfãtuieºte Excel cã rata dobânzii poate fimai mare ca 100% (implicit este sub 99%)

Exemplul 4

Se face un împrumut de 203900 lei, care se va rambursa în 20 luni, plãtind câte20000 lei lunar. Ne intereseazã care este dobânda aferentã împrumutului.

Suma împrumutatã este valoarea prezentã = pv = 203900 lei (o trecem cu plus). Platalunara = pmt = –20000 lei (va fi cu minus).Parametrul fv este 0, pentru cã dorim sã achitãm întreg împrumutul.

Funcþia este rate (20; –20,000; 203900,0) = 7.5% rata dobânzii la bancã.

Sarcini de laboratorObservaþi foaia de calcul urmãtoare. Formulaþi o problemã pentru ea!

E F explicatii constante formula valoare rezultat 3 dobanda ? =RATE(F4,F5,F8,F7) 9.60% 4 nr_rate 10 5 rata_periodica 0 7 valoare viitoare 50,000.00 8 valoare prezenta (20,000.00)

4. Funcþia NPER(rata;pmt;pv;[fv][tip])Calculeazã numãrul de perioade de rambursare a unui împrumut sau de depozitare

intr-o investiþie.

Exemplul 5

Care este numãrul de luni în care trebuie achitat un împrumut de 203900 lei, dacãrata dobânzii la bancã este de 7.5% ºi lunar plãtesc 20000 lei?

Suma de 203900 lei este o sumã pe care o avem – deci o vom pune cu plus ºireprezintã parametrul pv, iar 20000 lei reprezintã suma pe care o plãtesc, deci vaavea în formulã semnul minus ºi se trece în parametrul pmt.

Funcþia folositã este = nper (7.5%; –20000; 203900) = 20 (luni).


Sarcinã

Observaþi foaia de calcul urmãtoare. Formulaþi o problemã care sã fie rezolvatã cuajutorul acesteia!

E F G H explicatii constante formula valoare rezultat 3 dobanda 9.60%

4 nr_rate ? =NPER(F3,F5,F8,F7) 10

5 rata_periodica 0.00 lei 6 7 valoare viitoare 50,000.00 lei 8 valoare prezenta -20,000.00 lei

5. PMT(rata; nr-per; pv;[fv];[tip])rata sau plata periodicã pe parcursul nr-per de timp.

Determinã suma achitatã periodic pentru plata unui împrumut precizat în parametrulpv (este valoarea pe care o luãm ACUM ºi o vom achita în sume egale dupã cele nr-perperioade de timp). Parametrul fv este zero dacã ne propunem sã achitãm împrumutul. Fvare semnificaþia ultimei rate pe care o vom plãti. Deci, în termenii funcþiilor financiare,suma trebuie sã fie negativã!

Exemplul 6

Pentru construirea locuinþei banca Junior m-a creditat cu 200 mii Euro la o dobândãde 8% pe an. Trebuie achitat creditul în 30 ani. Care vor fi ratele mele lunare?

Pentru cã 200000 este suma primitã de la bancã, o vom trece în parametrul pv =+200000, dar rata lunarã va trebui sã fie plãtitã, deci se va afiºa cu minus. Formulaeste = Pmt (8%,30,200000,0) = –17765

E F G H explicatii constante formula valoare rezultat 3 dobanda 8.00%

4 nr_rate 30 5 rata_periodica ? =PMT(F3,F4,F8,F7) -17,765.49 lei

6 7 valoare viitoare 0.00 lei 8 valoare prezenta 200,000.00 lei

Sarcinã de laboratorProiectaþi o foaie de calcul care sã serveascã pentru situaþiile de împrumut. Va trebui

sã satisfacã toate cererile unui client: a. sã afle care este suma pe care o poate împrumuta,b. care este numarul de rate, c. cât are de plãtit ca ratã, d. ce dobândã achitã?


7.2. Funcþii pentru fluxuri monetare variabile

6. IRR( grup-bani; procent)Calculeazã rata internã de rentabilitate preconizatã pentru o serie de plãþi sau încasãri

care nu se fac regulat ºi nici nu sunt egale. Rata internã de rentabilitate corespunde laprocentul de dobândã pentru care valoarea actualizatã a încasãrilor viitoare este egalã cuvaloarea investiþiei iniþiale. Rata este o valoare cuprinsã de obicei între 0% ºi 100%, iardacã este previzionatã o ratã mai mare trebuie sã folosim ºi ultimul parametru (procent!).

Este de fapt o variantã a funcþiei RATE, dar cu sume variabile.

Exemplul 1

Pentru investiþia mea de 10000 $ am primit urmãtoarele sume: în primul an 1000,apoi 5000, 5000, 6000 $. Care este rata dobânzii?

Suma investitã va trebui pusã cu minus pentru cã reprezintã o sumã datã, celelaltesume sunt cu plus pentru cã am primit banii!

Observãm cã la rate constante am putea aplica funcþia RATE pe durata de 4 ani cu unavans dat. Dar cum valorile nu sunt aceleaºi vom folosi functia iRR (-10000, +1000,+5000, +5000, +6000, 200%) = –14%

A B C D E20 ini�ial -10000 -10000 -10000 1000021 Luna 1 10000 1000 1000 -100022 luna 2 20000 5000 0 -100023 Luna 3 80000 5000 4000 -100024 Luna 4 10000 6000 1000 -1000

=IRR(B20:B24;200%) 179.03%=IRR(C20:C24;200%) 20.40%=IRR(D20:D24;200%) -16.15%=IRR(E20:E24;200%) -28.71%

7. NPV(rata-de-schimb; grup-bani)Calculeazã valoarea prezentã pentru un grup de plãþi viitoare. Este similarã funcþiei

PV, doar cã se aplicã la sume periodice variabile.


Exemplul 2Fie o ratã a dobânzii de 5%/an ºi o investiþie iniþialã de 20 milioane. Sumele pe carele aºteptãm sunt 8 mil. dupã primul an, apoi 5 mil. în la doilea ºi în urmãtorii 4 mil.,4 mil., 2 mil. lei.Care este valoarea prezentã a încasãrilor? Dar valoarea netã a investiþiei?

Calculãm = NPV(5%;8000;5000;4000;4000;4000) = 22034 mii lei (reprezintãvaloarea prezentã a încasãrilor viitoare)

Dacã din aceastã valoare scãdem valoarea investitiei obþinem venitul net 22,034mil – 20 mil. = 2,034 lei venit net!

Sarcini de laborator1. Doresc sã depun aceeaºi sumã de 10000 lei la mai multe firme care oferã sume lunare

diferite, ba chiar sunt unele care nu dau bani o lunã sau douã. La o firmã mi se cere sã plãtescîntr-o lunã 1000 $! Am centralizat datele în tabelul de mai jos. Pe care firmã sã o aleg?

Firma X Firma Y Firma Z Firma Tdobând� 3% 3% 2% 5%

luna 1 2000 5000 5000 5000

luna 2 3000 1000 1000 3000

luna 3 0 1000 1000 0

luna 4 3000 1000 1000 3000

luna 5 4000 1000 1000 -1000

luna 6 1000 1000 10000 4000

investi�ie ini�ial� -10000 -10000 -10000 -10000

2. Vreau sã-mi cumpãr o maºinã care costã 5000 $. Am economisit 500 $, iar restultrebuie sã-i împrumut de la o bancã. Aº dori ca sã termin împrumutul în 5 ani. BancaViorica cere o dobândã de 24% pe an. iar banca Margarina doar 2,4% pe lunã. Cât am deachitat ratã lunarã la fiecare bancã?

3. Am moºtenit o avere de 1 milion $ ºi doresc sã-i investesc într-o afacere cu un câºtigde 5% pe an. Cred cã pot sã-mi petrec restul vieþii pe o insulã tropicalã cu 80.000 $ pe an.

a. Câþi ani îmi vor ajunge banii? Doresc sã primesc banii cuveniþi la începutulfiecãrui an.

b. Dacã aº gãsi o bancã cu o ratã de 10%, câþi ani voi trãi liniºtit?

4. Sã presupunem cã aveþi 20000 lei ºi doriþi sã-i investiþi astfel încât sã obþineþi50000 în 2 ani. Care ar fi dobânda? Dar dacã am dori sã obþinem 5 milioane?

5. Vãrul Sãndel mi-a cerut sã investesc în firma lui cei 10000 euro pe care îi am.Mi-a promis cã îmi va da anul viitor 2000 euro, peste 2 ani 3000 euro, apoi 4000 euro ºila sfârºitul celui de-al patrulea an incã 5000 euro.


Am aflat cã cea mai mare dobândã la bãncile comerciale este de 12% pe an. Ce sãfac?

5. Care sã fie dobânda unei investiþii de 100 mil. lei dacã primesc lunar 10, 15, 0, 2,4,6 mil. lei?

Test 6

Testul urmãreºte sã evalueze abilitãþile de a transpune în funcþiile financiare problemelereale întâlnite în viaþa de zi cu zi. Atenþie la formulãrile acestor probleme.

Copiaþi tabelul urmãtor pentru fiecare problemã în altã foaie de calcul ºi completaþi-l cuvalorile constante corespunzãtoare problemei. Anulaþi funcþiile ºi rezultatele care nuintereseazã în problemã!

E F G H

2 Explicatii Constante EXEMPLE Functii financiare valoare

rezultat 3 Dobanda (rate) 7% =RATE(F4,F5,F8,F7) 4 nr_rate (nper) 30 =NPER(F3,F4,F8,F7) 5 rata_periodica(pmt) 25,000.00 lei =PMT(F3,F4,F8,F7) 6 7 valoare viitoare (fv) 200,000.00 lei =FV(F3,F4,F5,F8) 8 valoare prezenta (pv) 1,000.00 lei =PV(F3,F4,F5,F7,0)

1. Am 20.000 lei pentru a-i investi acum ca depozit la o bancã. În fiecare an plãnuiescsã investesc încã 5000 lei. Banca Junior oferã o dobândã de 8% pe an. Câþi bani voiacumula în 10 ani?

2. Firma mea doreºte sã investeascã în firma „Electronic MSDC” o sumã de banipentru cã aceasta oferã o dobândã de 17% anual. Lunar am putea depune câte 20000 $ºi am dori sã cunoaºtem care sã fie nivelul investiþiei initiale astfel încât dupã 20 luni sãobþinem 10 milioane!

3. Visul meu este sã devin milionar! Doresc sã acumulez un milion de euro. Am 100.000euro investiþi de la care câºtig 10% pe an. Dacã depun 25.000 euro în fiecare an, cândvoi avea suma doritã?

4. Sã presupunem cã aveþi 20000 lei ºi doriþi sã-i investiþi astfel încât sã obþineþi50000 în 2 ani. Care ar trebui sã fie dobânda?

5. Care sã fie valoarea lunarã pe care o avem de plãtit pentru a rambursa un împrumutde 203900 lei în 20 luni, dacã dobânda este de 7.5%?

67LECÞIA 8 • ANALIZA DATELOR ºI ESTIMAREA TENDINÞELOR

Lecþia 8Analiza datelor ºi estimareatendinþelor

�� Construirea scenariilor

�� Tehnica Goal Seek

�� Analiza datelor prin Data Table

�� Funcþia trend

�� Analiza ºi estimarea prin grafice ºi Trendline

8.1. Analiza datelor prin construirea scenariilor

Un scenariu este o foaie de calcul parametrizatã care pentru un set de valori aleparametrilor afiºeazã instantaneu rezultatele calculate. Mai ales atunci când necesitãþilede analizã sau prezentare determinã reluãri ale configuraþiei parametrilor sau trecerearapidã de la o configuraþie la alta utilitarul Scenario Manager este de real folos. El esteapelat prin Tools�Scenarios. Sã vedem cum funcþioneazã rezolvând o problemã.

Exemplul 1

O familie doreºte sã depunã la bancã o sumã lunarã. Dacã banca practicã o dobândãde 7% pe an, suma iniþialã este de 100 lei ºi suma lunarã este de 200 lei. Cât vaeconomisi dupã 10 ani? Se doreºteºi afiºarea sumelor din contul ban-car în fiecare lunã.

Care va fi situaþia contului ºi sumafinalã dacã dobânda se pãstreazã la7% dar modificãm suma iniþialã la200, 300, 500? Dar dacã se mo-dificã dobânda la 5%?

Rezolvare:

Pasul 1. Fixãm parametrii de lucruai foii de calcul adicã dobânda,sumele iniþiale ºi lunare.

Pasul 2. Facem o serie cu pasul 1pentru numãrul lunii, serie care seîncheie la 120.

Fig. 8.1. Schemã de calcul fãrã scenarii


Pasul 3. Punem formula pentru prima lunã B7=suma iniþialã.

Pasul 4. Punem formula pentru luna a doua B8=B7+B7*dobânda/12+suma_lunarã.

Pasul 5. Copiem formula pânã în luna 120.

Pasul 6. Apelãm utilitarul Scenario Manager prin meniul Tools. Introducem pentrufiecare scenariu numele ºi domeniul de celule folosit pentru parametri. Celulele nutrebuie sã fie neapãrat adiacente. Se scriu valorile fiecãrui parametru pentru scenariuºi se precizeazã celula/ celulele unde se va citi rezultatul.

O privire comparativã asupra rezultatelor aºteptate se realizeazã prin butonul Summarycare afiºeazã un tabel centralizat al intrãrilor ºi ieºirilor din fiecare scenariu.

se fixeazã intrarea se fixeazã ieºirea analiza comparativã


Populaþia þãrilor dezvoltate ale lumii a fost de 1.245 mil. la sfârºitul lui 1996 ºi acrescut cu o ratã de 0.4% pe an. Populaþia celorlalte þãri a fost de 4.486 mil. ºi a crescutcu o ratã de 1.9% pe an. Creaþi o foaie de calcul pentru a previziona numãrul locuitorilorplanetei în 2025. Care va fi ponderea numãrului de locuitori ai þãrilor dezvoltate faþã detotal?

8.2. Atingerea unui obiectiv. Utilitarul Goal Seek

Un alt instrument pentru analizareadatelor este utilitarul Goal Seek.

De cele mai multe ori, în rezolvareaunei probleme plecãm de la anumite valoride intrare ºi vedem la ce rezultat ajungem.

Sunt ºi cazuri în care ºtim ce dorim sãobþinem ºi ne întrebãm care sã fie valoa-rea potrivitã la intrarea în procedura decalcul.

Spunem cã avem o problemã de tip„Goal Seek” .

Fig. 8.2. Foaia de calcul cu fereastra Goal Seekpentru dobândã


Exemplul 2

Reluãm problema bancarã ante-rioarã. Ne propunem sã obþinempeste 10 ani suma de 50000 Lei.Ne întrebãm ce dobândã ar trebuisã aibã banca? Sau ce sumã ini-þialã sã depunem la dobânda cu-rentã? Sau ce sumã sã depunemlunar, pãstrând ceilalþi parametriconstanþi?

Pentru a rezolva se lanseazã utili-tarul Goal Seek prin Tools�GoalSeek.

Se fixeazã în fereastra de dialogreferinþa asupra celulei rezultat ºivaloarea aºteptatã se scrie în celula respectivã. Se introduce de asemenea adresacelulei parametru de intrare.

Utilitarul „potriveºte” valorile în celula parametru ºi anunþã gãsirea sau nu a uneisoluþii!

O problemã rezolvatã. Planul financiar al familiei Ionescu

Familia Ionescu câºtigã 20 mii lei pe lunã, iar cheltuielile sunt: 3 mii taxe de intreþinere,2 mii alimente ºi 8 mii alte cheltuieli.

Visul lor este sã-ºi cumpere o casã care valoreazã 500 mii.

Pentru aceasta decid sã depunã tot restul de bani la o bancã care are o dobândãanualã de 10%. Ei sperã cã veniturile lor vor creºte cu 9% pe an, iar cheltuielile doar cu5%. Au strâns deja o sumã ca avans, dar nu ºtiu dacã este suficientã pentru ca sã cumperecasa peste 25 ani!

a. Proiectaþi o foaie de calcul pentru planul financiar al familiei Ionescu pe 25de ani.

b. Realizaþi scenarii ºi salvaþi-le în foi diferite. Tipãriþi-le la imprimantã.

c. Realizaþi un centralizator al scenariilor. De ex. Valoarea la bancã peste 25 aniîn condiþiile unei sume iniþiale variate, o dobândã constantã.

d. Care ar trebui sã fie veniturile realizate de familie pentru ca în 10 ani sã obþinãla bancã 500000 lei?

e. Care ar trebui sã fie procentul de creºtere a salariului pentru ca sã-ºi cumperecasa în 10 ani?

f. Ce sumã iniþialã trebuie depusã la bancã astfel încât în 25 ani sã obþinã baniide casã (în condiþiile dobânzii de 10% pe an ºi a creºterii salariale de 9% ºia creºterii cheltuielilor cu 5%)?

Fig. 8.3. Gãsirea unei soluþii: dobândã de 14%


Analizã

Planul financiar înseamnã simularea veniturilor ºi a cheltuielilor pe parcursul uneiperioade de 10 ani. Din venituri se scad cheltuielile ºi restul se depune la bancã.

Care vor fi intrãrile?

a. Elementele de cheltuieli: taxe, alimente, alte cheltuieli ºi procentul de creºtereanualã a acestora; b. salariul ºi procentul de creºtere; c. suma iniþialã de care dispunefamilia pentru începerea contului.

Care vor fi ieºirile?

Sumele anuale economisite la bancã ºi suma dupã 10 ani!

Ce calcule se fac?

a. Se însumeazã cheltuielile iniþiale din anul de pornire ºi se genereazã valori pentruanii urmãtori considerându-se o ratã de creºtere datã. Deci dacã plecãm de la total cheltuieliiniþiale în sumã de 100 lei ºi rata de creºtere de 5% pe an obþinem în anul urmãtorvaloarea = 100+100*6%/12 =100,50 lei, peste doi ani valoarea din bancã va fi 100,5 +100,5 * 6%/12 = 110 lei º.a.m.d. b. Dacã se porneºte de la un salariu iniþial dat segenereazã veniturile în anii urmãtori presupunând cã va exista ºi aici o ratã de creºtere.Formulele sunt similare creºterii cheluielilor. c. se depune anual la bancã diferenþa venituri-cheltuieli. d. Banca acordã o dobândã la suma existentã – înainte de a depune familiaeconomiile sale, deci în fiecare an vom calcula suma totalã în bancã ca fiind sumaeconomisitã + dobânda la suma anterioarã.

Proiectarea foii de calcul

Pasul 1. Se fixeazã parametrii de intrare ca în imagine

Fig. 8.4. Afiºarea legãturilor dintre celule se face prin Tools�Formula Auditing�Trace.


Veniturile (B2), taxe (B3), alimente (B4), alte cheltuieli (B5), Rata dobânzii bancareanuale (E1), rata de creºtere a veniturilor (E2), rata de creºtere a cheltuielilor E3), sumainþialã economisitã de familie (E5).

Pasul 2. Se proiecteazã structura bazei de date scriindu-se identificatorii de câmp ºiformatând la numeric întreg cu 2 zecimale.

Pasul 3. Se proiecteazã coloana A „an” cu o serie cu pasul 1.

Pasul 4. Se proiecteazã coloana B („venituri”) astfel: B9 = B2; B10 = B9 + B9 *crestere_salar – se copiazã B10 în coloanã.

Pasul 5. Se proiecteazã coloana C „cheltuieli” astfel C9 = B5; C10 = C9 + C9 *crestere_chelt ºi se copiazã C10 în coloanã.

Pasul 6. Se proiecteazã coloana D astfel D9 = B9 – C9 care se copiazã în rest.

Pasul 7. Se proiecteazã coloana E astfel E9 = initial + initial*dobinda, E10 = F9 +F9* dobânda se copiazã în restul coloanei E.

Pasul 8. Se proiecteazã coloana F astfel F9 = D9 + E9 ºi se copiazã în rest.

Pasul 9. Se înregistreazã scenariile prin utilitarul Scenarios Manager apelat prin meniulTools�Scenarios.

Pasul 10. Se obþine raportul comparativ al valorii finale dupã 10 ani folosind butonulSummary din fereastra Scenarios.

Pasul 11. Se apeleazã la utilitarul Goal Seek prin Tools�Goal Seek, fixându-se celulade rezultat F18 la valoarea sumei finale.


1. Hotelul Albatros oferã pentru cazarea pe o noapte preþul promoþional de 1000 lei.De asemenea, se acordã reducere pentru grupuri. Astfel: dacã grupul are mai puþin de 5persoane, reducerea este de 5%; între 6-9 persoane reducerea este de 12%; între 10 ºi19 reducerea este de 15%, iar peste 20 reducerea este de 19%.

a. Proiectaþi o foaie de calcul care sã permitã aflarea exactã a preþului de cazarepentru o persoanã din grupul din care face parte!

b. Managerul hotelului are ºi un anumit tarif pentru clienþii fideli. Lor li se aplicã altãgrilã de reducere: pânã în 5 persoane reducerea este de 8%; între 6-9 reducerea este de14%; între 10-19 reducerea este de 18% iar peste 20 reducerea este de 22%. Modificaþifoaia de calcul pentru noua situaþie.

2. Elevii clasei a X-a vor sã plece în vacanþã cu corturile la mare.

Pentru aceasta s-au informat ºi au obþinut preþul unui bilet de drum dus-întors. Tarifulde cazare în satul de vacanþã este redus cu 10% (pentru elevi) dacã grupul este între 10-15 persoane, cu 20% dacã grupul este între 16-20 persoane ºi cu 25% dacã grupul estemai mare de 21 persoane.

De asemenea, se poate lua masa în sat. Pentru fiecare zi se plãteºte separat miculdejun, prânzul ºi cina. Fiecare elev poate sã aleagã meniul complet sau numai o parte dinel. ªi aici s-ar putea negocia un procent de reducere, dar la faþa locului (în cazul cel mai


optimist, procentul este de 30% reducere indiferent de numãrul de persoane care iaumasa, dar numai pentru acei elevi care se angajeazã pentru meniul complet)

Grupul de prieteni doreºte sã afle care ar fi suma maximã pe care ar trebui sã o aibãfiecare asupra lui pentru a acoperi cheltuielile de transport ºi cazare ºi eventual diferenþarezultatã din reduceri! Aflaþi ºi suma totalã necesarã excursiei.

Imaginaþi diferite scenarii. De exemplu, dacã grupul ar fi de 22 de persoane care arsolicita meniu complet ºi ar beneficia de reducere la masã de 30%? Sau dacã grupul arfi de 13 persoane, care solicitã doar prânzul?

Afiºaþi variantele pe alte foi de calcul.

Tipãriþi variantele.

8.3. Tabele de simulare construite prin Data Table

O altã facilitate oferitã de Excel este construirea rapidã a tabelelor de simulare (numiteºi tabele de variaþie) folosite în procedeul de analizã a unui situaþii.

Un tabel de simulare este vãzut ca un domeniu de celule care ne aratã cum variazãrezultatele aceleiaºi formule când unul sau doi parametri variazã.

8.3.1. Folosirea Data��Table cu un parametru variabil

La o bancã dobânda practicatã pentru depozitele populaþiei este de 30% pe lunã.Clientul doreºte sã ºtie valorile obþinute peste 1, 2, 3, 4, 5 sau 6 luni, dacã depune sumade 100.000 lei.

O metodã rapidã este folosirea tabelelor de simulare construite automat prin Data" TableObservaþi foaia de calcul pentruaceastã problemã.

Pasul 1. Construim tabelul iniþialcu indicatorii ºi valorile respective.Vom folosi funcþia FV cu parametriidobândã lunarã; numãr luni; depu-nere iniþialã.

Pasul 2. Construim variaþia valoriiviitoare dupã numãrul de luni.

a. Vom realiza un tabel cu douãcoloane. Pe o coloanã (C) vom punenumãrul de luni. Pentru problemanoastrã vom pune o serie liniarã cupasul 1, termen iniþial 1.

Fig. 8.5. Organizarea foii pentru simularea evoluþieidepunerilor în timp


Fig. 8.7. Tabelul estearanjat pe coloane

b. Pe cealaltã coloanã (D) deasupra primei luni se pune adresa relativã a funcþiei FVdin tabelul iniþial (ex. D9).

c. Se selecteazã tabelul C11:D17.

d. Se alege opþiunea Table din meniul Data. În fereastra de dialog vom scrie în casetacu text Column input cell adresa variabilei dupã care se face simularea adicã $D$8 (numãrulde luni).

e. Observãm completarea automatã a tabelei cu valorile funcþiei FV în raport de numãrulde luni, pãstrând egali ceilalþi doi parametri: suma iniþialã ºi procentul dobânzii.

Fig. 8.6. Când tabelul de simulare este ordonat pe linii,completãm adresa parametrului în fereastra Row input cell.

Observaþie. Tabelul pentru simulare se poate construi ºi pe linii. În acest caz, vompune adresa funcþiei pe un rând ºi vom scrie valorile parametrului cu un rând mai sus.

Funcþia din tabeluliniþial are adresa D9

1 2 3 4 904,310.00 lei 100000 230000.00 399000 618700

Valorile deintrare

Valorile calculate defunctia FV

Sarcini de laborator1. Construiþi tabelul de variaþie a aceleiaºi funcþii FV când

procentul dobânzii ia valorile 30%, 25%, 27%, 17%, 10% ºi15%.

2. Construiþi tabelul de variaþie a aceleiaºi funcþii FV cândsuma achitatã iniþial variazã de la 100000 la 5000 cu pasul1000.

3. Construiþi tabelul de variaþie a aceleiaºi funcþii FV cândse cere sã se afle suma viitoare dupã 5 luni, 12 luni, 24 luni,30 luni ºi 36 de luni.

8.3.2. Folosirea Data��Table cu doi parametri variabili

Ne propunem sã calculãm sumele primite de la bancã dacã depunem iniþial 100000 leiîntr-o lunã, 2, 3, 4, 5, 6 luni, dacã dobânda bãncii este 30%, 40%, 50%, 60% pe lunã!

Este o problemã de calcul a funcþiei FV având un parametru constant (suma iniþialã) ºidoi parametri variabili (numãrul de luni ºi dobânda).


Vom folosi tot Data�Table ºi tabelul funcþiei FV din prima problemã.

Vom construi o matrice care va avea pe linii numãrul de luni ºi pe coloane dobânzile.

Aici vor fi rezultatele trecute automat de Data�TableEx. D23 = valoarea obþinutã dupã 2 luni la o dobândãde 30% cu aceeaºi sumã iniþialã de 100 mii.

c d e f g 21 904.310,00 lei 30% 40% 50% 60% 22 1 100000 100000 100000 100000 23 2 230000 240000 250000 260000 24 3 399000 436000 475000 516000 25 4 618700 710400 812500 925600 26 5 904310 1094560 1318750 1580960 27 6 1275603 1632384 2078125 2629536

Valorile dobânziiAdresa funcþiei =D9

Numãrul de perioade

a. În zona C22:C27 vom varia numãrul de luni, deci vom construi o serie numericãavând pasul 1. În domeniul D21:G21 vom scrie valorile dobânzii.

b. În colþul stânga sus al tabelului trecem adresa funcþiei FV din tabelul iniþial (=D9).

c. Selectãm întreg tabelul C21:G27.

d. Deschidem fereastra de dialog Data�Table.

Vom completa Row input cell cu adresa D7 = para-metrul dobândã, iar Column input cell cu D8 = adresavariabilei numãr luni din tabelul iniþial (vezi fig. 8.5).

e. Observãm completarea automatã a tabeluluiselectat.


1. Construiþi variaþia FV pentru numãrul de 6 luni, dacã suma iniþialã pleacã de la100000 la 1 milion ºi dacã dobânda lunarã este de 5, 8, 11, 17%

2. Construiþi variaþia FV pentru o dobândã de 17% lunar ºi variaþi suma iniþialã ºinumãrul de luni.

8.4. Previzionarea evoluþiei unui fenomen

Evoluþia viitoare a unui fenomen se poate construi prin Edit�Fill�Series dacã acestaare o evoluþie liniarã sau exponenþialã.

Fig. 8.8. Completarea parametrilordin tabelul iniþial


Dacã însã datele existente în perioadele precedente nu respectã regulile de construcþiea unei serii ºi dorim sã previzionãm valori viitoare, putem folosi liniile de tendinþã construiteprin grafice sau funcþia TREND.

Funcþia TREND întoarce valori previzionate având ca suport de plecare datele obþinuteîntr-o perioadã precedentã.

Exemplul 1

Se înregistreazã profitul obþinut de o societatecomercialã pe parcursul a 9 luni de activitate (înmilioane lei). Se doreºte previzionarea profituluiprobabil în lunile urmãtoare.

Vom folosi funcþia statisticã TREND(). Urmãriþi în tabelulalãturat valorile pentru numãrul lunii (M20:M31) ºipentru profit (N20:N28). În celula N29 apelãm funcþiaTrend. Completãm în fereastra de dialog a funcþiei Trendlinia Known_y’s cu domeniul cunoscut de valori aleprofitului N20:N28. Pe linia Known_x’s completãmdomeniul de variaþie a lunilor M20:M28. Pe liniaNew_x’s vom completa domeniul de variaþie a lunilorviitoare M29:M31.

Pentru celulele urmãtoare fa-cem o copie a formulei dinN29 pentru a vedea valoareaestimatã a profitului în urmã-toarele luni.

8.4.1. Folosirea graficelor. TRENDLINES

Folosirea utilitarului pentru constru-irea graficelor Excel este deja cunoscut.Vom realiza un grafic al valorilor cunos-cute din primele 9 luni prin Insert�Chart.Pentru a previziona profitul ne vom po-ziþiona pe un punct din grafic ºi alegemdin meniul contextual opþiunea AddTrendline.

Trendlines este o metodã de a sugerao evoluþie viitoare pe baza datelor din

M N 20 1 3 21 2 5 22 3 8 23 4 1 24 5 6 25 6 8 26 7 9 27 8 11 28 9 23 29 10 30 11 31 12

Fig. 8.9. Completarea funcþiei TREND

Fig. 8.10. Fixarea ?????????


trecut. În fereastra de dialog Trendline�Type selectãm tipul evoluþiei. Sunt mai multetipuri de evoluþii bazate pe diferite formule de ecuaþii.

Pasul urmãtor este sã indicãm în pagina Options numãrul de perioade pe care dorimsã extindem previziunea noastrã.

Urmãriþi în imaginea capturatã linia de evoluþie datã de Trendlines.

Observaþi o predicþie pentru un tip de trend polinomial.

Fig. 8.13. Alegerea tipului de evoluþie.

X este numãrul de perioade, ecuaþia prezice profitul (y) în funcþie de perioadã. R estecorelaþia între valorile prezise de ecuaþie ºi valorile actuale. Pãtratul R este utilizat pentru aindica variaþia valorilor faþã de linia de trend.

Sarcinã de laborator

1. Schimbaþi tipul ºi aflaþi care a fost evoluþia de acum 5 luni a profitului!

Fig. 8.12. Alegerea numãrului de perioadeviitoare se face prin caseta Forward.Putem solicita afiºarea ecuaþiei care a stat labaza previziunii.

Fig. 8.13. Fixarea tendinþei de tip polinomial


Test 7Testul vizeazã evalurea abilitãþilor de a opera cu funcþii financiare ºi de a construi

tabele de simulare ºi grafice.

1. Determinaþi rata lunarã pe care o are de achitat o persoanã care a contractat unîmprumut de x lei cu o dobândã de y% anual, ºtiind cã se doreºte achitarea în z luni!

2. Construiþi tabele de variaþie

a. când variazã dobânda;

b. când variazã suma împrumutatã;

c. când variazã numãrul de rate;

d. când variazã ºi dobânda ºi numãrul de rate;

e. când variazã suma împrumutatã ºi dobânda.

3. Construiþi graficul variaþiei funcþiei pentru punctul a.

4. Construiþi graficul variaþiei funcþiei pentru punctul d.

2. Sã presupunem cã un chestionar are numai 5 întrebãri: întrebãrile 1 ºi 2 au 2variante de rãspuns DA/NU; întrebãrile 3 ºi 4 au 5 variante a, b, c, d, e iar la întrebarea 5trebuia acordatã o notã de la 1-10. Se doreºte o foaie de calcu pentru prelucrareachestionarelor astfel încât sã se obþinã sub formã tabelarã ºi graficã pentru fiecare întrebare??????.

Exemplu

Întrebare 1Total nr. persoane cu DA ___ nr. persoane cu NU ___ TOTAL

nr. persoane .....din care Femei din care nr. femei cu DA nr. femei cu NUdin care Bãrbaþi din care nr. bãrbaþi cu DA

nr. bãrbaþi cu NUetc.

întrebare nr. chenar

nume persoan� sex ocupa�ie cost 1 2 3 4 5

1 Albu F elev 15 DA DA a a 10 2 Barbu F student 21 NU DA b a 9

DANUfara raspuns


Lecþia 9Gestiunea listelor de date înWord

�� Ce este o listã sau o bazã de date Word

�� Introducerea datelor utilizând formulare

�� Filtrarea ºi cãutarea informaþiilor

�� Sortarea

O listã sau o bazã de date Word este un tabel în care informaþiile au o anumitãsemnificaþie si sunt structurate astfel: prima linie indicã structura bazei de date ºi conþinenumele coloanelor (heading). Celelalte linii conþin valorile propriu-zise (records). Coloanele(Fields) au nume distinct. Nu sunt linii sau coloane vide. Fiecare articol trebuie sã aibãcel puþin o valoare care sã îl facã unic în cadrul tabelei.

Existã mai multe posibilitãþi de a crea tabele. Unele programe sunt chiar mai bunepentru a crea anumite tipuri de tabele, astfel:

• Pentru o tabelã care include formate grafice (liste cu simboluri sau numerotate),formatarea individualã a celulelor ºi divizarea celulelor pe diagonalã – folosimMicrosoft Word

• Pentru o tabelã care include calcule complexe, analize statistice sau diagramefolosim Microsoft Excel

• Pentru cãutãri rapide ºi sortãri folosim Microsoft Access sau Excel

• Pentru tabele relaþionale folosim Access

• Pentru o tabelã care este inclusã într-o prezentare folosim PowerPoint

• Pentru o tabelã vizibilã pe Web folosim Front Page sau Word

Pentru gestiunea tabelelor organizate ca baze de date folosim bara de butoane Data-base.

data form

add new record

sort ascending

find record

delete record

manage fields

sort descending

update fields

insert database

mail merge main document

dataform

add newrecord

sortascending

insertdatabase

findrecord

managefields

managefields

sortascending

updatefields

mail merge maindocument

79LECÞIA 9 • GESTIUNEA L ISTELOR DE DATE ÎN WORD

Ce facilitãþi oferã Word pentru tabele organizate într-o bazã de date?

1. editarea articolelor prin intermediul unui formular2. adãugarea unui articol nou3. ºtergerea unui articol4. corectarea articolelor5. cãutarea articolelor care îndeplinesc o anumitã condiþie6. ordonarea articolelor7. tratarea tabelei ca sursã de date pentru corespondenþã8. preluarea datelor din surse externe ca tabelã Word

9.1. Folosirea formularelor în tabelele Word

Dacã avem o bazã de date mare, cu multe articole ºi câmpuri, este util sã folosimformularul asociat prin butonul Data Form din bara Database.

Formularul prezintã un singur articol. Fiecare câmp este prezent prin numele ºi valoareapentru articolul curent. Putem sã ne deplasãm la orice articol, fie din aproape în aproape,fie indicând numãrul acestuia. Se pot adãuga noi articole (butonul New) se pot ºterge(butonul Delete).

O facilitate a formularului este ºi posibilitatea de cãutare ºi poziþionare în baza dedate pe articolul care îndeplineºte condiþia pusã. Butonul Find deschide o fereastrã dedialog prin care se introduce valoarea cãutatã ºi câmpul unde se cautã.

Sarcini de laborator1. Creaþi un tabel tip bazã de date cu vânzãrile din primul semestru care sã aibã

structura (cod, denumire produs, ian, feb, mar, apr, mai, iun, total).

2. Adãugaþi 4 articole.

Fig. 9.1 Formularulpermite actualizareaºi cãutarea.


3. Folosiþi formularul pentru a cãuta ºi a vã poziþiona pe articolele care au vânzãri înfebruarie de 4 unitãþi.

4. Folosiþi formularul pentru a adãuga alte 2 articole.

5. Folositi formularul pentru a ºterge articolul 3.

6. Poziþionaþi-vã rapid pe primul articol.

7. ªtergeti ultimul articol ºi apoi anulaþi operaþia.

9.2. Sortarea listelor Word

Pentru operaþia de aranjare a datelor dintr-o listã Word putem folosi meniul Table�Sort.Fereastra de dialog permite introducerea câmpurilor dupã care se va face ordonarea ºisensul ordonãrii.

Exemplul 1

Fig. 9.2. Sortarea tabelei Word.

Dorim aranjarea listei comenzilor cronologic,pentru aceeaºi datã pe clienþi ºi pentru ace-laºi client crescãtor dupã numãrul comenzii.

Observaþi în fig. 9.2 modul de completarea cerinþelor. Avem grijã ca butonul radioHeader row din secþiunea My list has sãfie activat, ceea ce înseamnã cã primulrând al tabelei conþine numele câmpurilor.

Se poate solicita ºi ordonarea dupã osingurã coloanã dacã folosim butonul Op-tions ºi selectãm Sort column only.

9.3. Operaþii asupra câmpurilor

Este util, mai ales în cazul tabelelor mari, sã putem face operaþii asupra structuriitabelei: sã redenumim coloane, sãadãugãm sau sã ºtergem câmpuri.Aceste operaþii se pot activa prinfereastra de dialog Manage Fieldsdeschisã prin butonul Manage Fieldsde pe bara Database.

Fig. 9.3. Fereastra de manipulare acâmpurilor.


Sarcini de laboratora. Folosiþi tabelul vânzãrilor din primul semestru ºi sortaþi-l dupã numele produsului.

b. Adãugaþi câmpurile datã, preþ ºi introduceþi valori în acestea.

c. Redenumiþi câmpul denumire la nume_produs.

d. Adãugaþi un nou câmp, denumit unitate de mãsurã ºi introduceþi valori în acesta.

e. Redenumiti câmpurile ian la ianuarie ºi feb la februarie.

9.4. Efectuarea calculelor în tabela Word

Pentru a face diferite calculeputem sã folosim Table�For-mula dupã ce ne poziþionãm pecelula unde dorim rezultatul.

Word are câteva funcþii pecare le putem alege din listaPaste function.

Formulele sau expresiilesunt combinaþii de termeni ºioperatori.

Operatorii sunt: +,-,*,/, %(procent), = , <.,>, >=, <=, <>.

Termenii pot fi numere, referinþe de celule din tabelul curent sau din alt tabel, precum ºifuncþii.

Câteva funcþii: AVERAGE(), COUNT(), MAX(), MIN(), PRODUCT(), SUM(), AND(), OR(), IF().

Referinþele la celule sunt adrese absolute ºi sunt numite A2, B1 etc., unde litera estenumele coloanei, iar cifra identificã linia la intersecþia cãrora se aflã celula la care seface referire.

Exemplul 2

Fie tabelul urmãtor cu 6 comenzi. Vom calcula media aritmeticã a preþurilor cu for-mula =average (G2:G7) sau cu =average(G:G)

Fig. 9.4.ProiectareauneiformuleWord serealizeazãprinfereastraFormula.

Nr_crt data client comanda_nr produs Cant pret valoare

Chapter 1 1/3/2005 SC. Albatros

12345 Mere 10 12 120

Chapter 2 1/4/2005 SC. Abbba 12346 Pere 20 2 Chapter 3 1/5/2005 Popescu M 1000 Mere 40 34 Chapter 4 2/5/2005 SC. Divanu 2340 Mere 20 56 Chapter 5 5/5/2005 ionescu 3400 Pere 100 7 Chapter 6 7/6/2005 S c

Harghita 3456 Nuci 100 12

total 290


Formula = sum(above) face suma pe coloana curentã plecând de la linia anterioarãcelulei unde este formula pânã la o valoare spaþiu sau invalidã sau pânã se ajunge lacapãtul tabelului.

Formula = f2*g2 calculeazã valoarea preþ*cantitate pentru o primã comandã.

Referirea celulelor din alte tabele faþã de tabela unde se evalueazã formula se poateface dacã tabela este de tip referinþã (bookmark).

Cum se definesc referinþele de tip Bookmark la un tabel?

Dorim sã ne referim la tabelul cu produse comandate de clienþi prin numele „Comenzi”.

Pasul 1. Se selecteazã tabelul.

Pasul 2. Se deschide fereastra de dialogInsert�Bookmark ºi se scrie numele referinþei.

Cum se folosesc referinþele tip bookmark?

În formule, bookmark-ul precede adresa celulei.

Exemple:

Formula = average (comenzi G:G) face me-dia preþurilor (coloana G) din tabela pe care amnumit-o „comenzi”.

Formula = sum(comenzi F2:F5) face suma can-titãþilor comandate pentru primele 4 comenzi.

Formula = sum(comenzi F5) introduce în celula rezultat valoarea gãsitã în tabelulComenzi, coloana F, linia 5.

Atenþie!

Formulele se recalculeazã manual. Dacã în tabel se modificã valoarea unei celule cea fost folositã într-o formulã se poate reflecta aceastã modificare în valoarea întoarsã deformulã prin tasta <F9>.

Pentru a calcula rapid suma se poate folosi butonul Autosum de pe Tables and BordersToolbar.


Fie un tabel cu vânzãrile de apã mineralã în primul semestru.

1. Calculaþi totalul pentru fiecare produs.

2. Calculaþi totalul pen-tru fiecare lunã ºi totalulgeneral.

3. Scrieþi pretul unui li-tru de apã mineralã într-ocelulã a altui tabel.

Fig. 9.5. Fereastra Bookmark.

co d d en um ire to ta l ian fe b m a r ap r m a i iu n 1 b orse c 9 4 5 4 5 5 2 b ib orten i 3 4 4 2 5 6 3 izvoru l a lb 4 4 7 4 1 0 4 p o ian a

n eg ri 2 2 5 7 8 9

5 s tan a de va le

3 4 5 6 2 8

23 21 28


4. Adãugaþi în tabelul intiþial o nouã coloanã pe care o numiþi Preþ ºi puneþi lafiecare rând valoarea preþ.

5. Adãugaþi o nouã coloanã unde sã calculaþi valoarea vânzãrilor totale pe produs.

6. Aflaþi media aritmeticã a vânzãrilor din luna a 3-a.

7. Aflaþi cea mai mare cantitate vândutã în luna mai.

8. Aflaþi cea mai micã cantitate vândutã dintre toate produsele ºi în toate lunile.

9.5. Preluarea datelor din surse externe în tabela Word

O bazã de date gestionatã de Word poate proveni ºi dintr-o sursã externã: o listã Excel,o tabelã Access sau Oracle etc.

Pasul 1. Folosim butonul Insert database de pe bara Databasecare deschide o fereastrã de dialog. Butonul Get Data permiteselectarea fiºierului pe care dorim sã-l folosim. Poate fi un fiºierExcel sau o bazã de date (Access, FoxPro, Oracle etc.)

Pasul 2. Apãsãm butonul Insert Data ºi prin fereastra de dia-log Select table selectãm din lista foilor de calcul care sunt înregistru Excel sau din lista tabelelor ºi a view-urilor din bazade date numai fiºierul dorit. Atenþie! Este bine sã activãmcomutatorul First row of data care determinã copierea tabeluluidin foaia de calcul cu primul rând interpretat ca nume de câmp.

Pasul 3. Putem decide dacã vor fi inserate în tabelul dindocumentul Word toate sau numai unele articole ale cãrornumere de ordine sunt cuprinse într-un interval (fereastra dedialog Insert data).

Pasul 4. Articolele obþinute pot fi supuse operaþiei de filtrare, sortare ºi selectare acâmpurilor dorite în tabelul destinaþie Word prin fereastra de dialog Query Option.

Pasul 5. Tabelul poate fi formatat alegându-se dintre ºabloanele de AutoFormat.

Exemplul 3

Avem într-un registru Excel cu numele Scoala.xls informaþii prelucrate sau nu despretoþi elevii ºcolii din anul 2000 ºi pânã în prezent. Registrul conþine mai multe foi (|nfoaia de calcul cu numele Absolvenþi avem lista elevilor care au absolvit liceul în aniitrecuþi, iar în foaia Elevi se aflã lista elevilor curenþi ai ºcolii etc.).

Dorim sã obþinem o bazã de date Word cu numele, CNP, nr-buletin, clasa ºi mediaelevilor care au absolvit anul acesta clasa a XII-a ºi care au media generalã peste 5.Dacã sunt elevi care nu au absolvit, atunci media lor fie nu este calculatã, fie este maimicã de 5.

Lista finalã trebuie sã conþinã elevii ordonaþi alfabetic pe clase.

Pret/l 100


Cum procedãm?

Pasul 1. Din bara de butoaneDatabase vom selecta butonul In-sert database ºi apoi butonul Getdata ºi alegem fiºierul Scoala.xls.

Pasul 2. Din fereastra Selecttable se alege foaia de calcul Elevi.

Pasul 3. Se fixeazã numãrul dearticole ca fiind All în fereastraInsert Data deschisã prin butonulcu acelaºi nume din fereastraprincipalã a operaþiei de definire a sursei de date.

Pasul 4. Deschidem fereastra de dialog QueryOption pentru a filtra articolele.

Fig 9.6. Selectarea tabelului din lista de tabele a registrului

Fig 9.7. Precizarea numãrului de articole

Pasul 5. Precizarea filtrului seface prin Query Options, Filter.Observaþi modul de construire aexpresiei. Se alege câmpul,operatorul ºi se scrie valoarea.

Pasul 6. Fixarea cheilor deordonare se face în fereastraQuery Option, Sort Records. Sepot fixa maxim 3 chei. Înproblema noastrã vom fixa caprimã cheie clasa, apoi numeleelevului.

Fig. 9.8. Fereastra Query/Filter

Fig. 9.9. Fereastra Query/Sort


Pasul 8. Se formateazã tabelul prin butonul Autoformat ºi se verificã corectitudineamanevrelor. Observaþi baza de date Excel inseratã în documentul Word.

Pasul 7. Selectareacâmpurilor din listaExcel se face totprin fereastra dedialog QueryOption�Selectfields. Iniþial, înambele fereastre seafla toate câmpurilelistei Excel.

nume cnp buletin clasa medie Albulescu Mircea

1234567898 mx 12345 12a 10

Dobrescu Paula

1222234567 mc 23459 12b 7

Chiru Ludmila 20909672269 dx 23458 12a 10


a. Creaþi un registru Excel cu douã foi de calcul. Prima foaie, cu numele Farmacii,conþine datele generale despre farmacii – codul, numele, adresa.

b. A doua conþine date despre medicamente (cod, nume, mod de prezentare, indicaþii,contraindicaþii, preþul, procentul de compensare).

c. A treia foaie conþine stocurile de medicamente cu structura (cod farmacie, codmedicament, stoc).

d. Creaþi un document Word care sã continã o situaþie a stocurilor de medicamentecare se compenseazã pentru toate farmaciile care au valoarea stocului mai mare decât5000 Lei. Inseraþi datele din Excel preluând un tabel care sã aibã numele, codul ºiadresa farmaciei, numele medicamentului, codul, mod de prezentare, indicaþii, contra-indicaþii, preþul, cantitatea în stoc, procentul de compensare.

e. Transformaþi tabelul preluat din Excel astfel:

• ºtergeþi coloanele adresa, indicaþii, cod_medicament, contraindicaþii;

• adãugaþi coloana preþ_compensat.

f. Calculaþi în Word preþul compensat al fiecãrui medicament.

g. Calculaþi în Word preþul mediu compensat al tuturor medicamentelor.

Fig. 9.10. FereastraQuery/Select Fields


h. Separaþi tabelul pe farmacii, cu fiecare farmacie pe foaia proprie. Copiaþi capul detabel la fiecare foaie. Puneþi data raportului în dreapta jos ºi numãrul foii în stânga sus.Formataþi tabelele.

Sfat. Compuneþi într-o foaie de calcul tabelul cu toate datele „lipind” datele din celedouã foi ºi apoi copiaþi doar valorile în altã foaie pentru a le insera în documentul Word.

9.6. Baza de date Word pentru corespondenþã (Mail merge main source)

O importantã facilitate oferitã utilizatorilor de cãtre Microsoft Word este de a fuzionadatele din baza de date cu machete (ºabloane) scrise ca documente Word pentrupersonalizare. În felul acesta pot fi concepute scrisori, etichete, plicuri cu aceeaºi formãgeneralã, dar care folosesc informaþiile din baza de date sursã ºi devin documenteparticularizate.

Pentru acest lucru putem folosi Mail Merge Wizard.

Paºii de bazã sunt:

1. Deschidem sau creãm o machetã sau un document principal (main document) careconþine textul ºi grafica comunã tuturor documentelor personalizat obþinute prin acestutilitar.

2. Deschidem sau creãm tabelul sursã (data source) care este un fiºier ce conþineinformaþia ce va fi inseratã în documentul principal.

3. Adãugãm sau personalizãm pe ºablon câmpurile de legãturã (field merge) din sursade date care vor personaliza documentul.

4. Verificãm rezultatul combinãrii în exemplarele personalizate obþinute din acelaºiºablon.

Exemplul 4

Pentru înscrierea la bacalaureat toþi elevii vor depune la secretariatul liceului o cererecu aspectul prezentat în continuare.

Pentru simplificarea lucrului, secretariatul doreºte sã completeze automat aceste cereripentru elevii din clasa a XII-a, mai ales cã datele personale ale elevilor sunt dejagestionate într-un fiºier Excel. Foile pot fi apoi distribuite elevilor pentru a fi semnatede cãtre aceºtia ºi depuse la secretariat.

Analiza problemei

În primul rând, identificãm câmpurile variabile din macheta cererii.

În imaginea alãturatã am subliniat aceste câmpuri. Avem nevoie de: numele elevului,numãrul de buletin ºi seria, CNP, clasa ºi media.


Secretariatul liceului dispune de unregistru Excel cu numele Scoala.xlsunde sunt înregistaþi toþi elevii încare foaia de calcul Elevi conþinelista elevilor înregistraþi.

Pasul 1. Apelãm utilitarul MailMerge Wizard prin Tools�Lettersand Mailings.

Acest utilitar ne va prezenta pascu pas operaþiile necesare. Putemtrece la pasul urmãtor sau putemreveni la paºii anteriori.

Pasul 2. Alegem sursa de date dinfiºierul Excel folosind pentruaceasta opþiunea Use an existinglist ºi prin Browse alegem fiºierulºi din acesta foaia de calcul.

Pasul 3. Din lista Excel selectãmarticolele necesare, le ordonãmetc. Modul de inserare a datelor din surse externe a fost deja prezentat.

Pasul 4. Compunem documentul principal ºi în zonele variabile inserãm câmpul delegãturã cu baza de date prin butonul insert Merge Field de pe bara de butoane MailMerge deschisã automat.

Pasul 5. Vom previziona rezultatul prin butoanele de deplasare existente în fereastraPreview your letters. Putem gãsi o anumitã persoanã prin butonul Find a recipient.

Domnule Director,

Subsemnatul/a Panainte M. Irina Ioana, legitimat� cu A.I. seria MX nr.

247110, CNP 2700817921167, elev� în cls a XII-A a Lic. De Informatic� – Ia�i

v� rog a-mi aproba prezentarea la examenul de bacalureat în sesiunea iunie-iulie

2006. Men�ionez c� am absolvit a clasa a XII cu media 9.77

V� mul�umesc,

Panainte Irina Ioana08.05.2006 C�tre Directorul Liceului De Informatic� „Gr. Moisil” – Ia�i

Fig. 9.12. Utilitarul Mail Merge permite deplasarea lapasul urmãtor ºi la cel precedent

Fig. 9.11.Exemplu decererepersonalizatã


Fig. 9.13. Lista afiºatã de Mail Merge permite selectarea manualã a articolelor – prin caseta de validaredin faþa primului câmp sau prin verificarea unei condiþii introdusã prin fereastra de dialog Filter.

Sarcini de laborator1. Realizaþi o bazã de date Excel cu clienþii unei firme. Reþineþi numele, adresa, codul

fiscal, dacã este persoanã fizicã sau juridicã (societate comercialã, societate pe acþiuni,asociaþie familialã etc.).

2. Trimiteþi persoanelor fizice câte o felicitare de Anul Nou pe care sã o compuneþi înWord.

3. Trimiteþi persoanelor juridice câte o somaþie de platã compusã tot în Word.

Fig. 9.14. LMacheta cereri are câmpurile trecute în zonele variabile


Testul verificã abilitãþile de lucru cu tabele Word ºi legãtura cu Excel

1. Realizaþi în Word un tabel pentru evidenþa manualelor ºcolare la bibliotecã. ªtim cãmanualele ºcolare cu caracter gratuit sunt folosite 4 ani. Fiecare elev care a primit carteatrebie sã o înapoieze (eventual).

Într-un tabel treceþi: cod-carte, titlul, autorul, editura, preþul, clasa, numãrul deexemplare primite, numãrul de exemplare comandate, numãrul de exemplare distribuiteîn anul1, anul2, anul3, anul4 ºi numãrul de exemplare returnate de cãtre elevi în anul1,anul2, anul3, anul4.

2. Realizaþi în acelaºi document Word un tabel cu datele fiecãrei edituri (nume, adresa,cont-la-bancã etc.).

2. Introduceþi date (maxim 10 cãrþi de la 3 edituri) folosind formularul Word.

3. Aranjaþi tabelul cãrþilor pe clase ºi titlu ºi tabelul editurilor alfabetic dupã nume

4. Puneþi formule pentru a calcula:a. valoarea fiecãrui titlu primit (cantitatea primitã*preþ).b. diferentele intre distribuit ºi returnat în fiecare an ºi total.

5. Formataþi ambele tabele. Daþi-le un titlu. Plasaþi referinte tip bookmark la celedouã tabele.

6. Realizaþi pentru bibliotecã fiºe cu situaþia fiecãrui manual. Situaþia este compusãca document standard ºi cuprinde numele liceului, sigla, adresa acestuia, numelebibliotecarei sau orice informaþie credeþi cã este utilã. Partea variabilã va fi preluatã dintabel: codul-manualului, numele manualului, autorul ºi clasa, preþul, cantitatea comandatã,cantitatea primitã ºi diferenþa (cantitativ ºi valoric). Puteþi adãuga ºi alte variabile.

7. Copiaþi tabelul într-un registru Excel ºi calculaþi valoarea totalã pe edituri a cãrþilortrimise ºcolii.

8. Realizati o corespondenþã personalizatã pentru fiecare editurã, arãtând valoareacãrþilor comandate ºi cea primitã de la editurã prin asocierea tabelului Excel realizat lapunctul anterior cu un document Word cu textul compus de voi.

Test 8


Lecþia 10Problema securitãþii datelor

�� Protecþie în Excel

�� Protecþie în Word

10.1. Protecþia datelor în Excel

Microsoft Excel oferã câteva niveluri de protecþie pentru a controla cine poate accesaºi modifica datele tabelelor Excel. Un registru de calcul poate fi utilizat în comun, simultan,într-o reþea de calculatoare, fiecare utilizator având posibilitatea de a vedea schimbãrilefãcute de ceilalþi utilizatori. Acesta se numeºte modul Shared (partajat) de funcþionare aregistrului. Modul Protected permite utilizarea exclusivã a registrului de cãtre un utilizator.

10.1.1. Protecþia la deschiderea registrului

Se va permite accesul la registru numai utilizatorilor care cunosc parola. Se procedeazãastfel:

• Se acþioneazã File�Save as.

• Se deschide fereastra Tools�Options�Security, unde se fixeazã parola de acces laregistru. Dacã dorim ca parola sã fie datã înainte ca utilizatorul sã intre în registru, vomfolosi Password to open. Dacã dorim ca parola sã fie cerutã înainte de închiderea registrului,vom folosi Password to modify.

• Se închide fiºierul (cu suprascriere).

Registrul poate în întregime sã fie deschis numai pentru vizualizare dacã se activeazãcomutatorul Read-only Recommended (vezi figura 10.1).

91LECÞIA 10 • PROBLEMA SECURITÃÞI I DATELOR

10.1.2. Protecþia elementelor unui registru

Se realizeazã prin Tools�Protect Workbook.Dacã se activeazã butonul Structure, utilizatoruluinu i se va permite sã realizeze operaþii cu foi decalcul (sã adauge, sã ºteargã, sã redenumeascãfoi sau sã vadã foile ascunse). Dacã se activeazãcomutatorul Windows, utilizatorul nu poate sãmodifice poziþia, dimensiunea ferestrei unde estedeschis registrul.

10.1.3. Protecþia foii de calcul

Protejarea foii de calcul– printr-o parolã sau nu- in-terzice accesul la unele ce-lule. Se realizeazã prinTools�Protections�ProtectSheet. Se poate da o parolã.

Implicit, în foaia de cal-cul toate celulele sunt blo-cate; astfel, operaþia deinstalare a protecþiei inhibãaccesul la întreaga foaie.

Fig. 10.1. Opþiune pentru deschiderea numai pentru vizualizare

Fig. 10.2. Protecþia elementelor unuiregistru-foi de calcul

Fig. 10.3. Fixarearestricþiilor asupraelementelor foii decalcul: sã nu sepoatã modificaformatele, sã nu sepoatã insera sauºterge linii saucoloane, sã nu sepoatã sorta, constituisubtotalizãri sautabele pivot etc.


De aceea, înainte de a aplica protecþiase vor selecta celulele la care accesul estepermis prin Format�Cells�Protection ºidezactivarea comutatorului Locked.

Anularea protectiei asupra foii de calculse face prin Tools�Protections�Unprotectsheet.

10.1.4. Urmãrirea corecþiilor fãcute de utilizatori în modul Share

Microsoft Excel poate sã pãstreze informaþii despre cum s-a modificat foaia de calcul.De fiecare datã când un utilizator modificã datele din foaie, acestea se înregistreazã într-un istoric. Acest lucru este esenþial atunci când foaia de calcul este utilizatã simultan demai mulþi utilizatori în reþea.

Exemplul 1

Am fixat operaþia de urmãrire a corecþiei ºi observãm cine ºi când a modificat valoareadin celulã.

Fig. 10.5. Urmãrireacorecþiei.Celula C3 a fostmodificatã!

Urmãrirea modificãrilor este posibilã doar în modul de lucru Shared workbook. Fiecareacþiune poate fi anulatã sau pãstratã. Istoricul pãstreazã ultimele modificãri (în modimplicit, din ultimele 30 zile). De fiecare datã când se închide registrul se ºterg dinistoric acele informaþii care nu se încadreazã în termen.

Fig. 10.6. Mecanismul de înregistrare acorecþiilor asupra unei baze de date seactiveazã prin Tools�TrackChanges�Highlight Changes ºi în fereastra dedialog se fixeazã, eventual, domeniul de celulevizat.Corecþiile pot fi urmãrite pe ecran sau în altãfoaie de calcul.

Istoricul corecþiilor se poate pãstra în altã foaie de calcul – History – ºi poate ficonsultat pentru a se decide dacã modificãrile pot fi acceptate sau trebuie anulate.

Fig. 10.4. Fixarea protecþiei la celulele selectate

93LECÞIA 10 • PROBLEMA SECURITÃÞI I DATELOR

Fig. 10.7. Foaia de calcul History conþine informaþii despre modificãrile foii.

10.2. Protecþia datelor în Word

1. Protecþia prin parolã la deschiderea documentului se face prin Tools�Options�Security. Introducem parola în caseta text Password to open.

2. Protecþia prin parolã la modificarea documentului se face prin Tools�Options�Security. Introducem parola în caseta text Password to modify.

3. Protecþia formularului Word se face prin butonul Protect form de pe bara de butoaneFoms. Anularea protecþiei se face prin butonul Reset Form fields. Înaintea distribuirii în vedereacompletãrii unui formular de cãtre utilizatori acesta trebuie protejat astfel încât utilizatorii sãacþioneze numai în zonele desemnate. Acest lucru se face prin Tools�Protect Docu-ment�Forms. Se atribuie o parolã. Utilizatorii care o cunosc pot ºterge protecþia ºi schimbaformularul. Utilizatorii care nu cunosc parola nu pot decât sã acceseze câmpurile formularului.

4. Urmãrirea modificãrilor se face folosind Tools�Protect document�Tracked changes.

Sarcini de laborator*Patronul echipei de fotbal Dinamo doreºte sã ºtie în orice moment unde a jucat sau va

juca echipa sa, cu cine, când, la ce orã. Desigur, ar dori ºi un clasament a jocurilordesfãºurate, Câte goluri au marcat jucãtorii din echipa sa ºi cât au primit la fiecare meci,precum ºi totalul pe echipe. De asemenea, patronul echipei Dinamo este interesat sã ºtiecâte bilete s-au pus în vânzare faþã de capacitatea stadionului, cât este câºtigul obþinutde echipa Dinamo la fiecare meci jucat, cât este câºtigul total de la momentul începeriievidenþei. Uneori patronul de la Dinamo doreºte sã ºtie ºi care este câºtigul estimatpentru perioada urmãtoare (în funcþie de biletele deja vândute la meciurile urmãtoare ºide un preþ mediu cunoscut pentru fiecare stadion în parte). Se cere sã faceþi analizaproblemei ºi sã proiectaþi un registru pentru patronul echipei Dinamo. Stabiliþi care suntutilizatorii registrului ºi care date sunt „secrete”.

Fig. 10.8. Lista corecþiilor poate fi afiºatã ºidirect pe ecran, în caseta de dialogurmând sã decideþi imediat dacã salvaþicorecþia sau nu (opþiunea Highlightonscreen).


Lecþia 11Macroinstrucþiuniºi obiecte de control

�� Ce este ºi cum se defineºte o macroinstrucþiune�� Execuþia unei macroinstrucþiuni�� Asocierea macroinstrucþiunii la un buton

O macroinstrucþiune cuprinde o secvenþã de comenzi. Ea se memoreazã ºi poate fiexecutatã ori de câte ori avem nevoie.

11.1. Cum se realizeazã o macroinstrucþiune?

Pasul 1. Se stabileºte ce se doreºte a se realiza cu ajutorul macroinstrucþiunii ºi sefixeazã cum se va realiza acest lucru: secvenþele sau manevrele într-o anumitã ordine. Seface un plan de acþiune.

Pasul 2. Se alege Tools�Macro�Record New Macro… care deschide o fereastrã dedialog.

Pasul 3. Se introduce un nume semnificativ al macrocomenzii ºi o scurtã descriere.

Putem sã asociem o combinaþie detaste pentru lansare rapidã (ShortcutKey) De exemplu <Ctrl><M>.

Pasul 5. Se deruleazã manevreledorite exact în ordinea intenþionatã.Fiecare manevrã este înregistratã ca oinstrucþiune.

Pasul 6. Se opreºte înregistrarea prinTools�Macro�Stop recording.

95LECÞIA 11 • MACROINSTRUCÞIUNI ºI OBIECTE DE CONTROL

11.2. Cum se activeazã o macroinstrucþiune?

1. Prin shortcut, dacã aþi fixat o astfel de combinaþie. De exemplu tastãm <Ctrl><M> peregistrul unde am memorat macro-ul.

2. Prin Tools�Macro�Macros, care deschide fereastra de dialog pentru alegerea macro-instrucþiunii. Putem lansa în execuþie prin butonul Run.

3. Prin activarea unui buton care are asociatã ca acþiune macroinstrucþiunea.

11.2.1. Cum se asociazã macro-ul unui buton pe bara cu instrumente?

Pentru crearea unuibuton folosim din meniulprincial comanda View� C u s t o m i z e �Commands� Macros.Executãm manevra Drag&Drop ºi plasãm butonulCustom Button pe bara debutoane a ferestrei Excel(toolbars). Din meniul con-textual ataºat butonuluialegem Assign Macros ºiindicãm numele pe caredorim sã-l atribuim macro-instrucþiunii.

Putem schimba imaginea butonului sau/ºi putem atribui acestuia un nume.

11.2.2. Cum se asociazã o macroinstrucþiune unui buton de comandã?

Macroinstrucþiunea poate fi asociatã unui buton (sau altui obiect) din foaia de calcul.Pentru aceastã operaþie se foloseºte bara de butoane Control toolbox. Se selecteazã butonulde pe barã ºi se contureazã poziþia ºi dimensiunea obiectului pe foaia de calcul cu mouse-ul.

Fig. 11.2. Bara de instrumenteButonul Design permite construirea ºi/sau editarea obiectelor de pefoaia de calcul.Butonul Properties permite afiºarea listei proprietãþilor asociateobiectelor de pe foaie.Butonul View code afiºeazã codul program asociat obiectelor.

Pasul 1. Vom plasa butonul pe foaia de calcul astfel: se selecteazã cu mouse-ul de peControl toolbox ºi apoi se contureazã forma acestuia pe suprafaþa foii de calcul.

Fig. 11.1. Fixarea macro-ului la un buton


Pasul 2. Vom edita obiectul aºa cum se aratã în continuare.Vom deschide fereastra de proprietãþi prin

meniul contextual asociat butonului decomandã (clic dreapta). Unele proprietãþi auvalori implicite, cum ar fi culoarea de fundalsau culoarea textului asociat. Sunt multeproprietãþi pe care le-am proiectat deja cândam conturat obiectul pe foaia de calcul:poziþia, lãþimea, lungimea. Putem sã leschimbãm. Vom folosi însã proprietateaCaption care asociazã textul explicativ buto-nului. De asemenea este util sã dãm numeintern butonului pentru a ne referi la acesta(proprietatea name)

Am proiectat forma butoanelor. Am proiectat macro-instrucþiunile. La acest pas trebuie sã le legãm. În modulde lucru Design vom deschide meniul contextual asociatbutonului ºi vom alege opþiunea Assign Macro… caredeschide fereastra cu macroinstrucþiuni. Vom alege macro-instrucþiunea doritã.

Editarea textului asociat butonului se faceprin meniul contextual CommandButtonObject�Edit.Odatã butonul proiectat pe foaia de calcul,acesta are asociatã o metodã =EMBED(..)Pentru cã noi vom asocia ca acþiune o macro-instrucþiune, va trebui sã ºtergem Formulabar.În aceastã situaþie, editarea se face prinfereastra Properties.

11.4. Alte obiecte de control pe foaia de calcul

1. Obiect tip ImagePutem insera imagini prin obiectul Image la care vom selecta, din fereastra de proprietãþi,

linia cu proprietatea Picture ºi vom alege fiºierul imagine dorit.2. Obiect tip LabelPutem folosi un obiect tip Label pe foaia de calcul, cãruia îi fixãm proprietatea Caption.

Fig. 11.3. Fixarea macro-ului unui butonde comandã

97LECÞIA 11 • MACROINSTRUCÞIUNI ºI OBIECTE DE CONTROL

Exemplul 1

Sã presupunem cã reþinem patru categorii de informaþii: despre jucãtori, echipe, meciuriºi stadioane în zone diferite ale aceleiaºi foi de calcul.

Fiecare buton de comandã are asociat o macroinstrucþiune dedeplasare la zona de date indicatã.Macro-ul cu numele GO_echipe este proiectat astfel:

Pasul 1. Ne poziþionãm în fereastra „principal” ºi deschidem meniulTools�Macros�New.

Pasul 2. Atribuim macrocomenzii numele: go_echipe.

Pasul 3. Selectãm foaia de calcul „baza” ºi celula AA1.

Pasul 4. Selectãm foaia de calcul „Principal”.

Pasul 5. Oprim înregistrarea acþiunii prin Tools�Macros�Stop macro.

Zona pentru meciuri începe la celula A1, zona pentru echipe începe la celula AA1, zonapentru informaþii despre stadioane incepe la celula A200, iar cea pentru jucãtori lacelula AA200.Foaia principalã va conþine 4 butoane pentru selectarea automatã a zonei din foaia decalcul necesaã unui utilizator.

Proiectarea macro-urilor (macroinstrucþiunilor)Proiectãm 4 macro-uri care vor avea misiunea sã dirijeze controlul cãtre zonele de date.

Macro-ul Go_meciuri se va proiecta astfel:

Pasul 1. Ne poziþionãm în fereastra „principal” ºi deschidem meniul Tools�Macros�New.Pasul 2. Atribuim macroinstrucþiunii numele: go_meciuri.

Pasul 3. Selectãm foaia de calcul „baza” ºi celula A1 (unde începe zona pentru meciuri).

Pasul 4. Ne întoarcem ºi selectãm foaia de calcul „principal”.


Pasul 5. Oprim înregistrarea acþiuniiprin Tools�Macros�Stop Macro.

Asocierea dintre macro ºi butonul decomandã se face prin proiectareabutonului. În modul Design deschi-dem meniul contextual cu opþiuneaAssign Macro.

Se va verifica nivelul cunoºtinþelor ºi al deprinderilor de operare cu macro-uri .

Se va puncta fiecare sarcinã cu 1 pct + 1 pct din oficiu

Deschideþi registrul de calcul „inspectorat”.

1. Proiectaþi o foaie de calcul “olimpiada_judet” care sã conþinãnumele elevului, disciplina, clasa, ºcoala de unde vine, premiul.Puneþi 10 articole în care sã fie maxim 2 ºcoli, maxim 3 discipline,premiul va fi 1, 2, 3 sau 4 (pentru menþiune). 1 pct

2. Proiectaþi o foaie de calcul numitã “principal” astfel:

a. nu va avea gridlines; 0.5 pct

b. va avea o imagine de fundal-poza inspectoratului; 0.5 pct

c. se va scrie textul “inspectoratul scolar” cu Word Art; 0.5 pctd. va avea un obiect tip Label cu numele realizatorilor aplicaþiei. 0.5 pct

3. Proiectaþi butoane de comandã pentru:

a deplasarea în foaia de calcul ºcoli ºi sortarea alfabeticã a ºcolilor 2 pct

b. deplasarea în foaia de calcul profesori ºi obþinerea unui listecentralizatoare a numãrului de profesorilor pe catedre. 2 pct

c deplasarea în foaia de calcul olimpiade, unde se va face o tabelã pivotcu numãrul elevilor premianþi pe ºcoli ºi discipline. 2 pct

Test 9

99LECÞIA 12 • NUMERE ALEATOARE º I S IMULÃRI

Lecþia 12Numere aleatoareºi simulãri

�� Funcþia de generare RANDOM

�� Simularea Monte Carlo

Într-o lume atât de complexã ca aceasta în care trãim, a lua decizii este un act foartedificil, pentru cã trebuie sã se þinã cont de o multitudine de factori care pot avea oevoluþie variatã. O cale de a rezolva problemele decizionale este de a construi un modelfizic sau matematic ºi de a-l supune diferitelor teste. Orice intrare conduce la o anumeieºire. Prin simularea – pe suficient de multe valori – a intrãrilor putem ajunge la o ieºirefavorabilã ºi actul decizional este simplificat.

Sã luãm, de exemplu, un decident de la o staþie de benzinã care trebuie sã decidãnumãrul de pompe strict necesare pentru motorinã ºi pentru benzinã. Nu trebuie sã fienici prea puþine, astfel încât sã se formeze cozi, dar nici prea multe, ca staþia sã ºomezeºi sã nu-ºi acopere cheltuielile! Prima întrebare pe care ºi-o pune decidentul este caresunt factorii care influenþeazã numãrul de pompe? Va trebui construit un model alfuncþionãrii staþiei de benzinã care sã simuleze sosirile maºinilor cu o anumitã ritmicitate,duratele de servire, tipul de combustibil, cantitãþile solicitate. Pentru fiecare set alparametrilor de intrare se calculeazã funcþia obiectiv ºi se memoreazã aceste scenarii.Acum decizia se poate lua mult mai uºor!

Ne propunem sã discutãm despre problemele de simulare ºi de estimare a evoluþieiunui fenomen folosind generarea aleatoare a datelor.

12.1. Funcþia Rand()

Funcþia Rand() genereazã un numãr aleator subunitarcu 15 zecimale, cuprins între 0 inclusiv ºi 1 exclusiv.

De fiecare datã când este apelatã, funcþia întoarcealt numãr.

Se poate solicita regenerarea numãrului prin mane-vra manualã cu F9.

Pentru a genera un numãr întreg într-un interval a-bfolosim formula = int(Rand()*b)+a


Sarcini

Scrieþi într-o celulã funcþia Rand() ºi copiaþi-o în urmãtoarele 5 celule. Ce observaþi?Se pãstreazã numãrul din prima celulã? Câte numere s-au generat? Copiaþi formula în1000 de celule. Se repetã numãrul generat?

Exemplul 1

Jocul „Cap-Pajurã!”Foaia de calcul va afiºa „cap” sau „pajurã” în funcþie de un numãr generat caresimuleazã aruncarea unei monede. Afiºaþi 10 situaþii!

AnalizaProbabilitatea ca o monedã sã cadã pe o faþã sau alta este egalã de 50%

Vom asocia numãrul generat fie la o faþã, fie la alta. Cum?

Dacã ºtim cã numãrul generat de funcþia Rand() este cuprins între 0 ºi 1, vom consideracã moneda cade pe „cap” dacã numãrul se gãseºte în intervalul 0-0.5 ºi vom afiºa„pajura” dacã numãrul generat este mai mare decât 0.51

Putem sã scriem formula urmãtoare = if(rand()>0.5;”pajura”;”cap”)Mai simplu:

Dacã avem o cãciulã cu 10 bilete, pe 5 dintre ele vom scrie „cap” ºi pe 5 vom scrie„pajurã”. Sã numerotãm biletele ºi sã spunem cã toate biletele de la 1 la 5 înseamnã„cap” si de la 6 la 10 înseamnã „pajurã”.

Noi nu avem cãciulã, dar avem calculator care poate sã genereze aceste valori ºi nerevine sarcina sã le interpretãm.

Pentru generarea numãrului vom folosi formula =int(rand()*10)+1.

Putem scrie formula urmãtoare =if (int(rand()*10)+1>5; „pajura”;”cap”).Dar moneda poate sã cadã ºi pe cant! Va trebui sã þinem cont ºi de aceastã posibilitate.Presupunem cã în 45% din cazuri moneda cade pe o faþã, în alte 45% cade pe cealaltãfaþã, iar în 10% moneda cade pe muchie.

Cum asociem numãrul generat la un eveniment? Dacã numãrul este între 1 ºi 10 va ficam greu, va trebui sã lucrãm cu zecimale, mai bine sã generãm un numãr între 1 ºi100.

Primul pas este sã împãrþim toate cele 100 de numerele în trei segmente care corespundprobabilitãþilor de producere a evenimentelor, deci numerele generate între 1-45 vorcorespunde „afiºare cap”; cele între 46-90 vor corespunde la „afiºare pajurã” iar celeîntre 91-100 la „afiºare muchie”).Sã scriem formula folosind if

=if(int(rand()*100)+1<46:”cap”; if (int(rand()*100)+1<91; „pajura”;”muchie”)

Dar, atenþie, formula are o hibã, pentru cã de fiecare datã când se apeleazã funcþiaRand() se genereazã alt numãr!


De aceea vom folosi altã metodã. Generãm numãrul o singurã datã ºi afiºãm mesajulcorespunzãtor cãutând valoarea într-o tabelã folosind Vlookup.

Eveniment Procent Interval de numere

Valoare în c�utare inexact�

Afi�are 1 167 1-167 1 Afi�are 2 167 168-334 168 Afi�are 3 167 335-501 335 Afi�are 4 167 502-669 502 Afi�are 5 167 670-836 670 Afi�are 6 167 837-1000 837 Total numere 1002

Proiectarea foii de calculPasul 1. Proiectãm tabela de cãutare îndomeniul I3:J6 ºi o vom numi„tabela_de_cãutare”.

Pasul 2. Scriem formula pentru generareîn celula F3 =int(rand()*100)+1.

Pasul 3. Scriem formula de afiºaremesaj prin cãutare în celula vecinã.

G3=vlookup(F3,tabela_de_cautare;2).

Pasul 4. Copiem pe coloanã ºi verificãmfuncþionarea simulãrii prin F9!

Exemplul 2

Dorim sã simulãm aruncarea unui zar. Sã proiectãm o foaie de calcul pentru 10 aruncãri.

AnalizaFiecare faþã corespunzãtoare unui numãr are exact aceeaºi probabilitate de apariþie.Probabilitatea de apariþie a unui eveniment este 1/6 = 0,1666666, adicã aproximativ= 0,167 (lucrãm cu 3 zecimale!) deci vom lua 1000 de numere pentru ca 167 dintreele sã reprezinte probabilitatea doritã. (obs. Este o micã eroare la ultimul eveniment!).Cum asociem numere fiecãrui eveniment?

Dacã vom genera un numãr între 1 ºi 167 înseamnã cã zarul cade cu numãrul 1, dacãnumãrul generat este între 168 ºi 334 înseamnã cã zarul afiºeazã valoarea 2, º.a.m.d.Sã urmãrim tabelul de mai jos.

Eveniment Procent Interval numere

Valoare în tabela de c�utare

Cerere 5 buc 10 0-9 0 Cerere 15 buc 20 10-29 10 Cerere 20 buc 30 30-59 30 Cerere 25 buc 30 50-89 50 Afi�are 30 10 90-99 90 Total numere 100


Eveniment Procent Interval numere Valoare în tabela de c�utare Cerere 5 buc 10 0-9 0 Cerere 15 buc 20 10-29 10 Cerere 20 buc 30 30-59 30 Cerere 25 buc 30 50-89 50 Afi�are 30 10 90-99 90 Total numere 100

Proiectarea foii de calculPasul 1. Nu uitãm sã folosim facili-tãþile seriilor de date: pentru primacoloanã vom începe cu valoarea 1ºi apoi prin Edit�Fill�Series scriemseria cu pasul 167 oprindu-ne la1000. Pentru a doua coloanã putemfolosi mânerul Fill în seria cu pasul1 pornind tot de la 1.

Pasul 2. Punem într-o celulã formula de generare =int(rand()*1000)+1.Pasul 3. Punem alãturi formula de cãutare.

Pasul 4. Copiem si verificãm funcþionarea prin tasta F9.

Exemplul 3

O problemã de simulare: problema stocurilorFirma de vânzare a telefoanelor mobile „Cell-Compact” doreºte sã îºi deschidã o

sucursalã în alt cartier. S-au înregistrat cererile pe zi pentru produsul sãu ºi s-a observatcã în 10% din cazuri se cer 5 bucãþi, în 20% se cer 15 bucãþi, în 30% din cazuri secer 20 sau 25 bucãþi, iar numai în 10% se vând 30 buc. pe zi.

Se doreºte sã se determine care sã fie stocul iniþial de telefoane pentru nouasucursalã astfel încât sã nu fie cereri neacoperite timp de 10 zile. Sã se simulezecererile. Sã se memoreze scenariile schimbând nivelul iniþial al stocului la valorile50, 100, 150, 200 ºi înregistrându-se numãrul de zile de rupturã de stoc.

AnalizãDate de intrare: stocul de la începutul gestiunii.

Date de ieºire: numãrul de zile cu rupturã de stoc.

Prelucrãri: în prima zi stocul iniþial este chiar stocul de început de gestiune.

În fiecare zi din stocul iniþial se scade cererea ºi se obþine stocul final.

Ruptura de stoc apare atunci când cererea este mai mare decât stocul iniþial.Stocul de la sfârºitul unei zile devine stoc iniþial în ziua urmãtoare

Cum simulãm o cerere? Vom genera un numãr între 0-99 ºi vom asocia ntervale devalori numerice pentru fiecare eveniment:


Proiectarea foii de calculPasul 1. Proiectãm tabela de cãutare într-o zonã vizibilã a foii de calcul pentru a putea

verifica corectitudinea datelor generate. Pentru verificarea suplimentarã se poate asociao coloanã în care sã calculãm numãrul de valori din intervale, deci procentul corespunzãtorcererii prin scãderea valorilor din celulele vecine. Ex: H4=I5-I4.

Pasul 2. Completãm stocul iniþial de gestiune în B2.

Pasul 3. Completãm seria cu pasul 1 pentru numãrul zilei.

Pasul 4. Completãm numãrul generat B5=int(rand()*100).

Pasul 5. Completãm cererea C5=vlookup(B5, $i$4:$j$8, 2).

Pasul 6. Completãm stocul iniþial al primei zile cu valoarea de început de gestiuneD5=B2.

Pasul 7. Calculãm stocul final E5 = D5 – C5.

Pasul 8. Punem o formulã pentru a marca ruptura de stoc, ex F5=IF(E5<0;”da”;”nu”).Pasul 9. Punem stocul iniþial al zilei urmãtoare ca fiind cel final al zilei curente, deci

D6=E5.

Pasul 10. Copiem toate formulele pe coloane.

Pasul 11. Completãm celula Rãspuns cu numãrul de zile cu rupturã E2=COUNTIF(F5:F14,”da”).

Pasul 12. Verificãm funcþionarea foii de calcul prezentatã mai sus cã pentru aceeaºivaloare de intrare apãsând tasta F9.

Observãm în imaginea foii de calcul cã 5 din zece zile sunt cereri neacoperite, ceeace, desigur, nu este avantajos pentru firmã.

Observaþie. Metoda simuleazã „realitatea” ºi se apropie de ea cu atât mai mult cu câtnumãrul de simulãri este mai mare. Putem reface situaþia pentru multe seturi de valorigenerate si compara indicatorul „numãrul de zile de rupturã”. Pentru aceasta vom centralizarezultatele în altã foaie de calcul.


1. Simulaþi situaþia cererilor în cel putin 8 cazuri.

2. Calculaþi pentru fiecare simulare gravitatea rupturii ca fiind suma cererilor nerezolvateîn cele 10 zile.

3. Puneþi un buton de comandã pe foaia de calcul care sã permitã înregistrarea valorilorindicatorilor în altã foaie.

4. Imaginaþi o foaie de calcul în care sã intervinã aprovizionarea cu o cantitate x înmomentul în care stocul atinge o valoare y.


Lecþia 13Elemente de programare înVisual Basic

�� Mediul Visual Basic�� Afiºarea mesajelor prin Msgbox�� Introducerea datelor prin Inputbox�� Instrucþiuni If, Select, Do While

Microsoft Windows lucreazã cu obiecte. Un obiect este un lucru (o fereastrã, o foaie decalcul, un registru, un buton, un comutator etc.) cãruia îi putem defini anumite proprietãþi.De exemplu, un buton de comandã poate fi caracterizat prin textul explicativ asociat,distanþa în pixeli faþã de marginea din stânga a ferestrei unde este plasat, distanþa faþã demarginea superioarã a ecranului, înãlþimea, starea vizibilã sau ascunsã etc. Fiecare obiectExcel (sau Access, sau Word) are definite toate proprietãþile într-o fereastrã specialã Prop-erties care se deschide din meniul View sau din meniul contextual ataºat obiectului.Utilizatorul poate seta anumite proprietãþi la valorile dorite sau acceptã valorile implicite.

Fig. 13.1. Fereastra de proprietãþi. Obiectul cãruia îi este descrisãlista de proprietãþI se poate alege din lista de obiecte.Pe linia de titlu se afiºeazã automat numele obiectului.Fiecare obiect are un nume intern – proprietatea name. Înexemplu name=”cmd_salut”Un buton de comandã are un text asociat – proprietateaCaption. Îîn captura Caption=”salut”.Alte proprietãþi: poziþia (Height, Top, Left), culoarea de fundal(BackColor), culoarea pentru scris (Fore Color), stilulcaracterelor (Font, size), imaginea asociatã (picture).

Obiectele recunosc ºi rãspund la anumite acþiuni, numite evenimente.

Un eveniment este o activitate specificã ºi predeterminatã, îniþiatã de sistemul deoperare (eveniment intern) sau de utilizator (eveniment extern) ºi la care un obiect ºtie sãreacþioneze. Exemplu: apãsarea pe butonul mouse-ului, miºcarea mouse-ului, apãsareape o tastã sau întâlnirea unei erori la introducerea datelor într-un textbox (casetã cu text).

105LECÞIA 13 • ELEMENTE DE PROGRAMARE ÎN VISUAL BASIC

Unui eveniment i se poate ataºa o anumitã procedurã (este numitã chiar Event proce-dure) sau o macroinstrucþiune în limbajul Visual Basic. Metodele sunt proceduri asociateunui obiect. De exemplu, o metodã asociatã la apãsarea cu mouse-ul pe un buton decomandã poate sã afiºeze un mesaj sau sã activeze o anumitã foaie de calcul, etc.

Calificarea obiectelor

Pentru a ne referi la obiecte trebuie sã cunoaºtem numele, proprietãþile ºi metodelesale. O proprietate sau o metodã se separã de obiectul calificat prin punct.

Exemple:a) Cmd_salut.caption=”salut” Fixãm o proprietate a butonului cmd_salutb) heets(“meciuri”).Select Se selecteazã-adicã devine activ obiectul

de tip foaie de calcul cu numele “meciuri”c) ActiveWindow.panels(1).Activate Se activeazã o zonã (panel) cu numãrul 1 din

fereastra activã

Visual Basic for Applications este un subset al limbajului de programare Visual Basicºi permite dezvoltarea aplicaþiilor într-o manierã programaticã. Este important de semnalatfaptul cã VBA este utilizat de majoritatea aplicaþiilor Office ºi putem transfera cod de lao aplicaþie la alta. Mai mult, datoritã automatizãrii OLE, putem întegra complet aplicaþiileOffice pentru a transfera date între ele ºi a rula chiar o aplicaþie din alta. Codul VBA estereþinut în module. Editarea unui modul se face în mediul Visual Basic Editor deschis dinTools�Macro�Visual Basic Editor sau direct la operaþia de asociere a metodei unui obiectprin Controls Toolbox, butonul View Code.

Toate modulele conþin o parte de declaraþii ºi o parte de proceduri. La fiecare pasputem fi asistaþi de un înstrument foarte util Auto-List Members – care afiºeazã proprietãþileºi metodele adecvate. Comentariile se scriu precedate de un caracter apostrof.

13.1. Afiºarea mesajelor prin funcþia Msgbox

Sã presupunem cã pe o foaie de calcul dorimun buton care sã afiºeze data curentã.

Nu dispunem de un macro care sã permitãafiºarea unui mesaj, astfel încât vom proiecta pascu pas procedura asociatã evenimentului Click albutonului.

Pasul 1. Vom proiecta butonul de comandãfixând proprietãþile

Name =” Cmd_salut” ºi caption = “Salut”.


Pasul 2. Vom scrie înstrucþiunile necesare în aplicaþia Visual Basic. Pentru aceastafolosim butonul View Code de pe Control Toolbox sau vom executa dublu clic cu mouse-ulpe butonul de comandã cmd_salut. Se deschide o fereastrã de proiectare manualã aprocedurii Click în mediul Visual Basic.

Sã analizãm puþin aceastã fereastrã.

Afiºarea datelor ºi a mesajelor se poate face prin funcþia internã MsgBox care areformatul general =MSGBOX(mesaj [, tip-butoane-ºi-pictograme][,titlu])

Fig. 13.2.Proiectarea coduluiîn VBA se face foarteuºor datoritãinstrumentului AutoList Members. Cândse recunoaºte osecvenþã de cod, sedeschide automatlista de opþiuni.

Argumentul <mesaj> conþine informaþia ce trebuie comunicatã ºi este singurulobligatoriu. Butoanele asociate ferestrei pot fi: Ok, Cancel, Abort, Retry, Ignore etc. Sepot folosi ºi pictograme cum ar fi semnul de întrebare, de exclamare, etc.

Reîntoarcere laExcel

Run – executãprocedura

Break – forþeazãoprirea procedurii

Stop –terminã

Acþiunea scrisã esteasociatãevenimentului click

Butonul se numeºte“cmd_salut”

Procedura asociatã se numeºte“cmd_salut_click()”.Observaþi funcþia Date pentru data sistem


Caseta de dialog pentru mesaj poate conþine 7 tipuri de butoane care au asociatã oanumitã constantã.

Constant� Valoare Buton vbOKOnly 1 OK vbCancel 2 Cancel vbAbort 3 Abort vbRetry 4 Retry vbIgnore 5 Ignore Vbyes 6 Yes Vbno 7 No

Pentru a ºti pe care buton a apãsat utilizatorul, vom folosi constantele asociate sauvalorile numerice.

Argumentul tip-butoane-ºi-pictograme permite definirea numãrului de butoane, a tipurilorºi a pictogramelor asociate.

Constant� Afi�arevbokOnly Butonul OKvbOkCancel Butoanele Ok �i CancelvbAbortRetryIgnore Butoanele Abort, Retry, IgnorevbYesNo Butoanele Yes �i NovbYesNoCancel Butonele Yes, No, CancelvbCritical Pictograma X ca semn critic (de interzicere)Vbquestion Pictograma ? semn de întrebarevbExclamation Pictograma ! semn de exclamarevbInformation Pictograma i semn de informa�ie

Exemple:

x = MsgBox(“astazi este “ & Date,vbInformation, “buna ziua”)

x = MsgBox(“astazi este “ & Date,vbExclamation, “buna ziua”)

x = MsgBox(“doriti continuare?”,vbQuestion + vbYesNo)

x = MsgBox(“astazi este “ & Date, vbCritical,“buna ziua”)


Atenþie! Vom lansa în execuþie metoda, adicã ne vom reîntoarce în Excel ºi vom apãsabutonul Cmd_salut. Dacã am scris corect procedura, atunci se va deschide caseta demesaje, dacã nu, va trebui sã refacem procedura.

Observaþie. Visual Basic dispune de altã funcþie pentru data sistem ºi anume DATE(),pe care am folosit-o în exemplul prezentat anterior.

Sã facem cunoºtinþã cu obiectele, proprietãþile ºi metodele lor prin câteva exemple.

Exemplul 1

Pentru butonul de comandã cmd_meciuri am asociat un macro cu numele go_meciuri.Observaþi în capturã codul acestui macro.

În cazul macroinstrucþiunii go_meciuri,ne-am poziþionat pe foaia de calcul Baza,apoi am activat celula A1 din cadranul 1(unde se gãseau datele despre meciuri),dupã care am oprit macroinstrucþiunea.

DeciAm fãcut cunoºtinþã cu clasa de obiecte Sheets, iar o instanþã a acesteia este foaia decalcul cu numele baza. Acestui obiect i s-a aplicat metoda Select. Fereastra activã(ActiveWindow) are un obiect inclus, ºi anume cadranul 1 (panels (1), cãruia i seaplicã metoda Activate. Aceeaºi metodã Select se asociazã ºi domeniului de celule(obiectul Range).

Exemplul 2

În foaia de calcul principal dorim un buton care sã ne conducã direct în foaia decalcul Clasament, sã ordoneze tabelul dupã numãrul de meciuri jucate ºi sã facã osubtotalizare dupã acest criteriu.

Macro-ul Go_clasament a înregistratselectarea foii de calcul clasament,a tuturor celulelor, sortarea dupacoloana B, subtotalizarea prinData�Subtotals si poziþionarea pe ocelulã oarecare (B19) pentru a ieºidin starea de selecþie a foii.


Exemplul 3

În foaia de calcul clasament vom proiecta un buton care sã anuleze subtotalizareadatelor ºi sã revinã în foaia principalã.

Pentru butonulCmd_revin_in_princ amasociat macro-ulrevin_din_clasam creatanterior prin metodaApplication.Run.

Pentru butonul dute_in_clasam am lansat în execuþie macro-ul“Go_clasament” creat anterior. Observaþi calificarea numelui demacroinstrucþiune prin numele registrului unde se gãseºte.

Macro-ul revin_din_clasam a pornit dinfoaia de calcul clasament, a selectat ocelulã din tabel ºi a anulat operaþia desubtotalizare (Data�Subtotals�RemoveAll), apoi s-a poziþionat pe o celulã ºi aselectat foaia de calcul principal.

Exemplul 4

Realizãm un macro prin care sã scriem douã valori ºi sã le adunãm. Codul se poatevedea folosind comenzile Tools�Macro�Visual Basic Editor.

Am selectat celula F15, unde am scris valoarea123, apoi în F16 am scris valoarea 234 ºi amadunat în celula F17.Deci pentru selectarea unei celule se foloseºteRange(“adresa”).Select.Pentru atribuirea unei valori în celula activã sefoloseºte ActiveCell.FormulaR1C1=valoare.


Exemplul 5

Realizãm un macro prin care facem manevra de copiere a unor date.

Am selectat foaia de calcul baza ºi am copiat toatedatele într-o nouã foaie de calcul.Deci pentru copiere se foloseºte Selection.CopyºiActiveSheets.Paste.Inserarea unui foi de calcul se face prinSheets.Add.

Ce am învãþat?

Selectarea unei foi de calcul Sheets("numele foii").SelectSelectarea unei celule Range("adres�").SelectLansarea unui macro Application.Run ("numele macro-ului")Activarea unei zone -panels- din foaiaactiv�

ActiveWindow.panels(num�rulzonei).Activate

Atribuirea unei valori într-o celul� ActiveCell.FormulaR1C1=valoare

13.2. Introducerea datelor prin funcþia INBOX

Existã în Visual Basic o funcþie prin care solicitãm informaþii ºi receptãm rãspunsulutilizatorului. Ea este Inbox() ºi are formatul general:

INBOX(ºir-text-afiºat-în-casetã, ºir-titlu)

Exemplul 1

Solicitãm întroducerea numelui utilizatorului aplicaþiei noastre într-o casetã de dia-log, ca în imagine. Dupã introducerea numelui, acesta se va afiºa într-un mesaj deîntâmpinare.

Cum facem?

Pasul 1. Proiectãm butonul decomandã.

Pasul 2. Deschidem editorulVisual Basic prin butonul ViewCode de pe Controls Toolbox. Sedeschide automat fereastra deeditare a metodei asociatã eve-nimentului click al acestui obiect.


13.3. Luarea unei decizii în program: instrucþiunea IF

O structurã decizionalã este o ramificare a instrucþiunilor care urmeazã evaluãriicondiþiei. Instrucþiunea If are formatul general:

IF conditie THEN

Acþiune1

ELSE

Acþiune2

END IF

Se citeºte astfel:

Dacã condiþia este adevãratã, atunci (THEN) se executã Acþiune1, altfel (ELSE) seexecutã Acþiune2. În ambele situaþii, dupã terminarea acþiunilor se revine dupã End IF.

Exemplul 1

Sã presupunem cã în aplicaþia FOTBAL avem unele date „secrete” pe care le-amascuns într-o zonã depãrtatã în foaia principal, începând din celula AA1000. Nepropunem sã verificãm parola utilizatorului ºi, dacã este cea doritã, sã-i permitemaccesul la zona secretã. Parola corectã este fotbal-toata-ziua.

Cum procedãm?Pasul 1. Proiectam un buton de co-mandã cu urmãtoarele proprietãþi:name=”cmd_salut”, Caption=”datesecrete”.

Pasul 2. Proiectãm metoda click()ca în imaginea capturatã folosindmetoda Select pentru a activa foaiade calcul (obiectul se numeºteSheets) ºi celula de pornire(Range(“AA1000”)).

Scriem instrucþiunile de intrare ºide afiºare. Rulãm pentru verificareprin butonul Run. Dacã apar erori, elesunt imediat semnalate.

Observaþi codul metodei în fe-reastra editorului.


13.4. Instrucþiunea de selecþie SELECT CASE.

Atunci când avem de evaluat o condiþie care poate lua mai multe valori, putem folosiinstrucþiunile IF imbricându-le, dar mai simplu de scris este instrucþiunea Select. Urmãriþiîn exemplu înstrucþiunea Select case comparativ cu If.

Exemplul 1

La aplicaþia FOTBAL dorim sã întrebãm utilizatorul ce informaþii doreºte sã vadã:despre meciuri, echipe, jucãtori sau stadioane. Presupunem cã am separat în foi diferiteaceste informaþii ºi le-am numit asigurând secretizarea registrului, în ordine: S1, S2,S3, S4.

If expresie=val1 then

Actiune-1

else

if expresie=val2

then Actiune2

else

Actiune3

end if

end if

Select case expresie

Case val1

Actiune1

Case val-2

Actiune2

else

Actiune3

End Select

Ambele instrucþiuni seexecutã astfel:Se evalueazã expresia. Dacãeste egalã cu val1, atunci seva executa Acþiune1, dacãnu, va verifica dacã este egalãcu val2. Dacã da, se vaexecuta Acþiune2, altfel seexecutã Acþiune3.

Dacã cererea este corectã,atunci programul va activafoaia de calcul solicitatã, altfelva da un mesaj.Observaþi codul procedurii înimaginea capturatã.


13.5. Instrucþiunea de ciclu Do WHILEPentru acele acþiuni care trebuie repetate se foloseste instrucþiunea DO.

Cum se executã?Se evalueazã condiþia ºi atât timp cât condiþia esteadevaratã se executã acþiunea.

Exemplul 1

Sã presupunem cã la întrarea în aplicaþia FOTBAL se deschide o foaie de prezentaregeneralã a aplicaþiei. Pentru a accesa celelalte foi se cere o parolã. Dacã este corectã,atunci se va deschide foaia principal care va dirija în continuare acþiunile utilizatorilor.Dacã parola este eronatã se permite reintroducerea ei, dar de maxim 5 ori. Dupãepuizarea încercãrilor se închide registrul! Pentru închiderea registrului curent (obiectuleste ActiveWorkbook) se foloseºte metoda Close, deci vom scrie: ActiveWorkbook.Close.Variabila folositã este eroare, care marcheazã faptul ca parola este gasitã prin valoareazero.

DO WHILE conditie

Actiune

LOOP

Eroare=adevaratNr_incercari=0Cat timp not eroare sinr_incercari<5 executa introduce parola în variabila x Daca x=”fotbal” Selectez foaia Principala eroare = fals Altfel Maresc nr_incercari Sf_ifSf_cat_timp

Dacã nr_încercãri=5 atunci dãm mesaj ºi închidemregistrul prin metoda Close.Observaþi forma instrucþiunii msgbox pentru ultimul afiº!

Problemã rezolvatã. Ghiciþi numãrul!Vã propunem proiectarea unui joc simplu. Calculatorul va genera un numãr ºi operatorul

va încerca sã-l ghiceascã!


Încercãrile se opresc atunci când esteghicit numãrul sau jucãtorul abandoneazã.

Fiecare situaþie este marcatã prin mesajesemnificative, prezentate în imaginileurmãtoare.

Cum facem?Pasul 1. Proiectãm butonul care declanºeazã jocul.

Pasul 2. Prin View Code întrãm în editorul Visual Basic ºi scriem instrucþiunile jocului.

Generarea numerelor aleatoare în întervalul 0-1 se face prin funcþia Rnd().Pentru a obþine numere între 1-10 vom folosi expresia INT(Rnd()*10)+1.

Variabila gãsit, care þine controlul reluãrilor, este iniþial zero. Dacã se gãseºte numãrulsau se abandoneazã jocul, valoarea sa devine 1.

Se repetã, cât timp nu s-a gãsit numãrul, introducerea unui numãr de jucãtor ºicompararea cu nr_computer. Dacã sunt egale, se afiºeazã mesaj ºi se seteazã gasit=1,altfel se afiºeazã un mesaj ºi se permite continuarea cu alt numãr sau abandonarea.Pentru aceasta am folosit în caseta de mesaje pe lângã pictogramã ºi vbOKCancel, pentru


includerea butoanelor (OK care va permite continuarea) ºi Cancel (care prin poziþionareavariabilei gasit=1 forþeazã ieºirea din ciclul While).

Observaþi cã funcþia chr(13) întoarce caracterul <New line> pentru a putea scrie pealtã linie continuarea ºirului!


1. Completaþi jocul astfel încât sã se numere încercãrile ºi sã se afiºeze la sfârºituljocului.

2. Completaþi jocul astfel încât, dacã s-a rãspuns corect, sã se poatã juca din nou,numãrându-se de fiecare datã ºi jocurile ºi încercãrile din fiecare joc.

3. Completaþi jocul astfel încât sã se introducã chiar la începutul jocului, de cãtrejucãtor, intervalul de variaþie a numãrului generat.

4. Completaþi jocul pentru a fi jucat de mai mulþi jucãtori. Se va introduce numelefiecãrui jucãtor.

Problemã rezolvatã. Cofetãria lui Moº CrãciunLa un studiu efectuat între orele 10-14 la cofetãria lui Moº Crãciun din oraºul nostru s-

a constatat cã în fiecare minut sosesc copii! În 10% din cazuri sosesc 1 sau 2, în 20%din cazuri sosesc 4 copii, iar în câte 30% din cazuri au venit câte 3 copii odatã! MoºCrãciun nu poate servi mai mult de 3 copii pe minut ºi a fãcut cerere scrisã sã fie aduseniºte Crãciuniþe în ajutor. Sã se simuleze activitatea cofetãriei ºi sã se verifice dacãcererea lui Moº Crãciun este justificatã. Care credeþi cã ar trebui sã fie numãrul ajutoarelor,dacã fiecare poate servi câte 3 copii pe minut?

Analiza problemeiEste tot o problemã de simulare. Vom asocia valori numerice frecvenþelor de producere

a evenimentelor, astfel: pentru sosirea unui singur copil (frecvenþa este de 10%) vomasocia numere între 0-9; pentru sosirea a 4 copii (frecvenþa este de 20%) vom asocianumere între 10-29; sosirea a 5 copii (frecvenþa de 30%) o vom simula printr-un numãr înintervalul 30-59, iar sosirea a 3 copii cu aceeaºi frecvenþã o vom simula printr-un numãrîntre 60-89. Restul de numere între 90-99 vor însemna sosirea unui singur copil.

Datele de intrare: capacitatea de servire sau numãrul de copii serviþi pe minut

Numãrul de copii sosiþi se va genera automat.

Date de ieºire: lungimea medie a cozii, numãrul total de simulãri, numãrul maxim decopii care au stat la coadã, numãrul total de copii sosiþi, numãrul total de copii serviþi.

Prelucrãri: în fiecare minut soseºte un numãr de copii, numãrul de copii la coadã esteegal cu numãrul de copii sosiþi + numãrul de copii aflaþi deja la coadã în minutul serviriiminus numãrul de copii serviþi.

Vom folosi o foaie de calcul pentru simularea servirilor, având ca parametru de intrarecapacitatea servirii ºi variind numãrul de copii sosiþi.


Pe o altã foaie de calcul vom centraliza rezultatele. Putem înregistra câte un scenariula apãsarea unui buton de comandã sau/ºi mai multe odatã.

Proiectarea foii de calcul pentru simulare. Am numit-o MoºCrãciun.1. Am aranjat lista cu indicatori în domeniul A3:B9 astfel:

a. capacitatea de servire este în celula B3 ºi am numit-o “capacitate”

b. lungimea medie a cozii B5=AVERAGE(G11:G131)

c. numãrul total de simulãri=COUNT(C11:C1000)

d. numãrul maxim de copii la coadã=MAX(G11:G130)

e. numãrul de copii sosiþi=SUM(E11:E130)f. numãrul de copii serviþi=B6*capacitate

2. Am proiectat tabela de cãutare a numãrului generat în zona F3:I7 ºi am numit-otabela_de_cautare.

3. Pe coloana C11:C130 am trecut minutul servirii, care se genereazã ca o seriepornind de la 10:00 cu pasul 1 pânã la 12:00.

4. Pe coloana D11:D130 am generat un numãr aleator în întervalul 1-100, deci ampus D11=int(rand()*100) ºi am copiat în rest.

5. Pe coloana E11:E130 am asociat numãrului generat evenimentul, adicã numãrulde copii sosiþi prin cãutare în tabela de cãutare E11==VLOOKUP(D11,tabela_de_cautare,4).

6. Pe coloana F11:F130 am pus capacitatea de servire.

F11=MIN(capacitate,E11) ºi F12==MIN(G11+E12,capacitate) pe care o vom copiaîn urmãtoarele 4 celule.

7. Pe coloana G11:G130 am pus numãrul de copii la coadã, astfel: G11=E11-F11 ºiG12=G11+E12-F12, pe care îl vom copia în restul coloanei.


Proiectarea foii de calcul pentru centralizarea simulãrii (centraliz_mos)Am considerat util sã calculãm media indicatorilor din fiecare scenariu.

B4=capacitate; B6=average (c6:az6) ºi am copiat în rest.

Pe coloana C am pusprimele valori prin copiere dinfoaia de calcul cu simularea.Atenþie! Am copiat doar valorileprin Copy�Paste special.

Celelalte valori vor fi copiateautomat prin execuþia unuimacro ºi inserate mereu într-onouã coloanã (D).

Proiectarea macro-ului pentru înregistrarea îndicatorilor în foaia de calcul de centralizare.Îl vom numi inreg-mos.

Pasul 1. Ne poziþionãm în foaia de calcul MoºCrãciun. Apãsãm F9 pentru un scenariu.

Pasul 2. Ne deplasãm în foaia de calcul Centraliz_mos.

Pasul 3. Ne poziþionam pe coloana D, o selectãm ºi înserãm o nouã coloanã prinInsert" Column.

Pasul 4. Ne întoarcem în foaia de calcul MoºCrãciun.

Pasul 5. Selectãm domeniul B3:B9 ºi copiem prin Edit�Copy sau CTRL+C.Pasul 6. Ne deplasãm pe foaia de calcul Centraliz_mos ºi selectãm celula D4.

Pasul 7. Lansãm comanda Edit�Paste Special cu selectarea comutatorului Value.

Pasul 8. Oprim înregistrarea.

Proiectarea unui macro pentru înregistrarea celor 10 scenarii se va face prin folosireaVisual Basic for Aplication.

Pasul 1. Prin Tools�Macro�New macro dãm nume unui nou macro ºi în partea deînregistrare selectãm doar o celulã. Închidem înregistrarea. (Am preferat acest lucru pentrusimplitatea procedeului. În felul acesta am creat un nou modul pe care putem sã-l editãmprin VBA.)


Pasul 2. Prin Tools�Macro�Macros deschidem fereastra cu toate macro-urile ºi nepoziþionãm pe noul macro.

Prin butonul Edit vom deschide fereastra VBA si scriem codul pentru execuþia repetatãa macro-ului “inreg_mos” ca în imaginea capturatã a acestei ferestre.


Sã se proiecteze o foaie de calcul pentru înscrierea participanþilor la Loto ºi afiºareacâºtigãtorilor. Fiecare participant este înregistrat cu numãrul biletului, numele ºi cele 6numere alese de acesta. Numerele sunt între 1 ºi 99. Extragerea numerelor câºtigãtoaredeterminã determinarea automatã a câºtigãtorilor.

Preþul unui bilet este de 200 lei. Din totalul sumei obþinute prin vânzarea biletelor60% se reþine de cãtre firma organizatoare, iar restul se împarte câºtigãtorilor! Atenþie!Este câºtigãtor doar acea persoanã care are toate numerele extrase pe biletul sãu. Generaþialeator numerele câºtigãtoare.

119LECÞIA 14 • DEZVOLTAREA PROIECTELOR C U BAZE DE DATE EXCEL

Lecþia 14Dezvoltarea proiectelor cu bazede date

�� Ce este ºi cum se realizeazã un produs informatic

�� Studii de caz

�� Teme pentru proiectul final

Metodologia de realizare a oricãrei lucrãri, nu numai a unei lucrãri informatice, trebuiesã permitã celui care realizeazã lucrarea sã rãspundã la urmãtoarele întrebãri: Ce trebuiefãcut? De ce trebuie fãcut? Cum ar trebui sã se facã? Când trebuie fãcut? Cine trebuie sãfacã? Cine rãspunde? Unde se face? Cât va costa? Cine va beneficia de rezultatul lucrãrii?

Procesul de realizare a unui produs informatic de o anumitã complexitate poate ºitrebuie sã fie structurat într-o serie de etape/subetape/activitãþi bine determinate. Înrealizarea produsului informatic sunt antrenate diferite persoane, nu numai informaticieni;de aceea este necesarã o bunã organizare ºi specializare, o repartizare precisã a sarcinilor;se recomandã folosirea tehnicilor speciale adecvate dimensiunii proiectului ºi etapeirespective. Trebuie sã existe o documentare bunã, într-o formã standardizatã, a fiecãreifaze parcurse.

Criteriul principal care trebuie sã stea la baza realizãrii produsului este cel economic,trebuie deci estimate corect costurile ºi beneficiile.

Etapa 1. Definirea problemei

Primul pas în efortul de a dezvolta un produs informatic este luarea contactului cuclientul pentru clarificarea problemei puse. Care este problema ce trebuie rezolvatã? S-aînþeles complet? Unde se va implementa produsul informatic? Ce variante de rezolvaresunt? Nu gãsim un produs pe piaþa soft care sã ne rezolve problema? Cât ar costa, ceavantaje avem dacã îl vom proiecta noi pas cu pas, comparativ cu achiziþionarea produsului?Ce costuri estimative ar fi? Ce este mai avantajos?

Scopul esenþial al acestei etape este clarificarea problemei. Gândiþi-vã ce pierdere detimp ºi bani pentru a rezolva impecabil o problemã greºit pusã! Este un moment decisivpentru cã, uneori, clientul îºi dã seama cã nu poate suporta financiar efortul de proiectarea produsului ºi renunþã.

Etapa 2. Analiza

Este o etapã foarte importantã pentru cã permite stabilirea exactã a intrãrilor, ieºirilorºi prelucrãrilor necesare rezolvãrii problemei. Cine va utiliza produsul? Cine va introduce


datele ºi cine are nevoie de informaþiile oferite de produs? Prin interviuri, chestionare ºiobservãri directe, prin cercetarea tuturor documentelor, actelor etc. se clarificã necesitãþileclientului ºi se stabilesc posibile variante de rezolvare. Mai concret, acum se fixeazãcare sunt datele de intrare ºi ce validãri suportã, care sunt datele de ieºire, în ce formatese cer aceste date (tabele centralizatoare, grafice) cu ce periodicitate etc. Se fixeazãprincipalele prelucrãri ale datelor.

Se aleg produsele soft cele mai potrivite pentru a rezolva integral sau parþial problema.Se ºtie cã Excel este foarte adecvat în cazurile de simulare, de reprezentare automatã agraficelor ºi situaþiilor centralizatoare. Dacã avem situaþii care cer la ieºire rapoarte saucomunicãri mai elaborate putem folosi Word. Dacã problema propusã este gestiunea uneisocietãþi comerciale cu multe activitãþi, atunci va trebui sã ne gândim la un program degestiune a bazelor de date – de exemplu Access.

Etapa 3. Proiectarea

Dacã hotãrâm cã Excel este soluþia pentru rezolvarea problemei noastre atunci vatrebui sã facem o schiþã pe hârtie a registrului separând zonele de introducere sau prezentarea aplicaþiei de cele de date de intrare, de calcule ºi de cele de ieºire.

• În zona de introducere este bine sã prezentãm aplicaþia ºi anumite indicaþii deutilizare. Uneori zona ocupã o primã foaie de calcul cu un design deosebit ºi careserveºte ºi ca panou de bord cu butoane pentru orientarea utilizatorilor printre foilede calcul. Putem folosi ºi un meniu aºezat pe bara de butoane.

• În zona datelor de intrare vor acþiona operatorii pentru introducerea ºi editarea datelorde bazã ale aplicaþiei, parametrii scenariilor. Este bine ca sã definim reguli devalidare ºi formate specifice care sã permitã operatorului introducerea corectã adatelor.

• În zona calculelor vom pune formulele pe care va trebui sã le protejãm la ºtergeriaccidentale. Stabilim dacã referinþele din formule sunt absolute sau relative. Estemai comod pentru proiectant ºi pentru claritatea expresiilor dacã vom da numezonelor referite absolut.

• În zona datelor de ieºire vor fi rezultatele prelucrãrilor sub forma unor tabele deraportare, tabele pivot, situaþii centralizate sau/ºi grafice.

Uneori este util sã proiectãm un registru ºablon salvat ca template. Un registru ºablon,în care zonele de date au valoare zero, este încãrcat de utilizator, apoi sunt completatezonele de intrare cu datele utilizatorului ºi registrul se salveazã pe disc cu alt nume.Registrul ºablon rãmâne în Template neschimbat.

Etapa 4. Construirea ºi testarea

Etapa de construire se poate uni cu etapa de proiectare pentru lucrãrile de mai micãamploare, mai ales prin folosirea unui mediu vizual de dezvoltare. Rãmâne însã necesarãtestarea produsului cu date fictive în vederea depistãrii ºi corectãrii erorilor. Primul paseste scrierea pe hârtie. Pentru o multitudine de valori posibile – tipice ºi extreme – pentrudatele de intrare ºi se urmãresc rezultatele obþinute manual ºi pe foaia de calcul.


Uneori foaia de calcul nu funcþioneazã corect din cauza lipsei de înþelegeri a problemeipuse iniþial. Beneficiarul aplicaþiei aºteaptã altceva. În acest caz va trebui sã se facã dinnou analiza problemei ºi sa se discute cu cei implicaþi în lãmurirea ei.

Se va verifica dacã formulele funcþioneazã corect pentru toate combinaþiile datelor deintrare.

Se verificã apoi modul cum se prezintã ieºirile, sub ce formã sunt, dacã sunt acceptatede beneficiar, dacã sunt uºor înþelese, dacã sunt vizibile, etc. în situaþia în care rãspunsulla aceste verificãri o impune se va reveni asupra proiectãrii registrului de calcul.

O altã sursã de erori este legatã de introducerea datelor. Sunt anumite date obligatoriide completat de cãtre operator, date care intervin în formule. Se va verifica validareafiecãrei date, mesajele cãtre operator, protecþiile puse.

Etapa 5. Implementarea

De obicei produsul program vine sã înlocuiascã o procedurã manualã sau învechitã.Se renunþã la vechiul sistem de prelucrare ºi sunt puse în practicã procedurile de culegere,transmitere ºi prelucrare a datelor specifice aplicaþiei informatice. Este etapa cea mai„riscantã“ pentru proiectanþi, pentru cã de succesul sau insuccesul demersului poatedepinde soarta întregului proiect. O aplicaþie informaticã nu este staticã; ea trebuie sã fiepermanent adaptatã la cerinþele noi care apar. Cerinþele de modificare pot fi de micãamploare ºi se vor realiza din „mers“ fãrã sã fie afectate structurile de bazã ale bazei dedate sau prelucrãrilor, caz în care are loc activitatea de întreþinere a aplicaþiei. Dacã însãsunt schimbãri majore în activitatea modelatã de produsul soft, atunci este necesarã o re-analizare ºi reproiectare software.

Etapa 6. Documentarea

Dacã proiectantul ar fi ºi singurul utilizator al registrului, probabil ar fi suficienteetichetele ºi câteva instrucþiuni pentru ca aplicaþia sã fie clarã. Dar produsul informaticpoate ºi trebuie sã fie folosit de un numãr mare de utilizatori, unii chiar nefamiliarizaþi cuExcel pe o perioadã mare de timp. Unii utilizatori cunosc mediul Windows, lucreazã cuExcel, alþii nu. Fiecare utilizator, fie cã introduce datele, fie cã citeºte rezultatele, trebuiesã primeascã instrucþiunile pentru activitatea la care este autorizat sã rãspundã. Deasemenea, dupã un timp aplicaþia va trebui sã fie revizuitã. Cel ce va avea aceastãsarcinã trebuie sã înþeleagã ce s-a fãcut, ce formule s-au folosit, unde sunt limitele ºirestricþiile aplicaþiei, pentru a corecta acolo unde este necesar.

Ce tipuri de documentaþie existã?

• documentaþia de prezentare a aplicaþiei – care conþine informaþii generale de naturãtehnicã ºi economicã asupra produsului program în întregul sãu; este destinatã poten-þialilor utilizatori; are un pronunþat caracter comercial; insistã asupra posibilitãþilor oferitede produsul program, eventualele performanþe care sã permitã alegerea produsului dinmai multe programe existente pe piaþã pentru rezolvarea aceleiaºi probleme.

• documentaþia de utilizare – care conþine informaþii privind utilizarea curentã aprodusului program, descrieri complete pentru un începãtor ale tuturor acþiunilor pe


care va trebui sã le execute. Se vor scrie separat instrucþiunile pentru operatori laintroducere date ºi pentru beneficiarii care consultã datele prelucrate.

• documentaþia destinatã proiectanþilor implicaþi în întreþinerea ºi dezvoltareaprodusului – va conþine descrieri complete ale foilor de calcul cu formule, cu rapoarte,descrieri ale zonelor protejate, eventual parolele de acces.

Documentaþia asigurã:

• certitudinea cã operaþiile indicate se vor realiza ºi, ulterior, se vor executa aºa cumtrebuie;

• excluderea anumitor presupuneri sau ipoteze în afara celor descrise;

• clarificarea elementelor ºi noþiunilor care apar;

• aplicarea anumitor structuri ºi proceduri tip în abordarea ºi rezolvarea unui numãrmare de probleme;

• utilizarea raþionalã ºi eficientã a tehnicii de calcul.

Specificaþiile sunt – pe plan mondial – din ce în ce mai apropiate de o formã standard.

Activitatea de documentare este destul de laborioasã ºi este necesar sã o privim caparte importantã a efortului de realizare a unui produs informatic alãturi de analizã,proiectare ºi implementare.

Studiu de caz – SC APEMIN SA

Societatea Comercialã Apemin SA este distribuitor unic de ape minerale pentru Moldova.Ea se aprovizioneazã cu sticle de apã mineralã de la diferiþi producãtori ºi distribuiemarfa cãtre clienþii sãi. Forma de ambalare este sticla de diferite capacitãþi. Preþul esteîn lei. O sticlã de Borsec de 1L poate costa 5000 lei, 7000 lei sau chiar 10000 lei.

Managerul doreste sã cunoascã în fiecare moment:

d. numãrul total de sticle intrate în depozit ºi ieºite (la momentul curent);

a. intrãrile ºi ieºirile cantitativ în fiecare lunã ºi pentru fiecare produs;

b. total intrãri/ieºiri pe produs de la începutul anului ºi pânã în luna curentã;

c. stocul curent pentru fiecare produs în parte, cantitativ ºi valoric.

Analiza problemeiPentru cã sunt mai multe sortimente de produse este comod sã þinem separat evidenþa

stocurilor, a intrãrilor ºi a ieºirilor.

Automat la o cantitate intratã/sau ieºitã va trebui actualizat stocul ºtiind cã stoc_curent=stoc_iniþial+intrãri-ieºiri.

Unitatea de mãsurã este diferitã. Se considerã cã Borsec de 1L este alt produs decâtBorsec de 2.5L.


Preþurile nu sunt aceleaºi pentru toatã perioada. Se considerã cã Borsec 1L la 2000lei este alt produs decât Borsec 1L la 1200 lei. Dacã se schimbã preþul, atunci se adaugão nouã linie – se considerã cã este alt produs.

Evidenþa activitãþii din fiecare an se þine într-un registru separat, care are aceleaºi foi:intrãri, ieºiri, stocuri. Numele acestui registru va avea ca sufix ultimele cifre ale anului.De exemplu, ape_min_04.xls, ape_min_05.xls etc.

La începutul activitãþii se vor introduce stocurile iniþiale.

Proiectarea foilor de calcul

Foile de calcul pentru intrãri ºi ieºiri au acelaºi conþinut, ºi anume informaþiile despreprodus (codul, denumirea etc.) ºi cantitãþile intrate/ieºite din fiecare produs pe lunã.Automat se vor calcula totalurile.

Totalul a fost pus pe linia superioarã.

În imagine sunt selectate zonele de date ºi anume:

Ce se introduce? Domeniu de celule

Denumire produs B5:B100

U.M. C5:C100

Pre� D5:D100

Atenþie! Aceste date trebuie introduse odatã în toate foile. Deci inainte de a le intro-duce grupaþi foile. Celulele neselectate conþin formule. Cu xxxxx am indicat o celulãnefolositã.

Ce se calculeaz�? Formule a. Num�rul curent AUTOFILL b. Linia de total care este o sum� a valorilor pe coloane:

E4=sum(E5:E100); copiem pe linia 4, cu excep�ia celulei F4

c. Totalul cantit��ilor intrate/ie�ite pentru fiecare produs

E5=sum(G5:O5); copiem pe coloana E

d. Total valoric pe produs F5=E5*D5; copiem pe coloana F


Foaia de calcul Stocuri

Ce se introduce? Domeniu de celule

TVA J1

Stocul ini�ial F5:F100

Zonele selectate sunt folosite pentru introduceri de date de cãtre operatori: informaþiiledespre produse (trebuie introduse simultan în cele 3 foi), cota TVAºi stocul iniþial.

Ce se calculeaz�? Formule

a. Linia de total care este o sum� a valorilorpe coloane:

E4=sum(E5:E100); copiem pe linia 4

b. Pentru fiecare produs totalul intr�riloreste preluat din foaia de calcul intr�ri

G5=intrari!E5; copiem pe coloana G

c. pentru fiecare produs, total ie�iri estepreluat din foaia de calcul ie�iri

H5=iesiri!E5; copiem pe coloana H

d. pentru fiecare produs Stoculcurent=stoc-ini�ial+total intr�ri-total ie�iri

E5=F5+G5-H5; copiem pe coloana E

f. Valoare stoc=stoc-curent*pre�ul I5=E5*D5; copiem pe coloana I

g. valoare TVA=valoare stoc*TVA J5=I5*TVA; copiem pe coloana J

h. valoare final�=valoare stoc+valoare TVA K5= I5+J5; copiem pe coloana K

Testarea

Pentru a testa funcþionarea vom deschide toate foile de calcul în ferestre diferite ºivom introduce valori oarecare. Urmãriþi planul de testare în tabelul urmãtor.


Ultima acþiune

Aplicãm protecþie prin parolã la foile de calcul pentru a evita modificarea formulelordin foile de calcul

Acþiunea va fi realizatã la nivelul fiecãrei foi.

Pasul 1. Selectãm zonele de date ºi prin Format�Cells�Protection anulãm comutatorulLocked.

Pasul 2. Selectãm zonele de formule ºi prin aceeaºi manevrã activãm comutatorulLocked.

Pasul 3. Tools�Protection�Protect Sheet.

Punem protecþie la registru pentru a nu putea adãuga/ºterge foi prin Tools�Protection�Protect Workbook.

Studiu de caz: Maxi-taxiO firmã de taxi are mai multe maºini pe care le trimite în teritoriu. Fiecare ºofer are

obligaþia de a înregistra kilometrajul maºinii la plecarea în cursã ºi la întoarcere în fiecarezi. Conducerea doreºte o situaþie a kilometrilor parcurºi de o maºinã ºi valoarea încasãrilorîn fiecare zi. ªeful mai doreste ceva: sã afle dacã maºina are ºi un consum neînregistrat!Diferenþele nejustificate apar dacã kilometrajul maºinii la sosirea din cursã într-o zi nucoincide cu cel existent la plecarea în cursã a doua zi.

Ac�iune Ce efect are? 1. Ne pozi�ion�m în foaia intr�ri �i grup�m celelalte foi; introducem un prim produs Borsec de 1.5 la pre�ul 1000 lei/sticla �i Bibor�eni de 1L la 2000 lei/sticla

Informa�iile apar în toate cele 3 foi în exact acelea�i celule

2. Înregistr�m sosirea a 12 sticle în ianuarie �i 7 în martie

E5=E4=19; F5=9000 în intrari G5=G4=E5=E4=19; I4=I5=19000; J4=J5=4180; K4=K5=23180 în stocuri

3. Modific�m TVA la 10% Se modific� coloana J4=J5=1900 �i K4=K5=20900

4. Se scot 19 sticle de Borsec E5=E4=19; F5=19000 în iesiri H5=H4=19; restul zero în stocuri

5. Punem pentru Borsec stocul ini�ial 100 Se modific� în stocuri: E5=E4=100 I4=I5=100000; J4=J5=10000; K4=K5=110000

6. Complet�m cu intr�ri în diferite luni pentru Borsec.

Observ�m sumele pe linia 5 dac� sunt corecte în foaia intr�ri �i totalul comunicat în stocuri.

7. Complet�m cu ie�iri în diferite luni pentru Borsec.

Observ�m s� fie actualizat totalul pe linia produsului �i corespondentul din stocuri.

8. Punem date pentru al doilea produs. Cum se modific� sumele pe coloane.


Se cere:a. Realizaþi o foaie de calcul pentru rezolvarea problemei pentru o lunã ºi o maºinã,

presupunând cã tariful practicat pentru un kilometru este fixat la începutul lunii. Numelelunii va fi afiºat pe prima linie, iar ziua va fi pe prima coloanã. Se va calcula automatsuma corespunzãtoare curselor ºi cea nejustificatã.

b. Se doreºte calcularea la finalul lunii a sumei achitate ca salariu ºoferului, ºtiind cãdin total încasãri firma îºi ia un comision de minim 30%. Apoi se scad cheltuielile debenzinã. Alte cheltuieli legate de întreþinerea maºinii sunt suportate de firmã ºi nu intrã înacest calcul. Se suportã din salariu eventualele penalizãri ºi amenzi de circulaþie. Dacãsunt kilometri nejustificaþi ºi ºeful constatã cã ºoferul este vinovat, atunci i se va scãdeadin salariu ºi va mai primi o penalizare suplimentarã de 10% din valoarea prejudiciului.Realizaþi noua variantã de foaie de calcul.

c. Cum ar putea ºeful sã vadã dacã ºoferul este corect? Mai ales cã acesta are obligaþiaca la sfârºitul fiecãrei zile sã depunã ºi banii încasaþi din curse. Faceþi corecturile necesareºi marcaþi situaþiile excepþie astfel: cu roºu diferenþa strict pozitivã, cu verde diferenþanegativã ºi nimic pentru zero Nu trebuie sã fie alterate celelalte calcule!

d. S-a constat cã tariful zilnic nu mai este constant pe lunã. Conducerea hotãrãºte cadiferenþele de kilometraj sã fie evaluate la preþul maxim din luna respectivã. Ce trebuiemodificat în foaia de calcul?

e. Foaia de calcul va trebui utilizatã de douã categorii de persoane: pe de o partepersonalul firmei, care trebuie sã introducã datele curente: kilometrajul, tariful, suma delei depusã în fiecare zi, iar pe de altã parte conducerea, pentru a afla informaþiile dorite.

Realizaþi accesul limitat numai la domeniul de date pentru personal printr-un buton pebara de butoane cu textul „utilizatori”.

Realizaþi alt buton care, folosit de conducere, va permite afiºarea ºi editarea întregiifoi de calcul.

Analiza problemeiPct. a)

Ce va introduce operatorul? Când?Zona din foaia

de calculTaxa /km La începutul lunii D1Ziua, indexul la plecare �i la sosire La fiecare curs� A4:C34

Ce se va calcula?Nr. km parcur�i ca diferen�� între index sosire�i index plecare

La fiecare curs� D4:D34

Valoarea corespunz�toare drumului ca nr. kmparcur�i înmul�it� cu pre�ul pe km

La fiecare curs�-efectuat� F4:F34

Numarul de km nejustifica�i este calculat cadiferen�� între indexul ma�inii la sosirea dincurs� într-o zi �i indexul înregistrat ca fiind deplecare în curs� a doua zi.

La fiecare curs�-cuîncepere din a doua zi.

E5:E34

Total km parcur�i La orice ad�ugare de date D36

Valoarea corespunz�toare km parcur�i La orice ad�ugare de date F37Total km nejustifica�i E39Valoarea nejustificat� E40


Proiectarea foii de calculFormulele sunt afiºate în imaginea urmãtoare. Observaþi faptul cã pentru diferenþe de

kilometraj a trebuit folositã o expresie cu funcþia ISBLANK, pentru a le calcula numaidacã în ziua curentã maºina a plecat în cursã. Folosim bara Horizontal Split pentru avedea în aceeaºi fereastrã toate datele!

Vom da nume domeniilor ºi celulelor pentru uºurinþa scrierii ºi înþelegerii formulelor.Astfel domeniul D4:D34 se va numi km_parcurºi; domeniul E5:E34 se va numi diferenþe;domeniul F4:F34 se va numi Valoare_zi etc.

Pct. b)Adãugãm în zona de date celule pentru comisionul firmei, preþul benzinei, penalizãrile

ºi amenzile luate de ºofer, precum ºi valoarea deciziei ºefului pentru cazul în care aparacele diferenþe nejustificate. Folosim din nou funcþia ISBLANK pentru a lua în consideraresau nu celula val-nejustificatã.

În imagine observaþi numele celulelor folosite ºi formulele necesare rezolvãrii.


Pct. c)Adãugãm coloana sumã depusã de ºofer în zona

de date. O numim lei_depusi pentru a ne puteareferi la ea. Atenþie! De fapt, dãm nume domeniuluide date de la prima zi la cea de-a 31-a zi a lunii.Calculãm într-o nouã coloanã (numitã dif_valoare)diferenþa dintre aceastã sumã ºi valoarea_zi.Folosim formatarea condiþionatã pe coloanadif_valoare. Atenþie! Pentru ascunderea valorii zerovom folosi culoarea White.

Pct. d)

Adãugãm o coloanã tarif-pe-km în zona de date. Modificãm formula în coloanavaloare_zi=tarif-pe-km*km_ parcurºi. Copiem formula pentru cele 31 de zile. Modificãmcelula D1 care are numele lei-km = max(tarif) ce intervine în calculul valorii pentrudiferenþe de kilometraj. Celelalte formule rãmân aceleaºi.

Pentru a vedea legãturile dintre formule (dependenþele) ne poziþionãm pe celula sursã(ex. E4) ºi activãm Tools��Formula Auditing��Trace Dependents.

Pct. e)Protecþia foii de calcul se va face prin ascunderea liniilor ºi coloanelor cu formule ºi

interzicerea acesului la ele atunci când utilizatorul este un operator oarecare.

Operatorului i se permite doar completarea unui domeniu fixat, ca în imagineaurmãtoare.

Dacã utilizatorul face parte din conducere, atunci vom anula protecþia ºi vom reafiºacelulele calculate.

Pentru uºurinþa operãrii, mai ales cã sunt manevre repeta-bile, vom realiza douã macro-instrucþiuni ºi le vom plasa pebara de butoane cu un text semnificativ.


Cum facem?Pasul 1. Proiectãm macroinstrucþiunea mac_utiliz pentru utilizatori obiºnuiþi:

1.a Deschidem editorul de macroinstrucþiuni prin Tools��Macros��New RecordMacro, atribuim un nume ºi începem înregistrarea manevrelor urmãtoare: nepoziþionãm pe coloanele E:J, le selectãm ºI le ascundem prinFormat��Column��Hide;

1.b ne poziþionãm pe liniile 35 pãnã la 50, le selectãm ºi le ascundem prinFormat��Row��Hide.

1.c. Selectãm tot domeniul B4:G34 ºi formatãm asfel încât sã permitem intro-ducerea/editarea valorilor în cazul protejãrii foii. Vom dezactiva caseta devalidare Locked din Format Cells��Protection.

1.d. Vom proteja foaia fãrã sã punem parolã deschizând pagina Tools��Protection��Protect Sheet ºi executãm clic pe OK.

1.e. Oprim înregistrarea.

1.f. Asociem macro-ul (cu numele mac_utiliz) butonului depus pe bara cuinstrumente (toolbar).

Pasul 2. Proiectãm macro-instrucþiunea pentru ºef. De astã datã manevrele vor fi anulareaprotectiei, apoi de afiºare a coloanelor ºi liniilor ascunse.

Asociem macro-ul butonului pe toolbar.


Sarcini de laboratorRealizaþi un proiect informatic cu baze de date în Excel util într-un domeniu de activitate

ales de voi (turism, învãþãmânt, producþie, asistentã socialã, gestiune societãþi comerciale,sãnãtate, transporturi, sport etc).

Cerinþe

1. Proiectul va conþine (cel puþin) 3 fiºiere: registru de calcul (Excel)/bazã de date(Access), documentaþie (Word) ºi prezentare (Powerpoint).

2. La nivelul clasei se vor realiza proiecte pentru „probleme” din domenii diferite.

3. Fiind un proiect didactic evaluarea va urmãri modul în care s-au însuºit tehnicilede analizã a unei probleme, de proiectare ºi de lucru efectiv cu bazele de date înExcel, de elaborare a documentaþiei unei lucrãri informatice în Word ºi deprezentare în PowerPoint.

Notarea se va face pe lucrare. Vã sugerãm urmãtoarele criterii, fiecare notat de la 1 la10 pct.

1. Fixarea corectã a datelor de intrare, alegerea sau folosirea unor coduri pentruuºurinþa prelucrãrilor, validãri complexe.

2. Imaginarea unor utilizatori diferiþi la aplicaþia voastrã, fiecare cu anumite drepturide acces; rezolvarea problemei protecþiei datelor.

3. Imaginarea unor moduri variate de prezentare a informaþiilor calculate din bazade date – grafice, tabele pivot, situaþii centralizatoare.

4. Folosirea unor funcþii logice, statistice, financiare, a operaþiilor de sortare ºifiltrare, folosirea operaþiei de cãutare.

5. Analiza datelor ºi estimarea tendinþelor de evoluþie a unui fenomen, construireaunor scenarii.

6. Legarea aplicaþiei Word cu Excel în vederea corespondenþelor automate.

7. Crearea unei interfeþe prietenoase pentru aplicaþie, folosirea macro-instrucþiunilor(sau a programãrii în Visual Basic) pentru automatizarea prelucrãrilor.

8. Structurarea informaþiei pentru documentaþiile proiectului având în vedereutilizatori diferiþi: fie clienþi – (prezentare Powerpoint) fie informaticieni pentrudezvoltarea aplicaþiei (document Word).

9. Imaginarea unor variante de rezolvare diferite – aceeaºi problemã în Excel ºiAccess sau douã variante în Excel; imaginarea unor situaþii de dezvoltare aaplicaþiei în viitor.

10. Prezentarea proiectului – modul de colaborare a echipei.

Fiecare echipã va fi formatã din minim 5 persoane cu urmãtoarele roluri: analist,proiectant, tester, documentarist, ºef de proiect. Fiecare echipã va cerceta în comundomeniul propus, îsi va defini clar „problema” pe care trebuie sã o rezolve, va fixa restricþiile


Sugestii de teme pentru proiecte

1. Teste-grilãRealizaþi un registru de calcul pentru compunerea testelor ºi examinarea elevilor pe

bazã de teste. Profesorul va introduce fiecare întrebare cu variantele sale de rãspuns,rãspunsul corect ºi punctajul.

Elevul va vedea doar întrebãrile ºi va rãspunde în coloana rezervatã lui. Nu va puteavedea nici punctajul total, nici rãspunsurile corecte.

Evaluarea se va face automat la cererea profesorului, când vor fi afiºate – pe ecransau/ºi la imprimantã – întrebãrile, rãspunsurile corecte, rãaspunsurile elevului cu mesajul“corect” sau “incorect”, punctajul oficial al rãspunsului ºi punctajul obþinut de elev.Foaia va calcula punctajul total al testului ºi cel obþinut de elev.

Este recomandat sã lucraþi cu macro-uri care sã vã permitã:

1. Introducerea de cãtre profesor a unui nou test (se va face un macro care sã inserezeo nouã foaie de calcul cu aceeaºi structurã ca a celei iniþiale, cu toate formulele, dar fãrãdatele de intrare!).

2. Afiºarea tuturor coloanelor pentru profesor.

ºi limitele proiectului. Nu toate activitãþile domeniului de interes pot fi modelate în cadrulacestui proiect didactic.

Echipa va alege o siglã pentru proiect, un motto, o anumitã imagine care sãindividualizeze proiectul ºi echipa.

Fiecare echipã îºi va repartiza rolurile pe membri. De exemplu:

a. Analistul – va realiza analiza problema fixându-se asupra utilizatorilor potenþiali,intrãrilor, ieºirilor, codificãrilor existente ºi/sau imaginate, validãrile impuse

b. Proiectantul – va proiecta ºi realiza registrul de calcul separând în foi distinctebaza de date, informaþiile prelucrate, panoul de bord al aplicaþiei.

c. Testerul – va testa funcþionarea registrului simulând toate situaþiile de intrareposibile

d. Documentaristul – va scrie documentaþia de utilizare a aplicaþiei (în Word) ºicea destinata intre]inerii si dezvoltarii produsului

e. ªeful de proiect – va „lega” ºi superviza toate aceste activitatãþi; va scriedocumentaþia de prezentare (în Powerpoint).

Fiecare echipã va stabili un plan a îndeplinirii sarcinilor astfel încât sã se poatã fizicrealiza proiectul ºi sã fie prezentat colegilor la termenul impus.

Atenþie!Nu uitaþi criteriile de evaluare. Imaginaþi situaþii sau informaþii cerute în „problema”

formulatã de voi pentru a fi punctaþi la toate cerinþele.


3. Ascunderea coloanelor cu rãspunsul corect ºi /sau a punctajului la fiecare întrebarepentru elev (puneþi protecþie la foaia de calcul atunci când este disponibilã elevului).

4. ªtergerea rãspunsurilor unui elev.

2. Cheltuieli de întreþinerePentru evidenþa cheltuielilor de întreþinere la o asociaþie de locatari se folosesc douã

evidenþe de bazã: una cu datele generale ale fiecãrui apartament (suprafaþã, numãr delocuitori, numãr de ochiuri gaz metan etc.), alta cu facturile lunare (total cheltuieli peasociaþie legate de apã rece, canalizare, cãldurã, întreþinere comunã).

Se cunoaºte cã sumele totale pentru gaz metan se vor împãrþi pe fiecare apartament înfuncþie de numãrul de ochiuri-gaz, cele legate de apã rece în funcþie de numãrul depersoane, iar cele de încãlzire în funcþie de suprafaþã. Pe baza lor se afiºeazã ºi se reþineîntr-un istoric suma datoratã de fiecare apartament în fiecare lunã. La achitarea sumelordatorate pentru întreþinere se elibereazã o chitanþã ºi se înregistreazã achitarea.

Afiºaþi sumele pentru luna curentã ºi datoriile pe fiecare scarã, separat ordonate dupãnumãrul apartamentului.

Trimiteþi somaþii de platã proprietarilor restanþieri.

3. PoliclinicaO policlinicã are mai multe cabinete particulare de diferite specializãri, fiecare cabi-

net fiind al unui singur medic.

Fiecare cabinet practicã tarife diferite pentru consultaþiile sale ºi plãteºte, în afaracheltuielilor interne legate de aparaturã, materiale etc.) cota parte din cheltuielile comunelegate de apã, cãldurã, luminã, curãþenie, pazã.

Fiecare cabinet trebuie sã-ºi afiºeze programul, sã-ºi planifice consultaþiile, sãînregistreze costurile ºi încasãrile. Odatã cu prezentarea pentru prima datã a bolnavuluila cabinet sunt trecute datele sale generale. Pentru fiecare consultaþie în parte sunt detaliatesimptomele, diagnosticul, tratamentul indicat ºi valoarea consultaþiei.

Policlinica are ºi un triaj, care face legãtura cabinetelor cu exteriorul. Pe de o parte, latriaj trebuie sã vinã informaþii de la cabinete, informaþii care, prelucrate, vor putea constituibaza informaþiilor cãtre exteriorul firmei.

Astfel, de obicei managerul general, interesat de bunul mers al afacerii sale, doreºtesã afle totalul veniturilor obþinute de fiecare cabinet pentru a putea calcula cât i se cuvinefirmei (s-a hotãrât ca fiecare cabinet sã nu plãteascã o taxã fixã, ci un procent din veniturileobþinute, conform unei grile elaborate de comitetul director).

4. SalariiPentru o normã întreagã lunarã un profesor primeºte un salariu de bazã fix de X lei.

Norma este 72 ore/lunã dacã profesorul are sub 25 ani vechime ºi de 64 ore/lunã altfel.Orele suplimentare se plãtesc cu 75% din salariul de bazã /normã. Dacã un profesor nuare norma întreagã, atunci el este retribuit ca ºi pentru orele suplimentare cu 75% dinsalarul de bazã /normã*numãrul sãu de ore. La salariu se poate adãuga un spor de 20%


dacã are salariu de merit sau de 30% dacã are gradaþie de merit.

Sporul de vechime se calculeazã astfel: Sub 5 ani – nimic, între 6 -15 ani se acordã5% din salariul de bazã; între 16-25 ani se acordã 10% iar peste 26 ani 15%. Impozitarease face în funcþie de totalul salariu + sporuri astfel:

Sub 5 milioane – zero lei; între 6-9 milioane -5%; între 10-15 milioane cu 12% iarpeste 16 milioane cu 20%

Salariile dintr-un an se reþin într-un registru pe câte o foaie în fiecare lunã, iar lasfârºitul anului se face un centralizator cu sumele totale primite ca salarii în fiecare lunãde fiecare persoanã. De asemenea, se calculeazã sumele totale/lunare ºi anuale pentrusporuri ºi impozite.

Pe o foaie specialã se doreºte un grafic al variaþiei salariului total pe luni.

5. Hotel de 5 steleUn hotel îºi stabileºte preþul pe o noapte pentru fiecare camerã în funcþie de poziþia

acesteia ºi confort. Astfel:

a. fiecare pat costã 200 lei; b. camerele cu vedere la mare au în plus câte 2000 leiindiferent câte paturi au. Cele cu vedere la piscinã câte 1500 lei, iar celelalte câte 800lei. c. pentru televizor se plãteºte o taxã de 500 lei, iar pentru telefon o taxã de 100 lei.d. camerele aflate la parter sau la ultimul etaj beneficiazã de o reducere de 200 lei, e.camerele tip apartament au un supliment la preþ de 3000 lei.

Aflaþi: Cât costã fiecare camerã. Cât va câºtiga în sezon hotelul dacã ar fi ocupatcomplet? Afiºaþi cu roºu camerele tip apartament, cu galben cele mai scumpe ºi cu verdecamerele cele mai ieftine.

Proiectaþi un registru care sã aibã pe prima foaie date despre hotel ºi pe a doua foaiedate despre camere.

A treia foaie poate fi un centralizator cu numãrul de camere ºi numãrul total de paturi,preþul mediu pe fiecare camerã cu 2/3/4 paturi.

Foaia a patra va afiºa ordonat dupã preþuri, la acelaºi preþ dupã poziþie, la aceeaºipoziþie dupã numãrul de paturi.

6. Campionat de fotbalCreaþi o bazã de date necesarã activitãþilor de informare dintr-un campionat de fotbal.

Veþi reþine informaþii despre echipe ºi fotbaliºti, despre meciuri ºi stadioane.

Odatã cu planificarea meciurilor, trimiteþi pentru fiecare stadion câte o scrisoare deforma urmãtoare, realizatã în Word!

Proiectaþi raportul final al campionatului.

Pentru fiecare echipã se vor afiºa meciurile jucate ºi scorul: la finalul raportului se vascrie totalul golurilor înscrise/primite de echipa respectivã, precum ºi punctajul.


Afiºaþi un raport cronologic cu situaþia meciurilor pe stadioane. Pentru fiecare stadionva fi afiºat punctajul maxim obþinutde o echipã care a jucat în deplasare,respectiv acasã. La sfârºitul raportuluise vor calcula numãrul total demeciuri jucate, numãrul total degoluri.

Desenaþi grafice al campionatuluievidenþiind: variaþia numãrului de

goluri primite ºimarcate pe echipe;ponderea în totalulgolurilor primite de oechipã a golurilorprimite în deplasarefaþã de cele primiteacasã.

CAMPIONATUL NA�IONAL DE FOTBAL Situa�ie Final�

Echipa............... pag. x data loc adversar scor

total goluri înscrise......total goluri primite....... total golaveraj......total punctaj.......

FEDERA�IA ROMÂN� DE FOTBAL AMATOR C�tre administra�ia stadionului................

Avem deosebita pl�cere s� v� anun��m planificarea meciului între ................... �i ................... la data ............ pe stadionul dumneavoastr�. Rug�m asigura�i buna desf��urare, eventual sponsorizare! azi,................ora.......... Semn�tura,

Lista meciurilor pe stadioane Stadionul:................

data echipa1 echipa2 scor

punctaj maxim (rezultat meci) în deplasare:...... punctaj maxim (rezultat meci) acas�:................. num�r de meciuri jucate pe stadion:..................

135REZOLVÃRI

Rezolvãri

Recapitulare, problema 38Pentru aflarea termenului 50 din ºirul

Fibbonacci vom scrie formulele ca în imagineºi vom copia pânã la numãrul de termeni dorit.

Recapitulare, problema 39

Celulele vor primi nume prinInsert�Name�Define saudirect în zona de afiºare aadresei celulei.

Recapitulare, problema 40Am numit celula C10 total_curent pentru a mã referi ca zonã de adresã absolutã în

formule ºi am folosit formatarea condiþionatã pentru diferenþã, scriind cu roºu acolo undevaloarea celulei este negativã.


Recapitulare, problema 41Atribuim celulei A1 un nume (Ratã_schimb) prin Insert�Name�Define. Selectãm

domeniul B4:B9 ºi-l denumim Sal_1.Formulele vor fiB11=sum(sal_1); B12=max(sal_1); B13=min(sal_1)

Recapitulare, problema 42Am preferat în locul unei formule mai complicate sã lucrãm termenii condiþiei compuse

în coloane separate. Am ales datele de test astfel încât sã putem verifica toate situaþiile.Urmãriþi cu roºu valorile care dau expresiei valoarea adevãratã.

137REZOLVÃRI

Lecþia 4, problema 1. Cãpºuni

Lecþia 4, problema 2. Cheltuieli de staþionare

Am introdus textele (plata pe orã, sosire, plecare) pe coloana A.b. Am formatat celula B1 pentru tipul Currencyc. Am formatat celulele C3 ºi C4 pentru momentele de timp doar cu ora ºi minutuld. Am introdus un comentariue. Am calculat numãrul de ore de staþionare B6=HOUR(C4-C3)f. Am calculat numãrul de minute de staþionare D6=MINUTE(C4-C3)g. Am calculat costul prin înmulþirea numãrului de ore cu preþul pe orã ºi am adunat

suma corespunzãtoare minutelor de staþionare.B8=B1*B6+ (B1/60)* D6h. Am formatat celula la tipul Currency

Lectia 4, problema 3. VacanþaLa prima vedere este necesar sã scãdem din data curentã data inceperii vacanþei. De

exemplu vom scrie =date(2005;06;15)-today()Dar în fiecare an este vacanþã ºi pentru a nu modifica de fiecare datã formula va trebui

sã indicãm faptul cã anul vacanþei este chiar anul curent. Deci am putea scrie =date(year(today());06;15)-today()

Dar oare întotdeauna vacanþa este în acelaºi an cu cel curent?Deci dacã luna curentã este mai mare decât 6 SAU luna curentã este egalã cu 6, dar

ziua este mai mare ca 15, atunci vacanþa este anul viitor!Urmãriþi calculele în foaia de calcul.


Lectia 4, problema 5. Flori

Numele celulelor formulele

exemplu

Luna_curenta =MONTH(TODAY()) 2 Ziua_curenta =DAY(TODAY()) 24 Anul_curent =YEAR(TODAY()) 2006 Anul_viitor =anul_curent+1 2007 azi =TODAY() 24.02.2006 nr_ curent =DATE(anul_curent;6;15)-azi 111 nr_viitor =DATE(anul_viitor;6;15)-azi 476

nr-zile

=iF (OR(luna_curenta<6; AND (luna_curenta=6;ziua_curenta<16)); nr_curent;nr_viitor)

111

Lectia 4, problema 6. Diferenþa dintre douã momente de timp

139REZOLVÃRI

Lectia 4, problema 7. ConsultanþãDate de intrare: numãrul de ore lucrate pentru fiecare activitate în fiecare zi a unei luni.

În foaia de calcul este marcat domeniul de intrare B2:E32.

Date de ieºire:a. numãrul total de ore lucrate în fiecare zi este calculat ca sumã în coloana F, astfel

F2=sum(B2:E2)b. procentul de ocupare cu activitatea de consultare a clienþilor în fiecare zi raportat la

total ore pe zi este calculat în coloana G astfel: G2=B2/F2Pentru a obþine procentajul formatãm coloana la tipul Percentage

c. numãrul de ore maxim lucrat intr-o zi este calculat în coloana H astfel: H2=max(B2:E2)d. lista activitãþilor care au ca numãr de ore valoarea maximã în zi s-a afiºat în coloana

I folosind funcþia pentru texte CONCATENATE (text1; text2;text3..) ºi coduri pentruactivitãþi (prima literã).i2=CONCATENATE(iF(B2=H2;”c “;” “); iF(C2=H2;”d “;” “); iF(D2=H2;”e “;” “);iF(E2=H2;”a “;” “))

e. pentru lista activitãþilor neefectuate în fiecare zi – adicã cele la care numãrul de oreeste zero am folosit coloana J ºi o formulã similarã cazului anteriorJ2=CONCATENATE(iF(B2=0;”c “;” “);iF(C2=0;”d “;” “);iF(D2=0;”e “;” “);iF(E2=0;”a “;” “))

Lecþia 6, problema 3. Vânzãtori citriceA. Presupunem urmãtoarea bazã de date. La aceste câmpuri vom face referire în

explicaþiile ce urmeazã.

B. Folosiþi Data�Validation.

C. Folosim pe rând butonul sort în ordinea inversã a câmpurilor sau ordonãm tabelul prinbuton pentru coloana nume-persoanã ºi apoi folosim fereastra Data�Sort ºi fixãm cheile înordinea datã.


D. Ordonãm tabelul dupã lunã, apoi aplicãm Data�Subtotals. Fixãm grupul la nivelullunã, funcþia este average iar câmpul unde se aplicã este valoarea vânzãrilor (val).

E. Este o situaþie de totalizare pe douã niveluri. Mai întâi obþinem tabelul ordonat dupãlocalitãþi ºi produse. Apoi aplicãm Data�Subtotals pentru localitãþi cu funcþia sum.Deschidem din nou Data�Subtotals pentru nivelul produs ºi cu aceeaºi funcþie, dar avemgrijã ca comutatorul Replace sã fie inactiv.

F. Folosim Data�Filter ºi fixãm în ordinepentru coloana produs filtru pe valoarea banane;pentru coloana localitate filtru pe valoarea iasi,iar pe coloana Luna filtru pe valoarea ianuarie.

Anularea se face tot prin Data�Filter.

G. Este o subtotalizare cu trei niveluri. Atenþiela ordonare dupã lunã, localitate ºi produs.

H. Oferim un exemplu ilustrativ al cerinþei.Desigur vom folosi Pivot table Wizard ºi vom plasa câmpul lunã la nivelul paginii, persoanãla nivelul liniilor ºi localitatea la nivelul coloanelor.

I. Folosim douã câmpuri pentru grupareadatelor pe linii: localitatea ºi produsul pe coloanevom pune luna iar la nivelul paginii – numelepersoanelor.

J.Pasul 1. Vom realiza tabelul pivot cu suma

câºtigului pe persoane ºi produse.Pasul 2. Vom selecta tabelul pivot ºi vom in-

troduce câmpul calculat cost.Astfel: din bara cu butoane selectãm meniul

PivotTable�Formula�Calculation fields. Intro-ducem numele cost ºi ca formulã valoarea dincâmpul costuri a tabelei sursã.

Pasul 3. Vom adãuga la tabelul pivot câmpulcu numele beneficiu calculat ca diferenþã între câºtiguri ºi costuri.

K.Vom pune Data�Filter�Autofilter. Pe coloana ian, pe coloana produs alegem portocale

iar pe coloana valoare selectãm Custom ºi punem expresia.

O.Pasul 1. Vom ordona dupã luni, dar nu alfabetic, ci cronologic. Pentru aceasta folosim

Data�Sort ºi, pentru primul criteriu, deschidem prin butonul Option fereastra de dialog în

141REZOLVÃRI

care alegem din lista First Key lunile anului. Dacãnu sunt definite atunci putem sã definim lista prinTools�Options�Custom List ºi pe urmã folosim listapentru a indica ordinea de sortare.

Pasul 2. Folosim Data�Subtotals.Pasul 3. În tabelul centralizator rezultat vom

ascunde detaliile.Pasul 3. Formatãm tabelul: ascundem coloanele în plus (produs, persoana etc); inserãm

linii pentru titlu; folosim altã dimensiune, stil etc.

M.Pasul 1. Afiºãm detaliile ºi înainte de totalul pentru luna ianuarie inserãm o nouã linie

cu valoarea vânzãrii de 200. Observãm recalcularea sumei.Pasul 2. Este necesarã sortarea descrescãtoare dupã valoare. De aceea vom ascunde

detaliile, ºi în tabelul centralizat, vom efectua sortarea dupã coloana valoare.

Lecþia 10, problema 1. Clubul DinamoProblema se referã la meciuri ºi clasamente, dar ºi la stadioane, locuri, preþuri. Primul

lucru pe care îl facem când analizãm o problemã este sã vedem ce evenimente se potpetrece ºi care sunt acþiunile asupra datelor.

1. Planificarea meciurilor pe care le va susþine echipa Dinamo se face din timp, încolaborare cu adversarii, ºi se fixeazã momentele ºi locurile de desfãºurare a fiecãrui meci.Acum se înregistreazã numele echipei adverse, stadionul, data, ora.

2. Modificarea datelor legate de meciurile planificate se poate face oricând, dar pânã ladesfãºurarea meciului.

3. Desfãºurarea unui meci al echipei Dinamo va determina înregistrarea numãrului degoluri primite ºi marcate de echipa cu care a jucat Dinamo. De asemenea, se poate înregistranumãrul de bilete vândute ºi suma de bani încasatã de echipã pentru acest meci.

4. Modificarea datelor legate de meciul desfãºurat deja nu va fi permisã! (deci va fi unmoment când hotãrâm blocarea accesului în vederea corectãrii acestor date).

4. La începerea evidenþei sau atunci când se planificã un meci pe un stadion nou se vaînregistra numãrul de locuri ale acestuia ºi poate preþul mediu practicat.

5. Solicitarea informaþiilor de cãtre patronul echipei se poate face oricând, deci datelede ieºire trebuie sã reflecte în orice moment situaþia doritã.

6. Accesul oricãrei alte persoane va fi numai pentru vizualizarea datelor legate de meciurilejucate ºi cele planificate! Vom ascunde de ochi indiscreþi datele legate de câºtiguri!

Cine are acces la date?1. Operatorul. El va introduce ºi va avea grijã sã actualizeze datele legate de planificarea

ºi desfãºurarea meciurilor. Nu va putea modifica scorurile unui meci sau numãrul de biletevândute la meciul desfãºurat deja. Nu va “vedea” situaþiile cerute de ºef.


2. Patronul echipei Dinamo. El va putea “vedea” toate datele în orice moment. Poate sãacorde acces operatorului la modificarea datelor legate de numãrul de locuri pe stadioane,preþurile practicate etc.

Date de intrare:numele echipei adverse (tip text), stadionul (de tip Text), data (tipul Date), ora (tipul

Time), numãrul total de locuri pentru stadion (de tip Number), numarul de bilete vândute (detip Number), numãrul de goluri marcate ºi numãrul de goluri primite (de tip Number), sumade bani primitã pentru meci (de tip Currency)

Date de ieºireTotal numãr de goluri primite ºi marcate de Dinamo, total numãr de goluri marcate ºi

primite de fiecare echipã, numãrul de bilete vândute raportat la numãrul de locuri al fiecãruistadion, câºtigul total la meciurile jucate, câºtigul estimat pentru meciurile viitoare.

Lecþia 12, problema 1. Ordonare valoriAnaliza problemei. Presupunem cã valorile sunt mici (între 1 ºi 10). Cele 3 valori vor fi

comparate intre ele ºi în funcþie de rezultat, se va afiºa o expresie cu denumirile variabilelorºi alãturi valorile în ordine.

Ce ne aºteptãm sã obþinem? Unul din ºirurile x,y,z sau x,z,y sau y,x,z etc. în funcþie derelaþiile dintre variabile.

Proiectarea foii de calculÎn B2, B3, B4 vom introduce valorile iar în D7 vom afiºa rezultatul ordonãrii.Am introdus valorile ºi am numit celulele x, y, z.Pentru cã sunt multe comparaþii, formula finalã va fi destul de stufoasã, aºa cã am

preferat sã efectuãm unele calcule parþiale. Astfel:

Pasul 1. Am transformat în ºir fiecare numãr folosind funcþia Text. ªablonul inhibã zerourilenesemnificative din numãr. La fiecare am concatenat caracterul virgulã ca separator.

Am obþinut astfel A11, A12, A13.

Pasul 2. Am dat nume acestor celule A11=text_x; A12=text_y; A13=text_z

Pasul 3. Am scris expresiile necesare fiecãrui ºir unite prin funcþia AND ºi pe liniaurmãtoare am trecut valoarea rezultat.

Ex. Pentru o situaþie de felul „dacã x<=y ºi Y<=z atunci va rezulta ºirul x, y, z” am folositcelulele A14 ºi A15.

143REZOLVÃRI

Am folosit concatenarea mesajului cu valorile text rezultate prin conversie.

Pasul 3. Am scris formula finalã în celula D7.

Testarea foii de calcul. Pentru uºurinþa testãrii am preferat generarea aleatoare a valorilorpentru x,y, z. Am folosit funcþia rand() ºi pentru a mã încadra în intervalul 1-10 am folositformula int(rand()*9)+1.

De asemenea este util sã controlãm funcþionarea formulei deci am folosit o facilitate aprogramului Excel care permite calcularea manualã a formulelor din foaia de calcul. Lafiecare apãsare a tastei F9 se genereazã un set de valori ºi se poate observa corectitudineaformulei. Acest mod de lucru se poate fixa folosind Tools�Options�Calculation�Manual.

Observaþie. Nu uitãm cã afiºarea formulelor/valorilor se face prin <ctrl>+<apostrof>

În final, am realizat formatarea foii: am ascuns liniile de calcule de la 11-25 ºi am pustitlu ºi mesaje!; am scos expresia de generare pentru celulele x, y, z pentru a permiteutilizatorilor introducerea valorilor reale!

Am refãcut modul de calcul automat pentru foaia de calcul prin Tools�Options�Calculation.


Bibliografie

1. Michael B. Karbo, Excel 2000 pentru începãtori, Egmont, 2002.

2. Mariana Panþiru, Tehnologia informaþiei ºi a comunicaþiilor, manual pentru clasa a X-a,Editura BIC ALL, Bucureºti, 2005.

3. Mariana Panþiru, Ionuþ Panþiru, Ioana Panþiru, Aplicaþii cu baze de date, Editura L&SSoft, Bucureºti, 2004.

Manual pentru clasa a 11-a - manuale.edu.ro a XI-a/Tehnologia informatiei... · Lecþia 1...

Documents

Transcript of Manual pentru clasa a 11-a - manuale.edu.ro a XI-a/Tehnologia informatiei... · Lecþia 1...