Dividir una taula en fulls

Microsoft Excel disposa de moltes eines per recollir dades de diverses taules (de diferents fulls o de diferents fitxers): enllaços directes, funció INDIRECTE (INDIRECTE), complements de Power Query i Power Pivot, etc. Des d'aquest costat de la barricada, tot es veu bé.

Però si us trobeu amb un problema invers: estendre les dades d'una taula a diferents fulls, aleshores tot serà molt més trist. En aquests moments, malauradament, no hi ha eines integrades civilitzades per a aquesta separació de dades a l'arsenal d'Excel. Per tant, haureu d'utilitzar una macro a Visual Basic, o utilitzar la combinació de la gravadora de macros + Power Query amb una mica de "perfeccionament de fitxers" després.

Vegem més de prop com es pot implementar això.

Formulació del problema

Tenim com a dades inicials una taula amb una mida de més de 5000 files per a vendes:

Tasca: distribuir les dades d'aquesta taula per ciutat en fulls separats d'aquest llibre. Aquells. a la sortida, només heu d'incloure a cada full les files de la taula on es va fer la venda a la ciutat corresponent:

Preparar

Per no complicar el codi macro i fer-lo tan fàcil d'entendre com sigui possible, fem un parell de passos preparatoris.

En primer lloc, la crear una taula de cerca separada, on una sola columna enumerarà totes les ciutats per a les quals voleu crear fulls separats. Per descomptat, aquest directori pot no contenir totes les ciutats presents a les dades font, sinó només aquelles per a les quals necessitem informes. La manera més senzilla de crear aquesta taula és utilitzar l'ordre Dades: elimina els duplicats (Dades — Elimina els duplicats) per a la còpia de la columna ciutat o funció UNIK (ÚNIC) - si teniu la darrera versió d'Excel 365.

Com que els nous fulls a Excel es creen de manera predeterminada abans (a l'esquerra de) de l'actual (anterior), també té sentit ordenar les ciutats d'aquest directori en ordre descendent (de Z a A), i després de la creació, la ciutat. Els fulls s'ordenaran alfabèticament.

En segon lloc, la пconvertir les dues taules a dinàmiques (“intel·ligent”) per facilitar el treball amb ells. Utilitzem l'ordre Inici – Format com a taula (Inici — Format com a taula) o drecera de teclat Ctrl+T. A la pestanya que apareix constructor (Disseny) cridem-los tablProdaji и TableCity, respectivament:

Mètode 1. Macro per a la divisió per fulls

A la pestanya Avançat revelador (Desenvolupador) feu clic al botó Visual Basic o utilitzeu la drecera del teclat Alt+F11. A la finestra de l'editor de macros que s'obre, inseriu un mòdul buit nou a través del menú Inserir – Mòdul i copieu-hi el codi següent:

Sub Splitter() per a cada cel·la dins l'interval("таблГорода") Range("таблПродажи"). Camp de filtre automàtic:=3, Criteria1:=cell.Interval de valors("таблПродажи[#All]").SpecialCells(xlVisibleell).Copy. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

Aquí amb un bucle Per a cada… A continuació implementat el pas per les cel·les del directori TableCity, on es filtra per a cada ciutat (mètode Filtre automàtic) a la taula de vendes original i després copiant els resultats al full de nova creació. Al llarg del camí, el full creat es rebateja amb el mateix nom de la ciutat i s'activa l'ajust automàtic de l'amplada de les columnes per a la bellesa.

Podeu executar la macro creada a Excel a la pestanya revelador botó Macros (Desenvolupador - Macros) o drecera de teclat Alt+F8.

Mètode 2. Creeu diverses consultes a Power Query

El mètode anterior, per tota la seva compacitat i simplicitat, té un inconvenient important: els fulls creats per la macro no s'actualitzen quan es fan canvis a la taula de vendes original. Si cal actualitzar sobre la marxa, haureu d'utilitzar el paquet VBA + Power Query, o més aviat, crear amb una macro no només fulls amb dades estàtiques, sinó consultes de Power Query actualitzades.

La macro en aquest cas és parcialment semblant a l'anterior (també té un cicle Per a cada… A continuació per iterar sobre les ciutats del directori), però dins del bucle ja no hi haurà filtratge i còpia, sinó que es crearà una consulta de Power Query i es carregarà els seus resultats a un full nou:

Sub Splitter2() Per a cada cel·la a l'interval ("taula de ciutats") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Font = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Contingut]," & Chr(13) & "" & Chr(10) & " #""Tipus canviat"" = Table.TransformColumnTypes(Font , {{""Categoria"", escriviu text}, {""Nom"", escriviu text}, {""Ciutat"", escriviu text}, {""Gestor"", escriviu text}, {""Oferta data "", escriviu datetime}, {""Cost"", escriviu número}})," & Chr(13) & "" & Chr(10) & " #""Files amb filtre aplicat"" = Table.Se " & _ "lectRows(#""Tipus canviat"", cadascun ([Ciutat] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Files amb filtre aplicat""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Proveïdor =Microsoft.Mashup.OleDb.1;Font de dades=$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 .RefreshOnQuenFile. = TrueOpen = fals. Cèl·lules .SavePassword = Fals . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Cel·la següent End Sub  

Després del seu llançament, veurem els mateixos fulls per ciutat, però les consultes de Power Query ja creades els formaran:

Amb qualsevol canvi a les dades d'origen, n'hi haurà prou amb actualitzar la taula corresponent amb el botó dret del ratolí: l'ordre Actualitza i desa (Actualització) o actualitzeu totes les ciutats alhora de manera massiva mitjançant el botó Actualitza-ho tot llengüeta dades (Dades — Actualitza-ho tot).

• Què són les macros, com crear-les i utilitzar-les
• Desar els fulls del llibre de treball com a fitxers separats
• Recollida de dades de tots els fulls del llibre en una taula