Cómo crear una tabla dinámica con el modelo de datos

Las tablas dinámicas tradicionales son una característica increíble de Excel, pero no están exentas de límites. Muchas de las restricciones típicas se eliminan cuando se utiliza el modelo de datos en lugar de una única tabla de Excel. Si desea aprender cómo crear una tabla dinámica utilizando el modelo de datos y aprender qué es el modelo de datos, prepárese… esta será una publicación divertida.

Descripción general

Antes de llegar demasiado lejos, saltemos hasta 30.000 pies. Para empezar, ¿qué es exactamente el modelo de datos? El modelo de datos proporciona una manera de organizar tablas y fórmulas que se pueden usar en una tabla dinámica. El modelo de datos viene con Excel 2016+ para Windows y anteriormente estaba disponible como complemento de Power Pivot. El resto de este artículo se presenta con Excel 2016 para Windows.

Crear una tabla dinámica a partir del modelo de datos en lugar de una única tabla de Excel ofrece numerosas ventajas. Éstos son sólo algunos para ayudarnos a comenzar.

• Podemos crear una tabla dinámica que utilice varios campos de varias tablas.
• Las fórmulas que podemos escribir superan con creces las disponibles en una tabla dinámica tradicional. Se utiliza un lenguaje llamado DAX para escribir las fórmulas y proporciona muchas funciones poderosas.
• Podemos seleccionar filas y columnas usando conjuntos con nombre.
• Podemos conectarnos directamente a la fuente de datos (en lugar de tener que copiar/pegar datos en una hoja de trabajo), usar una consulta Obtener y transformar (para limpiar los datos antes de que lleguen) y conectarnos a múltiples fuentes de datos (por ejemplo, un archivo csv, una tabla de base de datos y un libro de Excel) en un solo modelo.
• Una vez creado, podemos simplemente actualizar el informe en períodos posteriores (en lugar de tener que realizar todo el proceso de exportación, limpieza, importación y combinación en un solo proceso de tabla de datos).

Y estos son sólo algunos de los aspectos más destacados.

En esta publicación, nos prepararemos construyendo una tabla dinámica a partir de dos tablas. Creé un video y una narrativa completa con todos los detalles paso a paso a continuación.

Video

Detalles

Nuestro plan es crear una tabla dinámica a partir de dos tablas. Una tabla de datos tiene las transacciones y otra tabla almacena el plan de cuentas. Históricamente, necesitaríamos usar BUSCARV o algo así para combinar primero estas tablas en una sola tabla para usar con una tabla dinámica tradicional. Aquí usaremos el modelo de datos. Seguiremos estos pasos juntos:

• Habilitar el modelo de datos
• Importar las tablas de datos.
• Definir relaciones
• Construya la tabla dinámica

Empecemos.

Habilitar el modelo de datos

Primero, necesitaremos habilitar el complemento Power Pivot. Si tiene Excel 2016+ para Windows, simplemente haga clic en el comando de cinta Datos Administrar modelo de datos como se muestra a continuación:

Nota: dependiendo del tamaño de su pantalla, es posible que vea solo el ícono y no la etiqueta.

Al hacer clic en él la primera vez, se le pedirá que habilite los complementos:

Una vez que haga clic en Habilitar, estará listo y debería ver una pestaña de la cinta de Power Pivot. ¡Hurra!

Nota: Si tiene una versión anterior de Excel para Windows, deberá descargar e instalar el complemento gratuito Power Pivot desde el sitio web de Microsoft y seguir las instrucciones de instalación para su versión de Excel.

Importar las tablas de datos.

A continuación, importamos las tablas de datos. En nuestro caso, tenemos algunas transacciones almacenadas en un libro de trabajo DataTable. Las transacciones tienen el número de cuenta pero no el nombre de cuenta relacionado. Afortunadamente, tenemos algo llamado plan de cuentas, que se almacena en el libro de trabajo LookupTable.

El paso para importar tablas de datos variará dependiendo de dónde se encuentren sus datos de origen. Para comenzar, haga clic en el comando de cinta Power Pivot Administrar . Esto abre la ventana de Power Pivot, que se muestra a continuación.

Utilice el comando Obtener datos externos para señalar la fuente de datos subyacente.

En nuestro caso, los datos están en un par de archivos de Excel, por lo que usamos la opción Obtener datos externos De otras fuentes y luego seleccionamos Archivo de Excel en el cuadro de diálogo resultante. Buscamos el libro deseado y marcamos Usar la primera fila como encabezados de columna . Terminamos el asistente y bam, los datos se cargan en nuestro modelo de datos, como se muestra a continuación.

Nota: si está creando un modelo de datos dentro del libro que tiene las tablas, puede usar el comando Power Pivot Agregar al modelo de datos.

A continuación, hacemos lo mismo para extraer datos del archivo LookupTable Excel. La ventana actualizada de Power Pivot se muestra a continuación.

Con nuestros datos cargados en el modelo de datos, debemos decirle a Excel cómo se relacionan las tablas (qué columnas son comunes entre las tablas) definiendo las relaciones.

Definir relaciones

Hay varias formas de definir relaciones, pero mi forma favorita es utilizar la vista de diagrama visual. Para alternar entre la Vista de datos (que se muestra arriba) y la Vista de diagrama (que se muestra a continuación), simplemente haga clic en el comando Inicio Vista de diagrama . Ahora veremos las tablas con los nombres de las columnas (en lugar de ver las transacciones de datos), como se muestra a continuación.

Para definir la relación, haga clic en el nombre de la columna de DataTable y arrástrelo a la columna relacionada en LookupTable . En nuestro caso, estamos relacionando la columna AcctNum de DataTable con la columna AcctNum de LookupTable. Excel muestra la relación como se muestra a continuación.

Con nuestra relación definida, ahora podemos construir la tabla dinámica.

Construya la tabla dinámica

En la ventana de Power Pivot, simplemente hacemos clic en el comando Tabla dinámica Tabla dinámica y seleccionamos una hoja de trabajo nueva o una hoja de trabajo existente en el cuadro de diálogo Crear tabla dinámica resultante . Una vez que hacemos clic en Aceptar, bam, vemos el familiar panel de campo de tabla dinámica.

Pero espera un segundo… si lo miras más de cerca, se ve un poco diferente del panel de campo tradicional. Normalmente vemos una lista de campos que podemos insertar en el informe. Pero ahora, realmente vemos las tablas y podemos expandir cada tabla para ver los campos en cada una, como se muestra a continuación.

Y sí, podemos seleccionar campos de una o ambas tablas para nuestro informe. Por ejemplo, queremos el nombre de cuenta de la tabla de búsqueda en filas y el campo monto de la tabla de datos como valores. Y, ¡bam… listo!

Ahora, si su primera reacción es que hubiera sido más fácil usar BUSCARV para crear una sola tabla, lo entiendo totalmente. Pero aquí está la cuestión. Este ejemplo es bastante simple porque incluye una única tabla de búsqueda. El modelo de datos admite numerosas tablas de búsqueda, por ejemplo, un plan de cuentas y una tabla de calendario, una lista de departamentos, etc. Además, además de tener varias tablas de búsqueda en su modelo de datos, también puede tener varias tablas de datos .

Además, está la cuestión de actualizar nuestro informe de forma continua. Dado que no utilizamos BUSCARV para recuperar valores relacionados, no necesitamos cuidar un montón de fórmulas de búsqueda cada mes. A medida que se actualiza la fuente de datos externa, tal vez para una nueva cuenta o nuevas transacciones, podemos simplemente Actualizar y los nuevos datos fluyen hacia el informe. Como puedes imaginar, esto abre muchas posibilidades interesantes y puede ayudar a ahorrar tiempo en nuestros libros de uso recurrente

Archivos de muestra:

• Tabla de datos.xlsx
• Tabla de búsqueda.xlsx