En este artículo, aprenderemos cómo actualizar automáticamente los datos de la tabla dinámica en Excel.
Guión:
Como todos sabemos, cada vez que hacemos cambios en los datos de origen de una tabla dinámica, no se refleja inmediatamente en la tabla dinámica. Necesitamos actualizar las tablas dinámicas para ver los cambios cada vez que abramos un libro de Excel. Y si envía un archivo actualizado sin actualizar las tablas dinámicas, puede sentirse avergonzado. Entonces, aprenda aquí cómo encontrar la opción de actualización al usar la tabla dinámica
Actualizar datos al abrir un archivo en Excel
Primero cree una tabla dinámica y luego haga clic con el botón derecho en cualquier celda de la tabla dinámica.
Vaya a Opciones de tabla dinámica> pestaña Datos> Marque la casilla que dice Actualizar datos al abrir un archivo
Esto permitirá la actualización automática de datos cada vez que se abra el archivo.
Ejemplo :
Todos estos pueden resultar confusos de entender. Entendamos cómo usar la función usando un ejemplo. Aquí tenemos algunos datos y primero debemos crear una tabla dinámica y luego encontrar las opciones para habilitar la actualización automática de las tablas dinámicas.
Cree una tabla dinámica y luego haga clic con el botón derecho en cualquier celda de la tabla dinámica como se muestra a continuación.
Seleccione las Opciones de tabla dinámica y esto abrirá un cuadro de diálogo Opciones de tabla de PIvot.
Seleccione la pestaña de datos y luego marque la casilla que dice Actualizar datos al abrir el archivo. Puede realizar esto sin abrir y cerrar el archivo usando VBA.
Usando VBA
Entonces, aquí, aprenderemos cómo actualizar automáticamente una tabla dinámica usando VBA. De esta forma es más fácil de lo que imaginaba.
Esta es la sintaxis simple para actualizar automáticamente las tablas dinámicas en el libro de trabajo.
| 'Código en el objeto de hoja de datos de origen
Private Sub Worksheet_Deactivate () sheetname_of_pivot_table.Tablas dinamicas("pivot_table_name") .PivotCache.Refresh End Sub |
¿Qué son los cachés dinámicos?
Cada tabla dinámica almacena los datos en la caché dinámica. Es por eso que pivot puede mostrar datos anteriores. Cuando actualizamos las tablas dinámicas, actualiza la caché con nuevos datos de origen para reflejar los cambios en la tabla dinámica.
Entonces solo necesitamos una macro para actualizar la caché de las tablas dinámicas. Haremos esto usando un evento de hoja de trabajo para que no tengamos que ejecutar la macro manualmente.
¿Dónde codificar para actualizar automáticamente las tablas dinámicas?
Si sus datos de origen y tablas dinámicas están en hojas diferentes, entonces el código VBA debe ir en la hoja de datos de origen.
Aquí usaremos el evento Worksheet_SelectionChange. Esto hará que el código se ejecute cada vez que cambiemos de la hoja de datos de origen a otra hoja. Más adelante explicaré por qué utilicé este evento.
Aquí, tengo datos de origen en sheet2 y tablas dinámicas en sheet1.
Abra VBE usando la tecla CTRL + F11. En el explorador de proyectos, puede ver tres objetos, Hoja1, Hoja2 y el Libro de trabajo.
Dado que Sheet2 contiene los datos de origen, haga doble clic en el objeto sheet2.
Ahora puede ver dos menús desplegables en la parte superior del área de código. Desde el primer menú desplegable, seleccione la hoja de trabajo. Y en el segundo menú desplegable, seleccione Desactivar. Esto insertará un subnombre vacío Worksheet_Deactivate. Nuestro código estará escrito en este sub. Cualquier línea escrita en este sub, se ejecuta tan pronto como el usuario cambia de esta hoja a cualquier otra hoja.
En sheet1 tengo dos tablas dinámicas. Quiero actualizar solo una tabla dinámica. Para eso, necesito saber el nombre de la tabla dinámica. Para conocer el nombre de cualquier tabla dinámica, seleccione cualquier celda en esa tabla dinámica y vaya a la pestaña de análisis de la tabla dinámica. En el lado izquierdo, verá el nombre de la tabla dinámica. También puede cambiar el nombre de la tabla dinámica aquí.
Ahora que conocemos el nombre de la tabla dinámica, podemos escribir una línea simple para actualizar la tabla dinámica.
| Private Sub Worksheet_Deactivate ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
Y esta hecho.
Ahora, cada vez que cambie de los datos de origen, este código vba se ejecutará para actualizar la tabla dinámica1. Como puede ver en el gif a continuación.
¿Cómo actualizar todas las tablas dinámicas en el libro de trabajo?
En el ejemplo anterior, solo queríamos actualizar una tabla dinámica específica. Pero si desea actualizar todas las tablas dinámicas en un libro de trabajo, solo necesita hacer pequeños cambios en su código.
| Private Sub Worksheet_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Para cada PC de este libro de trabajo. pc.Refresh Siguiente pc End Sub |
En este código, estamos usando un bucle For para recorrer cada caché de pivote en el libro de trabajo. El objeto ThisWorkbook contiene todas las cachés dinámicas. Para acceder a ellos utilizamos ThisWorkbook.PivotCaches.
¿Por qué utilizar el evento Worksheet_Deactivate?
Si desea actualizar la tabla dinámica tan pronto como se realice algún cambio en los datos de origen, debe usar el evento Worksheet_Change. Pero no lo recomiendo. Hará que su libro de trabajo ejecute el código cada vez que realice algún cambio en la hoja. Puede que tenga que hacer cientos de cambios antes de querer ver el resultado. Pero Excel actualizará la tabla dinámica con cada cambio. Esto dará lugar a una pérdida de tiempo y recursos de procesamiento. Entonces, si tiene tablas dinámicas y datos en diferentes hojas, es mejor usar Evento de desactivación de hoja de trabajo. Te permite finalizar tu trabajo. Una vez que cambia a las hojas de la tabla dinámica para ver los cambios, se corrigen los cambios.
Si tiene tablas dinámicas y datos de origen en la misma hoja y desea que las tablas dinámicas se actualicen automáticamente, es posible que desee utilizar el evento Worksheet_Change.
| Private Sub Worksheet_Change (ByVal Target As Range)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
¿Cómo actualizar todo en los libros de trabajo cuando se realiza un cambio en los datos de origen?
Si desea actualizar todo en un libro de trabajo (gráficos, tablas dinámicas, fórmulas, etc.) puede usar el comando ThisWorkbook.RefreshAll.
| Private Sub Worksheet_Change (ByVal Target As Range)
ThisWorkbook.RefreshAll End Sub |
Nota : El código no cambia la fuente de datos. Entonces, si agrega datos debajo de los datos de origen, este código no incluirá esos datos automáticamente. Puede utilizar tablas de Excel para almacenar datos de origen. Si no desea usar tablas, también podemos usar VBA para incluir nuevos datos. Lo aprenderemos en el próximo tutorial.
Espero que este artículo sobre Cómo actualizar automáticamente los datos de la tabla dinámica en Excel sea explicativo. Encuentre más artículos sobre el cálculo de valores y fórmulas de Excel relacionadas aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico.
Cómo actualizar dinámicamente el rango de origen de datos de la tabla dinámica en Excel : Para cambiar dinámicamente el rango de datos de origen de las tablas dinámicas, usamos cachés dinámicos. Estas pocas líneas pueden actualizar dinámicamente cualquier tabla dinámica cambiando el rango de datos de origen. En VBA, use objetos de tablas dinámicas como se muestra a continuación …
Ejecutar macro si se realiza algún cambio en la hoja en el rango especificado : En sus prácticas de VBA, tendría la necesidad de ejecutar macros cuando cambia un cierto rango o celda. En ese caso, para ejecutar macros cuando se realiza un cambio en un rango objetivo, usamos el evento de cambio.
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.
El 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.
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.
Articulos populares :
50 accesos directos de Excel para aumentar su productividad : Acelere sus tareas en Excel. Estos atajos lo ayudarán a aumentar la eficiencia de su trabajo en Excel.
Cómo utilizar la función BUSCARV 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.
Cómo usar la función SI en Excel : La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO.
Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.