Scris de cofondator Kasper Langmann, Specialist Microsoft Office.

Cine altcineva vrea să manipuleze și să vizualizeze datele fără a transpira?

De aceea, am scris acest tutorial suprem pentru „Pivot Tables” în Excel.

Acesta este cel mai ambițios tutorial al nostru.

Vine la 7733 de cuvinte, are un fișier de exerciții, video și sarcini gratuite.

Deci, ia o ceașcă de cafea și începe!

* Acest tutorial este pentru Excel 2019/Microsoft 365 (pentru Windows). Ai o altă versiune? Nicio problemă, puteți urma în continuare aceiași pași.

Cuprins

Prototip:

Reveniți la acest ghid într-o clipă. Apăsați CTRL + D pentru a marca această pagină.

Ce este un tabel pivot și de ce îl utilizați?

Ah, da, tabelul pivot. Fără îndoială, unul dintre cele mai iubite instrumente Excel, dar jignite.

Mulți oameni au auzit de tabelele pivot, dar mai puțini știu exact ce sunt.

Deci, ce este un tabel pivot?

Cel mai bun mod de a-l defini este: este o funcție încorporată a Excelului, care vă permite să luați date și să le rearanjați.

Se creează un tabel care vă permite să alegeți ce și cum doriți să vizualizați datele.

De ce să folosiți tabelele pivot?

Pentru început, acestea îmbunătățesc capacitatea de a trage concluzii care pot fi acționate din datele dvs. Acest lucru este valabil mai ales pentru seturile mari de date cu multe atribute diferite.

Obțineți fișierul de exerciții GRATUIT

Inainte sa incepi:

De-a lungul acestui ghid, aveți nevoie de un set de date pentru a practica.

Am inclus unul pentru tine (gratuit).

Descărcați-l chiar mai jos!

Descărcați fișierul de exerciții GRATUIT

pivot

Video: Pregătirea datelor

Înainte de a crea un tabel pivot, datele dvs. trebuie să fie configurate imediat.

În acest videoclip, vă arăt exact cum se face acest lucru.

Cum se creează un tabel pivot

Bine, sunteți aici pentru a afla cum să creați un tabel pivot.

Urmează 6 pași către un tabel pivot subliniat mai jos. În câteva minute, ați creat primul dvs. tabel pivot 🙂

Apoi veți învăța cum creați un tabel pivot din mai multe foi (ceea ce este surprinzător de util).

Să vedem cum să creăm un tabel pivot.

Despre setul de date

Vom folosi un set de date care conține totaluri de vânzări pentru unele produse de dimensiuni și prețuri diferite. Setul de date include, de asemenea, date despre fiecare tranzacție, precum și prețul cu ridicata pentru fiecare.

Cum să creați un tabel pivot în 6 pași (simpli)

1: Asigurați-vă că toate coloanele dvs. de date au anteturi.

2: Asigurați-vă că datele dvs. nu conțin rânduri goale

3: Faceți clic pe orice parte a tabelului de date

4: În grupul „Tables” din fila „Insert”, faceți clic pe „PivotTable”. „Tabel/Interval” este selectat în mod implicit ca interval de date contiguu (asigurat de pașii 1 și 2). Lăsați valorile implicite selectate în caseta de dialog „Creați tabelul pivot”.

5: Faceți clic pe „OK” și noua dvs. foaie de lucru va fi activată acum.

6: Alegeți câmpurile pe care să le adăugați la tabelul pivot, după cum este necesar, din „Lista câmpurilor” din tabelul pivot.

Notă: „Lista câmpurilor” este vizibilă numai dacă faceți clic pe cursor în Tabelul pivot în sine. Dacă faceți clic într-o celulă din afara Tabelului pivot, „Lista câmpurilor” va deveni ascunsă.

Tabelul pivot rezultat este prezentat în figura următoare. Rețineți că „Luna” este setată ca coloane, în timp ce „Locație” se afișează ca rânduri, la fel cum am configurat-o.

Cum se creează un tabel pivot din mai multe foi

Acum știți cum să creați un tabel pivot din date dintr-o singură foaie de lucru.

Să vedem cum să creați un tabel pivot din mai multe foi de lucru.

Aici, numele lor sunt „Chicago”, „Nashville” și „San Francisco”.

Să selectăm prima foaie de lucru din cele trei, „Chicago”.

Apoi continuați și urmați următorii 11 pași ...

1: Apăsați Alt + D, apoi apăsați P pentru a face să apară „PivotTable și PivotChart Wizard”.

2: Sub „Unde sunt datele pe care doriți să le analizați?” selectați butonul radio pentru „Intervalele de consolidare multiple” și apoi faceți clic pe „Următorul”.

Aceasta vă duce la „Pasul 2” al vrăjitorului.

3: Selectați butonul radio „Voi crea câmpurile paginii” și faceți clic pe „Următorul”.

4: Următorul lucru pe care trebuie să-l faceți este să selectați primul interval de date.

În fișierul de exemplu, acesta va fi tabelul de date din fila „Chicago” și trebuie să selectați intervalul de la A2: H7.

5: Repetați același proces atât pentru datele privind vânzările locației „Nashville”, cât și pentru cele „San Francisco”.

6: Acum, evidențiați primul interval din secțiunea „Toate intervalele”. Apoi faceți clic pe butonul radio de lângă „1” în secțiunea „Câte câmpuri de pagină doriți?”.

7: Sub „Câmpul unu:” tastați un nume pentru „câmpul paginii”. În acest exemplu, pur și simplu tastați „Chicago”.

8: Acum repetați acest lucru pentru celelalte două game. Apoi faceți clic pe „Următorul”.

9: La pasul 3 al vrăjitorului, lăsați setarea implicită „Foaie de lucru nouă” selectată și faceți clic pe „Finalizare”.

Tabelul pivot rezultat ar trebui să arate ca în figura următoare.

Acest tabel pivot a combinat cifrele de vânzări din toate cele trei foi de lucru pentru locație.

Un lucru de reținut este filtrul din celula B1. Acest filtru vă permite să selectați una sau mai multe locații.

Actualizați gama Tabelului pivot

S-au modificat datele sursă pentru un tabel pivot existent?

Apoi, trebuie să vă actualizați gama Pivot Table.

În alte cazuri, puteți adăuga mai multe date.

Pentru a include aceste noi date, trebuie să vă schimbați sursa de date.

Iată o privire mai atentă asupra celor 2 metode.

Metoda 1: Schimbați sursa de date

Doriți să adăugați date noi la datele sursă ale tabelului pivot?

Apoi, trebuie să vă schimbați sursa de date pentru a o extinde la noua gamă.

Vă rugăm să rețineți că există o foaie de lucru în fișierul de exemplu cu un nume de filă „Oct Data To Add”.

După ce ați adăugat noile date din octombrie, accesați Tabelul pivot și faceți clic în orice celulă din tabelul pivot.

Acum faceți clic pe „Schimbați sursa de date” în grupul „Date” din fila „Analizați”.

Asigurați-vă că „Tabel/Interval” (în fereastra „Modificare sursă date tabel pivot”) corespunde intervalului care include noile date.

Dacă nu se potrivește, poate fi necesar să selectați manual întreaga gamă.

Doar faceți clic pe o celulă din date și utilizați comanda rapidă Ctrl + A pentru a extinde la noul interval utilizat.

Excel permite, de asemenea, utilizarea „intervalelor dinamice denumite” ca surse de date.

Acest lucru poate simplifica procesul de schimbare a sursei de date.

Pentru mai multe informații despre cum să creați „intervale denumite dinamice” în Excel, consultați această resursă.

De asemenea, trebuie să știți cum să reîmprospătați tabelul pivot ...

Și exact asta veți învăța în metoda 2 de mai jos!

Metoda 2: reîmprospătați un tabel pivot

O altă metodă despre care trebuie să știți este cum să reîmprospătați un tabel pivot.

Să presupunem că trebuie să modificați prețul:

  • Vrei să crești prețul de la 275,79 USD la 299,99 USD
  • Acest lucru este valabil pentru cod produs BP110966-XL

Puteți găsi acest lucru în datele dvs. originale pentru tabelul pivot.

Prețul incorect face ca numerele de vânzări să fie subevaluate.

Pentru a rezolva acest lucru, filtrați codul produsului respectiv și modificați manual prețul de vânzare.

Noul preț pentru toate aceste rânduri de date va avea un impact asupra sumelor din coloana „Total”.

Acum găsiți grupul „Date” și faceți clic pe „Reîmprospătare”.

Observați în figura următoare modul în care „Grand Total” a crescut datorită modificării prețului pe care am făcut-o.

Acest lucru a intrat în vigoare doar după ce am actualizat tabelul pivot.

Cum se utilizează tabelul pivot

Tabelul dvs. pivot este creat și sunteți foarte încântat să îl utilizați.

Dar cum utilizați tabelul pivot?

În acest sens este vorba despre această secțiune.

Subiecte:

  • Câmpuri
    • Adăugarea coloanelor
    • Filtre de raportare
  • Setări câmp valoric
    • In medie
    • Numara
    • Număr distinct
  • Gruparea
  • Câmpuri calculate
  • Descoperiți
  • Utilizați datele dintr-un tabel pivot într-o formulă

Utilizarea câmpurilor din tabelul pivot

Un „câmp” din tabelul pivot este menționat de antetul său în datele sursă (de exemplu, „Locație”) și conține datele găsite în acea coloană (de exemplu, San Francisco).

Prin separarea datelor în „câmpurile” respective pentru utilizare într-un tabel pivot, Excel permite utilizatorului său să:

  • Afișați seturile de date într-o ieșire mai logică de exemplu. „Unități totale vândute” în San Francisco. Acest lucru este prezentat mai detaliat mai târziu când discutăm despre „Setările câmpului valorii”.
  • Extrageți și afișați date relevante organizându-l într-un mod mai potrivit - fie la figurat, fie estetic.
  • Filtrați datele pe baza valorilor găsite în „câmpul” selectat.

Adăugarea de coloane la câmpurile din tabelul pivot

Să facem un pas înapoi și să recreăm tabelul pivot cu datele noastre din octombrie adăugate:

Acum avem tabelul gol cu ​​care să începem să aranjăm datele cu „câmpurile” din tabelul pivot.

Pentru a adăuga coloane de date în tabel, glisați și fixați câmpul dorit în „Etichete coloană”, „Etichete rând” sau „Valori” (aceste 3 sunt, de asemenea, tratate mai detaliat mai târziu).

Acest exemplu de configurare ar lista datele în rânduri separate prin „Locație” și „Element”. Coloanele sunt separate prin „Lună”.

Aceasta formează o grilă pentru a afișa „Totalul unităților vândute” în fiecare „locație” după „lună”.

Rezultatul acestui aranjament arată după cum urmează:

Când selectăm „Locație” și „Element” pentru rândurile noastre, se întâmplă ceva interesant:

  1. Totalurile „Locație” pentru fiecare lună sunt acum vizualizate ca subtotale pentru fiecare lună.
  2. Defalcarea pe „Articol” este, de asemenea, vizibilă în cadrul fiecărei „Locații”.

Pentru a ascunde rândurile „Articol”, faceți clic pe minusul de lângă rândurile „Locație”.

Acest lucru lasă doar subtotalurile vizibile pentru acea „Locație”.

Utilizarea filtrelor de raportare

Odată cu configurarea tabelului pivot, puteți vedea defalcarea vânzărilor totale în intervalele alese („lună”, „locație” și „articol”).

Aici intră în joc „Filtre de raportare”.

Trageți și fixați „Element” în zona „Filtru raport”.

De asemenea, să setăm „Rânduri” la „Lună” și „Coloane” la „Locație”.

Acest lucru vă oferă o perspectivă nouă asupra datelor.

Aceasta adaugă un alt rând deasupra tabelului pivot cu „câmpul de filtrare” selectat.

Există, de asemenea, o casetă derulantă pentru a face o selecție ...

Aceasta permite utilizatorului să selecteze doar datele relevante necesare pentru interogare:

Dacă doriți să selectați mai multe intrări din filtru, bifați caseta de lângă „Selectați mai multe articole” din partea de jos a meniului derulant.

Apoi, veți putea selecta toate intrările disponibile în filtru în orice combinație aveți nevoie.

Aici vom selecta Rack-ul „Mașină” și vom nota schimbarea valorilor:

Setări câmp valoric

În exemplul anterior, am folosit „Setarea câmpului valorii sumelor”.

Se afișează „Unități totale vândute” pentru fiecare „lună” și „locație” listate în tabelul pivot.

Prin utilizarea diferitelor „Setări câmp valoric”, datele din câmpul prezentat sub „Valori” sunt prezentate în moduri diferite.

Pentru a accesa „Setări câmp valoric”, faceți clic pe intrarea acestuia. Apoi selectați-l din meniul derulant rezultat: