Acest tutorial practic explică ce este o diagramă pivot și cum să o creăm și să o personalizăm. Vom vedea, de asemenea, diferența dintre o diagramă pivot și un tabel:
Graficele sunt considerate una dintre cele mai bune modalități de prezentare a raportului. Ele ne ajută la înțelegerea și analiza datelor într-un mod mai simplu. Graficele pivot din Excel ne oferă o reprezentare vizuală a datelor în diferite moduri.
În acest tutorial, vom învăța toate detaliile necesare pentru a lucra cu diagrame pivot în Excel. Crearea de diagrame de diferite tipuri, formatarea aspectului lor, adăugarea de filtre, adăugarea de formule personalizate și utilizarea formatului unui grafic pentru un alt grafic aparținând diferitelor tabele pivot.
Ce este o diagramă pivot în Excel
O diagramă pivot din Excel este o reprezentare vizuală a datelor. Vă oferă o imagine de ansamblu a datelor brute. Vă permite să analizați datele folosind diferite tipuri de grafice și layout-uri. Este considerată cea mai bună diagramă în timpul unei prezentări de afaceri care implică date uriașe.
Diagrama pivotantă Vs tabel
Tabel pivotant ne oferă o modalitate de a rezuma date mari într-o matrice de tip grilă. Puteți alege câmpurile pe care doriți să le utilizați în tabel pentru rânduri și coloane. Diagrama pivot ne oferă o reprezentare grafică a tabelului pivot. Puteți alege dintre mai multe modele și tipuri de diagrame.
Acest grafic rezumă, de asemenea, datele. Puteți crea atât un tabel pivot cât și un grafic pentru o sursă de date și le puteți gestiona simultan. Aceasta înseamnă că modificările efectuate în tabelul pivot se vor reflecta în grafic și viceversa.
Sursa de date
Mai jos este prezentat exemplul de sursă de date care va fi utilizat în acest tutorial. Faceți clic pe link pentru a descărca Exemplu_Data Pivot Chart
| ID-ul comenzii | Data comenzii | Denumirea produsului | Regiune | Orașul | Cantitate | Preț total |
|---|---|---|---|---|---|---|
| 1 | 03-01-2020 | Prăjituri simple | Nord | New York | 33 | 444.66 |
| 2 | 04-02-2012 | Biscuiți de zahăr | Sud | Lima | 432 | 346.33 |
| 3 | 05-04-2018 | Napolitane | Est | Boston | 33 | 32.54 |
| 4 | 06-05-2019 | Ciocolată | Vest | Teren de stejar | 245 | 543.43 |
| 5 | 07-07-2020 | Înghețată | Nord | Chicago | 324 | 223.56 |
| 7 | 09-09-2020 | Prăjituri simple | Est | Washington | 32 | 34.4 |
| 8 | 10-11-2020 | Biscuiți de zahăr | Vest | Seattle | 12 | 56.54 |
| 9 | 11-12-2017 | Napolitane | Nord | Toronto | 323 | 878.54 |
| 10 | 12-14-2020 | Ciocolată | Sud | Lima | 232 | 864.74 |
| 11 | 01-15-2020 | Înghețată | Est | Boston | 445 | 457.54 |
| 13 | 03-18-2018 | Cookie-uri de sare | Nord | New York | 5454 | 34546 |
| 14 | 04-18-2017 | Biscuiți cu brânză | Sud | Lima | 5653 | 3456.34 |
| 15 | 05-19-2016 | Cookie-uri de sare | Est | Washington | 4 | 74.4 |
| 16 | 06-20-2015 | Biscuiți cu brânză | Vest | Teren de stejar | 545 | 876.67 |
Creați o diagramă pivot
Există două moduri de a realiza o diagramă pivot în Excel.
#1) Creați din sursa de date
Putem crea o diagramă direct din foaia de date fără un tabel pivot.
Pentru a realiza acest lucru, urmați pașii de mai jos.
#1) Selectați orice celulă din tabel.
#2) Mergeți la Inserare -> Diagramă pivotantă
#3) Puteți alege să creați o foaie nouă sau să menționați intervalul de tabele în care doriți să plasați graficul la rubrica Foaie de lucru existentă.
#4) Faceți clic pe OK
Aceasta va crea o diagramă pivot goală și tabelul pivot aferent acesteia. Puteți adăuga câmpurile dorite pentru a genera un raport și o diagramă.
#2) Creați de la PivotTable
Dacă ați creat deja un tabel pivot, îl puteți utiliza pentru a genera un grafic pivot. Am creat un exemplu de tabel pivot, după cum se arată mai jos.
Pentru a crea o diagramă.
#1) Selectați orice celulă din PivotTable.
#2) Mergeți la Insert-> Diagramă pivotantă
#3) Vă va oferi o listă de grafice disponibile, selectați graficul dorit.
#4) Faceți clic pe OK.
Acest lucru va genera un grafic cu datele preluate din tabelul pivot. Exemplul de grafic pivot este prezentat mai jos.
Notă: Alternativ, puteți utiliza tasta rapidă F11. Faceți clic pe tabelul pivotant și apăsați F11 pe tastatură.
Personalizarea graficului
Puteți personaliza graficul cu ajutorul pictogramei + și a pictogramei de vopsire prezente în dreapta graficului.
Butonul + - Vă ajută să adăugați sau să eliminați elemente ale graficului, cum ar fi titluri, linii de grilă, legende etc. și să decideți poziția acestora.
Puteți adăuga titlul graficului, menționa titlurile axelor etc. Am adăugat titlul graficului și titlul axei ca exemplu.
Stilul graficului - Puteți modifica stilul și culoarea graficului făcând clic pe pictograma pensulă.
De asemenea, puteți modifica culoarea graficului după cum doriți din secțiunea de culori.
Grafice recomandate
Excel ne oferă Diagrame pivot recomandate care ne permit să alegem rapid tipul de Diagramă pivot care corespunde cerințelor afacerii dumneavoastră.
#1) Selectați tabelul sursă de date.
#2) Mergeți la Insert -> Grafice recomandate .
#3) Faceți clic pe Recommended Charts (Grafice recomandate).
#4) Faceți clic pe graficul de care aveți nevoie.
#5) Faceți clic pe OK
Tabelul pivot și graficul rezultat vor fi create într-o foaie nouă și le puteți personaliza în continuare, după cum este necesar.
Câmpurile diagramei pivotante
Acesta are 4 câmpuri, după cum se arată mai jos.
1. Filtre: Fields (Câmpuri) de sub această rubrică ne oferă posibilitatea de a adăuga filtre de raport.
2. Legende (Serie) : Câmpurile de aici reprezintă titlurile coloanelor din tabelul pivot.
3. Axă (categorii): Acestea reprezintă rândurile din tabelul pivot. Aceste câmpuri sunt afișate în bara axei de pe grafic.
4. Valori: Se utilizează pentru a afișa valorile numerice rezumate.
Instrumente pentru diagrame pivotante
Analizați: Există diverse opțiuni disponibile pentru a face graficul mai ușor de utilizat.
Numele graficului: Este numele graficului. Este utilizat la scrierea codului VBA și se află, de asemenea, în panoul de selecție. Este disponibil în Excel 2010 și ulterior.
Opțiuni: Se va afișa caseta de dialog PivotTable Options (Opțiuni tabel pivotant), unde puteți seta Layout & Format, setați să afișați/ascundeți totalul general, setați opțiunile de sortare, opțiunile de afișare etc.
Domeniul activ: Puteți schimba numele coloanei din tabel, de exemplu , De la Total general la Suma finală etc., iar acestea vor fi actualizate în tabel și în grafic.
Extindeți câmpul: Aceasta este utilizată pentru a extinde automat toate valorile.
Dacă aveți mai multe câmpuri, cum ar fi Anii, Trimestrele și Data, atunci, în loc să le extindeți individual, puteți face clic pe Expand Field.
Câmpul de colaps: Aceasta este opusă câmpului Expand Field (Extindeți câmpul). Aceasta va prăbuși câmpurile extinse și va prezenta un grafic compact.
Extindeți exemplul
Exemplu de colaps
Notă: Să presupunem că aveți doar un singur câmp în rânduri, atunci, făcând clic pe Expand Field (Extindeți câmpul), se afișează o fereastră de dialog cu toate câmpurile și puteți selecta câmpul dorit. Câmpul selectat va fi adăugat la secțiunea Rows (Rânduri), iar graficul va fi actualizat automat.
Introduceți Slicer
Puteți insera un slicer în grafic la fel ca în cazul tabelului pivot.
Pentru a integra slicerul cu o diagramă, urmați pașii de mai jos.
- Faceți clic pe graficul pivot.
- Mergeți la Tabul Analiză -> Inserare Slicer .
- În fereastra de dialog Select fields (Selectați câmpurile), trebuie să creați slicerii.
- Faceți clic pe OK
Aceasta va insera caseta de feliere, așa cum se arată mai jos. Am văzut cum se utilizează caseta de feliere în tutorialul nostru anterior.
Inserarea liniei de timp
Puteți insera o linie de timp în grafic la fel ca un tabel pivot.
Pentru a integra Timeline în grafic, urmați pașii de mai jos.
- Faceți clic pe graficul pivot.
- Mergeți la Tabul Analiză -> Inserare cronologie.
- Selectați câmpul Data necesar.
- Faceți clic pe OK
Acest lucru va insera linia de timp, așa cum se arată mai jos. Am văzut cum se utilizează linia de timp în tutorialul nostru anterior.
Rezultatul bazat pe linia de timp este actualizat atât în tabelul pivot, cât și în grafic.
Conexiune filtru
Puteți conectați slicerul sau linia de timp la mai multe diagrame pivot. De exemplu, am creat 2 tabele pivot și 1 slicer. Aplicați slicerul la ambele diagrame.
- Faceți clic pe graficul pivot la care nu este conectat în prezent slicerul.
- Mergeți la Analizați -> Filtru de conectare
- Selectați Slicerul pe care doriți să îl conectați.
- Faceți clic pe Ok
Acum puteți gestiona ambele diagrame cu un singur slicer.
Calcule
Dacă doriți să adăugați formule personalizate, puteți face acest lucru utilizând câmpul de calcul.
Exemplu:
#1) Selectați graficul pivot la care doriți să adăugați formulele personalizate.
#2) Mergeți la Analiză -> Câmpuri ->Articole -> Seturi
#3) Selectați Calculated Fields (Câmpuri calculate).
#4) La Name (Nume), introduceți numele pe care îl doriți.
#5) În Formula, adăugați formula dvs. personalizată. Dacă acordați o reducere de 10% din suma totală, puteți adăuga o formulă așa cum se arată mai jos.
#6) Tabelul pivot, câmpurile pivot și graficul vor fi actualizate în consecință.
Reîmprospătare
Ori de câte ori modificați valorile din sursa de date, faceți clic oriunde pe graficul pivot și faceți clic dreapta și selectați Refresh (Reîmprospătare) sau mergeți la Analyze -> Refresh (Analiză -> Reîmprospătare). Reîmprospătarea unui tabel pivot va reîmprospăta și graficul.
Modificarea sursei de date
Ori de câte ori adăugați mai multe rânduri la sursa de date, graficul nu va lua în considerare rândurile adăugate, deoarece am definit intervalul în timpul creării graficului.
Pentru a include noile rânduri:
- Faceți clic oriunde pe graficul pivotant.
- Mergeți la Analizează -> Schimbă sursa de date
- Se va afișa caseta de dialog Change PivotTable Data Source și puteți introduce noul interval de date.
- Faceți clic pe Ok
Asigurați-vă că efectuați pașii de mai sus pentru toate graficele în mod individual.
Clar
Folosind Clear (Șterge), puteți șterge întreaga diagramă pivot. Aceasta va fi o diagramă goală și un tabel.
- Faceți clic pe Diagrama pivotantă
- Analiză -> Șterge -> Șterge tot
De asemenea, puteți șterge toate filtrele aplicate prin Analizați -> Clear-> Clear Filters
Mutarea graficului
După ce ați creat o diagramă, o puteți muta în locația dorită.
Urmați pașii de mai jos:
- Faceți clic pe graficul pivot.
- Mergeți la Analizați -> Mutați graficul
- Selectați opțiunea dorită din fereastra de dialog:
- Foaie nouă: Foaia va fi creată automat, iar graficul va fi afișat.
- Obiect în: Puteți selecta una dintre foile disponibile, iar graficul va fi mutat pe foaia selectată.
Lista de câmpuri: Puteți afișa/ascunde panoul PivotChart Fields.
Butoane de câmp: Puteți afișa/ascunde câmpul de legendă, câmpul de axă, câmpul de valoare, filtrul de raport etc. pe grafic.
Design
În această filă sunt disponibile mai multe opțiuni de proiectare a graficului.
Adăugați elementul grafic: Aceasta ne oferă aceleași opțiuni pe care le-am obținut atunci când am făcut clic pe butonul + de lângă graficul pivot. Acestea ne ajută să adăugăm elemente la grafic, cum ar fi titlul, bara de erori etc.
Dispunere rapidă: Puteți modifica aspectul implicit și puteți selecta unul dintre aspectele predefinite disponibile. De exemplu, am mutat aspectul Regiunii în partea de sus în loc de partea dreaptă.
Schimbați culorile: Selectați diferite culori pentru graficul dumneavoastră.
Stil de diagramă: Alegeți Stilul pentru graficul dvs. din aceste grafice disponibile.
Comutați rândul/coloana: Puteți schimba cu ușurință rândurile și coloanele cu un singur clic, iar tabelul pivot și graficul vor fi actualizate automat.
Înainte de schimbarea rândului/coloanei
După comutarea rândului/coloanei
Selectați Data: Să presupunem că ați petrecut mult timp pentru a formata o diagramă pivot în conformitate cu standardele companiei dvs. și toate diagramele dvs. ar trebui să aibă același format. Atunci această opțiune vă este utilă. Nu puteți copia direct diagrama pivot și schimba sursa de date. Există câțiva pași care trebuie efectuați.
#1) Selectați graficul pivotant dorit și copiați zona graficului.
#2) Deschideți un nou registru de lucru. Fișier -> Noul registru de lucru
#3) Lipiți graficul copiat. Puteți observa în bara de meniu că în bara de meniu scrie Chart Tools și nu PivotChart Tools.
#4) Acum selectați zona Chart și apăsați opțiunea Cut (Tăiere).
#5) Mergeți la registrul de lucru în care doriți să utilizați acest grafic.
#6) Notă: Ar trebui să aveți deja un tabel pivot creat.
#7) Lipiți graficul de la pasul 4.
#8) Mergeți la Design prezent sub Chart Tools (Instrumente grafice). Faceți clic pe Select Data Tab (Selectați fila Date).
#9) Faceți clic pe orice celulă din tabelul pivot.
Diagrama pivot va fi creată cu datele prezente în noul tabel pivot, dar formatul rămâne același ca anterior. Puteți modifica axa și legenda după cum este necesar pentru noul tabel.
Graficul rezultat pentru noul tabel pivot este prezentat mai jos.
Schimbați tipul de diagramă: Puteți schimba tipul implicit de diagramă în coloane cu tipul dorit, după cum se arată mai jos.
Graficul se va actualiza automat în funcție de selecție.
Diagramă cu plăcintă
Diagramă cu bare
Format
Acestea sunt utilizate în principiu pentru a formata în mod personalizat textul prezent în interiorul graficului.
Selecția curentă: Se vor afișa toate elementele prezente în tabel și îl puteți selecta pe cel la care doriți să modificați stilul de format. De exemplu, Vom selecta titlul graficului și vom schimba stilul acestuia.
#1) Selectați Chart Title (Titlul graficului) din lista derulantă.
#2) Faceți clic pe Format Selection.
#3) Format Chart Title se va deschide în panoul din dreapta.
#4) Alegeți culoarea, stilul, marginea, etc., după cum doriți.
După câteva operații de formatare de bază, un titlu de grafic va arăta ca mai jos.
Resetați pentru a se potrivi cu stilul: Astfel, toate modificările vor fi resetate și se va reveni la stilul implicit.
Introduceți formele: Puteți insera forme cum ar fi linii, săgeți și, de asemenea, o casetă de text pentru o explicație mai bună.
Stil de formă: Puteți selecta diferite stiluri pentru zona de trasare. Selectați zona în care doriți să modificați stilul și faceți clic pe stil.
După aplicarea stilurilor la întreaga diagramă, coloana și rândurile sunt prezentate mai jos.
Aranjați: În cazul în care există mai multe diagrame pivot și acestea se suprapun unele peste altele în aceste opțiuni.
Aduceți înainte
- Selectați graficul pe care doriți să îl aduceți în față.
- Dați clic pe opțiunea Bring Forward pentru a aduce graficul cu un pas înainte.
Aduceți în față: Această opțiune va aduce graficul dvs. deasupra tuturor celorlalte grafice.
Trimiteți înapoi
- Selectați harta pe care doriți să o trimiteți înapoi.
- Faceți clic pe opțiunea Send backward (Trimitere înapoi) pentru a trimite graficul cu un nivel înapoi.
Trimiteți în spate: Aceasta este utilizată pentru a trimite graficul selectat înapoi la toate celelalte grafice.
Panoul de selecție
Puteți decide vizibilitatea graficului folosind panoul de selecție. Această pagină vă arată toate graficele și slicerul disponibile și puteți face clic pe pictograma cu ochi pentru a decide dacă elementul respectiv trebuie să fie vizibil sau nu pe foaia de lucru.
Dimensiune: Aceasta este utilizată pentru a personaliza înălțimea, lățimea, înălțimea și lățimea graficului pivot etc.
Întrebări frecvente
Î #1) Cum se creează o diagramă pivot în Excel?
Răspuns: Există două moduri de a crea diagrame pivot.
#1) Creați din sursa de date
- Selectați orice celulă din tabelul sursă de date.
- Mergeți la Inserare -> Diagramă pivotantă
- Selectați intervalul.
Aceasta va crea un tabel pivot și un grafic pivot gol.
#2) Creați de la PivotTable
Dacă aveți deja un tabel pivot:
- Selectați orice celulă din tabelul pivotant.
- Mergeți la Inserare -> Diagramă pivot
- Vă va oferi o listă de grafice disponibile, selectați graficul dorit.
Acest lucru va crea graficul cu datele relevante pentru tabelul pivot.
Î #2) De ce folosim o diagramă pivot în Excel?
Răspuns:
Există multe avantaje ale utilizării graficelor pivot:
- Acesta oferă o modalitate eficientă și ușoară de reprezentare a datelor într-o manieră grafică.
- Puteți rezuma cu ușurință datele prin tragerea câmpurilor dorite în oricare dintre cele 4 secțiuni disponibile ale tabelului.
- Oferă o modalitate eficientă de transformare a datelor brute într-un format organizat, manipulându-le cu ușurință prin filtrare, aliniere, personalizare, calcule, etc.
Î #3) Cum pot formata o diagramă pivot?
Răspuns: Puteți formata graficul utilizând diverse opțiuni prezente în secțiunea Instrumente pentru graficul pivot. Aceasta vă oferă opțiuni pentru a adăuga noi câmpuri, a schimba culoarea, fontul, fundalul etc., pentru a face graficul să arate mai interactiv și mai prezentabil. Faceți clic oriunde pe graficul pivot pentru a deschide secțiunea Instrumente.
Î #4) Pot adăuga un slicer la diagramele pivotante?
Răspuns: Da, diagramele pivotante pot fi completate cu slicere și linii de timp, ceea ce ne va ajuta să filtrăm simultan atât graficul, cât și tabelul pivotant aferent.
- Faceți clic pe graficul pivot.
- Mergeți la Tabul Analiză -> Inserare Slicer .
- În caseta de dialog Selectați câmpurile pe care doriți să le creați pentru feliere.
- Faceți clic pe OK
Puteți adăuga apoi o conexiune Filter pentru a lega un slicer de mai multe diagrame.
Concluzie
În acest tutorial, am învățat despre diagramele pivot din Excel. Este o reprezentare vizuală a unui tabel pivot sau a unei surse de date. Ne ajută să vizualizăm datele de sinteză într-un format grafic cu diferite tipuri de diagrame.
Există mai multe opțiuni disponibile pentru a filtra, formata, personaliza diagramele și adăuga diverse aspecte după cum doriți. O diagramă pivot în Excel este utilă atunci când se tratează o cantitate mare de date. Este foarte utilă în timpul unei prezentări de afaceri cu filtrare cu un singur clic, filtrare în funcție de timp, calcule personalizate etc.