Cum se creează un tabel pivot în Excel: un tutorial pas cu pas

Tabelul pivot este una dintre cele mai puternice - și intimidante - funcții ale Microsoft Excel. Puternic pentru că vă poate ajuta să rezumați și să înțelegeți seturi mari de date. Intimidant pentru că nu ești tocmai un expert în Excel, iar tabelele pivot au avut întotdeauna reputația de a fi complicate.

Vestea bună: să înveți cum să creezi un tabel pivot în Excel este mult mai ușor decât ai fi fost făcut să crezi.

Dar înainte de a vă ghida prin procesul de creare a unuia, să facem un pas înapoi și să ne asigurăm că înțelegeți exact ce este un tabel pivot și de ce ar putea fi necesar să utilizați unul.

Cu alte cuvinte, tabelele pivot extrag semnificația din acel amestec aparent nesfârșit de numere de pe ecran. Și mai precis, vă permite să vă grupați datele în moduri diferite, astfel încât să puteți trage concluzii utile mai ușor.

Partea „pivot” a unui tabel pivot provine din faptul că puteți roti (sau pivotați) datele din tabel pentru a le vizualiza dintr-o perspectivă diferită. Pentru a fi clar, nu adăugați, scădeți sau modificați datele dvs. atunci când faceți un pivot. În schimb, pur și simplu reorganizați datele, astfel încât să puteți dezvălui informații utile din acestea.

Pentru ce sunt folosite tabelele pivot?

Dacă încă vă simțiți puțin confuz cu privire la ceea ce fac de fapt tabelele pivot, nu vă faceți griji. Aceasta este una dintre acele tehnologii care sunt mult mai ușor de înțeles odată ce ați văzut-o în acțiune.

Scopul tabelelor pivot este de a oferi modalități ușor de utilizat de a rezuma rapid cantități mari de date. Ele pot fi folosite pentru a înțelege, afișa și analiza mai bine datele numerice în detaliu - și pot ajuta la identificarea și la răspunsul la întrebări neprevăzute în jurul acestora.

Iată șapte scenarii ipotetice în care un tabel pivot ar putea fi o soluție:

1. Compararea totalului vânzărilor diferitelor produse.

Să presupunem că aveți o foaie de lucru care conține date privind vânzările lunare pentru trei produse diferite — produsul 1, produsul 2 și produsul 3 — și doriți să aflați care dintre cele trei a adus cei mai mulți bani. Puteți, desigur, să vă uitați prin foaia de lucru și să adăugați manual cifra de vânzări corespunzătoare la un total cumulat de fiecare dată când apare produsul 1. Apoi puteți face același lucru pentru produsul 2 și produsul 3 până când aveți totaluri pentru toate. O bucată de tort, nu?

Acum, imaginați-vă că foaia de lucru pentru vânzări lunare are mii și mii de rânduri. Sortarea manuală a acestora pe toate ar putea dura o viață întreagă. Folosind un tabel pivot, puteți agrega automat toate cifrele de vânzări pentru produsul 1, produsul 2 și produsul 3 - și puteți calcula sumele respective - în mai puțin de un minut.

2. Afișarea vânzărilor de produse ca procente din vânzările totale.

Tabelele pivot arată în mod natural totalurile fiecărui rând sau coloană atunci când le creați. Dar aceasta nu este singura cifră pe care o poți produce automat.

Să presupunem că ați introdus numerele de vânzări trimestriale pentru trei produse separate într-o foaie Excel și ați transformat aceste date într-un tabel pivot. Tabelul vă va oferi automat trei totaluri în partea de jos a fiecărei coloane - după ce a adăugat vânzările trimestriale ale fiecărui produs. Dar ce se întâmplă dacă ați dori să aflați procentul pe care aceste vânzări de produse au contribuit la toate vânzările companiei, mai degrabă decât doar totalul vânzărilor acestor produse?

Cu un tabel pivot, puteți configura fiecare coloană pentru a vă oferi procentul coloanei din totalurile celor trei coloane, în loc de doar totalul coloanei. Dacă vânzările a trei produse au totalizat 200.000 USD în vânzări, de exemplu, iar primul produs a făcut 45.000 USD, puteți edita un tabel pivot pentru a spune în schimb că acest produs a contribuit cu 22,5% din toate vânzările companiei.

Pentru a afișa vânzările de produse ca procente din vânzările totale într-un tabel pivot, pur și simplu faceți clic dreapta pe celula care conține un total de vânzări și selectați Afișați valorile ca > % din totalul general .

3. Combinarea datelor duplicate.

În acest scenariu, tocmai ați finalizat o reproiectare a blogului și a trebuit să actualizați o grămadă de adrese URL. Din păcate, software-ul dvs. de raportare a blogului nu s-a descurcat foarte bine și a sfârșit prin a împărți valorile de „vizualizare” pentru postări individuale între două adrese URL diferite. Deci, în foaia de calcul, aveți două instanțe separate pentru fiecare postare de blog individuală. Pentru a obține date exacte, trebuie să combinați totalurile vizualizărilor pentru fiecare dintre aceste duplicate.

Acolo intră în joc tabla pivot. În loc să fie nevoie să căutați și să combinați manual toate valorile din duplicatele, vă puteți rezuma datele (prin tabel pivot) după titlul postării pe blog și voila: valorile de vizualizare din acele postări duplicat vor fi agregate automat.

4. Obținerea unui număr de angajați pentru departamente separate.

Tabelele pivot sunt utile pentru calcularea automată a lucrurilor pe care nu le puteți găsi cu ușurință într-un tabel Excel de bază. Unul dintre acele lucruri este numărarea rândurilor care au toate ceva în comun.

Dacă aveți o listă de angajați într-o foaie Excel, de exemplu, iar lângă numele angajaților sunt departamentele respective din care aparțin, puteți crea un tabel pivot din aceste date care să vă arate numele fiecărui departament și numărul de angajați. care aparțin acelor departamente. Tabelul pivot elimină eficient sarcina dvs. de sortare a foii Excel după numele departamentului și numărarea manuală a fiecărui rând.

5. Adăugarea valorilor implicite la celulele goale.

Nu fiecare set de date pe care îl introduceți în Excel va popula fiecare celulă. Dacă așteptați să vină date noi înainte de a le introduce în Excel, este posibil să aveți o mulțime de celule goale care par confuze sau au nevoie de explicații suplimentare atunci când afișați aceste date managerului dvs. Aici intervin tabelele pivot.

Puteți personaliza cu ușurință un tabel pivot pentru a umple celulele goale cu o valoare implicită, cum ar fi 0 USD sau TBD (pentru „a fi determinat”). Pentru tabele mari de date, posibilitatea de a eticheta rapid aceste celule este o caracteristică utilă atunci când multe persoane examinează aceeași foaie.

Pentru a formata automat celulele goale din tabelul pivot, faceți clic dreapta pe tabel și faceți clic pe Opțiuni tabel pivot. În fereastra care apare, bifați caseta etichetată Celule goale ca și introduceți ceea ce doriți să fie afișat atunci când o celulă nu are altă valoare.

Cum se creează un tabel pivot

1. Introduceți datele într-o serie de rânduri și coloane.
2. Sortați-vă datele după un anumit atribut.
3. Evidențiați celulele pentru a vă crea tabelul pivot.
4. Trageți și plasați un câmp în zona „Etichete rând”.
5. Trageți și plasați un câmp în zona „Valori”.
6. Ajustați-vă calculele.

Acum că aveți o idee mai bună despre ce pot fi folosite tabelele pivot, haideți să intrăm în esențialul cum să creați unul.

Pasul 1. Introduceți datele într-o serie de rânduri și coloane.

Fiecare tabel pivot din Excel începe cu un tabel Excel de bază, în care sunt găzduite toate datele dvs. Pentru a crea acest tabel, introduceți pur și simplu valorile într-un anumit set de rânduri și coloane. Utilizați rândul de sus sau coloana de sus pentru a clasifica valorile în funcție de ceea ce reprezintă.

De exemplu, pentru a crea un tabel Excel cu date despre performanța postărilor de blog, este posibil să aveți o coloană care listează fiecare „Pagini de top”, o coloană care listează „Clicuri” ale fiecărei adrese URL, o coloană care listează „Afișări” ale fiecărei postări și așa mai departe. (Vom folosi acest exemplu în pașii care urmează.)

Pasul 2. Sortați-vă datele după un anumit atribut.

Când aveți toate datele pe care doriți să le introduceți în foaia dvs. Excel, veți dori să sortați aceste date într-un fel, astfel încât să fie mai ușor de gestionat odată ce le transformați într-un tabel pivot.

Pentru a vă sorta datele, faceți clic pe fila Date din bara de navigare de sus și selectați pictograma Sortare de sub aceasta. În fereastra care apare, puteți opta pentru sortarea datelor după orice coloană doriți și în orice ordine.

De exemplu, pentru a sorta foaia Excel după „Vizualizări la data”, selectați acest titlu de coloană sub Coloană și apoi selectați dacă doriți să vă ordonați postările de la cel mai mic la cel mai mare sau de la cel mai mare la cel mai mic.

Selectați OK în colțul din dreapta jos al ferestrei Sortare și veți reordona cu succes fiecare rând al foii Excel după numărul de vizualizări pe care le-a primit fiecare postare de blog.

Pasul 3. Evidențiați celulele pentru a vă crea tabelul pivot.

După ce ați introdus datele în foaia de lucru Excel și le-ați sortat după bunul plac, evidențiați celulele pe care doriți să le rezumați într-un tabel pivot. Faceți clic pe Insert în partea de sus a navigației și selectați pictograma PivotTable . De asemenea, puteți să dați clic oriunde în foaia de lucru, să selectați „PivotTable” și să introduceți manual intervalul de celule pe care doriți să le includeți în PivotTable.

Aceasta va deschide o casetă de opțiuni în care, pe lângă setarea intervalului de celule, puteți selecta dacă să lansați sau nu acest tabel pivot într-o nouă foaie de lucru sau să-l păstrați în foaia de lucru existentă. Dacă deschideți o foaie nouă, puteți naviga către și departe de ea în partea de jos a registrului de lucru Excel. După ce ați ales, faceți clic pe OK.

Ca alternativă , puteți evidenția celulele, selectați Tabele pivot recomandate în partea dreaptă a pictogramei Tabel pivot și deschideți un tabel pivot cu sugestii prestabilite despre cum să organizați fiecare rând și coloană.

Notă: dacă utilizați o versiune anterioară de Excel, „PivotTables” poate fi sub Tabele sau Date de-a lungul navigației de sus, mai degrabă decât „Inserați”. În Foi de calcul Google, puteți crea tabele pivot din meniul drop-down Date din partea de sus.

Pasul 4. Trageți și plasați un câmp în zona „Etichete rând”.

După ce ați finalizat Pasul 3, Excel va crea un tabel pivot necompletat pentru dvs. Următorul pas este să trageți și să plasați un câmp – etichetat în funcție de numele coloanelor din foaia de calcul – în zona Etichete rând . Acest lucru va determina prin ce identificator unic - titlul postării de blog, numele produsului și așa mai departe - tabelul pivot vă va organiza datele.

De exemplu, să presupunem că doriți să organizați o mulțime de date de blogging după titlul postării. Pentru a face acest lucru, trebuie doar să faceți clic și să trageți câmpul „Pagini de sus” în zona „Etichete rând”.

Notă: tabelul pivot poate arăta diferit în funcție de versiunea de Excel cu care lucrați. Cu toate acestea, principiile generale rămân aceleași.

Pasul 5. Trageți și plasați un câmp în zona „Valori”.

Odată ce ați stabilit prin ce veți organiza datele, următorul pas este să adăugați niște valori trăgând un câmp în zona Valori .

Continuând cu exemplul de date de blogging, să presupunem că doriți să rezumați vizualizările postărilor de blog după titlu. Pentru a face acest lucru, trebuie pur și simplu să trageți câmpul „Vizualizări” în zona Valori.

Pasul 6. Ajustați-vă calculele.

Suma unei anumite valori va fi calculată în mod implicit, dar o puteți schimba cu ușurință la ceva precum medie, maximă sau minimă, în funcție de ceea ce doriți să calculați.

Pe un Mac, puteți face acest lucru făcând clic pe i -ul mic de lângă o valoare din zona „Valori”, selectând opțiunea dorită și făcând clic pe „OK”. După ce ați făcut selecția, tabelul pivot va fi actualizat în consecință.

Dacă utilizați un computer, va trebui să faceți clic pe micul triunghi inversat de lângă valoarea dvs. și să selectați Setări câmp valoric pentru a accesa meniul.

Când ți-ai clasificat datele după bunul plac, salvează-ți munca și folosește-o după cum vrei.

Sapă mai adânc cu tabele pivot

Acum ați învățat elementele de bază ale creării tabelelor pivot în Excel. Cu această înțelegere, vă puteți da seama de ce aveți nevoie din tabelul pivot și puteți găsi soluțiile pe care le căutați.

De exemplu, este posibil să observați că datele din tabelul pivot nu sunt sortate așa cum doriți. Dacă acesta este cazul, funcția de sortare din Excel vă poate ajuta. Alternativ, poate fi necesar să încorporați date dintr-o altă sursă în raportare, caz în care funcția CĂUTARE V poate fi utilă.

Nota editorului: această postare a fost publicată inițial în decembrie 2018 și a fost actualizată pentru a fi completă.