Modelo de datos de Excel (tabla de contenido)

  • Introducción al modelo de datos en Excel
  • ¿Cómo crear un modelo de datos en Excel?

Introducción al modelo de datos en Excel

La característica del modelo de datos de Excel permite la construcción fácil de relaciones entre informes fáciles y sus conjuntos de datos de fondo. Hace que el análisis de datos sea mucho más fácil. Permite la integración de datos de una gran cantidad de tablas distribuidas en varias hojas de trabajo simplemente construyendo relaciones entre columnas coincidentes. Funciona completamente detrás de escena y simplifica enormemente las funciones de informes como la tabla dinámica, etc.

En nuestro artículo, intentaremos mostrar cómo crear una tabla dinámica a partir de dos tablas empleando la función Modelo de datos, estableciendo así una relación entre dos objetos de tabla y creando así una tabla dinámica.

¿Cómo crear un modelo de datos en Excel?

Comprendamos cómo crear el modelo de datos en Excel con algunos ejemplos.

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

Ejemplo 1

  • Tenemos una lista de productos y tenemos un código de estantería para cada producto. Necesitamos una tabla donde tengamos la descripción de la estantería junto con los códigos de estantería. Entonces, ¿cómo incorporamos las descripciones de estanterías contra cada código de estantería? Quizás muchos de nosotros recurriríamos a usar VLOOKUP aquí, pero eliminaremos por completo la necesidad de usar VLOOKUP aquí usando Excel Data Model.

  • La tabla de la izquierda es la tabla de datos y la tabla de la derecha es la tabla de búsqueda. Como podemos ver en los datos, es posible crear una relación basada en columnas comunes.

  • Ahora, el modelo de datos solo es compatible con objetos de tabla. Por lo tanto, a veces puede ser necesario convertir conjuntos de datos en objetos de tabla. Para hacerlo, siga los pasos a continuación.
  1. Haga clic izquierdo en cualquier parte del conjunto de datos.
  2. Haga clic en la pestaña Insertar y navegue hasta Tabla en el grupo Tablas o simplemente presione Ctrl + T.
  3. Desmarque o marque Mi tabla tiene la opción Encabezado. En nuestro ejemplo, de hecho tiene un encabezado. Haga clic en Aceptar.
  4. Si bien todavía estamos enfocados en la nueva tabla, debemos proporcionar un nombre que sea significativo en el cuadro Nombre (a la izquierda de la barra de fórmulas).

En nuestro ejemplo, hemos nombrado la tabla Personal.

  • Ahora tenemos que hacer el mismo proceso para la tabla de búsqueda también y nombrarlo Shelf Code.

Creando una relación

En primer lugar, iremos a la pestaña Datos y luego seleccionaremos Relaciones en el subgrupo Herramientas de datos. Después de hacer clic en la opción Relaciones, al principio ya que no hay relación, por lo tanto, no tendremos nada.

Primero haremos clic en Nuevo para crear una relación. Ahora necesitaremos proporcionar los nombres de la tabla primaria y de búsqueda de la lista desplegable y luego también mencionar la columna que es común entre las dos tablas para que podamos establecer la relación entre las dos tablas, desde la lista desplegable de columnas

  • Ahora, la tabla primaria es la tabla que tiene los datos. Es la tabla de datos primaria - Tabla5. Por otro lado, la tabla relacionada es la tabla que tiene los datos de búsqueda; es nuestra tabla de búsqueda ShelfCodesTable. La tabla primaria es la que se analiza en función de la tabla de búsqueda que contiene datos de búsqueda que al final harán que los datos informados sean más significativos.

  • Entonces, la columna común entre las dos tablas es la columna Código de estante. Esto es lo que hemos usado para establecer la relación entre las dos tablas. Al llegar a las columnas, la Columna (extranjera) es la que se refiere a la tabla de datos donde puede haber valores duplicados. Por otro lado, la columna relacionada (primaria) se refiere a la columna en la tabla de búsqueda donde tenemos valores únicos. Simplemente estamos configurando el campo para buscar valores de la tabla de búsqueda en la tabla de datos.
  • Una vez que configuramos esto, Excel crearía una relación entre los dos detrás de escena. Integra los datos y crea un modelo de datos basado en la columna común. Esto no solo es ligero en cuanto a los requisitos de memoria, sino también mucho más rápido que usar VLOOKUP en libros grandes. Después de definir el modelo de datos, Excel trataría estos objetos como tablas del modelo de datos en lugar de una tabla de hoja de trabajo.
  • Ahora para ver qué ha estado haciendo Excel, podemos hacer clic en Administrar modelos de datos en Datos -> Herramientas de datos.

  • También podemos obtener la representación esquemática del modelo de datos cambiando la vista. Haremos clic en la opción Ver. Esto abrirá las opciones de vista. Luego seleccionaremos la Vista de diagrama. Luego veremos la representación esquemática, que muestra las dos tablas y la relación entre ellas, es decir, la columna común - Código de estante.

  • El diagrama anterior muestra una relación de uno a muchos entre los valores únicos de la tabla de búsqueda y la tabla de datos con valores duplicados.
  • Ahora tendremos que crear una tabla dinámica. Para hacer eso, iremos a la pestaña Insertar y luego haremos clic en la opción Tabla dinámica.

En el cuadro de diálogo Crear tabla dinámica, seleccione la fuente como “Usar el modelo de datos de este libro de trabajo”.

  • Esto creará la tabla Pivot y podemos ver que ambas tablas fuente están disponibles en la sección fuente.

  • Ahora crearemos una tabla dinámica que muestre el recuento de cada persona que tiene elementos archivados.

  • Seleccionaremos Personal en la sección Filas de la Tabla 5 (tabla de datos), seguido de Descripción (tabla de búsqueda).

  • Ahora arrastraremos el Código de estante de la Tabla 5 a la sección Valores.

  • Ahora agregaremos Meses de la Tabla 5 a la sección Filas.

  • O podríamos agregar los meses como filtro y agregarlo a la sección Filtros.

Ejemplo # 2

  • Ahora tenemos al Sr. Basu dirigiendo una fábrica llamada Basu Corporation. Basu está tratando de estimar los ingresos para 2019 con base en los datos de 2018.
  • Tenemos una tabla donde tenemos los ingresos para 2018 y los ingresos posteriores en diferentes niveles incrementales.

  • Entonces, tenemos los ingresos para 2018: $ 1.5 M y el crecimiento mínimo esperado para el año siguiente es del 12%. El Sr. Basu quiere una tabla que muestre los ingresos en diferentes niveles incrementales.
  • Crearemos la siguiente tabla para las proyecciones en diferentes niveles incrementales para 2019.

  • Ahora le daremos a la primera fila de Ingresos una referencia a los ingresos mínimos estimados para 2019, es decir, $ 1.68 M.

  • Después de usar la fórmula, la respuesta se muestra a continuación.

  • Ahora seleccionaremos toda la tabla, es decir, D2: E12 y luego iremos a Datos -> Pronóstico -> Análisis hipotético -> Tabla de datos.

  • Esto abrirá el cuadro de diálogo Tabla de datos. Aquí ingresaremos el porcentaje de incremento mínimo de la celda B4 en la celda Entrada de columna. La razón de esto es que nuestros porcentajes de crecimiento estimados proyectados en la tabla están organizados en forma de columnas.

  • Una vez que hagamos clic en Aceptar, el análisis Qué pasa si llenará automáticamente la tabla con los ingresos proyectados en los diferentes porcentajes incrementales.

Ejemplo # 3

  • Ahora supongamos que tenemos el mismo escenario que el anterior, excepto que ahora también tenemos que considerar otro eje. Supongamos que además de mostrar los ingresos proyectados en 2019 con base en los datos de 2018 y la tasa de crecimiento mínima esperada, ahora también tenemos la tasa de descuento estimada.

  • Primero, tendremos una tabla que se muestra a continuación.

  • Ahora daremos referencia al ingreso mínimo proyectado para 2019, es decir, la celda B5 a la celda D8.

  • Ahora seleccionaremos la tabla completa, es decir, D8: J18 y luego iremos a Datos -> Pronóstico -> Análisis hipotético -> Tabla de datos.

  • Esto abrirá el cuadro de diálogo Tabla de datos. Aquí ingresaremos el porcentaje de incremento mínimo de la celda B3 en la celda Entrada de columna. La razón de esto es que nuestros porcentajes de crecimiento estimados proyectados en la tabla están organizados en forma de columnas. Ahora también ingresaremos el porcentaje de descuento mínimo de la celda B4 en la celda Entrada de fila. La razón de esto es que nuestros porcentajes de descuento proyectados en la tabla están organizados en forma fila.

  • Haga clic en Aceptar. Esto hará que el análisis What-If llene automáticamente la tabla con los ingresos proyectados en los diferentes porcentajes incrementales según los porcentajes de descuento.

Cosas para recordar sobre el modelo de datos en Excel

  • Al calcular con éxito los valores de la tabla de datos, un simple Deshacer, es decir, Ctrl + Z no funcionará. Sin embargo, es posible eliminar manualmente los valores de la tabla.
  • No es posible eliminar una sola celda de la tabla. Se describe como una matriz internamente en Excel, por lo tanto, tendremos que eliminar todos los valores.
  • Necesitamos seleccionar correctamente la celda de entrada de fila y la celda de entrada de columna.
  • La tabla de datos, a diferencia de la tabla dinámica, no necesita actualizarse cada vez.
  • Con el modelo de datos en Excel, no solo podemos mejorar el rendimiento, sino que también facilitamos los requisitos de memoria en hojas de trabajo grandes.
  • Los modelos de datos también hacen que nuestro análisis sea mucho más simple en comparación con el uso de una serie de fórmulas complicadas en todo el libro de trabajo.

Artículos recomendados

Esta es una guía para el modelo de datos en Excel. Aquí discutimos cómo crear un modelo de datos en Excel junto con ejemplos prácticos y una plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

  1. Barra de fórmulas en Excel
  2. Imprimir cuadrículas en Excel
  3. Mirar ventana en Excel
  4. Excel SUMIFS con fechas

Categoría:

Consejos de Excel