Sadržaj
Microsoft Excel ima mnogo alata za prikupljanje podataka iz nekoliko tablica (s različitih listova ili iz različitih datoteka): izravne veze, funkcija INDIREKTNO (Neizravno), Power Query i Power Pivot dodaci itd. S ove strane barikade sve izgleda dobro.
Ali ako naiđete na inverzni problem - širenje podataka iz jedne tablice na različite listove - onda će sve biti mnogo tužnije. U ovom trenutku u arsenalu Excela, nažalost, nema civiliziranih ugrađenih alata za takvo odvajanje podataka. Dakle, morat ćete upotrijebiti makronaredbu u Visual Basicu ili upotrijebiti kombinaciju snimača makronaredbi + Power Queryja uz malo "pročišćavanja datoteke" nakon toga.
Pogledajmo pobliže kako se to može provesti.
Formulacija problema
Kao početne podatke imamo takvu tablicu veličine više od 5000 redaka za prodaju:
Zadatak: rasporediti podatke iz ove tablice po gradovima na posebne listove ove knjige. Oni. na izlazu morate dobiti na svakom listu samo one retke iz tablice u kojima je bila prodaja u odgovarajućem gradu:
Pripremiti
Kako ne bismo komplicirali makro kod i učinili ga što lakšim za razumijevanje, izvršimo nekoliko pripremnih koraka.
Prvi, stvoriti zasebnu tablicu pretraživanja, gdje će jedan stupac ispisati sve gradove za koje želite stvoriti zasebne listove. Naravno, ovaj imenik možda neće sadržavati sve gradove prisutne u izvornim podacima, već samo one za koje su nam potrebna izvješća. Najlakši način za izradu takve tablice je korištenje naredbe Podaci – Uklonite duplikate (Podaci — Ukloni duplikate) za kopiju stupca Grad ili funkciju JEDINSTVENO (JEDINSTVENA) – ako imate najnoviju verziju programa Excel 365.
Budući da se novi listovi u Excelu prema zadanim postavkama stvaraju prije (lijevo od) trenutnog (prethodnog), također ima smisla poredati gradove u ovom direktoriju silaznim redoslijedom (od Z do A) – tada će nakon stvaranja grad listovi će biti poredani abecednim redom.
Drugo, пpretvoriti obje tablice u dinamičke (“pametno”) kako biste lakše radili s njima. Koristimo naredbu Početna – Format kao tablica (Početna — Formatiraj kao tablicu) ili tipkovni prečac Ctrl+T. Na kartici koja se pojavi Konstruktor (Oblikovati) nazovimo ih tablProdaji и TableCity, odnosno:
Metoda 1. Makro za podjelu po listovima
Na kartici Napredno razvijač (Programer) kliknite na gumb Visual Basic ili koristite tipkovni prečac drugo+F11. U prozoru uređivača makronaredbi koji se otvori umetnite novi prazan modul kroz izbornik Umetak – Modul i tamo kopirajte sljedeći kod:
Sub Splitter() Za svaku ćeliju u Range("tablGoroda") Range("tablProdaži").AutoFilter Field:=3, Criteria1:=cell.Value Range("tablProdaži[#All]").SpecialCells(xlCellTypeVisible).Kopiraj Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Dannye").ShowAllData End Sub Ovdje s petljom Za svaki … Dalje implementiran prolaz kroz ćelije imenika TableCity, gdje se za svaki grad filtrira (metoda Autofilter) u izvornoj tablici prodaje i zatim kopiranje rezultata na novostvoreni list. Usput se kreirani list preimenuje u isto ime grada i na njemu se uključuje automatsko prilagođavanje širine stupaca radi ljepote.
Izrađenu makronaredbu možete pokrenuti u Excelu na kartici razvijač dugme makronaredbe (Razvojni programer — Makronaredbe) ili tipkovni prečac drugo+F8.
Metoda 2. Stvorite više upita u Power Queryju
Prethodna metoda, uza svu svoju kompaktnost i jednostavnost, ima značajan nedostatak - listovi stvoreni makronom se ne ažuriraju kada se izvrše promjene u izvornoj tablici prodaje. Ako je ažuriranje u hodu potrebno, tada ćete morati koristiti paket VBA + Power Query, točnije, stvoriti pomoću makronaredbe ne samo listove sa statičkim podacima, već ažurirane Power Query upite.
Makro je u ovom slučaju djelomično sličan prethodnom (također ima ciklus Za svaki … Dalje za iteraciju gradova u imeniku), ali unutar petlje više neće biti filtriranja i kopiranja, već stvaranja Power Query upita i učitavanja njegovih rezultata na novi list:
Sub Splitter2() Za svaku ćeliju u Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Izvor = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Promijenjena vrsta"" = Table.TransformColumnTypes(Izvor , {{""Kategorija"", upišite tekst}, {""Ime"", upišite tekst}, {""Grad"", upišite tekst}, {""Upravitelj"", upišite tekst}, {""Dogovor datum "", upišite datumvrijeme}, {""Cost"", upišite broj}})," & Chr(13) & "" & Chr(10) & " #""Retci s primijenjenim filtrom"" = Table.Se " & _ "lectRows(#""Promijenjena vrsta"", svaki ([Grad] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Retci s primijenjenim filtrom""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Nakon pokretanja, vidjet ćemo iste listove po gradovima, ali će ih formirati već kreirani Power Query upiti:
Uz sve promjene u izvornim podacima, bit će dovoljno ažurirati odgovarajuću tablicu desnom tipkom miša – naredba Ažurirajte i spremite (Osvježiti) ili ažurirajte sve gradove odjednom skupno pomoću gumba Update All kartica Datum (Podaci — Osvježi sve).
- Što su makronaredbe, kako ih izraditi i koristiti
- Spremanje listova radne knjige kao zasebne datoteke
- Prikupljanje podataka sa svih listova knjige u jednu tablicu