Tabelele pivotante
Tabelele pivotante
sunt cel mai puternic si mai elaborat procedeu de analiza a datelor. Prin crearea unui tabel pivotant se realizeaza un tabel de date cu mai multe intrari in care se rezuma date
provenite din diverse surse: liste Excel, baze de date, surse de date externe
etc. Tabelul poate fi exploatat interactiv.
Puterea unui tabel pivotant consta
in aceea ca imaginea grafica formata in foaia de calcul este dotata cu o serie
de elemente de control si cu o serie de proceduri care permit rearanjarea
automata a tabelului (pentru a vizualiza diferite aspecte), inserarea de
linii/coloane de totaluri si subtotaluri, includerea de functii utilizator; in
cazul in care numarul de dimensiuni este mai mare, tabelul este organizat pe
pagini, fiecare dintre acestea devenind vizibila printr-o simpla selectare.
Pentru a
avea o imagine intuitiva sa consideram lista Excel alaturata.
Este evident ca analiza datelor este dificila in cazul existentei unui mare numar de
inregistrari. Ar trebuie un mijloc de selectare a unui
anumit reprezentant, anumit oras, anumit produs, totaluri pe luni/produse etc.
Desi o asemenea operatiune este posibila prin
procedura de filtrare a unei liste Excel, o imagine mai completa a realitatii
se poate obtine formand un tabel pivotant bazat pe aceste date. Un exemplu este dat in figura urmatoare.
Se poate observa aranjarea pe pagini
a rezultatelor, fiecare pagina fiind determinata de optiunea selectata in lista
derulanta de langa butonul reprezentant (in figura este
data pagina care cuprinde date pentru toti reprezentantii). Structura de linii
si coloane este determinata de numarul campurilor de linii sau coloane (in
figura se observa doua campuri care determina linii luna
si produs si un camp care determina coloanele localitate). Datele interioare sunt obtinute prin prelucrarea automata a
campului valoare, functia totalizatoare fiind suma (mesajul din coltul
stanga sus al tabelului). Tabelul mai contine subtotaluri pentru fiecare
luna si totalurile generale pe linii si coloane.
In utilizarea unui tabel pivotant,
structura acestuia se poate modifica interactiv prin tragerea butoanelor
campuri in alte zone ale tabelului: de exemplu, daca se trage butonul luna peste zona de pagina, langa reprezentant,
atunci o pagina este definita de o optiune pentru reprezentant si o optiune
pentru luna (aceasta nu va mai figura pe linii). Restructurarea dinamica
produce recalcularea tabelului.
Elementele unui tabel pivotant
Un tabel pivotant contine campuri de mai multe tipuri. Un camp
corespunde, in general,
unui camp din baza de date sau lista de baza, dar poate fi creat si de catre
utilizator pe baza unei formule. Fiecare camp are
corespondent un buton care poate fi agatat si tras in alta zona a tabelului,
producand restructurarea acestuia. Datorita posibilitatii de modificare a
structurii tabelului pivotant, clasificarea data campurilor este
relativa.
- Camp de pagina (page
field) este un camp din datele sursa
care produce clasificarea informatiilor pe pagini. In exemplul anterior
campul reprezentant este camp de pagina.
Campul este insotit de un control de tip ComboBox
din care se selecteaza pagina vizibila. In lista derulanta sunt afisate
toate valorile acestui camp care se intalnesc in datele sursa.
- Camp de linie (row
field) este un camp din date care a
primit o orientare orizontala, pe linie. In exemplu, campurile luna si produs sunt campuri linie. Daca
sunt mai multe campuri linie, acestea formeaza in prezentarea vizuala o
structura ierarhica, doar nivelul exterior fiind listat o singura data.
- Camp de coloana (column
field) este un camp din date care a primit o
orientare verticala, pe coloana. In exemplu, campul localitate este un camp
coloana. Daca sunt
mai multe campuri coloane, acestea formeaza in prezentarea vizuala o
structura ierarhica, doar nivelul exterior fiind listat o singura data.
- Camp de date (data
field) este un camp din datele sursa
care contin informatia rezumata. In exemplu este
campul valoare. Pentru datele numerice functia implicita de
rezumare este sumarea (sum), pentru datele text
functia implicita este cea de numarare (count).
- Intrari (items) sunt valorile (categoriile) unui camp. De
exemplu, 'ian' si 'feb' sunt intrari ale campului luna. Aceste intrari sunt utilizate in tabelul pivotant
drept etichete de linii/coloane/pagini.
- Regiunea datelor (data
area) este partea tabelului pivotant
care contine rezumatele datelor, cu alte cuvinte zona din tabel fara liniile
si coloanele de campuri si etichete.
Crearea unui tabel pivotant
Microsoft Excel dispune de un wizard specializat pentru crearea unui tabel pivotant.
Cei trei pasi pe care trebuie sa-i parcurga utilizatorul in mod asistat
corespund celor patru dialoguri ale wizard-ului.
- Fixarea datelor sursa.
Pornirea wizard-ului pentru crearea unui tabel pivotant se realizeaza
folosind optiunea PivotTable Report din meniul Data.
Ca sursa de date se poate indica:
- Microsoft Excel list or database este
cazul frecvent al operarii in Excel si fixeaza ca sursa o lista Excel.
Este util in acest caz sa se selecteze lista
inainte de a se da comanda PivotTable Report.
- External data source se
arata ca datele sursa se afla intr-un fisier extern (baza de date Access
etc.).
- Multiple consolidation range permite
realizarea unei unificari de date sub forma de tabel pivotant.
- Another PivotTable
sursa de date este
un tabel pivotant creat anterior.
Se actioneaza
butonul Next pentru a se trece la urmatorul dialog.
- Referinta la datele sursa.
In al doilea dialog al wizard-ului se precizeaza locul exact, adresa, de
unde se preiau datele sursa. Daca acestea sunt intr-o lista Excel dialogul
este cel din figura urmatoare.
In partea inferioara se observa
butoanele de navigare intre ecranele wizard-ului:
- Cancel
anuleaza operatiunea de creare a tabelului pivotant.
- Back
se revine la dialogul precedent.
- Next
se accepta optiunile din dialogul curent si se trece la pasul
urmator.
- Finish
termina faza de preluare a optiunilor si creeaza tabelul
pivotant fara a mai afisa celelalte dialoguri.
In zona Range
se da referinta la domeniul datelor sursa, aceasta poate fi stabilita dinamica
prin utilizarea butonului de restrangere a dialogului sau prin cautare
actionand Browse.
- Localizarea tabelului
pivotant. Ultimul dialog al wizard-ului determina locul unde se
creeaza tabelul si diverse optiuni privind structura acestuia.
- New worksheet
tabelul se creeaza pe o foaie noua,
- Existing workshet tabelul
se creeaza intr-o zona a unei foi existente. Zona poate fi selectata
dinamic.
Prin actionarea butonului Finish
se termina wizard-ul si se creeaza tabelul pivotant pe baza optiunilor
selectate.
In grupul de butoane este afisat in acest dialog butonul Options care
deschide un nou dialog pentru configurarea tabelului.
Optiunile, suficient de sugestive
prin denumirile lor, se refera in principal la includerea in tabel a
liniilor/coloanelor de total (Grand totals for columns, Grand totals for
rows), la formatarea automata a tabelului rezultat (AutoFormat table),
la modul de afisare a celulelor vide sau cu erori (For error values, show, For empty cells, show). Alte optiuni sunt explicate in
sectiunile care urmeaza.
Grupul Data options fixeaza
relatiile dintre tabel si datele sursa (salvare, actualizare etc.). Prin OK se
revine la pasul 4 al wizard-ului.
Observatie. Dialogul poate fi afisat si
independent de wizard-ul de creare a unui tabel pivotant prin bara de unelte PivotTable sau meniul contextual.
Alaturi de butnul Options mai este butonul Layout care deschide urmatoarea fereastra:
Dialogul permite
stabilirea interactiva a campurilor tabelului. Dialogul contine o schema a tabelului cu
figurarea zonelor acestuia si, in partea dreapta, o lista cu toate campurile
definite in datele sursa.
Prin tragerea butonului
corespunzator unui camp din lista sursa in regiunea dorita a tabelului (Page
pagini, Row linii, Column coloane, Data regiunea datelor)
se creeaza in tabel un camp de pagina, de linie, de coloana sau, respectiv, de
date. In timpul cat dialogul este vizibil se poate
efectua orice operatiune de drag-and-drop cu butoanele reprezentand campurile.
La crearea unui camp de date se
stabileste implicit functia de centralizare, dar aceasta se poate modifica prin
click dublu pe campul de date, caz in care se poate selecta o alta functie predefinita.
Crearea unui camp calculat
In afara de liniile de totalizare create in mod automat se
pot introduce linii suplimentare care sa contina date calculate dupa formule
proprii. In aceasta operatiune se face distinctia intre un
camp calculat,
situat in nivelul superior al ierarhiei de campuri linie sau coloana, si un item
calculat, care reprezinta un camp
inserat pe un nivel
inferior al ierarhiei de campuri.
Pentru crearea unui camp
calculat urmati pasii:
- Activati o celula din tabelul
pivotant.
- Alegeti optiunea Formulas
din meniul PivotTable (sau bara de unelte PivotTable)
si selectati optiunea Calculated Field.
- Va
fi afisata fereastra Insert Calculated Field.
- In zona de editare Name
tastati un nume pentru campul care se creeaza.
- In zona Formula
creeati formula dupa care se calculeaza noul camp. Atunci cand este necesara o referinta la datele dintr-un alt camp,
operatiunea se realizeaza prin selectarea in lista Fields si
actionarea butonului Insert Field (rezultatul se vede in zona Formula).
- Apasati butonul Add
pentru a termina. Noul camp
poate fi pozitionat
in tabelul pivotant (pe linie sau pe coloana) in mod uzual.
Crearea unui item calculat
Pentru crearea unui item calculat (similar
unui camp calculat, dar situat pe un nivel inferior al ierarhiei de campuri) urmati pasii
urmatori.
- Daca tabelul pivotant este structurat ca un outline (total sau doar pentru
campul unde se insereaza noul item), eliminati gruparea intrarilor
campului unde are loc inserarea.
- Selectati campul sau o
intrare a campului unde veti adauga itemul calculat.
- Alegeti optiunea Formulas
din meniul PivotTable (sau bara de unelte PivotTable)
si selectati Calculated Item.
- Va
fi afisata fereastra Insert Calculated Item. Este de remarcat
informatia suplimentara din linia de titlu privind campul in care se
introduce noul subcamp.
- In zona editabila Name
dati un nume noii intrari.
- In zona Formula
creeati formula de calcul a itemului. Referinta
la un camp
sau o alta intrare
(doar din campul curent insa) se obtine utilizand listele Fields si
Items.
- Prin butonul Add
terminati definirea unei noi intrari.
- Pasii 2-7 pot fi repetati
pentru alte intrari calculate (ale aceluiasi camp) si procesul se termina
prin OK.
Afisarea rezultatelor pentru
campurile calculate
Pentru campurile calculate inserate de
utilizator sau create de Excel ca linii/coloane de totaluri se poate alege un mod de afisare implicand calcule suplimentare asupra
tabelului pivotant. Pentru a selecta un asemenea mod
de afisare a rezultatelor:
- deschideti meniul contextual
(prin click dreapta pe campul respectiv);
- selectati
optiunea Field si, daca nu este vizibila lista derulanta Show
data as, alegeti Options>> din fereastra afisata.
In lista derulanta Show data as
sunt disponibile optiunile urmatoare, selectarea unei optiuni producand
calculele si afisarea corespunzatoare a tuturor rezultatelor campului
respectiv:
- Difference From -
Afiseaza diferentele dintre valoarea campului selectat in lista Base
field si valoarea campului selectat in lista Base item.
- % Of - Afiseaza
procentaje raportate la valoarea selectata in Base item.
- % Difference From - Este o combinatie intre optiunile
precedente: afiseaza diferentele dintre valoarea campului si valoarea fixa
din Base item, afisarea fiind in procente.
- Running Total In -
Afiseaza datele ca totaluri succesive. Campul pentru care se alege aceasta
optiune trebuie sa fie selectat in prealabil.
- % of row - Afiseaza
datele din fiecare linie ca procentaj din totalul liniei.
- % of column -
Afiseaza datele din fiecare coloana ca procentaj din totalul coloanei.
- % of total -
Afiseaza datele ca procentaje din totalul general.
- Index - Afiseaza
datele utilizand formula: ((value in cell) x (Grand Total of Grand
Totals)) / ((Grand Row Total) x (Grand Column Total)). Este proportia
realizata in celula din ceea ce este asteptat in
cazul unei distributii uniforme a datelor.
Bara de unelte PivotTable
Pentru operatiuni asupra elementelor unui
tabel pivotant este disponibila o bara de unelte
specifica. Afisarea ei se face in mod uzual: View - Toolbars - PivotTable.
Figura urmatoare prezinta aceasta bara de unelte.
Utilizarea acestor unelte este explicata la prezentarea operatiunilor specifice pentru
tabelele pivotante.
Meniul PivotTable, deschis din prima unealta a barei PivotTable,
contine comenzi dintre care unele sunt acoperite de unelte ale barei PivotTable
iar altele de comenzi ale meniului contextual asociat unui tabel pivotant.
Operatiuni asupra unui tabel
pivotant
Dintre operatiunile permise intr-un
tabel pivotant sunt explicate in continuare doar cele mai importante.
Actualizarea unui PivotTable
Prin operatiunea de actualizare
intelegem recalcularea tabelului pentru a include eventualele modificari
efectuate asupra datelor sursa, operatiunea fiind importanta mai ales atunci
cand se considera o sursa externa de date.
Pentru actualizare urmati pasii:
- selectati o celula a tabelului,
- alegeti
optiunea Refresh Data din bara PivotTable (sau Refresh
Data din meniul PivotTable).
Observatii
- Pentru actualizarea
tabelului la deschiderea caietului se da comanda Options din meniul
PivotTable si sub Data source options se marcheaza Refresh
on open. In lipsa acestei marcari nu se realizeaza o actualizare a
datelor la deschiderea caietului.
- Daca sursa de date este o lista Excel si s-au adaugat noi linii si campuri
acesteia, dupa actualizarea tabelului se poate utiliza PivotTable Wizard
pentru adaugarea noilor campuri la structura tabelului.
- Pentru actualizarea tuturor
tabelelor pivotante din caiet se da comanda Refresh All din meniul PivotTable
a barei de unelte sinonime.
Modificarea sursei de date
Atunci cand se modifica structura listei
Excel considerata sursa de date a unui tabel pivotant, modificarea poate fi
reflectata in tabel urmand pasii:
- Selectati o celule a
tabelului.
- Alegeti optiunea PivotTable
Wizard.
- In dialogul PivotTable
Wizard - Step 3 of 4 apasati butonul Back.
- In dialogul PivotTable
Wizard - Step 2 of 4 selectati noul domeniu al datelor sursa prin
includerea liniilor/coloanelor adaugate.
- Apasati Next.
- In dialogul PivotTable
Wizard - Step 3 of 4 modificati dupa necesitati structura tabelului.
- Apasati butonul Finish.
Eliminarea
unui tabel pivotant
Eliminarea unui tabel pivotant din
foaia de calcul se poate efectua in doua moduri principale. Stergerea unui tabel pivotant nu
afecteaza datele sursa.
- Prin selectarea unei zone
din foaia de calcul care include in totalitate tabelul si comanda Clear All din meniul Edit.
Acest mod este accesibil atunci cand tabelul este
inconjurat de o zona de celule libere care permite selectia prealabila
stergerii.
- Utilizand comenzile din
bara de unelte PivotTable
1. In meniul PivotTable alegeti optiunea Select si
asigurati-va ca butonul Enable Selection este activ.
2. Activati prin click o celula a tabelului.
3. In meniul PivotTable selectati optiunea Select
si apoi Entire Table.
4. In meniul Edit selectati optiunea Clear si alegeti All.
Formatarea unui tabel pivotant
In formatarea unui tabel pivotant trebuie retinut ca pentru
pastrarea formatarii efectuate si dupa o operatiunea de actualizare (refresh)
a tabelului se va incepe prin verificarea ca butonul Enable Selection (dupa
succesiunea bara PivotTable meniul PivotTable Select) este
activ, ceea ce permite selectarea unor zone ale tabelului pivotant.
Urmati urmatoarele etape:
- Selectati partea dorita din
tabel.
- Formatati (chenar, font) in
mod uzual, cu unelte sau comenzi din meniul Format.
- Pentru stabilirea optiunilor
care afecteaza formatarea tabelului (cum ar fi AutoFormat) alegeti
optiunea Options (din meniul PivotTable de pe bara sinonima) sau din pasul 4 al wizard-ului PivotTable.
.com
