Lucrul cu tabele pivot în Microsoft Excel

PivotTables sunt una dintre cele mai puternice caracteristici ale programului Microsoft Excel. Ele permit cantități mari de date să fie analizate și sintetizate în doar câteva clicuri de mouse. În acest articol, explorăm PivotTables, înțelegeți ce sunt și aflați cum să le creați și să le personalizați.

Notă: Acest articol este scris utilizând Excel 2010 (Beta). Conceptul de PivotTable sa schimbat puțin de-a lungul anilor, dar metoda de creare a unui singur element sa schimbat în aproape fiecare iterație a programului Excel. Dacă utilizați o versiune de Excel care nu este 2010, așteptați ecrane diferite de cele afișate în acest articol.

O mică istorie

În primele zile ale programelor de calcul tabelar, Lotus 1-2-3 a condus hrănirea. Dominanța sa a fost atât de completă încât oamenii au crezut că este o pierdere de timp pentru ca Microsoft să se deranjeze să-și dezvolte propriul software de calcul tabelar (Excel) pentru a concura cu Lotus. Flash-forward către 2010 și dominarea Excel a pieței de calcul tabelar este mai mare decât Lotus a fost vreodată, în timp ce numărul de utilizatori care rulează Lotus 1-2-3 se apropie de zero. Cum sa întâmplat asta? Ce a provocat o astfel de inversare dramatică a averilor?

Analiștii din industrie au pus-o pe doi factori: În primul rând, Lotus a decis că această nouă platformă GUI, numită "Windows", a fost o poveste care nu se va desfășura niciodată. Ei au refuzat să creeze o versiune de Windows a Lotus 1-2-3 (de câțiva ani, oricum), prezicând că versiunea lor DOS a software-ului a fost tot ceea ce oricine ar avea vreodată nevoie. Microsoft, desigur, a dezvoltat Excel exclusiv pentru Windows. În al doilea rând, Microsoft a dezvoltat o caracteristică pentru Excel pe care Lotus nu a oferit-o în 1-2-3, și anume Tabele pivot.  Caracteristica PivotTables, exclusivă pentru Excel, a fost considerată atât de uimitor de utilă încât oamenii erau dispuși să învețe un întreg pachet software nou (Excel) mai degrabă decât să rămână cu un program (1-2-3) care nu-l avea. Această singură caracteristică, alături de aprecierea greșită a succesului Windows, a fost gheața de moarte pentru Lotus 1-2-3 și începutul succesului Microsoft Excel.

Înțelegerea tabelelor pivot

Ce este exact un PivotTable?

Puneți simplu, un PivotTable este un rezumat al unor date, create pentru a permite o analiză ușoară a datelor menționate. Dar, spre deosebire de un sumar creat manual, Excel PivotTables sunt interactive. Odată ce ați creat unul, îl puteți schimba cu ușurință dacă nu oferă informațiile precise despre datele pe care le-ați sperat. În câteva clicuri rezumatul poate fi "pivotat" - rotit astfel încât titlurile coloanelor să devină titluri de rând și invers. Mai sunt multe de făcut. Mai degrabă decât să încercăm să descriem toate caracteristicile PivotTables, le vom demonstra pur și simplu ...

Datele pe care le analizați utilizând un PivotTable nu pot fi date doar orice datele - trebuie să fie brut date, neprelucrate anterior (nesimarizate) - de obicei o listă de un fel. Un exemplu în acest sens ar putea fi lista de tranzacții de vânzare din cadrul unei companii în ultimele șase luni.

Examinați datele prezentate mai jos:

Observați că asta este nu date neprelucrate. De fapt, este deja un rezumat de un fel. În celula B3 putem vedea 30.000 de dolari, ceea ce se pare că reprezintă totalul vânzărilor lui James Cook pentru luna ianuarie. Deci, unde sunt datele brute? Cum am ajuns la cifra de 30.000 de dolari? Unde este lista originală a tranzacțiilor de vânzare din care a fost generată această cifră? Este clar că undeva, cineva trebuie să se fi confruntat cu dificultatea de a concura toate tranzacțiile de vânzare din ultimele șase luni în rezumatul pe care îl vedem mai sus. Cât timp crezi că ți-a luat asta? O oră? Zece?

Probabil că da. Vedeți, foaia de calcul de mai sus este de fapt nu un PivotTable. A fost creat manual din datele brute stocate în altă parte, iar într-adevăr, a fost nevoie de câteva ore pentru a fi compilate. Totuși, este exact acel rezumat ar putea să fie create folosind PivotTables, caz în care ar fi fost nevoie doar de câteva secunde. Să aflăm cum ...

Dacă ar fi trebuit să urmărim lista originală a tranzacțiilor de vânzare, s-ar putea să pară așa:

Este posibil să fiți surprins să aflați că, utilizând caracteristica PivotTable din Excel, putem crea un rezumat de vânzări lunar similar celui de mai sus în câteva secunde, cu doar câteva clicuri de mouse. Putem face acest lucru - și mult mai mult!

Cum se creează un PivotTable

Mai întâi, asigurați-vă că aveți unele date brute într-o foaie de lucru din Excel. O listă de tranzacții financiare este tipică, dar poate fi o listă cu aproape orice: datele de contact ale angajatului, colecția dvs. de CD-uri sau cifrele privind consumul de combustibil pentru parcul auto.

Deci, vom începe Excel ... și vom încărca o astfel de listă ...

Odată ce lista este deschisă în Excel, suntem gata să începem crearea PivotTable.

Faceți clic pe o singură celulă din listă:

Apoi, de la Introduce , faceți clic pe Masă rotativă icon:

Creați pivotul , care vă pune două întrebări: la ce date ar trebui să se bazeze noul dvs. PivotTable și unde ar trebui să fie creat? Pentru că am făcut deja clic pe o celulă din listă (în pasul de mai sus), întreaga listă din acea celulă este deja selectată pentru noi ($ A $ 1: $ G $ 88 alineatul pe plăţile foaie, în acest exemplu). Rețineți că am putea selecta o listă în orice altă regiune a oricărei alte foi de lucru sau chiar o sursă de date externă, cum ar fi o tabelă de bază de date Access sau chiar o tabelă de baze de date MS-SQL Server. De asemenea, trebuie să selectăm dacă dorim ca noul nostru PivotTable să fie creat pe un a nou foaie de lucru, sau pe existent unu. În acest exemplu vom selecta a nou unu:

Noua foaie de lucru este creată pentru noi și un tabel PivotTable este creat pe foaia de lucru:

În plus, apare o altă casetă: PivotTable Tabel Lista.  Această listă de câmpuri va fi afișată ori de câte ori faceți clic pe orice celulă din PivotTable (de mai sus):

Lista câmpurilor din partea superioară a casetei este de fapt colecția de rubrici din coloana originală de date brute. Cele patru casete goale din partea inferioară a ecranului ne permit să alegem modul în care ne-ar plăcea ca PivotTable să rezume datele brute. Până în prezent, nu există nimic în acele casete, așa că PivotTable este necompletat. Tot ce trebuie să faceți este să trageți câmpurile din lista de mai sus și să le aruncați în casetele inferioare. Un PivotTable este apoi creat automat pentru a se potrivi instructiunilor noastre. Dacă greșim, trebuie doar să tragem câmpurile înapoi de unde au provenit și / sau să tragă nou câmpurile pentru a le înlocui.

valori caseta este, fără îndoială, cea mai importantă dintre cele patru. Câmpul care este tras în această casetă reprezintă datele care trebuie să fie rezumate într-un fel (prin însumare, mediere, găsirea valorii maxime, minime etc.). Este aproape întotdeauna numeric date. Un candidat perfect pentru această căsuță în datele noastre de eșantionare este câmpul / coloana "Sumă". Să tragem câmpul în valori cutie:

Observați că (a) câmpul "Sumă" din lista de câmpuri este acum bifat și "Suma sumei" a fost adăugată la valori , indicând că s-a însumat coloana cu suma.

Dacă vom examina PivotTable în sine, într-adevăr vom găsi suma tuturor valorilor "Sumă" din foaia de lucru de date brute:

Am creat primul nostru PivotTable! Handy, dar nu impresionant. Probabil că avem nevoie de o mai bună înțelegere a datelor noastre decât de asta.

Referindu-ne la datele din eșantionul nostru, trebuie să identificăm una sau mai multe rubrici ale coloanelor pe care am putea să le utilizăm pentru a împărți acest total. De exemplu, putem decide că dorim să vedem un rezumat al datelor noastre în cazul în care avem rândul rândului pentru fiecare dintre vânzătorii diferiți din compania noastră și un total pentru fiecare. Pentru a realiza acest lucru, trebuie doar să trageți câmpul "Agent de vânzări" în câmpul Etichete rând cutie:

Acum, în cele din urmă, lucrurile încep să devină interesante! Pivotul nostru începe să se formeze ... .

Cu câteva clicuri am creat un tabel care ar fi trebuit mult timp să faceți manual.

Și ce altceva putem face? Într-un sens, PivotTable-ul nostru este complet. Am creat un rezumat util al datelor noastre sursă. Lucrurile importante sunt deja învățate! Pentru restul articolului, vom examina câteva moduri în care pot fi create mai multe tabele pivot complexe și modalități prin care acele tabele pivotare pot fi personalizate.

În primul rând, putem crea o Două-tabel dimensional. Hai să facem asta folosind "Metoda de plată" ca titlu de coloană. Trageți pur și simplu rubrica "Metodă de plată" la Etichete de coloană cutie:

Care arată astfel:

Începe să ajungă foarte rece!

Să facem asta Trei-tabel dimensional. Cum ar putea arăta o astfel de masă? Ei bine, să vedem…

Trageți coloana / pachetul "Pachet" la Filtrați raportul cutie:

Observați unde se termină ... .

Acest lucru ne permite să ne filtram raportul pe baza căruia a fost achiziționat "pachetul de vacanță". De exemplu, putem vedea defalcarea metodei de vânzări față de metoda de plată pentru agentul de vânzări toate pachete sau, cu câteva clicuri, schimbați-l pentru a afișa aceeași defalcare pentru pachetul "Sunseekers":

Și dacă vă gândiți la asta în mod corect, PivotTable noastre este acum tridimensional. Să păstrăm personalizarea ...

Dacă se va dovedi, să zicem, că nu vrem decât să vedem cec și card de credit tranzacții (adică nu există tranzacții în numerar), apoi putem deselecta elementul "Numerar" din titlurile coloanelor. Dați clic pe săgeata drop-down de lângă Etichete de coloană, și deblocați "Numerar":

Să vedem cum arată ... După cum puteți vedea, "Cash" este plecat.

Formatarea

Acesta este, evident, un sistem foarte puternic, dar până acum rezultatele par foarte clare și plictisitoare. Pentru început, numerele pe care le însumăm nu arată ca sumele dolarului - doar numere vechi. Să rectificăm asta.

O tentatie ar fi sa faceti ceea ce suntem obisnuiti sa faceti in astfel de circumstante si pur si simplu selectati intregul tabel (sau intreaga foaie de lucru) si folositi butoanele standard de formatare a numerelor de pe bara de unelte pentru a finaliza formatarea. Problema cu această abordare este că, dacă schimbați vreodată structura PivotTable în viitor (care este probabil 99%), atunci acele formate de numere vor fi pierdute. Avem nevoie de o modalitate care să le facă (semi-) permanente.

Mai întâi, găsim intrarea "suma sumei" în valori casetă și faceți clic pe el. Apare un meniu. Am selectat Setările câmpului de valori ... din meniu:

Setări câmp valoare caseta apare.

Apasă pe Formatul numărului butonul și standardul Formați caseta cu celule apare:

De la Categorie listă, selectați (de exemplu) Contabilitate, și scade numărul de zecimale la 0. Faceți clic pe O.K de câteva ori pentru a reveni la PivotTable ...

După cum puteți vedea, numerele au fost corect formate ca sumă în dolari.

În timp ce discutăm despre formatare, să formăm întregul PivotTable. Există câteva moduri de a face acest lucru. Să folosim o simplă ...

Apasă pe PivotTable Tools / Design fila:

Apoi apăsați pe săgeata din dreapta-jos a PivotTable Styles pentru a vedea o vastă colecție de stiluri încorporate:

Alegeți unul care se adresează și examinați rezultatul în PivotTable:

Alte optiuni

Putem lucra și cu date. Acum, de obicei, există multe, multe date într-o listă de tranzacții, cum ar fi cea cu care am început. Dar Excel oferă opțiunea de a grupa elementele de date împreună prin zi, săptămână, lună, an etc. Să vedem cum se face acest lucru.

Mai întâi, să eliminăm din coloana "Metoda de plată" Etichete de coloană (trageți pur și simplu înapoi în lista de câmpuri) și înlocuiți-o cu coloana "Data rezervată":

După cum puteți vedea, acest lucru face ca PivotTable să fie instantaneu inutil, oferindu-ne o coloană pentru fiecare dată în care a avut loc o tranzacție - o masă foarte largă!

Pentru a repara acest lucru, faceți clic dreapta pe orice dată și selectați Grup… din meniul contextual:

Se afișează caseta de grupare. Am selectat Luni și faceți clic pe OK:

Voila! A mult tabel mai util:

(De altfel, acest tabel este practic identic cu cel prezentat la începutul acestui articol - rezumatul original de vânzări care a fost creat manual.)

Un alt lucru interesant este să puteți avea mai mult de un set de titluri de rând (sau titluri de coloane):

... care arată așa ... .

Puteți face un lucru similar cu titlurile coloanelor (sau chiar filtrele de raport).

Ținând din nou lucrurile simple, să vedem cum să complotăm în medie valorile, mai degrabă decât valorile însumate.

Mai întâi, dați clic pe "suma sumelor" și selectați Setările câmpului de valori ... din meniul contextual care apare:

În Sumarul câmpului de valoare prin listă în Setări câmp valoare caseta, selectați In medie:

În timp ce suntem aici, hai să schimbăm Nume particularizat, de la "Media Suma" la ceva puțin mai concis. Introduceți ceva de tipul "Avg":

Clic O.K, și vezi cum arată. Observați că toate valorile se schimbă de la totaluri însumate la medii, iar titlul tabelului (celula din stânga sus) sa schimbat la "Avg":

Dacă ne place, putem avea chiar și sume, valori medii și numărare (contează câte vânzări erau acolo) pe același PivotTable!

Iată pașii pentru a obține ceva de genul acesta (pornind de la un PivotTable necompletat):

1. Trageți "Salesperson" în Etichete de coloană
2. Trageți câmpul "Sumă" în câmpul " valori de trei ori
3. Pentru primul câmp "Sumă", schimbați numele personalizat la "Total" și este format de număr Contabilitate (0 zecimale)
4. Pentru al doilea câmp "Sumă", schimbați numele personalizat la "Mediu", funcția sa la In medie și este formatul numărului la Contabilitate (0 zecimale)
5. Pentru cel de-al treilea câmp "Sumă", schimbați numele acestuia la "Count" și funcția sa la Numara
6. Glisați creatorul automat creat câmp de la Etichete de coloană la Etichete rând

Iată ce ajungem:

Total, medie și conta pe același PivotTable!

Concluzie

Există multe, multe alte caracteristici și opțiuni pentru PivotTables creat de Microsoft Excel - prea multe pentru a le afișa într-un articol ca acesta. Pentru a acoperi în totalitate potențialul PivotTables, ar fi necesară o carte mică (sau un site web mare). De asemenea, cititorii curajosi si / sau geeky pot explora PivotTables mai usor: pur si simplu clic dreapta pe aproape totul si vezi ce optiuni devin disponibile. Există, de asemenea, cele două panglici: Instrumente / Opțiuni PivotTable și Proiecta.  Nu contează dacă faceți o greșeală - este ușor să ștergeți PivotTable și să începeți din nou - o posibilitate că utilizatorii vechi DOS din Lotus 1-2-3 nu au avut niciodată.

Dacă lucrați în Office 2007, vă recomandăm să citiți articolul nostru despre cum să creați un PivotTable în Excel 2007.

Am inclus un registru de lucru Excel pe care îl puteți descărca pentru a vă exersa abilitățile PivotTable. Ar trebui să funcționeze cu toate versiunile din Excel începând cu anul 97.

Descărcați manualul nostru de practică Excel