Cum se schimbă aspectul unui tabel pivot în Excel. Testați capacitățile soluțiilor plătite

MS Excel Pivot Table este un instrument puternic de analiză a datelor. Acest instrument vă permite să extrageți informații dintr-o gamă largă de date, în orice context, cu doar câteva clicuri de mouse. În acest articol voi încerca să explic într-un limbaj simplu și ușor de înțeles, pas cu pas, ce și cum să faceți pentru a obține acest instrument MS Excel extrem de convenabil la dispoziția dumneavoastră.

Cerințe pentru datele sursă.

Deci, baza oricărui tabel pivot este o matrice de date construită corect - „tabelul corect”. În figura de mai jos puteți vedea un exemplu de matrice de date format corect:

Să ne uităm la care este, strict vorbind, „corectitudinea” acestui tabel? Corectitudinea este ca:

Fiecare coloană conține doar același tip de date, coloana A numai Date, în coloană ÎN numai Documente, în coloană CU doar Clienți, Bani în bani, Furnizori în Furnizori, Categorii de produse în Categorii și așa mai departe... Într-un astfel de tabel putem folosi foarte ușor Filtru;
Coloanele cu același tip de date nu se repetă;
Nu există rânduri totale în tabel, doar „date curate”;
Nu există celule goale în datele text din tabel, fiecare linie conține numele Clientului, Produsului, Furnizorului, Managerului și așa mai departe...

Apropo, într-o matrice, nu contează deloc în ce secvență se află coloanele și după ce câmp sunt sortate datele. Acest lucru nu afectează în niciun fel construcția Tabelului Pivot.

Și iată un exemplu de „tabel greșit”, din care, chiar dacă spargi, Tabelul Pivot nu va fi construit și, chiar dacă este construit, va fi complet imposibil să lucrezi cu datele... Nostru „ sistemele de contabilitate preferate, de obicei, ne „mulțumesc” cu astfel de tabele, care ni le oferă sub formă de rapoarte care sunt complet nepotrivite pentru analize ulterioare:

Iată un alt exemplu de „Matrice invalidă”:

Există o grămadă de „neregularități” aici:

În primul rând, coloanele „Furnizor” și „Categorie” au celule goale, așa că nu putem folosi filtrul;
În al doilea rând, aproape în fiecare coloană există linii „Total...”, sunt complet inutile pentru construirea Tabelului Pivot, în plus, vor sta doar în cale;
În al treilea rând, nici coloana „Total mare” nu este necesară;
În al patrulea rând, același tip de date, și anume „Bani”, se află în trei coloane: „Ian”, „Feb” și „Mar”, ceea ce va complica semnificativ construcția Tabelului Pivot, ceea ce înseamnă că va trebui să face ceva cu el...

Dar toate acestea sunt pentru o conversație separată, dacă doriți să învățați cum să convertiți rapid astfel de „tabele strâmbe” în „matrice corecte”, citiți articolele: „Cum să construiți rapid un tabel pivot dintr-un raport 1C sau SAP?” „Cum se transformă rapid un tabel într-o matrice pentru tabele pivot?

De fapt, construirea tabelului pivot:

Luați „matricea corectă”, plasați cursorul în orice celulă a matricei, selectați fila „Inserare” din meniul principal, în colțul din stânga, în secțiunea „Tabele”, faceți clic pe butonul „Tabel pivotant”:

În caseta de dialog „Creați tabel pivot” care se deschide, faceți clic pe „OK”:

MS Excel va crea o nouă foaie pe care va indica locul în care va fi inserat Tabelul Pivot, iar în dreapta va afișa o fereastră pentru configurarea câmpurilor Pivot Table, în care veți vedea toate denumirile coloanelor din matricea ta:

Să începem configurarea tabelului pivot. Ce fel de tăietură vrem? Categoriile de produse după manager - vă rugăm. Deplasați mouse-ul peste câmpul „Categorie”, faceți clic stânga pe el și trageți-l în câmpul „RINDURI”. Câmpul „Manager” este tras în „COLUMNS”, iar „Suma” este tras în „VALUES”:

Excel este un program destul de puternic în ceea ce privește calculele, dar majoritatea utilizatorilor îl folosesc la nivel de calculator. Unul dintre instrumentele puternice disponibile care poate economisi mult timp și poate prezenta datele mai clar este Tabelele Pivot, iar în continuare ne vom uita la cum să facem un Tabel Pivot în Excel.

Înainte de a realiza un tabel pivot în Excel, ar trebui să înțelegeți cerințele pe care trebuie să le îndeplinească datele sursă. În cel mai simplu caz, un tabel pivot în Excel este creat pe baza datelor tabelului în versiuni mai complexe, acesta este creat pe baza datelor încărcate din baze de date sau alte documente;

Indiferent de sursă, cerințele de date sursă pentru crearea unui tabel pivot în Excel vor fi aceleași. Toate valorile trebuie prezentate sub forma unui tabel, la începutul căruia trebuie să existe un antet. Nu este permis să aveți rânduri sau coloane goale sau date străine în afara tabelului care este direct adiacent acestuia. De asemenea, este de dorit să nu existe celule de tabel necompletate, ceea ce poate duce la calcule incorecte. Și, în sfârșit, tabelul nu ar trebui să aibă celule îmbinate sau rânduri ascunse cu coloane.

De exemplu, să luăm în considerare cea mai simplă opțiune și să încercăm să facem un tabel pivot în Excel bazat pe un raport anual improvizat privind proviziile de produse.

Trebuie să activăm orice celulă de sub antetul tabelului, mergeți la filă "Introduce"și selectați un element de meniu "Masă rotativă".

În fereastra care apare, Excel vă cere să confirmați că adresele întregului tabel sunt corecte sau să indicați o sursă externă, precum și să indicați locația tabelului pivot. În cazul nostru, toate valorile rămân implicite.

Pe noua foaie vedem o zonă pentru tabelul pivot și o listă de câmpuri care trebuie plasate în zonele tabelului pivot.

Acum trebuie să decidem ce anume vrem să obținem și ce date să analizăm. De exemplu, vrem să aflăm în ce orașe și ce bunuri le-a furnizat un anumit manager pe lună și pe trimestru și trebuie să cunoaștem și volumul. Pentru a face acest lucru, în regiune "Filtre" tragem campul cu mouse-ul "Administrator", spre zona „Coloane” muta terenul "Data de".

Acum să aruncăm o privire la șiruri. Trebuie să plasăm două câmpuri pe linii, iar ordinea în care sunt adăugate joacă un rol important. Dacă punem primul câmp "Produs", iar sub ea "Oraș", produsele vor deveni liste derulante care arată toate orașele în care produsul a fost expediat. Schimbarea genurilor într-o zonă "Siruri de caractere" vom obține un rezultat diferit.

Mai avem o regiune "Valori", la care vom muta terenul "Greutate".

După cum puteți vedea, numărăm numărul de expedieri, și nu masa, care ne interesează. Aceasta înseamnă că există ceva în neregulă cu datele noastre sursă. În tabelul original, trebuie să ajustăm ușor valorile și să setăm celulele la un format numeric.

După ce au fost efectuate modificările, accesați tabelul pivot și actualizați valorile făcând clic dreapta în tabelul pivot și selectând elementul corespunzător.

După cum puteți vedea, câmpul a fost înlocuit în listă și în zonă "Valori" trebuie re-adăugat.

Acum tabelul pivot din Excel este gata și puteți filtra toate livrările de către un anumit manager.

Trebuie să lucrați cu tabele pivot Excel în diferite zone. Puteți procesa rapid cantități mari de informații, puteți compara și grupa date. Acest lucru facilitează foarte mult munca managerilor, vânzătorilor, directorilor, marketerilor, sociologilor etc.

Tabelele pivot vă permit să generați rapid rapoarte diferite pe aceleași date. În plus, aceste rapoarte pot fi personalizate, modificate, actualizate și detaliate în mod flexibil.

Creați un raport utilizând expertul PivotTable

Avem un tabel de antrenament cu date:

Fiecare linie ne oferă informații complete despre o tranzacție:

in ce magazin au avut loc vanzarile;
ce bunuri și pentru ce sumă;
care dintre vânzători a încercat;
când (ziua, lună).

Dacă acesta este un lanț imens de magazine și vânzările merg bine, atunci într-un sfert dimensiunea mesei va deveni terifiantă. Va fi foarte dificil să analizezi datele pe o sută de rânduri. Și va dura mai mult de o zi pentru a compila un raport. Într-o astfel de situație, un tabel pivot este pur și simplu necesar.

Să creăm un raport utilizând expertul Pivot Table. În noile versiuni de Excel, din anumite motive, acesta este ascuns adânc în setări:

Selectați „Fișier” - „Opțiuni” - „Bară de instrumente Acces rapid”.
În lista derulantă din coloana din stânga: „Selectați o echipă din”, selectați „Toate echipele”.
În coloana din stânga, găsiți în ordine alfabetică și evidențiați: „Asistent tabel pivot și diagramă”. Faceți clic pe butonul dintre coloane: „Adăugați”, astfel încât instrumentul să se mute în coloana din dreapta și faceți clic pe OK.

Acum instrumentul se află în panoul de acces rapid, ceea ce înseamnă că este întotdeauna la îndemână.

Raportul final poate fi formatat și modificat.

Cum se actualizează datele într-un tabel pivot Excel?

Acest lucru se poate face manual și automat.

Configurarea actualizărilor automate când datele se modifică:

În fila „Lucrul cu tabele pivot” (trebuie să faceți clic pe raport), selectați meniul „Opțiuni”.
Deschideți „Opțiuni avansate pentru tabelul pivot”. Vrăjitorul se deschide.
În secțiunea „Date”, bifați caseta de lângă „Actualizare la deschiderea unui fișier”.

Acum, de fiecare dată când deschideți un fișier cu date modificate, tabelul pivot va fi actualizat automat.

Câteva secrete de formatare

Când rezumăm o cantitate mare de date într-un raport, gruparea poate fi necesară pentru a trage concluzii și a lua unele decizii. Să presupunem că trebuie să vedem rezultatele pentru o lună sau un trimestru.

Gruparea după dată într-un tabel pivot Excel:

Primim un raport care arată clar sumele vânzărilor pe lună. Să experimentăm și să setăm pasul la „Blocuri”. Rezultatul este un tabel rezumativ ca:

Dacă numele vânzătorilor nu este important pentru analiza activităților lanțului de magazine, putem genera un raport cu profit trimestrial.

Pentru a elimina rezultatele grupării, trebuie să faceți clic dreapta pe celula de date și să faceți clic pe Degrupare. Sau selectați această opțiune în meniul „Structură”.

Lucrul cu rezultate

Avem un raport de sinteză ca acesta:

Rezultatele sunt vizibile pe lună (realizat prin Grupare) și după nume de produse. Să facem raportul mai convenabil de studiat.

Cum să adăugați totalurile în partea de sus într-un tabel pivot:

Nu mai există acea aglomerație care a făcut dificilă perceperea informațiilor.

Cum să ștergeți subtotalurile? Doar în fila de aspect, selectați „Nu afișați subtotaluri”:

Vom primi un raport fără sume suplimentare:

Detalii despre informații

Tabelele rezumative uriașe, care sunt compilate pe baza unor tabele „străine”, trebuie periodic detaliate. Nu știm de unde provine suma dintr-o anumită celulă Excel. Dar puteți afla dacă ați împărțit tabelul pivot în mai multe foi.

Putem muta întregul PivotTable într-o foaie nouă selectând butonul Mutare din fila Acțiuni.

În mod implicit, absolut toate informațiile din coloana pe care o adăugăm în raport sunt plasate în tabelul pivot.

În exemplul nostru – TOATE produsele, TOATE datele, TOATE sumele și magazinele. Este posibil ca utilizatorul să nu aibă nevoie de unele elemente. Pur și simplu îngrădesc raportul și vă împiedică să vă concentrați asupra principalului lucru. Să eliminăm elementele inutile.

Faceți clic pe OK - tabelul pivot se schimbă.

Acum, folosind un exemplu simplu, vom învăța cum să creăm tabele pivot și să ne familiarizăm cu unele dintre capacitățile acestora.

De exemplu, să luăm declarația de active fixe a companiei ca date inițiale și să determinăm costul pozițiilor pentru fiecare ramură folosind un tabel pivot.

Înainte de a crea un PivotTable, asigurați-vă că nu există antete goale în tabelul sursă. Acest lucru este necesar deoarece fiecare coloană de tabel devine un câmp de tabel pivot din care pot fi colectate date.

De asemenea, vă sfătuiesc să convertiți intervalul de date inițial într-un tabel ( Acasă- Formatați ca tabel). Apoi, când adăugați sau eliminați rânduri și coloane, nu va trebui să modificați referința la intervalul respectiv din raportul rezumat.

Utilizatorii Excel 2013 pot selecta un PivotTable dintr-un aspect bazat pe recomandările oferite de Excel. Iată cum se face:

Creați singur un tabel pivot folosind designerul

Pentru cei care nu au Excel 2013, sau dacă tabelul pivot necesar nu este inclus în cele oferite, îl puteți crea de la zero folosind designerul. Pentru aceasta:

Important! Când selectați un interval, asigurați-vă că tabelul cu anteturi este selectat și că intervalul selectat nu include un rând total.

Indicați, de asemenea, unde doriți să plasați raportul tabelului pivot: pe o foaie nouă sau pe una existentă.

Recomand să plasați fiecare raport PivotTable pe o foaie separată. Acest lucru va ajuta la evitarea greșelilor dacă sunt adăugate date.

După generarea tabelului pivot, o zonă cu numele său va apărea în locația selectată. În mod implicit, raportul este apelat Tabel Pivot1. Pentru a începe, trebuie să faceți clic stânga pe această zonă. Ca rezultat, un aspect de tabel pivot se va deschide în partea dreaptă a foii.

Aspectul PivotTable constă dintr-o listă de câmpuri PivotTable care listează toate anteturile tabelului original și patru zone: FILTRE, COLONNE, LINIIȘi VALORI.

În funcție de zona în care mutăm acest câmp sau acel câmp, aspectul viitorului tabel pivot va depinde.

De exemplu, să creăm un raport rezumat în care calculăm valoarea de piață pentru fiecare grup din tabelul inițial. Acestea. dorim ca grupurile de vizavi să devină rânduri de raport rezumat. Aceasta înseamnă că trebuie să trageți câmpul Grup în zona de rânduri.

Această operație se poate face în încă 2 moduri:

bifați caseta de lângă câmp grup;

faceți clic pe podea grup faceți clic dreapta și selectați Adăugați la titlurile rândurilor .

După adăugarea câmpului, veți vedea o listă cu toate grupurile care se află în tabelul sursă:

Acum nu mai rămâne decât să adăugați suma după câmp Pretul din magazin. Pentru a face acest lucru, mutați câmpul Valoare de piață în zona de valori. Tabelul dorit a fost primit.

Un tabel pivot este utilizat pentru a analiza rapid cantități mari de date. Vă permite să combinați informații din diferite tabele și foi și să calculați rezultatul general. Acest instrument analitic universal extinde semnificativ capacitățile Excel.

Puteți genera totaluri noi pe baza parametrilor inițiali schimbând rândurile și coloanele. Puteți filtra datele afișând diferite elemente. Și, de asemenea, detaliază vizual zona.

Tabel pivot în Excel

De exemplu, folosim un tabel de vânzări de produse în diferite ramuri de vânzări.

Semnul arată în ce departament, ce, când și pentru ce sumă a fost vândut. Pentru a afla valoarea vânzărilor pentru fiecare departament, va trebui să calculați manual folosind un calculator. Sau creați un alt tabel Excel unde puteți afișa rezultatele folosind formule. Analizarea informațiilor folosind astfel de metode este neproductivă. Nu va dura mult să faci o greșeală.

Cea mai rațională soluție este crearea unui tabel pivot în Excel:

Simplu, rapid și de înaltă calitate.

Detalii importante:

Trebuie completat primul rând din intervalul specificat pentru reducerea datelor.
În tabelul de bază, fiecare coloană ar trebui să aibă propriul său titlu - este mai ușor să configurați un raport rezumat.
În Excel, puteți utiliza tabelele Access, SQL Server etc. ca sursă de informații.

Cum să faci un tabel pivot din mai multe tabele

Adesea trebuie să creați rapoarte rezumative din mai multe tabele. Există câteva semne informative. Trebuie să le combinăm într-unul comun. De dragul științei, să venim cu soldurile din depozitele din două magazine.

Procedura de creare a unui tabel pivot din mai multe foi este aceeași.

Să creăm un raport utilizând expertul Pivot Table:

După cum puteți vedea, în doar câteva clicuri puteți crea rapoarte complexe din mai multe foi sau tabele cu cantități variate de informații.

Cum să lucrați cu tabele pivot în Excel

Să începem cu cel mai simplu lucru: adăugarea și eliminarea coloanelor. De exemplu, să ne uităm la tabelul rezumativ al vânzărilor pentru diferite departamente (vezi mai sus).

În dreapta tabelului pivot aveam un panou de activități în care selectam coloane din lista de câmpuri. Dacă dispare, faceți clic pe semn.

Să adăugăm un alt câmp de raport la tabelul pivot. Pentru a face acest lucru, bifați caseta de lângă „Data” (sau vizavi de „Produs”). Raportul se schimbă imediat - apare dinamica zilnică a vânzărilor din fiecare departament.

Să grupăm datele în raport pe lună. Pentru a face acest lucru, faceți clic dreapta pe câmpul „Dată”. Faceți clic pe „Grup”. Selectați „pe lună”. Obțineți un tabel rezumativ ca acesta:

La modificați parametrii în tabelul pivot, debifați casetele de lângă câmpurile de rând existente și bifați celelalte câmpuri. Vom face un raport pe nume de produse, nu pe departamente.

Dar ce se întâmplă dacă eliminăm „data” și adăugăm „departament”:

Dar un astfel de raport se poate face dacă trageți câmpurile între diferite zone:

Pentru a face din titlul rândului titlul coloanei, selectați acest nume și faceți clic pe meniul pop-up. Faceți clic pe „Mutați la numele coloanelor”. În acest fel am mutat data în coloane.

Am plasat câmpul „Departament” în fața numelor produselor. Folosind secțiunea „mutare la început” a meniului.

Vom arăta detalii pentru un anumit produs. Folosind exemplul celui de-al doilea tabel pivot, care afișează soldurile depozitului. Selectați celula. Faceți clic dreapta – „extinde”.

În meniul care se deschide, selectați câmpul cu datele care trebuie afișate.

Când facem clic pe tabelul pivot, devine disponibilă o filă cu parametrii raportului. Cu ajutorul acestuia, puteți modifica anteturile, sursele de date și informațiile de grup.

Verificarea corectitudinii facturilor de utilitati emise

Folosind tabele pivot Excel, este ușor să verificați cât de corect calculează chiria organizațiilor de servicii. Un alt punct pozitiv este economiile. Dacă monitorizăm lunar câtă energie electrică și gaz se consumă, vom putea găsi o rezervă pentru economisirea banilor la plata unui apartament.

Pentru început, vă sugerăm să elaborați un tabel rezumativ al tarifelor pentru toate facturile de utilități. Datele vor fi diferite pentru diferite orașe.

De exemplu, am făcut un tabel rezumativ al tarifelor pentru Moscova:

În scop educativ, să luăm o familie de 4 persoane care locuiește într-un apartament de 60 de metri pătrați. m. Pentru a controla facturile de utilități, trebuie să creați tabele pentru calcule pentru fiecare lună.

Prima coloană = prima coloană din tabelul pivot. A doua este o formulă pentru calcularea formei:

Tarif * număr de persoane / citiri contoare / zonă

Formulele noastre se referă la foaia în care se află tabelul rezumativ cu tarife.

Dacă beneficiile sunt utilizate la calcularea facturilor de utilități, acestea pot fi incluse și în formule. Solicitați informații despre angajamente de la departamentul de contabilitate al organizației dvs. de servicii. Când tarifele se modifică, schimbați pur și simplu datele din celule.