Ndërtoni tabela me tituj të ndryshëm nga libra të shumtë

Formulimi i problemit

Ne kemi disa skedarë (në shembullin tonë - 4 copë, në rastin e përgjithshëm - aq sa ju pëlqen) në një dosje Raportet:

Brenda, këto skedarë duken kështu:

Ku:

• Fleta e të dhënave që na nevojitet thirret gjithmonë Fotografitë, por mund të jetë kudo në librin e punës.
  
• Përtej fletës Fotografitë Çdo libër mund të ketë fletë të tjera.
• Tabelat me të dhëna kanë një numër të ndryshëm rreshtash dhe mund të fillojnë me një rresht të ndryshëm në fletën e punës.
• Emrat e të njëjtave kolona në tabela të ndryshme mund të ndryshojnë (për shembull, Sasia = Sasia = Sasia).
• Kolonat në tabela mund të renditen në një mënyrë të ndryshme.

Detyrë: mbledhni të dhënat e shitjeve nga të gjithë skedarët nga fleta Fotografitë në një tabelë të përbashkët për të ndërtuar më pas një përmbledhje ose ndonjë analizë tjetër mbi të.

Hapi 1. Përgatitja e një drejtorie me emrat e kolonave

Gjëja e parë që duhet të bëni është përgatitja e një libri referimi me të gjitha opsionet e mundshme për emrat e kolonave dhe interpretimin e tyre të saktë:

Ne e konvertojmë këtë listë në një tabelë dinamike "të zgjuar" duke përdorur butonin Format si tabelë në skedë Fillimi (Shtëpia - Formatoni si tabelë) ose shkurtore të tastierës Ctrl+T dhe ngarkojeni në Power Query me komandën Të dhënat – Nga tabela/vargu (Të dhënat - nga tabela/vargu). Në versionet e fundit të Excel, ai është riemërtuar në Me gjethe (Nga fleta).

Në dritaren e redaktuesit të pyetjeve të Power Query, ne tradicionalisht fshijmë hapin Lloji i ndryshuar dhe shtoni një hap të ri në vend të tij duke klikuar në butonin f_xnë shiritin e formulës (nëse nuk është i dukshëm, atëherë mund ta aktivizoni në skedën Rishikim) dhe futni formulën atje në gjuhën e integruar të Power Query M:

=Table.ToRows(Burimi)

Kjo komandë do të konvertojë atë të ngarkuar në hapin e mëparshëm Burim tabela e referencës në një listë të përbërë nga lista të mbivendosura (Lista), secila prej të cilave, nga ana tjetër, është një palë vlerash Ishte-u bë nga një rresht:

Do të na duhen këto lloj të dhënash pak më vonë, kur të riemërtojmë në masë titujt nga të gjitha tabelat e ngarkuara.

Pasi të keni përfunduar konvertimin, zgjidhni komandat Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… dhe llojin e importit Thjesht krijoni një lidhje (Faqja kryesore - Mbyll&Ngarko - Mbylle&Ngarko te... - Krijo vetëm lidhje) dhe kthehuni në Excel.

Hapi 2. Ne ngarkojmë gjithçka nga të gjithë skedarët siç është

Tani le të ngarkojmë përmbajtjen e të gjithë skedarëve tanë nga dosja - tani për tani, siç është. Zgjedhja e ekipeve Të dhënat – Merr të dhëna – Nga skedari – Nga dosja (Të dhënat - Merr të dhëna - Nga skedari - Nga dosja) dhe më pas dosjen ku janë librat tanë burimorë.

Në dritaren e shikimit, klikoni Kthej (Transformo) or Ndryshim (Edit):

Dhe pastaj zgjeroni përmbajtjen e të gjithë skedarëve të shkarkuar (Binar) butonin me shigjeta të dyfishta në titullin e kolonës Përmbajtja:

Power Query në shembullin e skedarit të parë (Vostok.xlsx) do të na pyesë emrin e fletës që duam të marrim nga çdo libër pune – zgjidhni Fotografitë dhe shtypni OK:

Pas kësaj (në fakt), do të ndodhin disa ngjarje që nuk janë të dukshme për përdoruesin, pasojat e të cilave janë qartë të dukshme në panelin e majtë:

1. Power Query do të marrë skedarin e parë nga dosja (do ta kemi atë Vostok.xlsx - Shiko Shembull skedari) si shembull dhe importon përmbajtjen e tij duke krijuar një pyetje Konvertoni skedarin e mostrës. Kjo pyetje do të ketë disa hapa të thjeshtë si p.sh Burim (qasja në skedar) lundrim (përzgjedhja e fletës) dhe mundësisht ngritja e titujve. Kjo kërkesë mund të ngarkojë të dhëna vetëm nga një skedar specifik Vostok.xlsx.
   
2. Bazuar në këtë kërkesë, do të krijohet funksioni i lidhur me të Konvertoni skedarin (tregohet nga një ikonë karakteristike f_x), ku skedari burimor nuk do të jetë më një konstante, por një vlerë e ndryshueshme - një parametër. Kështu, ky funksion mund të nxjerrë të dhëna nga çdo libër që ne rrëshqasim në të si argument.
   
3. Funksioni do të aplikohet me radhë në çdo skedar (Binar) nga kolona Përmbajtja – hapi është përgjegjës për këtë Thirrni funksionin e personalizuar në pyetjen tonë që shton një kolonë në listën e skedarëve Konvertoni skedarin me rezultatet e importit nga çdo libër pune:

   

4. Kolonat shtesë hiqen.
5. Përmbajtja e tabelave të mbivendosur është zgjeruar (hapi Kolona e zgjeruar e tabelës) – dhe ne shohim rezultatet përfundimtare të mbledhjes së të dhënave nga të gjithë librat:

   
   

Hapi 3. Lëmim

Pamja e mëparshme e ekranit tregon qartë se montimi i drejtpërdrejtë "siç është" doli të ishte i cilësisë së dobët:

• Kolonat janë të kundërta.
• Shumë rreshta shtesë (bosh dhe jo vetëm).
• Titujt e tabelave nuk perceptohen si tituj dhe janë të përzier me të dhëna.
  

Ju mund t'i rregulloni të gjitha këto probleme shumë lehtë - thjesht shkulni pyetjen Convert Sample File. Të gjitha rregullimet që i bëjmë do të bien automatikisht në funksionin shoqërues të skedarit Convert, që do të thotë se ato do të përdoren më vonë kur importoni të dhëna nga çdo skedar.

Duke hapur një kërkesë Konvertoni skedarin e mostrës, shtoni hapa për të filtruar rreshtat e panevojshëm (për shembull, sipas kolonës Column2) dhe ngritja e titujve me butonin Përdorni rreshtin e parë si kokë (Përdorni rreshtin e parë si kokë). Tabela do të duket shumë më mirë.

Në mënyrë që kolonat nga skedarë të ndryshëm të vendosen automatikisht nën njëra-tjetrën më vonë, ato duhet të emërtohen njësoj. Ju mund të kryeni një riemërtim të tillë masiv sipas një drejtorie të krijuar më parë me një rresht të kodit M. Le të shtypim butonin përsëri f_x në shiritin e formulave dhe shtoni një funksion për të ndryshuar:

= Table.RenameColumns(#"Titujt e ngritur", Headers, MissingField.Ignore)

Ky funksion merr tabelën nga hapi i mëparshëm Koka të ngritura dhe riemëron të gjitha kolonat në të sipas listës së kërkimit të mbivendosur Titujt. Argumenti i tretë Fusha e humbur.Injoroje është e nevojshme në mënyrë që në ato tituj që janë në drejtori, por nuk janë në tabelë, të mos ndodhë një gabim.

Në fakt, kjo është e gjitha.

Duke iu kthyer kërkesës Raportet ne do të shohim një pamje krejtësisht të ndryshme - shumë më e bukur se ajo e mëparshme:

• Çfarë është Power Query, Power Pivot, Power BI dhe pse i duhen një përdoruesi të Excel-it
  
• 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ë