Sadržaj
Formulacija problema
Kao ulazni podatak imamo Excel datoteku, gdje se na jednom od listova nalazi nekoliko tablica s podacima o prodaji sljedećeg oblika:
Imajte na umu da:
- Tablice različitih veličina i s različitim skupovima proizvoda i regija u recima i stupcima bez sortiranja.
- Između tablica mogu se umetnuti prazni redovi.
- Broj stolova može biti bilo koji.
Dvije važne pretpostavke. Pretpostavlja se da:
- Iznad svake tablice u prvom stupcu nalazi se ime menadžera čiju prodaju tabela prikazuje (Ivanov, Petrov, Sidorov itd.)
- Nazivi roba i regija u svim tablicama pišu se na isti način – s velikim i malim slovima.
Krajnji cilj je sakupiti podatke iz svih tablica u jednu ravnu normaliziranu tablicu, pogodnu za naknadnu analizu i izradu sažetka, tj. u ovu:
Korak 1. Povežite se s datotekom
Kreirajmo novu praznu Excel datoteku i odaberite je na kartici Datum naredba Dobivanje podataka – iz datoteke – iz knjige (Podaci — Iz datoteke — Iz radne knjige). Odredite mjesto izvorne datoteke s podacima o prodaji, a zatim u prozoru navigatora odaberite list koji nam je potreban i kliknite na gumb Pretvori podatke (Transformacija podataka):
Kao rezultat toga, svi podaci iz njega trebali bi se učitati u uređivač Power Query:
Korak 2. Očistite smeće
Izbrišite automatski generirane korake modificirani tip (Promijenjena vrsta) и Povišena zaglavlja (Promovirana zaglavlja) i riješite se praznih redaka i redaka s ukupnim zbrojevima pomoću filtra nula и UKUPNO po prvom stupcu. Kao rezultat toga, dobivamo sljedeću sliku:
Korak 3. Dodavanje upravitelja
Da bismo kasnije razumjeli gdje je čija prodaja, potrebno je dodati stupac u našu tablicu, gdje će u svakom retku biti odgovarajuće prezime. Za ovo:
1. Dodajmo pomoćni stupac s brojevima redaka pomoću naredbe Dodaj stupac – stupac indeksa – od 0 (Dodaj stupac — stupac indeks — od 0).
2. Naredbom dodajte stupac s formulom Dodavanje stupca – prilagođeni stupac (Dodaj stupac — prilagođeni stupac) i tamo uvedite sljedeću konstrukciju:
Logika ove formule je jednostavna – ako je vrijednost sljedeće ćelije u prvom stupcu “Proizvod”, onda to znači da smo naletjeli na početak nove tablice, pa prikazujemo vrijednost prethodne ćelije s ime upravitelja. U suprotnom, ne prikazujemo ništa, tj. null.
Da bismo dobili nadređenu ćeliju s prezimenom, prvo se pozivamo na tablicu iz prethodnog koraka #”Indeks dodan”, a zatim navedite naziv stupca koji nam je potreban [Stupac 1] u uglatim zagradama i broj ćelije u tom stupcu u vitičastim zagradama. Broj ćelije bit će za jedan manji od trenutnog, koji uzimamo iz stupca indeks, Respektivno.
3. Ostaje popuniti prazna polja nula imena iz viših ćelija s naredbom Transformiraj – Ispuni – Dolje (Transformacija — Ispuna — Dolje) i izbrišite nepotrebni stupac s indeksima i retke s prezimenima u prvom stupcu. Kao rezultat toga dobivamo:
Korak 4. Grupiranje u zasebne tablice po menadžerima
Sljedeći korak je grupiranje redaka za svakog upravitelja u zasebne tablice. Da biste to učinili, na kartici Transformacija upotrijebite naredbu Grupiraj po (Transform – Group By) iu prozoru koji se otvori odaberite stupac Upravitelj i operaciju Svi redovi (Svi redovi) kako biste jednostavno prikupili podatke bez primjene bilo kakve funkcije agregiranja na njih (zbroj, prosjek itd.). P.):
Kao rezultat toga, dobivamo zasebne tablice za svakog upravitelja:
Korak 5: Transformirajte ugniježđene tablice
Sada dajemo tablice koje leže u svakoj ćeliji dobivenog stupca Svi podaci u pristojnom stanju.
Prvo izbrišite stupac koji više nije potreban u svakoj tablici Voditelj. Ponovno koristimo Prilagođeni stupac kartica transformacija (Transformacija — prilagođeni stupac) i sljedeća formula:
Zatim, s drugim izračunatim stupcem, podižemo prvi red u svakoj tablici do naslova:
I na kraju, izvodimo glavnu transformaciju - rasklapanje svake tablice pomoću M-funkcije Table.UnpivotOtherColumns:
Nazivi regija iz zaglavlja će ići u novi stupac i dobit ćemo užu, ali ujedno i dužu normaliziranu tablicu. Prazne ćelije s nula ignoriraju se.
Riješimo se nepotrebnih srednjih stupaca, imamo:
Korak 6 Proširite ugniježđene tablice
Preostaje proširiti sve normalizirane ugniježđene tablice u jedan popis pomoću gumba s dvostrukim strelicama u zaglavlju stupca:
...i konačno smo dobili ono što smo htjeli:
Rezultirajuću tablicu možete izvesti natrag u Excel pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…).
- Izradite tablice s različitim zaglavljima iz više knjiga
- Prikupljanje podataka iz svih datoteka u određenoj mapi
- Prikupljanje podataka sa svih listova knjige u jednu tablicu