Crear un modelo de datos en Excel

Se aplica a

Excel para Microsoft 365 Excel 2024 Excel 2021

Obtener ayuda con Copilot

Un modelo de datos permite integrar datos de varias tablas y crear de forma eficaz un origen de datos relacionales dentro de un libro de Excel. En Excel, los modelos de datos se usan de forma transparente, proporcionando datos tabulares que se usan en tablas 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, suele trabajar con él a través de la lista de campos de tabla dinámica y es posible que no se dé cuenta de que está allí.

Antes de empezar a trabajar con el modelo de datos, necesita obtener algunos datos. Para ello, usaremos la experiencia Power Query Get & Transform, por lo que quizá quiera dar un paso atrás y ver un vídeo, o seguir nuestra guía de aprendizaje sobre Get & Transform y Power Pivot. Los datos deben estar en tablas (no solo en rangos de celdas) para que puedan cargarse y relacionarse correctamente.

Requisitos previos

¿Dónde se encuentra PowerPivot?

Excel para Microsoft 365: Power Pivot se incluye en la cinta de opciones.

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

Excel para Microsoft 365 - Obtener & transformar (Power Query) se ha integrado con Excel en la pestaña Datos.

Introducción

Primero, necesita obtener algunos datos.

Cree un nuevo libro o abra uno que no contenga los datos.
En la cinta de opciones de Excel para Microsoft 365, seleccione la pestaña Datos. En la sección Obtener & transformar datos, seleccione Obtener datos para importar datos de cualquier número de orígenes de datos externos, como un archivo de texto, un libro de Excel, un sitio web, Microsoft Access, SQL Server u otra base de datos relacional que contenga varias tablas relacionadas.
Excel le pide que seleccione una o varias tablas. Si desea obtener varias tablas del mismo origen de datos, active la casilla Seleccionar varios elementos .
1. Seleccione Transformar. Al seleccionar varias tablas, Excel crea automáticamente un modelo de datos. Para obtener más información, vea: Crear, cargar o editar una consulta en Excel (Power Query).
  
  Nota
  
  Para estos ejemplos, usamos un libro de Excel con detalles ficticios del alumno sobre las clases y las calificaciones. Puede descargar nuestro libro Modelo de datos de alumnos y seguir las instrucciones. También puede descargar una versión con un modelo de datos completo.
Ahora tiene un modelo de datos que contiene todas las tablas que ha importado y se mostrarán en la lista de campos de tabla dinámica.

Nota

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. Consulte Obtener datos mediante el complemento Power Pivot para aprender los conceptos básicos de la importación de datos mediante una base de datos de SQL Server.
Un modelo puede contener una tabla única. Para crear un modelo basado en una sola tabla, selecciónela y haga clic en Agregar a modelo de datos en Power Pivot. Puede hacerlo si desea usar 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 memoria con Excel y Power Pivot.
Para obtener más información, consulte Tutorial: importar datos a Excel y crear un modelo de datos.

Recomendación

¿Cómo puede saber si el libro tiene un modelo de datos? Vaya a AdministracióndePower Pivot>. Si ve datos similares a hojas de cálculo, entonces existe un modelo. Vea: Para obtener más información, averigüe qué orígenes de datos se usan en el modelo de datos de un libro .

Crear relaciones entre las tablas

El siguiente paso consiste en crear relaciones entre las tablas para extraer datos de cualquiera de ellas. Cada tabla debe tener una clave principal o identificador de campo único, como el identificador de alumno o el 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 AdministracióndePower Pivot>.
En la pestaña Inicio , seleccione Vista de diagrama.
Se mostrarán todas las tablas importadas, y es posible que le interese un tiempo cambiar su tamaño en función del número de campos que tenga cada una.
A continuación, arrastre el campo de clave principal de una tabla a la siguiente. El siguiente ejemplo es la vista de diagrama de nuestras tablas de alumnos:

Hemos creado los siguientes vínculos:
- tbl_Students | Id. de > estudiante tbl_Grades | Id. de estudiante
  En otras palabras, arrastre el campo Id. de estudiante de la tabla Estudiantes al campo Id. de estudiante de la tabla Calificaciones.
- tbl_Semesters | Identificación > semestral tbl_Grades | Semestre
- tbl_Classes | Número de > clase tbl_Grades | Número de clase
Nota
- Los nombres de campo no tienen que ser iguales para crear una relación, pero sí tienen que ser del mismo tipo de datos.
- Los conectores de la vista de diagrama tienen un "1" en un lado y un "*" en el otro. Esto significa que hay una relación de uno a varios entre las tablas, lo que determina cómo se usan los datos en las tablas dinámicas. Consulte: Relaciones entre tablas en un modelo de datos para obtener más información.
- Los conectores solo indican que existe 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>de diseño>. Administrar relaciones. En Excel, puede ir aRelaciones de 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 a lo largo del 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 quiere colocar la tabla dinámica: una hoja de cálculo nueva o la ubicación actual.
Haga clic en Aceptar y Excel agregará una tabla dinámica vacía con el panel Lista de campos 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

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

Para empezar, seleccione cualquier celda de los datos que quiera agregar al modelo. Puede ser cualquier rango de datos, pero lo mejor es tener un formato de tabla de Excel .
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 active Agregar estos 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 Power Pivot

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