Viața unei persoane din lumea tehnologiei este o combinație incredibilă de numere și indicatori care necesită periodic sistematizare. Pentru a realiza aceasta din urmă, trebuie să utilizați instrumente tehnice speciale. Articolul nostru va dovedi că tabelele pivot din Excel sunt accesibile chiar și pentru manechini.

Puțină teorie

Tabelele pivot Excel (pentru manechine) sunt un tip de registru care conține o anumită bucată de date dintr-o sursă pentru analiză și este reprezentat astfel încât conexiunile logice să poată fi urmărite între ele. Baza pentru proiectarea sa este o listă specifică de valori.

Înainte de a începe lucrul, merită să pregătiți materialele necesare pentru aceasta, care pot fi compilate pentru analiză. Când creați o versiune pregătitoare, asigurați-vă că datele sunt clasificate, de exemplu, numerele nu sunt confundate cu desemnarea literei, iar toate coloanele aveau titluri.

Tabelele pivot în Excel sunt indispensabile celor care, datorită ocupației lor, se ocupă de un număr mare numere care trebuie sistematizate periodic și generate rapoarte. programul Excel vă va ajuta să calculați și să analizați un număr mare de valori, economisind timp.

Avantajele utilizării acestui tip de grupare de date:

în timpul funcționării, nu sunt necesare cunoștințe speciale de programare;
capacitatea de a combina informații din alte surse primare;
puteți completa copia de bază informații noi, ajustând ușor parametrii.

Învățarea să lucrezi cu tabele pivot în Excel nu necesită mult timp și se poate baza pe videoclipuri.

Un exemplu de creare a unui tabel pivot Excel - un algoritm pentru manechine

După ce ne-am familiarizat cu nuanțele teoretice de bază despre tabelele pivot în Excel, să trecem la aplicarea lor în practică. Pentru a începe crearea tabel pivotîn Excel 2016, 2010 sau 2007 trebuie să instalați software. De regulă, dacă utilizați programe de sistem Microsoft Office, atunci Excel este deja pe computer.

După ce l-a lansat, în fața ta se va deschide un câmp vast, împărțit într-un număr mare de celule. Tutorialul video de mai sus vă va spune mai multe despre cum să creați tabele pivot în Excel.

Folosind următorul algoritm, vom analiza în detaliu un exemplu despre cum să construim un tabel pivot în Excel.
În panoul din partea de sus a ferestrei, mergeți la fila „Inserare”, unde în colțul din stânga selectăm „Tabel pivot”.

Apoi, pe ecran se deschide o casetă de dialog în care trebuie să specificați parametrii corespunzători. În această etapă a creării tabelelor pivot în Excel, există mai multe puncte importante. Dacă înainte de a începe crearea registrului, setați pictograma cursorului pe foaie, atunci liniile goale ale ferestrei vor fi completate automat. În caz contrar, adresa intervalului de date trebuie desemnată independent.

Să aruncăm o privire mai atentă la completarea personală a elementelor din caseta de dialog.

Nu lăsăm prima linie goală, altfel programul va genera o eroare. Dacă există o sursă din care intenționați să transferați date, selectați-o în elementul „Utilizați sursa externă de date”. Sub sursă externă altul este subînțeles Caietul de lucru Excel sau un set de modele de date dintr-un SGBD.

Dați fiecărei coloane un titlu în avans.

Selectați locul în care va fi amplasat viitorul cadru cu celule. Aceasta poate fi o fereastră nouă sau această foaie, vă recomandăm să utilizați o altă foaie.

După ce am asigurat toate setările, obținem o bază gata făcută. În partea stângă se află zona în care se află baza viitorului cadru. În partea dreaptă există o fereastră de setări care vă ajută să gestionați registry.

Acum trebuie să ne dăm seama cum este construită întreaga structură. În fereastra de setări „Free Table Fields”, desemnați datele care vor fi prezente.

Întreaga structură este construită în așa fel încât datele text să joace rolul de elemente unificatoare, iar datele numerice să prezinte valori consolidate. De exemplu, să combinăm toate încasările pe departament și să aflăm câte bunuri a primit fiecare persoană. Pentru a face acest lucru, verificați două rubrici: departament și costul mărfurilor din lot.

Observați cum se află aceste date în zona inferioară a panoului de setări.

Departamentul a trecut automat în linii, iar datele numerice în valori. Dacă încercați să faceți clic pe orice coloană cu numere, acestea vor apărea în această zonă. Și o nouă coloană va fi adăugată în tabel în sine.

În acest caz, are loc însumarea. Acum putem afla din raportul nostru câte mărfuri au ajuns în fiecare departament și costul total al acestora.

Puteți regla liber lățimea coloanelor pentru a poziționa în mod optim datele. Pur și simplu extindeți lățimile coloanelor sau înălțimile rândurilor așa cum v-ați obișnuit în Excel.

Dacă nu vă place această orientare, puteți trage numele rândurilor în zona coloanelor cu mouse-ul - treceți cu mouse-ul, faceți clic butonul din stângași trageți.

În ceea ce privește calcularea rezultatelor, suma este departe de a fi singura funcție. Pentru a vedea ce mai oferă Excel, faceți clic pe numele din zona Valori și selectați cea mai recentă comandă.

În opțiunile câmpului de valoare veți găsi multe opțiuni de analiză.

Pentru fiecare valoare vă puteți selecta propria funcție. De exemplu, să adăugăm câmpul „Preț” și să găsim prețul maxim al produsului în fiecare departament. De fapt, vom afla cât costă cel mai scump.

Acum vedem că departamentul „Accesorii” a primit mărfuri în valoare de 267.660 de ruble, în timp ce cel mai scump are un preț de 2.700 de ruble.
Zona „Filtre” vă permite să setați criterii pentru selectarea înregistrărilor. Să adăugăm câmpul „Data primirii” pur și simplu bifând caseta de lângă acesta.

În zilele noastre, un tabel pivot în Excel pare ciudat dacă trebuie să analizăm după dată. Prin urmare, haideți să mutăm data de pe rânduri la filtre - doar trageți și plasați așa cum este indicat mai sus.

Rezultatul acestor acțiuni a fost apariția unui alt câmp deasupra. Pentru a selecta o dată, faceți clic pe săgeata de lângă cuvântul „Toate”.

Acum putem selecta o anumită zi pentru a deschide lista, faceți clic pe triunghiul din colțul din dreapta.

De asemenea, puteți selecta valori pentru departament.

Debifați pe cele care nu vă interesează și veți primi doar informațiile de care aveți nevoie.

În timp ce lucrați, este posibil să întâlniți un mesaj precum „Nume tabel pivot Excel invalid”. Aceasta înseamnă că primul rând al intervalului din care încearcă să extragă informații rămâne cu celule goale. Pentru a rezolva această problemă, trebuie să completați spațiile coloanelor.

Actualizează datele într-un tabel pivot în Excel

O întrebare importantă este cum să creați și să actualizați un tabel pivot în Excel 2010 sau altă versiune. Acest lucru este relevant atunci când veți adăuga date noi. Dacă actualizarea va avea loc doar pentru o coloană, atunci trebuie să faceți clic dreapta oriunde pe ea. În fereastra care apare, faceți clic pe „Actualizare”.

Dacă o astfel de acțiune trebuie efectuată cu mai multe coloane și rânduri simultan, atunci selectați orice zonă și panoul superior Deschideți fila „Analiză” și faceți clic pe pictograma „Actualizare”. Apoi, alegeți acțiunea dorită.

Dacă nu aveți nevoie de un tabel pivot în Excel, atunci ar trebui să vă dați seama cum să-l ștergeți. Nu va fi greu. Selectați toate componentele manual sau folosind comanda rapidă de la tastatură „CTRL+A”. Apoi, apăsați tasta „ȘTERGERE” și câmpul va fi șters.

Cum să adăugați o coloană sau un tabel la un tabel pivot Excel

Pentru a adăuga o coloană suplimentară, trebuie să o adăugați la datele sursă și să extindeți intervalul pentru registrul nostru.

Accesați fila Analiză și deschideți sursa de date.

Excel va sugera totul în sine.

Actualizați și veți primi o nouă listă de câmpuri în zona de setări.

Puteți adăuga un tabel numai dacă îl „lipești” cu cel original. Puteți înlocui un interval într-unul existent, dar nu puteți adăuga un alt interval din mers. Dar puteți crea un nou tabel pivot bazat pe mai multe originale, chiar și situate pe foi diferite.

Cum să faci un tabel pivot în Excel din mai multe foi

Pentru a face acest lucru, avem nevoie de Pivot Table Wizard. Să-l adăugăm la panou acces rapid(partea superioară a ferestrei este în stânga). Faceți clic pe săgeata derulantă și selectați „Mai multe comenzi”.

Selectați toate comenzile.

Și găsiți expertul Excel Pivot Table, faceți clic pe el, apoi pe „Adăugați” și OK.

Pictograma va apărea în partea de sus.

Ar trebui să aveți două tabele cu câmpuri identice pe foi diferite. Avem date despre încasările în departamente pentru lunile mai și iunie. Faceți clic pe comanda rapidă PivotTable Wizard și selectați Range Consolidation.

Avem nevoie de mai multe domenii, nu doar de unul.

În pasul următor, selectați primul interval și faceți clic pe butonul „Adăugați”. Apoi treceți la o altă foaie (faceți clic pe numele ei de mai jos) și „Adăugați” din nou. Veți avea două intervale create.

Nu trebuie să selectați întregul tabel. Avem nevoie de informații despre chitanțele departamentului, așa că am evidențiat un interval care începe cu coloana Departament.
Dați fiecăruia un nume. Faceți clic pe cercul 1, apoi introduceți „mai” în câmp, faceți clic pe cercul 2 și introduceți „iunie” în câmpul 2. Nu uitați să schimbați intervalele din zonă. Trebuie evidențiat cel pe care îl numim.

Faceți clic pe „Următorul” și creați pe o foaie nouă.

După ce faceți clic pe „Finish”, vom obține rezultatul. Acesta este un tabel multidimensional, deci este destul de dificil de gestionat. De aceea am ales un interval mai mic, pentru a nu ne încurca în măsurători.

Vă rugăm să rețineți că nu mai avem nume clare de câmpuri. Acestea pot fi scoase făcând clic pe elementele din zona de sus.

Debifând sau bifând casetele, ajustați valorile pe care doriți să le vedeți. De asemenea, este incomod ca calculul să fie același pentru toate valorile.

După cum puteți vedea, avem o singură valoare în zona corespunzătoare.

Modificarea structurii raportului

Am examinat pas cu pas un exemplu despre cum să creați un tabel pivot în Exce și vă vom spune cum să obțineți un alt tip de date în continuare. Pentru a face acest lucru, vom schimba aspectul raportului. După ce ați plasat cursorul pe orice celulă, accesați fila „Designer”, apoi „Aspect raport”.

Veți avea de ales dintre trei tipuri pentru structurarea informațiilor:

Forma condensată

Acest tip de program este aplicat automat. Datele nu sunt extinse, așa că practic nu este nevoie să defilați prin imagini. Puteți economisi spațiu pe semnături și lăsați-l pentru numere.

Forma structurata

Toți indicatorii sunt prezentați ierarhic: de la mic la mare.

Forma tabelară

Informațiile sunt prezentate sub masca unui registru. Acest lucru facilitează transferul celulelor pe foi noi.

După ce ați ales un aspect adecvat, asigurați ajustările efectuate.

Așadar, v-am spus cum să creați câmpuri de tabel pivot în MS Excel 2016 (în 2007, 2010, procedați prin analogie). Sperăm că aceste informații vă vor ajuta să analizați rapid datele dvs. consolidate.

O zi bună!

Această postare prezintă o colecție de instrumente simple și elegante pentru lucrul cu tabele pivot în Excel. Ceea ce se numește tips & tricks în engleză. Fă-ți puțin timp și citește sfaturile de aici. Cine știe, poate vei găsi în sfârșit răspunsul la o întrebare care te deranjează de mult?

Sfat 1: actualizați automat tabelele pivot

Uneori doriți ca tabelele pivot să se actualizeze automat. Să presupunem că ați creat un tabel pivot pentru un manager. Este puțin probabil să îl poți actualiza în mod regulat, cu excepția cazului în care managerul îți permite accesul la laptopul său. Poate fi activat actualizare automată tabel pivot, care va fi executat de fiecare dată când registrul de lucru este deschis:

Faceți clic dreapta pe PivotTable și în meniul contextual selectați elementul Opțiuni pentru tabel pivot.
În caseta de dialog care apare Opțiuni pentru tabel pivot selectați fila Date.
Bifați caseta Actualizează la deschiderea fișierului.

Orez. 1. Activați opțiunea Actualizează la deschiderea fișierului

Caseta de selectare Actualizează la deschiderea fișierului trebuie setat pentru fiecare tabel pivot separat.

Descărcați nota în sau format, exemple în format (fișierul conține cod VBA).

Sfat 2: actualizați simultan toate tabelele pivot ale caietului de lucru

Dacă registrul de lucru conține mai multe tabele pivot, actualizarea lor simultană poate fi problematică. Există mai multe modalități de a depăși aceste dificultăți:

Metoda 1: Puteți selecta pentru fiecare tabel pivot inclus în registrul de lucru, o setare care setează actualizări automate atunci când deschideți un registru de lucru (pentru mai multe detalii, consultați Sfatul 1).

Metoda 3: Utilizați codul VBA pentru a actualiza toate tabelele pivot dintr-un registru de lucru la cerere. Această abordare implică utilizarea metodei RefreshAll a obiectului Workbook. Pentru a utiliza această tehnică, creați un nou modul și introduceți următorul cod:

Sub Refresh_All()

ThisWorkbook.RefreshAll

Sfat 3: Sortați elementele de date în ordine aleatorie

În fig. Figura 2 arată ordinea implicită în care regiunile sunt afișate în PivotTable. Regiunile sunt sortate în ordine alfabetică: Vest, Nord, Midwest și Sud. Dacă politicile dvs. corporative necesită ca regiunea Vest să fie afișată mai întâi, urmată de regiunile Midwest, Nord și Sud, efectuați o sortare manuală. Pur și simplu introduceți Midwest în celula C4 și apăsați tasta Intră. Ordinea de sortare a regiunilor se va schimba.

Sfat 4: convertiți un tabel pivot în valori codificate hard

Scopul creării unui tabel pivot este de a rezuma și afișa datele într-un format adecvat. Datele sursă pentru tabelul pivot sunt stocate separat, ceea ce introduce o suprasarcină. Convertirea unui tabel pivot în valori vă permite să utilizați rezultatele acestuia fără a accesa datele originale sau memoria cache a tabelului pivot. Modul în care convertiți un tabel pivot depinde dacă întregul tabel este afectat sau doar o parte a acestuia.

Pentru a converti o parte dintr-un tabel pivot, urmați acești pași:

Selectați datele din tabelul Pivot de copiat, faceți clic dreapta și selectați din meniul contextual Copie(sau tastați Ctrl+C pe tastatură).
Faceți clic dreapta oriunde pe foaia de lucru și selectați comanda din meniul contextual Introduce(sau tastați Ctrl+V).

Dacă trebuie să convertiți întregul tabel pivot, urmați acești pași:

Selectați întregul tabel pivot, faceți clic dreapta și selectați din meniul contextual Copie. Dacă tabelul pivot nu conține zona FILTRE, atunci puteți utiliza comanda rapidă de la tastatură Ctrl+Shift+* pentru a selecta zona tabelului pivot.
Faceți clic dreapta oriunde pe foaie și selectați opțiunea din meniul contextual Inserție specială .
Selectați o opțiune Valoriși faceți clic Bine.

Este o idee bună să eliminați subtotalurile înainte de a converti un tabel pivot, deoarece acestea nu sunt cu adevărat necesare într-un set de date autonom. Pentru a șterge toate subtotalurile, accesați meniul Designer -> Subtotals -> Nu afișați subtotaluri. Pentru a elimina anumite subtotaluri, faceți clic dreapta pe celula în care sunt calculate subtotalurile. Selectați elementul din meniul contextual Opțiuni de câmpși în caseta de dialog Opțiuni de câmp in sectiune Rezultate selectați butonul radio Nu. După ce faceți clic pe butonul Bine subtotalurile vor fi șterse.

Sfat 5: completați celulele goale în câmpurile RÂND

După conversia unui tabel pivot, foaia de lucru afișează nu numai valorile, ci și întreaga structură de date a tabelului pivot. De exemplu, datele prezentate în fig. 3 au fost obținute pe baza unui tabel pivot cu un aspect sub formă tabelară.

Orez. 3. Utilizarea acestui tabel pivot convertit fără a completa celulele goale din partea stângă este problematică

Vă rugăm să rețineți că câmpurile RegiuneŞi Piața de vânzări păstrează aceeași structură de rând care este prezentă atunci când datele sunt în zona RÂNDURI a tabelului pivot. În Excel 2013 există cale rapidă Completarea celulelor din zona RÂND cu valori. Faceți clic în zona tabelului pivot și apoi navigați prin meniu Constructor -> Aspect raport -> (Fig. 4). Puteți converti apoi tabelul pivot în valori, ceea ce vă va oferi un tabel de date fără spații.

Orez. 4. După folosirea comenzii Repetați toate etichetele elementelor toate celulele goale sunt umplute

Sfat 6: Clasificarea câmpurilor numerice într-un tabel pivot

În procesul de sortare și ierarhizare a câmpurilor care conțin un număr mare de elemente de date, nu este întotdeauna ușor să se determine rangul numeric al elementului de date analizat. Mai mult, dacă tabelul pivot este convertit în valori, atribuirea unui rang numeric fiecărui element de date, afișat într-un câmp întreg, va facilita foarte mult analiza setului de date generat. Deschideți un tabel pivot similar cu cel prezentat în Fig. 5. Vă rugăm să rețineți că același indicator - Suma pe câmp Volumul vânzărilor- este afișat de două ori. Faceți clic dreapta pe a doua instanță a indicatorului și selectați comanda din meniul contextual Calcule suplimentare -> Sortați de la cel mai mare la cel mai mic(Fig. 6.)

După crearea unui rang, puteți personaliza etichetele câmpurilor și formatarea (Figura 14.9). Rezultatul va fi un raport frumos clasat.

Sfat 7: reduceți dimensiunea unui raport PivotTable

Când generați un raport PivotTable, Excel creează un instantaneu al datelor și îl stochează în memoria cache PivotTable. Cache-ul tabelului pivot este zonă specială memorie în care este stocată o copie a sursei de date pentru a accelera accesul. Cu alte cuvinte, Excel creează o copie a datelor și apoi o stochează într-un cache asociat cu registrul de lucru. Cache-ul tabelului pivot oferă optimizarea fluxului de lucru. Orice modificări aduse tabelului pivot, cum ar fi schimbarea locației câmpurilor, adăugarea de noi câmpuri sau ascunderea oricăror elemente, sunt finalizate mai rapid, iar cerințele pentru resursele de sistem sunt mult mai modeste. Principalul dezavantaj al cache-ului tabelului pivot este că, în esență, dublează dimensiunea fișierului registrului de lucru de fiecare dată când creați un tabel pivot de la zero.

Ștergeți datele originale. Dacă un registru de lucru conține un set de date sursă și un tabel pivot, dimensiunea fișierului acestuia se dublează. Prin urmare, puteți șterge în siguranță datele originale, iar acest lucru nu va afecta deloc funcționalitatea tabelului pivot. După ștergerea datelor originale, asigurați-vă că salvați o versiune comprimată a fișierului registrului de lucru. După eliminarea datelor originale, puteți utiliza PivotTable în modul normal. Singura problemă este că tabelul pivot nu poate fi actualizat din cauza lipsei datelor sursă. Dacă aveți nevoie de datele originale, faceți dublu clic la intersecția rândului și coloanei din zona totalurilor (în Fig. 7 aceasta este celula B18). Acest lucru face ca Excel să arunce conținutul cache-ului PivotTable într-o nouă foaie de lucru.

Sfat 8: creați un interval de date care se extinde automat

Probabil că ați întâlnit situații de mai multe ori când a trebuit să actualizați zilnic rapoartele din tabelul pivot. Necesitatea acestui lucru apare cel mai adesea atunci când înregistrările noi sunt adăugate în mod constant la sursa de date. În astfel de cazuri, va trebui să redefiniți intervalul utilizat anterior înainte ca noi înregistrări să fie adăugate la noul PivotTable. Redefinirea intervalului de date inițial pentru un PivotTable nu este dificilă, dar atunci când trebuie să o faceți frecvent, poate deveni destul de plictisitor.

Soluția la această problemă este să convertiți intervalul de date inițial într-un tabel înainte de a crea tabelul pivot. Cu tabelele Excel, puteți crea un interval numit care se poate extinde sau contracta automat în funcție de cantitatea de date pe care o conține. De asemenea, puteți asocia orice componentă, diagramă, tabel pivot sau formulă cu un interval, permițându-vă să urmăriți modificările din setul dvs. de date.

Pentru a implementa tehnica descrisă, selectați datele sursă, apoi faceți clic pe pictograma tabel aflată în filă Introduce(Fig. 8) sau apăsați Ctrl+T (T engleză). Clic Bineîn fereastra care se deschide. Rețineți că, deși nu trebuie să suprascrieți intervalul de date sursă din tabelul Pivot, va trebui totuși să faceți clic pe butonul atunci când adăugați date sursă la intervalul din PivotTable. Actualizare.

Sfat 9: comparați tabele obișnuite folosind un PivotTable

Dacă efectuați o analiză comparativă a două tabele diferite, este convenabil să utilizați un tabel pivot, care va economisi timp semnificativ. Să presupunem că există două tabele care afișează informații despre clienți pentru 2011 și 2012 (Fig. 9). Dimensiunile mici ale acestor tabele sunt oferite aici doar ca exemple. În practică, se folosesc tabele mult mai mari.

Procesul de comparare creează un tabel din care este creat un tabel pivot. Asigurați-vă că aveți o modalitate de a eticheta datele asociate cu aceste tabele. În exemplul luat în considerare, coloana este folosită pentru aceasta An fiscal(Fig. 10). După ce combinați două tabele, utilizați setul de date combinat rezultat pentru a crea un nou tabel pivot. Formatați tabelul Pivot pentru a utiliza zona Coloane Tabel Pivot ca etichetă de tabel (un identificator care indică originea tabelului). După cum se arată în Fig. 11, anii sunt în zona coloanei, iar informațiile despre clienți sunt în zona rândurilor. Zona de date conține volumele de vânzări pentru fiecare client.

Sfat 10: filtrați automat un tabel pivot

După cum știți, filtrele automate nu pot fi utilizate în tabelele pivot. Cu toate acestea, există un truc pentru a activa filtrele automate într-un tabel pivot. Principiul utilizării acestei tehnici este să plasați indicatorul mouse-ului în dreapta ultimului titlu al tabelului pivot (celula D3 din Figura 12), apoi să mergeți la panglică și să selectați comanda Date -> Filtra. De acum înainte, în tabelul pivot apare un filtru automat! De exemplu, puteți selecta toți clienții cu rate de tranzacție peste medie. Filtrele automate adaugă un nivel suplimentar de analiză la un tabel pivot.

Sfat 11: convertiți seturile de date afișate în PivotTables

Cel mai bun aspect pentru datele sursă convertite într-un PivotTable este un aspect tabelar. Acest tip de layout are următoarele caracteristici: nu există rânduri sau coloane goale, fiecare coloană are un titlu, fiecare câmp are o valoare corespunzătoare în fiecare rând, iar coloanele nu conțin grupuri repetate de date. În practică, întâlnim adesea seturi de date asemănătoare cu cele prezentate în Fig. 13. După cum puteți vedea, denumirile lunii apar într-un rând de-a lungul marginii de sus a tabelului, servind drept etichete de coloană și date reale. Într-un tabel pivot creat dintr-un tabel ca acesta, aceasta ar duce la gestionarea a 12 câmpuri, fiecare reprezentând o lună diferită.

Pentru a rezolva această problemă, puteți utiliza un tabel pivot cu mai multe intervale consolidate ca pas intermediar (pentru mai multe detalii, consultați). Pentru a converti un set de date care are un stil de matrice într-un set de date mai potrivit pentru crearea de tabele pivot, urmați acești pași:

Pasul 1: Combină toate câmpurile fără coloane într-o singură coloană. Pentru a crea tabele pivot cu mai multe intervale consolidate, trebuie să creați o singură coloană cu dimensiune. În acest exemplu, tot ceea ce nu aparține câmpului lună este considerat o dimensiune. Prin urmare câmpurile Piața de vânzăriŞi Descrierea serviciului ar trebui să fie combinate într-o singură coloană. Pentru a combina câmpuri într-o singură coloană, introduceți pur și simplu o formulă care concatenează cele două câmpuri folosind un punct și virgulă ca delimitator. Dați un nume noii coloane. Formula introdusă este afișată în bara de formule (Fig. 14).

Orez. 14. Rezultatul concatenării coloanelor Piața de vânzăriŞi Descrierea serviciului

După crearea coloanei concatenate, convertiți formulele în valori. Pentru a face acest lucru, selectați coloana nou creată, apăsați Ctrl+C și apoi executați comanda Introduce -> Inserție specială -> Valori. Acum puteți elimina coloanele Piața de vânzăriŞi Descrierea serviciului(Fig. 15).

Orez. 15. Coloane eliminate Piața de vânzăriŞi Descrierea serviciului

Pasul 2: creați un tabel pivot cu mai multe intervale de consolidare. Acum trebuie să apelați un apel familiar pentru mulți utilizatori versiunile anterioare Excel maestru de tabele pivot și diagrame. Pentru a apela acest expert, apăsați combinația de taste Alt+D+P. Din păcate, această combinație de taste este destinată versiunii în limba engleză a Excel 2013. În versiunea rusă, corespunde combinației de taste Alt+D+N. Dar din motive necunoscute de mine nu merge. Cu toate acestea, puteți afișa vechiul expert Pivot Table pe bara de instrumente de acces rapid, a se vedea. După pornirea expertului, selectați comutatorul În mai multe intervale de consolidare. Clic Următorul. Setați comutatorul Creați câmpuri de paginăși faceți clic Următorul. Determinați intervalul de lucru și faceți clic Gata(vezi detalii). Veți crea un tabel pivot (Figura 16).

Pasul 3: Faceți dublu clic pe intersecția rândului și coloanei din rândul total general.În această etapă, veți avea la dispoziție un tabel rezumativ (Fig. 16), care include mai multe intervale de consolidare, care este practic inutil. Selectați celula de la intersecția rândului și a coloanei de total general și faceți dublu clic pe ea (în exemplul nostru, celula N88). Veți primi o nouă foaie, a cărei structură seamănă cu structura prezentată în Fig. 17. Această fișă este de fapt o versiune transpusă a datelor originale.

Pasul 4. Împărțirea coloanei Rând în câmpuri separate. Rămâne să împărțim coloana Liniaîn câmpuri separate (întoarcerea la structura originală). Adăugați o coloană goală imediat după coloană Linia. Evidențiați Coloana A și apoi accesați fila Panglică Dateși faceți clic pe butonul Textul coloanei. Pe ecran va apărea o casetă de dialog Expert pentru distribuirea textelor în coloane. În primul pas, selectați butonul radio Cu delimitatoriși faceți clic pe butonul Următorul. În pasul următor, selectați butonul radio punct şi virgulăși faceți clic Gata. Formatați textul, adăugați un titlu și transformați datele originale într-un tabel apăsând Ctrl+T (Fig. 18).

Orez. 18. Acest set de date este ideal pentru crearea unui tabel pivot (comparați cu Figura 13)

Sfat 12: Includerea a două formate de numere într-un tabel pivot

Acum să luăm în considerare o situație în care un set de date normalizat face dificilă construirea unui tabel pivot convenabil pentru analiză. Un exemplu este prezentat în Fig. 19, care include doi indicatori diferiți pentru fiecare piață de vânzare. Notați coloana D, care identifică indicatorul.

Deși acest tabel poate fi un exemplu de formatare bună, nu este deloc grozav. Vă rugăm să rețineți că unele valori trebuie afișate în format numeric, în timp ce altele trebuie să fie afișate în format procentual. Dar în baza de date originală câmpul Sens este de tip Double. Când creați un tabel pivot dintr-un set de date, nu puteți atribui două formate de numere diferite aceluiași câmp Sens. Aici se aplică o regulă simplă: un câmp corespunde unui format de număr. Încercarea de a atribui un format de număr unui câmp căruia i-a fost atribuit un format procentual va face ca valorile procentuale să devină numere obișnuite care se termină cu un semn de procente (Figura 20).

Pentru a rezolva această problemă, se folosește un format de număr personalizat care formatează orice valoare mai mare de 1,5 ca număr. Dacă valoarea este mai mică de 1,5, este formatată ca procent. În caseta de dialog Format de celule selectați fila (toate formatele) iar în câmp Tip introduceți următorul șir de format (Fig. 21): [>=1.5]$# ##0; [<1,5]0,0%

Orez. 21. Aplicați un format de număr personalizat în care orice numere mai mici de 1,5 sunt formatate ca procente

Rezultatul obtinut este prezentat in Fig. 22. După cum puteți vedea, fiecare indicator este acum formatat corect. Desigur, rețeta dată în acest sfat nu este universală. Mai degrabă, indică o direcție în care să experimentezi.

Sfat 13: Creați o distribuție de frecvență pentru un tabel pivot

Dacă ați creat vreodată distribuții de frecvență folosind funcția Excel Frecvenţă, atunci probabil știți că aceasta este o sarcină foarte dificilă. Mai mult, după modificarea intervalelor de date, totul trebuie să o ia de la capăt. În această secțiune, veți învăța cum să creați distribuții simple de frecvență folosind un tabel pivot simplu. Mai întâi, creați un tabel pivot cu date în zona de rânduri. Atenție la fig. 23, unde în zona liniei se află un câmp Volum vânzări.

Faceți clic dreapta pe orice valoare din zona de rânduri și selectați opțiunea din meniul contextual Grup. În caseta de dialog Gruparea(Fig. 24) determinați valorile parametrilor care determină începutul, sfârșitul și pasul distribuției de frecvență. Faceți clic pe OK.

Orez. 24. În caseta de dialog Gruparea configurați parametrii de distribuție a frecvenței

Dacă adăugați un câmp la un tabel pivot Client(Fig. 25), obținem distribuția frecvenței tranzacțiilor clienților în raport cu mărimea comenzilor (în dolari).

Orez. 25. Acum aveți la dispoziție distribuția tranzacțiilor clienților în funcție de mărimile comenzii (în dolari)

Avantajul acestei tehnici este că un filtru de raport PivotTable poate fi utilizat pentru a filtra interactiv datele pe baza altor coloane, cum ar fi RegiuneŞi Piața de vânzări. Utilizatorul are, de asemenea, capacitatea de a configura rapid intervalele de distribuție a frecvenței făcând clic dreapta pe orice număr din zona de rânduri și apoi selectând o opțiune Grup. Pentru claritatea prezentării, se poate adăuga o diagramă rezumativă (Fig. 26).

Sfat 14: utilizați un tabel pivot pentru a distribui un set de date pe foile dintr-un registru de lucru

Analiștii trebuie adesea să creeze rapoarte PivotTable diferite pentru fiecare regiune, piață, manager și așa mai departe. Finalizarea acestei sarcini implică de obicei un proces îndelungat de copiere a Tabelului Pivot într-o nouă foaie de lucru și apoi schimbarea câmpului de filtru pentru a reflecta regiunea și managerul corespunzătoare. Acest proces este efectuat manual și repetat pentru fiecare analiză. Dar, în general, puteți delega crearea de tabele pivot individuale în Excel. Ca urmare a aplicării parametrului Un tabel pivot separat este creat automat pentru fiecare element din zona câmpurilor de filtrare. Pentru a utiliza această caracteristică, pur și simplu creați un PivotTable care include un câmp de filtru (Figura 27). Plasați cursorul oriunde în PivotTable și pe filă Analizăîntr-un grup de echipe Tabel pivot faceți clic pe lista derulantă Opțiuni(Fig. 28). Apoi faceți clic pe butonul Afișați paginile de filtrare a raportului.

Orez. 28. Faceți clic pe butonul Afișați paginile de filtrare a raportului

În caseta de dialog care apare (Fig. 29), puteți selecta un câmp de filtru pentru care vor fi create tabele pivot separate. Selectați câmpul de filtru corespunzător și faceți clic Bine.

Orez. 29. Caseta de dialog Afișarea paginilor de filtrare a raportului

Pentru fiecare element al câmpului de filtrare va fi creat un tabel pivot, plasat pe o foaie separată (Fig. 30). Rețineți că etichetele foii sunt denumite la fel ca și elementele câmpului de filtrare. Vă rugăm să rețineți că parametrul Afișați paginile de filtrare poate fi aplicat la câmpurile de filtrare unul câte unul.

Sfat 15: utilizați un tabel pivot pentru a distribui un set de date în registrele de lucru individuale

În sfatul 14 am folosit o opțiune specială pentru a separa tabele pivot pe piețele de vânzare pe diferite foi ale registrului de lucru. Dacă trebuie să împărțiți date sursă pentru diferite piețe de vânzare în cărți separate, puteți folosi un mic cod VBA. Pentru a începe, plasați câmpul pe care doriți să îl filtrați în zona câmpurilor de filtrare. Așezați câmpul Volumul vânzărilorîn intervalul de valori (Fig. 31). Următorul cod VBA selectează pe rând fiecare element FILTER și apelează funcția Afișați detalii, creând o nouă fișă de date. Această foaie este apoi salvată într-un registru de lucru nou

CodVBA.

Sub ExplodeTable()

Dim PvtItem ca PivotItem

Dim PvtTable ca PivotTable

Dim strfield ca PivotField

„Schimbarea variabilelor conform scriptului

ConststrFieldName = „Piața de vânzări” ‘<—Изменение имени поля

Const strTriggerRange = „A4” ‘<—Изменение диапазона триггера

„Schimbați numele tabelului pivot (dacă este necesar)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") ‘<—Изменение названия сводной

„Buclă prin fiecare element al câmpului selectat

Pentru fiecare PvtItem din PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = Adevărat

„Numirea unei foi temporare

ActiveSheet.Name = „TempSheet”

„Copierea datelor într-un registru de lucru nou și ștergerea unei foi temporare

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = Fals

ActiveWorkbook.SaveAs_

Nume fișier:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook.Închidere

Sheets(" Tempsheet ").Delete

Application.DisplayAlerts = Adevărat

Introduceți acest cod într-un nou modul VBA. Verificați valorile următoarelor constante și variabile și modificați-le dacă este necesar:

Const strFieldName. Numele câmpului folosit pentru a separa datele. Cu alte cuvinte, este un câmp care este plasat în zona paginilor filtru/tabel pivot.
Const strTriggerRange. O celulă de declanșare care stochează un singur număr din zona de date PivotTable. În cazul nostru, celula de declanșare este A4 (vezi Fig. 31).

Ca urmare a executării codului VBA, datele pentru fiecare piață de vânzare vor fi salvate într-un registru de lucru separat.

Nota a fost scrisă pe baza cărții lui Jelen, Alexander. . Capitolul 14.

Data: 16 martie 2017 Categorie:

Salutare prieteni. În ultimul articol, am configurat câmpurile și am discutat despre problemele de filtrare, sortare și proiectare a datelor. Este timpul să aprofundăm puțin în activitatea instrumentului pentru a obține rapoarte și mai moderne și avansate, precum și ușurința în utilizare a tabelelor pivot în munca de zi cu zi. Să începem!

Cum să copiați un tabel pivot

Recent, unul dintre cititori m-a întrebat: „Cum pot copia un tabel pivot dintr-o foaie în alta?” Am decis să subliniez această întrebare aici pentru că nu este prima dată când o aud. Un tabel pivot nu este un interval obișnuit de date; este o zonă de celule care conține rezultatele instrumentului. Prin urmare, nu puteți să copiați pur și simplu tabelul pivot într-o altă locație.

Dar puteți crea oricând un alt tabel similar într-o locație nouă. Dacă sunteți interesat doar de rezultatul funcționării instrumentului și nu veți face nicio manipulare legată de funcționarea tabelelor pivot, puteți copia datele în tabelul pivot și le lipiți într-un loc nou, ca valori, și nu ca un duplicat al tabelului pivot. Pentru a face acest lucru, urmați acești pași:

date dintr-un tabel pivot
una dintre metodele cunoscute. De exemplu, apăsați Ctrl+C pe tastatură
Plasați cursorul în celula în care doriți să fie localizat colțul din stânga sus al intervalului lipit
Executați pe Ribbon Acasă – Clipboard – Lipire – Valori.

Ca urmare, programul va insera valorile conținute în intervalul selectat. Apoi puteți lucra cu acest tabel ca și cu datele obișnuite de pe o foaie.

Cum să obțineți date detaliate dintr-un tabel pivot

Uneori trebuie să obțineți datele sursă pe baza cărora se calculează acest sau acel rând din tabelul pivot. Adică, faceți o selecție de date care satisface un anumit criteriu din tabelul pivot.

Să revenim la exemplul din postarea anterioară. Acolo aveam un tabel zilnic al vânzărilor pe grupuri de produse, regiuni și manageri. Am construit un tabel pivot în care am calculat valoarea vânzărilor grupate pe manageri și grupuri de produse.

Să ne imaginăm că, dintr-un motiv oarecare, eram interesați de vânzările zilnice de hardware ale lui Roman. Selectați celula de la intersecția rândului „Novel” și coloanei „Hardware”. Faceți clic dreapta pe el și selectați „Afișați detalii”. Programul va crea o foaie nouă și va afișa pe ea o selecție din tabelul original, de exemplu. toate vânzările de feronerie romană.

Sau puteți pur și simplu să faceți dublu clic pe valoarea dorită. Aceasta este o modalitate alternativă și mai simplă.

Vă rugăm să rețineți că rezultatul va fi prezentat ca , mai degrabă decât intervalul obișnuit. Puteți aprecia încă o dată confortul unei astfel de structuri de date și puteți profita de toate avantajele acesteia.

Calcule suplimentare în PivotTable

Dacă funcțiile de calcul standard dintr-un tabel pivot (suma, cantitate, abatere etc.) nu sunt suficiente pentru dvs., instrumentul are capacități de calcul suplimentare. În primul rând, faceți clic dreapta pe orice celulă din coloana de calcul și selectați Mai multe calcule. Vor fi multe opțiuni interesante în lista de calcule. De exemplu. Dorim să știm ce cotă din vânzări din cantitatea totală este realizată de fiecare manager din exemplu. Să efectuăm calcule suplimentare în coloana în care sunt rezumate vânzările. Ca parametru, selectați „% din cantitate pe coloană”. Uite, în 2 clicuri am trecut de la valori naturale la procente, acum este mai ușor să compari indicatorii, sau să-i ascunzi atunci când nu este recomandat să reflectezi anumite cifre în unități monetare în raport.

Priviți toate opțiunile de calcul posibile și probabil veți găsi ceva pe care să îl puteți utiliza chiar acum.

Câmpuri și obiecte calculate

Un alt mod interesant de a adăuga informații la un tabel este prin câmpuri și obiecte calculate. Sunt folosite atunci când trebuie să adăugați rânduri sau coloane suplimentare la un tabel. Din păcate, ele nu pot conține și ne-am obișnuit, dar este mai bine decât nimic.

Câmpuri calculate

Aceste obiecte sunt necesare pentru a insera coloane noi în tabel fără a le introduce în matricea de date originală. De exemplu, avem valoarea vânzărilor managerilor și numărul de cecuri. Să calculăm factura medie într-o coloană separată.

Efectuăm următoarea secvență de acțiuni:

Plasați cursorul într-una dintre celulele care conțin valori
Pe panglică faceți clic pe: Lucrul cu tabele pivot – Analiză – Câmpuri, articole, seturi – Câmp calculat
În fereastra care se deschide, în câmpul „Nume”, scrieți „Factură medie”
Acum introducem formula, trebuie să împărțim valoarea vânzărilor la numărul de chitanțe. În lista de câmpuri, faceți dublu clic pe „Suma vânzări”, scrieți semnul diviziunii „/” pe tastatură și faceți dublu clic pe „Numărul de chitanțe. Formula ar trebui să arate astfel:

Faceți clic pe OK și vedeți ce se întâmplă.

Acum avem o altă coloană, care calculează suma medie din cec pentru fiecare manager. Numele său apare în zona „Valori” din panoul de setări PivotTable.

Obiecte calculate

Obiectele calculate oferă o funcționalitate similară. Dar ei introduc rânduri, nu coloane, în tabel. De exemplu, avem valoarea vânzărilor managerilor și ne interesează cât va fi TVA (18%) la aceste vânzări și suma totală inclusiv TVA. Creați un obiect calculat:

Plasați cursorul pe orice rând din prima coloană sau pe orice coloană a primului rând
Faceți clic pe panglică: Lucrul cu tabele pivot – Analiză – Câmpuri, articole, seturi – Obiect calculat. Se va deschide o fereastră de inserare:

În câmpul „Nume” scrieți „TVA”, în lista „Câmpuri” selectați „Manager”
Făcând dublu clic pe numele fiecărui manager, notăm formula: =(Alexey+Anna +Victor +Victoria +Vitaly +Denis +Egor +Roman +Svetlana)*0.18
Eliminați câmpurile inutile și faceți clic pe OK. Primim un alt câmp în care se va calcula TVA. Valoarea din acest câmp va fi adăugată la total.

Gruparea datelor într-un tabel pivot

Când tabelul este gata, puteți face grupări suplimentare ale datelor. Acest lucru îi va îmbunătăți lizibilitatea și flexibilitatea. Voi evidenția câteva instrumente convenabile de grupare.

Gruparea cu pași

Dacă trebuie să combinați datele numerice în mai multe intervale pentru a minimiza raportul, utilizați acest instrument. De exemplu, tabelul nostru conține date privind vânzările pe zi. Trebuie să grupăm aceste date pe lună. Acest lucru se poate face foarte simplu într-un tabel pivot. Să o facem pas cu pas:

Creăm un tabel pivot cu zile în rânduri și vânzări în valori. Dacă nu știți cum să creați un tabel pivot, citiți mai întâi;
Faceți clic dreapta pe oricare dintre datele din tabelul pivot și selectați „Grup” din meniul contextual. Se va deschide fereastra de setări de grupare;

Câmpurile „Începând de la” și „până la” vor seta automat datele minime și maxime din listă. Dacă este necesar, puteți specifica aici o perioadă mai restrânsă pentru grupare
În lista „Incrementare”, selectați un interval de timp de referință. Pentru noi este „Luni”. Puteți selecta mai multe articole din această listă simultan. Să încercăm să construim pe sferturi și luni, marcați-le;
Faceți clic pe „Ok” și obțineți imediat rezultatul. Uite ce s-a intamplat:

Puteți grupa date numerice obișnuite în același mod. De exemplu, dorim să grupăm vânzările zilnice în trepte de 1000 și să aflăm care interval a avut cele mai multe încasări. Facem asta:

Construim un tabel pivot, în rânduri - sumele vânzărilor, în valori - numărul de încasări. La început vom ajunge cu o masă lungă și inutilă.

Faceți clic dreapta pe orice rând din prima coloană (valoarea vânzărilor) și selectați „Grup”. În fereastra care se deschide, setați numerele minime și maxime pentru grupare, precum și pasul. Pentru noi este 1000. În loc de o masă uriașă, obținem o masă compactă de zece rânduri. Fiecare rând conține un interval de sume și numărul de cecuri din acest interval.

Faceți clic dreapta pe orice celulă din coloana „Număr de chitanțe” și selectați Sortare – Descrescătoare;
Pentru claritate, puteți exprima rezultatul ca procent. Faceți clic pe aceeași celulă și selectați Calcule suplimentare - % din totalul coloanei. Atât, problema este rezolvată, vedem clar în ce intervale de cantitate au fost cele mai multe verificări.

Desigur, această sarcină este simplificată, diferă de cele pe care le vei rezolva. Dar principalul lucru este să înțelegeți mecanismul de funcționare, apoi îl puteți aplica în calcule.

De asemenea, puteți grupa înregistrările manual. Pentru a face acest lucru, selectați datele necesare și faceți clic Lucrul cu tabele pivot – Analiză – Grup – Grupare după selecție.

Apropo, pentru a degrupa, faceți clic dreapta pe coloana grupată și selectați „Ungrup”.

Filtrarea tabelelor pivot folosind slicere

Dacă știți puțin despre felii sau nu sunteți complet familiarizați cu ele, iată-l pe al meu. Pe scurt, slicerele sunt instrumente care vă permit să filtrați datele folosind butoanele de la distanță. Aceasta este o modalitate excelentă de a crea o interfață cu utilizatorul, deoarece, în realitate, nu orice utilizator Excel este avansat, la fel ca tine, și nu toată lumea știe cum să folosească filtrele de autofiltru, de căutare sau de tabel pivot.

Și arată așa:

Vedeți mai multe ferestre pe foaia de lucru cu datele enumerate în ele, precum și un tabel pivot care conține setul complet de date. Dar dacă cineva trebuie să vadă vânzări de hardware de la Roman pe 1 aprilie 2016? Faceți clic pe butoanele din ferestre:

În fereastra „Dată”, căutați și selectați 04/01/2016;
În fereastra „Manager”, selectați „Roman”;
În fereastra „Grup de produse”, faceți clic pe „Hardware”

Deci, în trei clicuri, orice utilizator poate selecta doar datele de care are nevoie dintr-o gamă largă de informații consolidate. Uite ce s-a intamplat:

Cred că e grozav! Deci, pentru a activa sectoarele în tabelele pivot, selectați orice celulă din acest tabel și executați pe panglică Lucrul cu Pivot Tables – Analiză – Filtrare – Insert Slicer. Pe ecran va apărea o fereastră în care trebuie să bifați acele câmpuri din tabelul pivot care pot fi folosite pentru a face felii. Fiecare câmp va avea propria casetă listă. Alegeți, faceți clic pe OK și gata, funcționează!

Cronologia funcționează în același mod. Acest instrument este foarte asemănător cu slicerele, dar gestionează câmpuri care conțin date. Pentru a adăuga o cronologie – faceți clic Lucrul cu tabele pivot – Analiză – Filtru – Inserare cronologie. După setări simple, va apărea o fereastră de filtrare a datei, care vă permite să limitați eficient și rapid perioadele de date afișate în raport.

Poate că acestea sunt toate funcțiile principale ale tabelelor pivot, deși le puteți studia în continuare capacitățile pe cont propriu. Sunt gata să vă răspund la întrebările despre materialul prezentat sau acele puncte care nu au fost incluse în recenzie. În următorul articol vom analiza opțiunile care vă pot face raportarea și mai clară. Pe curând!

Am intampinat recent o problema:

Tabelul site-urilor. Câmpurile indică diverși parametri. Toate site-urile sunt într-o listă generală și sunt, de asemenea, împărțite pe subiect. Fiecare subiect este într-o filă separată. Toată această chestiune este organizată folosind tabele pivot. Schimbând valorile din lista originală, acestea se schimbă în file la actualizarea tabelelor.

Problema, de fapt, este aceasta: majoritatea celorlalți angajați lucrează pe OpenOffice gratuit, care este foarte lent când deschid tabele pivot. Trebuie convertit

Rezumat_

tabele în cele obișnuite. Puteți, desigur, pur și simplu să copiați și să lipiți valorile pe o altă foaie, dar acest lucru necesită oarecum timp - există o mulțime de file, iar această operațiune este efectuată aproape în fiecare zi. Există o altă cale? Mulţumesc anticipat.

După crearea unui tabel pivot și personalizarea structurii acestuia, selectați întregul tabel și copiați-l în clipboard. Apoi, accesați fila Acasă și faceți clic pe butonul Inserare, apoi selectați Inserare valori din meniul derulant, așa cum se arată în Fig. 6.19.

Acest lucru va elimina tabelul pivot și îl va înlocui cu valori statice derivate din ultima stare a tabelului pivot. Valorile rezultate devin baza pentru tabelul pivot creat ulterior.

Figura 6.19. Comanda Inserare valori este folosită pentru a obține un tabel obișnuit cu valori statice bazate pe un tabel pivot.

Această tehnică este utilizată în mod eficient pentru a elimina interactivitatea unui tabel pivot. Acest lucru transformă PivotTable într-un tabel standard și, prin urmare, nu creează un PivotChart, ci o diagramă obișnuită care nu poate fi filtrată sau rearanjată. Aceeași observație se aplică și metodelor 2 și 3.

Salutare tuturor! Materialul de astăzi este pentru cei care continuă să stăpânească lucrul cu programe de aplicație și nu știu să facă un tabel pivot în Excel.

După ce ați creat un tabel general în oricare dintre documentele text, îl puteți analiza făcând tabele pivot în Excel.

Crearea unui tabel pivot Excel necesită îndeplinirea anumitor condiții:

Datele se încadrează într-un tabel cu coloane și liste cu nume.
Fără formulare necompletate.
Fără obiecte ascunse.

Cum să faci un tabel pivot în excel: instrucțiuni pas cu pas

Pentru a crea un tabel pivot, aveți nevoie de:

A fost creată o foaie goală, unde puteți vedea liste de zone și câmpuri. Anteturile au devenit câmpuri în noul nostru tabel. Tabelul pivot va fi format prin tragerea câmpurilor.

Acestea vor fi marcate cu o bifă, iar pentru o analiză ușoară le veți schimba în zonele tabelului.

Am decis că voi face analiza datelor printr-un filtru pe vânzător, astfel încât să fie clar de către cine și pentru ce sumă a fost vândut în fiecare lună și ce fel de produs.

Alegem un anumit vânzător. Țineți apăsat mouse-ul și mutați câmpul „Vânzător” la „Filtru de raportare”. Noul câmp este bifat și vizualizarea tabelului se modifică ușor.

Vom pune categoria „Produse” sub formă de linii. Transferăm câmpul de care avem nevoie în „Titli de rând”.

Pentru a afișa o listă derulantă, contează în ce ordine specificăm numele. Dacă inițial facem o alegere în favoarea unui produs din rânduri și apoi indicăm prețul, atunci produsele vor fi liste derulante și invers.

Coloana „Unități”, aflată în tabelul principal, afișa cantitatea de mărfuri vândută de un anumit vânzător la un anumit preț.

Pentru a afișa vânzările, de exemplu, pentru fiecare lună, trebuie să înlocuiți câmpul „Dată” cu „Nume coloane”. Selectați comanda „Grup” făcând clic pe dată.

Specificați perioadele de dată și pasul. Confirmați alegerea dvs.

Vedem o astfel de masă.

Să mutăm câmpul „Suma” în zona „Valori”.

Afișarea numerelor a devenit vizibilă, dar avem nevoie de formatul numeric

Pentru a corecta acest lucru, selectați celulele apelând fereastra cu mouse-ul și selectând „Format număr”.

Selectăm formatul numărului pentru următoarea fereastră și bifăm „Separator de grup de cifre”. Confirmați cu butonul „OK”.

Proiectarea unui tabel pivot

Dacă bifam caseta care confirmă selecția mai multor obiecte deodată, vom putea procesa date pentru mai mulți vânzători deodată.

Filtrul poate fi aplicat coloanelor și rândurilor. Bifând caseta de pe unul dintre tipurile de produse, puteți afla cât de mult a fost vândut de unul sau mai mulți vânzători.

Parametrii câmpului sunt de asemenea configurați separat. Folosind exemplul, vedem că un anumit vânzător rom într-o anumită lună a vândut cămăși pentru o anumită sumă. Făcând clic cu mouse-ul, în linia „Suma după câmp...” apelăm meniul și selectăm „Parametri câmp valoric”.

Apoi, pentru a rezuma datele din câmp, selectați „Cantitate”. Confirmați alegerea dvs.

Uită-te la masă. Arată clar că într-o lună vânzătorul a vândut 2 cămăși.

Acum schimbăm tabelul și facem ca filtrul să funcționeze pe lună. Transferăm câmpul „Dată” în „Filtru de raport”, iar acolo unde este „Nume coloane”, va fi „Vânzător”. Tabelul afișează întreaga perioadă de vânzări sau pentru o anumită lună.

Selectarea celulelor dintr-un tabel pivot va face să apară o filă numită „Lucrul cu tabelele pivot” și vor mai fi două file „Opțiuni” și „Designer”.

De fapt, puteți vorbi despre setările tabelelor pivot pentru o perioadă foarte lungă de timp. Faceți modificări în gustul dvs., obținând o utilizare convenabilă pentru dvs. Nu vă fie teamă să împingeți și să experimentați. Puteți schimba oricând orice acțiune apăsând combinația de taste Ctrl+Z.

Sper că ați învățat tot materialul și acum știți cum să faceți un tabel pivot în Excel.

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

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

Tabel pivot în Excel

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

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

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

Selectați celula A1, astfel încât Excel să știe cu ce informații va trebui să lucreze.
Din meniul „Inserare”, selectați „Tabel pivot”.
Se va deschide meniul „Creare Pivot Table”, unde selectăm intervalul și indicăm locația. Deoarece am plasat cursorul într-o celulă cu date, câmpul interval va fi completat automat. Dacă cursorul se află într-o celulă goală, trebuie să introduceți manual intervalul. Tabelul pivot poate fi realizat pe aceeași foaie sau pe alta. Dacă dorim ca datele rezumate să fie pe o pagină existentă, nu uitați să îi acordați un loc. Pe pagină apare următorul formular:
Să creăm un tabel care să arate valoarea vânzărilor pe departament. În lista de câmpuri din tabelul pivot, selectați numele coloanelor care ne interesează. Obținem rezultate pentru fiecare departament.

Simplu, rapid și de înaltă calitate.

Detalii importante:

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

Cum să faci un tabel pivot din mai multe tabele

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

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

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

Apelați meniul „Asistent tabel pivot și diagramă”. Pentru a face acest lucru, faceți clic pe butonul Setări Quick Access Toolbar și faceți clic pe „Alte comenzi”. Aici, în fila „Setări” găsim „Asistent tabel pivot”. Adăugați un instrument la bara de instrumente cu acces rapid. Dupa adaugarea:
Plasați cursorul pe prima placă și faceți clic pe instrumentul „Maeștri”. În fereastra care se deschide, rețineți că dorim să creăm un tabel în „mai multe intervale de consolidare”. Adică trebuie să combinăm mai multe locuri cu informații. Tipul de raport este „tabel pivot”. "Următorul."
Următorul pas este „crearea câmpurilor”. "Următorul."
Precizăm intervalul de date pentru care vom genera un raport de sinteză. Selectați primul interval împreună cu antetul - „adăugați”. Al doilea interval împreună cu numele coloanelor este „adăugați” din nou.
Acum selectați primul interval din listă. Am pus o pasăre la unitate. Acesta este primul câmp al raportului de sinteză. Îi dăm un nume - „Magazin 1”. Selectați al doilea interval de date - verificați numărul „2”. Numele câmpului este „Magazin 2”. Faceți clic pe „Următorul”.
Alegerea locului de amplasare a tabelului pivot. Pe o foaie existentă sau una nouă. Este mai bine să alegeți o foaie nouă, astfel încât să nu existe suprapuneri sau decalaje. Am prins asa:

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

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

Să începem cu cel mai simplu lucru: adăugarea și eliminarea coloanelor. Ca exemplu, luați în considerare un tabel rezumat al vânzărilor pentru diferite departamente (vezi mai sus).

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

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

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

Pentru a modifica parametrii din tabelul pivot, debifați câmpurile de rând existente și bifați casetele pentru alte câmpuri. Vom face un raport pe nume de produse, nu pe departamente.

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

Dar puteți face un raport ca acesta trăgând câmpuri între diferite zone:

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

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

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

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

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

Verificarea corectitudinii facturilor de utilitati emise

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

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

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

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

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

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

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

Descărcați toate exemplele PivotTable

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

Sursa de date pentru un tabel pivot este o listă de date, unde, de regulă, fiecare coloană acționează ca un câmp în tabelul pivot. Dar dacă ați primit un tabel care arată doar ca un tabel pivot (este formatat și arată ca acesta, dar este imposibil să utilizați instrumente pentru a lucra cu tabelele pivot). Și trebuie să o transformați într-o listă de date, de exemplu. efectuați operația inversă. În această postare, veți învăța cum să convertiți un tabel pivot cu două variabile într-o listă de date.

Imaginea arată principiul pe care l-am descris. Aceste. intervalul A2:E5 conține tabelul pivot original, care este convertit într-o listă de date (intervalul H2:J14). Al doilea tabel prezintă același set de date, doar dintr-o perspectivă diferită. Fiecare valoare din tabelul pivot original apare ca un rând, constând dintr-un element de câmp rând, un câmp de coloană și valoarea lor corespunzătoare. Afișarea datelor în acest fel este utilă atunci când trebuie să sortați și să manipulați datele în alte moduri.

Pentru a implementa capacitatea de a crea o astfel de listă, vom folosi instrumentele PivotTable. Să adăugăm un buton Expert tabel pivot la Bara de instrumente Acces rapid, care nu este disponibilă pentru noi pe panglică, dar rămâne ca un vestigiu din versiunile anterioare de Excel.

Accesați fila Fișier -> Opțiuni. În caseta de dialog care apare OpțiuniExcelaîn filă Bara de instrumente cu acces rapidîn câmpul din stânga găsiți articolul Tabel pivot și expert diagramă si adauga-l la cel potrivit. Faceți clic pe OK.

Acum aveți o pictogramă nouă pe Bara de instrumente Acces rapid.

Faceți clic pe această filă pentru a lansa Expert tabel pivot.

La primul pas al expertului, trebuie să selectați tipul de sursă de date pentru tabelul pivot. Instalarea comutatorului În mai multe intervale de consolidareși faceți clic Următorul.

La pasul 2a, specificați cum trebuie create marginile paginii. Puneți comutatorul Creați câmpuri de pagină -> Următorul.

La pasul 2b, pe teren Gamă selectați intervalul care conține datele și faceți clic Adăuga.În cazul nostru, aceasta va fi locația tabelului pivot original A1:E4.

În al treilea pas, trebuie să decideți unde doriți să plasați tabelul pivot și să faceți clic pe butonul Gata.

Excel va crea un tabel pivot cu datele. În partea stângă a ecranului veți vedea o zonă Lista câmpurilor din tabelul pivot. Eliminați toate elementele din câmpurile rând și coloană. Am scris mai detaliat despre editarea câmpurilor de rânduri și coloane într-un tabel pivot într-un articol anterior.

Veți ajunge cu un mic tabel pivot format dintr-o celulă care conține suma tuturor valorilor din tabelul original.

Faceți dublu clic pe această celulă. Excel va crea o nouă foaie de lucru care conține un tabel cu o listă de valori.

Titlurile acestui tabel oferă informații generale, poate doriți să le faceți mai descriptive.

Tabelele pivot sunt necesare pentru rezumarea, analizarea și prezentarea datelor aflate în tabele sursă „mari”, în diverse secțiuni. Să ne uităm la procesul de creare a tabelelor pivot simple.

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

există un tabel sursă cu multe rânduri (înregistrări), vorbim despre câteva zeci și sute de rânduri;
este necesar să se efectueze o analiză a datelor, care necesită eșantionarea (filtrarea) datelor, gruparea acestora (rezumat, numărare) și prezentarea datelor în diverse secțiuni (întocmirea rapoartelor);
Această analiză este dificil de realizat pe baza tabelului original folosind alte mijloace: ( CTRL+SHIFT+L), ;
tabelul sursă îndeplinește anumite cerințe (vezi mai jos).

Utilizatorii evită adesea utilizarea Tabelele pivot, pentru că Suntem siguri că sunt prea complicate. Într-adevăr, stăpânirea oricărui instrument sau metodă nouă necesită efort și timp. Dar, ca urmare, efectul stăpânirii a ceva nou ar trebui să depășească eforturile investite. În acest articol ne vom da seama cum să creăm și să folosim Tabelele pivot.

Pregătirea tabelului sursă

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

fiecare coloană trebuie să aibă un titlu;
Fiecare coloană trebuie să conțină valori într-un singur format (de exemplu, coloana Data livrării trebuie să conțină toate valorile numai în formatul Data; coloana „Furnizor” - numele companiilor doar î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 separate în coloane diferite. De exemplu, nu puteți introduce o adresă într-o singură celulă în formatul: „Oraș, Nume stradă, nr clădire”. Trebuie să creați 3 coloane cu același nume, altfel Tabel pivot va funcționa ineficient (dacă aveți nevoie de informații, de exemplu, în funcție de oraș);
Evitați tabelele cu structură „greșită” (vezi imaginea de mai jos).

În loc să producă coloane repetate ( regiunea 1, regiunea 2,...), în care va exista o abundență de celule goale, regândește structura tabelului, așa cum se arată în figura de mai sus (Toate valorile vânzărilor ar trebui să fie într-o coloană și să nu fie împărțite pe mai multe coloane. Pentru a implementați acest lucru, poate fi necesar să păstrați înregistrări mai detaliate (vezi figura de mai sus), în loc să afișaț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 construcție Tabel pivot, 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ă de 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 să punem o bifă în Lista de câmpuri de lângă câmpul Vânzări.

Deoarece Deoarece celulele din coloana Vânzări sunt în format numeric, acestea vor apărea 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ă trebuie, de exemplu, să determinați volumele de vânzări pentru fiecare Furnizor, atunci pentru a face acest lucru, debifați câmpul Produs din Lista de câmpuri și bifați câmpul Furnizor.

Detalierea datelor din tabelul pivot

Dacă aveți întrebări despre ce date din tabelul sursă au fost utilizate pentru a calcula anumite valori Tabel pivot, apoi faceți dublu clic cu mouse-ul pe o anumită valoare în Tabel pivot astfel încât să fie creată o foaie separată cu rânduri selectate din tabelul original. De exemplu, să ne uităm la 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. Se va crea o foaie separată numai cu rândurile tabelului sursă aferente Produsului „Portocale”.

Actualizarea tabelului pivot

Dacă după creaţie Tabel pivotîn tabelul sursă au fost adăugate înregistrări (rânduri) noi, atunci aceste date nu vor fi luate în considerare automat în Tabel pivot. Pentru a actualiza Tabel pivot selectați oricare dintre celulele sale și selectați elementul de meniu: meniu Lucrul cu tabele pivot/Opțiuni/Date/Reîmprospătare. Același rezultat poate fi obținut prin meniul contextual: selectați orice celulă Tabel pivot Actualizare.

Eliminarea unui tabel pivot

Şterge Tabel pivot posibil în mai multe moduri. Primul este să scoți pur și simplu foaia de pe Tabel pivot(cu excepția cazului în care există alte date utile, cum ar fi tabelul original). A doua modalitate este de a șterge numai Tabel pivot: selectați orice celulă Tabel pivot, apăsați CTRL+ O(toate vor fi evidențiate Tabel pivot), apăsați tasta Şterge.

Modificarea funcției de totaluri

La crearea Tabel pivot 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ă trebuie, de exemplu, să numărați numărul de loturi vândute ale fiecărui Produs, atunci trebuie să schimbați funcția de totaluri. În acest scop în Tabel pivot Totaluri după/Cantitate.

Modificarea ordinii de sortare

Acum să le modificăm puțin pe ale noastre Raport de sinteză. Mai întâi, să schimbăm ordinea de sortare a numelor Produselor: sortați-le în ordine inversă de la Z la A. Pentru a face acest lucru, prin lista derulantă din antetul coloanei care conține numele Produselor, accesați meniul și selecta Sortarea de la Z la A.

Acum să presupunem că produsul lui Baranka este cel mai important produs, așa că trebuie afișat în prima linie. Pentru a face acest lucru, selectați celula cu valoarea Baranki și plasați cursorul pe marginea celulei (cursorul ar trebui să arate ca o cruce cu săgeți).

Apoi, faceți clic stânga și trageți celula în poziția cea mai de sus din listă, direct sub antetul coloanei.

După eliberarea butonului mouse-ului, valoarea Baranka va fi mutată în poziția de sus în listă.

Modificarea formatului valorilor numerice

Acum să adăugăm un separator pentru grupurile de cifre pentru valorile numerice (câmpul Vânzări). Pentru a face acest lucru, selectați orice valoare din câmpul Vânzări, faceți clic dreapta pe meniul contextual și selectați elementul de meniu Format numeric…

În fereastra care apare, selectați un format de număr și bifați caseta Separator de mii de grupuri.

Adăugarea de noi câmpuri

Să presupunem că este necesar să se întocmească un raport privind vânzările de Produse, dar defalcat pe Regiunea de Vânzări. Pentru a face acest lucru, adăugați câmpul Regiunea de vânzări bifând caseta corespunzătoare din Lista de câmpuri. Câmpul Regiunea de vânzări va fi adăugat în zona Nume linii din Lista de câmpuri (la câmpul Produs). Prin schimbarea în zonă Nume de linii Lista câmpurilor, ordinea câmpurilor Produs și Regiunea vânzărilor, obținem următorul rezultat.

Evidențiind orice nume de produs și făcând clic pe elementul de meniu Lucrul cu tabele pivot/Opțiuni/ Câmp activ/ Restrângere tot câmpul, poate fi prăbușit Tabel pivot pentru a afișa numai vânzările pe regiune.

Adăugarea de coloane

Adăugarea câmpului Regiunea de vânzări la zona de rând a rezultat Tabel pivot extins la 144 de linii. Acest lucru nu este întotdeauna convenabil. Deoarece vânzările au fost efectuate doar în 6 regiuni, atunci câmpul Regiunea vânzări are sens să fie plasat în zona coloanei.

Tabel pivot va lua următoarea formă.

Schimbați coloanele

Pentru a schimba ordinea coloanelor, trebuie să prindeți antetul coloanei Tabel pivot trageți-l în locația dorită.

Eliminarea câmpurilor

Orice câmp poate fi eliminat din PivotTable. Pentru a face acest lucru, trebuie să treceț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âng al mouse-ului și trageți câmpul care urmează să fie șters dincolo de graniță din Lista 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 imediat 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 privind vânzările de Grupuri de Produse și trebuie făcut în 2 versiuni: una pentru loturile de Produse care au adus profit, cealaltă pentru cele neprofitabile. Pentru a face acest lucru:

Tabel pivot, faceți clic pe elementul de meniu;
Bifați casetele de lângă câmpurile Grup, Vânzări și Profit din Lista câmpurilor;
Mutați câmpul Profit din zona Nume linii din Lista de câmpuri în zona Filtru raport;

Vedere a rezultatului Tabel pivot ar trebui sa fie asa:

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

După ce faceți clic pe butonul 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 Tabel pivot O pictogramă de filtru a apărut vizavi de câmpul Profit. Puteți elimina un filtru debifând caseta din Lista de câmpuri.

Puteți șterge filtrul prin meniu Lucrul cu tabele pivot/Opțiuni/Acțiuni/Șterge/Șterge filtre.

Datele sunt, de asemenea, disponibile printr-o listă derulantă în antetele rândurilor și coloanelor Tabel pivot.

Totaluri multiple pentru un câmp

Să ștergem raportul creat anterior: selectați orice valoare Tabel pivot, faceți clic pe elementul de meniu Lucrul cu tabele 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 o altă copie a câmpului Vânzări în aceeași zonă Valori. ÎN Tabel pivot Vor apărea 2 coloane numărând sumele vânzărilor;

V Tabel pivot selectați orice valoare din câmpul Vânzări, faceți clic dreapta pe meniul contextual și selectați Totaluri după/Cantitate. Problema este rezolvată.

Dezactivarea liniilor totale

Linia totalurilor poate fi dezactivată prin intermediul meniului: Lucrul cu Tabele Pivote/ Designer/ Aspect/ Totaluri generale. Nu uitați să selectați mai întâi orice celulă Tabel pivot.

Gruparea numerelor și a datelor

Să presupunem că trebuie să pregătiți un raport privind momentul vânzărilor. Ca urmare, trebuie să obțineți următoarele informații: câte loturi de Produs au fost vândute în perioada de la 1 la 10 zile, în perioada de 11-20 de zile etc. Pentru a face acest lucru:

Să ștergem raportul creat anterior: selectați orice valoare Tabel pivot, faceți clic pe elementul de meniu Lucrul cu tabele 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 Produsului) din partea de sus a Listei de câmpuri. Câmpul Vânzări va fi plasat automat în zona Valori;
selectați valoarea unică a câmpului Vânzări în Tabel pivot, apelați meniul contextual cu butonul din dreapta al mouse-ului și selectați Totaluri după/Cantitate.
Trageți o altă copie a câmpului Vânzări în zona Nume linii;

Acum Tabel pivot arată câte loturi de Produs au fost vândute în 5, 6, 7, ... zile. Sunt 66 de linii în total. Să grupăm valorile în trepte de 10. Pentru a face acest lucru:

Selectați o valoare Tabel pivotîn coloana Nume rânduri;
Din meniu, selectați 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 Tabel pivot arata cate loturi de Produs au fost vandute in perioada de la 1 la 10 zile, in perioada 11-20 de zile etc.

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

O grupare similară se poate face folosind câmpul Data livrării. În acest caz fereastra Grupați după câmp va arata asa:

Acum Tabel pivot arată câte loturi de Produs au fost livrate în fiecare lună.

Formatarea condiționată a celulelor PivotTable

La celule Tabel pivot puteți aplica regulile la fel ca celulelor dintr-un interval obișnuit.
Să evidențiem, de exemplu, celulele cu cele mai mari 10 volume de vânzări. Pentru a face acest lucru:

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

Cum să transformi un tabel pivot într-unul obișnuit