Tabelele pivot sunt una dintre cele mai puternice caracteristici din Excel. Chiar dacă sunteți un începător, puteți extrage cantități mari de date în informații utile. Tabelul pivot vă poate ajuta să creați rapoarte în câteva minute. Analizați datele curate cu ușurință și, dacă datele nu sunt curate, vă poate ajuta să vă curățați datele. Nu vreau să te plictisesc, așa că hai să sări în el și să explorăm.
Cum se creează un tabel pivot
E simplu. Doar selectați-vă datele. Accesați Insert. Faceți clic pe tabelul pivot și gata.
Dar asteapta. Înainte de a crea un tabel pivot, asigurați-vă că toate coloanele au un antet.
Dacă vreun antet de coloană este lăsat necompletat, tabelul pivot nu va fi creat și va trece printr-un mesaj de eroare.
Cerința 1: Toate coloanele ar trebui să aibă un antet pentru a începe cu tabelele pivot în Excel
Ar trebui să aveți datele organizate cu titlu adecvat. După ce îl aveți, puteți insera tabelul pivot.
Introduceți tabelul pivot din panglică
Pentru a insera un tabel pivot din meniu, urmați acești pași:
1. Selectați intervalul de date
2. Accesați fila Inserare
3. Faceți clic pe pictograma Tabelului pivot.
4. Va apărea caseta de opțiuni Creare tabel pivot.
5. Aici puteți vedea intervalul de date pe care l-ați selectat. Dacă credeți că acesta nu este intervalul pe care doriți să îl selectați, redimensionați-l direct de aici, în loc să vă întoarceți și să selectați din nou datele.
6. Apoi, puteți selecta unde doriți tabelul pivot. Vă recomand să utilizați noua foaie de lucru, dar puteți utiliza și foaia de lucru curentă. Doar definiți locația în caseta Locație.
7. Acum, dacă ați terminat setările, apăsați butonul OK. Veți avea tabelul pivot într-o foaie nouă. Doar selectați câmpurile pentru rezumate. Vom vedea cum creăm un rezumat al datelor folosind tabelul pivot, dar mai întâi să clarificăm elementele de bază. În acest tutorial din tabelul pivot Excel, veți afla mai multe decât vă așteptați.
Introduceți comanda rapidă a tabelului pivot (Alt> N> V)
Aceasta este o comandă rapidă de tastatură secvențială este pentru a deschide Creați Masă rotativă caseta de optiuni.
Apăsați butonul Alt și eliberați-l. Lovit N și eliberați-l. Lovit V și eliberați-l. Se va deschide caseta de opțiuni Creare tabel pivot.
Acum pur și simplu urmați procedura de mai sus pentru a crea un tabel pivot în Excel.
Inserați comanda rapidă a tabelului pivot utilizând comanda rapidă Excel veche (Alt> D> P)
Un lucru care îmi place cel mai mult la Microsoft Excel este că în fiecare nouă versiune de Excel introduc noi funcții, dar ele nu aruncați vechile caracteristici (cum a făcut MS cu victoria 8. A fost jalnic). Acest permite utilizatorilor vechi să lucreze normal la versiunile noi, așa cum obișnuiau să lucreze la versiunile mai vechi.
Dacă apăsați secvențial ALT, D și P pe tastatură, Excel se va deschide creați un expert de tabel pivot.
Selectați opțiunea potrivită. Opțiunea selectată din captura de ecran de mai sus ne va conduce la crearea unui tabel pivot așa cum am creat înainte.
Apăsați Enter sau faceți clic pe Următorul dacă doriți să verificați intervalul selectat.
Apăsați Enter din nou.
Selectați o foaie de lucru nouă sau oriunde doriți ca tabelul pivot să apese pe Enter. Și s-a făcut.
Creați rapoarte utilizând tabele pivot
Acum știți cum să inserați un tabel pivot. Să ne pregătim pentru a face rapoarte folosind tabele pivot în câteva minute.
Avem datele pentru comanda staționară.
Câmpurile de coloană sunt:
Data comandă: Data comenzii (evident)
Regiune: Regiunea de ordine din țară
Client: Numele clientului (ce altceva poate fi)
Articol: Articol comandat
Unitate: Numărul de unități ale unui articol comandat
Cost unitar: Cost pe unitate
Total: Costul total al comenzii (unitate * cost unitar).
Pentru a crea rapoarte folosind tabele pivot vom folosi
Câmpuri din tabelul pivot: Conține lista numelor coloanelor din datele dvs.
Zonele pivot: Aceste 4 zone sunt utilizate pentru a vă arăta datele într-un mod manierat.
FILTRE: Puneți aici câmpuri din care doriți să aplicați filtre în raport.
COLOANE: Puneți câmpurile dorite aici în coloanele din raport: (Este mai bine să arătați decât să explicați)
RÂNDURI: Trageți câmpurile pe care doriți să le arătați ca în imaginea de mai sus, am arătat Regiunea în RUNURI.
VALORI: Selectați un câmp pentru a obține Număr, sumă, medie, procent (și multe altele) etc. pe care veți dori să le vedeți.
Acum, folosind informațiile de mai sus, am pregătit acest raport rapid pivot care arată din ce regiune câte comenzi sunt plasate pentru fiecare articol.
Acum că vă înțelegeți datele și câmpurile pivot (la urma urmei sunteți deștepți), să răspundem rapid la câteva întrebări legate de aceste date folosind tabelul pivot.
Q1. Câte comenzi există?
Tabelul pivot este inițial gol, așa cum se arată în imaginea de mai jos.
Trebuie să selectați câmpurile (numele coloanelor) în zonele corespunzătoare pentru a vedea rezumatul sau detaliile acelui câmp.
Acum, pentru a răspunde la întrebarea de mai sus, voi selecta Element (selectați orice coloană, asigurați-vă că nu are o celulă goală între ele) în câmpurile de valoare.
Selectați Element din lista de câmpuri și trageți-l în câmpul Valoare.
Avem răspunsul nostru. Tabelele pivot îmi spun că există un total de 43 de comenzi. Asta e corect.
Acum, că am selectat Data comandă în zona Valori, se afișează 42. Acest mijloace Data comandă are o celulă goală de cand știm că numărul total de comenzi este 43.
Identificați datele neregulate folosind tabele pivot și curățați-le.
Tabelul pivot vă poate ajuta să găsiți informații incorecte în date.
De cele mai multe ori datele noastre sunt pregătite de operatori de introducere a datelor sau de utilizatori care sunt de obicei neregulați și au nevoie de o curățare pentru a pregăti un raport și o analiză exacte.
Întotdeauna trebuie să vă curățați și să vă pregătiți datele într-un mod manierat, înainte de a pregăti orice rapoarte. Dar, uneori, numai după pregătirea raportului, aflăm că datele noastre prezintă o anumită neregulă. Haide, îți voi arăta cum …
Q2: Spuneți numărul de comenzi din fiecare regiune
Acum, pentru a răspunde la această întrebare:
Selectați Regiune și trageți-l în Rânduri Zona și Articol la Valori Zonă.
Vom obține date împărțite între regiune și înțelepciune. Putem răspunde din ce regiune câte comenzi au venit.
În total, există 4 regiuni în datele noastre, conform tabelului pivot. Dar așteaptă, observă asta Central și Centrle regiune. Știm că Centrle ar trebui să fie Central. Există o neregulă. Trebuie să mergem la datele noastre și să facem curățarea datelor.
Pentru a curăța datele din câmpul regiunii, filtrăm numele incorect al regiunii (Centrle) și îl corectăm (Central).
Acum reveniți la datele dvs. pivot.
Faceți clic dreapta oriunde pe tabelul pivot și faceți clic pe Reîmprospătare.
Raportul dvs. a fost actualizat.
Acum, puteți vedea că există doar 3 regiuni de fapt.
Pivotați formatarea raportului cu rânduri clasificate.
Uneori veți avea nevoie de rapoarte precum imaginea de mai sus. Acest lucru face mai ușor să vă vedeți datele într-un mod structurat. Puteți spune cu ușurință din ce regiune câte articole sunt comandate. Să vedem cum puteți face acest lucru.
Mutare Regiune și Articol la RÂNDURI zonă. Asigurați-vă că regiunea este în partea de sus și elementele în partea de jos, așa cum se arată în imagine.
Trage Articol pentru Valoare Zonă.
Drept urmare, veți primi acest raport.
Se va face. Dar uneori șeful tău vrea să raporteze sub formă de tabel, fără subtotale. Pentru a face acest lucru, trebuie să ne formatăm tabelul pivot.
Eliminați subtotalurile din tabelul pivot
Urmați acești pași:
1. Faceți clic oriunde pe tabelul pivot.
2. Mergeți la Proiecta Tab.
3. Faceți clic pe subtotalurile meniul.
4. Faceți clic pe Nu afișați subtotale.
Puteți vedea că nu există subtotale acum.
Amenda. Dar încă nu este sub formă de tabel. Regiunile și elementele sunt afișate într-o singură coloană. Arată-le separat.
Creați un tabel din tabel pivot
Acum, pentru a afișa regiuni și articole în diferite coloane, urmați acești pași:
1. Faceți clic oriunde pe tabelul pivot
2. Accesați fila Design
3. Faceți clic pe Aspect raport.
4. Faceți clic pe Afișare pentru opțiunea Formular tabular. În cele din urmă, veți avea această imagine sofisticată a raportului dvs.
Acum știm numărul total de comenzi plasate pentru fiecare articol din fiecare regiune. Este arătat în conte a coloanelor Elemente.
Vă rugăm să schimbați numele coloanei în Comenzi.
Acum arată mai bine.
Î3: Câte unități din fiecare articol sunt comandate?
Pentru a răspunde la această întrebare avem nevoie de o sumă de unități. Pentru a face acest lucru, trebuie doar să mutați câmpul Unități în valori. Acesta va însuma automat numărul de unități pentru fiecare articol. Dacă o coloană din tabelul pivot conține numai valori, în mod implicit, tabelul pivot arată suma acestor valori. Dar poate fi modificat dintr-o setare de câmp valoric. Cum? Îți voi arăta pe acesta din urmă.
Setări pentru câmpul valorii tabelului pivot
Q4: Prețul mediu al fiecărui articol?
În datele noastre eșantion, prețul unui articol este diferit pentru comenzi diferite. De exemplu, consultați Bindere.
Vreau să știu costul mediu al fiecărui articol din tabelul pivot. Pentru a afla acest lucru, trageți Costul unitar în câmpul valoare. Se va afișa suma costului unitar.
Nu vrem Suma costului unitar, noi vrem Media costului unitar. Pentru a face acest lucru …
1. Faceți clic dreapta oriunde pe suma coloanei Cost unitar din tabelul pivot
2. Faceți clic pe Setări câmp valoric
3. Pe baza opțiunilor disponibile, Selectați In medie și apăsați OK.
În cele din urmă, veți avea acest raport pivot:
Câmpuri calculate din tabelul pivot
Una dintre cele mai utile funcții ale tabelului pivot este câmpurile sale calculate. Câmpurile calculate sunt câmpuri care sunt obținute prin unele operații pe coloanele disponibile.
Să înțelegem cum să inserăm câmpuri calculate în tabelul pivot cu un exemplu:
Pe baza datelor noastre, am pregătit acest raport.
Iată, avem Suma de unități și Cost total. Tocmai am mutat coloana totală în câmpul Valori și apoi am redenumit-o în Cost total. Acum vreau să știu prețul mediu al unei unități din fiecare articol pentru fiecare regiune. Și asta ar fi:
Preț mediu = cost total / unități totale
Să introducem un câmp în tabelul pivot care arată prețul mediu al fiecărei regiuni de articole înțelept:
Urmați acești pași pentru a insera un câmp calculat în tabelul pivot
1. Faceți clic oriunde pe tabelul pivot și accesați fila Analize
2. Faceți clic pe Câmpuri, articole și seturi din grupul de calcul.
3. Faceți clic pe Câmpuri calculate.
Veți vedea această casetă de intrare pentru câmpul dvs. de calcul:
4. În caseta de introducere a numelui, scrieți Costul mediu sau orice vă place, Excel nu se va deranja. În caseta de introducere a formulelor, scrieți și apăsați OK.
= Total / Unități
Puteți scrie acest lucru manual de la tastatură sau puteți face dublu clic pe numele câmpurilor listate în zona Câmpuri pentru a efectua operațiuni.
5. Aveți câmpul calculat adăugat acum la tabelul pivot. Este denumită Suma costului mediu, dar nu este o sumă. Excel execută doar funcția implicită pentru a denumi coloana (ca un ritual). Redenumiți această coloană și limitați cifrele zecimale afișate.
Și acolo ai un câmp calculat. Îl poți face cât de complex vrei. De exemplu, am luat această operație simplă medie.
Gruparea în tabelul pivot
Aveți pregătit acest raport pivot.
Acum vreau ca acest raport să fie divizat anual. Vedeți instantaneul de mai jos.
Avem o coloană despre Data comenzii. Mutați câmpul OrderDate în rândurile din partea de sus.
Nu seamănă nimic cu raportul solicitat. Trebuie să avem date de grup în funcție de an.
Acum, pentru a grupa un câmp în tabelul pivot Excel, urmați acești pași:
1. Faceți clic dreapta pe câmpul pe care doriți să-l grupați.
2. Faceți clic pe Grup. Veți avea această casetă de opțiuni pentru personalizare. Deoarece aceasta este o coloană de date, Excel ne arată gruparea în consecință. Puteți alege data de început și de încheiere.
3. Alegeți ani de zile și apăsați OK. Ați realizat gruparea anuală în Tabelul pivot Excel.
Feliatoare ale tabelului pivot
Slicers au fost introduse în Excel 2010 ca un addIn. În Excel 2013 și 2016 este disponibil în mod implicit la fel ca filtrele.
Feliile nu sunt altceva decât filtre. Spre deosebire de filtre, Slicers afișează toate opțiunile disponibile chiar în fața ta. Îți face tabloul de bord mai interactiv.
Cum se adaugă tăietoare în tabelul pivot, Excel 2016 și 2013
Slicers pentru tabel pivot este ușor de adăugat. Urmați acești pași:
1. Faceți clic oriunde pe tabelul pivot și accesați fila Analize.
2. Faceți clic pe Insert Slicer. Veți avea o listă de câmpuri pentru datele dvs. Selectați câte doriți.
3. Pentru acest exemplu, selectați Regiune și apăsați OK.
Ați adăugat Slicer la raport. Acum aplicați filtrul cu un singur clic.
Introduceți cronologie în Excel 2016 și 2013
Aceasta este una dintre funcționalitățile mele preferate Tabelele pivot Excel. Această nouă funcționalitate funcționează numai cu date. Folosind aceasta, puteți selecta vizual un interval de timp pentru a vă filtra datele.
Pentru a insera un cursor Cronologie, urmați acești pași:
Cum se adaugă cronologie în tabelul pivot, Excel 2016 și 2013
1. Faceți clic oriunde pe tabelul pivot și accesați fila Analize.
2. Faceți clic pe Insert Timeline din Filter Group. Toate coloanele care conțin valori de timp în date provenite vor fi listate într-o casetă de opțiuni din care să alegeți. Aici avem doar unul. Deci da…
2. Alegeți opțiunile și apăsați Enter sau faceți clic pe OK. S-a terminat și aveți cronologia tabelului pivot chiar în fața dvs.
Puteți alege să o afișați zilnic, lunar, trimestrial sau anual. Am ales lunar aici.
În acest articol am abordat cele mai importante și utile funcționalități ale tabelului pivot. Am explorat noi caracteristici ale tabelului pivot în Excel 2016 și 2013. Am aflat despre utilizarea clasică a unui tabel pivot care a fost rulat în Excel 2007, 2010 și mai vechi. Sunt încă utile. Dacă nu ați găsit răspunsul dvs. legat de tabelul pivot aici, întrebați în secțiunea de comentarii.
Există multe alte funcții care încă nu sunt explicate. Aflăm funcția avansată a tabelului pivot în articolul următor. Până atunci Excel la toate.