Crear tablas dinámicas con datos externos

miércoles, octubre 04, 2017

No sé cuántos de mis lectores usan o conocen la opción "Utilice una fuente de datos externa" para crear tablas dinámicas


Cuando no existe la necesidad de hacer transformaciones a los datos originales, esta opción es muy conveniente (cuando necesitamos transformar datos Power Query es la herramienta más apropiada).
La idea del post surgió a partir de la consulta de un colega en el trabajo. Su problema: tenía que construir un reporte con una tabla dinámica, pero la tabla con los datos de origen tenía varios cientos de miles de filas, número que iba incrementándose cada mes. A corto plazo la base de datos superaría el millón de filas, más allá de la capacidad de una hoja de Excel.
Inmediatamente comprendí que mi colega importaba los registros (filas) de la base de datos a una hoja de Excel, lo cual es innecesario.
Las ventajas de crear una conexión sin importar físicamente los datos a una hoja de Excel son evidentes:

  • evitamos duplicar los datos (también en a base de datos y también en la hoja);
  • nuestro archivo será mucha más liviano;
  • cuando hay cambios en la base de datos (nuevos registros, por ejemplo) basta con apretar el botón Actualizar para refrescar los datos;
  • podemos desconectar las tablas dinámicas creadas del origen y así proteger datos discretos. Sobre este tema pondré un ejemplo al final del post.
Para crear una tabla dinámica con esta técnica empezamos con una hoja vacía. Abrimos el menú Insertar-Tabla dinámica-Utilice una fuente..." y apretamos el botón "Elegir conexión".


Si la conexión que buscamos no aparece en la lista de conexiones disponibles, usamos el botón "Examinar en busca de más" (en la parte inferior izquierda del formulario). En este ejemplo voy a crear una conexión a la base de datos Northwind que puede estar en mi máquina, en la red a la cual estoy conectado o en la Web. Al apretar el botón aceptar veremos l plantilla de la tabla dinámica vacía y la lista de campos que podemos utilizar para crear el reporte dinámico


A partir de aquí podemos crear nuestros reportes sin que los datos de base estén físicamente en la hoja. Por ejemplo, este reporte de ventas donde usamos el campe de Filtro para filtrar los informes por vendedor


Una de las ventajas, como señalamos, es que nuestro archivo será más liviano. El archivo de este ejemplo peso 132 KB. Si hubiéramos importado los datos a una hoja del cuaderno pesaría 427 KB, tres veces más.

La otra ventaja de este método surge cuando trabajamos con datos que requieren discreción. Siguiendo con nuestro ejemplo supongamos que generamos un reporte para cada agente de ventas (que hacemos con facilidad con la técnica que muestro en este post) pero no queremos que puedan ver los resultados de los otros colegas.

Una posibilidad es copiar y pegar los datos en otra hoja con la opción "sólo valores". Pero esta técnica tiene dos inconvenientes:
  • es trabajosa si se trata de muchas hojas (agentes en nuestro ejemplo);
  • al pegar los datos los formatos de la tabla desaparecen (podemos usar el tip que muestro en este post, pero también es trabajoso).
Si creamos los reportes dinámicos en base a una tabla dinámica "conectada", todo lo que tenemos que hacer es cancelar al conexión


Excel transforma la tabla dinámica en una matriz de valores, sin fórmulas, sin conexiones y manteniendo los formatos. Veamos el proceso en este video


4 comments:

Anónimo,  04 octubre, 2017 14:31  

Felicidades por tu blog, una duda... que diferencia hay entre utilizar este sistema o Microsoft query ( el de toda la vida ). yo siempre utilizo este ultimo y asi me ahorro de crear muchas conexiones a cada base de datos access.

Jorge Dunkelman 04 octubre, 2017 16:29  

MS Query permite ir más allá de crear una conexión (hay varios posts sobre el tema en mi blog). Aquí estoy mostrando otra posibilidad de conectarse a datos externos con algunas ventajas.
Por otro lado, MS Query nunca fue desarrollado completamente y hoy en día tenemos Power Query para conectarnos y transformar datos. Podemos decir sin dudar que MS Query es una herramienta caduca.

Anónimo,  04 octubre, 2017 22:53  

Buenas noches Jorge,

A mi en las conexiones no me salen esas que te aparecen en la captura (MS MONEY,...)

Un Saludo

Jorge Dunkelman 05 octubre, 2017 10:08  

Lo que aparece en la ventanilla de las conexiones depende que las hayas usado con anterioridad o que vengan con la instalación del Office. Para el caso, no te hacen falta.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP