sadržaj
Microsoft Excel ima mnogo alata za prikupljanje podataka iz nekoliko tabela (sa različitih listova ili iz različitih datoteka): direktne veze, funkcije INDIREKTNO (INDIREKTNO), 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 tabele u različite listove – onda će sve biti mnogo tužnije. U ovom trenutku, nažalost, u arsenalu Excela ne postoje civilizirani ugrađeni alati za takvo razdvajanje podataka. Dakle, moraćete da koristite makro u Visual Basicu, ili da koristite kombinaciju snimača makroa + Power Query sa malim „pročišćavanjem datoteke“.
Pogledajmo bliže kako se to može implementirati.
Formulacija problema
Za početne podatke imamo takvu tablicu veličine više od 5000 redova za prodaju:
Zadatak: rasporediti podatke iz ove tabele po gradovima na posebnim listovima ove knjige. One. na izlazu morate na svaki list dobiti samo one redove iz tabele u kojima je bila prodaja u odgovarajućem gradu:
Pripremite se
Kako ne bismo komplicirali makro kod i učinili ga što lakšim za razumijevanje, izvršimo nekoliko pripremnih koraka.
Prvo, kreirajte zasebnu tabelu za pretraživanje, gdje će u jednoj koloni biti navedeni svi gradovi za koje želite da kreirate zasebne listove. Naravno, ovaj direktorij možda ne sadrži sve gradove koji su prisutni u izvornim podacima, već samo one za koje su nam potrebni izvještaji. Najlakši način za kreiranje takve tablice je korištenje naredbe Podaci – Uklonite duplikate (Podaci — Ukloni duplikate) za kopiju kolone grad ili funkciju JEDINSTVENO (JEDINSTVENO) – ako imate najnoviju verziju programa Excel 365.
Budući da se novi listovi u Excel-u prema zadanim postavkama kreiraju prije (lijevo od) trenutnog (prethodnog), također ima smisla sortirati gradove u ovom direktoriju u opadajućem redoslijedu (od Z do A) – zatim nakon kreiranja, grad listovi će biti raspoređeni po abecednom redu.
Drugo, пpretvoriti obje tablice u dinamičke (“pametno”) kako bi vam olakšao rad s njima. Koristimo naredbu Početna – Formatirajte kao tabelu (Početna — Format kao tabela) ili prečica na tastaturi Ctrl+T. Na kartici koja se pojavi konstruktor (Dizajn) nazovimo ih tablProdaji и TableCity, odnosno:
Metoda 1. Makro za podjelu po listovima
Na kartici Napredno Developer (programer) kliknite na dugme Visual Basic ili koristite prečicu na tastaturi alt+F11. U prozoru uređivača makroa koji se otvori, umetnite novi prazan modul kroz meni Umetak – Modul i tamo kopirajte sljedeći kod:
Sub Splitter() Za svaku ćeliju u opsegu("tablGoroda") Range("tablProdaži").AutoFilter Field:=3, Criteria1:=cell.Value Range("tablProdaži[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Dannye").ShowAllData End Sub Ovdje sa petljom Za svaki … Sljedeći implementirao prolaz kroz ćelije direktorija TableCity, gdje se za svaki grad filtrira (metod AutoFilter) u originalnoj tablici prodaje i zatim kopirati rezultate u novokreirani list. Usput, kreirani list se preimenuje u isto ime grada i na njemu je uključeno automatsko prilagođavanje širine kolona radi ljepote.
Na kartici možete pokrenuti kreirani makro u Excelu Developer dugme Macros (Programer — Makroi) ili prečica na tastaturi alt+F8.
Metod 2. Kreirajte više upita u Power Queryju
Prethodna metoda, uz svu svoju kompaktnost i jednostavnost, ima značajan nedostatak - listovi kreirani makroom se ne ažuriraju kada se izvrše promjene u originalnoj tablici prodaje. Ako je potrebno ažuriranje u hodu, tada ćete morati koristiti VBA + Power Query paket, odnosno kreirati pomoću makroa 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 … Sljedeći za iteraciju po gradovima u direktoriju), ali unutar petlje više neće biti filtriranja i kopiranja, već kreiranja Power Query upita i učitavanja njegovih rezultata na novi list:
Sub Splitter2() Za svaku ćeliju u rasponu("tabela gradova") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Izvor , {{""Kategorija"", upišite tekst}, {""Ime"", upišite tekst}, {""Grad"", upišite tekst}, {""Menadžer"", upišite tekst}, {""Posao datum "", tip datetime}, {""Cost"", tip broj}})," & Chr(13) & "" & Chr(10) & " #""Redovi sa primijenjenim filterom"" = Table.Se " & _ "lectRows(#""Promijenjen tip"", svaki ([Grad] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Redovi sa primijenjenim filterom""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Dobavljač =Microsoft.Mashup.OleDb.1; Izvor podataka=$Workbook$;Location=" & cell.Value & ";Proširena svojstva=""""" _ , Odredište:=Raspon("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDelete. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = Next cell.Value Subalue Nakon pokretanja, vidjet ćemo iste listove po gradovima, ali će ih formirati već kreirani Power Query upiti:
Uz bilo kakve promjene u izvornim podacima, bit će dovoljno ažurirati odgovarajuću tabelu desnim dugmetom miša – naredbom Ažurirajte i sačuvajte (Osvježiti) ili ažurirajte sve gradove odjednom koristeći dugme Update All tabulator podaci (Podaci — Osvježi sve).
- Šta su makroi, kako ih kreirati i koristiti
- Čuvanje listova radne sveske kao zasebnih datoteka
- Prikupljanje podataka sa svih listova knjige u jednu tabelu