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

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.

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.

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. 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:

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. Imagen del autor 

Aparecerá una nueva ventana.

Ventana 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. 

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.

as 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.

Cuadro 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.

Campos 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. 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.

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.

Seleccionar 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. Imagen del autor

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

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 la Tabla. Imagen del autor

Ahora esta tabla está configurada como CustomerPQ.

Ventana 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.

Dos 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.

Abrir 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...  

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:

ExcelVentana Importar datos. Imagen del autor 

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

as 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.

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:

ExcelVentana Importar datos. Imagen del autor 

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

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.

de ExcelHerramientas de Datos. Imagen del autor

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

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.

Panel de de ExcelPanel de vista. Imagen del autor

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

Vista 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.

Pestañ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:

Crear 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.

Ventana 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.

Habilitar 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.

de ExcelPanel PivotTable. Imagen del autor

Aparecerá una ventana emergente como ésta:

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.

Hoja Hoja PivotTable. Imagen del autor 

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

Etiquetas 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:

Seleccionar 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:

Salida 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.

Registros 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.

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 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. 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. 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. Imagen del autor

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

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

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

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.

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. 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 la tabla dinámica. Imagen del autor

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

Opciones de Insertar Gráfico. Imagen del autor

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

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.