PivotTables sunt una dintre cele mai puternice caracteristici ale programului Microsoft Excel. Acestea permit analizarea și sintetizarea unor cantități mari de date î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 foarte puțin de-a lungul anilor, însă 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 pe care le vedeți în acest articol.
O mică istorie
În primele zile ale programelor de calcul tabelar, Lotus 1-2-3 a condus hrănirea. Dominația 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 până în 2010, iar dominanța 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, denumită "Windows", a fost o poveste care nu ar fi decolat niciodată. Ei au refuzat să creeze o versiune de Windows a Lotus 1-2-3 (pentru câțiva ani, oricum), prezicând că versiunea lor DOS a software-ului a fost tot ceea ce oricine ar avea nevoie vreodată. 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ă un program (1-2-3) care nu-l avea. Această singură caracteristică, alături de o interpretare greșită a succesului Windows, a fost un gest de moarte pentru Lotus 1-2-3 și începutul succesului Microsoft Excel.
Înțelegerea tabelelor pivot
Deci, ce este un PivotTable, exact?
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ă exact informațiile 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 doar orice date - trebuie să fie brut date, neprelucrate anterior (nesimarizate) - de obicei o listă de un fel. Un exemplu în acest sens ar putea fi lista tranzacțiilor de vânzare din cadrul unei companii în ultimele șase luni.
Examinați datele prezentate mai jos:
Observați că aceasta este nu date neprelucrate. De fapt, este deja un rezumat de un fel. În celula B3 vedem 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 în 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 utilizând PivotTables, caz în care ar fi fost nevoie doar de câteva secunde. Să vedem 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 lunar de vânzări 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 în 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 de mașini al companiei dvs.
Deci, vom începe Excel ... și vom încărca o astfel de listă ...
Odată ce lista este deschisă în Excel, suntem gata să începem să creăm PivotTable.
Faceți clic pe o singură celulă din listă:
Apoi, de la Introduce , faceți clic pe Masă rotativă icon:
Creați PivotTable , care vă pune două întrebări: la ce date ar trebui să se bazeze noul dvs. PivotTable și unde ar trebui să fie creat? Deoarece 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 a unei anumite surse de date externe, cum ar fi tabelul bazei de date Access sau chiar o tabelă de baze de date MS-SQL Server. De asemenea, trebuie să selectăm dacă doriți 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:
Apare și o altă casetă: Listă câmp PivotTable. 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 de sus 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 este cu siguranță cel 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ă analizăm PivotTable în sine, într-adevăr, găsim 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 noastre de eșantion, 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, am putea decide că dorim să vedem un rezumat al datelor noastre în cazul în care avem rândul rândului pentru fiecare dintre diferiții vânzători din compania noastră și un total pentru fiecare. Pentru a realiza acest lucru, trebuie doar să trageți câmpul "Persoană de vânzări" în câmpul Etichetele rândului 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 sursă. Lucrurile importante sunt deja învățate! Pentru restul articolului, vom examina câteva moduri în care pot fi create mai multe pivoturi complexe și modalități prin care aceste PivotTables 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:
Începând să ajungă foarte rece!
Să facem asta Trei- tabelul 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 agentului de vânzări față de metoda de plată pentru toate pachete sau, cu câteva clicuri, modificați-o pentru a afișa aceeași defalcare pentru pachetul "Sunseekers":
Și dacă vă gândiți la acest lucru în mod corect, PivotTable noastre este acum tridimensional. Să continuăm personalizarea ...
Dacă se va dovedi, să zicem că nu vrem decât să vedem carte de credit și 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 dezlipiți "Cash":
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 tentație ar fi să faci ceea ce suntem obișnuiți să facem în astfel de circumstanțe și pur și simplu să selectăm întreaga tabelă (sau întreaga fișă de lucru) și să folosim butoanele standard de formatare a numerelor de pe bara de instrumente pentru a finaliza formatarea. Problema cu această abordare este că, dacă schimbați vreodată structura PivotTable în viitor (care este cu 99% probabil), 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 caseta și faceți clic pe el. Apare un meniu. Am selectat Setările câmpului de valori ... din meniu:
Setări câmp valoare apare caseta.
Apasă pe Formatul numărului butonul și standardul Formați caseta cu celule apare:
De la Categorie listă, selectați (să zicem) 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 formatate ca sume î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, trageți în jos 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 din 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ă pe zi, săptămână, lună, an etc. Să vedem cum se face acest lucru.
Mai întâi, să eliminăm coloana "Metoda de plată" din 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 remedia 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 al vânzărilor 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).
Păstrați din nou lucrurile simple, să vedem cum să complotăm în medie mai degrabă decât valorile sumare.
Mai întâi, dați clic pe "suma sumei" ș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 selectați In medie:
În timp ce suntem aici, hai să schimbăm Nume particularizat, de la "Media Suma" la ceva mai concis. Introduceți ceva de genul "Avg":
Clic O.Kși vedeți cum arată. Observați că toate valorile se modifică din sumele însumate la medii, iar titlul tabelului (celula din partea stângă sus) sa schimbat la "Avg":
Dacă ne place, putem avea chiar și sume, valori medii și numărare (contează câte vânzări au fost) pe același PivotTable!
Iată pașii pentru a obține ceva de genul ăsta în loc (pornind de la un PivotTable necompletat):
câmp de la Etichete de coloană la Etichetele rânduluiIată ce sfârșim:
Total, media și conta pe același PivotTable!
Concluzie
Există multe, multe alte caracteristici și opțiuni pentru PivotTables create de Microsoft Excel - mult 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). Cititorii curajoși și / sau geeky pot explora și mai ușor PivotTables: Pur și simplu faceți clic dreapta pe aproape totul și vedeți ce opțiuni vă stau la dispoziție. 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 a utilizatorilor vechi DOS din Lotus 1-2-3 nu a 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 de lucru Excel
