1

ComunicareValentin Clocotici

2

Cursul nr. 10Excel: Analiza datelor

3

Outline• Atunci când structura datelor este complexă

(detalii plus informaţii de sinteză de genul subtotaluri), devine necesar un instrument de simplificare a vizualizării, prin care să se poată afişa datele la gradul de generalitate dorit.

• Se separă astfel informaţiile esenţiale de detalii care perturbă receptarea informaţiilor.

• Procedura oferită de Excel este cea de outline şi constă în dotarea foii de calcul cu simboluri grafice active prin acţionarea cărora se ascund detaliile de un anumit nivel.

4

Outline• Termenul Outline poate fi tradus prin trasare,

conturare, schiţare.• Există două moduri de a defini o structură

outline peste un domeniu de date:– automat = datele trebuie să fie structurate sistematic

prin includerea unor linii/coloane de sinteză;– manual = structurarea este efectuată de utilizator,

potrivit cerinţelor de vizualizare.• Simbolurile de outline apar doar dacă este

selectată opţiunea Outline simbols din Tools – Options – View.

5

Outline automat• Datele din foaia de calcul sunt structurate prin

includerea unor linii/coloane cu formule totalizatoare identice care:– se referă la celule dispuse într-un acelaşi mod (în

aceeaşi direcţie, în acelaşi număr etc.) şi– sunt plasate în aceeaşi parte faţă de detalii

(argumente) – toate coloanele de sinteză la stânga sau la dreapta, toate liniile de sinteză sus sau jos,

• Se poate realiza outline-ul automat prin– selectarea domeniului structurat– comanda Data – Group and Outline - Auto Outline.

6

Outline manual• Pentru a realiza un outline prin operare directă:

– se selectează liniile/coloanele cu rol de detalii– se dă comanda Data – Group and Outline - Group.– se repetă paşii anteriori pentru fiecare dintre grupările

dorite.• Este recomandabil ca gruparea să se efectueze

ierarhic de la nivelul cel mai de jos către cel superior. La fiecare nivel se vor defini toate grupurile nivelului.

• Operaţiunea inversă grupării este Data – Group and Outline – Group aplicată selecţiei de detalii.

7

Operarea unui outline

Row Level Bar

Column Level BarLevel Symbols

8

Parametrii unui outline

• Prin comanda Data – Group and Outline – Settings se pot fixa parametrii operaţiunii de grupare:– situarea stânga – dreapta a coloanelor totalizatoare,– situarea sus – jos a liniilor totalizatoare.

9

Parametrii unui outline• Dacă în dialogul Settings se selectează controlul

Automatic styles, atunci prin Create sau Apply Styles se defineşte un outline automat şi se aplică stiluri, sau doar se atribuie stiluri implicite.

• Implicit, se utilizează stilurile RowLevel_1, RowLevel_2 etc., ColLevel_1, ColLevel2 etc. pentru linii/coloane.

• Stilurile se pot modifica prin Format – Styles. • Se poate utiliza Format – AutoFormat, după

poziţionarea celulei active în domeniul datelor structurate prin outline.

10

Unificarea datelor (Consolidate)• Prin unificarea (consolidarea) datelor se înţelege

procedura prin care date aflate pe foi distincte sunt aduse, prin intermediul unei operaţii aritme-tice, pe o aceeaşi foaie.

• Cu alte cuvinte, prin consolidare se pot aduna, multiplica etc. date aflate în locaţii diferite, rezultatul fiind depus într-o foaie distinctă.

• Numim:– regiuni sursă = zonele unde se află valorile care se

unifică (source areas),– regiunea destinaţie = zona unde se centralizează

datele (consolidation table).

11

Unificarea datelor (Consolidate)

• Unificarea datelor se poate efectua în mai multe moduri:– prin referinţe 3-D (modalitate manuală),– prin poziţie (asistată de Excel),– prin categorii (asistată de Excel),– prin crearea unui tabel pivotant, metodă discutată

ulterior (asistată de Excel).• Modalităţile asistate de Excel se realizează prin

intermediului dialogului Consolidate, activat de Data – Consolidate.

12

Unificarea prin referinţe 3-D• Zona destinaţie se completează cu formule care

se referă la regiunile sursă.• Referinţele 3-D au forma completă:

[NumeCaiet]NumeFoaie!ReferinţăZonăPot fi date şi dinamic (utilizând mouse-ul).

• Nu există restricţii asupra structurii datelor din regiunile sursă (utilizatorul are control complet).

• Se păstrează legăturile cu zonele sursă, deci modificarea acestora se va reflecta, automat, în zona de consolidare.

13

Dialogul Consolidatefuncţia de

consolidare

regiunile sursă

Categorii de consolidare

Legătura cu sursele

14

Unificarea prin poziţie

• Este utilizată atunci când regiunile sursă sunt structurate identic în foile de calcul.

• Se urmează etapele:– se activează colţul din stânga-sus al regiunii

destinaţie,– se dă comanda Data – Consolidate,– se fixează funcţia de consolidare,– se stabilesc regiunile sursă prin repetarea paşilor

• fixare referinţă (prin scriere sau dinamic),• acţionarea butonului Add.

15

Unificarea prin poziţie• Dacă se doreşte, se stabilesc legături la datele

surse (a se vedea şi discuţia privind modificarea unei consolidări).

• Referinţele se dau fără a include etichetele care există, eventual, în regiunile sursă.

• In regiunea destinaţie, etichetele se trec manual.• Fiecare celulă a regiunii destinaţie va conţine

rezultatul calculării funcţiei de consolidare aplicată celulelor aflate în poziţiile corespunză-toare din zonele sursă.

16

Unificarea prin categorii• Acest mod de consolidare se aplică atunci când

regiunile sursă nu sunt structurate identic, dar utilizează un acelaşi set de etichete de linii/co-loane de date (care coincid sau nu ca ordine, ca prezenţă).

• Procesul de consolidare prin categorii parcurge aceleaşi prime etape ca şi consolidarea prin poziţie, în plus apare:– în grupul Use labels in se va selecta structura de

etichete existentă în zonele sursă (top row şi/sau left column).

17

Unificarea prin categorii• Dacă se doreşte, se stabilesc legături la datele

surse (a se vedea şi discuţia privind modificarea unei consolidări).

• Referinţele se dau cu includerea etichetelor din zonele sursă.

• In regiunea destinaţie, etichetele apar automat.• Fiecare celulă a regiunii destinaţie va conţine

rezultatul calculării funcţiei de consolidare aplicată celulelor aflate în poziţiile determinate de etichetele corespunzătoare din zonele sursă.

18

Modificarea consolidării• Pentru a elimina o regiune de consolidare se

selectează şi se elimină în mod uzual. Zona de consolidare nu funcţionează ca un tabel.

• La o consolidare 3-D se modifică formulele.• Modificarea consolidărilor prin poziţi/categorii

are loc, de regulă, prin refacerea consolidării cu modificările necesare (adăugare de noi regiuni, modificare de referinţe). Este necesar să se elimine consolidarea veche atunci când există legături către surse.

19

Tabele pivotante• Un tabel pivotant (Pivot Table) reprezintă un

tabel cu mai multe intrări (dimensiuni) care sintetizează informaţii dintr-o sursă de date.

• Este cel mai puternic instrument Excel de analiză a datelor, din categoria cuburilor OLAP (On Line Analitical Processing) în care informaţia este păstrată ierarhic şi nu în tabele.

• Forma vizuală a tabelului este dotată cu elemente active prin operarea cărora este posibilă modificarea tabelului, alegerea nivelului de generalitate a informaţiilor, modul de sinteză.

20

Tabele pivotanteCâmp de pagină Câmp de coloane Câmp de dateCâmp de linii

21

Elementele unui tabel pivotant

• Un tabel pivotant conţine câmpuri, care cores-pund la câmpuri din sursa de date sau sunt create de utilizator sub forma de câmpuri calcu-late (calculated fields) prin formule speciale.

• Fiecare câmp are ataşat un buton prin glisarea căruia se poate modifica structura tabelului şi tipul câmpului, aşa încât clasificarea următoare este relativă, se referă la structura tabelului la un moment dat şi la poziţia câmpului în această structură.

22

Elementele unui tabel pivotant• Câmp de pagină (page field) = este un câmp din

sursa de date care produce clasificarea rezulta-telor pe pagini. Fiecare valoare individuală a câmpului produce o pagină a tabelului (informa-ţiile afişate pe acea pagină sunt doar din înregis-trările care au acea valoare a câmpului de pagină).

• Câmp de linie (row field) = este un câmp care produce linii ale tabelului pivotant – fiecare valoare distinctă a câmpului determină o linie în tabel.

23

Elementele unui tabel pivotant• Câmp de coloană (column field) = este un câmp

care produce coloane ale tabelului pivotant – fiecare valoare distinctă a câmpului determină o coloană în tabel.

• Câmp de date (data field) = este un câmp din sursa de date pentru care se calculează rezu-matele statistice (suma, produsul, media etc.).

• Intrări (items) = valorile distincte ale câmpurilor de linie/coloane/pagini. Aceste valori produc etichetele de linii/coloane/pagini ale tabelului.

24

Elementele unui tabel pivotant• Regiunea datelor (data area) = este partea

tabelului pivotant care conţine rezultatele opera-ţiilor de sinteză (rezumare) a câmpurilor de date.

• Fiecare celulă a regiunii conţine sinteza câmpu-lui de date corespunzător, din toate înregistrările care se potrivesc exact cu valorile corespunză-toare ale câmpurilor de linie, coloană, pagină.

• Intr-un tabel pot exista mai multe câmpuri de pagină/linie/coloană. Ordinea introducerii lor de-fineşte o ierarhie respectată la afişarea intrărilor.

25

Definirea unui tabel pivotant• Etapele de definire a unui tabel pivotant:

– fixarea sursei de date = se admit liste Excel, surse externe de date (baze de date, interogări etc.), regiuni sursă de consolidare, alt tabel pivotant,

– stabilirea locaţiei unde se va afişa tabelul realizat,– construirea structurii tabelului şi fixarea parametrilor

acestuia.• Definirea unui tabel pivotant este gestionată de

Excel, în toate cazurile, de un utilitar specializat, iniţiat prin Data – PivotTable and PivotChart Report.

26

Definirea unui tabel pivotant• Primul dialog afişat

permite stabilirea sursei de date şi scopul utilita-rului (tabel pivotant sau grafic pivotant).

• Dialogul al doilea este specific sursei selectate. Tabelele obţinute diferă uşor după surse.

• Vom discuta în continua-re doar surse de tip listă Excel şi domenii de con-solidare.

27

Tabele pivotante (listă Excel)• Dialogul al doilea permite definirea domeniului

listei. Dacă lista este selectată prin poziţionarea celulei active, atunci are loc o “ghicire” a dome-niului listei, utilizatorul putând să o modifice.

• Dialogul al treilea stabileşte localizarea tabelului, dar, prin butoanele Layout şi Options dă acces la dialogurile de fixare a structurii tabelului şi, respectiv, de stabilire a parametrilor tabelului.

• Nu este necesar să se opereze în acest stadiu în dialogurile Layout şi Options. Se poate crea un tabel vid care să fie completat ulterior.

28

Dialogul Layout• Prin glisarea butoanelor care corespund la câmpurile

listei în locurile dorite din tabel se atribuie rolurile de câmp pagină, linie, coloană, date.

• Un acelaşi câmp poate fi glisat în mai multe locuri, dacă este necesar (de exemplu numărarea unor intrări, sau prezenţa mai multor funcţii pentru acelaşi câmp).

29

Dialogul Options• Există opţiuni privind

structura tabelului (prezenţa totalurilor, formatare).

• Se remarcă opţiunile privind actualizarea şi operarea tabelului pivotant. De exemplu, Enable drilldown permite (prin dublu click pe o celulă) vizualizarea (afişarea) anumitor detalii.

30

Tabele pivotante (consolidare)• Pentru ca o consolidare de date să poată fi

realizată ca un tabel pivotant, trebuie ca fiecare regiune sursă să conţină etichete în prima linie şi prima coloană.

• Consolidarea va fi similară consolidării prin cate-gorii, existând în plus paginile care, pe lângă pa-gina totalizatoare, corespund la regiunile sursă.

• Este indicat ca regiunile sursă să fie denumite, pentru ca extinderea lor să poată fi uşor consi-derată la actualizarea tabelului. Observaţia este valabilă şi atunci când sursa de date este o listă.

31

Tabele pivotante (consolidare)• Dialogul al doilea din utilitar permite fixarea numărului de

câmpuri pagină:– prima opţiune conduce la crearea automată a unui câmp pagină

cu intrări regiunile sursă şi total,– a doua opţiune permite definirea manuală a 1-4 câmpuri pagină.

32

Tabele pivotante (consolidare)• După opţiunea aleasă

apare un dialog în care partea principală este un sistem de definire a regiu-nilor sursă.

• Dacă se definesc mai multe câmpuri pagină, în dialog se pot fixa denumi-rile itemilor câmpului prin selectarea unei regiuni în lista domeniilor şi trecerea denumirii în controlul ataşat câmpului de pagină.

33

Câmpuri/intrări calculate• Pentru a adăuga un câmp calculat:

– se dă comanda PivotTable de pe bara de unelte PivotTable,

– se alege intrarea Formula şi Calculated Field,– se dă numele noului câmp în Name, – în zona editabilă Formula se construieşte formula de

calcul, denumirile câmpurilor existente fiind inserate prin uneltele de pe dialogul afişat,

– se termină prin Add şi OK.• Pentru forma formulelor se va studia intrarea din

suportul de curs şi din Excel – Help.

34

Câmpuri/intrări calculate• Pentru a adăuga un item calculat:

– se dă comanda PivotTable de pe bara de unelte PivotTable,

– se alege intrarea Formula şi Calculated Item,– se dă numele noii intrări în Name, – în zona editabilă Formula se construieşte formula de

calcul, denumirile itemilor existenţi în câmp fiind inserate prin uneltele de pe dialogul afişat,

– se termină prin Add şi OK.• Pentru forma formulelor se va studia intrarea din

suportul de curs şi din Excel – Help.

35

Operarea unui tabel pivotant• Funcţia de sinteză a unui câmp de date se modi-

fică prin comanda Field Settings din meniul con-textual ataşat câmpului.

• Modul de afişare a unui rezultat se poate modifi-ca prin Field Settings – Options şi alegerea opţiunii dorite din lista Show data as.

• Actualizarea tabelului, pentru a reflecta modifi-cările datelor sursă, se realizează prin Refresh Data din meniul contextual sau PivotTable.

• Eliminarea unui tabel pivotant se realizează prin selectarea integrală şi Edit – Clear – All.

36

Alte operaţiuni• Crearea unui grafic din datele unui tabel pivotant

– Deoarece datele conţinute într un tabel pivotant sunt o sinteză a informaţiilor, este utilă reprezentarea lor într o diagramă grafică. Prin natura tabelului pivotant acest lucru se realizează într un mod specific:

• Se permite selectarea datelor prin activarea opţiunii Enable Selection la care se ajunge prin meniul PivotTable de pe bara de unelte sinonimă şi comanda Select a meniului.

• Se elimină (ascund) subtotalurile.• Se selectează datele care se reprezintă grafic, incluzând câmpurile

linii şi coloane, dar nu totalurile generale sau câmpurile pagină. Prin dragare, selecţia se va iniţia din colţul dreapta jos, astfel încât să se poată selecta câmpurile linie/coloană (altfel se intră în procedura de restructurare a tabelului).

37

Alte operaţiuni• Crearea unui grafic din datele unui tabel pivotant (continuare)

– Se acţionează unealta Chart Wizard şi se urmează procedura de trasare a unei diagrame (a se vedea secţiunea dedicată acestui subiect).

• Observaţii. O diagramă creată dintr-un tabel pivotant rămâne legată de datele vizibile în tabel: aspectul reflectă rearanjarea câmpurilor, trecerea la o altă pagină etc.

• Pentru salvarea şi tipărirea diagramelor pentru toate paginile tabelului, se acţionează Show Pages de pe bara de unelte PivotTable. Fiecare pagină apare atunci pe o foaie separată şi se poate reprezenta şi tipări independent de celelalte.

• Afişarea unei liste de formule dintr un tabel pivotant– Se poate obţine o listă a tuturor formulelor utilizate într un tabel pivotant

prin• Se activează o celulă a tabelului.• Se dă comanda Formulas din meniul PivotTable şi apoi List Formulas.

38

Funcţii importante• Referinţe

– ADDRESS(row_num,column_num,abs_num,a1,sheet_text) = returnează referinţa la o celulă ca text;

• ADDRESS(1,2,3,true,”foaie1”) produce foaie1!$B1– COLUMNS(array), ROWS(array) = returnează

numărul de coloane/linii din domeniul referit• COLUMNS(A1:B4) produce 2

– INDIRECT(ref_text,a1) = returnează o referinţă dată ca text

• INDIRECT(“A1”) returnează adresa A1, INDIRECT(A1) returnează adresa data ca text în celula A1

– OFFSET(reference,rows,cols,height,width) = returnează o referinţă nouă obţinută prin deplasarea referinţei din primul argument.

39

Funcţii importante• Alegeri

– CHOOSE(index_num,value1,value2,...) = alege valoarea de pe locul index_num dintr-o listă

• sum(choose(a1,b1:b10,c1:c10)) produce sum(b1:b10) dacă a1 conţine valoarea 1.

– HLOOKUP(lookup_value,table_array,row_index_num,range_lookup), VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) = caută valoarea lookup_value în linia de top (coloana din stânga) a domeniului table_array şi returnează valoarea din linia/coloana col_index_num. In range_lookup se precizează prin true/false modul de căutare.

40

Funcţii importante

• Text– CHAR(number) = returnează caracterul specificat

prin number (1 la 255):• CHAR(75) produce K

– CONCATENATE(text1,text2,...) = returnează un şir obţinut prin concatenarea argumentelor.

– LEN(text) = returnează numărul de caractere.– VALUE(text) = returnează numărul memorat (ca text)

în argument:• VALUE(“100”) produce 100• VALUE(100) produce 100

41

Funcţii importante• Text

– EXACT(text1,text2) = verifică dacă şirurile sunt identice

– FIXED(number,decimals,no_commas) = formatează un număr ca text cu un număr de zecimale precizat:

• FIXED(1234.567,2,false) produce “1,234.56”– LEFT(text,num_chars), RIGHT(text,num_chars),

MID(text,start_num,num_chars) = returnează un număr de caractere din stânga/dreapta/interiorul unui şir

– LOWER(text), UPPER(text), PROPER(text) = transformă capitalizarea şirului.

42

Funcţii importante

• Logice– AND(logical1,logical2, ...),

OR(logical1,logical2,...) NOT(logical), = calculează funcţiile logice corespunzătoare.

– TRUE(), FALSE() = returnează valorile logice corespunzătoare; (parantezele sunt opţionale).

– IF(logical_test,value_if_true,value_if_false) = reprezintă o structură if – then – else liniarizată:

• if(A1<2,12,14) produce 12 pentru A1 = 1.

43

Funcţii importante• Financiare

– FV(rate,nper,pmt,pv,type) = calculează valoarea scadentă (viitoare) a unei investiţii prin plăţi periodice constante şi o dobândă constantă.

– PV(rate,nper,pmt,fv,type) = calculează valoarea actuală (prezentă) a unei investiţii

– PMT(rate,nper,pv,fv,type) = calculează plăţile (ratele) pentru a acoperi o datorie prin plăţi constante şi dobândă constantă.

rate = dobânda, nper = număr de perioade, pmt = plata (rata), pv = valoarea prezentă, fv = valoarea viitoare, type = 0 sau 1(plata la sfârşitul/începutul perioadei).

44

Funcţii importante

• Date calendaristice– NOW() returnează data curentă (zi, oră), TODAY()

returnează doar ziua curentă– DAY(serial_number), MONTH (serial_number),

YEAR(serial_number) returnează ziua, luna, anul– HOUR(serial_number), MINUTE(serial_number),

SECOND(serial_number) returnează ora, minutele, secundele

– WEEKDAY(serial_number,return_type) returnează ziua lucrătoare ca un întreg; de exemplu, pentru return_type=1, 1 este duminica, 7 este sâmbata.

45

Funcţii importante• Numerice

– CEILING(number,significance) rotunjeşte un număr la următorul multiplu de significance. CEILING(2.4, 0.5) este 2.5

– COUNTIF(range,criteria) numără în range celulele nevide care îndeplinesc condiţia COUNTIF(B3:B6,">55")

– INT(number), ROUND(number,num_digits) rotunjeşte un număr prin lipsă, respectiv prin adaos,

– PI() returnează valoarea lui – RAND() returnează un număr aleatoriu între 0 şi 1;

funcţia se recalculează odată cu foaia!