Cuprins
Tabele pivot este unul dintre cele mai puternice instrumente din Excel. Acestea vă permit să analizați și să însumați diferite rezumate ale unor cantități mari de date cu doar câteva clicuri de mouse. În acest articol, ne vom familiariza cu tabelele pivot, vom înțelege ce sunt acestea, vom învăța cum să le creăm și să le personalizăm.
Acest articol a fost scris folosind Excel 2010. Conceptul de PivotTables nu s-a schimbat prea mult de-a lungul anilor, dar modul în care le creați este ușor diferit în fiecare versiune nouă de Excel. Dacă aveți o versiune de Excel nu 2010, atunci fiți pregătit că capturile de ecran din acest articol vor diferi de ceea ce vedeți pe ecran.
Un pic de istorie
În primele zile ale software-ului pentru foi de calcul, bile de regulă Lotus 1-2-3. Dominanța sa a fost atât de completă încât eforturile Microsoft de a-și dezvolta propriul software (Excel) ca alternativă la Lotus au părut o pierdere de timp. Acum înainte rapid până în 2010! Excel domină foile de calcul mai mult decât a făcut vreodată codul Lotus în istoria sa, iar numărul de persoane care încă folosesc Lotus este aproape de zero. Cum se poate întâmpla? Care a fost motivul unei astfel de întorsături dramatice a evenimentelor?
Analiștii identifică doi factori principali:
- În primul rând, Lotus a decis că această nouă platformă GUI numită Windows era doar un moft trecător care nu va dura mult. Ei au refuzat să construiască o versiune Windows a Lotus 1-2-3 (dar doar pentru câțiva ani), prezicând că versiunea DOS a software-ului lor va fi tot ce ar avea nevoie consumatorii. Microsoft a dezvoltat în mod natural Excel special pentru Windows.
- În al doilea rând, Microsoft a introdus un instrument în Excel numit PivotTables, care nu era disponibil în Lotus 1-2-3. PivotTables, exclusiv pentru Excel, s-au dovedit a fi atât de covârșitor de utile încât oamenii au avut tendința să rămână cu noua suită de software Excel, mai degrabă decât să continue cu Lotus 1-2-3, care nu le avea.
PivotTables, împreună cu subestimarea succesului Windows în general, au jucat marșul morții pentru Lotus 1-2-3 și au introdus succesul Microsoft Excel.
Ce sunt tabelele pivot?
Deci, care este cel mai bun mod de a caracteriza ce sunt PivotTables?
În termeni simpli, tabelele pivot sunt rezumate ale unor date, create pentru a facilita analiza acestor date. Spre deosebire de totalurile create manual, tabelele pivot Excel sunt interactive. Odată create, le puteți modifica cu ușurință dacă nu vă oferă imaginea pe care o așteptați. Cu doar câteva clicuri de mouse, totalurile pot fi inversate, astfel încât titlurile coloanelor să devină titluri de rând și invers. Puteți face multe lucruri cu tabelele pivot. În loc să încerci să descrii în cuvinte toate caracteristicile tabelelor pivot, este mai ușor să le demonstrezi în practică...
Datele pe care le analizați cu PivotTables nu pot fi aleatorii. Ar trebui să fie date brute, ca o listă de vreun fel. De exemplu, ar putea fi o listă cu vânzările realizate de companie în ultimele șase luni.
Priviți datele prezentate în figura de mai jos:
Rețineți că acestea nu sunt date brute, deoarece acestea au fost deja rezumate. În celula B3 vedem 30000 USD, care este probabil rezultatul total pe care l-a făcut James Cook în ianuarie. Unde sunt atunci datele originale? De unde a venit cifra de 30000 de dolari? Unde este lista originală de vânzări din care a fost derivat acest total lunar? Este clar că cineva a făcut o treabă grozavă organizând și sortând toate datele vânzărilor din ultimele șase luni și transformându-le în tabelul totalurilor pe care îl vedem. Cât timp crezi că a durat? Ora? Ora zece?
Faptul este că tabelul de mai sus nu este un tabel pivot. A fost realizat manual din date brute stocate în altă parte și a durat cel puțin câteva ore pentru a fi procesat. Doar un astfel de tabel rezumativ poate fi creat folosind tabele pivot în doar câteva secunde. Să ne dăm seama cum…
Dacă ne întoarcem la lista originală de vânzări, ar arăta cam așa:
S-ar putea să fii surprins că din această listă de tranzacții cu ajutorul tabelelor pivot și în doar câteva secunde, putem crea un raport lunar de vânzări în Excel, pe care l-am analizat mai sus. Da, putem face asta și multe altele!
Cum se creează un tabel pivot?
În primul rând, asigurați-vă că aveți niște date sursă într-o foaie Excel. Lista tranzacțiilor financiare este cea mai tipică care apare. De fapt, ar putea fi o listă cu orice: detalii de contact ale angajaților, o colecție de CD-uri sau datele despre consumul de combustibil al companiei tale.
Deci, pornim Excel... și încărcăm o astfel de listă...
După ce am deschis această listă în Excel, putem începe să creăm un tabel pivot.
Selectați orice celulă din această listă:
Apoi pe filă inserare (Inserați) comanda de selectare Masă rotativă (Masă rotativă):
Va apărea o casetă de dialog Creați tabelul pivot (Crearea unui tabel pivot) cu două întrebări pentru tine:
- Ce date să folosiți pentru a crea un nou tabel pivot?
- Unde să puneți tabelul pivot?
Deoarece la pasul anterior am selectat deja una dintre celulele listei, întreaga listă va fi selectată automat pentru a crea un tabel pivot. Rețineți că putem selecta un interval diferit, un tabel diferit și chiar o sursă de date externă, cum ar fi un tabel de bază de date Access sau MS-SQL. În plus, trebuie să alegem unde să plasăm noul tabel pivot: pe o foaie nouă sau pe una dintre cele existente. În acest exemplu, vom alege opțiunea - Foaie de lucru nouă (la o foaie noua):
Excel va crea o foaie nouă și va plasa un tabel pivot gol pe ea:
De îndată ce facem clic pe orice celulă din tabelul pivot, va apărea o altă casetă de dialog: Lista câmpurilor din tabelul pivot (Câmpurile tabelului pivot).
Lista de câmpuri din partea de sus a casetei de dialog este o listă cu toate titlurile din lista originală. Cele patru zone goale din partea de jos a ecranului vă permit să spuneți Tabelului Pivot cum doriți să rezumați datele. Atâta timp cât aceste zone sunt goale, nici în tabel nu există nimic. Tot ce trebuie să facem este să trageți titlurile din zona de sus în zonele goale de mai jos. În același timp, un tabel pivot este generat automat, în conformitate cu instrucțiunile noastre. Dacă facem o greșeală, putem elimina titlurile din zona de jos sau trageți altele pentru a le înlocui.
Zonă Valori (Semnificațiile) este probabil cea mai importantă dintre cele patru. Ce rubrică este plasată în această zonă determină ce date vor fi rezumate (suma, medie, maximă, minimă etc.) Acestea sunt aproape întotdeauna valori numerice. Un candidat excelent pentru un loc în acest domeniu sunt datele de la rubrica Sumă (Cost) tabelului nostru original. Trageți acest titlu în zonă Valori (Valori):
Vă rugăm să rețineți că titlul Sumă acum este marcat cu o bifă, iar în zonă Valori (Valori) a apărut o intrare Suma sumei (Câmpul Sumă Sumă), indicând că coloana Sumă pe scurt.
Dacă ne uităm la tabelul pivot în sine, vom vedea suma tuturor valorilor din coloană Sumă masa originala.
Deci, primul nostru tabel pivot este creat! Convenabil, dar nu deosebit de impresionant. Probabil dorim să obținem mai multe informații despre datele noastre decât avem în prezent.
Să ne întoarcem la datele originale și să încercăm să identificăm una sau mai multe coloane care pot fi folosite pentru a împărți această sumă. De exemplu, putem forma tabelul nostru pivot în așa fel încât suma totală a vânzărilor să fie calculată pentru fiecare vânzător în mod individual. Acestea. vor fi adăugate rânduri la tabelul nostru pivot cu numele fiecărui agent de vânzări din companie și valoarea totală a vânzărilor. Pentru a obține acest rezultat, trebuie doar să trageți titlul persoana de vanzari (reprezentant de vânzări) în regiune Etichete de rând (Siruri de caractere):
Devine mai interesant! Tabelul nostru Pivot începe să prindă contur...
Vezi beneficiile? În câteva clicuri, am creat un tabel care ar fi durat foarte mult timp pentru a fi creat manual.
Ce altceva putem face? Ei bine, într-un fel, tabelul nostru pivot este gata. Am creat un rezumat util al datelor originale. Informații importante deja primite! În restul acestui articol, vom analiza câteva modalități de a crea tabele pivot mai complexe și vom învăța cum să le personalizăm.
Configurare tabel pivot
În primul rând, putem crea un tabel pivot bidimensional. Să facem asta folosind titlul coloanei Modalitate de plată (Modalitate de plată). Doar trageți titlul Modalitate de plată spre zona Etichete coloană (Coloane):
Obtinem rezultatul:
Arată foarte tare!
Acum să facem un tabel tridimensional. Cum ar arăta o astfel de masă? Să vedem…
Trageți antetul Pachet (Complex) în zonă filtre de raportare (Filtre):
Observați unde este...
Acest lucru ne oferă posibilitatea de a filtra raportul pe baza „Pentru ce complex de vacanță a fost plătit”. De exemplu, putem vedea o defalcare în funcție de vânzători și de metode de plată pentru toate complexele sau, în câteva clicuri de mouse, putem schimba vizualizarea tabelului pivot și afișam aceeași defalcare numai pentru cei care au comandat complexul Căutători de soare.
Deci, dacă înțelegeți corect acest lucru, atunci tabelul nostru pivot poate fi numit tridimensional. Să continuăm configurarea...
Dacă se dovedește brusc că numai plata prin cec și card de credit (adică plata fără numerar) ar trebui să fie afișată în tabelul pivot, atunci putem dezactiva afișarea titlului Bani gheata (Bani gheata). Pentru aceasta, alături de Etichete coloană faceți clic pe săgeata în jos și debifați caseta din meniul drop-down Bani gheata:
Să vedem cum arată tabelul nostru pivot acum. După cum puteți vedea, coloana Bani gheata a dispărut din ea.
Formatarea tabelelor pivot în Excel
Tabelele pivot sunt, evident, un instrument foarte puternic, dar până acum rezultatele par puțin simple și plictisitoare. De exemplu, numerele pe care le adunăm nu arată ca sume în dolari, ci sunt doar numere. Să reparăm asta.
Este tentant să faci ceea ce ești obișnuit într-o astfel de situație și pur și simplu să selectezi întregul tabel (sau întreaga foaie) și să folosești butoanele standard de formatare a numerelor de pe bara de instrumente pentru a seta formatul dorit. Problema cu această abordare este că, dacă modificați vreodată structura tabelului pivot în viitor (ceea ce se întâmplă cu o șansă de 99%), formatarea se va pierde. Ceea ce avem nevoie este o modalitate de a o face (aproape) permanentă.
Mai întâi, să găsim intrarea Suma sumei in Valori (Valori) și faceți clic pe el. În meniul care apare, selectați elementul Setări câmp valoric (Opțiuni de câmp de valoare):
Va apărea o casetă de dialog Setări câmp valoric (Opțiuni de câmp de valoare).
presa Format număr (Format număr), se va deschide o casetă de dialog. Celule de format (format de celule):
Din listă Categorii (Formate numerice) selectați Contabilitate (financiar) și setați numărul de zecimale la zero. Acum apăsați de câteva ori OKpentru a reveni la tabelul nostru pivot.
După cum puteți vedea, numerele sunt formatate ca sume în dolari.
În timp ce ne aflăm la formatare, să setăm formatul pentru întregul PivotTable. Există mai multe moduri de a face acest lucru. O folosim pe cea mai simplă...
Apasă pe Instrumente PivotTable: Design (Lucrul cu PivotTables: Constructor):
Apoi, extindeți meniul făcând clic pe săgeata din colțul din dreapta jos al secțiunii Stiluri de tabel pivot (Stiluri PivotTable) pentru a vedea colecția extinsă de stiluri inline:
Alegeți orice stil potrivit și priviți rezultatul în tabelul pivot:
Alte setări PivotTable în Excel
Uneori trebuie să filtrați datele după date. De exemplu, în lista noastră de tranzacții există multe, multe date. Excel oferă un instrument de grupare a datelor după zi, lună, an și așa mai departe. Să vedem cum se face.
Mai întâi eliminați intrarea. Modalitate de plată din regiune Etichete coloană (Coloane). Pentru a face acest lucru, trageți-l înapoi în lista de titluri și, în locul său, mutați titlul Data rezervată (data rezervarii):
După cum puteți vedea, acest lucru a făcut temporar inutil tabelul nostru pivot. Excel a creat o coloană separată pentru fiecare dată la care a fost efectuată tranzacția. Drept urmare, am obținut o masă foarte largă!
Pentru a remedia acest lucru, faceți clic dreapta pe orice dată și selectați din meniul contextual GRUP (Grup):
Va apărea caseta de dialog de grupare. Noi alegem Luni (Luni) și faceți clic OK:
Voila! Acest tabel este mult mai util:
Apropo, acest tabel este aproape identic cu cel afișat la începutul articolului, unde totalurile vânzărilor au fost întocmite manual.
Mai este un punct foarte important pe care trebuie să-l știi! Puteți crea nu unul, ci mai multe niveluri de titluri de rând (sau coloană):
… și va arăta așa…
Același lucru se poate face cu titlurile coloanelor (sau chiar filtrele).
Să revenim la forma inițială a tabelului și să vedem cum să afișam medii în loc de sume.
Pentru a începe, faceți clic pe Suma sumei iar din meniul care apare selectează Setări câmp valoric (Opțiuni de câmp de valoare):
Lista Rezumați câmpul valoric după (Operațiune) în caseta de dialog Setări câmp valoric (Opțiuni câmpuri de valoare) selectați In medie (In medie):
În același timp, cât suntem aici, să ne schimbăm CustomName (Nume personalizat) cu Media sumei (câmpul Sumă Sumă) la ceva mai scurt. Introduceți în acest câmp ceva de genul Medie:
Anunturi OK si vezi ce se intampla. Rețineți că toate valorile s-au schimbat de la totale la medii, iar antetul tabelului (în celula din stânga sus) s-a schimbat în Medie:
Dacă doriți, puteți obține imediat suma, media și numărul (vânzările) plasate într-un tabel pivot.
Iată un ghid pas cu pas despre cum să faceți acest lucru, începând cu un tabel pivot gol:
- Trageți antetul persoana de vanzari (reprezentant de vânzări) în regiune Etichete coloană (Coloane).
- Trageți titlul de trei ori Sumă (Cost) la zonă Valori (Valori).
- Pentru primul câmp Sumă schimba titlul in Total (Sumă), iar formatul numărului din acest câmp este Contabilitate (Financiar). Numărul de zecimale este zero.
- Al doilea câmp Sumă nume mediee, setați operația pentru aceasta In medie (Medie) și formatul numărului din acest câmp se schimbă, de asemenea, în Contabilitate (financiar) cu zero zecimale.
- Pentru al treilea câmp Sumă setează un titlu Conta și o operație pentru el - Conta (Cantitate)
- În Etichete coloană Câmp (Coloane) creat automat Σ Valori (Σ Values) – trageți-l în zonă Etichete de rând (Linii)
Iată cu ce vom ajunge:
Suma totală, valoarea medie și numărul de vânzări – toate într-un singur tabel pivot!
Concluzie
Tabelele pivot din Microsoft Excel conțin o mulțime de caracteristici și setări. Într-un articol atât de mic, nici nu sunt aproape să le acopere pe toate. Ar fi nevoie de o carte mică sau de un site web mare pentru a descrie pe deplin toate posibilitățile tabelelor pivot. Cititorii îndrăzneți și curioși își pot continua explorarea tabelelor pivot. Pentru a face acest lucru, faceți clic dreapta pe aproape orice element al tabelului pivot și vedeți ce funcții și setări se deschid. Pe Panglică veți găsi două file: Instrumente PivotTable: Opțiuni (analiza) și Amenajări (Constructor). Nu vă fie teamă să faceți o greșeală, puteți oricând să ștergeți PivotTable și să o luați de la capăt. Aveți o oportunitate pe care utilizatorii de mult timp DOS și Lotus 1-2-3 nu au avut-o niciodată.