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
- Crear una conexión a un origen de datos externos (un archivo de texto)
- Crear una tabla dinámica sobre estos datos importados
- 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.
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.
