1

CAPITOLUL VII – Microsoft Excel

7.1 Descrierea programului Microsoft Excel

Aplicaţia se lansează cu Start – Microdoft Office – Microsoft Excel, dublu click pe

pictograma de pe desktop, dacă există sau scriind Excel în fereastra asistentului Cortana și

executând click pe rezultatul afișat.

După deschiderea aplicaţiei de pe ecran apar două ferestre, una în alta. Prima fereastră,

cea exterioară, este fereastra aplicaţiei, iar cea interioară este fereastra registrului de calcul ce

afișează implicit conținutul primei foi de calcul, figura 7.1.

Figura 7.1 Fereastra Microsoft Excel 2010

Registrul de calcul (Workbook) este principalul document folosit în Excel pentru stocarea

şi prelucrarea datelor. Un registru de calcul este format dintr-un număr de foi de calcul

(Worksheet-uri, implicit 3), la care utilizatorul poate adăuga şi altele. În plus, se pot crea foi

pentru diagrame, pentru secvenţe macro, pentru module Visual Basic şi fereastre de dialog cu

controale ce execută macrocomenzi.

Fiecare foaie de calcul este formată din cel mult 16384 coloane şi 1048576 rânduri.

Coloanele au etichete dispuse în partea superioară a ferestrei documentului, notate de la A la Z şi

continuând până la coloana XFD. Rândurile sunt numerotate de la 1 la 1048576 în partea din

2

stânga a ferestrei. Intersecţia rândurilor cu coloanele formează celulele (cells), care sunt unităţi

fundamentale pentru stocarea de date. Fiecare celulă este referită prin această intersecţie. Spre

exemplu adresa celulei de la intersecţia coloanei C cu linia 5 este indicată (sau este adresată,

referită) ca fiind celula C5.

Fiecare foaie de calcul are în partea inferioară o eticheta (Sheet tab), care permite

identificarea acesteia în registrul de calcul. Etichetele au nume implicite Sheet 1, Sheet 2, etc.,

dar pot fi oricând redenumite pentru a reflecta cât mai bine conţinutul de date al foii. Pentru a

modifica numărul implicit de foi de calcul (3) dintr-un registru de calcul parcurgeţi comenzile:

FileOptionsGeneral, iar în lista derulantă Include this many Sheets faceţi modificarea dorită.

Pentru a redenumi o foaie de calcul plasaţi cursorul mouse-ului deasupra etichetei şi faceţi click

dreapta; selectaţi comanda Rename din meniul contextual şi introduceţi numele dorit. Tot cu

click dreapta puteți insera și tipurile speciale de foi de calcul amintite mai sus (pentru diagrame,

de dialog, etc).

Fereastra programului conţine toate elementele unei ferestre de aplicaţii, bară de titlu – pe

care sunt afişate sigla cu numele programului şi numele registrului de calcul deschis, bară de

meniuri, ribbonul cu opțiunile meniului ales şi bara pentru formule, figura 7. 1.

Pentru a nu întâmpina dificultăți ușor de evitat, atunci când lucrați cu Microsoft Excel,

este bine să țineți cont de următoarel.e observaţii:

1. Formula începe întotdeauna cu semnul egal.

2. Celulele trebuie formatate astfel încât, datele introduse în acestea, să coincidă cu formatul

celulei.

3. Pentru a comuta pe modul editare, adică pentru a edita parţial conţinutul unei celule, se apasă

tasta F2.

4. În Excel se pot uni celule (nu se pot împărţi) prin parcurgerea următorilor paşi: se

selectează celulele, apoi se dă click dreapta, din meniul contextual se alege Format Cells,

click pe tabela Alignment, se bifează caseta de validare Merge Cells.

Într-o foaie de calcul nouă este selectată automat celula de la intersecţia coloanei A cu

linia 1, (celula A1). Selectarea celulei este semnalizată prin conturul gros, mai gros decât al

celorlalte celule, şi semnifică faptul că celula este activă (active cell). În momentul în care se

introduc caractere de la tastatură acestea apar în celula activă. Pentru a introduce date în altă

celulă, mai întâi aceasta trebuie activată, operaţie care poate fi făcută folosind fie mouse-ul fie

tastatura:

1. Cu ajutorul mouse-ului puteţi activa rapid o celulă plasând cursorul pe celula respectivă

şi executând click pe butonul din stânga al mouse-ului.

2. Pentru a selecta alte celule cu ajutorul tastaturii folosiţi tastele cu săgeţi şi/sau tastele

Page Up şi Page Down de pe tastatură sau diverse alte combinaţii de taste. Tastele care

pot fi utilizate sunt prezentate în tabelul 7.1.

Tabelul 7.1 Comenzi pentru deplasarea în interiorul unui tabel Excel

Combinaţie detaste Efect

Tab Deplasare o celulă la dreapta

Enter Deplasare o celulă în jos

Shift + Tab Deplasare o celulă la stânga

Shift +Enter Deplasare o celulă în sus

Home Deplasare în coloana A pe rândul activ

Ctrl + Home Deplasare în celula A1 a foii de calcul

3

Ctrl + End Deplasarea în ultima celulă folosită din foaia de calcul

Page Up Deplasare în sus pe ecran

Page Down Deplasare în jos pe ecran

Alt + Page Up Deplasare o lăţime de ecran la stânga

Alt +Page Down Deplasare o lăţime de ecran la dreapta

Ctrl+Page Up Deplasare la foaia de calcul anterioară

Ctrl+Page Down Deplasare la următoarea foaie de calcul

Deplasarea la o anumită celulă se poate face şi folosind caseta de denumire a domeniului

de celule sau a unei celule (name box) care se află la capătul din stânga al barei pentru formule:

se execută click pe casetă, se scrie adresa celulei la care se doreşte saltul şi apoi se apasă tasta

Enter.

Pentru deplasarea într-o anumită celulă se poate folosi şi comanda Go To….

Din meniul Home, secțiunea Editing, apăsați buutonul Find and Select apoi selectaţi

comanda Go To… sau apăsaţi tasta F5 şi se va deschide fereastra de dialog Go To.... Se scrieţi în

caseta de text Reference referinţa (adresa) celulei pe care doriţi să o activaţi apoi apăsaţi tasta

Enter sau butonul OK din fereastră. Există mai multe posibilităţi de deplasare în foaia curentă.

Le puteţi vizualiza şi testa dacă alegeți opțiunea Go to Special.

7.2. Modificarea lăţimii coloanelor

Pentru a ajusta automat coloana la lăţimea celui mai mare rand, se plasează cursorul pe

suprafaţa de separare dintre eticheta coloanei în cauză şi eticheta următoarei coloane şi se

execută dublu click.

Lăţimea unei coloane se poate modifica manual folosind mouse-ul sau comenzile din

meniu. Dacă folosiţi mouse-ul plasaţi cursorul acestuia pe marginea din dreapta a etichetei

coloanei a cărei lăţime vreţi să o modificaţi, iar în momentul în care indicatorul ia forma unei

săgeţi orizontale cu două vârfuri, trageţi săgeata spre dreapta sau spre stânga pentru a mări sau

micşora lăţimea coloanei. Pentru a modifica lăţimea mai multor coloane, mai întâi să selectaţi

respectivele coloane astfel:

- plasaţi cursorul mouse-ului pe eticheta primei coloane din seria celor a căror lăţime trebuie

modificată

- apăsaţi butonul stâng al mouse-ului; toate celulele coloanei respective se vor înnegri.

Menţineţi butonul apăsat şi trageţi cursorul deasupra etichetelor celorlalte coloane până pe

ultima coloană din serie. toate coloanele selectate vor avea celulele înnegrite.

- eliberaţi butonul mouse-ului.

Având coloanele astfel selectate plasaţi cursorul mouse-ului pe marginea dreaptă a

ultimei etichete din serie şi procedaţi în continuare ca la modificarea lăţimii unei singure

coloane. Toate coloanele selectate vor avea aceeaşi lăţime cu cea pe care o stabiliţi pentru ultima

coloană.

Dacă coloanele ale căror lăţimi doriţi să le modificaţi nu sunt adiacente, atunci selectarea

lor se face astfel:

- apăsaţi tasta Ctrl şi menţineţi-o apăsată

4

- plasaţi cursorul mouse-ului pe eticheta uneia din colonele din seria care trebuie modificată

şi faceţi un click pe butonul stâng; coloana va fi selectată

- treceţi la următoarea coloană şi repetaţi pasul anterior; dacă în seria de coloane există şi

coloane adiacente, puteţi trage cursorul mouse-ului peste etichetele lor, până la selectarea

întregului grup.

- când aţi selecta ţi ultima coloană eliberaţi tasta Ctrl.

Excel dă posibilitatea formatării atât a conţinutului celulelor cât şi a celulelor însăşi prin

aplicarea de chenare de grosimi şi culori diverse sau texturi şi culori pentru fundalul celulelor.

Chenarele îmbunătăţesc aspectul foilor de calcul oferind separaţii vizuale între zonele lor.

Chenarele îmbunătăţesc şi aspectul rapoartelor tipărite. Pentru a aplica un chenar se selectează

mai întâi domeniul care trebuie formatat. Pentru selectarea unui domeniu procedaţi astfel:

1. poziţionaţi cursorul mouse-ului pe celula din unul din colţurile extreme ale domeniului.

2. apăsaţi butonul stâng al mouse-ului şi glisaţi cursorul în diagonală spre colţul opus, până la

acoperirea întregului domeniu. Toate celulele selectate, cu excepţia celei de la care s-a pornit

selecţia au culoarea neagră, indicând în acest fel faptul că au fost selectate, figura 7.2

Figura 7.2 Domeniu de celule selectat, B2:E6

3. click dreapta, alegeți Format Cells, iar din fereastra afișată alegeţi tabela Border, figura 7.3..

5

Figura 7.3 Tabela Border a ferestrei de dialog Format Cells

4. din secţiunea Line selectaţi stilul de linie (Style) ce va fi folosit pentru chenar iar din lista

derulantă Color, culoarea dorită pentru liniile chenarului.

5. în secţiunea Border a tabelei faceţi click cu butonul stâng al mouse-ului peste liniile machetei

tabelului care doriţi să ia aspectul şi culoarea selectate la pasul anterior. Puteţi folosi şi

butoanele din stânga şi de sub fereastra de previzualizare.

6. repetaţi paşii 4 şi 5 până la trasarea tuturor liniilor dorite

7. Apăsaţi butonul OK pentru aplicarea efectivă a chenarelor şi închiderea ferestrei

8. Apăsaţi butonul OK pentru aplicarea efectivă a texturii şi închiderea ferestrei

Dacă o celulă conţine un şir de caractere numerice care, din diverse motive, nu trebuie

interpretate de Excel ca număr, respectivul şir trebuie precedat de apostrof. Puteţi selecta, de

asemenea, formatul Text pentru celula respectivă, din tabela Number a ferestrei Format Cells.

7.3 Introducerea valorilor numerice

Datele numerice sunt valori constante şi sunt formate doar din valori numerice. Într–o

celulă pot fi introduse numere întregi (cum ar fi 228), fracţii zecimale (17,258), fracţii cu numere

întregi (1 1/4) şi valori notate în sistem ştiinţific (1.23E+08).

6

Dacă numărul introdus este aliniat la stânga în celula respectivă atunci numărul respectiv

NU ESTE recunoscut ca valoare numerică şi trebuie formatată în mod corespunzător celula sau

coloana respectivă . Dacă nu veţi face formatarea, datele nerecunoscute ce vor fi folosite

eventual într-o formulă, vor determina obţinerea unor rezultate eronate. Numărul introdus într-o

celulă şi recunoscut ca atare va fi aliniat la dreapta. Dacă numărul este real și nu este

recunoscut, atunci verificați si dacă ați folosit separatorul zecimal corect.

Figura 7.4 Fereastra de dialog Format Cells, tabela Number

Dacă se introduce un număr lung într-o celulă şi în locul lui celula afişează #### sau dacă

numărul apare în sistemul de notare ştiinţifică (ex. 1.23E+08) atunci lăţimea coloanei curente

este prea mică pentru a cuprinde numărul în întregime.

Pentru a elimina acest lucru faceţi dublu click pe marginea din dreapta a etichetei coloanei

pentru a o lărgi. MS Excel oferă posibilitatea schimbării modului de afişare a numerelor în celule

folosind alt format numeric. Pentru aceasta apăsați butonul Format și alegeți opţiunea Format

Cells sau apăsaţi tastele Ctrl+1 (unu) sau folosiţi comanda Format Cells din meniul contextual.

Oricare ar fi metoda aleasă, pe ecran va apărea fereastra de dialog Format Cells.

Din secţiunea Category: a tabelei Number selectaţi tipul formatului numeric dorit,

Number, Percentage, Fraction, Scientific. Câmpul Sample oferă previzualizarea numărului din

celula activă pentru oricare din formatele alese. Caseta Decimal places: dă posibilitatea stabilirii

numărului de zecimale cu care este afişat numărul, valoarea implicită fiind 2. Pentru categoriile

7

Date, Time, Fraction, Special şi Custom, în caseta Type este afişată o listă cu formate-model.

După ce stabiliţi formatele dorite pentru diversele tipuri de date apăsaţi butonul OK; Excel va

aplica formatul ales în celulele selectate în prealabil din foaia de calcul.

În scrierea formulelor trebuie să se ţină cont de prioritatea operaţiilor. Programul Excel

respectă regulile matematice privind ordinea operaţiilor.

Formula =B4+3*A5 calculează produsul dintre constanta 3 şi conţinutul celulei A5 la

care va aduna conţinutul celulei B4. Formula =(B4+3)*A5 va aduna conţinutul celulei B4 cu

constanta 3, iar rezultatul îl va înmulţi cu conţinutul celulei A5.

Separator de domeniu şi este întotdeauna reprezentat prin „:”, iar operatorul de

uniune se mai numeşte şi separator de listă, reprezentat prin „ ,” sau „;” în funcţie de

setările sistemului de operare. În cadrul formulelor şi funcţiilor utilizate în Excel cei doi

operatori au efecte mari asupra rezultatului obţinut. Astfel formula =AVERAGE(C2:G2)

va calcula media tuturor valorilor din celulele domeniului C2:G2. Aceeaşi formulă, scrisă

cu o mică diferenţă şi anume =AVERAGE(C2;G2) va calcula media aritmetică doar a

valorilor din celulele C2 şi G2.

Un domeniu de celule se poate denumi astfel încât, în cadrul formulelor, să se poată

folosi denumirea acestuia şi nu referinţele celulelor ce formează domeniul. Pentru a de numi un

domeniu de celule în caseta Name box se introduce denumirea sub care se doreşte referirea

ulterioară a celulelor în cadrul formulelor, figura 7.5.

a) b)

Figura 7.5. Denumirea unui domeniu de celule a) şi b) utilizarea domeniului în cadrul formulelor

Utilizarea denumirii domeniilor de celule este utilă în Excel deoarece uşurează lucrul cu

formule. Astfel, pentru calculul mediei vanzărilor se poate folosi numele domeniului şi nu

referinţele celulelor B2:B6.

7.3.1 Folosirea comenzii AutoFill

O funcţie foarte utilă a programului Excel este funcţia AutoFill, cu ajutorul căreia se

poate crea rapid o foaie de calcul prin completarea automată a unui domeniu de celule cu

înregistrări în serie. Este posibilă, de exemplu, completarea unui domeniu de celule cu o serie de

date consecutive sau crearea unei serii de titluri pentru coloane.

Pentru a crea o serie de înregistrări, procedaţi după cum urmează:

- completaţi primele două celule din serie, pe coloană sau pe rând.

- selectaţi cele două celule şi apoi plasaţi cursorul mouse-ului pe mânerul de umplere a

porţiunii selectate (acel mic pătrat din colţul din dreapta jos al selecţiei).

- apăsaţi butonul stâng al mouse-ului şi cu el menţinut apăsat trageţi în jos sau la dreapta de

acest mâner: celulele care urmează vor fi completate automat. În cazul formatelor

numerice pasul seriei este considerat ca fiind diferenţa dintre conţinutul celei de a doua

8

celule şi cel al primei celule. Pentru alte formate, cum ar fi date calendaristice sau zile

ale săptămânii, funcţia lucrează diferit: este suficient să se introducă numele zilei sau a

datei calendaristice într-un format recunoscut de Excel şi apoi trăgând de mânerul

funcţiei AutoFill completarea să se facă automat, în ordine crescătoare.

Folosirea comenzii Fill - Series

Cu ajutorul butonului Fill din secțiunea Editing a meniului Home se poate completa un

domeniu de celule cu o precizie mai mare decât procedeul descris mai sus. Funcţia permite

specificarea unei valori de pornire şi a unei valori limită. Sunt posibile mai multe moduri de

accesare a acestei funcţii.

1. Completarea unui domeniu de celule cu ajutorul opțiunii Series din secțiunea Editing:

- completaţi numărul de pornire sau data calendaristică iniţială în prima celulă a

domeniului care trebuie completat

- apăsați butonul Fill și alegeți opțiunea Series: ; pe ecran va apărea fereastra de dialog

Series, figura 7.6.

- în fereastra de dialog Series specificaţi direcţia de completare, pe rânduri (Rows) sau pe

coloane (Columns), tipul de serie (Type), liniar, exponenţial sau în progresie geometrică

(Growth), de date calendaristice (Date) sau autocompletare (AutoFill). În cazul în care

doriţi crearea unei serii de date calendaristice trebuie să specificaţi unitatea de măsură

(Date Unit).

Figura 7.6 Fereastra de dialog Series

- specificaţi valoarea raţiei (Step Value) – această valoare reprezentând numărul cu care se

modifică seria de la celulă la celulă

- introduceţi valoarea limită superioară (Stop Value), această valoare reprezentând ultima

valoare din serie.

- Se apasă butonul OK.

2. Completarea cu ajutorul meniului de comenzi rapide.

- completaţi doar prima celulă şi cu butonul drept al mouse-ului apăsat pe mânerul de

umplere, trageţi peste zona de celule care trebuie completate.

- eliberaţi butonul mouse-ului. Va apare un meniu contextual din care alegeţi opţiunea

Series.., care va deschide fereastra de dialog din figura 7.5, după care continuaţi cu paşii

9

arătaţi. Din acelaşi meniu, dacă alegeţi opţiunea Fill Series, seria va fi completată cu

pasul 1. De asemenea, dacă alegeţi opţiunea Copy cells conţinutul primei celule va fi

copiat în toate celelalte.

O altă funcţie utilă pentru crearea unei serii de înregistrări este AutoComplete. Pe măsură

ce se introduc date într-o listă, Excel memorează fiecare înregistrare. Când se începe

introducerea unei noi înregistrări, care este similară cu una anterioară, pe aceeaşi coloană, Excel

va completa în mod automat înregistrarea respectivă. În acest caz puteţi accepta varianta propusă

sau, în caz contrar, continuaţi scrierea noii înregistrări.

Lista memorată este accesibilă cu ajutorul comenzii Pick From Drop - down List din

meniul contextual. Selectând comanda, sub celula activată va fi derulată o listă cu înregistrările

anterioare, din care se poate alege data dorită.

Observaţie: lista este disponibilă numai dacă celula activată se află imediat sub ultima celulă completată din coloană.

7.4 Crearea unei diagrame cu ajutorul aplicaţie Chart Wizard

Excel oferă o metodă rapidă pentru realizarea rapidă a unei diagrame pornind de la datele

din foaia de calcul.

Datele pentru diagramă trebuie să fie incluse în coloane şi linii, dar nu trebuie neapărat să

se afle în coloane şi linii adiacente. Etichetele care vor fi folosite în diagramă trebuie plasate în

primele linii şi în coloanele din extrema stângă a foii de calcul. Dacă în diagramă trebuie să apară

şi etichetele coloanelor şi liniilor atunci acestea trebuie incluse în selecţie. Celulele care nu sunt

adiacente se selectează ţinând apăsată tasta Ctrl; se selectează fiecare grup de celule până la

completarea selecţiei după care se eliberează tasta Ctrl.

După selectarea datelor, din meniul Insert se selectează unul dintre tipurile de grafice

afișate în mod standard în secțiunea Charts. Dacă doriți alegerea unui alt tip de diagrame, apăsați

lansatorul de ferestre (butonul) din colțul dreapta-jos al acestei secțiuni.

Figura 7.7. Secțiunea Charts meniul Insert.

10

Figura 7.8. Fereastra Insert Chart.

Se va afișa fereastra |Insert Chart, figura 7.8, în care puteți selecta tipul diagramei.

Acestea sunt grupate pe categorii de șabloane (Templates), după care apăsați butonul OK.

Diagrama selectată va fi afișată în foaia de calcul curentă.

În momentul în care veți da click pe diagrama creată, bara de meniuri se va completa cu

încă trei meniuri, grupate în categoria Chart Tools și anume: Design, Layout și Format , figura

7.8.

Figura 7.9. Ajustarea automată a barei de meniuri în funcție de elementul activ (în acest caz

diagrama).

Toate modificările și completările ulterioare se vor realiza fie utilizând butoanele din

aceste meniuri, fie meniul contextual al diagramei. De exemplu, dacă se dorește completarea

diagramei cu un titlu și cu semnificația mărimilor reprezentate pe abscisă și ordonată, din meniul

Layout se apasă butonul Chart title, respectiv Axis titles. Vor fi afișate casete în care se pot

introduce informațiile dorite.

11

Figura 7.10 Adăugarea titlului diagramei.

Butonul Select Data dă posibilitatea stabilirii sau modificării domeniului de date luat în

considerare pentru crearea diagramei.

Includerea unor noi serii de date se poate realiza foarte ușor şi după încheierea generării

diagramei. Se procedează astfel: click pe diagramă; ca urmare zona de date va fi încercuită într-

un chenar albastru, figura 7.11. Includerea noilor serii de date se realizează prin redimensionarea

zonei încadrată în chenar albastru (trageți de colțul dreapta jos cu corsorul plasat pe marcaj până

când includeți și celulele respective). În momentul când ați încheiat procedeul, diagrama este

actualizată automat.

Există bineînțeles și alte metode care implică utilizarea unor opțiuni. Nu sunt la fel de

rapide ca cea descrisă, însă permit realizarea mai multor setări. De exemplu, dacă alegeți, din

meniul contextual al diagramei opțiunea, Select data, se va afișa fereastra din figura 7.11. Atenție

pe ce element al diagramei dați click dreapta – meniurile contextuale diferă. Din Chart data

range puteți ajusta zona cu date, puteți schimba reprezentarea diagramei prin comutarea între

rânduri și coloane, este posibilă adăugarea sau eliminarea seriilor de date și modificarea

denumirii acestora.

12

Figura 7.11. Fereastra Select data source.

7.5. Filtrarea datelor

Deoarece volumul datelor dintr-o foaie de lucru Excel poate fi la un moment dat foarte

mare, localizarea unei anumite date devine mai greoaie. În acest caz, o soluţie care poate localiza

cu precizie datele dorite o reprezintă filtrarea datelor.

Excel permite folosirea unei filtrări simple – Autofilter care aplică o filtrare simplă

asupra datelor şi care se poate ulterior personaliza şi o metodă de filtrare avansată ce aplică

criterii de filtrare mai complexe.

În cele ce urmează se prezintă atât utilizarea unui filtru simplu personalizat cât şi a unui

filtru avansat în rezolvarea aceleiaşi probleme.

Avem la dispoziţie următoarele date structurate într-o foaie de lucru Excel după cum

urmează:

13

Figura 7.12. Datele disponibile pentru filtrare

Se cer următoarele:

- lista persoanelor cu funcţia economist care au un salariu ce depăşeşte 1700 lei;

- lista cu primii 50% dintre angajaţi sortată descendent în funcţie de salariul de bază;

Pentru a obţine lista persoanelor cu funcţia economist şi care au un salariu ce depăşeşte

500 lei se procedează astfel:

- se execută click în zona cu date;

- meniul Home→secțiunea butonul Sort&Filter→opțiunea Filter;

- din lista derulantă activată la apăsarea butonului din dreapta celulei Funcţie se selectează

articolul Economist;

- se apasă butonul din dreapta celulei Salariu de bază şi din lista derulantă se alege

articolul Number Filters. Pentru că este vorba de economiştii care au un salariu mai mare

decât 500 lei se alege condiţia Greater than (mai mare decât), iar în dreapta se specifică

valoarea 1700, figura 7.13.

Figura 7.13. Fereastra Custom AutoFilter

14

Rezultatul obţinut este prezentat în figura 7.14.

Figura7.14. Datele obţinute în urma filtrării

Pentru a rezolva a doua cerinţă se revine pentru început la forma iniţială a tabelului, click

pe butonul Sort&Filter→Clear. Se alege opţiunea Sort Descending din lista derulantă a butonului

din dreapta celulei Salariu de bază şi apoi opţiunea, din Number Filters, Top 10. În a treia listă

derulantă din fereastra Top 10 AutoFilter (figura 7.15) se selectează Percentage şi de asemenea

se selectează procentajul dorit.

Figura 7.15. Fereastra Top10 Autofilter

Pentru a crea un filtru avansat (Advanced Filter) se introduc în celule criteriile ce vor

constitui domeniul de criterii. Astfel, pentru rezolvarea primei cerinţe s-au introdus criteriile în

celulele I2, J2 specificând în celulele I1 şi J1 numele celulelor din capul de tabel.

Se selectează Data→secțiunea Sort&Filter→Advanced. În câmpul Criteria Range se vor

selecta celulele ce conţin descrierea criteriilor apoi se apasă OK, figura 7.16. Rezultatul acestei

filtrări poate fi plasat în altă foaie de calcul decât cea curentă.

Figura7.16. Fereastra Advanced Filter pentru crearea unui filtru avansat

15

7.6 Referinţe relative şi absolute

Referirea la o celulă se poate face în două moduri: relativ şi absolut. Referirea relativă

localizează în tabel celulele la care se face referirea în formule în raport cu poziţia celulei care

conţine formula. Spre exemplu, dacă formula se află în celula F8 şi ea face referire la celulele B8

şi C7, atunci programul Excel va găsi celulele prin deplasarea cu patru celule la stânga (B8) şi

respectiv trei la stânga şi una sus (C7), vezi tabelul de mai jos.

A B C D E F

7 100

8 100 =B8+C7

9

Când se copie o formulă care foloseşte referinţe relative, referinţele din formula copiată

se actualizează, după logica expusă, corespunzător poziţiei actuale a formulei. În exemplul de

mai sus, dacă formula din celula F8 se copiază în celula F9 atunci formula din celula F9 se

transformă în =B9+C8, care se referă la celulele care se află cu patru celule la stânga şi respectiv

cu trei celule la stânga şi una mai sus faţă de celula F9.

Pentru ca referinţele să nu se modifice la copierea unei formule într-o altă celulă trebuie

folosite referinţe absolute. În exemplul de mai sus dacă doriţi ca formula să utilizeze valoarea din

celula C7 și să rămână nemodificată în urma acţiunii de copiere, trebuie să plasaţi simbolul dolar

($) în faţa referinţei coloanei şi rândului: =B8+$C$7. În interiorul unei formule pot fi utilizate

deopotrivă referinţe absolute şi relative, singura grijă este de a utiliza semnul $ în desemnarea

referinţelor absolute.

Observaţie: Transformarea unei adrese absolute în adresă relativă se poate realiza şi prin apăsarea succesivă a tastei F4. Astfel se plasează cursorul în bara de formule înaintea referinţei celulei ce va fi modificată şi se apasă tasta F4 până când se obţine o referinţă de tipul dorit.

Pentru a face referire la celule aflate în alte foi de calcul, referinţele celulelor trebuie

precedate de numele foii de calcul în care se află respectivele celule, urmat de semnul exclamării

(!). Exemplul următor adună conţinutul celulei A4 din foaia de calcul 1 cu conţinutul celulei B4

din foaia de calcul 2 a aceluiaşi registru.

=SUM(Sheet1!A4;Sheet2!B4) 1

Pentru referirea celulelor care se află în aceeaşi foaie de calcul cu celula unde se

introduce formula se poate omite eticheta foii, încât formula de mai sus produce acelaşi rezultat

cu următoarea formulă:

=SUM(A4;Sheet2!B4)

dacă rezultatul trebuie să apară în foaia 1.

Referinţele la celulele din alte foi de calcul pot fi, de asemenea, relative – ca în exemplul de mai

sus sau absolute:

=SUM(A4;Sheet2! $B$4)

Când faceţi referire la celule aflate în alte registre de lucru, referinţa va fi precedată şi de

numele registrului respectiv între paranteze drepte. Toate referinţele situate în alte registre devin

referinţe ABSOLUTE. Exemplul următor calculează suma dintre conţinutul celulei B7 din foaia

curentă şi conţinutul celulei A6 din foia 1 a registrului cu numele “registru_2.xls”.

1 S-a folosit separatorul de listă pentru setare regională România. Dacă exemplul nu funcţionează, folosiţi

separatorul virgulă (,)

16

=SUM(B7;[registru_2.xls]Sheet1!$A$6)

7.7. Utilizarea funcţiilor

O funcţie este o formulă predefinită care operează cu una sau mai multe valori şi

returnează una sau mai multe valori. Toate funcţiile sunt formate din nume şi argumente separate

prin, sau ; în funcţie de setările sistemului de operare. Argumentele specifică valorile sau celulele

ce vor fi folosite de către funcţie. Numărul maxim permis de argumente ale unei funcţii este 30.

În utilizarea funcţiilor apardeseori erori, de obicei uşor de remediat, ale căror coduri şi

semnificaţii sunt prezentate în Tabelul 1.

Tabel 1. Erori frecvente în utilizarea funcţiilor.

Inserarea unei funcţii se realizează din meniul Formulas, butonul

Function, prin apăsarea butonului Insert Function din stânga barei de

formule sau tastând SHIFT+F3. Funcţiile sunt grupate pe categorii; cele

recent utilizate, funcţii financiare, statistice, logice sau definite de utilizator.

17

Figura 7.17. Inserarea unei funcţii

Observaţie: Introducerea unei funcţii se poate realiza şi prin scriere directă (în cazul utilizatorilor cu experienţă), fără a apela la metodele prezentate mai sus.

După selectarea numelui funcţiei se apasă butonul OK. Se va afişa o fereastră ce permite

introducerea argumentelor funcţiei. De exemplu, în cazul funcţiei IF, macheta funcţiei este

următoarea:

Figura 7.18. Funcţia IF

Funcţia IF este o structură decizională de tip IF-THEN-ELSE care analizează valoarea de

adevăr (TRUE sau FALSE) a condiţiei (Logical test) şi, în funcţie de rezultatul evaluării, execută

operaţiile specificate în câmpul Value_if_True (condiţia este adevărată) sau ignoră operaţiile din

18

câmpul de mai sus şi execută direct funcţia sau formula scrisă în câmpul Value_if_false (condiţia

este falsă).

De exemplu, funcţia IF de mai jos, calculează media aritmetică a notelor introduse în

domeniul E2:E7 dacă toate notele sunt de trecere (>4), în caz contrar afişează semnul „-”.

Figura 7.19. Exemplu de utilizare a funcţiei IF.

Funcţia COUNTIF numără celulele dintr-un domeniu care satisfac anumite condiţii. Sintaxa

funcţiei este următoarea:

COUNTIF(range, criteria), unde range este domeniul de celule, iar criteria este condiţia

satisfăcută de celulele din domeniu. Condiţia poate fi scrisă explicit ca argument al funcţiei, caz

în care este încadrată în ghilimele sau poate fi scrisă într-o celulă separată, argumentul criteria al

funcţiei COUNTIF fiind în acest caz referinţa celulei în care s-a introdus în prealabil condiţia.

Astfel, de exemplu, funcţia COUNTIF se poate scrie:

=COUNTIF(A1:A10, ” >1”)

sau

=COUNTIF(A1:A10, B1), dacă în celula B1 s-a introdus criteriul „>1”.

Ambele funcţii calculează numărul de valori mai mari decât 1 din domeniul A1:A10.

Funcţia SUMIF însumează valorile dintr-un domeniu de celule corespunzătoare celulelor

dintr-un alt domeniu care respectă o anumită condiţie. Sintaxa funcţiei este următoarea:

SUMIF(range, criteria, sum_range) unde range este domeniul de celule pe care va fi aplicată

condiţia criteria, iar sum_range este domeniul de celule pe care se calculează suma

corespunzătoare celulelor domeniului care îndeplinesc condiţia.

În exemplul din figura 7.20 se doreşte calculul sumei vânzărilor realizate de firmele din

Suceava. Range va fi coloana Oras_firmă, criteria va fi „Suceava”, iar sum_range va fi coloana

Vânzări (mii lei).

19

Figura 7.20. Exemplu SUMIF.

COUNT – numără celulele ce conţin valori numerice (şi date calendaristice) din lista de

argumente

Figura 7.21. Exemplu COUNT.

COUNTA – numără celulele completate cu date (de orice natură)

Figura 7.22. Exemplu COUNTA.

Funcția DMIN

Returnează voloarea minimă dintr-o coloană (câmp) al unei baze de date sau a unei liste,

în funcție de condiția specificată.

Dmin(Database, Field, Criteria)

Database – domeniul (tabelul) completat cu date;

Field – coloana (denumirea sau numărul coloanei din tabel) pentru care se calculează valoarea

minimă

Criteria – condițiile impuse celorlalte coloane (câmpuri)

20

In figura 7.23 se prezintă modul de utilizare a funcției DMIN luânf în calcul același date

ca în cazul filtrării datelor. Pentru a putea plica funcția, criteriile vor fi scrise separat.

Se va ține cont de următoarele observații:

- condițiile se scriu separat față de tabel, menționând denumirea coloanei pe care se aplică

și condiția ca atare;

- Denumirea coloanelor trebuie să fie exact cea utilizată în tabel;

- În cazul în care se testează egalități se recomandă scrierea condiției astfel: = “=10” sau

=“=Financiar”

În exemplul de mai sus s-a determinat valoarea minimă a salariului pentru angajații cu

funcția Economist din deprtamentul Financiar.

=DMIN(A1:F17,6,I1:J2)

A1:F17 este domeniul cu date

6 este numărul coloanei Salariul de bază din tabel

I1:J2 este domeniul în care s/au introdus condițiile

Funcția DMAX are aceleași argumente dar va determina valoarea maximă pentru câmpul

numeric specificat, luând în considerare și condițiile impuse.

Funcția DCOUNT

Numără celulele ce conțin valori numerice dintr-un domeniu și satisfac condițiile

specificate.

Modul de pregătire a datelor este același: domeniul completat cu date și condițiile scrise

separat, la fel ca în cazul funcțiilr DMIN și DMAX.

Funcția va calcula numărul angajaților din departamentul Financiar, cu funcția Economist

care au Salariul de bază >1500 și se scrie astfel: =DMIN(A1:F17,6, I1:K2)

21

7.7.1 Funcţii financiare

Presupunem că am contractat un împrumut pentru o investiţie şi dorim să aflăm care este

rata lunară aferentă.

Funcţia PMT (payment)

Calculează suma de plată pentru un împrumut în condiţii de anuitate (plăţi cu valoare fixă

şi dobândă fixă).

Sintaxă

PMT(rate;nper;pv;fv;type)

Rate, nper, pv, fv au semnificaţiile prezentate la funcţiile anterioare.

Fv, dacă nu este menţionat, este considerat a fi egal cu 0. Reprezintă valoarea unei investiţii sau

a unui împrumut după realizarea ultimei plăţi.

Type este un număr ce poate lua valorile 0 sau 1 şi semnifică momentul plăţii. Dacă are valoarea

0 sau nu este specificat, atunci plata este făcută la sfârşitul perioadei, iar dacă are valoarea 1

atunci plata se face la începutul perioadei.

Funcţia IPMT (Interest payment)

Returnează dobânda pe o perioadă pentru o investiţie bazată pe plăţi periodice constante

şi o rată a dobânzii constantă. Reprezintă partea din rata lunară reprezentată de dobândă.

Sintaxă

IPMT(rate;per;nper;pv;fv;type)

Rate, nper, pv, fv, şi type au semnificaţiile de la funcţia PMT, iar per semnifică perioada pentru

care se doreşte calcularea dobânzii.

Exemple

Să considerăm un împrumut de 10000 Euro pe o durată de 10 luni, cu o dobândă anuală de 10%.

IPMT(0,1/12; 1; 10; 10000) returnează valoarea -83,33 Euro, reprezentând dobânda

aferentă primei luni, cu plata la sfârşitul lunii,

IPMT(0.1/12, 6,10,10000) returnează valoarea -42,53 Euro, reprezentând dobânda

aferentă celei de a şasea luni, cu plata la sfârşitul lunii.

Semnele minus din faţa numerelor indică faptul că sumele sunt de plată şi nu de încasat.

Putem construi un desfăşurător al dobânzilor de plată, aşa cum se arată în figura 7.23

Figura 7.23 Calculul dobânzii

22

Pentru a calcula suma de plată aferentă împrumutului, pe o perioadă oarecare, dispunem

de funcţia PPMT, descrisă în continuare:

Funcţia PPMT (Principal payment)

Returnează suma de plată aferentă sumei împrumutate (principalului), pentru o perioadă

dată, în condiţii de anuitate.

Sintaxă

PPMT(rate;per;nper;pv;fv;type)

Rate, nper, per, pv, fv, şi type au semnificaţiile deja cunoscute.

Exemple

IPMT(10%/12; 1; 10; 10000) returnează valoarea -963,07 Euro, reprezentând suma de

plată aferentă sumei împrumutate de 10000 Euro, corespunzătoare primei luni, cu plata la

sfârşitul lunii,

IPMT(0.1/12; 6; 10; 10000) returnează valoarea -42.53 Euro, reprezentând suma de plată

aferentă sumei împrumutate de 10000 Euro, corespunzător celei de a şasea luni, cu plata la

sfârşitul lunii.

Legătura între cele trei funcţii, calculate pentru acelaşi credit este PMT=PPMT+IPMT,

figura 7.24.

Figura 7.24. Legătura între funcţiile PMT, PPMT şi IPMT.

Funcţia FV

Returnează valoarea unei investiţii la un moment viitor în condiţiile unei rate fixe a dobânzii.

Argumentele funcţiei au semnificaţia deja prezentată la celelalte funcţii financiare.

Sintaxă

FV(rate,nper,pmt,[pv],[type])

Exemple

1. Care va fi soldul contului de economii în cazul unei persoane care, lunar, timp de 3 ani

depune în cont 65 lei cu o dobândă anuală de 3%. Soldul iniţial este 300 lei. FV(3%/12,36,-65,-300)=2773.55 lei

2. Care este suma pe care o persoană o mai are de returnat băncii, dacă a plătit timp de 3 ani cîte 250 lei dintr-un credit în valoare de

10000 lei, dobânda fiind 10%.

FV(10%/12,3*12,-250,10000)=3036.36

Funcţia PV

23

Returnează valoarea iniţială a unui împrumut sau investiţii. Funcţia

este aproape identică cu FV, cu deosebirea ca parametrul pv este

înlocuit cu fv.

Sintaxă

PV(rate,nper,pmt,[fv],[type])

Exemple

1. Doriţi să vă încheiaţi o asigurare de viaţă pe o perioadă de 25 de

ani. Agentul vă spune că, dacă aşteptaţi ca poliţa să ajungă la

maturitate şi plătiţi lunar o primă de asigurare de 100 lei, în

condiţiile unei dobânzi de 3%, veţi încasa în final 20000 lei.

Încheiaţi asigurarea?

PV(3%/12,25*12,-100)=21087.65

Răspuns: Nu veţi încheia asigurarea deoarece vi se propune o sumă mai

mică decât cea care rezultă în urma calculelor.

2. Ce sumă ar trebui să investiţi într-o afacere care vă garantează o

dobândă anuală de 10%, pentru ca, după 25 de ani soldul contului să

fie 200000 lei?

PV(10%/12,25*12,,200000)

3. Ce sumă vă puteţi permite să împrumutaţi primtr-un credit bancar,

ştiind că puteţi achita o rată de maxim 250 lei pe lună? Dobânda este

de 9%, iar durata pe care doriţi să se desfăşoare creditul este de 7

ani.

PV(9%/12,7*12,-250,0)

Funcţia NPER

Returnează durata de timp necesară achitării unui împrumut sau

acumulării unei anumite sume în condiţiile în care se cunoaşte soldul

iniţial, rata dobânzii şi contribuţiile lunare.

Sintaxă

NPER(rate,nper,pmt,[fv],[type])

Exemple În cât timp vă veţi permite să cumpăraţi un imobil în valoare de

230000 lei, în condiţiile în care, în contul de economii cu dobândă de

3% depuneţi lunar câte 400, iar soldul iniţial este de 1000 lei.

NPER(3%/12,-400,1000,230000)=359.34

Va trebui să faceţi 359 de plăţi lunare (30 de ani) pentru a putea

economisi suma respectivă.

7.8. Prognozarea evoluţiei unor valori folosind funcţia TREND

Funcţia TREND permite (utilizând metoda regresiei simple) prognozarea evoluţiei

viitoare a valorii unei variabile pe baza datelor deja cunoscute. Funcţia TREND încadrează o

dreaptă, prin metoda celor mai mici pătrate, în punctele reprezentate în sistem XoY

corespunzătoare valorilor cunoscute ale variabilei ce se va predicţiona. Spre deosebire de

FORECAST, TREND poate fi aplicată pe domenii de celule, adică va putea predicționa

simultan, pentru mai multe intervale de timp.

TREND(known_y's,known_x's,new_x's,const)

24

Unde: known_y’s – reprezintă valorile cunoscute în evoluţia variabilei ale căror valori viitoare se

vor prognoza

known_x's – intervalul de evoluţie a variabilei

new_x's – valorile viitoare predicţionate

De exemplu se doreşte realizarea unei prognoze a evoluţiei valorilor vânzărilor unei firme

pentru lunile noiembrie, decembrie cunoscându-se valoarea vânzărilor pentru celelalte 10 luni

anterioare.

Datele vor fi structurate pe două coloane, Luna şi Vânzări, iar în celula B12 se introduce

formula TREND având ca argumente:

- valorile cunoscute ale variabilei de prognozat B2:B11 (vânzările pe primele 10 luni)

- intervalul de timp corespunzător evoluţiei acestor valori A2:A11 (primele 10 luni ale

anului)

- intervalul de timp pe care se doreşte realizarea prognozei, celulele A12, A13 (ultimele

două luni ale anului). Notă: Este foarte important ca, înainte de începerea editării funcției, să se selecteze celulele corespunzătoare lunilor pentru care se va realiza predicția, adică celulele A12, A13. După ce se încheie scrierea funcției în loc să apăsați tasta Enter, apăsați combinația de taste CTRL+SHFT+ENTER.

Rezultatele obținute pot fi observate în figura 7.22.

Figura 7.22. Vânzările pe lunile noiembrie, decembrie prognozate cu funcţiile TREND și

FORECAST.

Funcția FORECAST permite, de asemenea predicționarea valorilor viitoare ale unei

variabile. Funcția va furniza o valoare predicționată la un moment dat. Semnificația

argumentelor este asemănătoare cu cea a funcției TREND.

FORECAST(x, known_y’s, known_x’s)

25

x – valoarea viitoare predicționată

known_y’s – valorile cunoscute ale variabilei

known_x’s – intervalele pentru care se cunosc valorile variabilei

Valoarea predicționată pentru luna Decembrie (celula C12) se va obține prin copierea

formulei din celula C11 (figura 7.22).