excel - Combine varias filas de tablas en la tabla maestra

office-2013

Question

¡Feliz lunes a todos!

Tiene una pregunta y espero que pueda ayudar. Tengo una hoja de cálculo de presupuesto que tiene una pestaña de presupuesto. En esta pestaña hay aproximadamente 8 tablas desglosadas en diferentes categorías. Cada tabla en la pestaña tiene exactamente las mismas columnas. ¿Existe una forma no vbscript / marco para crear una tabla maestra que combine todas las tablas en una sola tabla en una pestaña diferente? Parece que sería obvio, pero he intentado todo lo que puedo pensar y encontrar en línea y no parece haber una solución decente sin un complemento llamado consulta de energía.

Answer 1

Puede usar la funcionalidad del asistente de tabla dinámica para consolidar varios rangos (que son sus tablas) en una sola tabla dinámica.

Cuando le solicite que agregue sus rangos, use los nombres de las tablas con la siguiente sintaxis: Table4[#All]

Necesita el [#All] para obtener todos los datos asociados con la tabla. Simplemente repita esto para cada uno de los nombres de sus tablas que desea consolidar.

Descripción completa que he dado en mi respuesta aquí:

stackoverflow.com/questions/46685803/…

Nota: Si desea conservar los nombres de tabla o números de tabla originales, deberá seleccionar la opción:

1) "Crearé los campos de página"

2) Ingrese los rangos usando el nombre de la tabla, por ejemplo, Tabla4 [#All]

3) Seleccione cuántos campos de página desea 1-4 y agregue la etiqueta del elemento utilizada para identificar los rangos seleccionados a continuación, por ejemplo, Tabla4.

No estoy seguro de si 4 elementos es el máximo o si esto se puede extender a través de VBA. Sin embargo, también puede usar PowerQuery o UnionQuery.

Las siguientes citas son de aquí: http://www.contextures.com/xlPivot08.html

Incluyo un resumen en caso de que se pierdan los enlaces.

PowerQuery:

Si tiene una versión de Excel que admite el complemento Power Query de Microsoft, puede usarlo para combinar los datos en dos o más tablas. Las tablas pueden estar en el mismo libro de trabajo o en diferentes archivos.

http://www.contextures.com/xlPivot08.html#videopowerquery

Consulta de unión:

Si no puede combinar sus datos en una sola hoja de trabajo, otra solución es crear rangos con nombre en un archivo de Excel y usar Microsoft Query (MS Query) para combinar los datos.

http://www.contextures.com/xlPivot08.html#union01

Answer 2

Sé que solicitó una forma que no sea VBA, pero para completar, estoy agregando otra respuesta que también tiene una solución VBA, porque es muy simple, es increíblemente rápida y es genérica. Todo lo que necesita hacer es cortar y pegar este código en un módulo de código estándar, agregar un botón y asignarlo para activar la rutina de llamada, asignar un nombre a las tablas de origen que incluya el nombre completo de la tabla de resumen, y usted es bueno para ir.

Sub CombineTables(loDest As ListObject, Optional lcSource As ListColumn)

Dim ws              As Worksheet
Dim lo              As ListObject
Dim lc              As ListColumn
Dim rDest           As Range
Dim lDestRows       As Long
Dim lSourceRows     As Long

Application.ScreenUpdating = False

If lcSource Is Nothing Then Set lcSource = loDest.ListColumns(1)
If loDest.ListRows.Count > 0 Then loDest.DataBodyRange.Delete

For Each ws In ActiveWorkbook.Worksheets
    For Each lo In ws.ListObjects
        If lo <> loDest Then
            With lo
                If InStr(.Name, loDest.Name & "_") > 0 Then
                    On Error Resume Next
                    lDestRows = loDest.ListRows.Count
                    On Error GoTo 0
                    lSourceRows = .ListRows.Count
                    If lSourceRows > 0 Then

                        ''Work out where we want to paste the data to
                        Set rDest = loDest.HeaderRowRange.Offset(1 + lDestRows).Resize(lSourceRows)

                        ''Resize the destination table
                        loDest.Resize loDest.Range.Resize(1 + lSourceRows + lDestRows)       

                        For Each lc In .ListColumns
                         Intersect(loDest.ListColumns(lc.Name).Range.EntireColumn, rDest).Value2 = lc.DataBodyRange.Value
                        Next lc
                        Set lc = Nothing
                        On Error Resume Next
                        Set lc = .ListColumns(lcSource.Name)
                        On Error GoTo 0
                        If lc Is Nothing Then Intersect(lcSource.Range, rDest.EntireRow).Value2 = ws.Name
                    End If
                End If
            End With
        End If
    Next lo
Next ws

Application.ScreenUpdating = True

End Sub

Y aquí está la persona que llama:

Sub CombineTables_Caller()
CombineTables [SomeName].ListObject, [SomeName].ListObject.ListColumns("Source")
End Sub

Cuando presiono ese botón, el código buscará en todo el libro las tablas cuyos nombres contengan el nombre de la tabla de destino (en este caso, la tabla llamada "SomeName"), y luego traerá sus datos. Entonces, si está agregando nuevas pestañas, siempre que prefija sus nombres de Tabla con el nombre de la tabla de destino, se incluirán. Cualquier otra tabla (como la que se llama ''DifferentName'' se ignorará).

... y aquí está el resultado:

Answer 3

Si tiene Excel 2013 o posterior, esta es la excusa perfecta para jugar con PowerQuery, que ahora se llama ''Obtener y transformar'' en la cinta. Puede ver algo muy similar a su requisito en mi respuesta en la función indirecta de Excel para leer fechas y devolver valores dinámicos

Te sugiero que veas ese hilo ... incluso si por alguna razón no puedes usar PowerQuery para este desafío en particular, porque creo que vale la pena ver cuán simple es juntar tablas idénticas usando PowerQuery, incluso si solo para referencia futura. Básicamente es un desarrollador de VBA en una caja. ¡Es un gimp VBA!