Dividir una mesa en hojas

Microsoft Excel tiene muchas herramientas para recopilar datos de varias tablas (de diferentes hojas o de diferentes archivos): enlaces directos, función INDIRECT (INDIRECTO), complementos de Power Query y Power Pivot, etc. Desde este lado de la barricada, todo se ve bien.

Pero si te encuentras con un problema inverso (difundir datos de una tabla a diferentes hojas), entonces todo será mucho más triste. Por el momento, lamentablemente, no hay herramientas integradas civilizadas para tal separación de datos en el arsenal de Excel. Por lo tanto, tendrá que usar una macro en Visual Basic, o usar la combinación de grabador de macros + Power Query con un poco de "refinamiento de archivos" después.

Echemos un vistazo más de cerca a cómo se puede implementar esto.

Formulación del problema

Tenemos como dato inicial una tabla de este tipo con un tamaño de más de 5000 filas para ventas:

Tarea: distribuir los datos de esta tabla por ciudad en hojas separadas de este libro. Aquellos. en la salida, debe obtener en cada hoja solo aquellas filas de la tabla donde se realizó la venta en la ciudad correspondiente:

Preparar

Para no complicar el código de la macro y hacerlo lo más fácil de entender posible, realicemos un par de pasos preparatorios.

En primer lugar, la crear una tabla de búsqueda separada, donde una sola columna enumerará todas las ciudades para las que desea crear hojas separadas. Por supuesto, este directorio puede no contener todas las ciudades presentes en los datos de origen, sino solo aquellas para las que necesitamos informes. La forma más fácil de crear una tabla de este tipo es usar el comando Datos: eliminar duplicados (Datos — Eliminar duplicados) para copia de columna Ciudad o función ÚNICO (ÚNICO) – si tiene la última versión de Excel 365.

Dado que las nuevas hojas en Excel se crean de forma predeterminada antes (a la izquierda) de la actual (anterior), también tiene sentido ordenar las ciudades en este directorio en orden descendente (de Z a A); luego, después de la creación, la ciudad Las hojas se ordenarán alfabéticamente.

En segundo lugar, la пconvertir ambas tablas a dinámicas ("inteligente") para que sea más fácil trabajar con ellos. Usamos el comando Inicio – Formato como tabla (Inicio — Formatear como tabla) o atajo de teclado Ctrl+T. En la pestaña que aparece Constructor (Diseño) llamémoslos TablProdaji и MesaCiudad, respectivamente:

Método 1. Macro para división por hojas.

En la pestaña Avanzado revelador (Desarrollador) haga clic en el botón Visual Basic o usa el atajo de teclado otro+F11. En la ventana del editor de macros que se abre, inserte un nuevo módulo vacío a través del menú Insertar – Módulo y copiar el siguiente código allí:

Subdivisor () para cada celda en el rango ("tamaño") Rango ("tamaño"). Campo de filtro automático: = 3, Criterios 1: = celda. Rango de valor ("tamaño [#Todo]"). SpecialCells (xlCellTypeVisible). Copia Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Siguiente celda Worksheets("Данные").ShowAllData End Sub	  

Aquí con un bucle Para Cada... Siguiente implementado el paso por las celdas del directorio MesaCiudad, donde para cada ciudad se filtra (método Autofiltro) en la tabla de ventas original y luego copiar los resultados a la hoja recién creada. En el camino, la hoja creada se renombra con el mismo nombre de la ciudad y se activa el ajuste automático del ancho de las columnas para la belleza.

Puede ejecutar la macro creada en Excel en la pestaña revelador . Macros (Desarrollador — Macros) o atajo de teclado otro+F8.

Método 2. Crear múltiples consultas en Power Query

El método anterior, a pesar de su compacidad y simplicidad, tiene un inconveniente importante: las hojas creadas por la macro no se actualizan cuando se realizan cambios en la tabla de ventas original. Si es necesario actualizar sobre la marcha, tendrá que usar el paquete VBA + Power Query, o más bien, crear usando una macro, no solo hojas con datos estáticos, sino consultas Power Query actualizadas.

La macro en este caso es parcialmente similar a la anterior (también tiene un ciclo Para Cada... Siguiente para iterar sobre ciudades en el directorio), pero dentro del bucle ya no habrá filtrado y copiado, sino que se creará una consulta de Power Query y se cargarán sus resultados en una nueva hoja:

Sub Splitter2 () para cada celda en el rango ("Tabla de ciudades") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Fuente = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Contenido]," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado"" = Table.TransformColumnTypes(Fuente , {{""Categoría"", escriba texto}, {""Nombre"", escriba texto}, {""Ciudad"", escriba texto}, {""Gerente"", escriba texto}, {""Oferta fecha "", escriba datetime}, {""Coste"", escriba número}})," & Chr(13) & "" & Chr(10) & " #""Filas con filtro aplicado"" = Table.Se " & _ "lectRows(#""Tipo cambiado"", each ([Ciudad] = """ & celda.Valor & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Filas con filtro aplicado""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Proveedor =Microsoft.Mashup.OleDb.1;Fuente de datos=$Libro de trabajo$;Ubicación=" & celda.Valor & ";Propiedades extendidas=""""" _ , Destino:=Rango("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECCIONAR *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 Siguiente celda End Sub  

Después de su lanzamiento, veremos las mismas hojas por ciudad, pero las consultas Power Query ya creadas las formarán:

Con cualquier cambio en los datos de origen, bastará con actualizar la tabla correspondiente con el botón derecho del mouse: el comando Actualizar y guardar (Actualizar) o actualice todas las ciudades a la vez de forma masiva usando el botón Actualizar todo de la pestaña. Datos (Datos — Actualizar todo).

• Qué son las macros, cómo crearlas y usarlas
• Guardar hojas de libros de trabajo como archivos separados
• Recopilación de datos de todas las hojas del libro en una tabla