¿cómo actualizo automáticamente el rango de una tabla dinámica?

Actualizar una tabla dinámica es una molestia, ¿no? Si utiliza con frecuencia tablas dinámicas en su trabajo, estoy seguro de que puede identificarse.

La cuestión es que cada vez que agrega nuevos datos a la hoja de origen, debe actualizar el rango de origen de la tabla dinámica antes de actualizar su tabla dinámica.

Ahora imagine que si agrega datos a su hoja de origen todos los días, necesita actualizar el rango de origen todos los días.

Y cada vez que cambiar el rango de la tabla dinámica es un desastre. Sí, es cierto, cuanto más frecuentemente agregue datos, más necesitará actualizar el rango de origen.

Entonces, el punto es que necesita un método para actualizar automáticamente el rango de origen cuando agrega nuevos datos.

NOTA : Las tablas dinámicas son parte de HABILIDADES INTERMEDIAS DE EXCEL .

Aplicar tabla para tener un rango de tabla dinámica de actualización automática

Hace unos días le pregunté a John Michaloudis sobre su consejo millonario sobre la mesa dinámica. Dice: Coloque sus datos de origen en una tabla. Créame, esta es una propina de un millón de dólares.

Al aplicar una tabla en los datos de origen, no necesita cambiar el rango de origen de su tabla dinámica una y otra vez.

Cada vez que agrega nuevos datos, actualiza automáticamente el rango de la tabla dinámica.

Convierta datos en tabla antes de crear una tabla dinámica

Cada vez que cree una tabla dinámica, asegúrese de aplicar la tabla a los datos de origen siguiendo los siguientes pasos.

1. Seleccione una de las celdas de sus datos.
2. Utilice la tecla de acceso directo Ctrl + T o navegue hasta → Insertar pestaña → Tablas → Tabla.
3. Aparecerá una ventana emergente con su rango de datos actual.
   
4. Haga clic en Aceptar.
5. Ahora, para crear una tabla dinámica, seleccione cualquier celda de sus datos. Vaya a → pestaña Diseño → Herramientas → Resumir con tabla dinámica.
   
6. Haga clic en Aceptar.

Ahora, cada vez que agrega nuevos datos a su hoja de datos, el rango de la tabla dinámica se actualiza automáticamente y solo necesita actualizar su tabla dinámica.

Convertir datos en tabla después de crear una tabla dinámica

Si ya tiene una tabla dinámica en su hoja de trabajo, puede seguir los siguientes pasos para convertir su fuente de datos en una tabla.

1. Seleccione una de las celdas de su fuente de datos.
2. Utilice la tecla de acceso directo Ctrl + T o navegue hasta → Insertar pestaña → Tablas → Tabla.
3. Aparecerá una ventana emergente con su rango de datos actual.
4. Haga clic en Aceptar.
5. Ahora, seleccione una de las celdas en su tabla dinámica y vaya a → Analizar → Datos → Editar fuente de datos → Editar fuente de datos (menú desplegable).
6. Aparecerá una ventana emergente para volver a seleccionar su fuente de datos o también puede ingresar el nombre de la tabla en la entrada del rango.
   
7. Haga clic en Aceptar.

De ahora en adelante, cada vez que agregue nuevos datos en su hoja de origen, el rango de la tabla dinámica aumentará para actualizarla automáticamente.

Cree un rango de tabla dinámica dinámica con la función OFFSET

La otra mejor manera de actualizar automáticamente el rango de la tabla dinámica es utilizar un rango dinámico.

El rango dinámico puede expandirse automáticamente cada vez que agrega nuevos datos a su hoja de origen. Estos son los pasos para crear un rango dinámico.

1. Vaya a → Pestaña Fórmulas → Nombres definidos → Administrador de nombres.
2. Una vez que haga clic en el administrador de nombres, aparecerá una ventana emergente.
   
3. En la ventana del Administrador de nombres, haga clic en Nuevo para crear un rango con nombre.
4. En la ventana de su nuevo nombre, ingrese
   1. Un nombre para su nueva gama. Yo uso el nombre «SourceData».
   2. Especifique la extensión del rango. Puede especificar entre la hoja de trabajo o el libro de trabajo actual.
   3. Agregue un comentario para describir su rango con nombre. Ingrese la siguiente fórmula en la barra de entrada «Consultar».

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Al final, haga clic en Aceptar.

Ahora tienes rango dinámico para crear una tabla dinámica.

Todo lo que necesita hacer es simplemente crear una tabla dinámica con sus datos de origen y luego cambiar la fuente con el rango nombrado usando el mismo método que usé en el primer método de tablas.

Una vez que haya agregado nuevos datos a su hoja de origen, simplemente actualice su tabla dinámica.

¿Cómo funciona esta fórmula?

En la fórmula anterior, utilicé la función de compensación para crear un rango dinámico.

Mencioné la celda A1 como punto de partida, luego, sin mencionar filas y columnas, especifiqué la altura y el ancho del rango usando COUNTA.

COUNTA contará las celdas con valores de la columna A y la fila 1 y le indicará a offset que expanda su alto y ancho en consecuencia.

Lo único que debes tener en cuenta es que no haya ninguna celda vacía entre la columna A y la fila 1.

Actualizar la tabla dinámica usando código VBA

A la mayoría de la gente le gusta usar códigos VBA. Aquí está el código que se utilizará para actualizar el rango de la tabla dinámica con VBA.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Cosas que debe cambiar antes de usarlo en su libro de trabajo.

1. Línea 13: cambie el nombre de la hoja de trabajo de origen.
2. Línea 14: cambie el nombre de la hoja de la tabla dinámica.
3. Línea 17: cambie el nombre de la tabla dinámica.

Si aún tiene algún problema al usar este código, escríbame en el cuadro de comentarios. Ahora déjame mostrarte cómo funciona este código para que puedas modificarlo fácilmente según tus necesidades.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

En la parte anterior del código, hemos especificado la tabla dinámica y las variables de la hoja de trabajo de los datos de origen. Puede cambiar el nombre de la hoja de cálculo desde aquí.

 PivotName = "PivotTable2"

En la parte anterior del código, ingrese el nombre de la tabla dinámica en la que desea usar este código.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

La parte anterior del código creará un rango dinámico usando la celda A1 de la hoja de trabajo de la fuente de datos.

Verificará la última columna y la última fila con datos para crear un rango dinámico. Cada vez que ejecute esta macro, creará un nuevo rango dinámico.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

La parte anterior del código actualizará la tabla dinámica y mostrará un mensaje al usuario indicando que la tabla dinámica ahora está actualizada.