Cuprins
Formularea problemei
Ca date de intrare, avem un fișier Excel, în care una dintre foi conține mai multe tabele cu date de vânzări de următoarea formă:
Rețineți că:
- Tabele de diferite dimensiuni și cu diferite seturi de produse și regiuni în rânduri și coloane fără nicio sortare.
- Între tabele pot fi introduse linii goale.
- Numărul de mese poate fi oricare.
Două ipoteze importante. Se presupune că:
- Deasupra fiecărui tabel, în prima coloană, se află numele managerului ale cărui vânzări ilustrează tabelul (Ivanov, Petrov, Sidorov etc.)
- Denumirile mărfurilor și regiunilor din toate tabelele sunt scrise în același mod – cu precizie de majuscule.
Scopul final este de a colecta date din toate tabelele într-un singur tabel normalizat plat, convenabil pentru analiza ulterioară și construirea unui rezumat, adică în acesta:
Pasul 1. Conectați-vă la fișier
Să creăm un nou fișier Excel gol și să-l selectăm în filă Date Comandă Obține date – Din fișier – Din carte (Date — Din fișier — Din registru de lucru). Specificați locația fișierului sursă cu datele vânzărilor și apoi în fereastra navigatorului selectați foaia de care avem nevoie și faceți clic pe butonul Convertiți datele (Transformare date):
Ca rezultat, toate datele din acesta ar trebui să fie încărcate în editorul Power Query:
Pasul 2. Curățați gunoiul
Ștergeți pașii generați automat tip modificat (Tipul schimbat) и Antete ridicate (Anteturi promovate) și scăpați de liniile goale și liniile cu totaluri folosind un filtru zero и TOTAL de prima coloană. Drept urmare, obținem următoarea imagine:
Pasul 3. Adăugarea managerilor
Pentru a înțelege mai târziu unde sunt vânzările, este necesar să adăugați o coloană la tabelul nostru, unde în fiecare rând va fi un nume de familie corespunzător. Pentru asta:
1. Să adăugăm o coloană auxiliară cu numere de linie folosind comanda Adăugați coloană – coloană index – de la 0 (Adăugați coloană — coloană index — De la 0).
2. Adăugați o coloană cu o formulă cu comanda Adăugarea unei coloane – coloană personalizată (Adăugați coloană — coloană personalizată) și introduceți următoarea construcție acolo:
Logica acestei formule este simplă – dacă valoarea următoarei celule din prima coloană este „Produs”, atunci aceasta înseamnă că am dat peste începutul unui nou tabel, așa că afișăm valoarea celulei anterioare cu numele managerului. Altfel, nu afișăm nimic, adică nul.
Pentru a obține celula părinte cu numele de familie, ne referim mai întâi la tabelul de la pasul anterior #”Index adăugat”, apoi specificați numele coloanei de care avem nevoie [Coloana 1] între paranteze drepte și numărul celulei din acea coloană între paranteze. Numărul celulei va fi cu unul mai mic decât cel actual, pe care îl luăm din coloană index, respectiv.
3. Rămâne de completat celulele goale cu zero nume din celulele superioare cu comanda Transformare – Umplere – Jos (Transformare — Umplere — Jos) și ștergeți coloana care nu mai este necesară cu indici și rânduri cu nume de familie în prima coloană. Ca rezultat, obținem:
Pasul 4. Gruparea în tabele separate de către manageri
Următorul pas este gruparea rândurilor pentru fiecare manager în tabele separate. Pentru a face acest lucru, în fila Transformare, utilizați comanda Grupare prin (Transform – Grupare prin) și în fereastra care se deschide, selectați coloana Manager și operația Toate rândurile (Toate rândurile) pentru a colecta pur și simplu date fără a aplica nicio funcție de agregare la ele (suma, medie etc.). P.):
Ca rezultat, obținem tabele separate pentru fiecare manager:
Pasul 5: Transformați tabele imbricate
Acum dăm tabelele care se află în fiecare celulă a coloanei rezultate Toate datele în formă decentă.
Mai întâi, ștergeți o coloană care nu mai este necesară în fiecare tabel Manager. Folosim din nou Coloană personalizată fila Transformare (Transformare — coloană Personalizată) și următoarea formulă:
Apoi, cu o altă coloană calculată, ridicăm primul rând din fiecare tabel la titlurile:
Și, în sfârșit, efectuăm transformarea principală - desfășurarea fiecărui tabel folosind funcția M Table.UnpivotOtherColumns:
Numele regiunilor din antet vor intra într-o nouă coloană și vom obține un tabel normalizat mai restrâns, dar în același timp mai lung. Celulele goale cu zero sunt ignorate.
Scăpând de coloanele intermediare inutile, avem:
Pasul 6 Extindeți Tabelele imbricate
Rămâne să extindem toate tabelele imbricate normalizate într-o singură listă folosind butonul cu săgeți duble din antetul coloanei:
… și în sfârșit obținem ceea ce ne-am dorit:
Puteți exporta tabelul rezultat înapoi în Excel folosind comanda Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la...).
- Construiți tabele cu anteturi diferite din mai multe cărți
- Colectarea datelor din toate fișierele dintr-un folder dat
- Colectarea datelor din toate foile cărții într-un singur tabel