Cum să creați un plan de tabel pivot. Modificarea ordinii de sortare

Utilizatorii creează tabele pivot pentru a analiza, rezuma și prezenta cantități mari de date. Un astfel de instrument Excel vă permite să filtrați și să grupați informații, să le afișați în diferite secțiuni (pregătiți un raport).

Materialul sursă este un tabel cu câteva zeci și sute de rânduri, mai multe tabele într-o singură carte, mai multe fișiere. Amintiți-vă ordinea creării: „Inserare” - „Tabele” - „Tabel pivot”.

Și în acest articol vom analiza cum să lucrați cu tabele pivot în Excel.

Cum să faci un tabel pivot din mai multe fișiere

Primul pas este să încărcați informațiile în programul Excelși aliniați-l cu foile de calcul Excel. Dacă datele noastre sunt în Worde, le transferăm în Excel și facem un tabel conform tuturor regulilor Excel (dam titluri coloanelor, eliminăm liniile goale etc.).

Lucrările suplimentare privind crearea unui tabel pivot din mai multe fișiere vor depinde de tipul de date. Dacă informațiile sunt de același tip (există mai multe tabele, dar titlurile sunt aceleași), atunci expertul PivotTable este de ajutor.

Pur și simplu creăm un raport rezumat bazat pe date din mai multe intervale de consolidare.

Mult mai greu de făcut masă rotativă bazate pe tabele sursă de structură diferită. De exemplu, acestea:

Prima masă este sosirea mărfurilor. Al doilea este numărul de unități vândute în diferite magazine. Trebuie să combinăm aceste două tabele într-un singur raport pentru a ilustra soldurile, vânzările în magazin, veniturile și așa mai departe.

Expertul PivotTable va genera o eroare cu astfel de parametri inițiali. Deoarece una dintre condițiile principale pentru consolidare este încălcată - aceleași nume de coloane.

Dar cele două titluri din aceste tabele sunt identice. Prin urmare, putem combina datele și apoi crea un raport rezumat.

Se deschide o schiță de raport de rezumat cu o listă de câmpuri care pot fi afișate.

Să arătăm, de exemplu, cantitatea de mărfuri vândută.

Puteți afișa diferiți parametri pentru analiză, mutați câmpuri. Dar munca cu tabele pivot în Excel nu se termină aici: posibilitățile instrumentului sunt diverse.



Informații detaliate în tabelele pivot

Din raport (vezi mai sus) vedem că s-au vândut DOAR 30 de plăci video. Pentru a afla ce date au fost folosite pentru a obține această valoare, faceți dublu clic pe numărul „30”. Primim un raport detaliat:

Cum se reîmprospătează datele într-un tabel pivot Excel?

Dacă modificăm vreun parametru din tabelul original sau adăugăm un nou record, aceste informații nu vor fi afișate în raportul de sinteză. Această stare de lucruri nu ne convine.

Actualizarea datelor:

Cursorul ar trebui să fie în orice celulă a raportului rezumat.

Sau:

Butonul dreapta al mouse-ului - reîmprospătare.

A personaliza actualizare automata tabel pivot la modificarea datelor, procedăm conform instrucțiunilor:

Modificarea structurii unui raport

Adăugați câmpuri noi la tabelul pivot:

După modificarea intervalului, în rezumat a apărut câmpul „Vânzări”.

Cum se adaugă un câmp calculat la un tabel pivot?

Uneori, utilizatorul nu are suficiente date conținute în PivotTable. Nu are rost să schimbi informațiile originale. În astfel de situații, este mai bine să adăugați un câmp calculat (personalizat).

Aceasta este o coloană virtuală creată ca rezultat al calculelor. Poate afișa medii, procente, discrepanțe. Adică rezultatele diverse formule. Datele de câmp calculate interacționează cu datele din Tabel Pivot.

Instrucțiuni pentru adăugarea unui câmp personalizat:

Gruparea datelor într-un raport pivot

De exemplu, luați în considerare costul mărfurilor în diferiți ani. Câți bani au fost cheltuiți în 2012, 2013, 2014 și 2015. Gruparea după dată în tabelul pivot Excel se face după cum urmează. De exemplu, să facem un rezumat simplu după data livrării și sumă.

Făcând clic Click dreaptașoareci pentru orice întâlnire. Selectați comanda „Grup”.

În caseta de dialog care se deschide, setați parametrii de grupare. Data de început și de încheiere a intervalului sunt afișate automat. Selectați pasul - „Ani”.

Primim cantitatea de comenzi pe an.

În același mod, puteți grupa datele într-un tabel pivot după alți parametri.

Tabelele pivot sunt necesare pentru rezumarea, analizarea și prezentarea datelor care se află în tabele sursă „mari”, în diverse tăieturi. Luați în considerare procesul de creare a tabelelor pivot simple.

Tabele pivot (Inserare/ Tabele/ Tabel Pivot) poate fi utilă dacă sunt îndeplinite simultan următoarele condiții:

• exista un tabel initial cu multe randuri (inregistrari), vorbim de cateva zeci si sute de randuri;
• este necesar să se efectueze o analiză a datelor, care necesită eșantionarea datelor (filtrarea), gruparea acestora (însumarea, numărarea) și prezentarea datelor în diverse secțiuni (întocmirea rapoartelor);
• această analiză este dificil de realizat pe baza tabelului original folosind alte instrumente: ( CTRL+SHIFT+L), ;
• tabelul sursă îndeplinește anumite cerințe (vezi mai jos).

Utilizatorii evită adesea utilizarea Tabele pivot, deoarece sigur sunt prea complicate. Într-adevăr, învățarea oricărui instrument sau metodă nouă necesită timp și efort. Dar, ca urmare, efectul stăpânirii noului ar trebui să depășească eforturile investite. În acest articol, vom înțelege cum să creăm și să aplicăm Tabele pivot.

Pregatirea tabelului initial

Să începem cu cerințele pentru tabelul original.

• fiecare coloană trebuie să aibă un titlu;
• fiecare coloană ar trebui să conțină doar valori într-un format (de exemplu, coloana Data livrării ar trebui să conțină toate valorile în format data; coloana „Furnizor” - numele companiilor numai în format text sau puteți introduce Codul Furnizorului în format numeric);
• tabelul nu trebuie să conțină rânduri și coloane complet goale;
• valorile „atomice” trebuie introduse în celule, adică numai cele care nu pot fi împărțite în coloane diferite. De exemplu, nu puteți introduce o adresă într-o singură celulă în formatul: „Oraș, Numele străzii, numărul clădirii”. Trebuie să creați 3 coloane cu același nume, altfel masă rotativă va funcționa ineficient (dacă aveți nevoie de informații, de exemplu, în contextul orașului);
• evitați tabelele cu structură „greșită” (vezi figura de mai jos).

În loc să producă coloane repetate ( regiunea 1, regiunea 2, …) care va avea celule goale din plin, regândiți structura tabelului așa cum se arată în figura de mai sus (Toate valorile volumului de vânzări ar trebui să fie într-o coloană, nu împărțite în mai multe coloane. Pentru a implementa acest lucru, poate fi necesar să mențineți mai mult de înregistrări detaliate(vezi figura de mai sus), în loc să enumerați vânzările totale pentru fiecare regiune).

Sfaturi mai detaliate despre construirea tabelelor sunt prezentate în articolul cu același nume.

Ușurează puțin procesul de construire. masă rotativă, faptul că dacă originalul ( Inserare/ Tabele/ Tabel). Pentru a face acest lucru, mai întâi aduceți tabelul sursă în conformitate cu cerințele de mai sus, apoi selectați orice celulă din tabel și apelați fereastra de meniu Inserare/ Tabele/ Tabel. Toate câmpurile din fereastră vor fi completate automat, faceți clic pe OK.

Acum bifați caseta de lângă câmpul Vânzări din Lista de câmpuri.

pentru că Dacă celulele din coloana Vânzări sunt în format numeric, acestea vor fi plasate automat în secțiunea Listă câmpuri valori.

Cu câteva clicuri de mouse (șase mai exact) am creat un raport de vânzări pentru fiecare produs. Același rezultat ar putea fi obținut folosind formule (vezi articolul).
Dacă este necesar, de exemplu, să se determine volumele de vânzări pentru fiecare Furnizor, atunci pentru a face acest lucru, debifați caseta de lângă câmpul Bunuri din Lista câmpurilor și bifați caseta de lângă câmpul Furnizor.

Detalierea datelor din tabelul pivot

Dacă aveți întrebări despre ce date din tabelul original au fost utilizate pentru a calcula anumite valori masă rotativă, atunci este suficient să faceți dublu clic pe o anumită valoare în masă rotativă pentru a crea o foaie separată cu rânduri selectate din tabelul original. De exemplu, să vedem ce înregistrări au fost folosite pentru a rezuma vânzările Produsului „Portocale”. Pentru a face acest lucru, faceți dublu clic pe valoarea 646720. Va fi creată o foaie separată cu doar rândurile tabelului inițial legate de Produsul „Portocale”.

Actualizați un tabel pivot

Dacă după creare masă rotativă noi înregistrări (rânduri) au fost adăugate la tabelul inițial, aceste date nu vor fi luate în considerare automat în masă rotativă. A updata masă rotativă selectați oricare dintre celulele sale și selectați elementul de meniu: meniu Lucrul cu PivotTables/ Opțiuni/ Date/ Reîmprospătare. Același rezultat poate fi obținut prin meniul contextual: selectați orice celulă masă rotativă Reîmprospăta.

Ștergerea unui tabel pivot

Șterge masă rotativă posibil în mai multe moduri. Primul este să scoți pur și simplu foaia de pe masă rotativă(cu excepția cazului în care are alte date utile, cum ar fi tabelul original). A doua modalitate este de a se șterge numai pe sine masă rotativă: selectați orice celulă masă rotativă, faceți clic CTRL+ A(toate vor fi selectate) masă rotativă), apăsați tasta Șterge.

Modificarea funcției de totaluri

În timp ce creați masă rotativă valorile grupate sunt însumate implicit. Într-adevăr, atunci când am rezolvat problema găsirii volumelor de vânzări pentru fiecare Produs, nu ne-a păsat de funcția de totaluri - toate Vânzările aferente unui Produs au fost însumate.
Dacă este necesar, de exemplu, să se contorizeze numărul de loturi vândute ale fiecărui Produs, atunci funcția de totaluri trebuie modificată. Pentru aceasta in masă rotativă Totaluri per/ Cantitate.

Modificarea ordinii de sortare

Acum să ne modificăm Raport consolidat. Mai întâi, să schimbăm ordinea de sortare a numelor Bunurilor: sortați-le în ordine inversă de la Z la A. Pentru a face acest lucru, prin lista derulantă din capul coloanei care conține numele Bunurilor, intrați în meniu și selectați Sortați de la Z la A.

Acum să presupunem că elementul Baranka este cel mai important element, așa că ar trebui să fie afișat pe prima linie. Pentru a face acest lucru, selectați celula cu valoarea Baganka și plasați cursorul pe marginea celulei (cursorul ar trebui să ia forma unei cruci cu săgeți).

Apoi, apăsând butonul stâng al mouse-ului, trageți celula în poziția cea mai de sus din listă, chiar sub titlul coloanei.

După ce butonul mouse-ului este eliberat, valoarea Bagel va fi mutată în poziția cea mai de sus din listă.

Modificați formatul valorilor numerice

Acum să adăugăm un separator de grup de cifre la valorile numerice (câmpul Vânzări). Pentru a face acest lucru, selectați orice valoare din câmpul Vânzări, apelați meniul contextual cu butonul din dreapta al mouse-ului și selectați elementul de meniu Format numeric…

În fereastra care apare, selectați formatul numărului și bifați caseta Separator de mii.

Adăugarea de noi câmpuri

Să presupunem că doriți să pregătiți un raport privind vânzările de Bunuri, dar defalcat pe regiuni de vânzare. Pentru a face acest lucru, adăugați câmpul Regiune de vânzări bifând caseta de selectare corespunzătoare din lista Câmpuri. Câmpul Regiunea de vânzări va fi adăugat în zona Nume rânduri din Lista câmpurilor (la câmpul Produs). Prin schimbarea în zonă Nume de rânduriÎn lista de câmpuri, în ordinea câmpurilor Produs și Regiunea de vânzare, obținem următorul rezultat.

Evidențiind orice nume de produs și făcând clic pe elementul de meniu Instrumente tabel pivot/Opțiuni/ Câmp activ/ Restrângere toate câmpurile, poate fi rulat masă rotativă pentru a afișa numai vânzările pe regiune.

Adăugarea coloanelor

Adăugarea câmpului Regiune de vânzări în zona de rânduri a rezultat masă rotativă extins la 144 de linii. Acest lucru nu este întotdeauna convenabil. pentru că vânzările s-au făcut doar în 6 regiuni, este logic să plasați câmpul Regiune vânzări în zona coloanei.

masă rotativă va lua următoarea formă.

Schimbarea coloanelor

Pentru a schimba ordinea coloanelor, trebuie să prindeți antetul coloanei masă rotativă trageți-l în locul potrivit.

Eliminarea câmpurilor

Orice câmp poate fi eliminat din PivotTable. Pentru a face acest lucru, deplasați cursorul mouse-ului peste el în Lista de câmpuri (în zonele Filtru de raport, Nume raport, Nume coloane, Valori), apăsați butonul stânga al mouse-ului și trageți câmpul de șters în afara Listei de câmpuri.

O altă modalitate este să debifați caseta de lângă câmpul de șters din partea de sus a Listei de câmpuri. Dar, în acest caz, câmpul va fi șters dintr-o dată din toate zonele Listei de câmpuri (dacă a fost folosit în mai multe zone).

Adăugarea unui filtru

Să presupunem că este necesar să se întocmească un raport cu privire la vânzările Grupurilor de Bunuri și trebuie făcut în 2 versiuni: una pentru loturile de Bunuri care au adus profit, cealaltă pentru cele neprofitabile. Pentru asta:

• PivotTable, faceți clic pe elementul de meniu;
• Bifați în Lista câmpurilor din apropierea câmpurilor Grup, Vânzări și Profit;
• Mutați câmpul Profit din zona Nume rânduri din Lista de câmpuri în zona Filtru raport;

Tipul rezultatului masă rotativă ar trebui sa fie asa:

Acum profitând Lista derulantă (derulantă).într-o celulă B1 (Câmpul de profit) puteți, de exemplu, să construiți un raport privind vânzările de Grupuri de Bunuri care au adus profit.

După apăsarea butonului OK, vor fi afișate valorile vânzărilor numai pentru Loturile profitabile.

Vă rugăm să rețineți că în Lista de câmpuri masă rotativă o pictogramă de filtru a apărut vizavi de câmpul Profit. Puteți elimina filtrul debifând caseta de selectare din Lista câmpurilor.

Puteți șterge filtrul prin meniu Instrumente tabel pivot/Opțiuni/Acțiuni/Șterge/Șterge filtre.

De asemenea, datele sunt disponibile prin lista derulantă din anteturile rândurilor și coloanelor Masă rotativă.

Totaluri multiple pentru un câmp

• Ștergeți raportul creat anterior: selectați orice valoare masă rotativă, faceți clic pe elementul de meniu Instrumente tabel pivot/Opțiuni/Acțiuni/Șterge/Șterge tot;
• Bifați casetele de lângă câmpurile Produs și Vânzări din partea de sus a Listei de câmpuri. Câmpul Vânzări va fi plasat automat în zona Valori;
• Trageți și plasați o altă copie a câmpului Vânzări în aceeași zonă Valori. LA masă rotativă vor exista 2 coloane care numără valoarea vânzărilor;

• în masă rotativă selectați orice valoare din câmpul Vânzări, faceți clic dreapta pe meniul contextual și selectați Totaluri per/ Cantitate. Problema rezolvata.

Dezactivează numărul total de rânduri

Bara de totaluri poate fi dezactivată prin intermediul meniului: Lucrul cu PivotTables/ Constructor/ Aspect/ Totaluri generale. Nu uitați să preselectați orice celulă masă rotativă.

Gruparea numerelor și a datelor

Să presupunem că doriți să pregătiți un raport privind timpul de vânzare. Ca urmare, trebuie să obțineți următoarele informații: câte loturi de Bunuri au fost vândute în perioada de la 1 la 10 zile, în perioada de 11-20 de zile etc. Pentru asta:

• Ștergeți raportul creat anterior: selectați orice valoare masă rotativă, faceți clic pe elementul de meniu Instrumente tabel pivot/Opțiuni/Acțiuni/Șterge/Șterge tot;
• Bifați caseta de lângă câmpul Vânzări (data vânzării efective a Bunurilor) din partea de sus a Listei de câmpuri. Câmpul Vânzări va fi plasat automat în zona Valori;
• selectați singura valoare a câmpului Vânzări în masă rotativă, apelați meniul contextual cu butonul din dreapta al mouse-ului și selectați elementul Totaluri per/ Cantitate.
• Trageți o altă copie a câmpului Vânzări cu mouse-ul în zona Nume rânduri;

Acum masă rotativă arată câte loturi de Bunuri au fost vândute în 5, 6, 7, ... zile. Sunt 66 de linii în total. Să grupăm valorile cu un pas de 10. Pentru a face acest lucru:

• Evidențiați o valoare masă rotativăîn coloana Nume rânduri;
• Selectați un element din meniu Grupați după câmp;
• Completați fereastra care apare, așa cum se arată în figura de mai jos;

• Faceți clic pe OK.

Acum masă rotativă arată câte loturi de Bunuri au fost vândute în perioada de la 1 la 10 zile, în perioada de 11-20 de zile etc.

Pentru a degrupa valori, selectați Degrupațiîn meniu Lucrul cu PivotTables/Opțiuni/Grup.

O grupare similară poate fi efectuată prin câmpul Data livrării. În acest caz, fereastra Grupați după câmp va arata asa:

Acum masă rotativă arată câte loturi de Bunuri au fost livrate în fiecare lună.

Formatarea condiționată a celulelor din tabel pivot

La celule masă rotativă puteți aplica regulile așa cum ați proceda la celulele dintr-un interval normal.
Să evidențiem, de exemplu, celulele cu cele mai mari 10 volume de vânzări. Pentru asta:

• Selectați toate celulele care conțin valorile vânzărilor;
• Selectați un element de meniu Acasă/ Stiluri/ Formatare condiționată/ Reguli de selectare a primei și ultimelor valori/ Primele 10 elemente;
• Faceți clic pe OK.

Ce sunt tabelele pivot Excel? Probabil, o persoană neinițiată va presupune cel mai evident - acestea sunt tabele în care sunt colectate (rezumate) date din mai multe alte surse disparate care îndeplinesc o anumită interogare. De fapt, această concluzie nu este în întregime corectă.

Excel PivotTables este un instrument care colectează, conform unui algoritm dat, informații de la care cel mai adesea un tabel de bază de date! (Posibilitatea de a utiliza mai multe intervale de consolidare ca sursă există, dar nu va fi discutată în acest articol.)

Cu ajutorul tabelelor pivot dintr-un set haotic de rânduri ale bazei de date, puteți în secunde extrageți-le pe cele necesare, tăiați informațiile inutile din ele, adăugați informațiile lipsă și prezentați-le utilizatorului într-o formă frumoasă și ușor de citit.

Dacă lucrezi constant în MS Excel cu cantități mari de informații și nu știi nimic despre tabelele pivot, atunci ia în considerare că „bați cuie” cu un calculator fără a-i cunoaște adevăratul scop!

Când ar trebui folosite tabelele pivot?

În primul rând, atunci când lucrați cu o cantitate mare de date statistice, ceea ce este foarte laborios de analizat cu ajutorul sortării și filtrelor.

În al doilea rând, când este necesară repetarea (recalcularea) rapoartelor la anumite intervale din cauza modificărilor sursei - tabelul bazei de date.

În al treilea rând, atunci când se analizează modificările datelor din bazele de date în contextul diferitelor perioade de timp sau alte criterii.

Acestea sunt principalele situații în care este dezvăluită puterea magică reală a instrumentului Excel PivotTable.

Crearea tabelelor pivot și lucrul cu acestea nu afectează conținutul sursei - baza de date! Tabele pivot excelavă permit să priviți aceste date din unghiuri diferite fără a schimba nimic din baza de date!!!

Acesta este ultimul și cel mai important articol al seriei de recenzii pe stocarea informațiilor și gestionarea volumelor mari de date tabulare.

În articolele anterioare ale ciclului, am creat, am învățat, am aplicat și, am intrat.

Creați un șablon de tabel pivot.

Vom crea un tabel pivot în același registru de lucruexcela, unde se află sursa de informații - un tabel de bază de date, dar pe o altă foaie.

Continuăm să lucrăm cu minibaza educațională DB2 „Producția structurilor metalice pe secția Nr. 2”, cu care am lucrat în toate articolele acestui ciclu.

1. Deschideți fișierul în MS Excel.

2. Activăm („click cu mouse-ul”) orice celulă din interiorul tabelului de bază.

3. Executăm comanda meniului principal al programului „Date” - „Tabel pivot...”. Această comandă lansează mini-serviciul PivotTable Wizard.

4. Fără să ne gândim mult timp la opțiunile de alegere a pozițiilor comutatoarelor din ferestrele drop-down ale „Wizard...”, le configuram (mai precis, nu le atingem) așa cum se arată în capturile de ecran de mai jos, deplasarea între ferestre folosind butoanele „Următorul”.

La al doilea pas, „Wizard...” va alege intervalul în sine dacă ați pregătit corect baza de date și ați finalizat pasul 2 din această secțiune a articolului.

5. La al treilea pas al „Expert...”, faceți clic pe butonul „Terminare”. Șablonul tabel pivot este generat și plasat pe o nouă foaie a aceluiași fișier în care se află baza de date!

Cu excepția momentului șablon gol pe foaia nou creată fisier Excel vedeți panoul „Tabelele pivot” cu instrumentele sale și fereastra „Lista câmpurilor tabelului pivot” care apare atunci când orice celulă din interiorul tabelului pivot este activată. Apar automat, doar le-am așezat lângă șablon și am ajustat dimensiunile. Dacă nu vedeți panoul „Pivot Tables”, atunci apelați-l prin meniul principal cu comanda „View” - „Toolbars” - „Pivot Table”

În MS Excel 2007 și versiunile mai noi, „Wizard...” este desființat deoarece 99% dintre utilizatori nu modifică niciodată setările propuse de comutare și parcurg acești trei pași, pur și simplu fiind de acord cu opțiunile propuse (am făcut și asta).

În MS Excel 2007, folosind comanda „Pivot Table”, apare caseta de dialog „Create Pivot Table”, în care este suficient să specificați sursa de date pentru analiză și locația tabelului pivot care urmează să fie creat.

Crearea de tabele pivot Excel funcționale.

În șablonul creat există patru zone, în care pentru a crea un tabel pivot ar trebui să plasați numele câmpurilor-coloane ale tabelului bazei de date trăgându-le cu butonul stâng al mouse-ului din fereastra „Lista câmpurilor tabelului pivot”. Vă reamintesc că în baza de date un câmp este o coloană cu un nume.

Atenţie!!!

Elementele ferestrei „List of PivotTable Fields” sunt titlurile câmpurilor bazei de date!

1. Dacă plasați unul dintre elementele „Pivot Table Field List” în cel mai mult zona superioarașablon unde va deveni „Câmpul paginii”, atunci obținem un convenabil filtrați înregistrările acestui câmp.

2. Dacă glisați un element (sau două, mai rar trei sau mai multe) din Lista de câmpuri PivotTable în zona din stânga a șablonului, unde va deveni „Câmp de șiruri” antete de rând masă rotativă.

3. Dacă trageți un element (sau două, mai rar - trei sau mai multe) din „Lista...” în zona superioară a șablonului, unde va deveni „Câmp de coloană”, apoi vom obține din înregistrările acestui câmp al tabelului bazei de date titlurile coloanelor masă rotativă.

4. Dacă trageți un element (sau două, mai rar - trei sau mai multe) din „Lista ...” în zona centrală a șablonului, unde va deveni "articol de date", apoi vom obține din înregistrările acestui câmp de bază de date valorile masă rotativă.

Valorile - elementele de date - vor fi localizate în strictă conformitate cu regulile tabelelor bidimensionale, adică la intersecția antetelor de rând corespunzătoare și antetelor de coloană!

Nu foarte clar? Să trecem la exemple practice - totul va deveni clar!

Sarcina numărul 9:

Câte tone de structuri metalice au fost produse pentru fiecare comandă?

Să facem două lucruri pentru a răspunde la această întrebare! Schema acestor acțiuni este prezentată în figura anterioară cu săgeți albastre.

1. Trageți elementul „Comandă” din fereastra „List of Pivot Table Fields” în zona „Row Fields” a șablonului gol.

2. Trageți elementul „Masa totală, t” din fereastra „Lista de câmpuri din tabelul pivot” în zona „Elemente de date” a șablonului. Rezultatul este în captura de ecran din stânga. Cred că nu este nevoie de explicații.

Se află antetul „Elementelor de date” „Suma peste câmpul Masa totală, t”. de mai sus rubrica „Câmpuri de rând” și în stânga locului în care poate fi amplasată titlul „Câmpuri de coloană”. Fii atent la asta!

Sarcina numărul 10:

Câte tone de structuri din oțel au fost produse pentru fiecare comandă după date?

Continuăm să muncim.

3. Pentru a răspunde la întrebarea sarcinii nr. 10, este suficient să adăugați elementul „Dată” la tabelul nostru pivot din fereastra „Lista câmpurilor tabelului pivot” în zona „Câmpuri de coloană” a șablonului.

4. Înregistrările pot fi grupate, de exemplu, pe lună. Pentru a face acest lucru, în panoul „Pivot Tables”, faceți clic pe fila „Pivot Table” și selectați „Group and Structure” - „Group...”. În fereastra Grupare, faceți setările în conformitate cu captura de ecran din stânga.

Deoarece toate înregistrările din baza noastră de date au fost făcute în aprilie, după grupare, vedem doar două coloane - „Apr” și „Total mare”. Când înregistrările din mai și iunie apar în baza de date sursă, cele două coloane corespunzătoare sunt adăugate la tabelul pivot.

Sarcina numărul 11:

Când și câte tone și bucăți de timbre au fost produse în total prin comanda nr. 3?

5. Pentru a începe, trageți câmpul „Comandă” din fereastra „List of Pivot Table Fields” sau direct din Pivot Table în partea de sus a foii în zona „Page Fields” a șablonului.

7. Adăugați câmpul „Cantitate, buc” în zona „Elemente de date”.

S-a format configurația tabelului pivot Excel necesar pentru a răspunde la întrebarea sarcinii.

8. Faceți clic pe butonul săgeată în jos din celula B1 și în lista derulantă, în loc de intrarea „(Toate)”, selectați „3”, corespunzător comenzii nr. 3 care ne interesează și închideți lista făcând clic pe „ butonul OK”.

Răspunsul la întrebarea despre sarcină este în captura de ecran de sub acest text.

Formatarea tabelului pivot.

Dacă tabelul pivot trebuie să fie tipărit și arătat cuiva, sau doar pentru a vă mulțumi puțin, atunci trebuie formatat - dat un aspect decent și reformulat niște titluri „inadecvate”.

Titlurile „Suma pentru câmpul Greutatea totală, t” și „Suma pentru câmpul Cantitate, buc” sună, într-un fel, și de înțeles, dar cumva nu în rusă. Să le redenumim în „Masa totală de produse, t” și „Număr de produse, bucăți”.

1. Să continuăm cu tabelul creat. Activați celula B5 cu mouse-ul.

2. În bara de instrumente PivotTable, faceți clic pe butonul Opțiuni câmp (evidențiat în partea dreaptă în imaginea de mai jos).

3. În fereastra derulantă „Calculul câmpului tabelului pivot” schimbați numele și faceți clic pe butonul „OK”.

4. Conform unui algoritm similar, redenumim al doilea antet de câmp, după ce mai întâi „inserăm” mouse-ul pe celula B6.

5. În panoul „Tabel Pivot”, faceți clic pe butonul „Format raport” (evidențiat în partea stângă în imaginea de mai sus - deasupra punctului 3).

6. În fereastra „AutoFormat” care apare, selectați dintre opțiunile de formatare propuse, de exemplu, „Raport 6” și faceți clic pe „OK”.

Aspect tabelul nostru pivot s-a schimbat semnificativ.

Concluzie.

Vă atrag atenția asupra mai multor puncte foarte importante!

La schimbarea sursei (de exemplu, adăugarea unei alte înregistrări la baza de date), modificările nu vor avea loc automat în tabelul pivot în sine !!!

Pentru ca modificările din baza de date să se reflecte în tabelul pivot, este necesar să apăsați „manual” de fiecare dată pe butonul „Actualizați datele” (pictograma butonului este „ ! ) în panoul PivotTables!!!

Plasați în zona „Margini de pagină” câmpurile care conțin informații text, care poate deveni rubrica pentru tabelul pivot generat.

În zona Câmpuri de rând și Câmpuri de coloană, plasați câmpurile care conțin informații text, care poate deveni titluri pentru rânduri și coloane a creat un tabel pivot.

În zona „Elemente de date”, plasați în principal informatii numerice.

Continuați prin plasarea câmpurilor așa cum ați face-o pe o bucată de hârtie! Nu vă fie frică să faceți greșeli. Totul se remediază ușor.

Tabelele pivot create cu două sau trei mișcări ale mouse-ului într-una dintre secțiunile anterioare ale acestui articol au răspuns foarte repede la întrebări foarte dificile! Dacă comenzile sunt făcute în decurs de câteva luni, iar numărul de nume de mărci depășește câteva mii, atunci cât timp vă va dura să rezolvați problemele discutate mai sus? Ora? Zi? Excel PivotTables o fac instantaneu, repetat și fără erori!!!

S-au scris multe cărți groase și subțiri despre Excel PivotTables. În primul rând, acestea sunt cărți de Bill Jelen („Mr. Excel”) și Michael Alexander. Pentru cei care sunt interesați de acest subiect, recomand să le citească.

Desigur, este foarte greu, aproape imposibil, să vorbim în detaliu despre acest instrument minunat într-un scurt articol. Pe de o parte, am vrut să ating aspecte mai importante din punct de vedere practic, dar, pe de altă parte, în articolul introductiv nu am vrut să „intru în junglă”, sperierea utilizatorilor cu volumul și complexitatea informațiilor. Dacă acest „instrument magic” MS Excel stârnește interesul cititorilor blogului, atunci în viitor acest subiect va fi continuat cu lansarea unei mici broșuri cu recomandări practice importante și valoroase.

Asigurați-vă că utilizați Excel PivotTables! Valoarea ta ca specialist va creste de multe ori!

Pe această serie de șase articole, care este o prezentare generală a subiectului procesarea unor cantități mari de informații în Excelefectuat.

implor respectând opera autorului Abonati-va pentru anunţuri de articole în fereastra situată la sfârșitul fiecărui articol sau în fereastra din partea de sus a paginii!

Dragi cititori, lăsați întrebări, feedback și comentarii în comentariile din partea de jos a paginii.

Tabelele pivot Excel sunt unul dintre instrumentele care vă ajută să analizați rapid și ușor cantități mari de informații. Vom arăta cu exemple cum să construim tabele pivot în Excel.

În acest articol veți învăța:

Ce este un tabel pivot în Excel?

Când lucrați cu date în Excel, periodic este necesar să le analizați. Acest lucru este important, de exemplu, în vânzări - cine, în ce perioadă, câți au făcut vânzări, când a existat o recesiune și când a existat o creștere, care bunuri sunt cele mai solicitate, care sunt cele mai puține. Cu cât sunt mai multe date, cu atât este mai dificil să le analizezi. Cu cât probabilitatea de eroare din cauza factorului uman este mai mare. Prin urmare, este necesar să aplicați instrumentele care sunt furnizate de Excel însuși.

Tabelele pivot MS Excel este un instrument de analiză și prezentare a datelor într-o formă convenabilă și ușor de înțeles. Ele sunt utilizate în următoarele cazuri:

1. Cantitate mare de date inițiale. O cantitate mică de date poate fi calculată și analizată manual. Cu toate acestea, dacă există sute și mii de înregistrări la dispoziție, erorile sunt inevitabile și vor fi multe dintre ele. Timpul necesar procesării va crește, de asemenea, semnificativ.
2. Este necesar să se identifice tendințele și dinamica datelor. În tabelele pivot, puteți transforma cu ușurință rezultatul informațiilor, nu trebuie să le sortați manual.
3. Pentru a crea o diagramă sau un grafic bazat pe date formatate.
4. Este necesar să se facă o „tăiere” intermediară a rezultatelor, care se schimbă adesea, pentru compararea lor ulterioară. În acest caz, este suficient să actualizați datele, Excel va recalcula totul automat.
5. Pentru analiza și căutarea valorilor extreme - minime și maxime de date.
6. Pentru a reuni date disparate din surse diferite. Dacă este necesar, puteți adăuga rândul sau coloana necesară la datele sursă și puteți actualiza afișarea finală a raportului.

De asemenea, tabelele pivot din Excel sunt mai convenabile de utilizat atunci când vizualizați date. .

Cum arată un tabel pivot

Înainte de a vorbi despre cum să construim un tabel pivot în Excel, să arătăm cum arată. Datele pentru tabel sunt preluate din tabelul partajat specificat de utilizator. Din punct de vedere funcțional, tabelul este împărțit în patru segmente:

• valori;
• linii;
• coloane;
• filtru de raportare.

Tabelul Pivot al fișierului .xlsx are următoarele limitări:

• numărul maxim de câmpuri de rând este de 1.048.576 sau limitat de dimensiunea memoriei RAM;
• numărul maxim de câmpuri de coloană este de 16.384;
• numărul maxim de câmpuri de pagină este de 16.384;
• numărul maxim de câmpuri de date este de 16.384.

Să luăm următoarele date ca exemplu.

Tabelul pivot pentru aceste date va arăta astfel:

În partea dreaptă a tabelului pivot - nume de familie, în partea de sus - date, în centru - rezultatele lucrării. Evident, această prezentare a informațiilor este mai convenabilă și mai ușor de înțeles. .

În această imagine, segmentul de valoare este evidențiat cu roșu. Acesta este segmentul în care se efectuează calculele. Aici puteți vedea costul ofertelor încheiate pe zi și după nume.

Acesta este un segment de șir. Pot exista una sau mai multe valori aici. În cazul nostru, acestea sunt numele managerilor. Opus numelor din rândurile corespunzătoare - un rezumat al lucrării.

Acesta este segmentul coloanei. De obicei este o scară de timp, utilizarea sa facilitează analiza și percepția datelor.

Într-un expert special pentru tabel pivot, puteți modifica plasarea datelor. În cazul nostru, numele de familie pot fi plasate în segmentul de coloană, în date - în segmentul de rând. Apoi tabelul pivot va căpăta un nou aspect.

PivotTable Builder este intuitiv. În ea, puteți trage valori în câmpuri, puteți selecta valori dintr-o listă derulantă. În continuare, în articol, un tutorial despre crearea unui tabel pivot în Excel cu exemple. .

Cum se creează un tabel pivot în Excel

Vom spune și arăta cu exemple cum să creați un tabel pivot în Excel. Ca date inițiale, să luăm tabelul nostru cu informații despre munca managerilor pentru luna mai. Pentru a crea un tabel pivot, selectați „Tabel pivot” din meniul de sus din fila „Inserare”. Ele sunt evidențiate în imagine.

Va apărea o nouă fereastră:

Va trebui să specifice sursa ale cărei date vor fi folosite pentru a construi tabelul pivot. Pentru a face acest lucru, trebuie să selectați zona dorită sau întregul tabel și Excel va înlocui aceste date într-un rând.

Culoarea arată că întreaga matrice este selectată, datele au apărut în linie. Apoi trebuie să specificați unde Excel ar trebui să construiască tabelul pivot. Puteți selecta foaia curentă sau puteți selecta o foaie nouă.

Dacă selectați o foaie nouă, atunci în acest caz va fi creată, designerul tabelului pivot va apărea pe ea. Vă recomandăm să faceți acest lucru. După ce toate informațiile sunt specificate - datele sursă și locul pentru raport, trebuie să faceți clic pe butonul „OK” și va apărea o solicitare care vă va solicita să selectați câmpurile pentru construirea unui nou raport.

În dreapta acestuia, se va deschide o secțiune în care puteți specifica câmpurile pentru a le adăuga la tabelul pivot.

Sub acesta va exista un alt câmp de lucru unde puteți trage câmpuri în diferite zone.

Trebuie să bifați casetele pentru câmpurile obligatorii. Pozițiile specificate vor apărea automat în secțiunea de jos. Ele pot fi personalizate după bunul plac.

La reconstruirea datelor sursă, afișajul va fi reconstruit automat. Dacă există multe valori, atunci aceasta poate interfera operatie normala. Prin urmare, puteți bifa caseta de selectare „Amânați actualizarea aspectului”. Reconstrucția automată va fi dezactivată. Pentru a reîmprospăta tabelul pivot, faceți clic pe butonul Actualizează.

Astfel, vom crea un tabel pivot și acesta va lua următoarea formă.

Se poate observa că ordinea liniilor contează. Prima linie din constructor este prima. A doua linie din constructor devine un subparagraf al primului. În cazul nostru, rezultatele muncii fiecărui manager sunt prezentate cu o defalcare pe articole de mărfuri - articole cu referire la date.

Puteți schimba ordinea liniilor - pentru a face acest lucru, trebuie doar să trageți linia dorită în sus sau în jos cu mouse-ul. Dacă schimbați numele de familie și articolul, atunci tabelul pivot va lua următoarea formă.

Pentru ușurarea percepției informațiilor, sub-articolele pot fi restrânse. În mod implicit, acestea sunt afișate extinse. Pentru a restrânge pozițiile inutile, trebuie să faceți clic pe semnul minus de lângă articol. Apoi sub-articolele vor fi ascunse. Pentru a le extinde, dați clic pe semnul plus. Ele sunt evidențiate în imagine.

Segmentul central de valoare va afișa toate informațiile rezumate și calculate de program. Totalul va fi de asemenea afișat - după date, pe articole și dacă articolul nu este restrâns - atunci după numele managerilor.

Dacă faceți modificări în tabelul original, tabelul pivot trebuie actualizat. Pentru a face acest lucru, faceți clic dreapta oriunde în locul său și selectați în meniul apărut meniul contextual"Actualizați". Datele vor fi reconstruite.

Dacă s-au adăugat rânduri sau coloane noi, iar afișarea raportului vechi nu acoperă tot setul de date necesar, trebuie să le includeți în tabelul pivot. De exemplu, date noi au fost adăugate la gama principală de informații (evidențiate în culoare), iar tabelul pivot nu le ia în considerare încă.

Pentru a le adăuga la raport, trebuie să faceți clic pe tabelul pivot, să mergeți la fila „Parametri” și să selectați „Schimbați sursa de date” - această opțiune este evidențiată în imagine.
În fereastra care apare, specificați sursa de date împreună cu rândurile actualizate și faceți clic pe OK.

Tabelul pivot se va reconstrui automat. Pe imagine, datele pentru 29.05.2018, care nu erau disponibile anterior, sunt evidențiate color.

VIDEO: Cum să lucrați cu tabele pivot

Prietena mea, economistă la întreprindere, deține Excel, se uită uneori pe forumuri, dar timp de 5 ani nu a putut aborda cel mai mult, după părerea mea, instrumentul Excel util - tabele pivot. Ce este? Tabelele pivot sunt rezultatul unei selecții de date bine formate. Instrumentul are multe avantaje, nu îl voi descrie, voi spune doar că fără el, mulți ar trebui să stăpânească nu numai Excel, ci și Microsoft Access.

Părerea mea este că acest instrument ar trebui studiat singur, iar în acest articol vă voi spune nuanțele de care se poticnesc începătorii care vor să stăpânească acest instrument.

ST necesită date bine formate. Vedere ideală - o operație pe linie corespunde diferitelor câmpuri ale proprietăților înregistrării.

Am realizat un tabel simplu care apare in orice depozit. Depozitul livrează materiale de construcție către 4 magazine pe parcursul anului, iar fiecare operațiune de transport de mărfuri are propria evidență.

După cum puteți vedea, aici nu există o grupare sau o însumare a datelor, eu o numesc o foaie, pentru că, deși pot fi multe coloane, sunt mult mai multe rânduri. Nu există lacune aici, ceea ce este, de asemenea, important.

Excel va sugera un interval, așa că faceți clic pe OK.

Există 4 opțiuni aici: Rânduri, Coloane, Valori și Filtru de raport.

Să ne oprim mai în detaliu. Siruri de caractereși coloane practic aceleasi categorii. Puteți pune aici acele câmpuri care sunt orientative - de exemplu, nume, lună, categorie. Puteți pune două în rânduri și niciunul într-o coloană, caz în care tabelul dvs. va avea o grupare pe rânduri.

Valori- aici punem doar valori numerice.

Filtru de raportare. După cum înțelegeți, vă construiți singur cadrul și puteți ignora unele câmpuri, le puneți pe orizontală, altele pe verticală. Atât rândurile, cât și coloanele vor avea propriile filtre, dar filtrul de raport vă permite să selectați mai convenabil datele pe care trebuie să le afișați în acest tabel.

Acum, pe exemplul datelor mele.

Ce fel de masă poate lua:

După cum puteți vedea, există multe variante.

1) Deși tabelul pivot scoate foarte clar toate feliile de informații disponibile, nu este întotdeauna ușor să lucrezi în el, nu poate fi editat, ci doar în citire. Dacă aveți o mulțime de secțiuni de informații, atunci este mai ușor să faceți un tabel și să îl completați folosind funcția GET.DATA.PIVOTAL.TABLE și regulile de lucru cu legături relative.

Formatul funcției este simplu ("Valori"; "PivotTable"; "Field1"; "Item1"; "Field2"; "Item2", etc.)

Adică, puteți înlocui manual valorile Elementelor cu legăturile relative ale tabelului dvs.

2) Pentru a actualiza constant datele și a nu atinge aspectul, adăugați date în partea de jos a foii. Atunci nimic nu se va accesa cu crawlere pentru tine, iar actualizarea se va întâmpla cu un singur buton pentru a actualiza pe ST în sine. În plus, în sursa de date pentru ST, specificați fie toate coloanele foii (opțiune leneș), fie numele intervalului, ale cărui dimensiuni vor fi modificate în mod arbitrar fără a intra în setările ST.

„Ochilor le este frică, dar mâinile fac”