Përmbajtje
Formulimi i problemit
Si të dhëna hyrëse, kemi një skedar Excel, ku njëra nga fletët përmban disa tabela me të dhënat e shitjeve të formës së mëposhtme:
Vini re se:
- Tabelat e madhësive të ndryshme dhe me grupe të ndryshme produktesh dhe rajonesh në rreshta dhe kolona pa asnjë renditje.
- Linjat boshe mund të futen midis tabelave.
- Numri i tabelave mund të jetë çdo.
Dy supozime të rëndësishme. Supozohet se:
- Mbi çdo tabelë, në kolonën e parë, është emri i menaxherit, shitjet e të cilit ilustrojnë tabela (Ivanov, Petrov, Sidorov, etj.)
- Emrat e mallrave dhe rajoneve në të gjitha tabelat shkruhen në të njëjtën mënyrë - me saktësi të rastit.
Qëllimi përfundimtar është mbledhja e të dhënave nga të gjitha tabelat në një tabelë të sheshtë të normalizuar, e përshtatshme për analiza të mëvonshme dhe ndërtimin e një përmbledhjeje, dmth në këtë:
Hapi 1. Lidhu me skedarin
Le të krijojmë një skedar të ri bosh Excel dhe ta zgjedhim atë në skedën Data Komandë Merrni të dhëna - Nga skedari - Nga libri (Të dhënat — Nga skedari — Nga libri i punës). Specifikoni vendndodhjen e skedarit burimor me të dhënat e shitjeve dhe më pas në dritaren e navigatorit zgjidhni fletën që na nevojitet dhe klikoni në butonin Konvertoni të dhëna (Transformoni të dhënat):
Si rezultat, të gjitha të dhënat prej tij duhet të ngarkohen në redaktorin e Power Query:
Hapi 2. Pastroni plehrat
Fshi hapat e krijuar automatikisht lloji i modifikuar (Lloji i ndryshuar) и Koka të ngritura (Titujt e promovuar) dhe hiqni linjat boshe dhe linjat me totale duke përdorur një filtër null и TOTALI nga kolona e parë. Si rezultat, marrim foton e mëposhtme:
Hapi 3. Shtimi i menaxherëve
Për të kuptuar më vonë se ku janë shitjet, është e nevojshme të shtojmë një kolonë në tabelën tonë, ku në çdo rresht do të ketë një mbiemër përkatës. Për këtë:
1. Le të shtojmë një kolonë ndihmëse me numra rreshtash duke përdorur komandën Shto kolonën – kolonën e indeksit – Nga 0 (Shto kolonën — kolonën e indeksit — Nga 0).
2. Shtoni një kolonë me një formulë me komandën Shtimi i një kolone - Kolona e personalizuar (Shto kolonë - kolonë e personalizuar) dhe prezantoni ndërtimin e mëposhtëm atje:
Logjika e kësaj formule është e thjeshtë - nëse vlera e qelizës tjetër në kolonën e parë është "Produkt", atëherë kjo do të thotë se ne kemi ngecur në fillimin e një tabele të re, kështu që ne shfaqim vlerën e qelizës së mëparshme me emri i menaxherit. Përndryshe, ne nuk shfaqim asgjë, pra null.
Për të marrë qelizën mëmë me mbiemrin, fillimisht i referohemi tabelës nga hapi i mëparshëm #"Indeksi u shtua", dhe më pas specifikoni emrin e kolonës që na nevojitet [Kollona 1] në kllapa katrore dhe numri i qelizës në atë kolonë në kllapa kaçurrelë. Numri i qelizës do të jetë një më pak se ai aktual, të cilin e marrim nga kolona indeks, Respektivisht.
3. Mbetet për të mbushur qelizat boshe me null emrat nga qelizat më të larta me komandën Transformo – Plotëso – Poshtë (Transformo - Plotëso - Poshtë) dhe fshini kolonën që nuk nevojitet më me indekse dhe rreshta me mbiemra në kolonën e parë. Si rezultat, marrim:
Hapi 4. Grupimi në tabela të veçanta sipas menaxherëve
Hapi tjetër është grupimi i rreshtave për secilin menaxher në tabela të veçanta. Për ta bërë këtë, në skedën "Transformimi", përdorni grupin sipas komandës (Transform - Grupi By) dhe në dritaren që hapet, zgjidhni kolonën "Menaxheri" dhe operacionin "Të gjitha rreshtat" (Të gjitha rreshtat) për të mbledhur thjesht të dhëna pa aplikuar ndonjë funksion agregues në ato (shuma, mesatarja, etj.). P.):
Si rezultat, marrim tabela të veçanta për secilin menaxher:
Hapi 5: Transformoni tabelat e mbivendosura
Tani japim tabelat që shtrihen në secilën qelizë të kolonës që rezulton Të gjitha të dhënat në formë të mirë.
Së pari, fshini një kolonë që nuk është më e nevojshme në secilën tabelë Menaxher. Ne përdorim përsëri Kolona e personalizuar tab Transformim (Transformo — Kolona e personalizuar) dhe formulën e mëposhtme:
Pastaj, me një kolonë tjetër të llogaritur, ne ngremë rreshtin e parë në secilën tabelë në titujt:
Dhe së fundi, ne kryejmë transformimin kryesor - duke shpalosur secilën tabelë duke përdorur funksionin M Tabela.UnpivotOtherColumns:
Emrat e rajoneve nga kreu do të hyjnë në një kolonë të re dhe do të marrim një tabelë më të ngushtë, por në të njëjtën kohë, një tabelë më të gjatë të normalizuar. Qelizat e zbrazëta me null injorohen.
Duke hequr qafe kolonat e ndërmjetme të panevojshme, ne kemi:
Hapi 6 Zgjeroni Tabelat e Nested
Mbetet të zgjerohen të gjitha tabelat e mbivendosura të normalizuara në një listë të vetme duke përdorur butonin me shigjeta të dyfishta në kokën e kolonës:
… dhe më në fund arrijmë atë që donim:
Ju mund ta eksportoni tabelën që rezulton përsëri në Excel duke përdorur komandën Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia - Mbyll&Ngarko - Mbylle&Ngarko për…).
- Ndërtoni tabela me tituj të ndryshëm nga libra të shumtë
- Mbledhja e të dhënave nga të gjithë skedarët në një dosje të caktuar
- Mbledhja e të dhënave nga të gjitha fletët e librit në një tabelë