Razdelitev tabele na liste

Microsoft Excel ima veliko orodij za zbiranje podatkov iz več tabel (z različnih listov ali iz različnih datotek): neposredne povezave, funkcija INDIRECT (POSREDNO), dodatki Power Query in Power Pivot itd. S te strani barikade je vse videti dobro.

Če pa naletite na inverzno težavo - razporeditev podatkov iz ene tabele na različne liste - potem bo vse veliko bolj žalostno. Trenutno v arzenalu Excela na žalost ni civiliziranih vgrajenih orodij za takšno ločevanje podatkov. Zato boste morali uporabiti makro v Visual Basicu ali uporabiti kombinacijo snemalnika makrov + Power Query z malo "izpopolnitve datoteke".

Oglejmo si podrobneje, kako je to mogoče izvesti.

Formulacija problema

Kot začetne podatke imamo takšno tabelo z velikostjo več kot 5000 vrstic za prodajo:

Razdelitev tabele na liste

Naloga: razdeliti podatke iz te tabele po mestih na ločene liste te knjige. Tisti. na izhodu morate na vsakem listu dobiti samo tiste vrstice iz tabele, kjer je bila prodaja v ustreznem mestu:

Razdelitev tabele na liste

Pripravimo

Da ne bomo komplicirali kode makra in jo naredili čim bolj razumljivo, izvedimo nekaj pripravljalnih korakov.

Prvič, ustvarite ločeno iskalno tabelo, kjer bodo v enem stolpcu navedena vsa mesta, za katera želite ustvariti ločene liste. Seveda ta imenik morda ne vsebuje vseh mest, ki so prisotna v izvornih podatkih, ampak le tista, za katera potrebujemo poročila. Takšno tabelo najlažje ustvarite z ukazom Podatki – odstranite dvojnike (Podatki — odstranite dvojnike) za kopijo stolpca mesto ali funkcijo UNIK (UNIKATNO) – če imate najnovejšo različico programa Excel 365.

Ker so novi listi v Excelu privzeto ustvarjeni pred (levo od) trenutnega (prejšnjega), je tudi smiselno mesta v tem imeniku razvrstiti po padajočem vrstnem redu (od Ž do A) – potem po ustvarjanju mesto listi bodo urejeni po abecedi.

Drugič, пpretvori obe tabeli v dinamično (»pametni«), da bo z njimi lažje delati. Uporabljamo ukaz Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo) ali bližnjico na tipkovnici Ctrl+T. Na zavihku, ki se prikaže Konstruktor (Oblikovanje) pokličimo jih tablProdaji и TableCityoziroma:

Razdelitev tabele na liste

Metoda 1. Makro za razdelitev po listih

Na zavihku Napredno razvijalec (Razvijalec) kliknite na gumb Visual Basic ali uporabite bližnjico na tipkovnici druga+F11. V oknu urejevalnika makrov, ki se odpre, prek menija vstavite nov prazen modul Vstavi – Modul in tja kopirajte naslednjo kodo:

Sub Splitter() Za vsako celico v Range("таблГорода") Range("таблПродажи").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("Данные").ShowAllData End Sub

Tukaj z zanko Za vsak … Naprej izvajal prehod skozi celice imenika TableCity, kjer se za vsako mesto filtrira (metoda Samodejni filter) v prvotni prodajni tabeli in nato kopiranje rezultatov na novo ustvarjeni list. Na poti se ustvarjeni list preimenuje v isto ime mesta in na njem je vklopljeno samodejno prilagajanje širine stolpcev za lepoto.

Izdelani makro lahko zaženete v Excelu na zavihku razvijalec Gumb Makri (Razvijalec — Makri) ali bližnjico na tipkovnici druga+F8.

2. način. Ustvarite več poizvedb v Power Query

Prejšnja metoda ima kljub vsej svoji kompaktnosti in preprostosti pomembno pomanjkljivost - listi, ustvarjeni z makrom, se ne posodobijo, ko se spremenijo prvotna tabela prodaje. Če je potrebno sprotno posodabljanje, boste morali uporabiti paket VBA + Power Query ali bolje rečeno ustvariti z uporabo makra ne samo liste s statičnimi podatki, temveč posodobljene poizvedbe Power Query.

Makro je v tem primeru delno podoben prejšnjemu (ima tudi cikel Za vsak … Naprej za ponavljanje mest v imeniku), vendar znotraj zanke ne bo več filtriranja in kopiranja, temveč ustvarjanje poizvedbe Power Query in nalaganje njenih rezultatov na nov list:

Sub Splitter2() Za vsako celico v Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Vir = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategorija"", vnesite besedilo}, {""Ime"", vnesite besedilo}, {""Mesto"", vnesite besedilo}, {""Upravitelj"", vnesite besedilo}, {""Posel" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Vrstice z uporabljenim filtrom"" = Table.Se " & _ "lectRows(#""Spremenjena vrsta"", vsak ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Vrstice z uporabljenim filtrom""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Ponudnik =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

Po zagonu bomo videli iste liste po mestih, vendar jih bodo oblikovale že ustvarjene poizvedbe Power Query:

Razdelitev tabele na liste

Ob morebitnih spremembah izvornih podatkov bo dovolj, da ustrezno tabelo posodobite z desnim gumbom miške – ukaz Posodobi in shrani (Osveži) ali posodobite vsa mesta hkrati v velikem obsegu z uporabo gumba Update All tab datum (Podatki — Osveži vse).

  • Kaj so makri, kako jih ustvariti in uporabljati
  • Shranjevanje listov delovnega zvezka kot ločenih datotek
  • Zbiranje podatkov z vseh listov knjige v eno tabelo

Pustite Odgovori