Reporting completo de las tablas dinámicas Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Necesitamos conocer las propiedades y características de nuestra tabla dinámica, para ello nos interesa crear un reporting completo de las tablas dinámicas Excel.

Reporting completo de las tablas dinámicas Excel
  • CREAR MACRO

  1. Abrir el libro deseado, que contiene una tabla dinámica.

  2. Pulsar la tecla Alt y mantener pulsada / Pulsar la tecla F11. Mostrará el editor de Visual Basic.

  3. Ir a: Barra de Menú / Insertar / Módulo. Mostrará la ventana del módulo donde escribimos la macro.

  4. Escribir el código de la macro:

    Sub ReportTD()
    Dim hj As Worksheet
    Dim hoja As Worksheet
    Dim prop As PivotTable
    Dim i, j As Long
    Set hoja = Worksheets.Add
    i = 2: j = 1

    With hoja

    .Cells(1, j) = "Application:"
    .Cells(2, j) = "Name:"
    .Cells(3, j) = "SourceData:"
    .Cells(4, j) = "RefreshDate:"
    .Cells(5, j) = "GrandTotalName:"

    For Each hj In ActiveWorkbook.Worksheets

    For Each prop In hj.PivotTables
    .Cells(1, i).Value = prop.Application
    .Cells(2, i).Value = prop.Name
    .Cells(3, i).Value = prop.SourceData
    .Cells(4, i).Value = prop.RefreshDate
    .Cells(5, i).Value = prop.GrandTotalName
    Next

    Next

    .Activate

    End With

    End Sub

    Podemos incluir más propiedades (ver Propiedades de tabla dinámica) añadiendo 2 líneas en la posición, .Cells(6, j) = "[Rótulo de propiedad]:" y .Cells(6, i).Value = prop.[Nombre de propiedad] con el número incrementable.

  5. Guardar el libro con la extensión *.xlsm. Libro habilitado para macros.

  • EJECUTAR MACRO

  1. Ejecutar la macro "ReportTD".

    Nueva hoja con las propiedades de la tabla dinámica
  2. Verificar como se inserta una hoja nueva con la información de la tabla dinámica.

  • PROPIEDADES DE TABLA DINÁMICA.

PropiedadDescripción
AllowMultipleFilters
Establece o recupera un valor que indica si un campo de tabla dinámica puede tener varios filtros aplicados al mismo tiempo. Lectura y escritura de tipo Boolean.
AlternativeText
Devuelve o establece la cadena de texto descriptivo (alternativa) para la tabla dinámica especificada. Lectura y escritura.
Application
Cuando se usa sin un calificador de objeto, esta propiedad devuelve un objeto Application que representa la aplicación Microsoft Excel. Cuando se utiliza con un calificador de objeto, esta propiedad devuelve un objeto Application que representa al creador del objeto especificado (puede utilizar esta propiedad con un objeto de automatización OLE para devolver la aplicación de dicho objeto). Es de sólo lectura.
CacheIndex
Devuelve o establece el número de índice de la memoria caché de tabla dinámica. Lectura y escritura de tipo Long.
CalculatedMembersInFilters
Devuelve o establece si se van a evaluar los miembros calculados de los servidores OLAP en filtros. Lectura y escritura.
ColumnGrand
Muestra Verdadero si el informe de tabla dinámica muestra los totales generales de columnas. Lectura y escritura de tipo Boolean.
CompactLayoutColumnHeader
Especifica el título que se muestra en el encabezado de columna de una tabla dinámica con un formato de filas compactas. Valor de tipo String de solo lectura.
CompactLayoutRowHeader
Especifica el título que se muestra en el encabezado de fila de una tabla dinámica en el formulario de diseño de fila compacta. Stringde lectura y escritura.
CompactRowIndent
Devuelve o establece el incremento de sangría de los elementos dinámicos cuando está habilitado el formato de filas compactas. De lectura y escritura.
Creator
Devuelve un entero de 32 bits que indica la aplicación en la que se creó el objeto. Sólo lectura de tipo Long.
DataBodyRange
Devuelve un objeto Range que representa el rango de valores de una tabla dinámica. Sólo lectura.
DisplayContextTooltips
Controla si se muestra información sobre herramientas para las celdas de tabla dinámica. Lectura y escritura de tipo Boolean.
DisplayErrorString
Muestra Verdadero si el informe de tabla dinámica muestra una cadena de error personalizada en las celdas que contienen errores. El valor predeterminado es False. Lectura y escritura de tipo Boolean.
DisplayFieldCaptions
Controla si se muestran o no los botones de filtro y los títulos PivotField para filas y columnas en la cuadrícula. De lectura y escritura.
DisplayImmediateItems
Devuelve o establece un valor Boolean que indica si los elementos de las áreas de fila y columna son visibles cuando el área de datos de la tabla dinámica está vacía. Establecer esta propiedad en False para ocultar los elementos en las áreas de fila y columna cuando el área de datos de la tabla dinámica está vacía. El valor predeterminado es Verdadero.
DisplayMemberPropertyTooltips
Controla si se deben mostrar las propiedades de miembro en la información sobre herramientas. Lectura y escritura de tipo Boolean.
DisplayNullString
Muestra Verdadero si el informe de tabla dinámica muestra una cadena personalizada en las celdas que contienen valores nulos. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
EnableDataValueEditing
Muestra Verdadero para deshabilitar la alerta de sobrescritura de valores en el área de datos de la tabla dinámica. Verdadero también permite al usuario cambiar valores de datos que anteriormente no se pudieron cambiar. El valor predeterminado es False. Lectura y escritura de tipo Boolean.
EnableDrilldown
Muestra Verdadero si está habilitado el análisis en profundidad. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
EnableFieldDialog
Muestra Verdadero si el cuadro de diálogo Campo de tabla dinámica está disponible cuando el usuario hace doble clic en el campo de tabla dinámica. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
EnableFieldList
False para deshabilitar la capacidad de mostrar la lista de campos para la tabla dinámica. Si la lista de campos ya estaba se muestre desaparece. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
EnableWizard
Muestra Verdadero si el Asistente para tablas dinámicas está disponible. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
EnableWriteback
Devuelve o establece si está habilitada la escritura en el origen de datos para la tabla dinámica especificada. El valor predeterminado es False. Lectura y escritura.
ErrorString
Devuelve o establece un valor de tipo String que representa la cadena que se muestra en las celdas que contienen errores cuando la propiedad DisplayErrorString es Verdadero.
FieldListSortAscending
Controla el criterio de ordenación de los campos en la lista de campos de tabla dinámica. Cuando esta propiedad se establece en Verdadero, los campos se ordenan en orden ascendente. Cuando se establece en False, los campos se ordenan en orden del origen de datos. Lectura y escritura.
GrandTotalName
Devuelve o establece la etiqueta de cadena de texto que se muestra en el título de la columna o fila del total general en el informe de tabla dinámica especificado. El valor predeterminado es la cadena "Total general". Stringde lectura y escritura.
Hidden
Comprueba si existe la tabla dinámica en el nivel de hoja de cálculo. Boolean. Sólo lectura
InGridDropZones
Esta propiedad se utiliza para alternar zonas de colocación de cuadrícula de un objeto PivotTable . En algunos casos, también afecta el diseño de la tabla dinámica. Lectura y escritura de tipo Boolean.
LayoutRowDefault
Esta propiedad especifica la configuración de diseño de los campos dinámicos cuando se agregan a la tabla dinámica por primera vez. XlLayoutRowTypede lectura y escritura.
ManualUpdate
Muestra Verdadero si el informe de tabla dinámica se actualiza sólo cuando el usuario lo solicita. El valor predeterminado es False. Lectura y escritura de tipo Boolean.
MergeLabels
Muestra Verdadero si el elemento de fila exterior del informe de tabla dinámica especificado, elemento de columna, subtotal y rótulos de totales generales usan celdas combinadas. Lectura y escritura de tipo Boolean.
Name
Devuelve o establece un valor de tipo String que representa el nombre del objeto.
NullString
Devuelve o establece la cadena que se muestra en las celdas que contienen valores nulos cuando la propiedad DisplayNullString es Verdadero. El valor predeterminado es una cadena vacía (""). Stringde lectura y escritura.
PageFieldOrder
Devuelve o establece el orden de en qué página campos se agregan al diseño del informe de tabla dinámica. Puede ser una de las siguientes constantes XlOrder : xlDownThenOver o xlOverThenDown. La constante predeterminada es xlDownThenOver. Lectura y escritura de tipo Long.
PageFieldStyle
Devuelve o establece el estilo utilizado en el área de campo de página enlazada. El valor predeterminado es una cadena null (ningún estilo se aplica de forma predeterminada). Stringde lectura y escritura.
PageFieldWrapCount
Devuelve o establece el número de campos de página en cada columna o fila en el informe de tabla dinámica. Lectura y escritura de tipo Long.
PreserveFormatting
Muestra Verdadero si el formato se conserva cuando el informe se actualiza o se vuelve a calcular por operaciones tales como desplazamiento, ordenación o cambio de los elementos de campo de página. Para tablas de consulta, esta propiedad es Verdadero si cualquier formato común de las cinco primeras filas de datos se aplican a las nuevas filas de datos de la tabla de consulta. No tiene el formato de las celdas no usadas. La propiedad es False si el último autoformato aplicado a la tabla de consulta se aplica a las nuevas filas de datos. El valor predeterminado es Verdadero.
PrintDrillIndicators
Especifica si se permite o no los indicadores de detalle se imprimen con la tabla dinámica. Lectura y escritura de tipo Boolean.
PrintTitles
Muestra Verdadero si se establece la impresión de títulos de la hoja de cálculo basándose en el informe de tabla dinámica. False si se imprimen los títulos de la hoja de cálculo. El valor predeterminado es False. Lectura y escritura de tipo Boolean.
RefreshDate
Devuelve la fecha en la que se actualizó por última vez el informe de tabla dinámica. Sólo lectura de la fecha.
RefreshName
Devuelve el nombre de la persona que actualizó por última vez los datos del informe de tabla dinámica. Sólo lectura de la cadena.
RepeatItemsOnEachPrintedPage
Muestra Verdadero si se muestran etiquetas de fila, columna y elemento en la primera fila de cada página cuando se imprime el informe de tabla dinámica especificado. False si se imprimen etiquetas sólo en la primera página. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
RowGrand
Muestra Verdadero si el informe de tabla dinámica muestra los totales generales de filas. Lectura y escritura de tipo Boolean.
RowRange
Devuelve un objeto Range que representa el rango que contiene el área de fila en el informe de tabla dinámica. Es de sólo lectura.
SaveData
Muestra Verdadero si los datos para el informe de tabla dinámica se guardan con el libro. False si sólo se guarda la definición del informe. Lectura y escritura de tipo Boolean.
ShowDrillIndicators
La propiedad ShowDrillIndicators se utiliza para alternar la visualización de los indicadores de detalle en la tabla dinámica. Lectura y escritura de tipo Boolean.
ShowPageMultipleItemLabel
Cuando establece en Verdadero (valor predeterminado), "(varios elementos)" aparecerá en la celda de tabla dinámica en la hoja de cálculo cada vez que los elementos están ocultos y un agregado de los elementos no ocultos se muestra en la vista Tabla dinámica. Lectura y escritura de tipo Boolean.
ShowTableStyleColumnHeaders
La propiedad ShowTableStyleColumnHeaders se establece en Verdadero si deben mostrarse los encabezados de columna en la tabla dinámica. Lectura y escritura de tipo Boolean.
ShowTableStyleColumnStripes
La propiedad ShowTableStyleColumnStripes muestra columnas con bandas que las columnas pares tienen un formato diferente de las columnas impares. Esto hace que las tablas dinámicas fáciles de leer. Lectura y escritura de tipo Boolean.
ShowTableStyleRowHeaders
La propiedad ShowTableStyleRowHeaders se establece en Verdadero si deben mostrarse los encabezados de fila de la tabla dinámica. Lectura y escritura de tipo Boolean.
ShowTableStyleRowStripes
La propiedad ShowTableStyleRowStripes muestra filas con bandas que las filas pares tienen un formato diferente de las filas impares. Esto hace que las tablas dinámicas fáciles de leer. Lectura y escritura de tipo Boolean.
ShowValuesRow
Devuelve o establece si se muestra la fila de valores. Lectura y escritura.
SmallGrid
Muestra Verdadero si Microsoft Excel utiliza una cuadrícula de dos celdas de ancho por dos celdas de profundo en un informe de tabla dinámica recién creado. False si Excel utiliza un esquema de la Galería de símbolos en blanco. Lectura y escritura de tipo Boolean.
SortUsingCustomLists
La propiedad SortUsingCustomLists controla si se usan listas personalizadas para ordenar los elementos de los campos, tanto inicialmente cuando se inicializa el campo dinámico y se ordenan los elementos dinámicos por sus rótulos; y versiones posteriores cuando el usuario aplica un criterio de ordenación. Lectura y escritura de tipo Boolean.
SourceData
Devuelve el origen de datos para el informe de tabla dinámica, como se muestra en la siguiente tabla. Lectura y escritura Variant.
SubtotalHiddenPageItems
Muestra Verdadero si oculta los elementos de campo de página en el informe de tabla dinámica se incluyen en la fila y los subtotales de columna, totales de bloques y totales generales. El valor predeterminado es False. Lectura y escritura de tipo Boolean.
Summary
Devuelve o establece la descripción asociada a la cadena de texto alternativa de la tabla especificada. Lectura y escritura.
TableRange1
Devuelve un objeto Range que representa el rango que contiene todo el informe de tabla dinámica, pero no incluye los campos de página. Es de sólo lectura.
TableRange2
Devuelve un objeto Range que representa el rango que contiene todo el informe de tabla dinámica, incluidos los campos de página. Es de sólo lectura.
TableStyle2
La propiedad TableStyle2 especifica el estilo de tabla dinámica aplicado actualmente a la tabla dinámica. Lectura y escritura.
Tag
Devuelve o establece una cadena guardada con el informe de tabla dinámica. Stringde lectura y escritura.
TotalsAnnotation
Muestra Verdadero si se muestra un asterisco (*) junto a cada subtotal y total del informe de tabla dinámica especificado, si el informe se basa en un origen de datos OLAP. El valor predeterminado es Verdadero. Lectura y escritura de tipo Boolean.
VacatedStyle
Devuelve o establece el estilo aplicado a las celdas vacías cuando se actualiza el informe de tabla dinámica. El valor predeterminado es una cadena null (ningún estilo se aplica de forma predeterminada). Stringde lectura y escritura.
Value
Devuelve o establece un valor String que representa el nombre del informe de tabla dinámica.
Version
Devuelve un valor XlPivotTableVersionList que representa el número de versión de Microsoft Excel.
VisualTotals
Es true (valor predeterminado) para habilitar las tablas dinámicas de procesamiento analítico en línea (OLAP) Processing después de un elemento se ha ocultado de la vista. Lectura y escritura de tipo Boolean.
VisualTotalsForSets
Devuelve o establece si se van incluir elementos filtrados en los totales de los conjuntos con nombre de la tabla dinámica especificada. Lectura y escritura.
Nivel de dificultad: Experto VBA-Macros Excel Experto VBA – Macros

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *