Download - Sa intelegem tabelel pivot
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.