Cum se construiește un tabel pivot în Excel. Cum să lucrezi cu datele

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, de fapt, „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 rând 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?” și „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 pe acesta cu butonul stâng al mouse-ului și trageți-l în câmpul „RINDURI”. Câmpul „Manager” este tras în „COLUMNS”, iar „Suma” este tras în „VALUES”:

Tabelele pivot sunt unul dintre cele mai eficiente instrumente din MS Excel. Cu ajutorul lor, puteți transforma un milion de rânduri de date într-un raport concis în câteva secunde. Pe lângă rezumarea rapidă a rezultatelor, tabelele pivot vă permit să schimbați literalmente modul în care analizați din mers, trăgând câmpuri dintr-o zonă a raportului în alta.

Tabelele pivot sunt, de asemenea, unul dintre cele mai subestimate instrumente Excel. Majoritatea utilizatorilor nu sunt conștienți de posibilitățile care se află în mâinile lor. Să ne imaginăm că tabelele pivot nu au fost încă inventate. Lucrezi pentru o companie care își vinde produsele către diverși clienți. Pentru simplitate, există doar 4 articole în sortiment. Produsele sunt achiziționate în mod regulat de câteva zeci de clienți aflați în diferite regiuni. Fiecare tranzacție este introdusă în baza de date și reprezintă o linie separată.

Directorul dumneavoastră vă instruiește să faceți un scurt raport cu privire la vânzările tuturor mărfurilor pe regiune (regiune). Problema poate fi rezolvată după cum urmează.

Mai întâi, să creăm un aspect de tabel, adică un antet format din valori unice pentru produse și regiuni. Să facem o copie a coloanei de produs și să eliminăm duplicatele. Apoi, folosind insert special, transpunem coloana într-un rând. La fel facem și cu regiunile, doar fără a transpune. Vom primi antetul raportului.

Această plăcuță trebuie completată, adică rezumați veniturile pentru produsele și regiunile relevante. Acest lucru este ușor de realizat folosind funcția SUMIFS. Să adăugăm și rezultatele. Veți primi un raport rezumat al vânzărilor pe regiune și produs.

Ați finalizat sarcina și arătați raportul directorului. Privind la masă, el generează mai multe idei grozave deodată.

— Este posibil să faceți un raport nu asupra veniturilor, ci asupra profitului?

— Este posibil să afișați produsele pe rânduri și regiunile după coloane?

— Este posibil să se creeze astfel de tabele pentru fiecare manager separat?

Chiar dacă sunteți un utilizator Excel cu experiență, crearea de noi rapoarte va dura mult timp. Ca să nu mai vorbim de posibilele erori. Cu toate acestea, dacă cunoașteți tabele pivot, atunci răspunsul este: da, am nevoie de 5 minute, poate mai puțin.

Iată cum se face. Deschideți datele sursă. Un tabel pivot poate fi construit folosind un interval obișnuit, dar ar fi mai corect să-l transformăm. Acest lucru va rezolva imediat problema captării automate a datelor noi. Selectați orice celulă și accesați fila Introduce. Pe partea stângă a panglicii sunt două butoane: și Tabele pivot recomandate.

Dacă nu știți cum să organizați datele existente, puteți utiliza comanda Tabele pivot recomandate. Excel va afișa miniaturi ale posibilelor aspecte bazate pe datele dvs.

Faceți clic pe opțiunea corespunzătoare și tabelul pivot este gata. Rămâne doar să-l aduci în minte, pentru că... Este puțin probabil ca un preparat standard să coincidă complet cu dorințele tale. Dacă trebuie să construiți un tabel pivot de la zero sau aveți o versiune veche a programului, faceți clic pe butonul. Va apărea o fereastră în care trebuie să specificați intervalul sursă (dacă activați orice celulă de tabel Excel, aceasta va fi determinată de la sine) și locația viitorului tabel pivot (va fi selectată implicit o nouă foaie).

De foarte multe ori nu este nevoie să schimbați nimic aici. După ce faceți clic pe OK, va fi creată o nouă foaie Excel cu un aspect necompletat de tabel pivot.

Înainte de a trece la setări, să ne familiarizăm cu interfața și conceptele de bază. Dispunerea tabelului este personalizabilă în panou Câmpuri din tabel pivot, care se află pe partea dreaptă a foii.

În partea de sus a panoului există o listă cu toate câmpurile disponibile, adică coloanele din datele sursă. Dacă trebuie să adăugați un câmp nou la aspect, puteți bifa caseta de lângă acesta - Excel însuși va determina unde ar trebui plasat acest câmp. Cu toate acestea, nu este întotdeauna corect, așa că este mai bine să trageți mouse-ul în locul dorit pe aspect. Câmpurile pot fi, de asemenea, șterse debifând sau trăgându-le înapoi.

Tabelul pivot este format din 4 zone, care sunt situate în partea de jos a panoului: valori, rânduri, coloane, filtre. Să aruncăm o privire mai atentă asupra scopului lor.

Gama de valori– aceasta este partea centrală a tabelului pivot cu valori care se obțin prin agregarea datelor sursă folosind metoda selectată.
În cele mai multe cazuri, agregarea are loc prin Însumarea. Dacă toate datele din câmpul selectat sunt în format numeric, Excel va atribui o sumă implicită. Dacă datele sursă conțin cel puțin un text sau celulă goală, atunci în loc de sumă se va calcula Cantitate celule. În exemplul nostru, fiecare celulă este suma tuturor produselor corespunzătoare din regiunea corespunzătoare.

Puteți utiliza alte metode de calcul în celulele PivotTable. Există aproximativ 20 de tipuri (medie, valoare minimă, proporție etc.). Există mai multe moduri de a schimba metoda de calcul. Cel mai simplu este să faceți clic dreapta pe orice celulă a câmpului dorit din tabelul pivot în sine și să selectați o altă metodă de agregare.

Zona liniei– numele liniilor care se află în coloana din stânga. Acestea sunt toate valorile unice ale câmpului (coloana) selectat. Pot exista mai multe câmpuri în zona rândurilor, apoi tabelul devine cu mai multe niveluri. Aici sunt plasate de obicei variabile calitative precum numele produselor, lunile, regiunile etc.

Zona coloanelor– asemănător cu rândurile, arată valorile unice ale câmpului selectat, doar în coloane. Titlurile coloanelor sunt, de obicei, un indicator calitativ. De exemplu, ani și luni, grupuri de produse.

Zona de filtrare– folosit, după cum sugerează și numele, pentru filtrare. De exemplu, raportul în sine arată produsele în funcție de regiune. Trebuie să vă limitați tabelul pivot la o anumită industrie, perioadă sau manager. Apoi un câmp de filtru este plasat în zona de filtrare și valoarea dorită este selectată din lista derulantă.

Adăugând și eliminând câmpuri în zonele specificate, puteți personaliza orice porțiune de date dorite în câteva secunde.

Să vedem cum funcționează acest lucru în acțiune. Deocamdată, să creăm același tabel care a fost deja creat folosind funcția SUMIFS. Pentru a face acest lucru, trageți în zonă Valori câmpul „Venituri”, în zonă Siruri de caractere trageți câmpul „Regiune” (regiune de vânzări) la Coloane- "Produs".

Ca rezultat, obținem un tabel pivot real.

A durat literalmente 5-10 secunde pentru a-l construi. Modificarea unui tabel pivot existent este, de asemenea, ușoară. Să vedem cum dorințele regizorului se traduc ușor în realitate.

Să înlocuim veniturile cu profit.

Produsele și zonele pot fi schimbate și prin tragerea mouse-ului.

Există mai multe instrumente disponibile pentru filtrarea tabelelor pivot. În acest caz, vom plasa pur și simplu câmpul „Manager” în zona de filtrare.

Totul a durat câteva secunde. Iată cât de ușor este să lucrezi cu Pivot Tables. Desigur, nu toate sarcinile sunt atât de banale. Există și cazuri în care este necesar să se folosească o metodă de agregare mai sofisticată, adăugarea de câmpuri calculate, formatare condiționată etc. Dar aceasta este o utilizare mai avansată a tabelelor pivot.

Datele inițiale

Pentru a lucra cu succes cu tabele pivot, datele sursă trebuie să îndeplinească o serie de cerințe. O condiție prealabilă este prezența numelor deasupra fiecărui câmp (coloană) prin care aceste câmpuri vor fi identificate. Acum câteva sfaturi utile.

1. Cel mai bun format pentru date este o foaie de calcul Excel. Este bine pentru că fiecare câmp are un nume și atunci când sunt adăugate rânduri noi, acestea sunt incluse automat în tabelul pivot.

2. Evitați repetarea grupurilor în coloane. De exemplu, toate datele ar trebui să fie într-un câmp și nu împărțite, de exemplu, pe lună în coloane separate.

3. Îndepărtați golurile și celulele goale, altfel acest rând poate cădea din analiză.

4. Aplicați formatarea adecvată câmpurilor. Numerele trebuie să fie în format numeric, datele trebuie să fie date. În caz contrar, vor apărea probleme în timpul grupării și procesării matematice. Dar Excel vă va ajuta aici, pentru că... Determină destul de bine formatul datelor.

În general, există puține cerințe, dar ar trebui să le cunoașteți.

Dacă faceți modificări sursei (de exemplu, adăugați noi rânduri), tabelul pivot nu se va schimba până când nu îl reîmprospătați folosind butonul din dreapta al mouse-ului

sau
prin comanda din filă Date – Actualizează toate.

Acest lucru a fost făcut intenționat, deoarece tabelul pivot ocupă mult spațiu în RAM. Prin urmare, pentru a utiliza resursele computerului mai economic, se lucrează nu direct cu sursa, ci cu memoria cache, unde se află un instantaneu al datelor sursă.

Folosind tabele pivot chiar și la acest nivel de bază, puteți crește semnificativ viteza și calitatea procesării unor cantități mari de date. Dacă rămâne ceva neclar, scrieți în comentarii.

Tabele 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.

Când am scris acest articol, am folosit Excel 2010. 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 versiune nouă de Excel. Dacă aveți o versiune de Excel, alta decât 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. Dominanța sa a fost atât de completă încât eforturile Microsoft de a-și dezvolta propriul software (Excel) ca alternativă la Lotus au părut 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 se poate întâmpla? 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 exclusivitate Excel, au fost atât de incredibil de utile încât oamenii au fost înclinați să învețe noul software Excel, mai degrabă decât să rămână cu 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?

În termeni simpli, 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 diferite pe care le puteți face cu tabelele pivot. Î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? Ora? Ora 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 Masă rotativă(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 sau chiar o sursă de date externă, cum ar fi un tabel de bază de date Access 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 acest exemplu, vom alege opțiunea - 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 pe scurt

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. Acestea. Î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. Informații importante au fost deja primite! Î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ă aruncăm o privire…

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ă numai plățile prin cec și card de credit (adică transfer bancar) ar trebui să fie afișate în tabelul pivot, atunci putem dezactiva afișarea antetului Bani gheata(Bani gheata). 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 Bani gheata:

Să vedem cum arată tabelul nostru pivot acum. După cum puteți vedea, coloana Bani gheata 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 Celule de format(Format celulă):

Din listă Categorie(Formate numerice) selectați Contabilitate(financiar) și setați numărul de zecimale la 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 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, selectează 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 In medie(In 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 singur tabel pivot.

Iată un ghid pas cu pas despre cum să faceți acest lucru, începând cu un tabel pivot gol:

1. Trageți titlul Agent de vânzări(Reprezentant de vânzări) în regiune Etichete de coloane(Coloane).
2. Trageți titlul de trei ori Cantitate(Cost) la zonă Valori(Valori).
3. 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.
4. Al doilea câmp Cantitate Nume medie e, setați operația pentru aceasta In medie(Medie) și modificați formatul numărului din acest câmp în Contabilitate(financiar) cu numărul de zecimale egal cu zero.
5. Pentru al treilea câmp Cantitate setați titlul Numarași o operație pentru el - Numara(Cantitate)
6. În zonă Etichete de coloane Câmp (Coloane) creat automat ΣValori(Σ Values) – trageți-l în zonă Etichete de rând(Siruri de caractere)

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ă.

Tabelele pivot din Excel vă permit să analizați datele care se află într-un interval mare. Mai mult, tabelul sursă care va fi folosit pentru analiză poate fi creat fie în Excel, fie într-o altă bază de date sau alt document.

Tot ce trebuie să faceți este să selectați anteturile necesare pentru rânduri și coloane dintre cele existente. De asemenea, puteți aplica un filtru întregului tabel pivot în ansamblu sau numai valorilor care sunt indicate în numele rândurilor și coloanelor.

Acum despre cerințele care trebuie respectate la crearea lui în Excel. Datele sursă trebuie prezentate sub forma unui tabel, care trebuie să aibă un titlu pentru coloane, adică un antet. În aceste scopuri, crearea unui tabel inteligent în Excel este perfectă. De asemenea, nu trebuie să conțină rânduri, coloane sau celule goale. Nu ar trebui să existe rânduri/coloane ascunse sau celule îmbinate.

Cum se creează

Acum să ne uităm la un exemplu de creare a unui tabel pivot. Să presupunem că avem date despre vânzările unui magazin de îmbrăcăminte: care vânzător, ce număr, ce articole a vândut și pentru ce sumă.

Pentru a crea un tabel pivot, selectați orice celulă din cea originală, apoi accesați fila „Inserare” și faceți clic pe butonul "Masă rotativă".

Apare următoarea casetă de dialog. În el trebuie să indicați intervalul sau numele tabelului cu datele sursă, iar aici îl puteți selecta dintr-o altă sursă. Apoi marcați cu un marcator unde să plasați raportul care urmează să fie creat. Să o facem pe o foaie nouă. Faceți clic pe OK.

O nouă foaie va fi creată în registrul de lucru Excel deschis, pe care va fi plasat tabelul pivot încă gol.

O listă de câmpuri și zone va apărea în partea dreaptă. Câmpurile sunt toate anteturile de coloană care se aflau în intervalul inițial. Folosind mouse-ul, le vom trage într-una din cele patru zone de mai jos. Astfel, formând un tabel pivot.

Câmpurile adăugate vor fi marcate cu o bifă. În zonele tabelului, le puteți schimba locurile pentru a obține aspectul care este cel mai potrivit pentru analiza datelor dvs. sursă.

Cum să aranjezi câmpurile

Acum să decidem pe ce principiu vom analiza datele. De exemplu, trebuie să aflați care vânzător a vândut ce produs în fiecare lună și pentru ce sumă.

Vom filtra datele în funcție de intervalul selectat de vânzători. Adică selectăm un vânzător, iar tabelul va afișa bunurile vândute de acesta. Faceți clic stânga pe câmpul „Vendor” și trageți-l în zona „Raport Filter”. Tabelul s-a schimbat și câmpul adăugat este acum marcat cu o bifă.

Pentru linii, selectați „Produse”. În mod similar, trageți câmpul dorit în zonă „Titluri de rând”.

Vă rugăm să rețineți că puteți introduce mai multe câmpuri într-o zonă. De exemplu, în rânduri vom selecta produse și vom indica prețul. Produsele au devenit liste derulante care afișează prețul. Dacă mai întâi specificați prețul și apoi produsele, lista derulantă va deveni preț. Aici contează ordinea câmpurilor.

Dacă am avea o coloană „Unități” în tabelul original. Apoi, acest tabel rezumativ ar arăta care vânzător, în ce lună, câte unități de mărfuri a vândut la un anumit preț.

Spre regiune „Titlurile coloanelor” Trageți câmpul „Dată”. Pentru a afișa vânzările nu pentru fiecare zi, ci, de exemplu, pe lună, faceți clic dreapta pe orice dată și selectați „Grup” din meniu.

Tabelul va lua următoarea formă.

Acum trageți câmpul „Sumă” în zona „Valori”.

După cum puteți vedea, au fost afișate doar numere, deși în intervalul inițial a fost setat formatul numeric al celulelor pentru această coloană; acesta poate fi și monetar sau financiar. În coloana „Dată”, formatul de celulă era și el adecvat – data.

Pentru a remedia acest lucru, selectați intervalul dorit de celule din rezumat și apăsați butonul din dreapta al mouse-ului. apoi selectați din meniu „Format număr”.

În fereastra următoare, selectați „Numeric”, puteți bifa caseta „Separator de grup de biți”și faceți clic pe „OK”.

Cum să lucrezi cu datele

După ce am luat în considerare toate câmpurile necesare, putem începe să lucrăm cu tabelul pivot în Excel. Selectarea unui vânzător: puteți selecta unul, mai multe sau toate simultan, bifând caseta „Selectați mai multe articole”.

De asemenea, puteți utiliza un filtru pentru rânduri și coloane. În exemplu, acestea sunt produse și luni. De exemplu, bifând casetele pentru Costum și Pantaloni, puteți afla cu cât au fost vândute de toți vânzătorii sau de un anumit vânzător.

În zona Valori, puteți configura setările pentru câmp. Exemplul afișează suma valorilor: Roma a vândut cămăși în valoare de 1.800,00 în februarie. sa vedem cate bucati are. Faceți clic stânga pe linie „Suma după câmp...”și selectați din meniu „Opțiuni câmpuri de valoare”.

În fereastra următoare, selectați „Cantitate” din listă, selectați ceea ce este potrivit special pentru cazul dvs. și faceți clic pe „OK”.

Acum, privind valorile, putem înțelege că Roma a vândut două cămăși în februarie.

Acum să facem filtrul general pentru tabel pe lună. Schimbăm zonele: în „Filtru de raport” tragem câmpul „Data”, în „Titlurile coloanelor”- "Vanzator" .

Un astfel de tabel rezumativ va arăta cât de mult și ce fel de bunuri a vândut fiecare vânzător pentru întreaga perioadă sau pentru o anumită lună.

Acordați atenție și benzii. Când selectați orice celule dintr-un tabel pivot, pe acesta apare o filă „Lucrul cu tabelele pivot” cu două subfile „Parametri” și „Designer”.

Problema cu care ne confruntăm cu toții nu este lipsa datelor; dimpotrivă - acestea sunt cantități uriașe de date! Acesta este motivul pentru care susțin utilizarea unei funcții Excel excelente - Tabele pivot pentru a rezuma și analiza datele dvs.

A vedea puterea Tabelului Pivot este la fel de simplu ca și a compara datele originale cu o versiune a Tabelului Pivot; Cu doar câteva clicuri, am putut vedea prețul mediu al unui articol în funcție de stat.

Ca profesionist în finanțe, sunt înclinat genetic să iubesc foile de calcul. Dar am descoperit și că folosesc foi de calcul pentru a-mi organiza munca creativă și independentă. Indiferent pentru ce folosiți foile de calcul, un tabel pivot vă poate ajuta să obțineți mai mult sens din datele dvs.

Vom construi această lecție conform cu a noastră, pentru a lucra mai bine cu datele. Vă voi arăta cinci dintre cele mai bune practici mele preferate pentru tabelul pivot.

Pe parcursul acestui tutorial voi folosi exemplele de date furnizate de Microsoft pe această pagină. Folosiți aceste date pentru a recrea exemplele sau pentru a testa funcțiile pe care le demonstrez.

Cum să utilizați tehnici avansate de tabel pivot în Excel (video scurt)

Îmi place să predau folosind screencast-uri, care vă oferă posibilitatea de a mă urmări cum folosesc funcțiile pas cu pas. Urmăriți scurtul videoclip de mai jos care acoperă cinci dintre funcțiile mele avansate preferate de tabel pivot Excel:

5 trucuri avansate de tabel pivot în Excel

Continuați să citiți pentru a afla cum să utilizați fiecare dintre aceste cinci funcții din lecția de mai jos, inclusiv: Secționare, Cronologie, Vizualizare tabel, Câmpuri calculate și Tabele pivot recomandate. Să începem.

1. Secțiuni

felii- Un instrument de punctare și clic pentru a rafina datele incluse într-un tabel pivot Excel. Introduceți un slicer și puteți modifica cu ușurință datele incluse în tabelul pivot.

În acest exemplu am inserat o felie pentru tip Articol. După ce dau clic pe Rucsac, tabelul pivot arată numai acest parametru în tabel.

De multe ori elaborez rapoarte de bord care vor fi folosite de alții. Adăugarea de slicere poate ajuta utilizatorul final să personalizeze raportul după bunul său plac.

Pentru a adăuga un slicer, faceți clic în tabelul pivot și găsiți fila Analiză pe panglica Excel.

Bifați mai multe casete pentru a include aceste coloane, fiecare dintre ele va fi o felie.

Ține Ctrl de pe tastatură pentru a selecta mai multe elemente de tip slicer care vor include selecții multiple dintr-o coloană ca date PivotTable.

2. Cronologie

Cronologie Acesta este un tip special de slicer folosit pentru a ajusta datele incluse ca parte a datelor PivotTable. Dacă datele dvs. conțin date, trebuie neapărat să încercați Timeline ca o modalitate de a selecta date din anumite perioade de timp.

Accesați secțiunea Analizați > Inserați cronologia pentru a adăuga o cronologie, un tip special de slicer care gestionează datele incluse într-un tabel pivot avansat Excel bazat pe dată.

Sfat: dacă această funcție nu funcționează pentru dvs., asigurați-vă că datele sursă au o dată formatată în foaia de calcul ca dată.

Pentru a adăuga o cronologie, asigurați-vă că selectați Tabelul Pivot (faceți clic în interiorul acestuia) și apoi faceți clic pe butonul Analizați > Inserați cronologia pe panglica Excel. În fereastra pop-up, selectați o coloană de dată (sau mai multe coloane) și faceți clic Bine pentru a crea o cronologie.

Faceți clic și trageți în fereastra Cronologie pentru a selecta un interval de timp specific pentru Tabelul Pivot.

Odată ce cronologia este inserată, puteți face clic și muta mânerele de pe cronologie pentru a modifica ceea ce este inclus în tabelul pivot.

Puteți schimba modul în care funcționează cronologia făcând clic pe lista derulantă din colțul din dreapta jos. Puteți modifica cronologia pentru a afișa date trimestriale sau anuale în loc de date specifice, de exemplu.

3. Vedere tabelară

Vizualizarea standard a PivotTables în Excel arată ca o cascadă; Pe măsură ce trageți mai multe câmpuri pe rânduri, Excel creează mai multe „straturi” în date.

Problema este că este dificil să se încadreze formule în PivotTables în Standard View. Dacă aveți date într-un tabel pivot, dar doriți să le vizualizați ca o foaie de calcul obișnuită, trebuie să utilizați o vizualizare de tabel pentru PivotTable.

Selectați Designer > Aspect raport > Afișare în formă tabelară pentru a lucra cu tabelul pivot ca un tabel obișnuit.

De ce ar trebui să utilizați o vizualizare de tabel? Aducerea datelor din Tabelul Pivot într-un stil tabelar clasic vă va permite să introduceți mai ușor formule de date sau să le introduceți într-un raport separat.

Utilizați Vizualizare tabel pentru a face ca tabelul pivot să arate mai mult ca linii și coloane standard.

De cele mai multe ori, prefer să folosesc vizualizarea tabelului Excel. Arată mai mult ca o foaie de calcul standard și este mai ușor să scrieți formule și să lucrați cu datele din ea. Aș putea la fel de bine să iau această vedere și să o lipesc într-o filă nouă.

4. Câmpuri calculate

Câmpurile calculate sunt o modalitate de a adăuga o coloană la un tabel pivot care nu se află în datele sursă. Puteți utiliza operațiuni matematice standard pentru a crea câmpuri complet noi cu care să lucrați. Luați două coloane existente și folosiți matematica pentru a crea altele complet noi.

Să presupunem că avem date despre vânzări într-o foaie de calcul. Avem numărul de articole vândute și prețul de vânzare pentru fiecare articol. Acesta este momentul ideal pentru a utiliza un câmp calculat pentru a calcula totalul comenzii.

Pentru a începe să lucrați cu câmpurile calculate, începeți prin a face clic în interiorul Tabelului Pivot și apoi găsiți pe Panglică Analiză. Faceți clic pe meniu Câmpuri, elemente și seturiși apoi selectați Câmp calculat.

Utilizare Analiză > Câmpuri, membri și seturi > Câmp calculat pentru a insera câmpul calculat în tabelul pivot.

În noua fereastră pop-up, începeți prin a da un nume câmpului calculat. in cazul meu o voi numi Comanda totala. Valoarea totală a comenzii este cantitatea înmulțită cu prețul fiecărei unități. Apoi am dublu clic pe primul câmp de titlu (cantitate) din lista de câmpuri din această fereastră.

Pentru a calcula costul total al comenzii, am înmulțit câmpul de preț unitar existent ( Preț unitar) pe câmpul de cantitate ( Cantitate).

După adăugarea numelui acestui câmp, voi adăuga un semn de înmulțire * , apoi faceți dublu clic pe cantitatea totală (cantitatea). Să mergem înainte și să facem clic Bine.

Acum Excel mi-a actualizat PivotTable extins și a adăugat un nou câmp calculat. De asemenea, veți vedea o listă de tabele pivot în Lista de câmpuri, astfel încât să le puteți trage oriunde în raport oricând aveți nevoie.

Dacă nu doriți să utilizați aritmetica pe două coloane, puteți introduce și propriile valori aritmetice în câmpul calculat. De exemplu, dacă aș dori să adaug pur și simplu 5% impozit pe vânzări pentru fiecare comandă, aș putea scrie următoarele în câmpul calculat:

Am înmulțit costul total al comenzii cu 1,05 pentru a calcula costul inclusiv impozitul pe vânzări; puteți utiliza valori numerice împreună cu câmpurile existente.

În principiu, câmpurile calculate pot conține orice operator matematic standard, cum ar fi adunarea, scăderea, înmulțirea și împărțirea. Utilizați aceste câmpuri calculate dacă nu doriți să actualizați datele originale.

Mergi la Inserați > pentru a încerca această caracteristică.

Această caracteristică este atât de ușor de utilizat încât este de la sine înțeles. Îl puteți folosi pentru a crea rapid tabele pivot avansate în Excel. Doar evidențiați datele, accesați fila Introduce pe panglica Excel și selectați Tabele pivot recomandate.

Fereastra pop-up conține multe opțiuni pentru crearea unui tabel pivot din datele sursă. Faceți clic pe miniaturile din partea stângă a acestei ferestre pentru a vedea opțiunile recomandate pentru PivotTable oferite de Excel.

Caracteristica PivotTable Recommendation oferă multe opțiuni pentru a vă analiza datele cu un singur clic.

Chiar dacă aceasta este o caracteristică avansată de care puțini utilizatori sunt conștienți, este, de asemenea, un instrument excelent pentru rularea tabelelor pivot. Nimic nu vă împiedică să modificați Tabelul Pivot înlocuind singur câmpurile, dar este un bun punct de plecare.

De asemenea, îmi place această caracteristică ca instrument de explorare a datelor. Dacă nu știu ce caut când încep să explorez datele, tabelele pivot Excel recomandate sunt adesea mai perspicace decât mine!

Repetați și continuați să învățați (cu și mai multe lecții Excel)

Această lecție avansată de Excel v-a ajutat să vă aprofundați în Tabelele Pivote, una dintre caracteristicile mele preferate pentru analiza și vizualizarea unei foi de calcul Excel. Folosesc tabele pivot pentru a găsi sens în seturi mari de date, astfel încât să pot lua decizii mai bune și să iau măsuri.

Aceste lecții vă vor duce abilitățile cu Excel și Tabelele Pivot la următorul nivel. Verifică-i:

• ExcelZoo are o prezentare excelentă a tehnicilor Pivot Table în articolul lor, 10 Lecții pentru stăpânirea tabelelor pivot (în engleză).
• Noi, cei de la Envato Tuts+, am acoperit tabele pivot cu un tutorial pentru începători.
• Pentru o introducere mai ușoară în Microsoft Excel, consultați seria noastră de tutoriale.

Ce mai vrei să știi despre tabelele pivot? Spune-mi ideile sau întrebările tale în comentariile de sub acest tutorial.