Cum să grupați datele într-un tabel pivot Excel. Exemple pentru gruparea datelor, numărului și câmpurilor de text. Cum să depanați problemele de grupare și să evitați problemele.
Gruparea datelor
Într-un tabel pivot, puteți grupa datele, numărul și câmpurile de text. De exemplu, grupați datele comenzilor după an și lună sau grupați scorurile testelor în benzi de 10. Puteți selecta manual elemente de text într-un câmp al tabelului pivot și grupați elementele selectate. Aceasta vă permite să vedeți rapid subtotaluri pentru un anumit set de articole din tabelul pivot.
Acest videoclip arată cum să grupați datele după lună și ani și cum să grupați manual elementele de text. Există pași scrise sub videoclip.
Gruparea automată a datelor
În Excel 2016 și versiunile ulterioare, datele sunt grupate automat atunci când adăugați un câmp de dată într-un tabel pivot. Puteți anula gruparea în Excel 2016 și puteți dezactiva această caracteristică în versiunile ulterioare.
Dezactivați gruparea automată a datelor
În Excel 2019 și Excel 365, puteți schimba o opțiune Excel , pentru a activa sau a dezactiva această funcție de grupare a datelor
NOTĂ: Aceasta este o setare la nivel de aplicație și va afecta toate registrele de lucru Excel.
Pentru a activa funcția de grupare automată a datelor oprit:
- Pe panglică, faceți clic pe fila Fișier, apoi faceți clic pe Opțiuni
- În partea stângă, faceți clic pe categoria Date
- La sfârșitul secțiunea Opțiuni date, adăugați o bifă la „Dezactivați gruparea automată a coloanelor de dată / oră în tabelele pivot”
- Faceți clic pe OK pentru a aplica noile setări.
Anulați gruparea de date
Pentru toate versiunile (Excel 2016 și versiuni ulterioare), puteți dezgropa manual datele, cu una dintre aceste metode :
Comandă rapidă de la tastatură
- Adăugați câmpul Data la tabelul pivot
- Apăsați imediat Ctrl + Z, pentru a anula gruparea automată a datei
- Câmpurile grupate sunt eliminate, iar câmpul original Data rămâne în tabelul pivot
Comanda de grupare
- Adăugați câmpul Data în tabelul pivot
- În orice moment, faceți clic dreapta pe oricare dintre elementele sau titlurile câmpului de dată
- În meniul cu clic dreapta, faceți clic pe Dezagregare
- Câmpurile grupate sunt eliminate, iar câmpul original Data rămâne în tabelul pivot
Datele de grupare
Într-un tabel pivot, există mai multe în opțiuni pentru gruparea datelor – secunde, minute, ore, zile, luni, trimestre și ani.
Puteți selecta una sau mai multe dintre aceste opțiuni, pentru a crea tipul de grupare de date de care aveți nevoie. Exemplele de mai jos arată cum să grupați datele:
– după lună și an
– după săptămână
– după o perioadă de 4 săptămâni
Grupați datele după lună și an
Pentru a grupa articolele într-un câmp Data
- Faceți clic dreapta pe una dintre datele din tabelul pivot.
- În meniul pop-up, faceți clic pe Grup
- În caseta de dialog Grupare, selectați una sau mai multe opțiuni din lista „După”.
- Pentru a limita datele grupate, puteți seta o dată de început și de sfârșit, tastând datele în casetele „Începând de la” și „Se încheie la”
- Faceți clic pe OK pentru a închide caseta de dialog.
Datele grupului după săptămână
Pentru a grupa articolele dintr-un câmp Data în funcție de săptămână
- Faceți clic dreapta pe una dintre datele din tabelul pivot.
- În meniul popup, faceți clic pe Grupare
- În caseta de dialog Grupare, selectați Zile din lista „După”.
- Pentru „Număr de zile”, selectați 7
- Se determină intervalul săptămânal până la data din caseta „Începând de la”, așa că ajustați acest lucru dacă este necesar. În exemplul de mai jos, 1 ianuarie 2012, a fost introdusă ca dată de începere.
- Faceți clic pe OK
Date de grup pe perioade de 4 săptămâni
Urmăriți acest videoclip pentru a vedea cum să grupați câmpul de dată în perioade de 4 săptămâni și setați data de începere. Există instrucțiuni scrise sub videoclip.
Datele grupului pe perioadă de 4 săptămâni
În acest exemplu, compania are 13 perioade de vânzare în fiecare an, cu 4 săptămâni în fiecare perioadă. În caseta de dialog Gruparea tabelului pivot, nu există nicio opțiune pentru gruparea pe săptămâni. În schimb, putem folosi opțiunea Zile.
Cu un pic de aritmetică simplă, putem calcula numărul de zile pe perioadă:
- 4 săptămâni pe perioadă
- 7 zile pe săptămână
- 7 x 4 = 28 de zile pe perioadă
Urmați acești pași pentru a grupa datele în perioade de 4 săptămâni:
- Faceți clic dreapta pe date și faceți clic pe Grup
- În fereastra de grupare, faceți clic pe Zile, pentru ao selecta
- Dacă sunt selectate alte tipuri de grupare, faceți clic pe ele, pentru a le deselecta
- Setați numărul de zile la 28
- NOTĂ: pentru a grupa datele în perioade de o săptămână, utilizați 7 ca număr de zile.
Utilizarea datei de începere implicite
Ca dată de început, Excel selectează automat prima dată din datele dvs. sursă. În ecranul de mai sus, puteți vedea că 1 ianuarie 2013 este data de începere a acestor date sursă.
Dacă verific în Outlook, acea dată cade într-o zi de marți.
Dacă fac clic pe OK în fereastra de grupare, Excel va crea perioade care încep într-o zi de marți. În datele grupate afișate mai jos, prima perioadă merge de marți, 1 ianuarie 2013 până luni, 28 ianuarie 2013.
Remediați data de începere
În loc să utilizați data de pornire implicită, puteți introduce o dată diferită, pentru a obține data de începere de care aveți nevoie.
În acest exemplu, „ Vom schimba data de începere la 31 decembrie 2012. Această dată cade într-o zi de luni, deci acest lucru obligă toate grupările să înceapă într-o zi de luni, care corespunde perioadelor de vânzări ale companiei.
Pentru a stabili data de începere:
- Faceți clic dreapta pe una dintre celulele de date grupate din tabelul pivot
- Faceți clic pe Grup
- În fereastra Grupare, modificați data de început – 31 decembrie 2012
- NOTĂ: Caseta de selectare Auto pentru „Începând de la” este debifată automat – lăsați-o bifată.
- Faceți clic pe OK
Acum, perioadele sunt grupate corect, cu o zi de luni ca dată de început pentru fiecare perioadă.
Gruparea după dată adaugă elemente suplimentare
Urmăriți acest scurt videoclip pentru a vedea cum să ascundeți textul pe elementele suplimentare create atunci când grupați un câmp de dată sau utilizați un cronologie în Excel 2013, pentru a filtra după o perioadă de dată. Există instrucțiuni scrise sub videoclip.
Descărcați fișierul eșantion pentru elementele suplimentare din gruparea de date. Fișierul zip este în format xlsx și nu conține macrocomenzi.
Gruparea după dată adaugă elemente suplimentare
Nu există nicio modalitate de a elimina aceste elemente suplimentare, dar puteți schimba subtitrările, pentru a înlocui textul cu caractere spațiale. Sau, adăugați câmpuri noi în datele sursă, pentru a calcula anul și anul / luna, pentru fiecare dată și utilizați câmpurile noi din tabelul pivot, în loc de grupare.
În Excel 2013, puteți utilizați filtrul Cronologie, pentru a filtra rapid după an, trimestru, lună sau zi.
Descărcați fișierul eșantion pentru extra elemente din gruparea de date. Fișierul comprimat este în format xlsx și nu conține macrocomenzi.
Numere de grupare
Utilizați gruparea pentru a crea un rezumat concis, în loc să afișați o listă lungă de numere individuale. Acest videoclip vă arată pașii, iar instrucțiunile scrise sunt sub videoclip.
Gruparea numerelor
În loc să afișați numerele individual într-un câmp Row, le puteți grupa, pentru a crea un rezumat concis al datelor.
În acest exemplu, tabelul pivot original arată unitățile vândute, pe produs. În coloana C, se arată numărul de comenzi în care a fost vândută acea cantitate.
Pentru a rezuma comenzile, grupați unitățile cu zeci. Deci, în loc să apară 3 și 9 în rânduri separate, acestea vor fi rezumate în gruparea 1-10.
Pentru a grupa numerele
- Faceți clic dreapta pe unul a numerelor de unități din tabelul pivot.
- În meniul pop-up, faceți clic pe Grup
- În caseta de dialog Grupare, introduceți 1 în caseta Începând de la
- În acest exemplu, cel mai mare număr de unități este 50 și puteți introduce un număr mai mare, dacă este necesar.
- Faceți clic pe OK, pentru a aplica gruparea
În tabelul pivot, Unitățile apar acum în grupuri – 1 – 10, 11 – 20 etc.
Pentru a face un aspect mai concis, puteți muta câmpul de unități grupate în zona Coloane, ca titluri.
Gruparea articolelor text
Caseta de dialog Gruparea nu este disponibilă dacă încercați să grupați elementele într-un câmp text. În schimb, puteți grupa manual articolele.
Urmăriți acest videoclip pentru a vedea pașii pentru crearea grupurilor din elemente text. Apoi, redenumiți un grup sau adăugați mai multe elemente. Mai târziu, puteți dezgropa unul sau mai multe dintre grupuri. Există instrucțiuni scrise sub videoclip.
Gruparea elementelor text
Caseta de dialog Gruparea nu este disponibilă dacă încercați să grupați elementele într-un câmp text. În schimb, puteți grupa manual elementele.
- Selectați elementele pe care doriți să le grupați
- Faceți clic dreapta pe unul dintre elementele selectate și faceți clic pe Grup
Aceasta creează un câmp nou în lista de câmpuri, cu numele câmpului original și un număr, cum ar fi City2
Noul câmp este poziționat deasupra câmpului inițial în aspectul tabelului pivot, iar elementele grupate au un nume generic, cum ar fi Group1.
Pentru a redenumi grupul:
- Faceți clic pe celulele de titlu cu numele implicit , de exemplu Grup1
- Tastați un nume nou, cum ar fi Campania A.
Pentru a adăuga mai multe elemente pentru un grup:
- Selectați toate elementele din grup
- Apăsați tasta Ctrl și selectați elementele pe care doriți să le adăugați la grup
- Faceți clic dreapta pe unul dintre elementele selectate și faceți clic pe Grup
- NOTĂ: Dacă grupul a fost redenumit, numele respectiv va fi suprascris cu un nume implicit.
Dezagregarea elementelor de text
După gruparea manuală a elementelor de text, poate doriți să dezgropați unele, sau toate, a grupurilor dintr-un câmp pivot.
Pentru a dezgropa un singur grup:
- Faceți clic dreapta pe un element din grup
- Faceți clic pe Deconectați, iar grupul revine la elemente individuale.
Pentru a grupa un singur grup:
- Faceți clic în partea de sus dintr-un titlu de grup sau faceți clic în partea de sus a câmpului pivot, pentru a selecta toate elementele
- Faceți clic dreapta pe un element din grup
- Faceți clic pe Dezgrupare și toate grupurile selectate revin la elemente individuale.
Probleme la gruparea datelor
Dacă încercați să grupați o dată sau un număr , este posibil să vedeți un mesaj de eroare care să spună „Nu se poate grupa selecția respectivă”. Acest lucru poate fi cauzat de Modelul de date sau de celulele necompletate sau de elementele grupate anterior. Vedeți detaliile de mai jos.
Model de date
Când ați creat tabelul pivot, dacă ați bifat caseta „Adăugați aceste date la modelul de date”, nu veți putea grupa articolele. În loc de un tabel pivot normal, este un pivot de putere bazat pe OLAP, iar caracteristica de grupare nu este disponibilă.
Celule sau text goale
Într-un tabel pivot normal (nu în modelul de date), gruparea problema apare de obicei atunci când câmpul conține înregistrări cu
- o celulă necompletată într-un câmp de dată / număr sau
- o intrare text într-un câmp de dată / număr.
Pentru a remedia problema
- Pentru celulele necompletate, completați data / numărul (utilizați o dată / un număr fals dacă este necesar).
- Dacă există text în câmpul de dată / număr, eliminați-l.
- Dacă numerele sunt recunoscute ca text, utilizați una dintre tehnicile pentru a schimba textul în numere reale. Apoi reveniți la acest tutorial din tabelul pivot și încercați pașii de grupare.
Gruparea anterioară
Dacă nu aveți celule necompletate sau text în coloana de dată, poate rămâne un câmp grupat din data anterioară în care ați grupat datele.
- Verificați lista câmpurilor, pentru a vedea dacă există o a doua copie a câmpului de dată, de ex Data2.
- Dacă există, adăugați-l în zona rândurilor și dezgrupați-l.
- Apoi, ar trebui să puteți grupa din nou câmpul de dată.
Gruparea într-un tabel pivot afectează altul
Dacă creați două tabele pivot pe baza aceluiași tabel Excel din Excel 2007, când modificați gruparea într-un tabel pivot, aceeași grupare apare în celălalt tabel pivot.
Deoarece ați creat cele două tabele pivot din aceeași sursă de date, în mod implicit folosesc aceeași cache pivot, grupată articolele sunt aceleași în ambele tabele.
Pentru a utiliza grupări diferite în fiecare tabel pivot, va trebui să creați o memorie cache pivot separată pentru fiecare tabel pivot.
Utilizați următoarea metodă, sugerată în grupurile de știri Excel de Dave Peterson.
Pentru a crea o memorie cache pivot separată pentru al doilea tabel pivot:
- Tăiați al doilea tabel pivot și lipiți-l într-un nou registru de lucru.
- Schimbați gruparea celui de-al doilea tabel pivot.
- Tăiați al doilea tabel pivot din noul registru de lucru și lipiți-l înapoi în registrul de lucru original.
Descărcați Fișier eșantion
Descărcați fișierul eșantion comprimat pentru acest tutorial
Instrumente pentru tabelul pivot
Pentru a economisi timp la construirea, formatarea și modificarea tabelelor pivot, utilizați instrumentele în programul meu de completare Pivot Power Premium. Cu doar câteva clicuri, puteți:
- copiați formatarea dintr-un tabel pivot și să îl aplicați unui alt tabel pivot.
- modificați toate valorile din Număr în sumă
- eliminați „suma din” din toate titlurile
și multe altele!
Mai multe tutoriale
Ștergeți elementele vechi din tabelul pivot
Câmp calculat – Număr
Calculat Elemente
Funcții de rezumat