Përmbajtje
Microsoft Excel ka shumë mjete për mbledhjen e të dhënave nga disa tabela (nga fletë të ndryshme ose nga skedarë të ndryshëm): lidhje të drejtpërdrejta, funksion INDIREKT (Indirekt), shtesat Power Query dhe Power Pivot, etj. Nga kjo anë e barrikadës, gjithçka duket mirë.
Por nëse hasni në një problem të kundërt – përhapja e të dhënave nga një tabelë në fletë të ndryshme – atëherë gjithçka do të jetë shumë më e trishtuar. Për momentin, nuk ka mjete të civilizuara të integruara për një ndarje të tillë të të dhënave në arsenalin e Excel, për fat të keq. Kështu që do t'ju duhet të përdorni një makro në Visual Basic, ose të përdorni kombinimin makro regjistrues + Power Query me pak "përpunim skedari" më pas.
Le të hedhim një vështrim më të afërt se si mund të zbatohet kjo.
Formulimi i problemit
Ne kemi si të dhëna fillestare një tabelë të tillë me madhësi më shumë se 5000 rreshta për shitje:
Detyrë: të shpërndani të dhënat nga kjo tabelë sipas qytetit në fletë të veçanta të këtij libri. Ato. në dalje, duhet të merrni në secilën fletë vetëm ato rreshta nga tabela ku shitja ishte në qytetin përkatës:
Përgatit
Për të mos e komplikuar kodin makro dhe për ta bërë atë sa më të lehtë për t'u kuptuar, le të kryejmë disa hapa përgatitor.
Së pari, krijoni një tabelë të veçantë kërkimi, ku një kolonë e vetme do të listojë të gjitha qytetet për të cilat dëshironi të krijoni fletë të veçanta. Sigurisht, ky direktori mund të mos përmbajë të gjitha qytetet e pranishme në të dhënat burimore, por vetëm ato për të cilat na duhen raporte. Mënyra më e lehtë për të krijuar një tabelë të tillë është përdorimi i komandës Të dhënat - Hiqni dublikatat (Të dhënat - Hiqni dublikatat) për kopjen e kolonës qytet ose funksion UNIK (UNIK) – nëse keni versionin më të fundit të Excel 365.
Meqenëse fletët e reja në Excel krijohen si parazgjedhje përpara (në të majtë të) asaj aktuale (të mëparshme), ka gjithashtu kuptim që qytetet në këtë drejtori të renditen në rend zbritës (nga Z në A) - pastaj pas krijimit, qyteti fletët do të renditen sipas alfabetit.
Së dyti, пkonvertoni të dyja tabelat në dinamike (“i zgjuar”) për ta bërë më të lehtë punën me ta. Ne përdorim komandën Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë) ose shkurtore të tastierës Ctrl+T. Në skedën që shfaqet konstruktor (Dizajn) le t'i thërrasim tablProdaji и TableCity, përkatësisht:
Metoda 1. Makro për ndarjen me fletë
Në skedën Avancuar zhvillues (Zhvilluesi) klikoni në butonin Visual Basic ose përdorni shkurtoren e tastierës Ndalet+F11. Në dritaren e redaktorit makro që hapet, futni një modul të ri bosh përmes menysë Insert – Moduli dhe kopjoni kodin e mëposhtëm atje:
Nën ndarës() Për çdo qelizë në intervalin ("таблГорода") Range("таблПродажи").Fusha e filtrit automatik:=3, Kriteri1:=qeliza.Rapsi i vlerës ("таблПродажи[#Të gjitha]").SpecialCells(xlCellTypeVisible). Kopjo Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Fletët e punës së qelizës tjetër("Данные").ShowAllData Fund Sub Këtu me një lak Për Secilin … Tjetër zbatoi kalimin nëpër qelizat e drejtorisë TableCity, ku për çdo qytet filtrohet (metoda Filtri automatik) në tabelën origjinale të shitjeve dhe më pas kopjoni rezultatet në fletën e krijuar rishtazi. Gjatë rrugës, fleta e krijuar riemërohet në të njëjtin emër të qytetit dhe mbi të ndizet automatikisht përshtatja e gjerësisë së kolonave për bukuri.
Mund të ekzekutoni makronë e krijuar në Excel në skedën zhvillues buton macros (Zhvilluesi - Makro) ose shkurtore të tastierës Ndalet+F8.
Metoda 2. Krijo pyetje të shumta në Power Query
Metoda e mëparshme, me gjithë kompaktësinë dhe thjeshtësinë e saj, ka një pengesë të rëndësishme - fletët e krijuara nga makro nuk përditësohen kur bëhen ndryshime në tabelën origjinale të shitjeve. Nëse përditësimi i shpejtë është i nevojshëm, atëherë do të duhet të përdorni paketën VBA + Power Query, ose më saktë, të krijoni duke përdorur një makro jo vetëm fletë me të dhëna statike, por pyetje të përditësuara të Power Query.
Makroja në këtë rast është pjesërisht e ngjashme me atë të mëparshme (ajo gjithashtu ka një cikël Për Secilin … Tjetër për të përsëritur mbi qytetet në drejtori), por brenda ciklit nuk do të ketë më filtrim dhe kopjim, por krijimi i një pyetjeje Power Query dhe ngarkimi i rezultateve të tij në një fletë të re:
Nën Splitter2() Për çdo qelizë në Range ("Tabela e qytetit") ActiveWorkbook.Queries.Shto Emrin:=cell.Vlera, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Burimi = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Përmbajtja]," & Chr(13) & "" & Chr(10) & " #""Lloji i ndryshuar"" = Table.TransformColumnTypes(Burimi , {{""Kategoria"", shkruani tekstin}, {""Emri"", shkruani tekstin}, {""Qyteti"", shkruani tekstin}, {""Menaxheri"", shkruani tekstin}, {""Marrëveshje" data "", shkruani datetime}, {""Cost"", shkruani numrin}})," & Chr(13) & "" & Chr(10) & " #""Rreshtat me filtrin e aplikuar" = Tabela.Shiko " & _ "lectRows(#""Lloji i ndryshuar"", secili ([Qyteti] = """ & qeliza. Vlera & """))" & Chr(13) & "" & Chr(10) & "në " & Chr(13) & "" & Chr(10) & " #""Rreshtat me filtër të aplikuar""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Burimi:= _ "OLEDB; Ofruesi =Microsoft.Mashup.OleDb.1;Burimi i të dhënave=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destinacioni:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & qeliza.Vlera & "]") .Numrat e rreshtit = False .FillAdjacentFormulas = False .PreserveFormatting = E vërtetë .RefreshOnFileOpen = False .BackgroundQuery = E vërtetë .RefreshStyle = xlInsertwordDeleteseells. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Rifresko BackgroundQuery:=Fundi False Me ActiveSheet Fund.Name = qeliza tjetër. Pas nisjes së tij, ne do të shohim të njëjtat fletë sipas qytetit, por pyetjet e krijuara tashmë të Power Query do t'i formojnë ato:
Me çdo ndryshim në të dhënat e burimit, do të jetë e mjaftueshme të përditësoni tabelën përkatëse me butonin e djathtë të miut - komandën Përditëso & Ruaj (Rifresko) ose përditësoni të gjitha qytetet menjëherë në masë duke përdorur butonin Përditëso të gjitha tab Data (Të dhënat - Rifresko të gjitha).
- Çfarë janë makro, si t'i krijoni dhe përdorni ato
- Ruajtja e fletëve të librit të punës si skedarë të veçantë
- Mbledhja e të dhënave nga të gjitha fletët e librit në një tabelë