Përmbajtje
Formulimi i problemit
Le të shohim një zgjidhje të bukur për një nga situatat shumë standarde me të cilat përballen shumica e përdoruesve të Excel herët a vonë: ju duhet të mbledhni shpejt dhe automatikisht të dhënat nga një numër i madh skedarësh në një tabelë përfundimtare.
Supozoni se kemi dosjen e mëposhtme, e cila përmban disa skedarë me të dhëna nga qytetet e degëve:
Numri i skedarëve nuk ka rëndësi dhe mund të ndryshojë në të ardhmen. Çdo skedar ka një fletë të emërtuar Shitjetku ndodhet tabela e të dhënave:
Numri i rreshtave (urdhrave) në tabela, natyrisht, është i ndryshëm, por grupi i kolonave është standard kudo.
Detyrë: për të mbledhur të dhëna nga të gjithë skedarët në një libër me përditësimin e mëvonshëm automatik kur shtoni ose fshini skedarët ose rreshtat e qytetit në tabela. Sipas tabelës përfundimtare të konsoliduar, atëherë do të jetë e mundur të ndërtohen çdo raport, tabela kryesore, të dhëna të renditjes së filtrave, etj. Gjëja kryesore është të jeni në gjendje të grumbulloni.
Ne zgjedhim armë
Për zgjidhjen, na duhet versioni më i fundit i Excel 2016 (funksionaliteti i nevojshëm tashmë është ndërtuar në të si parazgjedhje) ose versionet e mëparshme të Excel 2010-2013 me shtesën falas të instaluar Kërkesa e energjisë nga Microsoft (shkarkoni këtu). Power Query është një mjet super fleksibël dhe super i fuqishëm për ngarkimin e të dhënave në Excel nga bota e jashtme, më pas heqjen dhe përpunimin e tyre. Power Query mbështet pothuajse të gjitha burimet ekzistuese të të dhënave – nga skedarët e tekstit te SQL dhe madje edhe Facebook 🙂
Nëse nuk keni Excel 2013 ose 2016, atëherë nuk mund të lexoni më tej (thjesht talleni). Në versionet e vjetra të Excel-it, një detyrë e tillë mund të realizohet vetëm duke programuar një makro në Visual Basic (që është shumë e vështirë për fillestarët) ose me kopjim manual monoton (që kërkon shumë kohë dhe gjeneron gabime).
Hapi 1. Importoni një skedar si mostër
Së pari, le të importojmë të dhëna nga një libër pune si shembull, në mënyrë që Excel të "marrë idenë". Për ta bërë këtë, krijoni një libër pune të ri bosh dhe…
- nëse keni Excel 2016, atëherë hapni skedën Data dhe pastaj Krijo pyetje - Nga skedari - nga libri (Të dhënat - Pyetje e re - Nga skedari - Nga Excel)
- nëse keni Excel 2010-2013 me shtesën Power Query të instaluar, atëherë hapni skedën Kërkesa e energjisë dhe zgjidhni mbi të Nga dosja – Nga libri (Nga skedari - Nga Excel)
Më pas, në dritaren që hapet, shkoni te dosja jonë me raporte dhe zgjidhni cilindo nga skedarët e qytetit (nuk ka rëndësi se cili, sepse të gjithë janë tipikë). Pas disa sekondash, duhet të shfaqet dritarja Navigator, ku duhet të zgjidhni fletën që na nevojitet (Shitjet) në anën e majtë, dhe përmbajtja e saj do të shfaqet në anën e djathtë:
Nëse klikoni në butonin në këndin e poshtëm djathtas të kësaj dritareje Shkarko (Ngarkim), atëherë tabela do të importohet menjëherë në fletë në formën e saj origjinale. Për një skedar të vetëm, kjo është e mirë, por ne duhet të ngarkojmë shumë skedarë të tillë, kështu që do të shkojmë pak më ndryshe dhe do të klikojmë butonin Korrigjim (Edit). Pas kësaj, redaktori i pyetjeve të Power Query duhet të shfaqet në një dritare të veçantë me të dhënat tona nga libri:
Ky është një mjet shumë i fuqishëm që ju lejon të "përfundoni" tabelën në pamjen që na nevojitet. Edhe një përshkrim sipërfaqësor i të gjitha funksioneve të tij do të merrte rreth njëqind faqe, por, nëse është shumë shkurt, duke përdorur këtë dritare mund të:
- filtroni të dhënat e panevojshme, linjat boshe, rreshtat me gabime
- renditni të dhënat sipas një ose më shumë kolonave
- shpëtoj nga përsëritja
- ndani tekstin ngjitës sipas kolonave (sipas kufizuesve, numrit të karaktereve, etj.)
- vendos tekstin në rend (heqja e hapësirave shtesë, korrigjimi i rastit, etj.)
- konvertoni llojet e të dhënave në çdo mënyrë të mundshme (shndërrojini numrat si teksti në numra normalë dhe anasjelltas)
- transpozoni (rrotulloni) tabelat dhe zgjeroni tabelat e kryqëzuara dydimensionale në të sheshta
- shtoni kolona shtesë në tabelë dhe përdorni formula dhe funksione në to duke përdorur gjuhën M të integruar në Power Query.
- ...
Për shembull, le të shtojmë një kolonë me emrin e tekstit të muajit në tabelën tonë, në mënyrë që më vonë të jetë më e lehtë të ndërtojmë raporte të tabelave kryesore. Për ta bërë këtë, kliko me të djathtën në titullin e kolonës datadhe zgjidhni komandën Kolona dublikatë (Kollona dublikatë), dhe pastaj kliko me të djathtën në kokën e kolonës dublikatë që shfaqet dhe zgjidhni Komandat Transformimi - Emri i muajit - i muajit:
Duhet të formohet një kolonë e re me emrat e tekstit të muajit për çdo rresht. Duke klikuar dy herë mbi një titull kolone, mund ta riemërtoni nga Kopjo Data për një më të rehatshme Muaj, p.sh.
Nëse në disa kolona programi nuk e ka njohur saktë llojin e të dhënave, atëherë mund ta ndihmoni duke klikuar në ikonën e formatit në anën e majtë të secilës kolonë:
Ju mund të përjashtoni linjat me gabime ose linja boshe, si dhe menaxherët ose klientët e panevojshëm, duke përdorur një filtër të thjeshtë:
Për më tepër, të gjitha transformimet e kryera janë të fiksuara në panelin e duhur, ku ato gjithmonë mund të kthehen (të kryqëzuara) ose të ndryshojnë parametrat e tyre (ingranazhet):
E lehtë dhe elegante, apo jo?
Hapi 2. Le ta transformojmë kërkesën tonë në një funksion
Për të përsëritur më pas të gjitha transformimet e të dhënave të bëra për çdo libër të importuar, ne duhet ta konvertojmë kërkesën tonë të krijuar në një funksion, i cili më pas do të zbatohet, nga ana tjetër, në të gjithë skedarët tanë. Për ta bërë këtë është në fakt shumë e thjeshtë.
Në Redaktorin e pyetjeve, shkoni te skeda View dhe klikoni butonin Redaktues i avancuar (Shiko - Redaktues i avancuar). Duhet të hapet një dritare ku të gjitha veprimet tona të mëparshme do të shkruhen në formën e kodit në gjuhën M. Ju lutemi vini re se shtegu i skedarit që kemi importuar për shembull është i koduar në kod:
Tani le të bëjmë disa rregullime:
Kuptimi i tyre është i thjeshtë: rreshti i parë (rruga e skedarit)=> e kthen procedurën tonë në funksion me argument rrugën e skedarit, dhe më poshtë ndryshojmë shtegun fiks në vlerën e kësaj ndryshoreje.
Të gjitha. Klikoni mbi Fund dhe duhet ta shihni këtë:
Mos kini frikë se të dhënat janë zhdukur - në fakt, gjithçka është në rregull, gjithçka duhet të duket kështu 🙂 Ne kemi krijuar me sukses funksionin tonë personal, ku i gjithë algoritmi për importimin dhe përpunimin e të dhënave mbahet mend pa u lidhur me një skedar specifik . Mbetet t'i japim një emër më të kuptueshëm (për shembull merrni të Dhënat) në panelin në të djathtë në fushë Emri dhe ju mund të korrni Faqja kryesore — Mbylle dhe shkarko (Shtëpia - Mbyll dhe ngarko). Ju lutemi vini re se shtegu i skedarit që kemi importuar për shembull është i koduar në kod. Do të ktheheni në dritaren kryesore të Microsoft Excel, por në të djathtë duhet të shfaqet një panel me lidhjen e krijuar me funksionin tonë:
Hapi 3. Mbledhja e të gjithë skedarëve
E gjithë pjesa më e vështirë është prapa, pjesa e këndshme dhe e lehtë mbetet. Shkoni te skeda Të dhënat – Krijo pyetje – Nga skedari – Nga dosja (Të dhënat - Pyetje e re - Nga skedari - Nga dosja) ose, nëse keni Excel 2010-2013, në mënyrë të ngjashme me skedën Kërkesa e energjisë. Në dritaren që shfaqet, specifikoni dosjen ku ndodhen të gjithë skedarët e qytetit tonë burim dhe klikoni OK. Hapi tjetër duhet të hapë një dritare ku do të renditen të gjithë skedarët Excel që gjenden në këtë dosje (dhe nëndosjet e saj) dhe detajet për secilën prej tyre:
Kliko Ndryshim (Edit) dhe përsëri futemi në dritaren e njohur të redaktuesit të pyetjeve.
Tani duhet të shtojmë një kolonë tjetër në tabelën tonë me funksionin tonë të krijuar, i cili do të "tërheqë" të dhënat nga çdo skedar. Për ta bërë këtë, shkoni te skeda Shto kolonë – Kolona e personalizuar (Shto kolonë - Shto kolonë të personalizuar) dhe në dritaren që shfaqet, futni funksionin tonë merrni të Dhënat, duke specifikuar për të si argument shtegun e plotë për çdo skedar:
Pas klikimit OK kolona e krijuar duhet të shtohet në tabelën tonë në të djathtë.
Tani le të fshijmë të gjitha kolonat e panevojshme (si në Excel, duke përdorur butonin e djathtë të miut - Heq), duke lënë vetëm kolonën e shtuar dhe kolonën me emrin e skedarit, sepse ky emër (më saktë qyteti) do të jetë i dobishëm që të ketë në të dhënat totale për çdo rresht.
Dhe tani "momenti wow" - klikoni në ikonën me shigjetat e veta në këndin e sipërm të djathtë të kolonës së shtuar me funksionin tonë:
… hiqni zgjedhjen Përdorni emrin origjinal të kolonës si parashtesë (Përdor emrin origjinal të kolonës si parashtesë)dhe klikoni OK. Dhe funksioni ynë do të ngarkojë dhe përpunojë të dhënat nga çdo skedar, duke ndjekur algoritmin e regjistruar dhe duke mbledhur gjithçka në një tabelë të përbashkët:
Për bukuri të plotë, mund të hiqni gjithashtu shtesat .xlsx nga kolona e parë me emrat e skedarëve - duke zëvendësuar standardin me "asgjë" (kliko me të djathtën në kokën e kolonës - zëvendësim) dhe riemëroni këtë kolonë në qytet. Dhe gjithashtu korrigjoni formatin e të dhënave në kolonën me datën.
Të gjitha! Klikoni mbi Faqja kryesore - Mbylle dhe ngarko (Shtëpia - Mbyll & Ngarko). Të gjitha të dhënat e mbledhura nga pyetja për të gjitha qytetet do të ngarkohen në fletën aktuale të Excel në formatin "Tabela e zgjuar":
Lidhja e krijuar dhe funksioni ynë i montimit nuk kanë nevojë të ruhen veçmas në asnjë mënyrë - ato ruhen së bashku me skedarin aktual në mënyrën e zakonshme.
Në të ardhmen, me çdo ndryshim në dosje (duke shtuar ose hequr qytete) ose në skedarë (duke ndryshuar numrin e rreshtave), do të mjaftojë të klikoni me të djathtën direkt në tabelë ose në pyetjen në panelin e djathtë dhe të zgjidhni komandë Përditëso & Ruaj (Rifresko) – Power Query do të “rindërtojë” të gjitha të dhënat përsëri në pak sekonda.
PS
Amendamenti. Pas përditësimeve të janarit 2017, Power Query mësoi se si t'i mbledhë vetë librat e punës në Excel, dmth. nuk ka nevojë të bëjë më një funksion të veçantë – kjo ndodh automatikisht. Kështu, hapi i dytë nga ky artikull nuk është më i nevojshëm dhe i gjithë procesi bëhet dukshëm më i thjeshtë:
- Zgjedh Krijo kërkesë – Nga skedari – Nga dosja – Zgjidh dosje – OK
- Pasi të shfaqet lista e skedarëve, shtypni Ndryshim
- Në dritaren e Redaktuesit të pyetjeve, zgjeroni kolonën Binary me një shigjetë të dyfishtë dhe zgjidhni emrin e fletës që do të merret nga çdo skedar
Dhe kjo eshte e gjitha! Këngë!
- Ridizajnimi i shtyllës së tërthortë në një të sheshtë të përshtatshme për ndërtimin e tabelave kryesore
- Ndërtimi i një grafiku të animuar me flluskë në Power View
- Makro për të mbledhur fletë nga skedarë të ndryshëm Excel në një