Crear un modelo de datos en Excel

Un modelo de datos le permite integrar datos de varias tablas, creando de forma eficaz un origen de datos relacional dentro de un libro de Excel. En Excel, los modelos de datos se usan de forma transparente y proporcionan datos tabulares usados en tablas dinámicas y gráficos dinámicos. Un modelo de datos se visualiza como una colección de tablas en una lista de campos y, la mayoría de las veces, ni siquiera sabrá que existe.

Antes de empezar a trabajar con el modelo de datos, necesita obtener algunos datos. Para ello, usaremos la experiencia Obtener transformación de & (Power Query), por lo que es posible que quiera dar un paso atrás y ver un vídeo, o seguir nuestra guía de aprendizaje sobre Obtener transformación & y Power Pivot.

¿Dónde se encuentra PowerPivot?

Excel 2016 & Excel para Microsoft 365 - Power Pivot se incluye en la cinta de opciones.

¿Dónde está Get & Transform (Power Query)?

Excel 2016 & Excel para Microsoft 365 : Obtener transformación de & (Power Query) se ha integrado con Excel en la pestaña Datos .

Introducción

En primer lugar, necesitas obtener algunos datos.

En Excel 2016 y Excel para Microsoft 365, use Data > Get & Transform Data > Get Data to import data from any number of external data sources, such as a text file, Excel workbook, website, Microsoft Access, SQL Server u otra base de datos relacional que contenga varias tablas relacionadas.
Excel le solicitará que seleccione una tabla. Si desea obtener varias tablas del mismo origen de datos, active la opción Habilitar selección de varias tablas . Al seleccionar varias tablas, Excel crea automáticamente un modelo de datos.

Nota: Para estos ejemplos, usamos un libro de Excel con detalles ficticios de alumnos en clases y calificaciones. Puede descargar nuestro libro de muestra Modelo de datos de estudiante y seguir los pasos. También puede descargar una versión con un modelo de datos completado..
Seleccione una o más tablas y, a continuación, haga clic en Cargar.

Si necesita editar los datos de origen, puede elegir la opción Editar . Para obtener más información, vea: Introducción al Editor de consultas (Power Query).

Ahora tiene un modelo de datos que contiene todas las tablas que importó y se mostrarán en la lista de campos de tabla dinámica.

Notas:

Los modelos se crean de forma implícita al importar dos o más tablas a la vez en Excel.
Los modelos se crean explícitamente cuando se usa el complemento Power Pivot para importar datos. En el complemento, el modelo se representa en un diseño con pestañas similar a Excel, donde cada pestaña contiene datos tabulares. Vea Obtener datos con el complemento Power Pivotpara conocer los conceptos básicos de la importación de datos con una base de datos de SQL Server.
Un modelo puede contener una tabla única. Para crear un modelo basado en solo una tabla, seleccione la tabla y haga clic en Agregar al modelo de datos en Power Pivot. Puede hacer esto si desea usar las características de Power Pivot, como conjuntos de datos filtrados, columnas calculadas, campos calculados, KPI y jerarquías.
Las relaciones de la tabla pueden crearse automáticamente si se importan tablas relacionadas que tienen relaciones de clave principal y externa. Excel puede usar normalmente la información importada de la relación como base para las relaciones de la tabla en el modelo de datos.
Para obtener sugerencias sobre cómo reducir el tamaño de un modelo de datos, vea Crear un modelo de datos eficiente en cuanto a memoria con Excel y Power Pivot.
Para una mayor exploración, vea Tutorial: Importar datos a Excel y Crear un modelo de datos.

Sugerencia: ¿Cómo puede saber si el libro tiene un modelo de datos? Vaya a Power Pivot > Administrar. Si ve datos como una hoja de cálculo, existe un modelo. Vea: Averigüe qué orígenes de datos se usan en un modelo de datos de libro para obtener más información.

Crear relaciones entre las tablas

El siguiente paso es crear relaciones entre las tablas para que pueda extraer datos de cualquiera de ellas. Cada tabla debe tener una clave principal o un identificador de campo único, como identificador de alumno o número de clase. La forma más sencilla es arrastrar y colocar esos campos para conectarlos en la vista de diagrama de Power Pivot.

Vaya a Power Pivot > Administrar.
En la pestaña Inicio , seleccione Vista de diagrama.
Se mostrarán todas las tablas importadas y es posible que quiera tardar algún tiempo en cambiar su tamaño en función del número de campos que tenga cada una.
Después, arrastre el campo de clave principal de una tabla a la siguiente. El ejemplo siguiente es la vista de diagrama de nuestras tablas de alumnos:

Hemos creado los siguientes vínculos:
- tbl_Students | Id. del alumno > tbl_Grades | Id. del alumno
  
  Es decir, arrastre el campo Id. de alumno de la tabla Alumnos al campo Id. de alumno de la tabla Calificaciones.
- tbl_Semesters | Id. de semestre > tbl_Grades | Semestre
- tbl_Classes | Número de clase > tbl_Grades | Número de clase
Notas:
- No es necesario que los nombres de campo sean los mismos para crear una relación, pero deben ser del mismo tipo de datos.
- Los conectores de la vista diagrama tienen un "1" en un lado y un "*" en el otro. Esto significa que existe una relación uno a varios entre las tablas y que determina cómo se usan los datos en las tablas dinámicas. Vea: Relaciones entre tablas en un modelo de datos para obtener más información.
- Los conectores solo indican que hay una relación entre las tablas. En realidad, no le mostrarán qué campos están vinculados entre sí. Para ver los vínculos, vaya a Power Pivot > Administrar relaciones > diseño > > Administrar relaciones. En Excel, puede ir a Relaciones > datos.

Usar un modelo de datos para crear una tabla dinámica o un gráfico dinámico

Un libro de Excel puede contener solo un modelo de datos, pero ese modelo puede contener varias tablas que se pueden usar repetidamente en todo el libro. Puede agregar más tablas a un modelo de datos existente en cualquier momento.

En Power Pivot, vaya a Administrar.
En la pestaña Inicio , seleccione Tabla dinámica.
Seleccione dónde desea colocar la tabla dinámica: una nueva hoja de cálculo o la ubicación actual.
Haga clic en Aceptar y Excel agregará una tabla dinámica vacía con el panel Lista de campos mostrado a la derecha.

A continuación, cree una tabla dinámica o cree un gráfico dinámico. Si ya ha creado relaciones entre las tablas, puede usar cualquiera de sus campos en la tabla dinámica. Ya hemos creado relaciones en el libro de ejemplo Modelo de datos de alumnos.

Agregar datos actuales y no relacionados a un modelo de datos

Suponga que ha importado o copiado una gran cantidad de datos que desea usar en un modelo, pero que no los ha agregado al modelo de datos. Insertar nuevos datos en un modelo es más fácil de lo que piensa.

Empiece seleccionando cualquier celda dentro de los datos que desea agregar al modelo. Puede ser cualquier rango de datos, pero los datos con formato de tabla de Excel son los mejores.
Utilice uno de estos métodos para agregar los datos:
Haga clic en Power Pivot > Agregar al modelo de datos.
Haga clic en Insertar > Tabla dinámica y, a continuación, active Agregar datos al modelo de datos en el cuadro de diálogo Crear tabla dinámica.

El intervalo o la tabla se agrega ahora al modelo como tabla vinculada. Para obtener más información acerca de cómo trabajar con tablas vinculadas en un modelo, vea Agregar datos con tablas vinculadas de Excel en Power Pivot.

Agregar datos a una tabla de Power Pivot

En Power Pivot, no puede agregar una fila a una tabla escribiendo directamente en una fila nueva como en una hoja de cálculo de Excel. Pero puede agregar filas copiando y pegando, o actualizando los datos de origen y actualizando el modelo de Power Pivot.