Programe de calcul tabelar

EXCEL

CUPRINS

PROGRAME DE CALCUL TABELAR ................................................................................................................... 1 

EXCEL ................................................................................................................................................................... 1 

PE SCURT DESPRE SESIUNEA DE LUCRU EXCEL .......................................................................................................... 2 

TEMA NR. 1: INIȚIERE ÎN UTILIZAREA PROGRAMULUI DE CALCUL TABELAR EXCEL .................................................... 5 

TEMA NR. 2: OBȚINEREA, RAFINAREA ŞI SALVAREA GRAFICELOR; SIMULĂRI PE BAZĂ DE GRAFICE ......................... 10 

TEMA NR. 3: FUNCȚII STATISTICE, MATEMATICE ŞI FINANCIARE ............................................................................. 14 

TEMA NR. 4: FUNCȚII LOGICE ŞI FUNCȚII DE CĂUTARE ............................................................................................ 20 

TEMA NR. 5: TABELE DE SIMULARE ........................................................................................................................ 30 

TEMA NR. 6: BAZE DE DATE: DEFINIRE ŞI ACTUALIZARE, SORTARE, INTEROGARE, FUNCȚII STATISTICE .................... 32 

2

Pe scurt despre sesiunea de lucru Excel

Excel este un program de calcul tabelar care face parte din pachetul integrat Microsoft Office, alături de Word, PowerPoint Access etc. Dacă utilizatorul este familiarizat cu facilităţile oferite de celelalte componente ale pachetului (ne referim în special la Word şi Power Point), atunci învăţarea programului Excel este foarte simplă.

Sesiunea de lucru Excel este perioada de timp în care utilizatorul introduce date (numere, texte, formule şi funcţii), realizează selecţii, construieşte condiţii, lansează comenzi etc., iar sistemul returnează rezultate şi /sau afişează mesaje de dialog (inclusiv de eroare).

După instalarea pe calculator a pachetului Microsoft Office, deschiderea unei sesiuni de lucru Excel se

poate realiza, la alegere, accesând: - butonul START din care se selectează: Programs, MS Office, Microsoft Excel (figura 1.1); - pictograma Excel de pe Desktop, dacă a fost creată o scurtătură /shortcut pentru acest program (figura

1.2).

Figura 1.1. Meniul Start

Figura 1.2. Pictograma Microsoft Excel

3

Indiferent de modalitatea de lansare, pentru început, Excel deschide fereastra standard de lucru (figura 1.4). În plus, în ultimele versiuni ale pachetului MS Office la deschiderea sesiunii de lucru, implicit, în dreapta ecranului se activează panoul de activităţi Task Pane, organizat pe mai multe zone, care diferă în funcţie de operaţiunea în curs de derulare:

• Create an interactive Web site - crearea interactivă a unui site Web folosind, sub supravegherea unui „asistent”, programul Front Page;

• Find links in a workbook – căutarea unor legături Web, în registrul de lucru curent; • Search for... - căutarea în funcţie de anumite criterii; • Open – deschiderea unuia dintre fişierele aflate în lista ultimelor fişiere cu care s-a lucrat, sau a unui

alt fişier, căutat cu ajutorul butonului More...care deschide o fereastră Open. După cum se observă, în partea superioară a ferestrei sunt disponibile meniul rapid, bara de titlu (în care

apare, pentru sesiunea curentă, numele implicit al primului fişier Excel – Book1), bara de meniu, ribbon-ul (linia cu instrumentele meniului activ la un moment dat). Pe lângă aspectele specifice Excel, aceste bare asigură exploatarea facilităţilor prezente în toate componentele Microsoft Office. Sub aceste bare este plasată bara de formule care vizualizează, în stânga, în caseta de nume, adresa celulei curente sau numele simbolic atribuit unui domeniu (grup de celule), iar în dreapta conţinutul celulei (date, formule, funcţii). Între aceste elemente sunt disponibile şi butoanele: = pentru a construi formule de calcul, X-Cancel pentru anularea introducerii şi restaurarea vechiului conţinut al celulei curente şi √ - Enter pentru confirmarea /acceptarea datelor introduse.

Notă: Veţi observa că atunci când este lansat constructorul de funcţii, în locul butonului = apare butonul fx, iar în locul casetei de nume este afişată caseta Paste Functions care poate fi consultată în vederea alegerii, din lista subordonată ei, a funcţiei dorite de utilizator pentru celula curentă. Meniul rapid Linia meniu

Linia Titlu Linia Format

Linia de formule

Figura 1.4. Fereastra de lucru Excel

Etichete foi de calcul

Celula curentă

Foaia de calcul curentă

Bara de stare

4

Scurtă prezentare a registrului de lucru şi a foii de calcul Registrul /agenda de lucru (Book) este fişierul de tip .xlsx (la Excel 2007 fişierele aveau extensia .xls) în

care sunt salvate foile de calcul ce conţin datele şi modelele definite în Excel (inclusiv grafice). Foaia de calcul (Sheet) este o colecţie bidimensională de coloane (16.384 – identificate prin literele A,

B, ... ,AA, AB,..., XFD) şi linii (1-1.048.576 – identificate prin numere), la intersecţia cărora sunt celulele /casetele /căsuţele în care se introduc date (texte, numere, formule, funcţii).

Adrese relative, absolute şi mixte de celule Celulele se identifică prin adrese (codul de coloana & codul de linie) care pot fi:

- relative - sunt implicite şi se modifică la copiere (ex. A1, CD234, IV65536); - absolute - se obţin prin plasarea semnului $ înaintea codului de coloană şi a celui de linie (ex. $A$1, $CD$234, $IV$65536); nu se modifică la copiere; sunt recomandate atunci când în anumite calcule (formule, funcţii) se utilizează constante; - mixte - sunt o combinaţie a primelor două, la copiere modificându-se fie coloana, fie linia (ex. A$1, $CD234, IV$65536).

Notă: Rapid tipul de adresă de celulă poate fi modificat folosind tasta funcţională F4. La un moment dat este activă o singură celulă, numită celulă curentă /activă. Ea este marcată printr-un

chenar îngroşat care are în colţul dreapta-jos butonul Autofill folosit în special la operaţia de copiere (prin tehnica drag&drop).

Meniurile Excel Fiind o componentă a pachetului Microsoft Office, în Excel pe lângă meniurile generale, cunoscute din

Word şi Power Point, sunt disponibile meniuri şi comenzi specifice programelor de calcul tabelar (ex. meniurile Chart şi Data).

Tipuri de date Excel stabileşte tipul datei după natura primului caracter introdus sau după rezultatul evaluării formulei sau

funcţiei specificate. Data este de tip numeric atunci când conţine cifrele 0-9 şi primul caracter introdus este: - orice cifră 0-9; - semnul algebric + sau -; - unul din caracterele speciale: #, $, . , =. Dacă introducerea începe cu un alt caracter (o literă, spaţiul etc.), atunci data este de tip caracter. Formulele şi funcţiile au ca prefix semnul =. Notă: În memoria internă datele calendaristice sunt reprezentate ca numere.

Facilităţi de formatare Excel oferă mai multe modalităţi de formatare: simplă, predefinită şi condiţională. La formatarea simplă, în principal, rămân valabile facilităţile cunoscute din Word. Din meniul Home, cea

mai folosită este fereastra Format Cells care se accesează cu săgeata de context,. Opţiunile aceste ferestre permit: - stabilirea formatului pentru datele numerice - Number: General, Number (care permite şi stabilirea

numărului de zecimale), Scientific, Currency (pentru precizarea simbolului monetar), Percentage (folosit şi pentru a preciza numărul de zecimale într-o reprezentare procentuală), Date, Custom (pentru generarea unor formate personalizate) etc.

5

- alinierea conţinutului celulelor – Alignment (Orizontal: General, Left, Center, Right, Fill, Justify; Vertical: Top, Bottom, Center, Justify; Orientation – text scris normal sau înclinat într-un anumit unghi etc.; Wrap text – scrierea pe mai multe rânduri în aceiaşi celulă; Merge Cells – fuzionarea celulelor*; Pentru formatarea predefinită se foloseşte din meniul Home, secţiunea Styles, oţiunile Format as Table

sau Cell Styles care oferă mai multe autoformate (Simple, Classic1, List1, 3D Effects 2 etc.); Formatarea condiţională se realizează din meniul Home secţiunea Styles cu opţiunea Conditional

Formatting. Salvarea şi regăsirea registrelor de lucru

Operaţiile de salvare (se salvează registrul de lucru - fişierul .xlsx.) şi regăsire a fişierelor sunt identice cu cele din Word.

Tema nr. 1: Iniţiere în utilizarea programului de calcul tabelar EXCEL

PROBLEMA NR. 1. Creaţi, într-o foaie numită Situaţia financiară, în cadrul unui registru de lucru numit Profit.xlsx următorul tabel, cu valorile exprimate în mii RON lei:

Anul Venituri Cheltuieli fixe Cheltuieli variabile Profit/ Pierdere

2004 1350 300 950 2005 1475 250 1200 2006 1730 285 1455 2007 2500 500 1500 2008 2600 620 1850

Valorile din coloana Profit /Pierdere se calculează după formula: Profit(Pierdere) = Venituri –( Cheltuieli fixe + Cheltuieli variabile).

Evidenţiaţi prin formatare condiţională profiturile cu valoarea de cel puţin 100. PROPUNERE DE REZOLVARE

În rezolvarea acestei probleme am optat pentru configurarea tabelului în colţul dreapta-sus, al primei foi de calcul (figura 1.5).

Au fost parcurşi următorii paşi: - s-au completat, cu datele cerute, celulele din domeniul A1:E6; - în celula E2, s-a construit formula =B2-(C2+D2); în bara de formule este afişat conţinutul celulei curente E2, iar în foaia de calcul este afişat rezultatul evaluării acestei formule; - s-a selectat formula din celula E2 şi apoi s-a copiat în celulele E3:E6 (se folosesc comenzile Copy – pentru celula E2 şi Paste – pentru domeniul de celule E3:E6, din meniul Edit sau meniul rapid, sau butonul AutoFill);

Pentru scoaterea în evidenţă, în coloana E, a valorilor mai mari de 100: - s-a selectat domeniul E2:E6; - s-a activat din meniul Format comanda Conditional Formatting în fereastra căreia: s-a construit condiţia:

Cell Value - Is greater than - 100 ; s-a activat butonul Format şi s-a ales Font Style Bold Italic, Color Black; s-a activat OK;

- s-a confirmat condiţia construită activând butonul OK.

* pentru centrarea titlurilor de tabele folosiţi, din bara de instrumente Format, pictograma Merge and Center.

6

Pentru redenumirea foii de calcul din Sheet1 în Situaţia financiară: dublu click pe eticheta Sheet1 sau activarea meniului rapid (click pe butonul din dreapta al mouse-ului când indicatorul de mouse este poziţionat pe etichetă) şi selectarea opţiunii Rename urmată de scrierea noului nume.

Pentru salvarea fişierului (cu toate foile de calcul, inclusiv Situaţia financiară) se folosesc comenzi Save din meniul File sau pictograma Save din bara cu instrumente Standard. Reamintim că extensia .xlsx este implicită.

Figura 1.5. Formatare condiţională

În figura 1.5 este prezentat rezultatul acestei lucrări, fiind păstrată forţat fereastra New Formatting Rule din Conditional Formatting.

PROBLEMA NR. 2. Angajaţii unei firme producătoare de software primesc salariile în Euro (conform tabelului următor). Cursul valutar (4,3456 lei/Euro) este precizat într-o celulă în afara tabelului. Să se calculeze, în lei, drepturile salariale cuvenite. Notă: Formula trebuie introdusă o singură dată şi apoi copiată.

NUME SALARIU (Euro) SALARIU (RON) Anghelache Marius 320 Mihalache Valeriu 850 Popescu Claudia 530 Radu Miruna 260

PROPUNERE DE REZOLVARE

Rezolvarea acestei probleme este prezentată în figura 1.6. Formula pentru calculul, în lei, a drepturilor salariale, foloseşte adresa absolută a celulei E4 în care este introdus cursul de schimb Leu /Euro.

7

Figura 1.6. Calculul salariilor

Se foloseşte adresa absolută pentru ca formula introdusă pentru primul salariat (în celula C4 - formula

=B4*$E$4) să poată fi copiată şi pentru ceilalţi angajaţi. Dacă nu s-ar folosi adresa absolută ar apărea următoarele situaţii generatoare de erori: - să se introducă în formulă valoarea absolută, caz în care, în fiecare zi ar trebui reintrodusă formula, ştiut fiind că acest curs de schimb fluctuează de la o zi la alta. Folosind adresa absolută cursul se actualizează doar în celula E4, Excel reevaluând formula automat pentru toţi salariaţii; - să se introducă adresa relativă, caz în care ar fi corect rezultatul doar pentru primul salariat, restul angajaţilor având salariul în lei 0 (zero). Situaţia derivă din faptul că la copierea formulei din celula C4 în celulele C5:C7, se actualizează şi adresa relativă a celulei E4, ajungându-se la construcţiile B5*E5, B6*E6 etc. Cum în Excel celulele libere sunt evaluate la 0, rezultatul este zero pentru toţi salariaţii, mai puţin primul.

PROBLEMA NR. 3. Creaţi o agendă de lucru cu denumirea Indicatori, pe care o salvaţi într-un folder /director creat de dumneavoastră. În prima foaie de calcul (denumită Trim. I 2009) completaţi următoarele date:

Indicatori Ianuarie Februarie Martie Impozit Venituri 16% Cheltuieli Profit brut /Pierdere Impozit Profit net

Calculaţi Profitul brut sau pierderea, Valoarea impozitului şi Profitul net. Notă: Cota de impozit va fi preluată prin adresa absolută a celulei în care este precizată. Formataţi tabelul folosind o opţiune AutoFormat.

PROPUNERE DE REZOLVARE În rezolvarea acestei probleme (figura 1.7) s-au realizat următoarele operaţiuni:

8

Figura 1.7. Calculul indicatorilor financiari

- s-au introdus datele cerute în enunţ; - în celula B4 s-a construit formula =B2-B3 pentru a calcula rezultatul din luna ianuarie; - s-a copiat formula din celula B4 în domeniul C4:D4; - în celula B5 s-a calculat impozitul pentru luna ianuarie după formula =B4*$F$2; - s-a copiat formula din celula B5 în domeniul C5:D5; - în celula B6 s-a calculat profitul net pentru luna ianuarie după formula =B4-B5; s-a copiat formula din celula B6 în domeniul C6:D6.

În figura 1.8 este prezentat rezultatul unor operaţii de îmbunătăţire a aspectului tabelului: - s-au introdus 3 linii deasupra tabelului folosindu-se, din meniul Home, secţiunea Cells, opţiunea Insert Sheet Rows (Atenţie! Vă poziţionaţi pe linia deasupra căreia doriţi inserarea unui nou rând). Observaţi că după actualizare Excel renumerotează liniile şi actualizează adresele celulelor din formule (inclusiv a celor absolute); - s-a editat titlul Indicatori financiari Trim I 2009, pentru care s-a ales fontul Arial, Bold de 11 pts; - pentru centrarea titlului s-au selectat celulele din domeniul A2:D2 (coloanele tabelului) şi s-a activat pictograma Merge and Center, din bara cu instrumentele de formatare; - pentru trasarea liniilor s-a selectat tabelul şi din meniul Home, secţiunea Font, s-a ales opţiunea All Borders.

Figura 1.8. Autoformatul Simple

9

Problema nr. 4. Cunoscându-se următoarele date de intrare: CVU (Cheltuieli variabile pe unitatea de produs) = 12,5 lei, PU (Preţul unitar) = 17 lei şi CF (Cheltuielile fixe) = 1350 lei, să se stabilească punctul critic (Punctul critic = CF/ (PU-CVU)) pentru lansarea în fabricaţie a unui produs. Se va realiza o simulare pentru diverse cantităţi după modelul de mai jos:

Cantitatea Ch. Fixe

Ch. Variabile

Total Cheltuieli

Venituri Profit /Pierdere

0 50 100

.

.

600

PROPUNERE DE REZOLVARE Pentru rezolvarea acestei probleme propunem următoarea configurare a tabelului (figura 1.9).

Figura 1.9. Stabilirea Punctului critic

În zona A3:B5 s-au introdus datele iniţiale, care stau la baza tabelului obţinut în zona A7:F20. În celula D4 s-a calculat punctul critic după formula: CF/(PU-CVU) = B3 / (B4-B5). Direct de la tastatură s-a introdus doar capul de tabel (A7:F7), restul celulelor fiind încărcate cu date pe

baza adreselor din zona de intrare şi a formulelor construite. Coloana Cantitate (A8:A20) va fi completată prin generarea unei serii de numere folosind butonul

AutoFill după precizarea primelor două valori: valoarea de start şi valoarea incrementului. Coloana Ch. Fixe (B8:B20) va prelua, specificând adresa absolută a celulei din zona datelor de intrare,

valoarea de 1350; Coloana Ch. Variabile (C8:C20) se va calcula după formula: Cantitate (adresa relativă) * CVU (adresa

absolută); Coloana Total Cheltuieli (D8:D20) se va calcula adunând Ch. Fixe şi Ch. Variabile (adrese relative); Coloana Venituri (E8:E20) se va calcula după formula: Cantitate (adresa relativă) * PU (adresa

absolută);

10

Coloana Profit /Pierdere (F8:F20) se va calcula după formula Venituri -Total Cheltuieli (adrese relative).

În tabelul propriu-zis se va completa doar prima linie, după care formulele vor fi copiate cu butonul AutoFill sau comenzile Copy şi Paste. Vor fi folosite următoarele construcţii: - în celula B8 se va introduce =$B$3; - în celula C8 se va edita formula =A8*$B$5; - în celula D8 se va edita formula =B8+C8; - în celula E8 se va edita formula =A8*$B$4; - în celula F8 se va edita formula =E8-D8.

Aşa cum se observă în figura 1.9 punctul critic este 300, ceea ce înseamnă că trebuie lansată în fabricaţie o cantitate mai mare de 300 de bucăţi /kg /perechi (depinde de unitatea de măsură folosită), din produsul respectiv pentru a obţine profit.

Tema nr. 2: Obţinerea, rafinarea şi salvarea graficelor; simulări pe bază de grafice

Pentru obţinerea, rafinarea şi salvarea graficelor se folosesc: • secţiunea Chart, din meniul Insert, • meniul Design: • meniul Layout.

Se parcurg patru etape:

a. Stabilirea modelului /tipului de grafic – Chart Type: –din meniul Insert, din modelele standard: Column, Line, Pie (reprezintă grafic o singură serie de date), Bar, Area, Scatter; - din meniul Insert, din alte modele Other Charts. -din meniul Design, din lista Charts Layout. Lista completa a modelelor poate fi accesată şi cu săgeata contextuală a secţiunii Charts, din meniul Insert.

b. Stabilirea sursei datelor – meniul Design, secţiunea Data, opţiunea Select Data, fereastra Select Data Source.

c. Rafinarea graficelor – meniul Layout cu următoarele secţiuni • Labels cu oţiunile:

- Chart Titles – stabilirea titlului pentru grafic; - Axis Titles - stabilirea titlurilor pentru axe; - Legend – stabilirea poziţiei legendei şi dacă aceasta va fi sau nu afişată; - Data Labels – plasarea (sau nu) unor etichete de tip text sau valoare, corespunzătoare datelor

reprezentate; - Data Table – plasarea în zona graficului a unui tabel ce conţine datele din domeniul reprezentat.

• Axes cu opţiunile: - Axes– stabilirea modului de afişare a etichetelor pentru axe; - Gridlines – trasarea grilelor orizontale şi verticale.

d. Salvarea /stabilirea locaţiei de plasare a graficului – meniul Design, opţiunea Move Chart din secţiunea Location:

- ca obiect într-o foaie de calcul – Object in (implicit în foaia de calculcurentă);

11

- într-o foaie specială pentru grafic – New Sheet. Ulterior un grafic poate fi modificat folosind toate opţiunile prezentate anterior sau cele din meniul rapid al

graficului.

PROBLEMA NR. 1. Pe baza tabelului următor, realizaţi un grafic de tip Line care să evidenţieze evoluţia cheltuielilor pe cele 3 categorii specificate.

Anul Cheltuieli materii prime

Cheltuieli de

personal

Taxe şi impozite

Total cheltuieli

2005 500 1200 1000 2006 600 1800 1400 2007 900 2100 1500 2008 950 2200 1700

Cheltuielile totale se calculează după formula: Ch. materii prime + Ch. de personal + Taxe şi impozite.

Trasaţi o linie de Trend pentru taxe şi impozite, vizând perioada 2009-2011. PROPUNERE DE REZOLVARE

Pentru rezolvare, într-o primă etapă, după completarea tabelului cu datele din enunţ, s-a calculat totalul cheltuielilor. S-a activat pictograma AutoSum (Σ) din bara cu instrumente Standard când prompterul era poziţionat în celula E2. Implicit, Excel a stabilit domeniul A2:D2, incluzând în interval toate celulele care conţin date numerice. S-a impus selectarea doar a celulelor B2:D2, anul nefiind o valoare semnificativă. Această funcţie SUM(B2:D2) a fost copiată în celulele E3:E5.

Figura 1.11. Grafic cu linie de trend

După configurarea tabelului, pentru obţinerea graficului cerut (figura 1. 11) s-au realizat următoarele operaţii:

- s-a selectat domeniul de celule B2:D5; - s-a meniul Insert şi s-au parcurs cei patru:

1. stabilirea tipului de grafic – s-a ales tipul Line; 2. stabilirea datelor de repreyentat grafic - meniul Design, secţiunea Data, opţiunea Select Data, fereastra Select Data Source.

12

a. s-a selectat opţiunea Switch Row/ Column; b. din fereastra Select Data Source, s-a activat butonul Edit şi s-au specificat adresele de celule care conţin anii pentru a fi afişaţi ca etichete pe axa X: Horizontal (Category) Axis Labels ='Ev. cheltuieli'!$A$2:$A$7; 3.rafinarea graficului: din meniul Design s-au folosit opţiunile secţiunii Labels: - Titles pentru titlul graficului: Evoluţia cheltuielilor; - Axes Titles, Primary Vertical Axis Title pentru a afişa „mii lei” pe axa Y; - Legend cu opţiunea Bottom pentru plasarea legendei sub grafic; 4. salvarea graficului - din meniul Design, secţiunea Location, s-a ales salvarea graficului ca obiect în foaia de calcul curentă : Object in Ev. Cheltuieli.

Figura 1.12. Fereastra Add Trendline

Pentru a adăuga o linie de trend care să estimeze evoluţia în următorii trei ani a cheltuielilor cu taxe şi impozite s-a folosit din meniul Layout comanda Add Trendline. S-a optat pentru o tendinţă liniară cu numele Tendinţă taxe şi impozite. Acest nume a fost introdus în fereastra Format Trendline (figura 1.12). Tot în această fereastră se precizează şi numărul de ani pentru care va fi trasată linia de trend (Forecast, Forward, 3 periods).

PROBLEMA NR. 2. În tabelul următor să se stabilească TOTAL COST şi PRET VANZARE pentru cele trei produse specificate. Preţul de vânzare se obţine adăugând la suma totală a cheltuielilor a unui procent de 35% ce reprezintă profitul. Procentul se va introduce într-o celulă din afara tabelului şi va fi referit prin adresă absolută.

Categorii de cheltuieli PRODUS A (mii lei)

PRODUS B (mii lei)

PRODUS C (mii lei)

Materiale 2000 2500 1200

13

Amortizări 200 250 100 Salarii 1000 1500 600 TOTAL COST PRET VANZARE

Să se modifice tabelul, introducând o coloană pentru un nou produs şi o linie pentru o nouă categorie de

cheltuieli (ex. Cheltuieli de desfacere). Să se realizeze un grafic care să prezinte structura procentuală a cheltuielilor totale. Pe grafic trebuie să

fie vizibil procentul ce revine fiecărui tip de cheltuială în total cheltuieli. PROPUNERE DE REZOLVARE

Într-o primă etapă se construieşte tabelul conform enunţului şi se calculează Costul total şi Preţul de vânzare pentru primul produs. Se copie formulele pentru celelalte produse. Atenţie la calculul preţului de vânzare! Se va folosi adresa absolută a celulei în care este introdus procentul pentru profit (vezi, în figura 1.13, bara de formule în care este afişat conţinutul celulei B7 (=B6+B6*$G$2). S-a obţinut şi coloana Total în care s-au însumat cheltuielile pentru cele trei produse (s-a selectat pictograma AutoSum).

În etapa a doua s-au inserat câte o linie şi o coloană pentru a adăuga o nouă categorie de cheltuială şi, respectiv, un nou produs. S-au utilizat din meniul Insert comenzile Rows pentru Cheltuieli de desfacere şi respectiv Column pentru Produsul D. La introducerea datelor se va observa actualizarea automată a tuturor formulelor şi funcţiilor construite anterior. Atenţie la inserare! Excel adaugă o linie sau o coloană deasupra liniei curente, respectiv la stânga coloanei curente (în care este plasat cursorul).

În a treia etapă s-a obţinut graficul, după descrierea de la lucrarea precedentă. Rezultatul acestei succesiuni de operaţii este prezentat în figura 1.13.

Figura 1.13. Grafic Diagramă de structură

14

Tema nr. 3: Funcţii statistice, matematice şi financiare

În Excel există aproape două sute de funcţii grupate după tipul operaţiilor pe care le realizează. Pentru fiecare funcţie activată se deschide o fereastră în care sunt afişate: formatul funcţiei, o succintă descriere a funcţiei şi zone distincte de editare pentru argumentele din format. În plus, este afişat şi rezultatul ce se va obţine prin executarea funcţiei.

PROBLEMA NR. 1. Folosiţi funcţiile pentru calculul amortizării (SLN(), DDB() şi SYD()),

cunoscându-se următoarele date: - o întreprindere achiziţionează un utilaj la preţul de 95000 lei; - durata de viaţă este estimată la 5 ani; - valoarea reziduală este egală cu zero.

Să se calculeze amortizarea corespunzătoare fiecăruia din cei 5 ani din durata de viaţă normată, până la amortizarea completă a mijlocului fix. PROPUNERE DE REZOLVARE În rezolvarea acestei probleme am optat pentru funcţiile SLN(), DDB() şi SYD(). În figura 1.15 este afişată fereastra funcţiei SLN() ca urmare a activării generatorului de funcţii (cu pictograma fx din bara de instrumente Standard, sau cu opţiunea Insert Function din meniul Formulas).

Figura 1.15. Funcţia SLN()

Deoarece aceste funcţii vor fi construite pentru primul an de calcul a amortizării (linia 8) şi vor fi copiate pentru ceilalţi ani, celulele în care sunt datele de intrare vor fi referite prin adrese absolute. Din acelaşi motiv, în cazul celorlalte două metode, în formatul funcţiilor apare ca ultim argument anul, referit prin adresa relativă a celulei în care este precizat: - în celula B8 s-a construit funcţia =SLN($C$3;$C$5;$C$4); - în celula C8 s-a construit funcţia =DDB($C$3;$C$5;$C$4;A8); - în celula D8 s-a construit funcţia =SYD($C$3;$C$5;$C$4;A8). În figura 1.17 este afişat rezultatul aplicării acestor funcţii. S-a optat pentru formatul în care simbolul monetar este „lei”, stabilit prin fereastra Format Cells, Number, Currency, Symbol, lei din meniul Home (figura 1.16).

15

Figura 1.16. Formate pentru date numerice

Figura 1.17. Funcţii pentru calculul amortizării

PROBLEMA NR. 2 Creaţi un tabel, într-o foaie numită Calculul profitului, în registrul de lucru numit Functii Excel.xlsx.

Luna Venituri Cheltuieli Profit Ianuarie 570000 390000 Februarie 538000 405000 Martie 658000 530000 Aprilie 510000 275000 Mai 625000 398000 Iunie 568000 458000

Calculaţi: - Venitul mediu, minim şi maxim; - Media, minimul şi maximul cheltuielilor; - Media, minimul şi maximul profitului.

16

PROPUNERE DE REZOLVARE După introducerea datelor din enunţul problemei se calculează Profitul aşa cum s-a prezentat într-o

lucrare anterioară (Venituri – Cheltuieli).

Figura 1.18. Funcţii statistice

În figura 1.18 este prezentat rezultatul obţinut ca urmare a folosirii funcţiilor statistice corespunzătoare. Ele au fost construite doar pentru Venituri, pentru Cheltuieli şi Profit fiind copiate:

- în celula B12 s-a construit funcţia =AVERAGE(B5:B10); - în celula B13 s-a construit funcţia =MIN(B5:B10); - în celula B14 s-a construit funcţia =MAX(B5:B10) .

Copierea poate fi realizată, dintr-o dată pentru mai multe celule. Se selectează celulele ce conţin formulele /funcţiile construite şi apoi se lansează comenzile Copy şi Paste sau se foloseşte Autofill.

PROBLEMA NR. 3. Într-o foaie numită Calculul valorii viitoare, să se calculeze valoarea depozitului bancar peste trei ani, dacă se constituie un depozit iniţial de 2.500 lei, iar banca oferă o dobânda de 12,50% pe an. PROPUNERE DE REZOLVARE

Pentru rezolvare se foloseşte funcţia FV(). În figura 1.19 este afişată fereastra în care s-a calculat valoarea ce se va capitaliza în contul bancar, în condiţiile date de enunţul problemei. În bara de formule se observă conţinutul celulei C9 în care Excel evaluează funcţia FV(). Valoarea iniţială a depozitului este cu minus fiind o „plată”. Argumentele funcţiei pot fi editate în bara de formule, sau pot fi precizate în fereastra proprie acestei funcţii, dacă a fost activat generatorul de funcţii.

17

Figura 1.19. Funcţia FV()

PROBLEMA NR. 4. Pe o altă foaie, numită Calculul ratei lunare, să se calculeze rata lunară ce trebuie achitată pentru un credit de 55.000 lei, cu o dobânda de 21,00 % pe an, pe o perioadă de 5 ani. PROPUNERE DE REZOLVARE

Acest calcul implică utilizarea funcţiei PMT(). Cum rata dobânzii este anuală, pentru a stabili rambursarea lunară, rezultatul funcţiei se împarte la 12 (vezi bara de formule din figura 1.20).

Figura 1.20. Funcţia PMT()

PROBLEMA NR. 5. Într-o altă foaie de calcul aflaţi de câţi ani este nevoie pentru a obţine o valoare

viitoare de 50.000 lei, în următoarele condiţii: - depunerea anuală: 3.500 lei; - rata anuală a dobânzii: 11,20%.

18

PROPUNERE DE REZOLVARE Funcţia utilizată în acest caz este NPER(), în care depunerea anuală se introduce cu minus, rezultând

9.22 ani, necesari pentru a atinge suma dorită (figura 1.21).

Figura 1.21. Funcţia NPER()

PROBLEMA NR. 6. În următoarea foaie de calcul aflaţi care este plata anuală ce trebuie efectuată pentru

ca în ani, la o rată lunară a dobânzii de 19,50%, să se obţină o valoare viitoare de 75.000 lei. PROPUNERE DE REZOLVARE Problema se rezolvă folosind funcţia PMT(). Deoarece perioada şi rata dobânzii nu sunt exprimate în aceeaşi unitate de timp, prin funcţie s-a stabilit rata anuală (vezi bara de formule din figura 1.22).

Figura 1.22. Funcţia PMT()

PROBLEMA NR. 7. Un întreprinzător realizează o investiţie iniţială de 250.000 lei. El se aşteaptă să obţină de pe urma acestei investiţii, în următorii 4 ani, următoarele venituri: 35.000 lei, 37.500 lei, 25.000 lei şi

19

35000 lei. Să se calculeze rata internă de rentabilitate a investiţiei. Aproximarea rezultatului este de 0,10. Reamintim că investiţia iniţială trebuie introdusă cu semnul minus, fiind o “plată”. Modelul proiectat pentru acest exemplu este prezentat în figura 1.23.

Figura 1.23. Funcţia IRR()

PROBLEMA NR. 8 Să se calculeze dobânda care se percepe la un împrumut de 30.000 lei acordat pe o perioadă de 5 ani, dacă se doreşte ca rambursările lunare să fie de 1000 lei. PROPUNERE DE REZOLVARE

O astfel de problemă se rezolvă folosind funcţia RATE(). Rezultatul este afişat în figura 1.24. A fost necesară înmulţirea cu 12 a rezultatului returnat de funcţia RATE() pentru a afla rata anuală a dobânzii.

PROBLEMA NR. 9. O societate de asigurări oferă poliţe de asigurare care aduc deţinătorilor, în viitor, timp de 10 de ani, lunar câte 500 €. Rata dobânzii, de 8%, se consideră a fi constantă în această perioadă. Care este valoarea reală (prezentă) a unei astfel de poliţe de asigurare. Modelul de rezolvare a acestei probleme este propus în figura 1.25.

20

Figura 1.24. Funcţia RATE()

Figura 1.25. Funcţia PV()

Tema nr. 4: Funcţii logice şi funcţii de căutare

Funcţiile logice. Aceste funcţii oferă posibilitatea de a adăuga noi facilităţi de decizie foilor de calcul. Din această categorie cele mai utilizate sunt funcţiile: =IF(), =AND(), =OR(), =NOT(), =FALSE() şi =TRUE().

Funcţii de căutare şi referire. Funcţiile din această categorie asigură căutarea şi returnarea unor valori dintr-un anumit domeniu de celule. În aplicaţiile economice cele mai utilizate funcţii de acest tip sunt =LOOKUP(), =VLOOKUP() şi =HLOOKUP().

PROBLEMA NR. 1 Folosind datele din următorul tabel să se stabilească valoarea penalizărilor plătite furnizorilor în funcţie de numărul de zile de întârziere a plăţii contravalorii facturii. În coloane distincte se va stabili, pentru fiecare factură în parte: numărul de zile de întârziere la plată, dacă se va calcula sau nu penalizare şi valoarea penalizării.

21

Figura 1.26. Situaţia achitării facturilor

Notă: penalizările se vor calcula astfel: - până la 3 zile întârziere – nu se calculează penalizări; - între 3 şi 5 zile întârziere – 0,1% din valoarea facturii; - între 5 şi 7 zile întârziere – 0,2% din valoarea facturii; - peste 7 zile întârziere – 1% din valoarea facturii.

PROPUNERE DE REZOLVARE După cum se observă din tabelul din enunţ, pentru data limită de achitare a facturii şi respectiv data

achitării facturii s-a folosit formatul mm.dd.yyyy, ales din meniul Format cu comanda Cell, Number, Custom, Type (figura 1.27).

Figura 1.27. Categorii de formate pentru date numerice

(inclusiv date calendaristice)

22

În coloana F s-a calculat numărul de zile de întârziere după relaţia: Data achitării facturii - Data întocmirii facturii. Operaţia este posibilă deoarece în memoria internă datele calendaristice sunt reprezentate ca numere. În celula F5 s-a construit formula =E5 – B5. S-au folosit adrese relative pentru a putea copia formula în celulele F6-F15.

În coloana G, folosind funcţia IF() s-a afişat DA sau NU după cum se vor calcula, sau nu, penalizări în funcţie de numărul de zile de întârziere a plăţii unei facturi (figura 1.28):

- în G5 s-a construit funcţia =IF(E5-B5<=3;”NU”;”DA”); putea fi folosită şi construcţia =IF(F5<=3;”NU”;”DA”); - în G6:G15 s-a copiat funcţia din G5.

Figura 1.28. Fereastra funcţiei IF()

Argumentele funcţiei se pot edita direct în bara de formule sau se lansează generatorul de funcţii şi se foloseşte fereastra de editare specifică acestei funcţii.

În coloana H s-a folosit funcţia IF() pentru a stabili valoarea penalizărilor. Funcţia s-a construit în H5 şi s-a copiat în H6:H15:

=IF(F5<=3;0;IF(AND(F5>3;F5<=5);0,1%*D5; IF(AND(F5>5;F5<=7);0,2%*D5;1%*D5)))

sau =IF(F5<=3;0;IF(F5<=5;0,1%*D5;IF(F5<=7;0,2%*D5;1%*D5)))

23

Rezultatul final al acestei probleme este prezentat în figura 1.29.

Figura 1.29. Utilizarea funcţiei IF()

PROBLEMA NR. 2. La o societate comercială se organizează un concurs pentru ocuparea unui post de operator de calcul. La concurs se prezintă cinci candidaţi. Probele concursului sunt: analiza dosarului de înscriere (în care se depun: CV-ul, actele de studii, scrisoarea de intenţii, recomandările etc.); proba scrisă, testarea aptitudinilor şi interviul. Analiza dosarului se finalizează cu calificativul Admis sau Respins, la celelalte probe putând participa doar cei admişi. Proba scrisă, testarea aptitudinilor şi interviul se notează pe scara 1-10. Sub nota 7 candidatul este respins. Să se calculeze media fiecărui candidat şi să se afle numele candidatului câştigător. PROPUNERE DE REZOLVARE

Pentru rezolvarea acestei probleme propunem următoarea configurare a tabelului (figura 1.30).

Figura 1.30. Rezultatele concursului

24

În coloana G se va calcula Rezultatul pentru fiecare concurent, reamintind că cel cu dosarul respins nu se poate prezenta la celelalte probe. Se vor folosi funcţiile logice IF() şi AND() sau OR() şi funcţia statistică AVERAGE() pentru calculul mediei. Propunem următoarele două variante, editate pentru primul candidat. Pentru ceilalţi candidaţi se vor folosi comenzile de copiere sau butonul Autofill (figura 1.31). În celula G8 se va edita:

=IF(C8="Respins";"Respins";IF(AND(D8>=7;E8>=7;F8>=7); AVERAGE(D8:F8);"Respins"))

sau =IF(OR(C8="Respins";D8<7;E8<7;F8<7);"Respins"; AVERAGE(D8:F8))

Figura 1.31. Utilizarea funcţiei IF()

Pentru a afla care este candidatul câştigător se va cere o formatare condiţională la nivel de linie după modelul din figurile 1.32.a şi 1.32.b După cum se observă condiţia a fost construită folosindu-se funcţia Max(), pentru a extrage valoarea maximă din coloana Rezultat final. Formula pentru definirea condiţiei este :=$G8=MAX($G$8:$G$12). S-a folosit adresa mixtă deoarece sunt vizate numai valorile din coloana G, indiferent de linie.

Pentru formatare s-a utilizat scrierea Bold Italic, negru şi subliniere cu două linii (s-a activat butonul de comandă Format şi apoi s-au selectat, din fereastra Format Cells, opţiunile dorite într-o manieră asemănătoare operaţiei de formatare din Word).

25

Figura 1.32.a Formatare condiţională

Figura 1.32.b Rezultatul concursului - Formatare condiţională

PROBLEMA NR. 3. Să se obţină un stat de plată a salariilor cunoscându-se Marca salariatului, Numele şi prenumele, Vechimea în muncă şi Salariul de bază. Se vor calcula: Valoarea sporului de vechime, Totalul venitului impozabil, Valoarea impozitului, Totalul reţinerilor, Avansul şi Restul de plată.

Pentru stabilirea valorii sporului de vechime propunem următoarele tranşe de impozitare:

Tranşe de vechime (ani)

Algoritmul de stabilire a sporului de vechime

până la 5 0 5-9 5% din salariul de bază

10-14 10% din salariul de bază 15-19 20% din salariul de bază

peste 19 25% din salariul de bază

Pentru calculul impozitului se aplică un procent de 16% asupra Total Venit impozabil (Salar de bază +

Valoare spor vechime), iar Avansul reprezintă 45% din Total Venit impozabil.

26

PROPUNERE DE REZOLVARE

Pentru calculul valorii sporului de vechime s-a folosit funcţia IF(). În cadrul acesteia, pentru construirea unei condiţii compuse (venitul cuprins între limitele unui interval), s-a utilizat funcţia logică AND (figura 1.33).

Funcţia de stabilire a valorii sporului se construieşte doar pentru primul salariat (în celula E6) şi se copie pentru ceilalţi angajaţi (celulele E7:E19) prin procedeele cunoscute:

=IF(C6<5;0;IF(AND(C6>=5;C6<10);D6*5%; IF(AND(C6>=10;C6<15);D6*10%; IF(AND(C6>=15;C6<20);D6*20%;D6*25%))))

Figura 1.33 Calculul sporului de vechime (Funcţia IF())

La fel se procedează şi în celelalte coloane (figura 1.34). Se stabileşte formula sau funcţia pentru primul salariat (de pe linia 6) şi se copie pentru ceilalţi salariaţi (liniile 7-19).

Total Venit impozabil (coloana F) - în F6 se editează formula =D6 + E6 şi, apoi, se copie în F7:F19; Impozit (coloana G) - în G6 se scrie formula =F6*16% care, apoi, se copie în G7:G19; Total Retineri (coloana I) – în H6 se editează funcţia =SUM(G6:H6) care se copie în G7:G19; Avans (coloana J) – în J6 se editează formula =D6*45% şi se copie în J7:J19; Rest de plată (coloana K) – în K6 se scrie formula =F6-(I6+J6), care se copie în K7:K19.

27

Figura 1.34 Stat de plată a salariilor

PROBLEMA NR. 4. Într-un stat de plată a salariilor (figura 1.34) să se identifice numele şi restul de

plată pentru angajatul care are marca* cu numărul 244. PROPUNERE DE REZOLVARE

Pentru rezolvarea acestei probleme se foloseşte funcţia VLOOKUP(). Se observă în figura 1.34 că tabelul a fost sortat crescător după prima coloană, în funcţie de care se va realiza căutarea (folosind pictograma de sortare A-Z, din bara cu instrumente Standard, sau comanda Sort, din meniul Data).

Funcţia este =VLOOKUP(B22;A6:H19;2) pentru a căuta numele salariatului cu marca 244 şi respectiv =VLOOKUP(B22;A6:H19;8) pentru a extrage restul de plată.

Dacă datele din coloana Marca nu ar fi fost sortate era necesară folosirea argumentului False, în formatul funcţiei: =VLOOKUP(B22;A6:H19;2;False) pentru Numele şi prenumele şi =VLOOKUP(B22;A6:H19;8;False) pentru Restul de plată a salariatului cu marca specificată.

Mai întâi se introduce numărul de marcă dorit (în celula B22). Această valoare se caută, pe verticală, în prima coloană, din domeniul A6:K19. La găsirea mărcii 244, Excel continuă căutarea pe linia respectivă şi se „opreşte” în coloana a doua, în care este Numele şi prenumele, sau în coloana a unsprezecea, în care este Restul de plată (figura 1.35).

* Marca este un cod unic de identificare al salariaţilor /angajaţilor

28

Figura 1.35. Funcţia VLOOKUP()

Ori de câte ori se va introduce în celula B22 un alt număr de marcă, în celulele C22 şi respectiv D22 vor fi afişate Numele şi prenumele şi Restul de plată corespunzătoare salariatului cu marca specificată (figura 1.36), ceea ce înseamnă că funcţiile VLOOKUP() nu trebuie rescrise.

Figura 1.36. Funcţia VLOOKUP()

PROBLEMA NR. 5. Folosind tabelul din figura 1.37 să se identifice rapid care este volumul vânzărilor realizat de raionul Jucării în anul 2008. PROPUNERE DE REZOLVARE

Pentru a afla care este volumul vânzărilor realizat de oricare dintre raioanele magazinului, în oricare dintre anii incluşi în tabel se va folosi funcţia HLOOKUP() care cere sortarea, de la stânga la dreapta, după ani. Pentru o astfel de sortare se va folosi din meniul Data, comanda Sort cu opţiunea Sort left to right (figura 1.38).

29

Figura 1.37. Situaţia vânzărilor pe raioane

Figura 1.38. Funcţia HLOOKUP()

În bara de formule se observă structura funcţiei, pentru aflarea vânzărilor la raionul Jucării în anul 2004 (celula A20):

=HLOOKUP(A20;A6:F16;7). După cum se observă în figura 1.38, se poate configura câte o zonă distinctă pentru fiecare raion în parte

şi, atunci când se doreşte aflarea volumului vânzărilor pentru un anumit raion, se introduce, în celula de căutare, doar anul respectiv.

De fapt, Excel citeşte conţinutul celulei A20. Găseşte 2008 şi caută, pe orizontală, pe prima linie din tabel (domeniul A6:F16), această valoare. După poziţionarea pe coloana corespunzătoare anului 2008 Excel afişează conţinutul celulei care se află la intersecţia acestei coloane cu linia pe care se află raionul dorit (pentru raionul Jucării linia 7 din domeniul selectat). Ori de câte ori în celula A20 se va introduce un alt an (deci o altă valoare de căutare), în celula B20 se va reevalua funcţia HLOOKUP() şi se va afişa informaţia dorită (volumul vânzărilor pentru anul respectiv).

30

Tema nr. 5: Tabele de simulare

Simularea presupune utilizarea unei funcţii sau formule şi definirea unei zone în care să fie plasate rezultatele obţinute. Pe baza acestor rezultate se pot realiza analize, previziuni şi prognoze care să permită stabilirea unei anumite strategii pentru “conduita” viitoare a utilizatorului ce apelează la facilităţile acestui tip de programe. Tabela obţinută prin simulare nu poate fi modificată, datele din celule fiind stabilite pe baza altor date considerate “date de intrare”.

După definirea datelor de intrare (argumentele funcţiei sau ale formulei) se configurează zona în care Excel va depune rezultatele simulării. În cazul simulării cu o singură variabilă această zonă va fi formată dintr-o singură coloană şi mai multe linii sau o singură linie şi mai multe coloane, după cum valorile variabilei sunt plasate pe coloană sau pe linie. În cazul simulării cu două variabile zona va avea mai multe coloane şi mai multe linii, de asemenea în funcţie de numărul valorilor celor două variabile. Obligatoriu variabila /variabilele trebuie să fie argument /argumente ale formulei /funcţiei care stă la baza simulării. În ambele situaţii se utilizează meniul Data, secţiunea Data Tools, rubrica What-If Analysis, opţiunea Data Table care deschide fereastra comanda Data Table unde, în zonele Row input cell şi /sau Column input cell utilizatorul specifică celula /celulele în care se află variabila /variabilele în funcţie de care Excel va realiza simularea. Problemă. Un client, contractează la BRD- Groupe Societe Generale un credit de 90.000 €, cu o rată anuală

a dobânzii de 9%, pe o perioadă de 20 de ani. Să se simuleze valoarea plăţii lunare în următoarele două situaţii: - se modifică o singură variabilă, rata dobânzii, în intervalul 5%-11%; - se modifică două variabile: rata dobânzii, în intervalul 5%-11%, şi numărul de ani pentru rambursare, în intervalul 15-25. PROPUNERE DE REZOLVARE

Simularea cu o variabilă. La baza acestei simulări stă funcţia financiară PMT() care este utilizată în celula C8. Se calculează plată lunară (rezultatul returnat de funcţia PMT() se împarte la 12):

=PMT(C6;C5;C4)/12 Practic, simularea presupune următorii paşi:

- definirea pe coloană sau pe linie a valorilor ipotetice pe care le ia rata anuală a dobânzii (valori de la 5.00% la 11.00%, cu raţia de 0.50%, în domeniul de celule B12-B24);

- în celula C11 (aşadar în coloana următoare – C, pe linia imediat de deasupra – 11), se apelează celula C8 (în cazul acesta se spune că celula C11 este dependentă de celula C8); în C11 se editează =C8;

- se selectează zona de generare a rezultatului, acoperind coloanele B şi C şi liniile ce conţin valorile variabilei (B11:C24) Atenţie! Celula B11 - din colţul stânga-sus, este liberă (figura 1.39);

- se activează comanda Data Table din meniul Data secţiunea Data Tools şi se precizează, în zona Column input cell celula $C$6, cea care în zona datelor de intrare conţine rata dobânzii;

- se acţionează butonul de comandă OK.

31

Figura 1.39 Simulare cu o variabilă.

Simularea cu două variabile. Modificând două variabile, etapele parcurse sunt (figura 1.41.a):

- se generează, pe coloană (în zona A12:A24) o serie de potenţiale valori ale ratei dobânzii (între 5.00% şi 11.00%), iar pe linie (în zona C11:M11) o serie de potenţiale valori pentru numărul de ani de rambursare a creditului (între 15 şi 25);

- în celula B11 (situată în colţul stânga-sus a zonei de simulare cu două variabile) se reeditează formula, funcţia sau conţinutul celulei folosită la simulare (=C8);

- se selectează zona de simulare (B11:M24); - din meniul Data se activează comanda Data Table secţiunea Data Tools; - în fereastra Data Table se precizează în zona Row input cell celula din zona datelor de intrare care conţine

variabila ce se modifică pe linie (numărul de ani - $C$5), iar în zona Column input cell celula care conţine variabila ce se modifică pe coloană (rata anuală a dobânzii - $C$6);

- se activează, din fereastra Data Table, butonul de comandă OK.

32

Figura 1.41.a. Simulare cu 2 variabile

Rezultatul simulării cu două variabile este prezentat în figura 1.41.b.

Figura 1.41.b. Rezultatul simulării cu 2 variabile

Dacă am fi optat şi pentru prezentarea formulelor (combinaţia CTRL + ` ), s-ar fi observat că fiecare celulă conţine funcţia Table ale cărei argumente sunt celule în care au fost introduse valorile potenţiale ale variabilelor care se modifică.

Tema nr. 6: Baze de date: definire şi actualizare, sortare, interogare, funcţii statistice

Excel permite lucrul cu liste. Prin listă se înţelege o colecţie de elemente denumite înregistrări (records),

fiecare înregistrare fiind structurată în unităţi de informaţie denumite câmpuri (fields). Structurarea pe linii şi coloane, facilitează sortarea şi căutarea informaţiilor. Această organizare se aseamănă cu cea folosită în sistemele de gestiune a bazelor de date, motiv pentru care listele mai complexe şi cu un volum mare de date sunt denumite baze de date.

33

La organizarea unei liste trebuie avute în vedere câteva reguli dintre care amintim: - fiecare înregistrare este memorată pe o singură linie şi fiecare câmp este definit ca o singură coloană; - listele au un antet (header row) constituit din prima linie şi în care se indică numele câmpurilor listei; - numele de câmpuri pot fi formate din până la 255 de caractere, dar este recomandată folosirea unor nume

scurte prin care să se sugereze conţinutul informaţional al datelor încărcate; - numele de câmpuri trebuie să fie diferite atunci când urmează să fie realizată filtrarea datelor; - într-o listă pot fi organizate orice tip de date: text, cifre, date calendaristice, imagini etc.; - datele dintr-o coloană trebuie să fie de acelaşi tip, ele fiind valori pentru un singur câmp; - între antetul listei şi articolele listei nu trebuie lăsate linii libere; - pentru fiecare articol trebuie să existe cel puţin un câmp încărcat cu date.

Organizarea şi gestionarea datelor dintr-o listă sau bază de date se realizează cu ajutorul opţiunilor meniului Data.

Încărcarea datelor în listă Încărcarea unei liste cu date poate fi realizată direct de la tastatură sau folosind facilităţi Excel:

• Get Externat Data (From Access, from Web, From Text etc.); • instrumentele AutoComplete şi PickList cu care se completează celulele din coloana curentă pe baza

valorilor de intrare anterioare.

Sortarea înregistrărilor Pentru schimbarea ordinii înregistrărilor se foloseşte comanda Sort&Filter din meniul Data sau meniul

Home. Se pot stabili multiple criterii /chei de sortare, alese dintre câmpurile /coloanele bazei de date. A doua cheie, a treia cheie etc. sunt operaţionale numai dacă pentru cheiile precedente există cel puţin două înregistrări care să aibă aceeaşi valoare. Fiecare cheie are două opţiuni Ascending şi Descending, după cum sortarea se cere a fi crescătoare sau descrescătoare.

Rapid, pentru sortarea datelor după un singur criteriu (coloana curentă) se pot folosi pictograma Sort&Filter (opţiunile Sort Ascending (A-Z) sau Sort Descending (Z-A)) din meniul Data.

Regăsirea şi filtrarea bazelor de date Regăsirea datelor încărcate în liste sau baze de date se poate realiza folosind Sort&Filter din meniul

Data sau meniul Home. Filtrarea permite afişarea, în acelaşi timp, a tuturor înregistrărilor care respectă criteriul sau criteriile

stabilite. Criteriile sunt instrucţiuni /condiţii transmise de către utilizator programului Excel pentru o anumită operaţiune de căutare în listă. La rândul lor filtrele pot fi obţinute cu ajutorul comenzilor AutoFilter şi Advanced Filter.

Comanda AutoFilter oferă un acces rapid la informaţiile care răspund condiţiei de filtrare afişând aceste date în foaia de calcul. La lansarea acestei comenzi, în dreapta fiecărui nume de câmp este plasat un buton-săgeată care permite selectarea sau construirea criteriului /criteriilor de selecţie.

În lista derulantă deschisă sunt disponibile opţiuni generale sau specifice fiecărui câmp în parte:

34

Figura 42. Opţiuni AutoFilter

All este implicită, în listă fiind prezente toate înregistrările. În plus, după o filtrare anterioară reface lista

iniţială. Top 10 poate fi folosită numai în coloanele ce conţin numere sau date calendaristice şi permite extragerea

celor mai mari sau mai mici valori din listă pe baza unui număr sau a unui procent ales de utilizator. Custom permite crearea explicită de către utilizator a unor expresii de filtrare cu ajutorul unor operatori

relaţionali (>, <, >=, <= etc.). Pentru expresii compuse se pot folosi butoanele radio AND sau OR. Prin AutoFilter se pot construi condiţii oricât de complexe, singurul aspect negativ al opţiunii putându-l

constitui suprascrierea rezultatelor filtrului peste datele de intrare. Aceasta nu înseamnă că celelalte articole din baza de date au fost şterse fizic. Ele au fost doar “ascunse”, oricând, cu opţiunea Show All din comanda Filter putând fi reafişate. Această operaţie este implicită, atunci când se dezactivează comanda AutoFilter.

Comanda Advanced Filter este folosită atunci când se doreşte obţinerea unei liste distincte cu înregistrările care îndeplinesc condiţia /condiţiile de filtrare construite de utilizator. Pentru aceasta este necesară definirea în foaia de calcul curentă a trei zone distincte: zona datelor de intrare; zona criteriilor de filtrare; zona datelor de ieşire.

Zona datelor de intrare reprezintă domeniul înregistrărilor din baza de date ce va fi supus restricţiilor de filtrare. Este obligatorie includerea în această zonă a numelor de câmpuri şi a coloanelor folosite la construirea criteriilor de filtrare.

Zona criteriilor de filtrare este definită pentru condiţiile /restricţiile de filtrare şi trebuie să cuprindă pe prima sa linie numele câmpului sau câmpurilor cu care se construiesc aceste restricţii. Este necesar de făcut precizarea că zona criteriilor nu trebuie să conţină linii libere şi că numele de câmp /câmpuri trebuie să fie ortografiate exact ca şi numele câmpurilor din baza de date (zona datelor de intrare). Pentru aceasta este recomandată folosirea comenzilor de editare Copy şi Paste. Criteriile pot fi simple sau multiple. În cazul celor multiple ele pot fi plasate pe aceiaşi linie sau pe linii diferite.

Zona datelor de ieşire este declarată pentru copierea într-o zonă distinctă a înregistrărilor care respectă criteriul /criteriile de filtrare.

La selectarea comenzi Advanced Filter se deschide o fereastră cu acelaşi nume. Rezultatul filtrării poate fi dirijat în zona datelor de intrare (dacă se selectează butonul radio Filter the list, in-place ) sau într-o locaţie stabilită de utilizator, prin definirea unei zone a datelor de ieşire (dacă se selectează butonul radio Copy to another location). Dacă înaintea lansării comenzii Advanced Filter nu au fost stabilite zonele de lucru pentru realizarea filtrului, se pot folosi zonele de editare List range (pentru zona datelor de intrare), Criteria range (pentru zona criteriilor de filtrare) şi Copy to (pentru zona datelor de ieşire). Zona Copy to poate fi utilizată numai după selectarea butonului radio Copy to another location. În plus, această zonă poate fi folosită şi pentru copierea rezultatelor unui filtru în alte foi sau registre de lucru. În acest caz, trebuie specificat numele foii, a registrului de lucru şi calea de acces la acesta.

35

Activarea casetei de validare Unique records only are ca efect extragerea, în zona datelor de ieşire, dintre duplicatele zonei datelor de intrare, doar a primelor înregistrări.

Funcţiile pentru lucrul cu baze de date Aceste funcţii au ca prefix litera D şi sunt construite, în principal, pe funcţiile statistice - DAVERAGE(),

DCOUNT(), DMAX(), DMIN() şi o serie de funcţii matematice - DSUM(), DPRODUCT(), realizând respectivele calcule cu valori ale câmpurilor încărcate în articolele bazelor de date. Aceste funcţii au în structura lor trei argumente:

- grupul /zona de intrare (baza de date /domeniul de celule selectat din baza de date); - câmpul asupra căruia se vor aplica funcţiile (câmpul este precizat prin numele scris între ghilimele

sau numărul de ordine al coloanei pe care se află); - zona criteriului în care se specifică restricţiile de selecţie.

PROBLEMA NR. 1. Să se obţină şi apoi să se actualizeze o bază de date care să conţină informaţii privind salariaţii unei societăţi (Marcă, Nume şi prenume, Cod secţie, Denumire secţie, Funcţie, Salariu de bază). PROPUNERE DE REZOLVARE

După introducerea antetului (Marca, Nume şi prenume, Secţia, Denumire secţie, Funcţie, Salariu de bază), încărcarea datelor poate fi realizată direct în celulele tabelului

În baza de date Salariaţi sunt încărcate 18 înregistrări.

Problema nr. 2. Să se sorteze articolele din baza de date Salariaţi după Denumire secţie, Funcţie şi Nume

şi prenume. PROPUNERE DE REZOLVARE

Pentru a obţine ordinea cerută s-a lansat comanda Sort din meniul Data, deschizându-se fereastra din figura 1.43.

Figura 1.43. Comanda Sort din meniul Data

Pentru prima cheie s-a stabilit câmpul Denumire secţie (în zona Sort by), pentru a doua cheie s-a precizat

câmpul Funcţie (în zona Then by), ultima cheie fiind Nume şi prenume (în cea de a doua zona Then by). Pentru toate cele trei chei s-a ales opţiuniea A to Z (Ascending). De altfel, această opţiune este implicită. Rezultatul acestei operaţii de sortare este prezentat în figura 1.44.

36

Figura 1.44. Rezultatul sortării

PROBLEMA NR. 3. Să se obţină prin autofiltrare: - lista economiştilor cu salariul peste 200 lei; - lista primilor 50% de angajaţi, în ordinea crescătoare a salariilor. PROPUNERE DE REZOLVARE

Pentru a obţine astfel de liste s-a lansat, din meniul Data, comanda Filter, Autofilter (Atenţie! cursorul trebuie poziţionat în interiorul bazei de date /listei), după care: - pentru câmpul Funcţie s-a selectat Economist; - pentru câmpul Salariu de bază s-a selectat opţiunea Custom şi s-a construit condiţia: Salariu de baza is

greater than 1500; - s-a activat OK (figura 1.45).

37

Figura 1.45. Comanda AutoFilter

Pentru afişarea a 50% din angajaţi, în ordinea crescătoare a salariilor (angajaţii cu salariile cele mai

mari), s-a construit condiţia în caseta de dialog Top 10 AutoFilter. Pentru o mai bună înţelegere a operaţiunii, peste rezultatul filtrului s-a plasat forţat, caseta de dialog Top 10 (figura 1.46).

Figura 1.46. Opţiunea Top 10 din comanda AutoFilter

PROBLEMA NR. 4. Să se obţină prin filtrare avansată lista economiştilor care au salariul peste 1500 lei.

PROPUNERE DE REZOLVARE În figura 1.47 este prezentat un filtru avansat obţinut prin selectarea din baza de date Salariaţi, a

economiştilor care au un salariu mai mare de 1500 lei. Domeniul celor trei zone cerute de această interogare este vizibil în fereastra Advanced Filter:

- zona datelor de intrare A4:F21;

38

- zona criteriilor - H5:I6; - zona de ieşire – D24:G24.

Atenţie! Pentru zona de ieşire se stabileşte o singură linie, cea în care sunt precizate numele de câmpuri/coloane ce vor fi extrase din zona datelor de ieşire.

Figura 1.47. Filtrarea avansată

PROBLEMA NR. 5 Din statul de plată prezentat în figura 1.44 să se afişeze următoarele informaţii: - numărul economiştilor; - salariul maxim la categoria Economist; - salariul minim, în secţia 3, la categoria Inginer; - numărul economiştilor cu salariul cuprins între 1.500 şi 1.750 lei.

PROPUNERE DE REZOLVARE În această lucrare sunt folosite funcţiile statistice pentru baze de date, apelate din categoria de funcţii

Database (figura 1.48).

Figura 1.48. Funcţii statistice pentru baze de date

39

În celula D24 s-a utilizat funcţia DCOUNTA() pentru a afla numărul economiştilor: =DCOUNTA(A4:F22;5;H4:H5). Cele trei argumente ale funcţiei sunt stabilite astfel:

- Database: A4:F22; - Field: 5; - Criteria: H4:H5.

S-a utilizat funcţia DCOUNTA() care contorizează numai celulele care conţin date (de ex. sunt ignoraţi salariaţii pentru care nu se cunoaşte funcţia). Argumentul Field precizează, prin 5, numărul coloanei Funcţie (a cincea coloană din tabel).

În celula D26 s-a utilizat funcţia DMAX() pentru a stabili salariul maxim la economişti: =DCOUNTA(A4:F22;6;H4:H5).

În celula D28 s-a utilizat funcţia DMIN(), cu criteriu compus, pentru a stabili salariul minim la inginerii din secţia 3:

=DCOUNTA(A4:F22;6;H7:I8). În celula D30 s-a utilizat funcţia DCOUNTA(), cu criteriu compus, pentru a stabili numărul

economiştilor cu salariul cuprins între 1.500 lei şi 1.750 lei: =DCOUNTA(A4:F22;5;H11:K12).

În cazul criteriilor /condiţiilor compuse restricţiile propriu-zise se plasează pe aceeaşi linie, operatorul logic implicit fiind AND. În cazul exemplului luat vor fi extraşi în zona de ieşire, doar salariaţii care îndeplinesc toate restricţiile din criteriu.

PROBLEMĂ GENERALĂ Să se creeze un registru de lucru în care să se organizeze, în foi distincte datele privind salariaţii unei

societăţi comerciale şi orele lucrate de aceştia în luna ianuarie 2009. Foaia de calcul cu lista salariaţilor să fie denumită Salariaţi (figura 1.49), iar cea care ţine evidenţa timpului lucrat Pontaj (figura 1.50).

Figura 1.49. Lista salariaţilor

40

Să se obţină, în acelaşi registru de lucru, într-o altă foaie de calcul, numită Situatia veniturilor_Ian_2009, situaţia veniturilor realizate în luna ianuarie 2009 (figura 1.51).

Figura 1.50. Pontaj - Ianuarie 2009

Pentru a realiza combinarea informaţiilor din primele două foi de calcul se va utiliza referirea celulelor de tip Sheet!adresă celulă. Propunem ca valoarea după care să se caute în foile Salariaţi şi Pontaj să fie Marca. Pentru o căutare exactă cele două foi trebuie sortate crescător după această coloană, sau trebuie folosit argumentul False în formatul comenzii Vlookup(). Practic, în foaia Stat de plată se vor utiliza următoarele construcţii, doar pe prima linie de valori, după care se vor copia pentru toţi salariaţii: - în celula A6 =Salariati!A5 pentru a prelua din foaia de calcul Salariaţi marca primului salariat; - în celula B6 =VLOOKUP(A6;Salariati!A5:B22;2;FALSE) pentru a prelua din foaia Salariaţi, numele şi

prenumele primului salariat; - în celula C6 =VLOOKUP(A6;Salariati!A5:C22;5; FALSE) pentru a prelua din foaia Salariaţi, funcţia primului

salariat; - în celula D6 =VLOOKUP(A6;Salariati!A5:E22;6; FALSE) pentru a prelua din foaia Salariaţi, tariful orar al

primului salariat; - în celula E6 =VLOOKUP(A6;Pontaj!A5:B22;2; FALSE) pentru a prelua din foaia Pontaj, numărul de ore lucrate

de primul salariat; - în celula F6 =D6*E6 pentru a calcula venitul realizat de primul salariat.

41

Figura 1.51. Situaţia veniturilor realizate - ianuarie 2009

După obţinerea statului de plată realizaţi următoarele operaţii: - sortaţi lista după Nume şi Prenume sau după orice alt criteriu; - obţineţi lista salariaţilor care au lucrat mai mult de 130 de ore şi au obţinut un venit mai mare de 900 lei; - extrageţi orele lucrate şi venitul realizat de Ionescu Marius; - stabiliţi numărul salariaţilor care au lucrat mai mult de 130 de ore şi au obţinut un venit mai mare de 1000; - realizaţi o simulare, estimând creşteri pentru tarifului orar şi numărul de ore lucrate; - reprezentaţi grafic ponderea veniturilor realizate de salariaţi în total venituri.