Análisis de datos avanzado: modelo de datos
El modelo de datos está disponible en Excel 2013 y versiones posteriores. En Excel, puede utilizar el modelo de datos para integrar datos de varias tablas en el libro de trabajo actual y / o de los datos importados y / o de las fuentes de datos conectadas al libro de trabajo a través de conexiones de datos.
Con un modelo de datos, puede crear relaciones entre las tablas. El modelo de datos se usa de forma transparente en los informes de tabla dinámica, gráfico dinámico, PowerPivot y Power View.
Crear modelo de datos mientras se importan datos
Cuando importa datos de bases de datos relacionales como la base de datos de Microsoft Access que contienen varias tablas relacionadas, el modelo de datos se crea automáticamente si importa más de una tabla a la vez.
Opcionalmente, puede agregar tablas al modelo de datos cuando importa datos de las siguientes fuentes de datos:
- Bases de datos relacionales, una tabla a la vez
- Archivos de texto
- Libros de Excel
Por ejemplo, mientras está importando datos de un libro de Excel, puede observar la opción Add this data to the Data Model, con una casilla de verificación habilitada.
Si desea agregar los datos que está importando al modelo de datos, marque la casilla.
Crear modelo de datos a partir de tablas de Excel
Puede crear el modelo de datos a partir de tablas de Excel mediante los comandos de PowerPivot. Aprenderá PowerPivot en detalle en capítulos posteriores.
Todos los comandos del modelo de datos están disponibles en la pestaña PowerPivot de la cinta. Puede agregar tablas de Excel al modelo de datos con estos comandos.
Considere el siguiente libro de trabajo de datos de ventas, en el que tiene una hoja de trabajo de Catálogo de productos que contiene Producto, ID de producto y Precio. Tiene cuatro hojas de trabajo para las ventas en 4 regiones: Este, Norte, Sur y Oeste.
Cada una de estas cuatro hojas de trabajo contiene el número de unidades vendidas y el monto total de cada uno de los productos en cada mes. Debe calcular el monto total de cada uno de los productos en cada región y el monto total de ventas en cada región.
Los siguientes pasos le permiten llegar a los resultados deseados:
- Empiece por crear el modelo de datos.
- Haga clic en la hoja de trabajo del Catálogo de productos.
- Haga clic en la pestaña POWERPIVOT en la cinta.
- Haga clic en Agregar al modelo de datos. Aparece el cuadro de diálogo Crear tabla.
- Seleccione el rango de la mesa.
- Marque la casilla Mi tabla tiene encabezados. Haga clic en Aceptar.
Aparece una nueva ventana, PowerPivot para Excel, <su nombre de archivo de Excel>.
El siguiente mensaje aparece en el centro de la ventana en blanco:
La tabla Product Backlog que agregó al modelo de datos aparece como una hoja en la ventana de PowerPivot. Cada fila de la tabla es un registro y puede avanzar y retroceder entre los registros usando los botones de flecha izquierda y derecha en la parte inferior de la ventana.
- Haga clic en la pestaña Tabla vinculada en la ventana de PowerPivot.
- Haga clic en Ir a la tabla de Excel.
Aparece la ventana de datos de Excel.
- Haga clic en la pestaña de la hoja de trabajo - Este.
- Haga clic en la pestaña POWERPIVOT en la cinta.
- Haga clic en Agregar al modelo de datos.
Aparece otra hoja en la ventana de PowerPivot que muestra la tabla Este.
Repita para las hojas de trabajo: norte, sur y oeste. En total, ha agregado cinco tablas al modelo de datos. Su ventana de PowerPivot se ve a continuación:
Crear relaciones entre tablas
Si desea realizar cálculos en las tablas, primero debe definir las relaciones entre ellas.
Haga clic en la pestaña Inicio en la cinta de opciones de la ventana de PowerPivot. Como puede observar, las tablas se muestran en la Vista de datos.
Haga clic en Vista de diagrama.
Las tablas aparecen en la Vista de diagrama. Como puede observar, algunas de las tablas pueden estar fuera del área de visualización y todos los campos de las tablas pueden no ser visibles.
- Cambie el tamaño de cada tabla para mostrar todos los campos de esa tabla.
- Arrastre y organice las tablas para que se muestren todas.
- En la tabla Este, haga clic en ID de producto.
- Haga clic en la pestaña Diseño en la cinta.
- Haga clic en Crear relación. Aparece el cuadro de diálogo Crear relación.
En el cuadro debajo de Tabla, se muestra Este. En el cuadro debajo de Columna, se muestra ID de producto.
- En el cuadro debajo de Tabla de búsqueda relacionada, seleccione Catálogo de productos.
- El ID de producto aparece en el cuadro debajo de la columna de búsqueda relacionada.
- Haga clic en el botón Crear.
Aparece la línea que representa la relación entre las tablas Este y Product Backlog.
- Repita los mismos pasos para las tablas: norte, sur y oeste. Aparecen líneas de relación.
Resumiendo los datos en las tablas en el modelo de datos
Ahora, ya está todo listo para resumir los datos de ventas de cada uno de los productos en cada región en solo unos pocos pasos.
- Haga clic en la pestaña Inicio.
- Haga clic en tabla dinámica.
- Seleccione tabla dinámica en la lista desplegable.
El cuadro de diálogo Crear tabla dinámica aparece en la ventana de tablas de Excel. Seleccione Nueva hoja de trabajo.
En una nueva hoja de trabajo, aparece una tabla dinámica vacía. Como puede observar, la Lista de campos contiene todas las tablas del Modelo de datos con todos los campos mostrados.
Seleccione ID de producto de la Tabla 1 (Catálogo de productos).
Seleccione Importe total de las otras cuatro tablas.
Para cada uno de los campos en ∑ Valores, cambie el Nombre personalizado en Configuración de campo de valor para mostrar los nombres de región como etiquetas de columna.
La suma de la Cantidad total será reemplazada por la etiqueta que proporciones. La tabla dinámica con valores resumidos de todas las tablas de datos le muestra los resultados requeridos.
Agregar datos al modelo de datos
Puede agregar una nueva tabla de datos al modelo de datos o nuevas filas de datos a las tablas existentes en el modelo de datos.
Agregue una nueva tabla de datos al modelo de datos con los siguientes pasos.
Haga clic en la pestaña DATOS en la cinta.
Haga clic en Conexiones existentes en el grupo Obtener datos externos. Aparece el cuadro de diálogo Conexiones existentes.
Haga clic en la pestaña Tablas. Se mostrarán los nombres de todas las tablas del libro.
Haga clic en el nombre de la tabla que desea agregar al modelo de datos.
Haga clic en el botón Abrir. Aparece el cuadro de diálogo Importar datos.
Como sabe, al importar la tabla de datos, se agrega automáticamente al modelo de datos. La tabla recién agregada aparece en la ventana de PowerPivot.
Agregue nuevas filas de datos a las tablas existentes en el modelo de datos.
Actualice la conexión de datos. Las nuevas filas de datos de la fuente de datos se agregan al modelo de datos.