Crear tablas dinámicas con datos externos

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