Saltar al contenido principal
InicioTutorialesHojas de cálculo

Cómo crear modelos de datos en Excel: Guía completa

Creamos modelos de datos formateando los datos, creando relaciones, utilizando Power Query y aprovechando Power Pivot para una integración y análisis de datos sin fisuras.
Actualizado 29 jul 2024  · 13 min leer

Los modelos de datos nos permiten cargar, integrar y analizar datos de múltiples fuentes, facilitando una visión más dinámica y perspicaz de los datos. 

A lo largo de las secciones del tutorial, exploraremos varias formas de crear modelos de datos en Excel, incluyendo el uso de tablas existentes, la importación de nuevas tablas y el aprovechamiento de herramientas como Power Query, Power Pivot y PivotCharts.   

¡Pongámonos en marcha!

Cómo crear un modelo de datos en Excel

Éstos son los pasos generales que hay que seguir para crear un modelo de datos en Excel:

  1. Formatea tus datos como tablas.
  2. Crea relaciones entre las tablas.
  3. Utiliza Power Query para cargar y transformar datos.
  4. Utiliza Power Pivot para el modelado avanzado de datos.
  5. Utiliza Tablas dinámicas y Gráficos dinámicos para visualizar tus datos.

Cuándo utilizar un modelo de datos en Excel

Crear un modelo de datos en Excel es especialmente útil en varios escenarios comunes, como la limpieza de datos, la integración y transformación de datos y el análisis de datos complejos. He aquí algunas de las principales ventajas.

  • Facilita la limpieza de datos: Los modelos de datos ayudan a limpiar y normalizar diferentes conjuntos de datos para mantener la uniformidad y la coherencia. Esto es fundamental cuando se trabaja con datos de distintas fuentes, lo que requiere mezclarlos y preprocesarlos. 
  • Realiza o resuelve la integración y transformación de datos: Con el modelo de datos establecido, todos los datos disponibles de cualquier otra fuente pueden integrarse y transformarse fácilmente, y los usuarios de los datos pueden recibir una visión unificada fácil de procesar y analizar.
  • Mejora la visualización de datos: Los modelos de datos apoyan el desarrollo de visualizaciones avanzadas de datos, como los gráficos dinámicos y los informes de Power View. 
  • Mejora la seguridad y la gestión de los datos: Con un modelo de datos, se puede realizar un acceso controlado a diferentes partes de tus datos. Te resultará más fácil gestionar los permisos y los datos dentro del modelo para reducir los riesgos de acceso no autorizado y de violación de datos.
  • Agiliza la notificación de datos: Mediante los modelos de datos, la elaboración de informes sobre los datos puede automatizarse por completo, facilitando así el proceso de elaboración de informes y de toma de decisiones. Esto hace posible que los informes y cuadros de mando se ajusten dinámicamente en caso de que se introduzcan nuevos datos. Así, todas las partes interesadas reciben información actualizada para su uso.
  • Maneja grandes conjuntos de datos: Los modelos de datos pueden trabajar con grandes conjuntos de datos en Excel, superando las limitaciones de las hojas de cálculo tradicionales de Excel.
  • Permite el Análisis de Datos Complejos: La función de crear relaciones entre varias tablas de datos permite realizar análisis avanzados de datos que podrían no ser fáciles con las funciones habituales de Excel.

Cómo crear un modelo de datos importando datos

En esta sección, recorreremos el proceso de creación de un modelo de datos en Excel importando conjuntos de datos de muestra. Utilizaremos dos conjuntos de datos de muestra: Pedido y producto. He creado un repositorio público en GitHub llamado Excel-Data-Model para que puedas descargar los conjuntos de datos y seguir este tutorial.

El primer paso es importar los datos a Excel. Empieza abriendo un nuevo libro de Excel y haz clic en la pestaña Datos. En Obtener datos, selecciona Obtener datos > De archivo > De libro de Excel.

Utilizar modelos de datos de Excel para importar datos a Excel

Importar datos a Excel. Imagen del autor

Selecciona el libroOrder.xlsx y haz clic en Importar. Se abrirá la ventana del Navegador: Selecciona la tabla Sheet1.

Ventana del navegador de modelos de datos de Excel

La ventana del Navegador en Excel. Imagen del autor

Haz clic en la opción Cargar en, que abrirá la ventana Importar datos. En el cuadro de diálogo, selecciona Only Create Connection y marca Add this data to the Data Model. Haz clic en Aceptar.

 Ventana Importar datos con modelos de datos de Excel

Ventana Importar datos. Imagen del autor

Repite los pasos anteriores para cargar la hoja Product. Una vez completadas estas dos tareas, tu libro de Excel tendrá este aspecto:

 Consultas y panel de conexiones en el modelo de datos de Excel

Panel Consultas y Conexiones. Imagen del autor

¡Enhorabuena! Has importado las dos hojas en el archivo Excel. El siguiente paso es establecer relaciones entre las tablas importadas. Para ello, ve a la pestaña Herramientas de Datos y haz clic en Relaciones.

Pestaña Herramientas de datos en el modelo de datos de Excel

Pestaña Herramientas de datos. Imagen del autor 

Aparecerá una nueva ventana.

Gestionar relaciones en el modelo de datosVentana de ExcelVentana Gestionar relaciones. Imagen del autor

Haz clic en Nuevo para crear una relación. Se abrirá la ventana emergente Crear relación. Define la relación como se muestra a continuación. 

Ventana Crear relación de ExcelVentana Crear relación. Imagen del autor

Haz clic en Aceptar. La relación se establece, y puedes confirmarla en la pestaña Conexiones.

Panel Consultas y Conexiones de ExcelPanel Consultas y Conexiones. Imagen del autor

La siguiente etapa consiste en crear la Tabla dinámica. Ve a la pestaña Insertar y haz clic en Tabla dinámica. A continuación, en el cuadro de diálogo Tabla dinámica, selecciona Utilizar el modelo de datos de este libro.

Tabla dinámica en el modelo de datosCuadro de diálogo de ExcelCuadro de diálogo Tabla dinámica. Imagen del autor

Selecciona la ubicación de la Tabla dinámica. Haz clic en Aceptar. Se abrirá la Tabla dinámica y podrás ver los campos en Campos de la Tabla dinámica.

en el modelo de datosCampos PivotTable de ExcelCampos PivotTable. Imagen del autor

Para construir la Tabla dinámica, realiza el siguiente paso.

En el panel Campos de la tabla dinámica, arrastra los campos a las áreas Filas, Columnas, Valores y Filtros:

  • Filas: ProductName
  • Valores: Cantidad

La Tabla dinámica resultante tendrá este aspecto:

Panel Campos de la tabla dinámica en el modelo de datos de Excel

Panel Campos de la tabla dinámica. Imagen del autor

Puedes personalizar la Tabla dinámica y darle el formato que necesites.

Cómo crear un modelo de datos con Power Query 

Para esta sección, utilizaremos los datos almacenados en el archivo de Excel denominado, Sample_Data. Este archivo también está disponible en el mismo enlace del apartado anterior. Hay dos tablas en la hoja llamada, Data, que tienen los siguientes registros.

Tablas Excel para unir de ExcelTablas Excel para unir. Imagen del autor

Construiremos modelos de datos en esta hoja Excel existente. Para empezar, selecciona cualquier celda de la tabla Customer ID and Name y, a continuación, pulsa Ctrl + T.

de una tabla en el modelo de datosSeleccionar una celda de ExcelSeleccionar una celda. Imagen del autor

Se abrirá la ventana emergente Crear tabla, como se muestra a continuación.

Crear tabla emergente en el modelo de datos de Excel

Crear tabla emergente. Imagen del autor

 Haz clic en Aceptar y ya tendrás lista la tabla. 

Modelo de datos PivotTable en ExcelPivotTable en Excel. Imagen del autor

Ahora vamos a dar un nombre a esta tabla. Ve a la ventana contextual Diseño de tabla y en Nombre de tabla, cambia el nombre de la tabla a CustomerPQ. Ten en cuenta que también puedes darle cualquier otro nombre. 

Ventana contextual de diseño de tablas en el modelo de datos de Excel

Ventana contextual de Diseño de la Tabla. Imagen del autor

Ahora esta tabla está configurada como CustomerPQ.

Tablas en el modelo de datosVentana contextual de Diseño de de ExcelVentana contextual de Diseño de Tablas. Imagen del autor

Repite el proceso para la segunda tabla. Esta vez, una vez creada la tabla, establece el nombre PurchasePQ.

en ExcelDos tablas en Excel listas para el Modelo de Datos Dos tablas en Excel listas para el Modelo de Datos. Imagen del autor

Ahora, la tabla activa es la tabla PurchasePQ. Ve a Datos, y selecciona Desde Rango de Tablas. Aparecerá la siguiente hoja.

en el modelo de datosAbrir el Editor de Power Query de ExcelAbrir el Editor de Power Query. Imagen del autor

Para crear una conexión y añadir la tabla Purchase PQ al Modelo de datos, haz clic en la pestaña desplegable de Cerrar y cargar, y selecciona Cerrar y cargar a...  

Cerrar el Editor de Power Query de ExcelCerrar el Editor de Power Query. Imagen del autor

Se abrirá una ventana Importar Datos, que debes rellenar de la siguiente manera:

Ventana Importar datos en el modelo de datos de ExcelVentana Importar datos. Imagen del autor 

Una vez completados los pasos anteriores, verás el cuadro Consultas y Conexiones.

Cuadro Consultas y Conexiones de ExcelCuadro Consultas y Conexiones. Imagen del autor

Ahora, añade la tabla CustomerPQ a la conexión. Para ello, haz clic en cualquier celda de la tabla, ve a Datos y selecciona Desde rango de tabla. Aparecerá la siguiente hoja.

Tablas listas para el Modelo de Datos de ExcelTablas listas para el Modelo de Datos de Excel. Imagen del autor

Repite el proceso que has completado para la tabla PurchasePQ, y rellena la ventana Importar datos como se muestra a continuación:

Ventana Importar datos en el modelo de datos de ExcelVentana Importar datos. Imagen del autor 

 Pulsa OK. La pestaña Consultas y Conexiones mostrará ahora las dos tablas.

Panel Consultas y Conexiones de ExcelConsultas y Conexiones. Imagen del autor

Ahora estamos preparados para establecer relaciones. Dentro de la pestaña Datos, ve a la sección Herramientas de Datos, y haz clic en Gestionar Modelo de Datos.

Panel Herramientas de Datos en el modelo de datos de ExcelHerramientas de Datos. Imagen del autor

Se abrirá la página Power Pivot for Excel.

Modelo de datos de Power Pivot para ExcelPower Pivot para Excel. Imagen del autor

Ahora, para crear una relación entre las dos tablas, haz clic en la Vista Diagrama.

vista en el modelo de datosPanel de de ExcelPanel de vista. Imagen del autor

 Se abrirá una pantalla como la que se muestra a continuación. 

diagrama en el modelo de datosVista de de ExcelVista de diagrama. Imagen del autor

Puedes ver que hay dos mesas, pero aún no están conectadas. Para crear la relación, ve a la pestaña Diseño y haz clic en la pestaña Crear relación.

en el modelo de datosPestaña Diseño de ExcelPestaña Diseño. Imagen del autor

Se abrirá una nueva ventana. Rellena los datos como se indica a continuación:

en el modelo de datosCrear una ventana de Relación de ExcelCrear una ventana de Relación. Imagen del autor

Pulsa OK. Te darás cuenta de que la relación ya está establecida.

en el modelo de datosVentana que muestra que se ha establecido una relación de ExcelVentana que muestra que se ha establecido una relación. Imagen del autor 

Guarda el Power Pivot utilizando la pestaña Guardar o Ctrl + S, y cierra la pestaña. Se muestra la siguiente salida.

ventana emergente Contenido en el modelo de datosHabilitar la de ExcelHabilitar la ventana emergente Contenido. Imagen del autor

Haz clic en Habilitar contenido para crear la conexión. Ahora la conexión está establecida. Ya podemos utilizar la Tabla dinámica. Para ello, ve a Insertar y haz clic en la opción Tabla dinámica, y selecciona Desde modelo de datos.

Panel PivotTable en el modelo de datos de ExcelPanel PivotTable. Imagen del autor

Aparecerá una ventana emergente como ésta:

Pestaña Tabla dinámica del modelo de datos Pestaña Tabla dinámica del modelo de datos. Imagen del autor

Haz clic en Nueva hoja de cálculo y en Aceptar. Se crea una nueva hoja llamada Sheet1.

PivotTable en ExcelHoja Hoja PivotTable. Imagen del autor 

En los Campos de la Tabla dinámica, puedes encontrar las tablas.

en el modelo de datosEtiquetas bajo Campos PivotTable de ExcelEtiquetas bajo Campos PivotTable. Imagen del autor

Selecciona Customer Name en la tabla CustomerPQ. A continuación, en PurchasePQ, selecciona Purchase. Los Campos de la PivotTable tendrán este aspecto:

en Excel con nuestro modelo de datos deSeleccionar elementos en los campos de la tabla dinámica ExcelSeleccionar elementos en los campos de la tabla dinámica. Imagen del autor 

Esto generará la Tabla dinámica dentro de la hoja que tendrá el siguiente aspecto:

en el modelo de datosSalida de tablas dinámicas de ExcelSalida de tablas dinámicas. Imagen del autor

Bingo! De este modo, puedes utilizar Power Query para crear el Modelo de Datos y utilizar Power Pivot para crear informes resumidos.

Cómo crear un modelo de datos directamente en Power Pivot

En esta sección, utilizarás Power Pivot para crear un modelo de datos y preparar un informe resumido. 

Para empezar, ve a la hoja llamada Power Pivot en los datos que hemos comentado antes, y crea las tablas para nuestros dos registros.

en el modelo de datosRegistros que se utilizarán de ExcelRegistros que se utilizarán. Imagen del autor 

Ya hemos visto cómo crear tablas en las secciones anteriores, así que sigue esos pasos para crear las dos tablas.

La única diferencia sería que estableceremos nuevos nombres para estas tablas, de modo que podamos diferenciarlas de la sección anterior: 

  • Establece el nombre de la primera tabla en CustomerPP.
  • Establece el nombre de la segunda tabla en PurchasePP.

Dos tablas utilizadas para el modelo de datos de Excel

Datos Tablas de los registros. Imagen del autor

Se crean las dos tablas y el siguiente paso es añadirlas al modelo de datos. Para ello, ve a Power Pivot, y selecciona Añadir a Modelo de Datos.

Añadir al modelo de datos de Excel en PowerPivot en Excel

Añadir a Modelo de Datos en PowerPivot. Imagen del autor

Se abrirá una ventana emergente de Power Pivot for Excel con el siguiente aspecto.

Ventana emergente de Power Pivot para Excel con el modelo de datos de Excel

Ventana emergente de Power Pivot para Excel. Imagen del autor 

 

Guarda y cierra esta ventana. Repite el mismo proceso con la tabla CustomerPP para añadirla al modelo de datos.

Ventana emergente de Power Pivot para Excel con el modelo de datos de Excel

Ventana emergente de Power Pivot para Excel. Imagen del autor

Una vez añadidas estas tablas al modelo de datos, puedes establecer la relación entre ellas utilizando Power Pivot. Para ello, haz clic en lavista de diagrama .

Vista de diagrama en el modelo de datos de Excel

Vista de diagrama. Imagen del autor

A continuación, ve a Diseño y haz clic en Crear relación.

Crear una ficha Relación en el modelo de datos de Excel

Pestaña Crear una relación. Imagen del autor

Se abrirá una nueva ventana. Rellena los datos como se indica a continuación:

Crear una relación entre dos tablas utilizando Modelos de datos en Excel

Crea una relación entre dos tablas. Imagen del autor

Haz clic en OK. Te darás cuenta de que la relación se ha establecido.

 Las relaciones se establecen en el Modelo de datos de Excel

Se establecen relaciones. Imagen del autor

A partir de aquí es fácil crear una Tabla dinámica, como hemos hecho en el apartado anterior. El resultado final debería ser así.

Resumen de la tabla dinámica con el modelo de datos de Excel

Resumen de la tabla dinámica. Imagen del autor

Siguiendo los pasos anteriores, puedes utilizar Power Pivot para crear el Modelo de Datos y crear un análisis resumido.

Cómo crear un modelo de datos con PivotTable o PivotChart

Una vez creada la Tabla dinámica, es fácil insertar un Gráfico dinámico. Con la tabla dinámica (Sheet 1 ) seleccionada, ve a la pestañaInsertar.

Resumen de una tabla dinámica dentro de un modelo de datos de Excel

Resumen de la tabla dinámica. Imagen del autor

Haz clic en Gráfico dinámicoselecciona el tipo de gráfico y haz clic en Aceptar.

Utilizar Modelos de Datos en Excel con las opciones de Insertar Gráfico

Opciones de Insertar Gráfico. Imagen del autor

Esto creará el siguiente Gráfico Dinámico.

Utilizar modelos de datos en Excel con gráficos dinámicos

Visualización de gráficos dinámicos. Imagen del autor

Puedes personalizar el Gráfico dinámico:

  • Ajustar los elementos del gráfico, como títulos, leyendas y etiquetas de los ejes, y
  • Formatear el gráfico para mejorar su aspecto y legibilidad.

Genial, has utilizado la Tabla dinámica para construir tu primera visualización.

Preguntas y problemas comunes sobre el modelo de datos de Excel

Éstas son algunas de las preguntas y problemas que suelen asociarse a los modelos de Datos de Excel:

¿Qué debo hacer si un problema con el modelo de datos impide que Microsoft Excel abra un libro?

Este problema puede deberse a la corrupción del modelo de datos o a problemas de compatibilidad. Aquí tienes los pasos para resolverlo:

  1. Abrir en Modo Seguro: Prueba a abrir Excel en Modo Seguro manteniendo pulsada la tecla Ctrl mientras inicias Excel. Esto a veces puede evitar el problema.
  2. Buscar actualizaciones: Asegúrate de que Excel está actualizado yendo a Archivo > Cuenta > Opciones de actualización.
  3. Oficina de Reparaciones: Utiliza la herramienta de reparación de Office. Ve a Panel de control > Programas y características, selecciona tu instalación de Office y elige Cambiar > Reparar.
  4. Desactivar complementos: Ve a Archivo > Opciones > Complementos y desactiva todos los complementos. Reinicia Excel e intenta abrir de nuevo el libro.
  5. Extraer datos: Si no puedes abrir el libro de trabajo, intenta utilizar una herramienta de recuperación de datos o ponte en contacto con el soporte técnico de Microsoft para obtener ayuda avanzada.

¿Cómo puedo solucionar errores en el modelo de datos?

Para solucionar errores en el modelo de datos:

  1. Comprueba los tipos de datos: Asegúrate de que los tipos de datos de las columnas utilizadas para las relaciones son coherentes.
  2. Validar relaciones: Comprueba que todas las relaciones están correctamente definidas.
  3. Revisa los cálculos: Comprueba si hay errores en las columnas y medidas calculadas.
  4. Actualizar datos: Asegúrate de que todas las conexiones de datos están actualizadas.
  5. Mensajes de error: Revisa cualquier mensaje de error en la ventana de Power Pivot para detectar problemas específicos.

Siguiendo estos pasos, podrás gestionar y solucionar eficazmente tu modelo de datos en Excel, garantizando un análisis de datos fluido y eficaz. El soporte técnico de Microsoft es otro recurso útil para solucionar problemas. 

Reflexiones finales

¡Gracias por completar el tutorial! Has aprendido un concepto importante en tu viaje para dominar el análisis de datos con Excel. La creación de modelos de datos mejorará significativamente tu capacidad de análisis de datos y la de tu organización. También aumenta tu eficacia en la gestión de diferentes tablas de datos y registros. 

Para aprender más, considera la posibilidad de explorar las siguientes fuentes:   

  • Introducción a Excel: Una guía completa para iniciarte en Excel, que cubre las funcionalidades y características básicas.
  • Análisis de datos en Excel: Aprende diversas técnicas y herramientas de análisis de datos disponibles en Excel para mejorar tu capacidad analítica.
  • Fundamentos de Excel: Un curso que cubre los conceptos y operaciones fundamentales en Excel para construir una base sólida.
  • Visualización de datos en Excel: Explora cómo crear visualizaciones impactantes e interactivas utilizando las herramientas integradas de Excel.
  • Introducción a Power Query en Excel: Comprende cómo utilizar Power Query para importar, limpiar y transformar datos de forma eficaz en Excel.

Además, aprende Cómo obtener una certificación Microsoft Excel para validar tus conocimientos.

Preguntas frecuentes

¿Qué es un modelo de datos en Excel y para qué sirve?

Con un modelo de datos en Excel, es posible unir datos de varias fuentes en una sola fuente de datos. Esto te permite no sólo realizar análisis complejos mediante Tablas dinámicas y Gráficos dinámicos, sino también realizar cálculos y relaciones de muchos a muchos sin redundancia en tus datos.

¿Puedo actualizar mi modelo de datos si cambian mis datos de origen, y cómo lo hago?

Sí, puedes actualizar fácilmente tu modelo de datos si cambian tus datos de origen. Para ello, sólo tienes que ir a la pestaña Datos y hacer clic en Actualizar todo. Esto actualizará los datos de tu modelo y de las Tablas Dinámicas y Gráficos Dinámicos asociados.

¿Puedo utilizar datos de bases de datos externas en mi modelo de datos de Excel?

Sí, puedes utilizar datos de bases de datos externas. Ve a Datos > Obtener Datos y selecciona la fuente apropiada. A continuación, sigue las instrucciones para conectarte a la base de datos, selecciona las tablas que necesites y añádelas al modelo de datos.

¿Cómo elimino una tabla del modelo de datos?

Para eliminar una tabla del modelo de datos, ve a Datos > Gestionar modelo de datos para abrir la ventana de Power Pivot. Selecciona la tabla que quieras eliminar, haz clic sobre ella con el botón derecho del ratón y selecciona Eliminar.

¿Cuáles son las ventajas de utilizar Power Query para modelar datos en Excel?

Las ventajas de utilizar Power Query para modelar datos en Excel incluyen transformaciones de datos, automatización, integración con varias fuentes, interfaces intuitivas y fáciles de usar, y escalabilidad.

Temas

Aprende Excel con DataCamp

Certificación disponible

Course

Preparación de datos en Excel

3 hr
23.8K
Comprender cómo preparar los datos de Excel mediante funciones lógicas, fórmulas anidadas, funciones de búsqueda y Tablas Dinámicas.
See DetailsRight Arrow
Start Course
Ver másRight Arrow
Relacionado

blog

Cómo exportar datos de Power BI a Excel

¿Te gustaría ver los datos que hay detrás de tus visualizaciones de Power BI? Aquí tienes nuestra guía para exportar datos de Power BI a Excel.
Eugenia Anello's photo

Eugenia Anello

6 min

tutorial

Tutorial de Modelado de datos en Power BI

Descubre qué es el modelado de datos en Power BI y cómo unas buenas prácticas de modelado de datos pueden llevar tus informes de Power BI al siguiente nivel.
Joleen Bothma's photo

Joleen Bothma

11 min

tutorial

Tutorial de Power BI para principiantes

Aprende los fundamentos de Power BI y a crear un informe básico con este tutorial paso a paso.
DataCamp Team's photo

DataCamp Team

16 min

tutorial

Cómo crear un cuadro de mando en Excel en 3 sencillos pasos

Aprende todo lo que necesitas saber sobre cómo crear un cuadro de mando en Excel, con consejos y ejemplos.
Joleen Bothma's photo

Joleen Bothma

12 min

tutorial

Tutorial sobre cómo crear tablas de fechas en Power BI

Aprende a crear tablas de fechas en Power BI con este tutorial visual paso a paso.
Kafaru Simileoluwa's photo

Kafaru Simileoluwa

12 min

tutorial

Tutorial de paneles de Power BI

Aprende a crear un panel en Power BI en este tutorial paso a paso, desde cargar tu conjunto de datos hasta compartir tu panel completado con tu equipo.
Kafaru Simileoluwa's photo

Kafaru Simileoluwa

18 min

See MoreSee More