✅ Pregătirea datelor sursă pentru tabelul pivot

Cuprins

Care este un design bun pentru datele sursă pentru tabelul pivot?
Capcanele obișnuite de evitat în timpul pregătirii datelor sursă
Exemple de proiecte de date sursă incorecte
[STUDIU DE CAZ] Conversia unei date greșit formatate în date sursă pregătite pentru tabelul pivot

A avea datele în formatul corect este un pas crucial în crearea unui tabel pivot robust și fără erori. Dacă nu se face corect, puteți ajunge să aveți o mulțime de probleme cu tabelul pivot.

Care este un design bun pentru datele sursă pentru tabelul pivot?

Să aruncăm o privire la un exemplu de date sursă bune pentru un tabel pivot.

Iată ceea ce îl face o sursă bună de proiectare a datelor:

Primul rând conține anteturi care descriu datele din coloane.
Fiecare coloană reprezintă o categorie de date unică. De exemplu, coloana C are numai date despre produs, iar coloana D și numai date despre lună.
Fiecare rând este o înregistrare care ar reprezenta o instanță a tranzacției sau vânzării.
Anteturile de date sunt unice și nu se repetă nicăieri în setul de date. De exemplu, dacă aveți numere de vânzări pentru patru trimestre într-un an, NU ar trebui să le denumiți pe toate ca vânzări. În schimb, dați acestor anteturi de coloane nume unice, cum ar fi Vânzările Q1, Vânzările Q2 și așa mai departe …
- Dacă nu aveți titluri unice, puteți continua să creați un tabel pivot, iar Excel le va face automat unice adăugând un sufix (cum ar fi Sales, Sales2, Sales3). Cu toate acestea, acesta ar fi un mod îngrozitor de a pregăti și utiliza un tabel pivot.

Capcanele obișnuite de evitat în timpul pregătirii datelor sursă

Nu ar trebui să existe coloane goale în datele sursă. Acesta este ușor de observat. Dacă aveți o coloană goală în datele sursă, nu veți putea crea un tabel pivot. Se va afișa o eroare așa cum se arată mai jos.
În datele sursă nu ar trebui să existe celule / rânduri necompletate. Deși puteți crea cu succes un tabel pivot în ciuda faptului că aveți celule sau rânduri goale, există multe efecte secundare care vă pot mușca mai târziu în cursul zilei.
- De exemplu, să presupunem că aveți o celulă necompletată în coloana de vânzări. Dacă creați un tabel pivot folosind aceste date și puneți câmpul de vânzări în zona coloanelor, acesta vă va arăta COUNTUL și nu SUMA. Asta pentru că Excel interpretează întreaga coloană ca având date text (doar datorită unei singure celule goale).
Aplicați formatul relevant celulelor din datele sursă. De exemplu, dacă aveți date (care sunt stocate ca numere de serie în backend în Excel), aplicați unul dintre formatele de dată acceptabile. Acest lucru vă va ajuta să creați tabelul pivot și să utilizați Data ca unul dintre criteriile de rezumare, grupare și sortare a datelor.
- Dacă aveți câteva secunde, încercați acest lucru. Formatați datele din tabelul pivot ca numere, apoi creați un tabel pivot folosind aceste date. Acum, în Tabelul pivot, selectați câmpul de dată și vedeți ce se întâmplă. Acesta îl va pune automat în zona valorilor. Acest lucru se datorează faptului că Tabelul Pivot nu știe că acestea sunt date. Acesta le interpretează ca numere.
Nu includeți niciun total de coloană, total de rânduri, medii etc. ca parte a datelor sursă. Odată ce aveți tabelul pivot, le puteți obține cu ușurință mai târziu.
Creați întotdeauna un tabel Excel și apoi utilizați-l ca sursă pentru un tabel pivot. Aceasta este mai mult o bună practică și nu o capcană. Tabelul pivot ar funcționa foarte bine cu o sursă de date care nu este și un tabel Excel. Avantajul cu tabelul Excel este că poate ajusta datele în expansiune. Dacă adăugați mai multe rânduri la setul de date, nu este necesar să reglați datele sursă din nou și din nou. Puteți pur și simplu să reîmprospătați tabelul pivot și acesta va contabiliza automat noile rânduri adăugate la datele sursă.

Exemple de proiecte de date sursă incorecte

Să aruncăm o privire la câteva exemple proaste ale proiectelor de date sursă.

Proiectare greșită a datelor sursă - Exemplul 1

Acesta este un mod obișnuit de a menține datele, deoarece este ușor de urmărit și de înțeles. Există două probleme cu acest aranjament de date:

Nu obțineți imaginea completă. De exemplu, puteți vedea că vânzările pentru Mid West în primul trimestru sunt 2924300. Dar este o singură vânzare sau un număr de vânzări. Dacă aveți fiecare înregistrare disponibilă pe un rând separat, puteți face o analiză mai bună.
Dacă mergeți mai departe și creați un tabel pivot folosind acest lucru (pe care îl puteți), veți obține câmpuri diferite pentru trimestre diferite. Ceva așa cum se arată mai jos:

Proiectare de date sursă defectuoasă - Exemplul 2

Această reprezentare a datelor poate fi primită bine de către conducere și de publicul prezentărilor PowerPoint, dar nu este potrivită pentru crearea unui tabel pivot.

Din nou, acesta este genul de rezumat pe care îl puteți crea cu ușurință folosind un tabel pivot. Deci, chiar dacă doriți în cele din urmă un astfel de aspect pentru datele dvs., mențineți datele sursă într-un format pregătit pentru pivot și creați această vizualizare folosind tabelul pivot.

Proiectare de date sursă defectuoasă - Exemplul 3

Aceasta este din nou o ieșire care poate fi obținută cu ușurință utilizând un tabel pivot. Dar nu poate fi folosit pentru a crea un tabel pivot.

Există celule goale în setul de date și sferturile sunt distribuite ca anteturi de coloană.

De asemenea, regiunea este specificată în partea de sus, în timp ce ar trebui să facă parte din fiecare înregistrare.

[STUDIU DE CAZ] Conversia unei date greșit formatate în date sursă pregătite pentru tabelul pivot

Uneori, este posibil să obțineți un set de date care nu este adecvat pentru a fi utilizat ca date sursă pentru tabelul pivot. Într-un astfel de caz, este posibil să nu aveți de ales decât să convertiți datele într-un format de date compatibil cu pivotul.

Iată un exemplu de proiectare defectuoasă a datelor:

Acum puteți utiliza funcțiile Excel sau interogarea pivot pentru a converti aceste date într-un format care poate fi utilizat ca date sursă pentru tabelul pivot.

Să vedem cum funcționează ambele metode.

Metoda 1: Utilizarea formulelor Excel

Să vedem cum să folosim funcțiile Excel pentru a converti aceste date într-un format pregătit pentru tabelul pivot.

Creați un antet de coloană unic pentru toate categoriile din setul de date original. În acest exemplu, ar fi regiune, trimestru și vânzări.
În celula de sub antetul Regiunii, utilizați următoarea formulă: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2) / COUNTA ($ B $ 1: $ E $ 1), 0))
- Trageți formula în jos și va repeta toate regiunile.
În celula de sub antetul Quarter, utilizați următoarea formulă: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1) , 0))
- Trageți formula în jos și va repeta toate sferturile.
În antetul de sub Vânzări, utilizați următoarea formulă: = INDEX ($ B $ 2: $ E $ 5 $, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 )))
- Trageți-l în jos pentru a obține toate valorile. Această formulă utilizează datele Regiune și Trimestru ca valori de căutare și returnează valoarea vânzărilor din setul de date original.

Acum puteți utiliza aceste date rezultate ca date sursă pentru tabelul pivot.

Faceți clic aici pentru a descărca fișierul exemplu.

Metoda 2: Utilizarea Power Query

Power Query are o caracteristică care poate converti cu ușurință acest tip de date în formatul de date pregătit pentru pivot.

Dacă utilizați Excel 2016, funcțiile Power Query ar fi disponibile în fila Date din grupul Obțineți și transformați. Dacă utilizați Excel 2013 sau versiunile anterioare, îl puteți folosi ca supliment.

Iată un ghid excelent despre instalarea Power Query de Jon de la Excel Campus.

Din nou, având în vedere că aveți datele formatate așa cum se arată mai jos:

Iată pașii pentru a converti datele sursă în format pregătit pentru tabelul pivot:

Convertiți datele într-un tabel Excel. Selectați setul de date și accesați Insert -> Tables -> Table.
În caseta de dialog Inserare tabel, asigurați-vă că este selectat intervalul corect și faceți clic pe OK. Aceasta va converti datele tabulare într-un tabel Excel.
În Excel 2016, accesați Date -> Obțineți și transformați -> Din tabel.
- Dacă utilizați Power Query Addin într-o versiune anterioară, accesați Power Query -> Date externe -> Din tabel.
În editorul de interogări, selectați coloanele pe care doriți să le anulați. În acest caz, acestea sunt cele pentru cele patru trimestre. Pentru a selecta toate coloanele, țineți apăsată tasta Shift, apoi selectați prima coloană și apoi ultima coloană.
În Editorul de interogări, accesați Transformare -> Orice coloană -> Unpivot Coloane. Aceasta va converti datele coloanei în formatul Pivot Table friendly.
Power Query dă nume generice coloanelor. Schimbați aceste nume cu cele dorite. În acest caz, schimbați atributul în trimestru și valoarea în vânzări.
În Editorul de interogări, accesați Fișier -> Închidere și încărcare. Aceasta va închide caseta de dialog Power Query Editor și va crea o foaie de lucru separată care va conține datele cu coloane nepivotate.