Course
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:
- Formatea tus datos como tablas.
- Crea relaciones entre las tablas.
- Utiliza Power Query para cargar y transformar datos.
- Utiliza Power Pivot para el modelado avanzado de datos.
- 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:
- 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.
- Buscar actualizaciones: Asegúrate de que Excel está actualizado yendo a Archivo > Cuenta > Opciones de actualización.
- 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.
- Desactivar complementos: Ve a Archivo > Opciones > Complementos y desactiva todos los complementos. Reinicia Excel e intenta abrir de nuevo el libro.
- 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:
- Comprueba los tipos de datos: Asegúrate de que los tipos de datos de las columnas utilizadas para las relaciones son coherentes.
- Validar relaciones: Comprueba que todas las relaciones están correctamente definidas.
- Revisa los cálculos: Comprueba si hay errores en las columnas y medidas calculadas.
- Actualizar datos: Asegúrate de que todas las conexiones de datos están actualizadas.
- 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.
Aprende Excel con DataCamp
Course
Introducción a Power Query en Excel
Course
Power Pivot en Excel
blog
Cómo exportar datos de Power BI a Excel
Eugenia Anello
6 min
tutorial
Tutorial de Modelado de datos en Power BI
tutorial
Tutorial de Power BI para principiantes
DataCamp Team
16 min
tutorial
Cómo crear un cuadro de mando en Excel en 3 sencillos pasos
tutorial
Tutorial sobre cómo crear tablas de fechas en Power BI
tutorial