Utilizarea tabelelor pivot (pivot table)

Un tabel pivot utilizeaza date pe doua dimensiuni pentru a crea un tabel in trei dimensiuni. Sunt o cale foarte buna pentru a concentra multa informatie intr-un spatiu restrans.Tabelele pivot iti permit si sa faci auditul datelor. Caracteristica unica a tabelelor pivot este capacitatea lor de a muta campurile prin tragere si plasare. Excel recalculeaza instantaneu datele in noua rearanjare. Nu trebuie sa scrii noi formule sau sa refiltrezi datele deoarece tabelul pivot se recalculeaza automat atunci cand se recalculeaza campurile.

Aplicatia Pivot Table Wizard poate fi folosita pentru a crea un nou tabel pivot sau pentru a edita un tabel pivot deja existent.

Pentru a crea un tabel pivot trebuie sa parcurgi pasii:

Executa secventa DataPivotTable and PivotChart Report (Raport tabel pivot si diagrama pivot).

In pasul 1 al aplicatiei PivotTable Wizard trebuie indicata sursa de date pe care doresti sa le utilizezi pentru tabelul pivot. Pentru sursa de date ce trebuie analizata iti stau la dispozitie urmatoarele patru optiuni:

Microsoft Excel List or Database (Lista sau baza de date Microsoft Excel) – utilizeaza date organizate cu etichete de linie si coloana pe o foaie de calcul. Este parametru predefinit.

External Data source (Sursa externa de date) – utilizeaza un fisier sau o baza de date din afara registrul 525c27f ui de calcul curent.

Multiple Consolidation Ranges (Zone multiple de centralizare) – creaza un tabel sau o diagrama pivot din mai multe foi de calcul Excel.

Another PivotTable or PivotChart (Alt tabel pivot sau diagrama pivot) – creaza un tabel sau o diagrama pivot dintr-un alt raport pivot din acelasi registru de calcul.

Stabileste tipul de raport pivot pe care vrei sa-l creezi: un tabel sau o diagrama. Apoi selecteaza butonul Next.

In pasul numarul 2 al aplicatiei Pivot Table Wizard selecteaza o sursa de date daca aceasta nu este selectata sau daca datele se gasesc intr-un alt registru de calcul sau domeniu decat cel prezent in caseta de dialog.

Pasul 3 al aplicatiei PivotTable Wizard iti ofera mai multe optiuni de plasare si formatare a tebelului pivot. Tabelul pivot poate fi plasat pe o noua foaie de calcul sau pe foaia curenta. Daca optezi pentru foaia de calcul existenta trebuie sa precizezi localizarea tabelului pivot. De asemenea, poti folosi butoanele Layout (Aspect) si Options(Optiuni) pentru a continua precizarea parametrilor doriti pentru noul tabel pivot.. Acesti parametri pot fi modificati si ulterior, dupa crearea tabelului sau diagramei pivot.

Cand ai terminat de selectat optiunile dorite, executa click pe Finish (Terminare) pentru a crea tabelul.

Executarea unui click pe butonul Layout din pasul 3 duce la afisarea casetei de dialog Pivot Table Wizard-Layout.

Poti utiliza aceasta caseta de dialog pentru a controla modul de afisare a datelor intr-un tabel pivot. Campurile datelor selectate apar in partea dreapta a casetei de dialog. Selecteaza si trage campurile dorite in zona din centrul casetei de dialog si plaseaza-le in sectiunile Row (Rand), Column (Coloana), Page (Pagina) si Data (Date) pentru a obtine aspectul dorit.

Lista de mai jos descrie cele patru zone in care pot fi introduse campurile:

Page creeaza un meniu derulant deasupra tabelului, care iti permite sa extragi o anumita optiune cum ar fi un departament sau o tara.

Row aplica un format vertical tabelului, rezumand datele de sus in jos – reprezinta fiecare obiect din campurile din partea stanga a tabelului pivot.

Column aplica un format orizontal tabelului, rezumand datele de la stanga la dreapta – prezinta fiecare obiect din campurile din partea de sus a tabelului pivot.

Data – aceasta zona este rezumatul valorilor. Aici poti aduna, numara sau crea alte functii de analiza pentru datele introduse. Executa dublu click pe camp pentru a obtine acces la functia sau tipul de rezumat dorit.

Functiile alese de Excel, care sunt la dispozitie in campul Data, depind de natura campului plasat. Poti sa aplici si alte functii, cum ar fi medie, maxim, minim. Pentru a schimba functia pe care Excel o utilizeaza la rezumarea datelor, in sectiunea Data a casetei de dialog Pivot Table and PivotChart Wizard-Layout executa dublu click pe campul a carui functie vrei sa o modifici. Se deschide caseta de dialog Pivot Table Field, de unde poti selecta functia dorita.

Poti personaliza functiile de rezumare executand click pe butonul Options (Optiuni) ca sa extinzi caseta de dialog PivotTable Field. Optiunile suplimentare iti permit sa analizezi datele ca procentaje din total, in functie de campurile si obiectele de baza. Campul de baza este campul de comparatie din analiza personalizata, iar obiectul de baza este obiectul de camp din aceeasi analiza.

Executarea unui click pe butonul Options din pasul 3 al casetei de dialog Pivot Table Wizard deschide caseta de dialog Pivot Table Options, in care poti continua precizarea optiunilor de formatare si surse de date.

Poti apela aceasta caseta de dialog si dupa ce ai terminat crearea tebelului pivot, fie prin executarea unui click pe butonul Pivot Table din bara de instrumente Pivot Table si selectarea optiunii Table Options (Optiuni tabel) din lista derulanta, fie prin executarea unui click dreapta pe tabelul pivot si selectarea comenzii Table Options din meniul rapid.

Optiunea Name (Nume) din caseta Pivot Table Options iti permite sa stabilesti un nume pentru tabelul pivot. In mod prestabilit, Excel numeste noile tabele Pivot Table 1, Pivot Table 2, dar poti introduce oricand alt nume.

Optiunea Grand Totals for Columns (Totaluri generale pentru coloane) executa functiile de analiza si asigura totaluri generale pentru fiecare coloana din tabelul pivot. Optiunea Grand Totals for Rows (Totaluri generale pentru randuri) face acelasi lucru pentru fiecare linie din tabelul pivot. Aceste optiuni sunt selectate implicit, insa este bine sa le deselectezi pentru a nu aglomera tabelele pivot cu prea multe totaluri.

Daca tabelul pivot contine campuri ascunse, vei dori, probabil, sa obtii un subtotal pentru aceste campuri, insa fara sa afisezi continutul campurilor. In astfel de cazuri, selecteaza optiunea Subtotal Hidden Page Itema (Subtotal elemente pagina ascunsa).

Excel aplica automat un autoformat prestabilit noilor tabele pivot. Daca preferi sa selectezi un alt format, poti dezactiva optiunea AutoFormat Table din caseta de dialog Pivot Table Options sau poti modifica formatarea dupa crearea tabelului pivot.

Pentru a schimba formatarea automata aplicata alege FormatAutoFormat sau executa click pe pe butonul   Format Report (Formatarea raport) din bara de instrumente Pivot Table.

tabel neformatat

 

acelasi tabel formatat cu AutoFormat

 

O importanta caracteristica a aplicatiei Excel 2000 este capacitatea de pastrare a formatarii unui tabel pivot atunci cand actualizezi datele sau ii modifici aspectul. Optiunea Preserve Formatting (Pastrare formatare) este selectata implicit insa o poti dezactiva daca preferi sa revii la formatul original.

Optiunea Merge Labels (Imbinare etichete) iti permite sa combini celulele din coloanele si liniile perimetrului exterior al tabelului pivot. Aceasta este o caracteristica de formatare.

Un tabel pivot poate ocupa mai multe pagini. Pentru a afisa etichetele pentru coloane si linii se bifeaza optiunea Repeat Items Labels on Each Pronted Page (Se repeta etichetele elementelor pe fiecare pagina imprimata).

Optiunea Set Print Titles (Stabilire titluri de imprimare) este prin definitie dezactivata. Selecteaza aceasta optiune in cazul in care doresti ca la imprimare sa utilizezi etichetele de camp si obiect in titlurile tabelului pivot.

Prin definitie, ordinea campurilor in macheta este de sus in jos si apoi in lateral. Daca preferi ca ordinea sa fie de la stanga la dreapta si apoi in jos, schimba parametrul Page Layout (Aspect pagina) din caseta de de dialog PivotTable Options. In macheta poti modifica numarul de campuri pe coloana sau linie precizand numarul respectiv in caseta Fields per Columns (Campuri pe coloana). Prin definitie, numarul este zero.

In caseta Pivot Table Options exista doua optiuni care iti permit sa controlezi ce va afisa Excel in celulele in care nu apar datele asteptate. Optiunea For Error Values, Show_ (Pentru valori de eroare, se afiseaza_) iti permite sa inlocuiesti valorile eronate cu un caracter sau un loc gol; optiunea For Empty Cells, Show_ (Pentru celule goale, se afiseaza_) iti permite sa stabilesti un caracter sau un spatiu pentru celulele goale. De exemplu, daca ai un raport care contine un mesaj #ERR, vei gasi ca este util sa atragi atentia asupra erorilor introducand cuvintele nume lipsa in caseta corespunzatoare optiunii For Error Values, Show_. Aceasta configuratie va duce la afisarea respectivelor cuvinte in orice celula ce contine o eroare. In plus, poti aplica formatari conditionate pentru a folosi si culori.

Sectiunea Data Options (Optiuni date) din caseta de dialog Pivot Table Options iti ofera cateva caracteristici utile. Lista de mai jos descrie aceste optiuni:

n       Save Data With Table Layout (Salvare date si aspect tabel) – salveaza o copie a datelor provenite dintr-o sursa externa de date. Este selectata in mod prestabilit. Deselectarea ei reduce dimensiunea registrului de calcul, dar te obliga sa actualizezi manual tabelul pivot atunci cand deschizi registrul – cu exceptia cazului cand este activata optiunea Refresh on Open (Reimprospatare la deschidere). Daca aceasta optiune este dezactivata nu poti lucra cu tabele pivot pana nu le reactualizezi.

n       Enable Drilldown (Activare selectie dinm lista) – prezinta detalii cand executi dublu click pe un camp; selectata in mod prestabilit.

n       Refresh on Open (Reimprospatare la deschidere) – actualizeaza tabelul pivot la deschiderea registrului de calcul. Nu este selectata in mod prestabilit.

n       Refresh Every _ Minutes (Reimprospatare la fiecare _ minute) permite actualizarea automata dupa intervalul de minute stabilit. Nu este selectata in mod prestabilit. Aceasta optiune este disponibila doar pentru tabelele pivot bazate pe surse externe de date.

n       Save Password (Salvare parola) – salveaza parola asociata sursei externe de date din care provine informatia. Nu este selectata in mod prestabilit; activarea ei poate compromite securitatea bazei de date.

n       Background Query (Interogare in fundal) – ruleaza interogarea in fundal, permitand utilizatorului sa continue lucrul. Nu este selectata in mod prestabilit.

n       Optimiza Memory (Optimizare memorie) – optimizeaza si gestioneaza memoria cand se folosesc tabele pivot; reduce performantele. Nu este selectata in mod prestabilit.

Atunci cand creezi o diagrama pivot, Excel creeaza in interiorul registrului de calcul o foaie de diagrama si o foaie pentru tabelul pivot. Deoarece informatia din diagrama trebuie sa provina dintr-un tabel Excel creaza automat tabelul.

Atunci cand doresti sa creezi o diagrama pivot trebuie sa parcurgi pasii:

Selecteaza tabelul pivot pentru care doresti sa creezi diagrama.

Din bara Pivot Table apasa butonul Chart Wizard.

Diagrama este creata automat pe o alta foaie de calcul.

Daca doresti sa modifici tipul de diagrama executi click dreapta pe diagrama si alegi un alt tip.

Diagramele pivot pot fi formatate pe foi de calcul separate pentru reprezentarea si afisarea datelor in format de diagrama si pot fi combinate cu datele din alte foi de calcul, transformandu-se intr-un instrument flexibil si foarte puternic.

Dupa ce ai creat un tabel sau o diagrama pivot le poti modifica aspectul tragand si plasand campuri sau utilizand optiunile din meniurile barei Pivot Table.

Bara de instrumente Pivot Table iti permite sa creezi macheta unui tabel pivot prin tragerea campurilor din bara de instrumente in zonele din tabelul pivot.

Diagramele pivot iti ofera forta tabelelor pivot si a diagramelor combinata intr-o singura suprafata interactiva. Poti selecta si trage campurile in noi pozitii pe diagrama iar programul Excel va pivota diagrama pentru a corespunde cu noua amplasare a campului.

camp mutat