Meniul
AcasăJocuri

Diagramă dintr-un tabel pivot Excel. Diagrame pivot

Lucrarea practică nr. 11

Tema lecției: Tabele pivot și diagrame.

Scopul lecției: Dobândiți abilități practice în crearea de tabele pivot și diagrame.

Planul lecției :

Crearea unui tabel pivot. Personalizarea aspectului tabelului pivot. Actualizarea datelor într-un tabel pivot. Modificați formatul câmpurilor dintr-un tabel pivot. Creați diagrame pivot. Crearea unui tabel pivot.

Creați un nou registru de lucru.

Redenumiți foaia1 în Vânzări.

Pe o foaie Vânzări Creați un tabel ca acesta:

Calculați valoarea vânzărilor. Suma vânzărilor = Preț*Cantitate.

Selectați orice celulă din intervalul tabelului sursă.

A executa Date → PivotTable.

În prima fereastră Expert tabel pivot Clic Mai departe.

În fereastra următoare trebuie să specificați intervalul tabelului sursă. Dacă programul nu face acest lucru, specificați singur intervalul. Clic Mai departe.

În fereastra următoare, specificați pentru a plasa tabelul pe o foaie nouă și faceți clic Gata.

Aspectul PivotTable a fost plasat pe o foaie nouă. Redenumiți această foaie în Analiză.

Personalizarea aspectului tabelului pivot.

Du-te la foaie Analiză.Și selectați una dintre celulele de aspect PivotTable.

După ce selectați o celulă de aspect PivotTable, ar trebui să apară o bară de instrumente Tabele pivotȘi fereastra Lista câmpurilor din tabelul pivot.

Folosind mouse-ul, trageți în zonă Rânduri Camp Nume, Spre câmpul zonei coloane Lună, Spre regiune Elemente de date Trageți câmpul Suma de vânzări.

După parcurgerea pașilor de mai sus, tabelul ar trebui să arate astfel:

Actualizarea datelor într-un tabel pivot.

Când actualizați datele din tabelul sursă, datele din tabelul pivot nu sunt actualizate automat.

Du-te la foaie VânzăriȘi modificați valoarea celulei cu orice preț la 55555.

Du-te la foaie Analiză.

Selectați o celulă în interiorul aspectului PivotTable.

Pe bara de instrumente Tabele pivot Faceți clic pe butonul de actualizare a câmpului

Verificați dacă datele au fost actualizate.

Notă: Pentru a schimba formatul raportului PivotTable, faceți clic pe butonul din bara de instrumente Tabele pivot.

Modificați formatul câmpurilor dintr-un tabel pivot.

Selectați una dintre celulele PivotTable.

Faceți clic dreapta pe el și selectați din meniul contextual Parametrii câmpului.

În fereastra În câmp Operațiune Selectați In medie.

Faceți clic pe butonul Format.

La fereastră Format de celule Specificați formatul monedei. Clic BINE.

Înapoi la fereastră Calcul câmpului tabel pivot clic BINE.

Exercițiu: Adăugați un câmp în zona de rânduri PrețȘi instalați Operațiune - Sumă;

Format: monetar.

Creați diagrame pivot.

Diagramele pivot sunt o afișare grafică a modificărilor indicatorilor câmpurilor din tabelele pivot.

Pe bara de instrumente Tabele pivot Faceți clic pe pictogramă Chart Wizard.

Excel creează automat o nouă foaie cu un PivotChart gata făcut.

Accesați foaia cu diagrama pivot.

Pe bara de instrumente Diagrame Faceți clic pe butonul Tipul graficului.

În lista de diagrame, indicați Diagramă cu bare.

Întrebări de control:

Cum se creează un raport PivotTable? Cum să setați formatarea automată pe o structură de raport PivotTable? Cum se modifică parametrii câmpului într-un tabel pivot? Cum se actualizează câmpurile tabelului pivot? Cum se creează o diagramă pivot? Cum schimb tipul de diagramă pivot?

Concluzie:În această lucrare, am studiat modalități de a crea tabele pivot și diagrame. După finalizarea acestei lucrări, putem spune că raportul PivotTable vă permite să rezumați rapid cantități mari de date, să schimbați rânduri și coloane pentru a obține diferite rezumate ale datelor sursă, să filtrați datele prin afișarea diferitelor pagini sau să afișați detalii în zonele dorite. .


În acest articol, vom analiza tehnicile pentru crearea și utilizarea unei diagrame pivot.


O diagramă pivot este o diagramă care combină și rezumă automat cantități mari de date. Puteți crea o diagramă pivot bazată fie pe un tabel simplu, fie pe un tabel pivot.


În primul rând, ne vom uita la modul de a crea un PivotChart dintr-un PivotTable. Pentru instruire practică, puteți folosi exemplul nostru descarcându-l de pe link (simple table.xlsx).


Pentru a crea un tabel pivot bazat pe date dintr-o diagramă gata făcută, urmați acești pași:


1. Selectați tabelul pivot de care aveți nevoie făcând clic pe el;

2. Pe fila Introduce in grup Diagrame selectați tipul de diagramă dorit.




Am ales un grafic linie simplu. Ca rezultat, a apărut un grafic gata făcut care conține date din tabelul pivot, precum și o fereastră:




Vă rugăm să rețineți fereastra Zona de filtrare PivotTable nu vă permite să modificați condițiile pentru construirea unei diagrame - adică nu puteți construi o diagramă bazată pe coloanele tabelului principal (de exemplu, folosind o coloană Volumul vânzărilor, buc.) care nu sunt incluse în tabelul rezumativ. Și invers - includerea datelor într-un tabel pivot este reflectată simultan în diagrama pivot:




Fereastră Zona de filtrare PivotTable conceput pentru gestionarea convenabilă a unui tabel pivot și a unei diagrame construite pe baza acestuia:



Prin modificarea valorilor filtrelor și câmpurilor de axe, puteți afișa segmentul de date de interes pentru anumite valori ale axelor de coordonate. De exemplu, este foarte convenabil să analizați datele vânzărilor în magazine individuale pentru ultima săptămână:




În mod implicit, un diagramă pivot este creat pe aceeași foaie în care se află tabelul pivot. Acest lucru nu este întotdeauna convenabil, așa că puteți muta diagrama pivot pe o nouă foaie folosind comanda Mutați diagrama din meniul contextual. Configurarea formatului unei diagrame pivot este aceeași ca o diagramă obișnuită, dar folosind comenzi din grupul de file Lucrul cu PivotCharts, care se deschid după ce faceți clic pe diagrama pivot.


După cum ați înțeles deja, o diagramă pivot construită pe baza unui tabel pivot existent este strâns legată de aceasta. Acest lucru nu este întotdeauna convenabil, așa că deseori are sens să construiți imediat o diagramă pivot bazată pe tabelul de bază. Pentru a face acest lucru aveți nevoie de:


1. Selectați intervalul de date de care avem nevoie (sau plasați cursorul pe tabelul de care avem nevoie - apoi Excel va introduce automat întregul tabel în intervalul de date);


2. Pe fila Introduce in grup Mese selectați secțiunea Masă rotativă, și apoi punct Diagramă pivot.

Data: 16 martie 2017 Categorie:

Bună prieteni. Vreau să dedic această scurtă postare graficelor pivot din Excel. Acest instrument este ignorat nemeritat de mulți utilizatori ai programului. Cu toate acestea, atunci când sunt cuplate cu PivotTables și folosind funcționalități avansate (cum ar fi slicere), PivotCharts pot crea o experiență excelentă pentru utilizator. Și apoi, pentru a primi raportul în forma dorită, utilizatorul va avea nevoie de doar câteva minute, chiar și atunci când tabelul cu datele sursă conține mii de înregistrări.

Iată un exemplu. Am adăugat rapid un grafic pivot la tabelul rezumat cu vânzările managerilor pe regiune. Întregul proces de creare a unui tabel și diagramă nu a durat mai mult de un minut:

Graficul este dinamic și se va modifica pe măsură ce tabelul pivot se va modifica.

Cum să construiți o diagramă pivot

Pentru a construi o diagramă pivot, trebuie să efectuați următoarea secvență de acțiuni:

  1. Construim un tabel pivot care va fi sursa de date pentru diagramă
  2. Selectați orice celulă de tabel și faceți clic pe panglică: Lucrul cu tabele pivot – Analiză – Instrumente – Diagramă pivot
  3. În fereastra care se deschide, selectați și faceți clic pe OK
  4. Daca este necesar,

Apropo, dacă aveți Microsoft Office 2013 sau o versiune superioară, puteți sări peste primul punct. Doar faceți clic pe panglică Inserare - Diagrame - Diagram pivot. Procesul de creare se va asemăna cu aspectul unui tabel pivot, cu toate acestea, tabelul nu va fi afișat. În versiunile anterioare, totuși, va trebui mai întâi să construiți un tabel pivot.

Atâta tot, am făcut o diagramă rezumativă și acum rapoartele noastre au căpătat un aspect finit, iar calculele au devenit rapide și ușoare!

În următorul articol, aștept cu nerăbdare un subiect foarte important -. Dacă nu ești familiarizat cu el încă, citește-l, nu vei regreta. De exemplu, îl folosesc aproape în fiecare zi.

Ca întotdeauna, aștept întrebările și comentariile voastre!

Diagrame

Diagramele sunt folosite pentru a prezenta serii de date numerice sub formă grafică. Acestea sunt concepute pentru a facilita înțelegerea unor volume mari de date și a relațiilor dintre diferitele serii de date. Pentru a construi un grafic (diagramă) pe baza datelor disponibile, trebuie să: a) Selectați datele care vor participa la construirea diagramei (nu este nevoie să selectați celule care vor fi numele seriei și etichetele de-a lungul axei X ) așa cum se arată în fig. 115; b) în fila panglică „Inserare”, faceți clic pe oricare dintre tipurile de diagrame prezentate în grupul „Diagrame” (Fig. 116). După aceasta, pe foaie va apărea o diagramă bazată pe datele selectate (Fig. 117).

Elementele diagramei sunt:

Zona graficului (1);

Zona graficului (2);

Elemente de date din seria de date care sunt utilizate pentru a construi diagrama (3);

Axe orizontale (axa categoriei) și verticale (axa valorilor) de-a lungul cărora este construită diagrama (4);

Legenda diagramei (5);

Titlul diagramei și titlurile axelor (6);

În mod implicit, titlul diagramei și etichetele axelor nu sunt afișate pe diagramă. Pentru a le afișa, trebuie să selectați diagrama, apoi pe fila panglică „Design”, în grupul „Aspecte diagramă”, selectați un aspect care conține anumite etichete (Fig. 118).

Pentru a schimba numele seriilor și etichetelor de-a lungul axei orizontale (axa categoriei), trebuie să faceți clic dreapta pe zona diagramei și să selectați „Selectați datele...” în meniul care se deschide. După aceasta, se va deschide fereastra de selecție a datelor „Selectare sursă de date”. În partea stângă a acestei ferestre puteți schimba numele rândurilor. Pentru a face acest lucru, selectați rândul dorit și faceți clic pe butonul „Editați” din partea stângă a ferestrei. În fereastra „Edit Series” care se deschide (Fig. 119), în câmpul Series name, introduceți numele necesar al seriei sau selectați celula care conține numele seriei. În cazul nostru, aceasta este celula A2 (datele originale sunt prezentate în Fig. 115). Pentru a schimba etichetele de-a lungul axei orizontale (axa categoriei), trebuie să faceți clic pe butonul „Schimbare” din partea dreaptă a ferestrei „Selectați sursa de date” și în fereastra „Etichete axelor” care se deschide, selectați intervalul care conține etichete. În cazul nostru, acestea sunt celule de la B1 la F1 (datele originale sunt prezentate în Fig. 115).

Pentru a schimba tipul de diagramă după ce a fost construită, faceți clic dreapta pe diagramă și selectați „Schimbați tipul diagramei...” din meniul care se deschide. Opțiune alternativă: Selectați diagrama și pe panglică, în fila „Design”, faceți clic pe butonul „Modificați tipul diagramei”.

Pentru a muta o diagramă într-o foaie separată, trebuie să faceți clic dreapta pe diagramă și să selectați „Mutați diagrama...” din meniul care se deschide. În fereastra „Mutați o diagramă” care se deschide, selectați elementul „Pe o foaie separată”, schimbați numele acestei foi dacă doriți și faceți clic pe butonul „Ok” (Fig. 122).

Tabele pivot

Un raport PivotTable vă permite să rezumați, să analizați, să explorați și să prezentați date dintr-o foaie de calcul. Un raport PivotTable poate fi deosebit de util atunci când trebuie să rezumați un set mare de numere, iar datele și subtotalurile combinate vă permit să priviți datele din unghiuri diferite și să comparați măsuri similare din diferite grupuri de date.

Exemplul de mai jos se bazează pe un tabel care conține costurile diferitelor tipuri de rechizite de birou pentru diferite departamente pentru perioada 2009-2013.

Înainte de a crea un PivotTable, trebuie să selectați intervalul de date care va fi utilizat pentru PivotTable. Un interval poate fi fie un interval de celule, fie mai multe coloane. Diferențele în alegerea unui interval sunt următoarele: dacă selectați un interval limitat de date, atunci când adăugați date noi în rândurile de sub intervalul selectat, aceste date nu vor fi luate în considerare în tabelul pivot. Dacă selectați coloane, atunci când adăugați date noi la rândurile de mai jos, aceste date vor fi luate în considerare în tabelul pivot.

Important: intervalul selectat nu trebuie să conțină rânduri goale sau celule goale în primul rând.

În fig. 123 arată datele sursă din care va fi creat tabelul pivot. Pentru a crea un tabel pivot, trebuie să selectați un interval de date (în acest caz, intervalul este A1:G10). După aceasta, pe fila panglică „Insert”, trebuie să faceți clic pe butonul „Pivot Table” (Fig. 124)

Va fi creat un tabel pivot gol și se va deschide „Lista câmpurilor tabelului pivot” (Fig. 126). În partea de sus a acestei liste sunt toate câmpurile PivotTable disponibile, iar în partea de jos este aspectul tabelului în care puteți adăuga aceste câmpuri. Pentru a adăuga un câmp la un aspect de tabel, trebuie să faceți clic stânga pe numele câmpului din lista de câmpuri și să îl trageți în containerul de aspect corespunzător. A doua opțiune: faceți clic dreapta pe numele câmpului dorit din lista de câmpuri și în meniul care se deschide, selectați comanda „Adăugați la filtrul de raport”, „Adăugați la numele coloanelor”, „Adăugați la numele rândurilor” sau „ Adaugă la valori”. Este logic să adăugați câmpuri care conțin valori text sau date în containerele Nume rânduri sau Nume coloane și date numerice în containerul Valori.

Exemplul nr. 1. Dacă trebuie să afișați costurile fiecărui departament pentru materiale de birou în funcție de an în tabelul pivot, atunci trebuie să adăugați câmpul „Numărul departamentului” la containerul „Nume rânduri” și câmpul „Anul” la „Numele coloanelor”. ” container (sau invers – depinde doar care va fi afișat în numele rândurilor sau, respectiv, coloanelor). Trebuie să adăugați câmpul „Suma cost” la containerul „Valori” (Fig. 127). Rezultatul este un tabel rezumativ prezentat în Fig. 128.

Orez. 128

Exemplul nr. 2. Dacă trebuie să afișați costurile totale ale fiecărui departament pentru fiecare tip de produs în tabelul pivot, atunci trebuie să adăugați câmpul „Număr departament” la containerul „Nume rânduri” și câmpul „Nume produs” la „ Container Nume coloane” (sau invers – depinde doar de asta ceea ce va fi afișat în titlurile rândurilor sau, respectiv, coloanelor). Trebuie să adăugați câmpul „Suma cost” la containerul „Valori” (Fig. 129). Rezultatul este un tabel rezumativ prezentat în Fig. 130.

Tipul de operație utilizat în containerul Valori poate fi mai mult decât o simplă sumă. Pentru a selecta tipul de operație, trebuie să faceți clic stânga pe săgeata din numele câmpului din containerul „Valori” și în meniul care se deschide, selectați „Parametri câmp valoric...” (Fig. 131). În fereastra „Value Field Parameters” care se deschide, selectați tipul de operație dorit și faceți clic pe „Ok” (Fig. 132)

Pentru a ascunde/afișa lista de câmpuri dintr-un tabel pivot, faceți clic dreapta pe tabel, iar în meniul care apare, selectați „Ascunde lista de câmpuri” sau „Afișează lista de câmpuri” în consecință. Dacă datele sursă s-au modificat, PivotTable poate fi reîmprospătat pentru a reflecta modificările efectuate. Pentru a face acest lucru, faceți clic dreapta pe tabel și selectați „Actualizare” din meniul care apare. Puteți efectua aceleași operații pe celulele unui tabel pivot ca și pe celulele tabelelor obișnuite: aplicați diferite tipuri de formatare, setați filtre, efectuați sortarea etc.

După cum se poate vedea din Figura 134, toate datele au fost selectate din datele sursă al căror număr de departament este „Departamentul 2” (numele rândului în tabelul pivot) și numele produsului este egal cu „Creioane” (numele coloanei în tabelul pivot).

Diagramă pivot

Procesul de creare a unei diagrame pivot este aproape identic cu procesul de creare a unui tabel pivot. Mai întâi, selectați intervalul de date din care va fi construit diagrama pivot, apoi pe fila panglică „Insert”, faceți clic pe săgeata din colțul butonului „Pivot Table” și selectați „Pivot Chart” (Fig. 135) . În caseta de dialog New PivotTable and PivotChart, selectați opțiunea Selectați tabel sau interval și verificați dacă intervalul de celule este corect în caseta Tabel sau Interval. De asemenea, în funcție de locul în care doriți să plasați tabelul creat, trebuie să selectați „Pe o foaie nouă” sau „Pe o foaie existentă” și să faceți clic pe butonul „Ok” (Fig. 136).

Se creează un PivotTable gol și un PivotChart gol și se deschide Lista de câmpuri PivotTable. Singura diferență dintre această listă și cea similară la crearea unui tabel pivot este numele containerelor din aspect. Deci, în loc de containerul „Titluri rând”, există acum „Câmpuri (categorie) axe”, iar în loc de containerul Titluri coloane, există acum „Câmpuri legenda (Rânduri)” (Fig. 137). Restul funcționalitatea rămâne aceeași: nume de câmpuri sunt adăugate containerelor de layout și pe baza acestora se formează un tabel rezumat și o diagramă.

Important: atunci când creați o diagramă pivot, diagrama în sine și tabelul pivot creat cu acesta sunt indisolubil legate între ele, iar când modificați una, cealaltă se modifică în consecință.

Cu diagramele pivot, puteți efectua același set de acțiuni ca și în cazul celor obișnuite: schimbați tipul, aspectul, stilul diagramei, adăugați și modificați numele axelor și diagramei în sine, transferați diagrama într-o foaie separată.

Dacă adăugați ambele câmpuri „Anul” și „Numărul departamentului” la un container (de exemplu, „câmpurile pentru axe (categorie)”), rezultatul va fi următorul tabel pivot (Fig. 141) și diagramă pivot (Fig. 142) .

După cum se poate observa din cifre, în acest caz, în tabelul pivot, datele sunt grupate automat după numărul de departamente, iar în cadrul grupelor apar anii, valoarea costurilor este afișată într-o coloană. În graficul rezumat, datele sunt, de asemenea, grupate după numele departamentului pe axa categoriilor.Anii sunt afișați și în cadrul grupelor, iar datele din grafic reprezintă suma totală a costurilor pentru fiecare departament pentru un anumit an.

Crearea unei diagrame bazată pe un PivotTable este foarte ușoară. Comutați în modul Diagramă pivot(Vizualizare diagramă pivot), iar pe ecran va apărea o diagramă pivot, aproximativ la fel ca în Fig. 8.50.

Asemenea unui tabel pivot, are un câmp de filtru - „Țara”, care este afișat în colțul din stânga sus al ecranului, câmpuri de rând și coloană, care sunt afișate aici în dreapta și în jos. Această diagramă pivot este strâns legată de tabel. Dacă comutați la modul PivotTable și îi schimbați structura, această modificare va fi afișată în PivotChart și invers, dacă schimbați structura PivotChart acum, atunci această modificare va apărea în PivotTable când reveniți la acel mod.

Orez. 8.50.

Dar puteți crea o diagramă direct pe baza unei interogări sau a unui tabel. În acest caz, un tabel pivot va fi creat în același timp. Un PivotTable și un PivotChart sunt două forme de prezentare a acelorași date.

Ca exemplu, se propune construirea unui diagramă pivot pentru interogarea „Vânzări de către angajați și țări”.

  1. Deschideți această interogare în vizualizarea Design.
  2. Cererea are doi parametri: [Data de început] și [Data de încheiere], care sunt folosite pentru filtrarea datelor. Pentru un PivotChart, acești parametri nu sunt necesari, așa că mai întâi eliminați expresia din linie Condiție de selecție(Criterii), apoi deschideți caseta de dialog Opțiuni(Parametrii de interogare) (vezi secțiunea „Interogări cu parametri” din capitolul 4)și eliminați ambele opțiuni.
  3. Faceți clic pe săgeata de pe buton Vedere(Vizualizare) bara de instrumente și selectați elementul din meniu Diagramă pivot(Vizualizare diagramă pivot). Va apărea o fereastră, a cărei parte principală este ocupată de zona de afișare a diagramei (Fig. 8.51), limitată de axe de coordonate și marcată cu linii de grilă. În plus, puteți vedea zona de filtrare, care joacă același rol și este situată în același mod ca în tabelul pivot, zona de categorii și zona de rând, care corespund rândurilor și coloanelor din tabelul pivot. Câmpurile ale căror valori ar trebui trasate de-a lungul axei X (orizontale) sunt transferate în zona categoriilor, iar câmpurile ale căror valori corespund fiecare unei serii de puncte sau coloane de pe diagramă (în funcție de tipul de diagramă) sunt transferate spre zona seriei. Aceste câmpuri corespund câmpurilor coloanelor dintr-un diagramă pivot. Zona de date conține câmpuri ale căror valori vor fi afișate de-a lungul axei Y (verticală) a diagramei.

Orez. 8.51.

  1. Trageți câmpul „Țară” din lista de câmpuri în zona de filtrare, câmpurile „Nume” și „Prenume” în zona categorii și câmpul „Data expedierii” în zona rândurilor. După lună). Urmărește cum se modifică zona diagramei.[ Dacă nu vedeți caseta de dialog cu o listă de câmpuri, faceți clic pe butonul Lista câmpurilor(Fields List) bara de instrumente. ]
  2. Mutați câmpul Valoarea vânzării în zona de date și diagrama este gata. Faceți clic pe butonul Adăugați o legendă(Show Legend) pentru a afișa legenda, după care veți obține diagrama prezentată în Fig. 8.52.
  3. De asemenea, puteți introduce etichete pentru axele diagramei. Faceți clic pe inscripție Numele axei(Titlul axei) sub axa X. Afișați fereastra Proprietăți(Proprietăți) și extindeți fila Format(Format). Intră în câmp Titlu(Titrare): Angajații. În mod similar, introduceți eticheta Volum vânzări pentru axa Y.