Edukiak
Microsoft Excel-ek hainbat taulatako datuak biltzeko tresna ugari ditu (fitxa ezberdinetatik edo fitxategi ezberdinetatik): lotura zuzenak, funtzioa ZEHARKOAK (ZEHARKA), Power Query eta Power Pivot gehigarriak, etab. Barrikadaren alde honetatik denak itxura ona du.
Baina alderantzizko arazo batekin topo egiten baduzu –taula batetik datuak orri ezberdinetara zabaltzea– orduan dena askoz tristeagoa izango da. Momentuz, ez dago Excel-en arsenalean datuak bereizteko tresna zibilizaturik, zoritxarrez. Beraz, makro bat erabili beharko duzu Visual Basic-en, edo makro grabagailua + Power Query konbinazioa erabili ondoren "fitxategi finketa" pixka batekin.
Ikus dezagun nola gauzatu daitekeen hau.
Arazoaren formulazioa
Hasierako datu gisa salmentarako 5000 errenkada baino gehiagoko tamaina duen taula bat dugu:
Egitekoa: taula honetako datuak hirika banatzea liburu honetako fitxa bereizietan. Horiek. irteeran, orri bakoitzean salmenta dagokion hirian zegoen taulako errenkadak bakarrik jaso behar dituzu:
Prestatu
Makro-kodea ez zaildu eta ahalik eta errazen ulertzeko, egin ditzagun prestaketa-urrats pare bat.
Lehenik eta behin, sortu bilaketa-taula bereizi bat, non zutabe bakarrean fitxa bereiziak sortu nahi dituzun hiri guztiak zerrendatuko ditu. Jakina, baliteke direktorio honek iturriko datuetan dauden hiri guztiak ez edukitzea, txostenak behar ditugunak baizik. Taula hori sortzeko modurik errazena komandoa erabiltzea da Datuak - Kendu bikoiztuak (Datuak — Kendu bikoiztuak) zutabeen kopiarako hiria edo funtzioa UNIK (BAKARRA) – Excel 365-en azken bertsioa baduzu.
Excel-en orri berriak lehenespenez oraingoaren (aurreko) aurretik (ezkerrean) sortzen direnez, zentzuzkoa da direktorio honetako hiriak beheranzko ordenan ordenatzea (Z-tik A-ra) - gero, sortu ondoren, hiria. orriak alfabetikoki antolatuko dira.
Bigarrenik, пBi taulak dinamiko bihurtu (“adimentsua”) haiekin lan egitea errazteko. Komandoa erabiltzen dugu Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu) edo teklatuko lasterbidea Ctrl+T. Agertzen den fitxan Constructor (Diseinua) dei diezaiegun tablProdaji и TableCityhurrenez hurren:
1. metodoa. Orrialdeka zatitzeko makroa
Aurreratua fitxan developer (Garatzailea) egin klik botoian Visual Basic edo erabili teklatuko lasterbidea Alt+F11. Irekitzen den makro editorearen leihoan, sartu modulu huts berri bat menuan Txertatu – Modulua eta kopiatu bertan kodea:
Azpibanatzailea () Barrutian dagoen gelaxka bakoitzeko ("таблГорода") Barrutia("таблПродажи"). Iragazki automatikoko eremua:=3, Criteria1:=gelaxka.Balioen barrutia("таблПродажи[#Guztiak]").SpecialCells(xlVisibleellCopyy). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Hurrengo gelaxka Worksheets("Данные").ShowAllData End Sub Hemen begizta batekin Bakoitzerako… Hurrengoa direktorioko gelaxketatik igarotzea inplementatu du TableCity, non hiri bakoitzeko iragazten den (metodoa Iragazki automatikoa) jatorrizko salmenta-taulan eta, ondoren, emaitzak kopiatu sortu berri den fitxan. Bidean, sortutako orria hiriaren izen bereko izena hartzen du eta zutabeen zabalera automatikoki egokitzeko edertasuna aktibatuta dago.
Excel-en sortutako makroa exekutatu dezakezu fitxan developer botoia Makroak (Garatzailea — Makroak) edo teklatuko lasterbidea Alt+F8.
2. metodoa. Sortu hainbat kontsulta Power Query-n
Aurreko metodoak, trinkotasun eta sinpletasun osoz, eragozpen nabarmena du: makroak sortutako orriak ez dira eguneratzen jatorrizko salmenta-taulan aldaketak egiten direnean. Abisuan eguneratzea beharrezkoa bada, orduan VBA + Power Query sorta erabili beharko duzu, edo hobeto esanda, makro bat erabiliz sortu ez datu estatikoak dituzten orriak, baizik eta Power Query kontsulta eguneratuak.
Kasu honetan makroa aurrekoaren antzekoa da partzialki (ziklo bat ere badu Bakoitzerako… Hurrengoa direktorioko hirietan zehar errepikatzeko), baina begizta barruan ez da gehiago iragazten eta kopiatuko, baizik eta Power Query kontsulta bat sortu eta bere emaitzak orri berri batera igoko dira:
Azpi Splitter2() Barrutiko gelaxka bakoitzeko ("Hiri taula") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "utzi" & Chr(13) & "" & Chr(10) & " Iturria = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Edukia]," & Chr(13) & "" & Chr(10) & " #""Mota aldatua"" = Table.TransformColumnTypes(Iturria , {{""Kategoria"", idatzi testua}, {""Izena"", idatzi testua}, {""Hiria", idatzi testua}, {""Kudeatzailea"", idatzi testua}, {""Deal data "", idatzi datetime}, {""Cost"", idatzi zenbakia}})," & Chr(13) & "" & Chr(10) & " #""Iragazkia aplikatutako errenkadak"" = Taula.Se " & _ "lectRows(#""Aldatu da mota"", bakoitza ([Hiria] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Iragazkia aplikatutako errenkadak""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Hornitzailea =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Hedatutako Propietateak=""""" _ , Helmuga:=Barrutia("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells =GordePasword . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Fresh BackgroundQuery:=False Amaiera ActiveSheet.Name = cell.Value Hurrengo gelaxka End Sub Abian jarri ondoren, orri berdinak ikusiko ditugu hirika, baina dagoeneko sortutako Power Query kontsultak osatuko dituzte:
Iturburu-datuen edozein aldaketarekin, nahikoa izango da dagokion taula eguneratzea saguaren eskuineko botoiarekin - komandoa Eguneratu eta Gorde (Freskatu) edo eguneratu hiri guztiak aldi berean botoia erabiliz Eguneratu guztiak fitxa Data (Datuak — Freskatu guztiak).
- Zer dira makroak, nola sortu eta nola erabili
- Lan-koadernoko orriak fitxategi bereizi gisa gordetzea
- Liburuko fitxa guztietako datuak taula batean biltzea