Modelo de datos en Excel

¿Qué es el modelo de datos en Excel?

El modelo de datos en Excel es un tipo de tabla de datos donde dos o más de dos tablas están en relación entre sí a través de una serie de datos común o más, en el modelo de datos, las tablas y los datos de varias otras hojas o fuentes se unen para formar una única tabla que puede tener acceso a los datos de todas las tablas.

Explicación

  • Permite integrar datos de varias tablas creando relaciones basadas en una columna común.
  • Los modelos de datos se utilizan de forma transparente, proporcionando datos tabulares que se pueden utilizar en tablas dinámicas en Excel y gráficos dinámicos en Excel. Integra las tablas, lo que permite un análisis exhaustivo mediante tablas dinámicas, Power Pivot y Power View en Excel.
  • El modelo de datos permite cargar datos en la memoria de Excel.
  • Se guarda en la memoria donde no podemos verlo directamente. Luego, se puede indicar a Excel que relacione los datos entre sí mediante una columna común. La parte 'Modelo' del modelo de datos se refiere a cómo se relacionan todas las tablas entre sí.
  • El modelo de datos puede acceder a toda la información que necesita incluso cuando la información está en varias tablas. Una vez creado el modelo de datos, Excel tiene los datos disponibles en su memoria. Con los datos en su memoria, se puede acceder a los datos de muchas formas.

Ejemplos

Puede descargar esta plantilla de Excel de modelo de datos aquí - Plantilla de Excel de modelo de datos

Ejemplo 1

Si tenemos tres conjuntos de datos relacionados con el vendedor: primero que contiene información de ingresos, un segundo que contiene los ingresos del vendedor y un tercero que contiene los gastos del vendedor.

Para conectar estos tres conjuntos de datos y establecer una relación con ellos, hacemos un Modelo de datos con los siguientes pasos:

  • Convierta los conjuntos de datos en objetos de tabla:

No podemos crear una relación con conjuntos de datos ordinarios. El modelo de datos solo funciona con objetos de tablas de Excel. Para hacer esto:

  • Paso 1: haga clic en cualquier lugar dentro del conjunto de datos, luego haga clic en la pestaña 'Insertar' y luego haga clic en 'Tabla' en el grupo 'Tablas'.

  • Paso 2: marque o desmarque la opción: 'Mi tabla tiene encabezados' y haga clic en Aceptar.

  • Paso 3: con la nueva tabla seleccionada, ingrese el nombre de la tabla en el 'Nombre de la tabla' en el grupo 'Herramientas'.

  • Paso 4: ahora podemos ver que el primer conjunto de datos se convierte en el objeto 'Tabla'. Al repetir estos pasos para los otros dos conjuntos de datos, vemos que también se convierten en objetos de 'Tabla' como se muestra a continuación:

Agregar los objetos 'Tabla' al modelo de datos: a través de conexiones o relaciones.

A través de conexiones

  • Seleccione una tabla y haga clic en la pestaña 'Datos' y luego haga clic en 'Conexiones'.

  • En el cuadro de diálogo resultante, hay un icono de 'Agregar'. Expanda el menú desplegable de 'Agregar' y haga clic en 'Agregar al modelo de datos'.

  • Haga clic en 'Tablas' en el cuadro de diálogo resultante y luego seleccione una de las tablas y haga clic en 'Abrir'.

Al hacer esto, se creará un modelo de datos de libro con una tabla y aparecerá un cuadro de diálogo de la siguiente manera:

Entonces, si repetimos estos pasos también para las otras dos tablas, el modelo de datos ahora contendrá las tres tablas.

Ahora podemos ver que las tres tablas aparecen en Conexiones del libro de trabajo.

Vía Relaciones

Crear la relación: una vez que ambos conjuntos de datos sean objetos de tabla, podemos crear una relación entre ellos. Para hacer esto:

  • Haga clic en la pestaña 'Datos' y luego haga clic en 'Relaciones'.

  • Veremos un cuadro de diálogo vacío ya que no hay conexiones actuales.

  • Haga clic en 'Nuevo' y aparecerá otro cuadro de diálogo.

  • Expanda los menús desplegables 'Tabla' y 'Tabla relacionada': aparece un cuadro de diálogo 'Crear relación' para elegir las tablas y columnas que se utilizarán para una relación. En la expansión de 'Tablas', seleccione el conjunto de datos que deseamos analizar de alguna manera, y en 'Tabla relacionada', seleccione el conjunto de datos que tiene valores de búsqueda.
  • La tabla de búsqueda en Excel es la tabla más pequeña en el caso de una a muchas relaciones y no contiene valores repetidos en la columna común. En la expansión de 'Columna (extranjera)', seleccione la columna común en la tabla principal, en 'Columna relacionada (primaria)', seleccione la columna común en la tabla relacionada.

  • Con estas cuatro configuraciones seleccionadas, haga clic en 'Aceptar'. Aparece un cuadro de diálogo de la siguiente manera al hacer clic en 'Aceptar'.

Si repetimos estos pasos para relacionar otras dos tablas: Tabla de ingresos con tabla de Gastos, también se relacionan en el Modelo de datos de la siguiente manera:

Excel ahora crea la relación entre bastidores combinando datos en el modelo de datos en función de una columna común: ID de vendedor (en este caso).

Ejemplo # 2

Ahora, digamos en el ejemplo anterior que deseamos crear una tabla dinámica que evalúe o analice los objetos de la tabla:

  • Haga clic en 'Insertar' -> 'Tabla dinámica'.

  • En el cuadro de diálogo resultante, haga clic en la opción que dice: 'Usar una fuente de datos externa' y luego haga clic en 'Elegir conexión'.

  • Haga clic en 'Tablas' en el cuadro de diálogo resultante y seleccione el modelo de datos del libro de trabajo que contiene tres tablas y haga clic en 'Abrir'.

  • Seleccione la opción 'Nueva hoja de trabajo' en la ubicación y haga clic en 'Aceptar'.

  • El panel Campos de tabla dinámica mostrará los objetos de la tabla.

  • Ahora los cambios en la tabla dinámica se pueden realizar en consecuencia para analizar los objetos de la tabla según sea necesario.

Por ejemplo, en este caso, si deseamos encontrar los ingresos totales o los ingresos de un vendedor en particular, se crea una tabla dinámica de la siguiente manera:

Esto es de gran ayuda en el caso de un modelo / tabla que contiene un gran número de observaciones.

Entonces, podemos ver que la tabla dinámica utiliza instantáneamente el modelo de datos (seleccionándolo al elegir la conexión) en la memoria de Excel para mostrar las relaciones entre las tablas.

Cosas para recordar

  • Usando el modelo de datos, podemos analizar datos de varias tablas a la vez.
  • Al crear relaciones con el modelo de datos, superamos la necesidad de utilizar VLOOKUP, SUMIF, la función INDICE y las fórmulas COINCIDIR, ya que no necesitamos obtener todas las columnas en una sola tabla.
  • Cuando los conjuntos de datos se importan en Excel desde fuentes externas, los modelos se crean implícitamente.
  • Las relaciones de tabla se pueden crear automáticamente si importamos tablas relacionadas que tengan relaciones de clave primaria y externa.
  • Al crear relaciones, las columnas que estamos conectando en tablas deben tener el mismo tipo de datos.
  • Con las tablas dinámicas creadas con el modelo de datos, también podemos agregar segmentaciones y dividir las tablas dinámicas en cualquier campo que queramos.
  • La ventaja del modelo de datos sobre las funciones LOOKUP () es que requiere sustancialmente menos memoria.
  • Excel 2013 admite solo una a una o una a muchas relaciones, es decir, una de las tablas no debe tener valores duplicados en la columna a la que estamos vinculando.