Contidos
Microsoft Excel ten moitas ferramentas para recoller datos de varias táboas (de diferentes follas ou de diferentes ficheiros): ligazóns directas, función INDIRECTO (INDIRECTO), complementos de Power Query e Power Pivot, etc. Dende este lado da barricada, todo se ve ben.
Pero se te atopas cun problema inverso: espallar datos dunha táboa a diferentes follas, entón todo será moito máis triste. Polo momento, non hai ferramentas integradas civilizadas para tal separación de datos no arsenal de Excel, por desgraza. Polo tanto, terás que usar unha macro en Visual Basic ou usar a combinación de gravadora de macros + Power Query cun pequeno "refinamento de ficheiros" despois.
Vexamos máis de cerca como se pode implementar isto.
Formulación do problema
Temos como datos iniciais unha táboa deste tipo cun tamaño de máis de 5000 filas para vendas:
Tarefa: distribuír os datos desta táboa por cidades en follas separadas deste libro. Eses. na saída, cómpre incluír en cada folla só as filas da táboa onde se realizou a venda na cidade correspondente:
Preparar
Para non complicar o código macro e facelo o máis fácil de entender, imos realizar un par de pasos preparatorios.
En primeiro lugar, o crear unha táboa de busca separada, onde unha única columna listará todas as cidades para as que quere crear follas separadas. Por suposto, este directorio pode non conter todas as cidades presentes nos datos fonte, senón só aquelas para as que necesitamos informes. O xeito máis sinxelo de crear unha táboa deste tipo é usar o comando Datos - Eliminar duplicados (Datos — Eliminar duplicados) para copia de columna cidade ou función ÚNICO (ÚNICA) – se tes a última versión de Excel 365.
Dado que as follas novas en Excel se crean de forma predeterminada antes (á esquerda da) da actual (anterior), tamén ten sentido ordenar as cidades deste directorio en orde descendente (de Z a A) e despois da creación, a cidade. as follas ordenaranse alfabeticamente.
En segundo lugar, o пconverter ambas táboas en dinámicas (“intelixente”) para facilitar o traballo con eles. Usamos o comando Inicio - Formato como unha táboa (Inicio — Formato como táboa) ou atallo de teclado Ctrl+T. Na pestana que aparece Construtor (Deseño) chamémoslles tablProdaji и TableCity, respectivamente:
Método 1. Macro para división por follas
Na pestana Avanzado revelador (Desenvolvedor) prema no botón Visual Basic ou use o atallo do teclado alt+F11. Na xanela do editor de macros que se abre, insira un novo módulo baleiro a través do menú Inserir – Módulo e copia alí o seguinte código:
Sub Splitter() Para cada cela no rango("таблГорода") Range("таблПродажи"). Campo de filtro automático:=3, Criteria1:=cell.Rango de valores("таблПродажи[#All]").SpecialCells(xlVisibleellCopyy). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Aquí cun bucle Para cada... Seguinte implementou o paso polas celas do directorio TableCity, onde se filtra para cada cidade (método Filtro automático) na táboa de vendas orixinal e despois copiando os resultados na folla de nova creación. Ao longo do camiño, a folla creada pasa a chamarse co mesmo nome da cidade e a axuste automático do ancho das columnas para a beleza está activada nela.
Pode executar a macro creada en Excel na pestana revelador botón Macros (Desenvolvedor - Macros) ou atallo de teclado alt+F8.
Método 2. Crea varias consultas en Power Query
O método anterior, por toda a súa compacidade e sinxeleza, ten un inconveniente importante: as follas creadas pola macro non se actualizan cando se realizan cambios na táboa de vendas orixinal. Se é necesaria a actualización sobre a marcha, terás que usar o paquete VBA + Power Query ou, mellor dito, crear usando unha macro non só follas con datos estáticos, senón consultas de Power Query actualizadas.
A macro neste caso é parcialmente similar á anterior (tamén ten un ciclo Para cada... Seguinte para iterar sobre as cidades do directorio), pero dentro do bucle xa non haberá filtrado nin copia, senón que se creará unha consulta de Power Query e cargará os seus resultados nunha nova folla:
Sub Splitter2() Para cada cela no rango("Táboa da cidade") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Contido]," & Chr(13) & "" & Chr(10) & " #""Tipo modificado"" = Table.TransformColumnTypes(Fonte , {{""Categoría", escriba texto}, {""Nome"", escriba texto}, {""Cidade"", escriba texto}, {""Xestor"", escriba texto}, {""Trato data "", escriba datetime}, {""Custo"", escriba número}})," & Chr(13) & "" & Chr(10) & " #""Filas co filtro aplicado"" = Table.Se " & _ "lectRows(#""Tipo cambiado"", cada unha ([Cidade] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "en " & Chr(13) & "" & Chr(10) & " #""Fichas co filtro aplicado""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provedor =Microsoft.Mashup.OleDb.1;Fonte de datos=$Workbook$;Location=" & cell.Value & ";Propiedades ampliadas=""""" _ , Destino:=Intervalo("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePasword .SavePasword SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Celda seguinte End Sub Despois do seu lanzamento, veremos as mesmas follas por cidade, pero as consultas de Power Query xa creadas formaránas:
Con calquera cambio nos datos de orixe, será suficiente actualizar a táboa correspondente co botón dereito do rato: o comando Actualizar e gardar (Actualizar) ou actualiza todas as cidades á vez de forma masiva usando o botón Actualizar todo aba Data (Datos — Actualizar todo).
- Que son as macros, como crealas e usalas
- Gardando follas de cadernos como ficheiros separados
- Recopilación de datos de todas as follas do libro nunha soa táboa