Urmăriți videoclipul - cel mai rapid mod de a nepivota datele în Excel
Tabelele pivot sunt excelente atunci când doriți să analizați o cantitate imensă de date în câteva secunde. De asemenea, vă permite să creați rapid diferite vizualizări ale datelor prin simpla glisare și plasare.
Și pentru a crea un tabel pivot, trebuie să aveți datele într-un format specific pentru tabelul pivot.
În multe cazuri, este posibil să obțineți datele în formate care nu sunt pregătite pentru Tabelul pivot.
Acesta este adesea cazul când cineva colectează manual date și creează un format care este mai ușor de citit de către oameni (nu din tabelele pivot).
Ceva așa cum se arată mai jos:
Formatul de date de mai sus este ceva ce vă așteptați să obțineți ca rezultat al unei analize a tabelului pivot.
Acum, dacă doriți să analizați aceleași date și să vedeți care au fost vânzările totale pe fiecare regiune sau pe lună.
Deși acest lucru se poate face cu ușurință folosind tabele pivot, din păcate, nu puteți alimenta datele de mai sus într-un tabel pivot.
Deci, trebuie să dezimplicați datele și să le faceți tabelul pivot prietenos.
Deși există câteva modalități de a face acest lucru utilizând formula Excel sau VBA, Power Query (Obțineți și transformați în Excel 2016) este cel mai bun instrument pentru a nepivota datele.
Unpivot date folosind Power Query
Iată pașii pentru unpivotarea datelor folosind Power Query:
(Dacă datele dvs. sunt deja într-un tabel Excel, începeți de la pasul 6 înainte)
- Selectați orice celulă din setul de date.
- Accesați fila Inserare.
- Faceți clic pe pictograma Tabel.
- În caseta de dialog „Creați tabel”, asigurați-vă că intervalul este corect. Puteți modifica intervalul dacă este necesar.
- Faceți clic pe OK. Aceasta vă va converti datele tabulare într-un tabel Excel.
- Cu orice celulă selectată în tabelul Excel, faceți clic pe fila Date.
- În grupul de date Obțineți și transformați, faceți clic pe pictograma „Din tabel / interval”.
- În caseta de dialog Creare tabel care se deschide (dacă se deschide), faceți clic pe OK. Aceasta va deschide Editorul de interogări folosind datele din tabelul Excel.
- În editorul de interogări, faceți clic dreapta pe coloana Regiune.
- Faceți clic pe opțiunea „Dezimpivotați alte coloane”. Acest lucru vă va dezactiva instantaneu datele.
- Schimbați numele coloanei „Atribut” cu un nume mai semnificativ, cum ar fi „Luni”.
- După ce aveți datele nepivotate, este o practică bună să vă asigurați că tipurile de date sunt corecte. În acest exemplu, faceți clic pe o celulă pentru fiecare coloană și vedeți tipul de date în fila Transformare. Dacă este necesar, puteți schimba și tipul de date.
- (Opțional) Schimbați numele interogării dvs. în „Vânzări”.
- Accesați fila Acasă (în editorul de interogări).
- Faceți clic pe Închidere și încărcare.
Pașii de mai sus v-ar dezvârti setul de date folosind Power Query și ar fi readus în Excel ca tabel într-o nouă foaie de lucru.
Acum puteți utiliza aceste date pentru a crea diferite vizualizări folosind un tabel pivot. De exemplu, puteți verifica valoarea totală a vânzărilor în funcție de lună sau de regiune.
Reîmprospătarea interogării când sunt adăugate date noi
Totul funcționează bine.
Dar ce se întâmplă când se adaugă date noi la setul nostru de date original.
Să presupunem că obțineți date pentru luna iulie, care este în același format cu cel cu care am început.
Trebuie să repet din nou toți pașii pentru a include aceste date în setul meu de date nepivotat?
Raspunsul este nu.
Și acesta este ceea ce este atât de minunat la Power Query. Puteți continua să adăugați date noi (sau să modificați datele existente), iar Power Query le-ar actualiza instantaneu imediat ce le reîmprospătați.
Lasă-mă să-ți arăt cum.
Să presupunem că mai jos este noul set de date pe care îl obțin (care are date suplimentare pentru luna iulie):
Iată pașii pentru reîmprospătarea interogării deja create și dezimpivotarea acestor date:
- Adăugați aceste date noi la datele dvs. originale pe care le-ați folosit pentru a crea interogarea.
- Deoarece adăugați date în coloana alăturată a unui tabel Excel, tabelul Excel se va extinde pentru a include aceste date în el. Dacă nu este întâmplător, faceți-l manual glisând pictograma mică „L” inversată din partea dreaptă jos a tabelului Excel.
- Accesați fila Date și faceți clic pe Interogări și conexiuni. Aceasta va afișa un panou cu toate interogările existente în el.
- Faceți clic dreapta pe interogarea Vânzări din panoul Interogări.
- Faceți clic pe Reîmprospătare.
Asta e! Noile date sunt instantaneu nepivotate și adăugate la datele existente.
Ați observa că numărul de rânduri afișat în actualizările interogării pentru a vă arăta noile numere. În acest exemplu, au fost 24 înainte de actualizare și au devenit 28 după actualizare.
Acest lucru înseamnă, de asemenea, că dacă ați creat orice tabele pivot folosind datele obținute de la Power Query, acele tabele pivot s-ar reîmprospăta și pentru a vă arăta rezultatele actualizate.
