Sa intelegem Pivot Tables 1 din 16

EXCEL 2003PIVOT TABLES

SUPORT DE CURS

2011

Sa intelegem Pivot Tables 2 din 16

Pivot Tables Tabele Pivot sunt o facilitate rapida si flexibila de a afisa centralizari, totalizari asupra unui tabel voluminos de date.In acest fel putem analiza , filtra sorta informatiile , concentrandu-ne asupra rezultatelor afisate.

Proiectare Tabele Pivot Pentru ca Tabelel Pivot sunt destinate sa efectueze insumarea datelor este important sa decidem ce fel de insumari dorim si mai ales care este forma finala a raportului dorit.

Primul pas 1: Analiza Datelor Avand datele in fisierul Sa intelegem Pivot Tables.xls ne propunem sa raspundem la intrebarile urmatoare print-un raport cat mai usor de realizat.

De cate ori oferim fiecare eveniment in fiecare locatie?

Ce evenimente oferim pentru fiecare oras (Locatie)?

Care sunt locatiile aferente fiecarui eveniment ?

Care este valoarea medie pentru Costul si Spatiul aferent fiecarui eveniment ?

Repartitia evenimentelor pe lunile anului.

Ce avenimente au avut deja loc ? Filtering

Care este cel mai costisitor eveniment? Sorting

Pasul 2: Proiectarea Tabelului Cum doriti sa fie afisata informatia ? Stabiliti care sunt campurile care raspund acestor cerinte si mai ales unde vor fi plasate aceste campuri.

Mai jos avem datele din fisierul Excel - Sa intelegem Pivot Tables.xls

Sa intelegem Pivot Tables 3 din 16

Intrebarea 1: De cate ori oferim fiecare eveniment in fiecare locatie ?

Model 1

Model 2

Observati utilizarea optiunii Wrap text pentru celulele cu denumirea evenimentului.

Intebarea 2 : Ce evenimente oferim pentru fiecare oras (Locatie)

Model 1 :

Locatia (COLUMN)

Eveniment (ROW) Count Of Eveniment

(DATA)

Eveniment (COLUMN)

Locatia(ROW) Count Of Eveniment

(DATA)

Count of Eveniment numara cate evenimente au loc in fiecare oras.

Eveniment (ROW)

Count Of Eveniment (DATA)

Locatia (ROW)

Sa intelegem Pivot Tables 4 din 16

Model 2

Intrebarea 3 : Care sunt locatiile aferente fiecarui eveniment ?

Model 1 :

Locatia (COLUMN)

Eveniment (COLUMN)

Count Of Eveniment (DATA)

Locatia (ROW)

Count Of Eveniment (DATA)

Eveniment (ROW)

Sa intelegem Pivot Tables 5 din 16

Model 2 : Eveniment (COLUMN)

Locatia (COLUMN)

Count Of Eveniment (DATA)

Sa intelegem Pivot Tables 6 din 16

Creare Pivot Tables Pasii

1. Selectati orice celula in tabelul cu date.2. Selectati Data -> Pivot Table and Pivot Chart Report 3. Indicati ca datele pentru Pivot Table se afla in sheetul Excel, click Next

4. Verificati ca este selectst intregul domeniu de celule din tabelul Excel sau daca nu doriti tot domeniul selectati numai portiunea dorita .

5. Click Next

6. Specificati daca doriti ca Pivot tables sa apara pe un sheet nou sau pe acelasi sheet cu datele.

Plasand pivot table pe un alt sheet va permite formatarea si manipularea datelor mult mai usor

Sa intelegem Pivot Tables 7 din 16

Aceasta apare pe ecran. Acum vom folosi functia Drag and Drop a butonului stang al mousului , tinem apasat butonul stang al mousului pe Eveniment din fereastra Pivot Table Field List si tragem campul respectiv in zona Drop Row Fields Here (Zona ROW).

Analog tragem Locatia in Zona COLUMN si Eveniment in Zona DATA, asa va arata Raportul :

IMPLICIT daca introduceti in zona DATA un camp NUMERIC Excel va realiza functia de SUM insumare, iar daca introduceti un camp de tip TEXT in zona DATA Excel va realiza functia COUNT .Puteti sa modificati functia implicita dand dublu click pe pe denumirea campului din tabel si veti alege functia dorita.

Modificarea formei Dupa ce ati creat un Pivot Table este foarte usor sa modificati forma acestuia. Pur si simplu trageti denumirea campului (culoare gri) si veti muta campul din zona ROW in zona COLUMN. Puteti introduce alte campuri din fereastra Pivot Table Field List.

Puteti elimina definitiv un camp din tabel pur si simlu tragandu-l in afara.

Zonele ROW/COLUMN/DATA Aceste zone le veti incaraca cu campurile aferente conform raportului dorit. Campul Eveniment si Campul Locatia au o sageata mica in partea dreapta.

Zona COLUMN

Zona DATA

Zona ROW Zona PAGE

Sa intelegem Pivot Tables 8 din 16

Daca doriti sa afisati numai anumite evenimente din sageata din dreapta campului Evenimente veti scoate bifa de la evenimentele nodorite. Pentru a le reafisa punem bifa la loc. All se refera la reafisarea tuturor evenimentelor. Analog se procedeaza pentru oricare camp cap de coloana sau cap de rand.

Summary Field Properties – Proprietatile campului Suma Putem sa modificam ce fel de calcul dorim pentru zona DATA .1. Selectati o celula din zona DATA (click dreapta) si alegeti Field Settings…

2. Scrieti in caseta Name : Numar de evenimente

Observati ca in caseta Summarize by : apare Count pentru ca avem un camp de tip Text in Zona DATA.

Butonul Number ne permite sa facem formatari iar butonul Hide permite ascunderea campului respectiv.

Daca am avea camp de tip numeric in zona DATA am putea sa modificam calculele sa facem valoarea medie alegand Average, sau valoarea maxima alegand Max.

Semnificatia Functiilor :Sum Realizeaza suma valorilor numerice.Count Numara cate celule nu sunt goale - contin informatie.Average Calculeaza valoarea medie.Max Reda valoarea maxima .Min Reda valoarea minima.Product Realizeaza produsul a dua sau mai multe campuri numerice.Count Nums Returneaza numarul celulelor numerice din domeniul respectiv.StdDev Standard deviation pe un esantion.StdDevp Standard deviation pe toata populatia.Var Variatia datelor pe un esantion Varp Variatia datelor pe intreaga populatie.

Exercitii : Valoarea medie a costurilor pentru fiecare eveniment

Sa intelegem Pivot Tables 9 din 16

Remarcati in campul Grand Total pe orizontala avem valoarea medie (500+250)/2=375In acest raport am tras Costul in zona DATA si am ales Average – valoarea medie.

Valoare medie a spatiului ocupat de fiecare eveniment

Remarcati in campul Grand Total pe orizontala avem valoarea medie (50+100)/2=75In acest raport am tras Spatiu in zona DATA si am ales Average – valoarea medie.

Sa intelegem Pivot Tables 10 din 16

Lectia 2 : Particularizari

Obiective :

Elementele barei de instrumente Pivot Table Toolbar

Explorarea optiunilor Pivot Table

Introducerea Zonei PAGE

Lucrul cu gruparea datelor

Exercitii

Elementele barei de instrumente

Pivot Table Toolbar

Pentru ca sa apara automat bara de instrumente Pivot Table Toolbar dati click in orice celula din Tabelul Pivot.

.

Buton Descriere

Furnizeaza un meniu de optiuni aditionale : camp calculat si alte optiuni generale

Formatari automate , borduri, umbre.

Creeaza automat un grafic pe baza tabelului pivot existent si il plaseaza intr-un sheet nou.

Afiseaza Pivottale Wizard pentru a putea efectua corecturi .

Ascunde/Afiseaza Detalii – expandeaza/comprima grup de informatii

Refresh- improspateaza tabelul pivot cu ultimele date introduse in tabelul din care s-a creat Pivot Table

Afiseaza Pivot Table Field Box pentru campul selectat

Sa intelegem Pivot Tables 11 din 16

Pivot Table Options Dim bara de intrumente Pivot Table Toolbar , dati click in sageata de la Pivot Table si veti gasi Table Options , apare acesata fereastra :

Optiune Efect

Grand Totals for Columns/Rows

Daca este bifat afiseaza Total General la sfarsitul fiecarui rand sau coloana.

AutoFormat TableImplicit este bifat si permite aplicarea unor formate predefinite. Coloanele care contin date numerice sunt automat largite la largimea potrivita dupa aplicarea optiunii Refresh.

Subtotal Hidden Page ItemsDaca aveti incarcata zona Page , puteti decide daca doriti sau un sa fie incluse toate articolele paginii in subtotaluri.

Merge-Labels Concateneaza celulele rand exterior si etichete de coloana.

Repeat Item Labels on Each Printed Page

Campul rand exterior se va repeta la inceputul fiecarei pagini

Preserve FormattingPastreaza toate formatarile individuale (celule si font) pe care le-ati aplicat , chiar daca ati aplicat Refresh ( din semnul de exclamare rosu)

Fields per columnDetermina unde se afiseaza Zona Page daca contine mai multe campuri. Lucreaza impreun acu optiunea Page Layout.

For errror Values Show Tastati valoarea dorita a se afisa in caz ca in aceste celule apare eroare.

For empty Cells Show Tastati ce valoare sa fie afisata in celulele goale.

Set Print TitlesStabiliti titlu de rand sau coloana ce sa apara la fiecare salt la pagina noua.

Save Data With Table Layout

Se aplica numai cand datele pentru tabelul pivot se afla in alt fisier excel.

Enable DrilldownPermite ca dand dublu click pe o celula de insumare sa afiseze toate randurile care participa la formarea acelei sume(din celula).

Refresh On OpenDe fiecare data cand deschidem fisierul tabelul pivot este automat actualizat (Refresh).

Sa intelegem Pivot Tables 12 din 16

Page Fields

Zona PAGE ofera inca o sansa pentru filtarea datelor. De exemplu la intrebarea : Ce evenimente oferim pentru fiecare oras (Locatie)? Dar ati dori sa vedeti numai un singur oras odata, puteti introduce Locatia in zona PAGE

Trageti campul Locatia in Zona PAGE , campul Eveniment in Zona ROW, campul Eveniment in zona DATA si raportul va arata astfel

Sageata de langa All ne permite sa selectam o anumita localitate sau anumite localitati.

Sa intelegem Pivot Tables 13 din 16

Gruparea Datelor

Text Groups Gruparea dupa text

De exemplu doriti sa vedeti evenimentele grupate dupa by Business sau by Management.

1. Realizati un Pivot Table cu evenimentele pe zona ROW si cu Locatia in Zona COLUMN, in zona DATA introduceti Evenimentul.

2. In stanga selectati un vniment referitor la Marketing , tineti apasat CTRL si selectati inca un eveniment referitor la Marketing. Din meniul Data -> Group and Outline -> Group .

3. Apare un antet Group1 . Click dreapta pe aceasta celula Field Settings si schimbati numele.Pentru a expanda sau a desface un grup dublu –click pe celula cu numele grupului. Analog pe coloana, putem grupa dateva orase intr-un grup.

Remarcati in Group1 avem : Bucuresti, Dorohoi si Suceava.

Date Groups Puteti grupa datele dupa zi, luna an,

1. Creati un tabel pivot . Introduceti Eveniment in zona ROW si Start in zona COLUMN.

2. Click dreapta pe o celula cu data calendaristica si urmati Group and Show Detail -> Group

Sa intelegem Pivot Tables 14 din 16

3. Selectati in ce mod doriti gruparea datelor si click OK.

Aici aveti rezultatul gruparii dupa luna si an.

Exercitii de transformare Pivot Tables:

Tragem Cost in zona gri Sum of Spatiu

Sa intelegem Pivot Tables 15 din 16

Tragem Data peste Total , iata rezultatul :

Avem un raport unde vedem pentru fiecare eveniment spatiul total si costul total.

Voi trage campul Cost din Pivot Table Field List in zona DATA si fiecare zona o voi particulariza, astfel avem Max of Cost, Sum of Cost, Average of Cost si putem sa mai introducem inca odata Cost sa creem Min of Cost.

Sa intelegem Pivot Tables 16 din 16

Atentie !! Remarcati chenarul brazdat care inconjoara zona DATA numai aici introducem Cost si nu mai sus. Apoi dam click dreapta pe orice celula noua inserare alegem Field Settings si din fereastra care apare alegem functia dorita , dupa cum vedeti am ales Average , valoarea medie. Daca in caseta Name as introduce un text acesta va apare in loc de Average of Cost 2.