Dacă citiți acest tutorial, există mari șanse să fi auzit de (sau chiar să folosiți) tabelul pivot Excel. Este una dintre cele mai puternice funcții din Excel (fără glumă).
Cea mai bună parte a utilizării unui tabel pivot este că, chiar dacă nu știți nimic în Excel, puteți face lucruri destul de minunate cu el, cu o înțelegere foarte simplă a acestuia.
Să începem.
Click aici pentru a descărca eșantionul de date și a continua.
Ce este un tabel pivot și de ce ar trebui să vă pese?
Un tabel pivot este un instrument din Microsoft Excel care vă permite să rezumați rapid seturi de date imense (cu câteva clicuri).
Chiar dacă sunteți absolut nou în lumea Excel, puteți utiliza cu ușurință un tabel pivot. Este la fel de ușor ca să trageți și să fixați antetele rândurilor / coloanelor pentru a crea rapoarte.
Să presupunem că aveți un set de date așa cum se arată mai jos:
Acestea sunt date despre vânzări care constă în aproximativ 1000 de rânduri.
Are datele despre vânzări în funcție de regiune, tipul comerciantului cu amănuntul și client.
Acum șeful dvs. ar putea dori să știe câteva lucruri din aceste date:
- Care au fost vânzările totale în regiunea Sud în 2016?
- Care sunt primii cinci vânzători cu amănuntul?
- Cum s-a comparat performanța The Home Depot cu alți comercianți cu amănuntul din sud?
Puteți merge mai departe și puteți utiliza funcțiile Excel pentru a vă oferi răspunsurile la aceste întrebări, dar ce se întâmplă dacă brusc șeful dvs. vine cu o listă de alte cinci întrebări.
Va trebui să reveniți la date și să creați noi formule de fiecare dată când există o modificare.
Acesta este locul în care tabelele pivot Excel sunt foarte utile.
În câteva secunde, un tabel pivot va răspunde la toate aceste întrebări (așa cum veți afla mai jos).
Dar adevăratul beneficiu este că poate găzdui șeful tău bazat pe date, răspunzând imediat la întrebările sale.
Este atât de simplu, încât la fel de bine poți să iei câteva minute și să îi arăți șefului cum să o facă singur.
Sperăm că acum aveți o idee de ce sunt atât de grozave tabelele pivot. Să mergem mai departe și să creăm un tabel pivot folosind setul de date (prezentat mai sus).
Inserarea unui tabel pivot în Excel
Iată pașii pentru a crea un tabel pivot folosind datele prezentate mai sus:
- Faceți clic oriunde în setul de date.
- Accesați Insert -> Tables -> Pivot Table.
- În caseta de dialog Creare tabel pivot, opțiunile implicite funcționează bine în majoritatea cazurilor. Iată câteva lucruri de verificat:
- Tabel / gamă: Este completat în mod prestabilit pe baza setului de date. Dacă datele dvs. nu au rânduri / coloane goale, Excel va identifica automat intervalul corect. Puteți modifica manual acest lucru dacă este necesar.
- Dacă doriți să creați tabelul pivot într-o anumită locație, sub opțiunea „Alegeți unde doriți să fie plasat raportul PivotTable”, specificați locația. Altfel, se creează o nouă foaie de lucru cu Tabelul pivot.
- Faceți clic pe OK.
De îndată ce faceți clic pe OK, se creează o nouă foaie de lucru cu Tabelul pivot în ea.
În timp ce tabelul pivot a fost creat, nu veți vedea date în acesta. Tot ce ați vedea este numele tabelului pivot și o instrucțiune cu o singură linie în stânga și câmpurile tabelului pivot în dreapta.
Acum, înainte de a intra în analiza datelor folosind acest tabel pivot, să înțelegem care sunt piulițele și șuruburile care fac un tabel pivot Excel.
Nuci și șuruburi ale unui tabel din pivot Excel
Pentru a utiliza eficient un tabel pivot, este important să cunoașteți componentele care creează un tabel pivot.
În această secțiune, veți afla despre:
- Pivot Cache
- Zona valorilor
- Zona Rândurilor
- Zona Coloane
- Zona de filtre
Pivot Cache
De îndată ce creați un tabel pivot folosind datele, se întâmplă ceva în backend. Excel face un instantaneu al datelor și le stochează în memoria sa. Acest instantaneu se numește cache pivot.
Când creați diferite vizualizări folosind un tabel pivot, Excel nu revine la sursa de date, ci folosește cache-ul pivot pentru a analiza rapid datele și a vă oferi rezumatul / rezultatele.
Motivul pentru care se generează o memorie cache pivot este pentru a optimiza funcționarea tabelului pivot. Chiar și atunci când aveți mii de rânduri de date, un tabel pivot este foarte rapid în rezumarea datelor. Puteți trage și plasa elemente în casetele rânduri / coloane / valori / filtre și va actualiza instantaneu rezultatele.
Notă: Un dezavantaj al cache-ului pivot este că crește dimensiunea registrului dvs. de lucru. Întrucât este o replică a datelor sursă, atunci când creați un tabel pivot, o copie a acestor date este stocată în memoria cache pivot.
Citeste mai mult: Ce este cache-ul pivot și cum se folosește cel mai bine.
Zona valorilor
Zona Valori este cea care deține calculele / valorile.
Pe baza setului de date afișat la începutul tutorialului, dacă doriți rapid să calculați vânzările totale pe regiuni în fiecare lună, puteți obține un tabel pivot așa cum se arată mai jos (vom vedea cum să creați acest lucru mai târziu în tutorial) .
Zona evidențiată în portocaliu este Zona valorilor.
În acest exemplu, are vânzările totale în fiecare lună pentru cele patru regiuni.
Zona Rândurilor
Anteturile din stânga zonei Valori fac zona Rânduri.
În exemplul de mai jos, zona Rânduri conține regiunile (evidențiate cu roșu):
Zona Coloane
Titlurile din partea de sus a zonei Valori fac zona Coloane.
În exemplul de mai jos, zona Coloane conține lunile (evidențiate cu roșu):
Zona de filtre
Zona Filtre este un filtru opțional pe care îl puteți utiliza pentru a detalia în continuare setul de date.
De exemplu, dacă doriți să vedeți doar vânzările pentru comercianții cu amănuntul Multiline, puteți selecta acea opțiune din meniul derulant (evidențiat în imaginea de mai jos), iar Tabelul pivot se va actualiza cu datele numai pentru comercianții cu amănuntul Multiline.
Analiza datelor folosind tabelul pivot
Acum, să încercăm să răspundem la întrebări folosind Tabelul pivot pe care l-am creat.
Click aici pentru a descărca eșantionul de date și a continua.
Pentru a analiza datele folosind un tabel pivot, trebuie să decideți cum doriți să arate rezumatul datelor în rezultatul final. De exemplu, este posibil să doriți toate regiunile din stânga și vânzările totale chiar lângă aceasta. Odată ce aveți în vedere această claritate, puteți pur și simplu să glisați și să fixați câmpurile relevante din tabelul pivot.
În secțiunea Pivot Tabe Fields, aveți câmpurile și zonele (așa cum este evidențiat mai jos):
Câmpurile sunt create pe baza datelor backend utilizate pentru tabelul pivot. Secțiunea Zone este locul unde plasați câmpurile și, în funcție de locul în care merge un câmp, datele dvs. sunt actualizate în Tabelul pivot.
Este un mecanism simplu de glisare și plasare, în care puteți trage pur și simplu un câmp și îl puteți pune într-una din cele patru zone. De îndată ce faceți acest lucru, acesta va apărea în Tabelul pivot în foaia de lucru.
Acum, să încercăm să răspundem la întrebările pe care le-a avut managerul dvs. folosind acest tabel pivot.
Q1: Care au fost vânzările totale în regiunea Sud?
Trageți câmpul Regiune în zona Rânduri și câmpul Venit în zona Valori. Ar actualiza automat tabelul pivot în foaia de lucru.
Rețineți că de îndată ce renunțați la câmpul Venituri din zona Valori, acesta devine Suma veniturilor. În mod implicit, Excel rezumă toate valorile pentru o anumită regiune și arată totalul. Dacă doriți, puteți schimba acest lucru în Număr, Mediu sau alte valori statistice. În acest caz, suma este ceea ce aveam nevoie.
Răspunsul la această întrebare ar fi 21225800.
Q2 Care sunt primii cinci vânzători cu amănuntul?
Trageți câmpul Client în zona Rând și câmpul Venit în zona valorilor. În cazul în care există alte câmpuri în secțiunea de zonă și doriți să îl eliminați, pur și simplu selectați-l și trageți-l din el.
Veți obține un tabel pivot așa cum se arată mai jos:
Rețineți că, în mod implicit, articolele (în acest caz clienții) sunt sortate în ordine alfabetică.
Pentru a obține primii cinci comercianți cu amănuntul, puteți pur și simplu să sortați această listă și să utilizați primii cinci nume de clienți. Pentru a face acest lucru:
- Faceți clic dreapta pe orice celulă din zona Valori.
- Accesați Sortare -> Sortare de la cel mai mare la cel mai mic.
Aceasta vă va oferi o listă sortată pe baza vânzărilor totale.
Î3: Cum s-a comparat performanța The Home Depot cu alți comercianți cu amănuntul din sud?
Puteți face o mulțime de analize pentru această întrebare, dar aici să încercăm și să comparăm vânzările.
Trageți câmpul Regiune în zona Rânduri. Acum trageți câmpul Client în zona Rânduri de sub câmpul Regiune. Când faceți acest lucru, Excel va înțelege că doriți să vă clasificați datele mai întâi în funcție de regiune și apoi de clienți din regiuni. Veți avea ceva așa cum se arată mai jos:
Acum trageți câmpul Venituri din zona Valori și veți avea vânzările pentru fiecare client (precum și regiunea generală).
Puteți sorta comercianții cu amănuntul pe baza cifrelor de vânzări urmând pașii de mai jos:
- Faceți clic dreapta pe o celulă care are valoarea vânzărilor pentru orice comerciant cu amănuntul.
- Accesați Sortare -> Sortare de la cel mai mare la cel mai mic.
Acest lucru ar ordona instantaneu toți comercianții cu amănuntul după valoarea vânzărilor.
Acum puteți să scanați rapid prin regiunea Sud și să identificați că vânzările Home Depot au fost de 3004600 și au funcționat mai bine decât patru comercianți cu amănuntul din regiunea Sud.
Acum există mai multe modalități de a jupui pisica. De asemenea, puteți pune Regiunea în zona Filtru și apoi selectați doar Regiunea Sud.
Click aici pentru a descărca datele eșantionului.
Sper că acest tutorial vă oferă o prezentare generală de bază a tabelelor pivot Excel și vă ajută să începeți cu acesta.
Iată câteva tutoriale pentru tabelul pivot care vă pot plăcea:
- Pregătirea datelor sursă pentru tabelul pivot.
- Cum se aplică formatarea condiționată într-un tabel pivot în Excel.
- Cum să grupați datele în tabelele pivot în Excel.
- Cum să grupați numerele în tabelul pivot în Excel.
- Cum se filtrează datele într-un tabel pivot în Excel.
- Utilizarea secționatoarelor în tabelul pivot Excel.
- Cum se înlocuiește celulele goale cu zero în tabelele pivot Excel.
- Cum se adaugă și se utilizează un câmp calculat din tabelul pivot Excel.
- Cum se reîmprospătează tabelul pivot în Excel.
