Cómo crear relaciones entre varias tablas usando el modelo de datos en Excel
Excel es una herramienta poderosa para el análisis de datos y la posterior automatización cuando se manejan grandes conjuntos de datos. Podría pasar mucho tiempo analizando toneladas de datos usando BUSCARV, PARTIDA DE ÍNDICE, SUMIF, etc.
Gracias al modelo de datos de Excel, puede ahorrar un tiempo precioso a través de informes de datos automáticos. Descubra con qué facilidad puede asignar una relación entre dos tablas utilizando el modelo de datos y una ilustración de dicha relación en una tabla dinámica en la siguiente sección.
Los requisitos básicos
Necesitará Power Pivot y Power Query (Get & Transform) para realizar varias tareas mientras crea el modelo de datos de Excel . Así es como puede obtener estas funciones en su libro de Excel:
Cómo obtener Power Pivot
1. Excel 2010: deberá descargar el complemento Power Pivot de Microsoft y luego instalarlo para su programa Excel en su computadora.
2. Excel 2013: la edición Office Professional Plus de Excel 2013 incluye Power Pivot. Pero, debe activarlo antes del primer uso. Aquí es cómo:
- Haga clic en Archivo en la cinta de un libro de Excel.
- Luego haga clic en Opciones para abrir Opciones de Excel .
- Ahora, haga clic en Complementos .
- Seleccione Complementos COM haciendo clic en el menú desplegable del cuadro Administrar .
- Haga clic en Ir y luego seleccione la casilla de verificación de Microsoft Power Pivot para Excel .
3. Excel 2016 y posteriores: encontrará el menú Power Pivot en la cinta .
Cómo obtener Power Query (Obtener y transformar)
1. Excel 2010: puede descargar el complemento Power Query de Microsoft . Después de la instalación, Power Query aparecerá en la cinta .
2. Excel 2013: debe activar Power Query siguiendo los mismos pasos que acaba de hacer para que Power Pivot sea funcional en Excel 2013.
3. Excel 2016 y versiones posteriores: puede encontrar Power Query (Obtener y transformar) yendo a la pestaña Datos en la cinta de Excel.
Cree un modelo de datos importando datos al libro de Excel
Para este tutorial, puede obtener datos de muestra preformateados de Microsoft:
Descargar : datos de muestra de los estudiantes (solo datos) | Muestra de datos de estudiantes (modelo completo)
Puede importar una base de datos con varias tablas relacionadas de muchas fuentes como libros de trabajo de Excel, Microsoft Access, sitios web, SQL Server, etc. Luego, debe formatear el conjunto de datos para que Excel pueda utilizarlo. Estos son los pasos que puede probar:
1. En Excel 2016 y ediciones posteriores, haga clic en la pestaña Datos y seleccione Nueva consulta .
2. Encontrará varias formas de importar datos de fuentes externas o internas. Elija el que más le convenga.
3. Si usa la edición Excel 2013, haga clic en Power Query en la cinta y luego seleccione Obtener datos externos para elegir los datos para importar.
4. Verá el cuadro Navegador donde debe elegir qué tablas necesita importar. Haga clic en la casilla de verificación Seleccionar varios elementos para elegir varias tablas para importar.
5. Haga clic en Cargar para completar el proceso de importación.
6. Excel creará un modelo de datos para usted usando estas tablas. Puede ver los encabezados de las columnas de la tabla en los listados de Campos de tabla dinámica .
También puede utilizar funciones de Power Pivot como columnas calculadas, KPI, jerarquías, campos calculados y conjuntos de datos filtrados del modelo de datos de Excel. Para ello, deberá generar el modelo de datos a partir de una única tabla. Puedes probar estos pasos:
1. Formatee sus datos en un modelo tabular seleccionando todas las celdas que contienen datos y luego haga clic en Ctrl + T.
2. Ahora, seleccione toda la tabla y luego haga clic en la pestaña Power Pivot en la cinta .
3. En la sección Tablas , haga clic en Agregar al modelo de datos .
Excel creará relaciones de tabla entre datos relacionados del modelo de datos. Para esto, debe haber relaciones de clave primaria y externa dentro de las tablas importadas.
Excel utiliza la información de relación de la tabla importada como base para generar conexiones entre las tablas en un modelo de datos.
Construir relaciones entre las tablas en el modelo de datos
Ahora que tiene un modelo de datos en su libro de Excel, deberá definir las relaciones entre las tablas para crear informes significativos. Debe asignar un identificador de campo único o clave principal a cada tabla, como ID de semestre, número de clase, ID de estudiante, etc.
La función Vista de diagrama de Power Pivot le permitirá arrastrar y soltar esos campos para construir una relación. Siga estos pasos para crear enlaces de tabla en el modelo de datos de Excel:
1. En la cinta del libro de Excel, haga clic en el menú de Power Pivot .
2. Ahora, haga clic en Administrar en la sección Modelo de datos . Verá el editor de Power Pivot como se muestra a continuación:
3. Haga clic en el botón Vista de diagrama ubicado en la sección Ver de la pestaña Inicio de Power Pivot. Verá los encabezados de las columnas de la tabla agrupados según el nombre de la tabla.
4. Ahora podrá arrastrar y soltar el identificador de campo único de una tabla a otra. A continuación se muestra el esquema de relación entre las cuatro tablas del modelo de datos de Excel:
A continuación se describe el vínculo entre tablas:
- Estudiantes de mesa | Identificación del estudiante para clasificar las calificaciones | Identificación del Estudiante
- Semestres de mesa | ID del semestre para las calificaciones de la tabla | Semestre
- Clases de mesa | Número de clase a los grados de la mesa | Identificador de clase
5. Puede crear relaciones eligiendo un par de columnas de valor único. Si hay duplicados, verá el siguiente error:
6. Notarás Estrella (*) en un lado y Uno (1) en el otro en la Vista de diagrama de relaciones. Define que existe una relación de uno a varios entre las tablas.
7. En el editor de Power Pivot, haga clic en la pestaña Diseño y luego seleccione Administrar relaciones para saber qué campos hacen las conexiones.
Generar una tabla dinámica con el modelo de datos de Excel
Ahora puede crear una tabla dinámica o un gráfico dinámico para visualizar sus datos desde el modelo de datos de Excel. Un libro de Excel puede contener solo un modelo de datos, pero puede seguir actualizando las tablas.
Debido a que los datos cambian con el tiempo, puede seguir usando el mismo modelo y ahorrar tiempo al trabajar con el mismo conjunto de datos. Notará un mayor ahorro de tiempo cuando trabaje con datos en miles de filas y columnas. Para crear un informe basado en tabla dinámica, siga estos pasos:
1. En el editor de Power Pivot, haga clic en la pestaña Inicio .
2. En la cinta , haga clic en Tabla dinámica .
3. Elija cualquiera entre Nueva hoja de trabajo o Hoja de trabajo existente.
4. Seleccione Aceptar . Excel agregará una tabla dinámica que mostrará el panel Lista de campos a la derecha.
A continuación se muestra una vista holística de una tabla dinámica creada utilizando el modelo de datos de Excel para los datos de muestra de los estudiantes utilizados en este tutorial. También puede crear tablas dinámicas o gráficos profesionales a partir de macrodatos utilizando la herramienta Modelo de datos de Excel.
Transforme conjuntos de datos complejos en informes simples utilizando el modelo de datos de Excel
El modelo de datos de Excel utiliza los beneficios de crear relaciones entre tablas para producir tablas o gráficos dinámicos significativos para fines de informes de datos.
Puede actualizar continuamente el libro de trabajo existente y publicar informes sobre datos actualizados. No tiene que editar fórmulas ni invertir tiempo en desplazarse por miles de columnas y filas cada vez que se actualizan los datos de origen.