Cómo actualizar dinámicamente tabla dinámica de rango de origen de datos en Excel
Actualmente, podemos cambiar o actualizar dinámicamente tablas dinámicas usando link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] o `link: / excel-range-name-dynamic- rangos con nombre en excel [Rangos con nombre dinámicos]. Pero estas técnicas no son infalibles. Como todavía tendrá que actualizar la tabla dinámica manualmente. Si tiene datos grandes que contienen miles de filas y columnas, las tablas de Excel no lo ayudarán mucho. En su lugar, hará que su archivo sea pesado. Entonces, la única forma que queda es VBA.
En este artículo, aprenderemos cómo podemos hacer que nuestra tabla dinámica cambie automáticamente la fuente de datos. En otras palabras, automatizaremos el proceso manual de cambiar la fuente de datos para incluir dinámicamente nuevas filas y columnas agregadas a las tablas de origen y reflejar el cambio en la tabla dinámica al instante.
Escribir código en la hoja de datos fuente Dado que queremos que esto sea completamente automático, usaremos módulos de hoja para escribir código en lugar de un módulo principal. Esto nos permitirá usar //events-in-vba/the-worksheet-events-in-excel-vba.html[worksheet events] `.
Si los datos de origen y las tablas dinámicas están en hojas diferentes, escribiremos el código VBA para cambiar la fuente de datos de la tabla dinámica en el objeto de hoja que contiene los datos de origen (no que contiene la tabla dinámica). Presione CTRL + F11 para abrir el editor de VB. Ahora vaya al explorador de proyectos y busque la hoja que contiene los datos de origen. Haga doble clic en él.
Se abrirá una nueva área de codificación. Es posible que no vea ningún cambio, pero ahora tiene acceso a los eventos de la hoja de trabajo.
Haga clic en el menú desplegable de la izquierda y seleccione la hoja de trabajo. En el menú desplegable de la izquierda, seleccione desactivar. Verá un sub en blanco escrito en el nombre del área de código worksheet_deativate. Nuestro código para cambiar dinámicamente los datos de origen y actualizar la tabla dinámica irá en este bloque de código. Este código se ejecutará siempre que cambie de la hoja de datos a cualquier otra hoja. Puede leer sobre todos los eventos de la hoja de trabajo enlace: / events-in-vba-the-worksheet-events-in-excel-vba [aquí].
Ahora estamos listos para implementar el código.
Código fuente para actualizar la tabla dinámica de forma dinámica con un nuevo rango Para explicar cómo funciona, tengo un libro de trabajo. Este libro contiene dos hojas. Sheet1 contiene los datos de origen que pueden cambiar. Sheet2 contiene la tabla dinámica que depende de los datos de origen de sheet2.
Ahora he escrito este código en el área de codificación de sheet1. Estoy usando el evento Worksheet_Deactivate, por lo que este código se ejecuta para actualizar la tabla dinámica cada vez que cambiamos de la hoja de datos de origen.
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
Dim pc As PivotCache
Dim source_data As Range
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
pt.ChangePivotCache pc
End Sub
Si tiene un libro de trabajo similar, puede copiar directamente estos datos. He explicado que este código funciona a continuación.
Puede ver el efecto de este código en gif a continuación.
¿Cómo este código cambia automáticamente los datos de origen y actualiza las tablas dinámicas? En primer lugar, usamos un evento worksheet_deactivate. Este evento se activa solo cuando la hoja que contiene el código se cambia o se desactiva. Así es como se ejecuta automáticamente el código.
Ahora, para cambiar los datos de origen de la tabla dinámica, cambiamos los datos en la caché dinámica. Se crea una tabla dinámica utilizando la caché dinámica. La caché dinámica contiene los datos de origen antiguos hasta que la tabla dinámica no se actualiza manualmente o el rango de datos de origen se cambia manualmente.
Hemos creado referencias de tablas dinámicas con el nombre pt, la caché dinámica con el nombre pc y un rango con el nombre source_data. Los datos de origen contendrán todos los datos. Para obtener dinámicamente toda la tabla como rango de datos, determinamos la última fila y la última columna.
lstrow = Celdas (Rows.Count, 1) .End (xlUp) .Row
lstcol = Celdas (1, Columns.Count) .End (xlToLeft) .Column
Usando estos dos números, definimos source_data. Estamos seguros de que el rango de datos de origen siempre comenzará desde A1.
Establecer source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Ahora tenemos los datos de origen que son dinámicos. Solo necesitamos usarlo en la tabla dinámica.
Almacenamos estos datos en la caché dinámica, ya que sabemos que la caché dinámica almacena todos los datos.
Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) A continuación, definimos la tabla dinámica que queremos actualizar. Dado que queremos actualizar la tabla dinámica1 (nombre de la tabla dinámica. Puede verificar el nombre de la tabla dinámica en la pestaña de análisis mientras selecciona la tabla dinámica) en la hoja1, establecemos pt como se muestra a continuación.
Establecer pt = Sheet2.PivotTables («PivotTable1»)
Ahora simplemente usamos este caché dinámico para actualizar la tabla dinámica. Usamos el método changePivotCache del objeto pt.
pt.ChangePivotCache pc
Y tenemos nuestra tabla dinámica automatizada. Esto actualizará automáticamente su tabla dinámica. Si tiene varias tablas con la misma fuente de datos, simplemente use la misma caché en cada objeto de tabla dinámica.
Así que sí, chicos, así es como pueden cambiar dinámicamente el rango de la fuente de datos en Excel. Espero haber sido lo suficientemente explicativo. Si tiene alguna pregunta sobre este artículo, hágamelo saber en la sección de comentarios a continuación.
Artículos relacionados:
link: / custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [Cómo actualizar automáticamente las tablas dinámicas usando VBA]: Para actualizar automáticamente sus tablas dinámicas, puede usar Eventos de VBA. Utilice esta sencilla línea de código para actualizar su tabla dinámica automáticamente. Puede utilizar cualquiera de los 3 métodos de actualización automática de tablas dinámicas.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Ejecutar macro si se realiza algún cambio en la hoja en el rango especificado]: * En sus prácticas de VBA, lo haría obtienen la necesidad de ejecutar macros cuando un cierto rango o celda cambia. En ese caso, para ejecutar macros cuando se realiza un cambio en un rango objetivo, usamos el evento de cambio.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-done [Ejecutar macro cuando se realiza algún cambio en la hoja] | Entonces, para ejecutar su macro cada vez que se actualiza la hoja, usamos los Eventos de la hoja de trabajo de VBA.
link: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [Código VBA más simple para resaltar la fila y columna actual usando] | Utilice este pequeño fragmento de VBA para resaltar la fila y columna actuales de la hoja.
link: / events-in-vba-the-worksheet-events-in-excel-vba [Los eventos de la hoja de trabajo en Excel VBA] | El evento de la hoja de trabajo es realmente útil cuando desea que sus macros se ejecuten cuando ocurre un evento específico en la hoja.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-su-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 accesos directos le harán trabajar aún más rápido en Excel. La función VLOOKUP en Excel | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas. COUNTIF en Excel 2016 | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico.
La función Countif es esencial para preparar su tablero.
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel] | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.