Tablas dinámicas sobre una conexión a datos. Actualizar origen de los datos


Cuando se crea una tabla dinámica, el origen de datos puede estar en el mismo libro, ó en otro archivo externo (csv, txt, mdb,csv...).

La ventaja de tener estos datos en un origen externo, es, que si se producen modificaciones importantes de los datos, (por ejemplo si semanalmente desde la aplicación de gestión y ventas de la empresa exportamos datos a un archivo de texto), no tendríamos que copiar estos datos a un libro de excel cada vez que se actualizan estos datos.

Este origen de datos externos se conecta al libro a través de una conexión de datos, que es un conjunto de información que describe cómo localizar la ubicación, iniciar una sesión y tener acceso al origen de datos externo.

Vamos a ver paso a paso como hacerlo



  1. Crear una conexión a un origen de datos externos (un archivo de texto)
  2. Crear una tabla dinámica sobre estos datos importados
  3. Como actualizar la conexión y la tabla dinámica

1. crear una conexión a un origen de datos externos.

Desde la ficha "datos" seleccionamos "desde texto" y buscamos la ubicación del archivo de texto y botón de "Importar".a continuación aparece un asistente donde le indicamos el tipo de archivo de texto ( en nuestro caso delimitado por tabuladores).


 Esta información nos la facilitará quien haya generado el archivo de texto. Si no lo sabemos, podemos hacer alguna prueba con otros delimitadores (comas, punto y coma) ó probar con ancho fijo.



Una vez finalizado el asistente nos preguntará donde queremos generar la tabla importada.

Importante, accedemos al botón de propiedades y desmarcamos "Solicitar nombre de archivo al actualizar", esto evitará que cada vez que queramos actualizar los datos en nuestro libro, con las modificaciones que haya sufrido el archivo de texto, nos pregunte por su nombre y ubicación. Además si consideramos que puede haber a lo largo del día varias actualizaciones, podemos indicarle un intervalo en minutos para que se actualice regularmente. Otra opción interesante es que cada vez que abramos nuestro libro de Excel, se actualice con los datos más recientes.


Ya tenemos una hoja de cálculo dependiente de un origen de datos que en nuestro caso es un archivo de texto. Podemos acceder a modificar las propiedades en cualquier momento y podemos actualizar manualmente los datos desde "Actualizar todo", con las opciones indicadas anteriormente no nos preguntará nada y comprobaremos que nuestra hoja de cálculo tiene más filas que antes y con nuevos datos.

2. Crear una tabla dinámica sobre estos datos importados

Este paso lo veremos rápidamente porque quizás sea el más conocido.
Estando dentro de nuestra tabla (que a su vez está conectada a un archivo externo), vamos a la ficha insertar-tabla dinámica. no modificamos nada y aceptamos. Se genera una nueva hoja con nuestro informe de tabla dinámica. Podemos incorporar a "fila" "columna" y datos los campos que nos interesen.



3.Como actualizar la conexión y la tabla dinámica

Supongamos que es lunes y se produce un volcado de los datos de ventas, se ha actualizado el archivo externo que contiene los datos. Esto se refleja en la tabla dinámica...NO

Debemos refrescar la conexión de los datos y refrescar la tabla dinámica.

refrescar la conexión de los datos lo tenemos en la ficha datos y refrescar tabla dinámica en opciones de tabla dinámica 


Trabajar con rangos dinámicos

En el paso 3 hemos visto que es necesario actualizar la tabla dinámica.

¿Qué solución tenemos, si el rango de datos origen, está cambiando constantemente?. No podemos estar actualizando el origen de datos de la tabla dinámica cada vez que se añada un nuevo registro.

La solución pasa por trabajar con rangos dinámicos. Crearemos un "Nombre de Rango", y al definirle el origen de los datos, le indicaremos que es un origen variable (dinámico). ¿Cómo?


Utilizando la función Desref (y Contara)
DESREF(ref; filas; columnas; [alto]; [ancho])

Por lo tanto, al definir el "Nombre del rango" indicaremos que se refiere a:
=DESREF(Hoja1!$A$1; 0; 0; CONTARA(Hoja1!$A:$A); CONTARA(Hoja1!$1:$1))

Esta fórmula nos devolverá un rango, que incluye las celdas contiguas a la celda A1 y con una altura y anchura del rango  igual al tamaño de la tabla origen de los datos.