Exemplu de tabel pivot în excel. De ce versiuni noi?

Bună dragă cititor!

Astăzi vreau să abordez un astfel de important și functie globala MS Excel este. Un tabel pivot în Excel este specializat pentru a pune în ordine cantitatea uriașă de date care se află în tabel și le face în forma și volumul de care aveți nevoie. Și dacă lucrezi cu cantități mari de date și nu folosești un tabel pivot, este ca și cum ai trata calculatorul ca pe un ciocan. Pur și simplu nu puteți procesa din punct de vedere fizic mii, zeci sau chiar sute de mii de rânduri în care stocați o cantitate imensă de material într-un timp scurt, dar capacitatea unui tabel pivot vă va permite să faceți acest lucru, și nu într-unul singur. formă, dar în forma de care aveți nevoie. Cu toate acestea, astfel de manipulări nu apar nici un bit pe datele inițiale.

Un tabel pivot poate schimba datele în mod dinamic, ceea ce înseamnă că atunci când faceți ajustări în baza de date (tabelul de date sursă), acestea vă schimbă automat și tabelul pivot.

Apare o întrebare firească, în care utilizarea unui tabel pivot va da cel mai mare efect:

În primul rând atunci când baza de date este analizată după diverse criterii (oraș, nomenclatură, personal, perioada anului etc.)
în al doilea rând când lucrezi doar cu o sumă mare sau informatii analitice Ei nu vă pot ajuta deloc;
în al treilea rând, acesta este momentul în care cele 2 opțiuni anterioare trebuie recalculate în mod constant, actualizându-vă baza de date.

Deși poate nu am atins alte cazuri de utilizare, consider că acestea sunt principalele, iar restul sunt derivate ale acestora.

Singurul dezavantaj mare din toate tabelele pivot este că nu poate fi aplicat dacă datele din el îndeplinesc condiții specifice, și anume:

Fiecare coloană fără excepție trebuie să aibă propriul antet;
Trebuie să completați toate rândurile și coloanele; nu trebuie să existe spații.
Pentru toate coloanele de date, trebuie să existe anumite formate de celule pentru datele care ar trebui să fie stocate în ele (de exemplu, câmpul „Dată” are nevoie de un format de dată calendaristică, iar câmpul „Cont” are nevoie de un format de text etc.)
Valorile din aceste celule trebuie să fie „single”, ceea ce înseamnă că nu sunt împărțite (de exemplu, „Acordul nr. 23 din 09/03/2016” trebuie să fie scris în 3 coloane diferite „Document”, „Număr” și „Data”, aceasta vă va permite să creați un sistem flexibil și convenabil). Acest lucru este posibil și folosind
Dacă păstrați un tabel de venituri și cheltuieli în care, în plus, este nevoie și de scădere, atunci introduceți datele în baza de date inițială care era deja inițial cu semnul „-” și apoi într-o formă restrânsă veți obține rezultatul de care aveți nevoie;
Designul mesei dumneavoastră pivot în sine trebuie să aibă un aspect optim.

Dacă ați îndeplinit deja toate condițiile, veți primi un set de instrumente minunat pentru a lucra cu informațiile dvs. și nu numai.

Cum se creează un tabel pivot în Excel

În primul rând, verificăm dacă tabelul nostru este format corect: toate coloanele au numele corect, au formatul de celulă adecvat, unele sunt text, altele sunt numerice etc... Asigurați-vă că verificați dacă doriți să obțineți un rezultat excelent. Și dacă totul este bine, să continuăm:

Pe panoul de control, selectați fila „Inserare” și obțineți o alegere dintre 2 opțiuni pentru crearea tabelului pivot:

În primul rând, să luăm în considerare opțiunea pentru începători, acesta este momentul în care ți-o oferă ea însăși opțiuni gata făcute, și alegi totul singur, foarte simplu și opțiune accesibilă pentru a crea un tabel pivot în Excel:

Pentru a începe, trebuie doar să îndreptăm cursorul spre tabel și, în meniul „Inserare”, faceți clic pe pictograma „Tabele pivot recomandate” și Excel inteligent va veni cu și va crea un tabel pivot pentru dvs. După cum puteți vedea, există opțiuni din care să alegeți în captură de ecran, tot ce trebuie să faceți este să alegeți una dintre opțiunile propuse care vi se potrivește cel mai bine.

Nimic complicat această opțiune nu, încercați să experimentați și vă veți îmbunătăți abilitățile practice în crearea unui tabel pivot.

2. Tabel pivot (vrăjitor tabel pivot)

Dar din această secțiune a articolului încep cele mai interesante lucruri. Și să începem să lucrăm selectând meniul „Inserare”, blocul „Tabele” și pictograma „Tabel pivotant”. Totodată, nu uitați să indicați cu cursorul baza de date sursă sau tabelul din care vom face un rezumat.

În fereastra care se deschide, selectăm mai multe condiții pentru crearea unui tabel pivot - acesta este intervalul de date sursă necesare și unde ar trebui să punem tabelul pivot, unul lângă altul pe o foaie, sau să creăm o foaie nouă și să o plasăm pe el. Și, deoarece cursorul era deja pe tabel, Excel a determinat rapid și automat structura tabelului și a inserat-o în coloana intervalului. Faceți clic pe „OK” și obținem:

După cum puteți vedea, a fost creat un nou „Sheet3” și a fost numit „Pivot Table Creation Wizard” sau, pentru prieteni, pur și simplu „Pivot Table Designer”, pe care îl vedeți de fapt în următoarea captură de ecran. În constructor, indicați ce coloane din tabelul sursă trebuie să transferați în tabelul rezumat și ce calcule vor trebui efectuate pe ele, iar toate acestea se întâmplă prin simpla glisare a antetului tabelului de care avem nevoie în zona necesară.

Așa că am obținut primul rezultat, dar nu ne convine pentru că nu rezumăm numărul de fructe care au fost vândute, ceea ce înseamnă că trebuie să tragem antetul coloanei „Greutate, kg” din zona „RINDURI” și creați acea structură rezumativă a tabelului pe care îl dorim.

Ei bine, forma este aceeași, desigur, dar rezultatul nu este același, și anume câmpul „Greutate, kg” colectează în funcție de criteriu - numărul de valori, dar avem nevoie de , ceea ce înseamnă că mutăm cursorul mouse-ului la zona de valori „VALOARE” și în câmpul „Cantitate” specificat în câmpul Greutate, kg”, apăsați butonul stâng al mouse-ului pentru a afișa meniul contextual. Trebuie să selectăm ultimul element „Opțiune pentru câmpuri de valoare”.

În meniul care apare, schimbăm „Nume personalizat” într-un formular normal sau cel de care avem nevoie, selectăm operația dorită după câmp, avem nevoie de „Suma”, nu de „Cantitate”. Dacă este necesar, puteți modifica formatul celulelor câmpului, dar în acest moment Nu mai avem nevoie de asta.

Ei bine, am primit plăcuța necesară cu forma de care avem nevoie, valori și, bine, format. Totul din el este făcut așa cum avem nevoie și pare să fie ca totul și îl putem termina. Dar încă merită puțin mai multă muncă, de exemplu, eliminarea câmpurilor inutile „(vide)”, deoarece acestea nu ne sunt de nici un folos și strică interiorul rezumatului creat. tabele Excel. Așa că haideți să lucrăm în continuare, învățând să facem semnul mai frumos. Și pentru a face acest lucru, apelați meniul încorporat din antetul tabelului și debifați caseta de selectare din lista de informații afișate, asta este, câmpul a dispărut.

Vedeți, știința modului în care sunt create tabelele pivot nu este atât de complicată, dar fiecare utilizator Excel care se respectă trebuie să cunoască și să înțeleagă această problemă. De asemenea, folosind tabele pivot în Excel, vă puteți crea propriile valori.

Ei bine, am făcut un tabel rezumativ cu o selecție de fructe. Dar ce ar trebui să facem dacă avem nevoie și suntem interesați să știm cum are loc efectiv mișcarea între țări? Da, și oricine va fi interesat din unghiuri diferite și, din moment ce am format deja tabelul și am făcut totul pentru o muncă perfectă. Copiem pur și simplu tabelul nostru și în câmpul zonei necesare „RINDURI” schimbăm valorile calculate. Indicăm „Țara” ca prima valoare calculată, atâta tot, dintr-un tabel de date inițial am primit 2 tabele rezumative ale datelor de care avem nevoie.

De asemenea, merită să vorbim despre faptul că atunci când manipulăm tabelele pivot, Excel creează în plus un nou meniu în panoul de control pentru lucrul cu datele din tabel:

Și mai mult, dacă sunteți un utilizator foarte estetic și nu vă place cu adevărat exemplul standard de decorare a tabelelor pivot în Excel, puteți utiliza stiluri suplimentare de tabel pivot. În meniul „Constructor” există selecție mareși cred că o să-ți placă unul dintre ele. După cum puteți vedea, pur și simplu câștigând bani, mesele noastre au devenit mai frumoase și lucrul cu ele a devenit mai distractiv.

Dar nu uitați că datele din tabelul nostru nu pot fi actualizate independent, ceea ce înseamnă că atunci când există o modificare a datelor sursă (de exemplu, tocmai am eliminat datele din 2 rânduri), totalul nostru s-a modificat, ceea ce înseamnă că trebuie să raportăm acest lucru tabelului nostru pivot:

Pentru a face acest lucru, în meniul „Lucrul cu tabele pivot”, accesați fila „Analiză” și faceți clic pe butonul „Actualizare” și toate datele vor fi recalculate datorită modificărilor pe care le-am făcut tabelului nostru original:

Ei bine, în principiu, despre cum să lucrezi cu tabele pivot în Excel și atât, iar restul va veni din practică și

Procesarea unor volume mari de informații și crearea de rapoarte complexe pe mai multe niveluri este destul de dificilă fără utilizarea instrumentelor de automatizare. Excel 2010 este tocmai instrumentul care vă permite să simplificați aceste sarcini prin crearea de tabele pivot (încrucișate) de date (tabel pivot).

Tabelul pivot în Excel 2010 este utilizat pentru:

identificarea relațiilor într-un set mare de date;
gruparea datelor în funcție de diferite caracteristici și urmărirea tendințelor în grupuri;
găsirea de elemente repetate, detaliere etc.;
crearea de rapoarte ușor de citit este cel mai important lucru.

Există două moduri de a crea tabele pivot. Să ne uităm la fiecare dintre ele.

Metoda 1: Creați tabele pivot utilizând instrumentul standard Excel 2010 Pivot Table

Înainte de a crea un raport PivotTable, să decidem ce va fi folosit ca sursă de date. Să luăm în considerare opțiunea cu o sursă situată în același document.

1. Mai întâi, creați o masă simplă enumerarea elementelor pe care trebuie să le utilizați în raport. Linia de sus trebuie să conțină titluri de coloane.

2. Deschideți fila „Insert” și selectați instrumentul „Pivot Table” din secțiunea „Tables”.

Dacă trebuie să creați o diagramă pivot împreună cu tabelul pivot, faceți clic pe săgeata din colțul din dreapta jos al pictogramei „Tabel pivot” și selectați „Diagrama pivot”.

3. În caseta de dialog New PivotTable care se deschide, selectați tabelul de date nou creat sau intervalul acestuia. Pentru a face acest lucru, selectați zona dorita.

Puteți specifica o sursă externă ca date pentru analiză: selectați butonul radio în câmpul corespunzător și selectați conexiunea dorită din lista celor disponibile.

5. După confirmarea acțiunii făcând clic pe butonul „OK”, va fi creat și deschis un aspect al raportului. Să luăm în considerare.

În jumătatea dreaptă a ferestrei, este creat un panou cu instrumente principale de management - „Lista câmpurilor tabelului pivot”. Toate câmpurile (titlurile coloanelor din tabelul de date sursă) vor fi listate în zona „Selectați câmpurile de adăugat în raport”. Bifați casetele necesare și va fi creat un raport de tabel pivot cu câmpurile selectate.

Locația câmpurilor poate fi controlată - faceți-le nume de rânduri sau coloane, trăgându-le în ferestrele corespunzătoare și, de asemenea, sortați-le într-o ordine convenabilă. Puteți filtra articole individuale trăgând câmpul corespunzător în fereastra Filtru. Fereastra „Valoare” conține câmpul pentru care se fac calculele și se însumează rezultatele.

Alte opțiuni pentru editarea rapoartelor sunt disponibile din meniul Instrumente PivotTable din filele Opțiuni și Proiectare. Aproape fiecare dintre instrumentele din aceste file are o mulțime de setări și funcții suplimentare.

Metoda 2: Creați un Tabel Pivot folosind instrumentul Expert Tabel Pivot și Diagramă

Pentru a utiliza această metodă, va trebui să puneți la dispoziție un instrument care nu este afișat implicit pe panglică. Deschideți fila „Fișier” - „Opțiuni” - „Panou acces rapid" În lista Selectați comenzi din, selectați Comenzi de pe panglică. Și mai jos, din lista de comenzi, selectați „Pivot Table and Chart Wizard”. Faceți clic pe butonul „Adăugați”. Pictograma expert va apărea în partea de sus a panoului de acces rapid.

Expertul PivotTable din Excel 2010 nu este foarte diferit de instrumentul similar din Excel 2007. Pentru a crea tabele pivot folosindu-l, urmați acești pași.

1. Faceți clic pe pictograma expert din panoul Acces rapid. În caseta de dialog, selectați butonul radio pentru elementul de care aveți nevoie din lista de surse de date:

„într-o listă sau bază de date Microsoft Excel" - sursa va fi baza de date a foilor de lucru, dacă există;
„într-o sursă de date externă” - dacă există o conexiune la bază externă, care vor trebui selectate dintre cele disponibile;
„în mai multe intervale de consolidare” - dacă trebuie să combinați date din diferite surse;
„date într-un alt tabel pivot sau diagramă pivot” — un tabel sau diagramă pivot existent este luat ca sursă.

2. După aceasta, selectați tipul de raport care trebuie creat - „tabel pivot” sau „diagramă pivot (cu tabel)”.

Dacă sursa este selectată documentul curent, unde există deja un tabel simplu cu elemente ale viitorului raport, setați intervalul de acoperire - selectați zona dorită cu cursorul. Apoi, selectați unde să plasați tabelul - pe o foaie nouă sau pe foaia curentă - și faceți clic pe „Terminare”. Tabelul Pivot va fi creat.
Dacă trebuie să consolidați date din mai multe surse, selectați butonul radio din zona corespunzătoare și selectați tipul de raport. Și apoi va trebui să indicați cum să creați câmpurile de pagină ale viitorului tabel pivot: un câmp sau mai multe câmpuri.

Când selectați „Creați câmpuri de pagină”, în primul rând va trebui să specificați intervalele sursei de date: selectați primul interval, faceți clic pe „Adăugați”, apoi pe următorul etc.

Pentru comoditate, intervalelor li se pot da nume. Pentru a face acest lucru, selectați unul dintre ele din listă și specificați numărul de câmpuri de pagină de creat pentru acesta, apoi dați fiecărui câmp un nume (etichetă). După aceea, selectați următorul interval și așa mai departe.

Când selectați o sursă de date externă, utilizați aplicația Microsoft Interogare inclusă în Excel 2010 sau dacă doriți să vă conectați Date de birou, sunt utilizate opțiunile din fila Date.
Dacă documentul conține deja un raport PivotTable sau un Pivot Chart, le puteți utiliza ca sursă. Pentru a face acest lucru, trebuie doar să specificați locația lor și să selectați intervalul de date dorit, după care va fi creat un nou tabel pivot.

Să presupunem că trebuie să creați un raport pe baza datelor disponibile. Pentru aceasta se pot folosi caracteristici standard. Dar atunci când apare necesitatea de a completa tabelul sau de a-i schimba structura, efectuarea unor astfel de modificări poate necesita un efort considerabil.
Utilizarea tabelelor pivot rezolvă această problemă. Datorită acestora, puteți modifica cu ușurință structura, calculele și puteți filtra datele sursă.

Crearea unui tabel pivot

Înainte de a crea un tabel, asigurați-vă că datele sursă sunt prezentate corect: coloanele au titluri, numerele sunt prezentate în format numeric și nu în format text etc. În caz contrar, puteți obține rezultate incorecte.
Accesați fila „Inserare”, apoi în secțiunea „Tabele”, faceți clic pe pictograma „Tabel pivotant” sau selectați elementul corespunzător din lista derulantă.

În fereastra care apare, trebuie să selectați o sursă de date care reprezintă:

Gama (poate fi într-o altă carte);
Tabel de date (este indicat numele);
Date dintr-o sursă externă obținute prin interogare SQL dintr-o bază de date etc.

Apoi este indicată locația de plasare. Dacă selectați o foaie nouă, aplicația o va crea și va plasa tabelul începând din prima celulă. Dacă foaia necesară există deja, atunci puteți selecta independent adresa de la începutul acesteia.

Gestionarea unei liste de câmpuri de tabel

După ce faceți clic pe butonul „OK” din fereastra de creare a tabelului pivot, este creată o zonă goală pentru a o găzdui și este afișată o fereastră cu o listă a tuturor câmpurilor.

Numele câmpurilor sunt anteturile coloanelor de date sursă, așa că încercați să le denumiți cât mai clar și pe scurt posibil.
Sub listă sunt 4 zone. Aceștia sunt responsabili pentru acțiunile privind datele:

Titluri de rând – afișează valorile câmpurilor ca titluri de rând. În esență această zonă nu schimbă nimic, dacă îi atribuiți toate câmpurile disponibile, vom obține tabelul original;
Titluri de coloane – Valorile câmpurilor sunt afișate ca titluri de coloane. Acțiunea seamănă cu o transpunere normală;
Valori – Calculele sunt efectuate pe valorile coloanei plasate în această zonă. În acest caz, un câmp îi poate fi atribuit de mai multe ori;
Filtru de raport – aici sunt plasate câmpuri ale căror valori servesc la filtrarea rândurilor de date sursă pe care tabelul pivot ar trebui să le ignore.

Pentru a atribui o zonă unui anumit câmp, pur și simplu trageți câmpul cu mouse-ul.
Ca exemplu, vom folosi un tabel care arată dinamica cursului de schimb al dolarului în raport cu rubla timp de 9 luni:

Lună	Data	Cant	Bine	Schimba
ianuarie	10.01.2013	1	30,4215	0,0488
ianuarie	11.01.2013	1	30,3650	-0,0565
ianuarie	12.01.2013	1	30,2537	-0,1113
...	...	...	...	...
septembrie	28.09.2013	1	32,3451	0,1715

Este necesar să creați un tabel pivot în Excel care să calculeze cursul de schimb mediu pentru fiecare lună (un tabel gol a fost deja creat la începutul acestei secțiuni).

Trageți câmpul „Lună” în zona „Nume linii”. Vom atribui „Curs” zonei „Valori”, după care vom modifica parametrii câmpului astfel încât media aritmetică să fie calculată din acesta. Pentru a face acest lucru, faceți clic pe elementul dorit din zonă, în meniul care se deschide, faceți clic pe „Parametri câmp valoric...”. În fereastra care apare, există o filă „Operațiune”. În el, trebuie să selectați „Medie” din listă. Gata.

De asemenea, puteți modifica titlul în parametrii câmpului și aplicați calcule suplimentare în aceeași filă a meniului în cauză.

Câmpuri de tabel pivot calculate

Dacă operațiunile și calculele furnizate nu sunt suficiente, atunci Excel vă permite să vă creați propriul câmp calculat în tabelul pivot. Pentru a face acest lucru, selectați o celulă din zona tabelului, accesați fila „Opțiuni” („Analiză” pentru Excel 2013) a panglicii care apare. Apoi, în secțiunea „Instrumente”, faceți clic pe pictograma „Formule”, din meniul drop-down (în versiunea 2010 și mai sus calea este diferită: Secțiunea „Calcule” -> Listă drop-down „Câmpuri, elemente și seturi” ”) selectați „Câmp calculat...”. Ar trebui să apară o fereastră:

Dați-i un nume prietenos și scrieți formula folosind orice funcție (rețineți că câmpurile calculate nu funcționează cu text). De exemplu, să înmulțim cursul de schimb cu 1000 și să scădem 13 procente (=Rata*1000*0,87). Să numim câmpul „ZP”, să-l adăugăm în zona de valori și să aplicăm maximul ca operație. Consultați noua vizualizare a raportului:

Este posibil să observați că rezultatul pentru noul câmp returnează un rezultat „ciudat”. Acest lucru se întâmplă deoarece valoarea coloanei „rata” este mai întâi însumată și abia apoi rezultatul rezultat este utilizat pentru calcule. Dacă adăugați o dată în zona titlului rândului, calculul va returna rezultatul necesar:

Opțiuni de tabel pivot în Excel

Pentru a studia în continuare subiectul, vom construi un tabel mai complex (principiul construcției nu diferă de cel discutat mai devreme).
Datele sursă reprezintă o listă de 100 de rânduri, unde se reflectă fiecare intrare salariile angajați din diverse industrii din anumite regiuni:

Din exemplu, puteți vedea că tabelul pivot prezintă o structură arborescentă dacă este utilizat mai mult de 1 câmp. Rădăcina reprezintă valorile coloanei care se află pe primul loc în lista zonei „Nume rânduri”. Toate câmpurile ulterioare sunt imbricate în el și unele în altele, în conformitate cu ordinea lor în listă, care poate fi modificată prin simpla glisare a mouse-ului. Fiecare ramură individuală a unui astfel de copac poate fi pliată și deschisă. Această proprietate se aplică și zonei titlului coloanei.
În mod implicit, Excel setează un aspect comprimat pentru tabelele pivot. Acesta poate fi modificat prin parametri (clic dreapta pe zona tabelului -> opțiuni tabel pivot -> Ieșire -> Aspect clasic) sau prin designer:

Utilizarea unui aspect tabelar vă permite să plasați fiecare câmp într-o coloană separată și să afișați în plus subtotalurile pentru acesta.
Dacă nu este necesar un rezumat suplimentar, acesta ar trebui eliminat pentru a face tabelul mai ușor de citit. Doar faceți clic dreapta pe el și debifați elementul corespunzător din listă. Pentru a scăpa de toate totalurile, cu excepția celor principale, în fila designer din secțiunea aspect, selectați „Subtotaluri” -> „Nu afișați subtotaluri”.

Deoarece PivotTable este o structură arborescentă, titlul rândului este afișat o singură dată. În Microsoft Excel, începând cu versiunea 2010, puteți aplica opțional repetarea etichetei elementului la aspectul dvs.

Acum tabelul pivot finalizat arată astfel: Foaie Excel:

Pe lângă proprietățile discutate mai sus, puteți seta următoarele prin parametrii tabelului:

Numele tabelului pivot;
Fuzionarea și alinierea semnăturilor;
Afișarea valorilor pentru celulele goale;
Modificați automat lățimea coloanelor;

Tabelele pivot sunt considerate unul dintre cele mai puternice instrumente Excel pentru lucrul cu date. Ele există pentru a simplifica un tabel complex și greoi și pentru a face rezultatele calculelor simple, ușor de înțeles și accesibile. Dar! Nu orice tabel poate fi folosit pentru a crea un tabel rezumativ.

Tabelul trebuie să fie sub forma unei liste obișnuite, adică titlurile de coloană pot fi doar pe primul rând. Am dat un exemplu de astfel de tabel în Fig. P1.1.

Dacă aveți titluri sau subtotaluri intermediare în tabel, atunci acestea trebuie eliminate. Pentru a nu explica în cuvinte toate beneficiile unui tabel pivot, îl voi arăta cu un exemplu. Faceți clic pe butonul Pivot Table din grupul Tables din meniul Insert (Fig. A1.2).

Se va deschide o fereastră. În acesta, trebuie mai întâi să selectați tabelul sau intervalul de tabel pentru care creați tabelul pivot și să specificați unde să îl plasați. Îl poți așeza pe o foaie nouă, sau îl poți așeza lângă masa originală, pe aceeași foaie, în funcție de care îți este mai convenabil (Fig. A1.3).

Am introdus un tabel pivot pe aceeași foaie. Un panou de designer pentru crearea unui tabel pivot a apărut în partea dreaptă a ferestrei și în partea de sus grup nou tabs - Lucrul cu tabele pivot.

Cum se face un tabel pivot?

În partea dreaptă a panoului, numită PivotTable Field List, vedeți o listă de titluri de coloane din tabelul prezentat în figură. P1.1. Din aceste câmpuri puteți acum, ca de la un constructor, să compuneți masa noua. Pentru a face acest lucru, trebuie să trageți numele câmpului în zona necesară cu mouse-ul. Am decis că voi avea numele lunilor în coloanele tabelului pivot și numele de familie în rânduri și voi completa câmpul Valori cu valorile din coloana Primite. Adică, tabelul rezumativ va include doar date despre banii primiți. Rezultatul este prezentat în Fig. P1.4.

Un PivotTable se numește astfel deoarece aduce toate rezultatele într-un tabel simplu, însumând rezultatele generale. În acest caz, tabelul rezumă datele (în coloana Valori scrie Sumă în câmpul Primite). Apropo, deschideți lista butonului Calcule din fila Parametri (Fig. A1.5).

Am stabilit rezultatele în funcție de valoarea medie și, după cum puteți vedea în Fig. P1.5, acum tabelul pivot nu calculează suma pe lună și nume, ci valoarea medie: salariul mediu pe lună și media pe angajat. În plus, puteți crea o diagramă rezumat pe baza valorilor din tabelul pivot (Fig. A1.6).

Acest lucru afișează un grup de file numit Lucrul cu diagrame pivot, care nu este nimic nou pentru dvs. Ne-am uitat la toate acestea când ne-am uitat la modul în care funcționează diagramele obișnuite. Apropo, vă rugăm să rețineți: am schimbat rândurile și coloanele, astfel încât totalurile sunt acum calculate în funcție de valoarea coloanei Remaining (vezi Fig. A1.6). Am făcut asta doar ca să știți că valorile coloanei, rândului și câmpurilor de valoare pot fi amestecate pentru a se potrivi nevoilor dvs.

De asemenea, puteți introduce o felie într-un tabel pivot. Acesta este un filtru suplimentar care vă permite să faceți rezultatul și mai clar (Fig. A1.7).

În grupul Sort and Filter din fila Opțiuni, faceți clic pe butonul Insert Slicer și selectați parametrul după care doriți să filtrați datele. Am indicat luna. Acum puteți selecta o anumită lună în fereastra slice, iar tabelul pivot va afișa numai datele legate de această lună (vezi Fig. A1.6). De asemenea, veți avea la dispoziție o întreagă filă - Cutting Tools. Apropo, puteți introduce nu doar o felie în masă, ci mai multe.

Ti-am spus cel mai mult tehnici simple lucrul cu tabele pivot. Dacă înțelegi asta, poți înțelege orice altceva. Nu uitați că înainte de a crea un tabel pivot, tabelul sursă trebuie pregătit pentru aceasta, adică asigurați-vă că nu conține titluri și totaluri intermediare. Ei bine, dacă ceva încă nu este clar sau doriți să studiați capacitățile tabelelor pivot mai detaliat, atunci vă recomand să apelați la materialele site-ului special Pivot Tables Excel 2010, care este dedicat în întregime metodelor de lucru cu date în Pivot. Tabelele Excel 2010.

29.10.2012

Tabelele pivot este unul dintre cele mai puternice instrumente Excel. Acestea vă permit să analizați și să rezumați diferite rezultate pentru volume mari de date cu doar câteva clicuri. În acest articol ne vom familiariza cu tabelele pivot, vom înțelege ce sunt acestea, vom afla cum să le creăm și să le configuram.

Excel 2010 a fost folosit pentru a scrie acest articol Conceptul de tabele pivot a rămas în mare parte neschimbat de mulți ani, dar modul în care sunt create este ușor diferit în fiecare. noua versiune Excela. Dacă ai Versiunea Excel nu 2010, atunci fiți pregătit că capturile de ecran din acest articol vor diferi de ceea ce vedeți pe ecran.

Puțină istorie

În zorii dezvoltării programelor pentru crearea foilor de calcul, regulile Lotus erau 1-2-3. Superioritatea sa a fost atât de completă încât eforturile Microsoft de a-și dezvolta propriile sale software(Excel) ca alternative la Lotus părea o pierdere de timp. Acum înainte rapid până în 2010! Excel domină industria foilor de calcul mai mult decât a făcut Lotus vreodată în istoria sa, iar numărul de persoane care încă folosesc Lotus se apropie de zero. Cum s-ar putea întâmpla asta? Care a fost motivul unei astfel de întorsături dramatice a evenimentelor?

Analiștii identifică doi factori principali:

În primul rând, Lotus a decis că această nouă platformă GUI numită Windows era doar un moft trecător care nu va dura mult. Ei au refuzat să creeze o versiune Windows a Lotus 1-2-3 (dar doar pentru câțiva ani), prezicând că versiunea DOS a software-ului lor este tot ceea ce consumatorii ar avea nevoie vreodată. Microsoft, desigur, a dezvoltat Excel special pentru Windows.
În al doilea rând, Microsoft a dezvoltat un instrument în Excel numit tabele pivot, care nu era disponibil în Lotus 1-2-3. Tabelele pivot, o caracteristică exclusivă a Excel, au fost atât de incredibil de utile încât oamenii erau înclinați să învețe noile pachet software Excel, în loc să continue să lucreze în Lotus 1-2-3, care nu le avea.

Tabelele pivot, împreună cu subestimarea succesului Windows în general, au jucat marșul morții pentru Lotus 1-2-3 și au marcat începutul succesului Microsoft Excel.

Ce sunt tabelele pivot?

Deci, care este cel mai bun mod de a descrie ce sunt tabelele pivot?

Vorbitor într-un limbaj simplu Tabelele pivot sunt rezumate ale unor date, create pentru a facilita analiza acestor date. Spre deosebire de totalurile create manual, tabelele pivot Excel sunt interactive. Odată create, le puteți schimba cu ușurință dacă nu vă oferă imaginea pe care sperați să o obțineți. Cu doar câteva clicuri, totalurile pot fi inversate, astfel încât titlurile coloanelor să devină titluri de rând și invers. Există multe lucruri pe care le puteți face cu tabelele pivot. diverse actiuni. În loc să încerci să descrii în cuvinte toate capacitățile tabelelor pivot, este mai ușor să le demonstrezi în practică...

Datele pe care le analizați cu PivotTables nu pot fi orice fel de date. Acestea ar trebui să fie date brute, ca o listă de un fel. De exemplu, aceasta ar putea fi o listă a vânzărilor finalizate în companie în ultimele șase luni.

Priviți datele prezentate în figura de mai jos:

Vă rugăm să rețineți că acestea nu sunt date brute, deoarece au fost deja rezumate. În celula B3 vedem 30.000 de dolari, care este probabil totalul pe care l-a făcut James Cook în ianuarie. Atunci unde sunt datele sursă? De unde a venit cifra de 30.000 de dolari? Unde este lista originală de vânzări din care a fost derivat acest total lunar? Este clar că cineva a făcut tot posibilul să organizeze și să sorteze toate datele de vânzări din ultimele șase luni și să le transforme în tabelul rezumativ pe care îl vedem. Cât timp crezi că a durat? Oră? la zece?

Cert este că tabelul de mai sus nu este un tabel rezumativ. A fost creat manual din date brute stocate în altă parte și a durat cel puțin câteva ore pentru a fi procesat. Acesta este exact tipul de tabel rezumat care poate fi creat folosind tabele pivot, petrecând doar câteva secunde pe el. Să ne dăm seama cum...

Dacă ne întoarcem la lista originală de vânzări, ar arăta cam așa:

S-ar putea să fii surprins că din această listă de tranzacții de tranzacționare, folosind tabele pivot și în doar câteva secunde, putem crea un raport lunar de vânzări în Excel, despre care am discutat mai sus. Da, putem face asta și multe altele!

Cum se creează un tabel pivot?

În primul rând, asigurați-vă că aveți niște date sursă pe o foaie Excel. Lista tranzacțiilor financiare este cel mai tipic lucru care se găsește. De fapt, ar putea fi o listă cu orice: informații de contact ale angajaților, o colecție de CD-uri sau date despre consumul de combustibil al companiei tale.

Deci, hai să lansăm Excel... și să încărcăm această listă...

Odată ce am deschis această listă în Excel, putem începe să creăm un tabel pivot.

Selectați orice celulă din această listă:

Apoi pe filă Introduce(Inserați) comanda de selectare Tabel Pivot(Tabel rezumat):

Va apărea o casetă de dialog Creați un tabel pivot(Crearea unui tabel pivot) cu două întrebări pentru tine:

Ce date ar trebui să folosesc pentru a crea un nou tabel pivot?
Unde ar trebui să plasez tabelul pivot?

Deoarece la pasul anterior am selectat deja una dintre celulele listei, întreaga listă va fi selectată automat pentru a crea un tabel pivot. Rețineți că putem selecta un interval diferit, un tabel diferit și chiar o sursă de date externă, de exemplu, un tabel de bază de date Accesați datele sau MS-SQL. În plus, trebuie să alegem unde să plasăm noul tabel pivot: pe o foaie nouă sau pe una dintre cele existente. ÎN în acest exemplu vom alege varianta - Foaie de lucru nouă(Pe o foaie nouă):

Excel va crea o nouă foaie de lucru și va plasa un tabel pivot gol pe ea:

De îndată ce facem clic pe orice celulă din tabelul pivot, va apărea o altă casetă de dialog: Lista de câmpuri din tabel pivot(Câmpurile tabelului pivot).

Lista de câmpuri din partea de sus a casetei de dialog este o listă cu toate titlurile din lista originală. Cele patru zone goale din partea de jos a ecranului vă permit să spuneți Tabelului Pivot cum doriți să rezumați datele. În timp ce aceste zone sunt goale, nici în tabel nu există nimic. Tot ce trebuie să facem este să trageți titlurile din zona de sus în zonele goale de mai jos. În acest caz, un tabel pivot este generat automat în conformitate cu instrucțiunile noastre. Dacă am făcut o greșeală, putem elimina titlurile din zona de jos sau trageți altele pentru a le înlocui.

Regiune Valori(Semnificațiile) este probabil cea mai importantă dintre cele patru. Ce titlu este plasat în această zonă determină ce date vor fi rezumate (sumă, medie, maximă, minimă etc.) Acestea sunt aproape întotdeauna valori numerice. Un candidat excelent pentru o poziție în acest domeniu - date de la rubrica Cantitate(Cost) tabelului nostru original. Să tragem acest titlu în zonă Valori(Valori):

Vă rugăm să rețineți că titlul Cantitate este marcat acum cu o bifă, iar în zonă Valori A apărut intrarea (Valori). Suma Sumei(Câmpul Sumă după sumă), indicând că coloana Cantitate rezumat

Dacă ne uităm la tabelul pivot în sine, vom vedea suma tuturor valorilor din coloană Cantitate masa originala.

Deci, primul nostru tabel pivot este creat! Convenabil, dar nu deosebit de impresionant. Probabil dorim mai multe informații despre datele noastre decât avem în prezent.

Să ne uităm la datele sursă și să încercăm să identificăm una sau mai multe coloane care pot fi folosite pentru a defalca această sumă. De exemplu, putem crea tabelul nostru pivot în așa fel încât suma totală a vânzărilor să fie calculată separat pentru fiecare vânzător. Aceste. În tabelul nostru pivot vor fi adăugate rânduri cu numele fiecărui agent de vânzări al companiei și valoarea totală a vânzărilor acestuia. Pentru a obține acest rezultat, trebuie doar să trageți titlul Agent de vânzări Etichete de rând(linii):

Devine din ce în ce mai interesant! Tabelul nostru pivot începe să prindă contur...

Vedeți beneficiile? În câteva clicuri, am creat un tabel care ar fi durat foarte mult timp pentru a fi creat manual.

Ce altceva putem face? Ei bine, într-un fel, tabelul nostru pivot este deja gata. Am creat un rezumat util al datelor brute. Deja primit informatii importante! În restul acestui articol, vom analiza câteva modalități de a crea tabele pivot mai complexe și vom învăța cum să le personalizăm.

Configurarea unui tabel pivot

În primul rând, putem crea un tabel pivot bidimensional. Să facem asta folosind antetul coloanei Modalitate de plată(Modalitate de plată). Doar trageți titlul Modalitate de plată spre regiune Etichete de coloane(Coloane):

Obtinem rezultatul:

Arata foarte tare!

Acum să facem un tabel tridimensional. Cum ar putea arăta o astfel de masă? Să vedem...

Trageți titlul Pachet(Complex) în zonă Filtru de raportare(Filtre):

Observați unde a ajuns...

Acest lucru ne oferă posibilitatea de a filtra raportul după atributul „Pentru ce complex de vacanță a fost plătit”. De exemplu, putem vedea o defalcare în funcție de vânzători și metode de plată pentru toate complexele, sau în câteva clicuri putem schimba vizualizarea tabelului rezumativ și arătăm aceeași defalcare numai pentru cei care au comandat complexul Căutători de soare.

Deci, dacă înțelegeți corect acest lucru, atunci tabelul nostru pivot poate fi numit tridimensional. Să continuăm configurarea...

Dacă se dovedește brusc că tabelul pivot ar trebui să afișeze doar plata prin cec și Card de credit(adică plată fără numerar), atunci putem dezactiva ieșirea antetului Numerar(Numerar). Pentru a face asta lângă Etichete de coloane faceți clic pe săgeata în jos și debifați elementul din meniul drop-down Numerar:

Să vedem cum arată tabelul nostru pivot acum. După cum puteți vedea, coloana Numerar a dispărut din ea.

Formatarea tabelelor pivot în Excel

Evident, PivotTables sunt un instrument foarte puternic, dar până acum rezultatele par puțin simple și plictisitoare. De exemplu, numerele pe care le adunăm nu arată ca sume în dolari, ci sunt doar numere. Să reparăm asta.

Este tentant să faci acțiunile obișnuite într-o astfel de situație și pur și simplu să selectezi întregul tabel (sau întreaga foaie) și să folosești butoanele standard de formatare a numerelor de pe bara de instrumente pentru a seta formatul dorit. Problema cu această abordare este că, dacă modificați vreodată structura tabelului pivot în viitor (și există o șansă de 99% ca acest lucru să se întâmple), formatarea se va pierde. Avem nevoie de o modalitate de a o face (aproape) permanentă.

Mai întâi, să găsim intrarea Suma Sumeiîn zonă Valori(Valori) și faceți clic pe el. În meniul care apare, selectați elementul Setări câmp valoric(Opțiuni de câmp de valoare):

Va apărea o casetă de dialog Setări câmp valoric(Parametrii câmpului de valori).

Faceți clic pe butonul Format de număr(Format număr), apare o casetă de dialog Formatați celule(Format celulă):

Din listă Categorie(Formate numerice) selectați Contabilitate(financiar) și numărul de zecimale setat egal cu zero. Acum apăsați de mai multe ori Bine pentru a reveni la tabelul nostru pivot.

După cum puteți vedea, numerele au ajuns să fie formatate ca sume în dolari.

Acum că am terminat cu formatarea, să setăm formatul pentru întregul tabel pivot. Există mai multe moduri de a face acest lucru. Să-l folosim pe cel mai simplu...

Deschide fila Instrumente PivotTable: Design(Lucrul cu tabele pivot: Designer):

Selectați orice stil adecvat și priviți rezultatul în tabelul pivot:

Alte setări pentru tabelele pivot în Excel

Uneori trebuie să filtrați datele după date. De exemplu, lista noastră de tranzacții comerciale conține multe, multe date. Excel oferă un instrument de grupare a datelor după zi, lună, an etc. Să vedem cum se face.

În primul rând, eliminați intrarea. Modalitate de plată din regiune Etichete de coloane(Coloane). Pentru a face acest lucru, trageți-l înapoi în lista de titluri și, în locul său, mutați titlul Data rezervată(Data rezervarii):

După cum puteți vedea, acest lucru a făcut temporar inutil tabelul nostru pivot. Excel a creat o coloană separată pentru fiecare dată la care a fost efectuată tranzacția. Drept urmare, am obținut o masă foarte largă!

Pentru a remedia acest lucru, faceți clic dreapta pe orice dată și selectați din meniul contextual paragraf Grup(Grup):

Apare caseta de dialog de grupare. Noi alegem Luni(Luni) și apăsați Bine:

Voila! Acest tabel este mult mai util:

Apropo, acest tabel este aproape identic cu cel afișat la începutul articolului, unde rezultatele vânzărilor au fost compilate manual.

Mai este un punct foarte important pe care trebuie să-l știi! Puteți crea nu unul, ci mai multe niveluri de titluri de rând (sau coloană):

...si va arata asa...

Același lucru se poate face cu anteturile de coloană (sau chiar filtrele).

Să revenim la vizualizarea tabelului inițial și să vedem cum să afișam valorile medii în loc de sume.

Pentru a începe, faceți clic pe Suma Sumei iar din meniul care apare, selectați Setări câmp valoric(Opțiuni de câmp de valoare):

Pe listă Rezumați câmpul de valoare cu(Operațiune) în caseta de dialog Setări câmp valoric(Opțiuni câmp de valoare) selectați Medie(Medie):

În același timp, cât suntem aici, să ne schimbăm Nume personalizat(Nume personalizat) cu Media sumei(câmpul Cantitate după cantitate) la ceva mai scurt. Introduceți așa ceva în acest câmp Mediu:

Clic Bineși uite ce s-a întâmplat. Observați că toate valorile s-au schimbat de la totaluri la medii, iar titlul tabelului (în celula din stânga sus) s-a schimbat în Mediu:

Dacă doriți, puteți obține imediat suma, media și cantitatea (vânzările) plasate într-un tabel pivot.

Aici instrucțiuni pas cu pas cum să faci asta începând cu un tabel pivot gol:

Trageți titlul Agent de vânzări(Reprezentant de vânzări) în regiune Etichete de coloane(Coloane).
Trageți titlul de trei ori Cantitate(Cost) la zonă Valori(Valori).
Pentru primul câmp Cantitate schimba numele in Total(Sumă), iar formatul numărului din acest câmp este Contabilitate(Financiar). Numărul de zecimale este zero.
Al doilea câmp Cantitate nume medie e, setați operația pentru aceasta Medie(Medie) și modificați formatul numărului din acest câmp în Contabilitate(financiar) cu numărul de zecimale egal cu zero.
Pentru al treilea câmp Cantitate setează titlul Contași o operație pentru el - Conta(Cantitate)
În zonă Etichete de coloane Câmp (Coloane) creat automat ΣValori(Σ Values) – trageți-l în zonă Etichete de rând(șiruri)

Iată ce obținem în final:

Total, medie și numărul de vânzări - toate într-un singur tabel pivot!

Concluzie

Tabelele pivot din Microsoft Excel conțin multe, multe funcții și setări. Un articol atât de scurt nici măcar nu se poate apropia de a le acoperi pe toate. Pentru a descrie pe deplin toate capabilitățile tabelelor pivot ar fi nevoie de o carte mică sau de un site web mare. Cititorii curajoși și curioși pot continua să exploreze tabelele pivot. Pentru a face acest lucru, faceți clic dreapta pe aproape orice element al tabelului pivot și vedeți ce funcții și setări se deschid. Pe Panglică veți găsi două file: Instrumente PivotTable: Opțiuni(Analiză) și Proiecta(Constructor). Nu vă fie teamă să faceți o greșeală, puteți oricând să ștergeți tabelul pivot și să o luați de la capăt. Aveți o oportunitate pe care utilizatorii de multă vreme DOS și Lotus 1-2-3 nu au avut-o niciodată.