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 de 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 Excel din tabelul pivot 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ă secvențială de la tastatură 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, ele introduc funcții noi, dar nu renunță la vechile caracteristici (la fel ca MS cu win 8. A fost jalnic). Acest permite utilizatorului mai în vârstă să lucreze normal la versiuni noi, așa cum obișnuia să lucreze la versiuni mai vechi.
Dacă apăsați secvențial ALT, D și P pe tastatură, Excel se va deschide pentru a crea 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 mai 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).
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 Articol (selectați orice coloană, asigurați-vă că nu are o celulă goală între ele) în câmpurile de valoare.
Selectați Articol 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.
Sfat Pro: Ar trebui să vă verificați datele dacă sunt corecte sau nu. Dacă acest număr nu se potrivește cu datele, înseamnă că fie ați selectat o anumită gamă incorectă, fie că câmpul respectiv are celule goale.
Informații: Câmpul valoric implicit contorizează numărul de intrări dintr-o coloană dacă conține text și sume dacă câmpul conține doar valori. Puteți modifica acest lucru în setările câmpului valoric. Cum? Vă vom vedea mai târziu în acest tutorial pentru tabelul pivot.
Acum, că am selectat Data comandă în Valuesarea, se arată 42. Aceasta 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.
INFO: Indiferent de modificările pe care le efectuați în datele sursă, tabelul pivot va continua să lucreze la datele vechi până când îl reîmprospătați. Excel creează un cache pivot și un tabel pivot rulează pe acel cache. Când este reîmprospătat, vechea memorie cache este schimbată cu date noi.
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 meniul Subtotale.
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 forma tabulară opțiune. Î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 de articole coloane.
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 …
- Faceți clic dreapta oriunde pe suma coloanei Cost unitar din tabelul pivot
- Clic Setări câmp valoric
- 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 Analizați fila
2. Faceți clic pe Câmpuri, articole și seturi în 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 Media Cost sau orice îți place, excel nu te deranjează. În formulă caseta de intrare, scrieți și apăsați Bine.
= 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.
