Buscar valores para empezar a hacer análisis de información es algo con lo que todo mundo tiene que lidiar, y cuando pasa lo normal es recurrir a la función VLOOKUP o XLOOKUP, pero más de una vez eso acaba en tener que usarlos cientos o hasta miles de veces. Si te ha tocado tener que conectar la Tabla 1 con la Tabla 2 y así para que todos los datos que necesitas para hacer ese reporte dinámico, entonces vas a amar la función de modelo de datos y relaciones en Excel.
En pocas palabras, esta función te ayuda a conectar un conjunto de datos con otro conjunto de datos para que puedas crear reportes dinámicos combinados.
Usando VLOOKUP y XLOOKUP (modo tradicional)
Por ejemplo, digamos que te toca revisar registros de ventas para tu empresa y te gustaría saber cuántas unidades estás vendiendo por categoría de producto y género de tus clientes. Tus datos de transacción se ven algo así:
En este ejemplo, el problema sería que solo tienes el ID de tu producto y el ID de tus clientes.
Si se resolviera esto utilizando la función VLOOKUP o XLOOKUP, esto es lo que pasaría:
Primero habría que buscar los datos de todos los clientes y productos para ponerlos en rangos separados. Después habría que hacer una función VLOOKUP/XLOOKUP para buscar la categoría del producto y otra función VLOOKUP/XLOOKUP para buscar el género de cada cliente. Luego habría que llenar todas las fórmulas para la lista de transacciones completa, y al final habría que hacer una tabla dinámica.
Como comparación, si hubiera 2000 transacciones tendrías que hacer 4000 funciones VLOOKUP para crear este único reporte. Y aun así, 2000 no es un número particularmente grande para muchos negocios.
Usando un modelo de datos y relaciones, los pasos se reducirían a:
- Crea relaciones entre tablas de Productos, Clientes y Ventas
- Crea una tabla dinámica.
Creando relaciones en Excel (paso a paso)
Lo primero será organizar tus datos como tablas (cosa que es vital hacer de todos modos, tal y como explicamos el otro día). Para hacer una tabla de forma rápida, tan solo selecciona las celdas a ocupar y ahora presiona CTRL + T. Si lo necesitas, puedes ir a la pestaña Diseño y asignar un nombre a tu tabla.
Ahora ve a la pestaña Datos y dale click al botón Relaciones
Selecciona Nuevo para crear una nueva relación. En la opción de Tabla selecciona la tabla que usarás de base en la relación y en la opción de Columna selecciona la columna de esa tabla con la que vas a trabajar. En las respectivas opciones de abajo selecciona la tabla y columna con la cual se va a relacionar la primera.
Este sería el paso básico, pero se puede ir más allá y usar las relaciones en reportes dinámico y como herramienta de análisis.
Para ello vamos a seleccionar cualquier tabla y nos iremos a la pestaña Insertar, y de ahí le daremos a Tabla dinámica. En el menú que saldrá asegúrate que la opción «Agregar estos datos al modelo de datos» que está hasta abajo esté marcada.
En la lista de campos de tu tabla dinámica marca «Todos» en lugar de «Activo» para ver todos los nombres de las tablas.
Ahora selecciona los campos de las tablas que necesites para crear un reporte dinámico combinado o una tabla dinámica.
EJEMPLO
Volviendo al primer caso donde se usaban funciones VLOOKUP/XLOOKUP había que hacer un reporte de categoría de productos vendidos y género de los clientes. Todo el proceso (sin importar el número de datos) sería así si se usan relaciones:
- Añadir categoría a las etiquetas de fila.
- Añadir género a las etiquetas de columna.
- Añadir cantidad a los valores
- Listo.
Beneficios de un modelo de datos basado en tablas dinámicas.
En cuanto tengas un modelo de datos en tu hoja de cálculo tendrás acceso a una variedad de beneficios, como:
- Conteos distintos
Este simple pero a veces dificil conteo para calcular un número es fácil de obtener una vez que tienes un modelo de datos dinámicos. Tan solo ve a las opciones del valor del campo y cambia el tipo de resúmen a «Conteo distinto»
- DAX y medidas
Una vez que tienes un modelo de datos puedes usar las funciones completas de Power Pivot en tus libros de trabajo. Puedes crear medidas (usando lenguaje DAX) y calcular cosas que regularmente serían imposibles usando solo Excel.
- Tablas dinámicas de datos en otras bases de datos o documentos Puedes combinar modelos de datos con las habilidades de Power Query para crear tablas dinámicas de datos en otros lugares; como crear un reporte dinámico desde datos de ventas en SAP con datos de clientes en un sistema ERP.
- Tablas dinámicas de más de un millón de filas de datos
También te puedes conectar a bases de datos masivas y crear tablas dinámicas desde ellas si estás usando modelos de datos.
- Convertir tablas dinámicas a fórmulas
Una vez que tienes un modelo de datos basado en una tabla dinámica puedes convertirlo en una serie de fórmulas. Para acceder a esta función solo ve a la pestaña Análisis. Esto reemplazará tu dinámica con un montón de fórmulas CUBE.
Cosas a tener en cuenta al usar relaciones en Excel.
- Ten el mismo tipo de datos en ambas columnas
Las columnas que conectes en ambas tablas deben tener el mismo tipo de datos (como que ambos tengan números, fechas, texto…)
- Solo puedes usar relaciones «uno a uno» o «uno a varios»
Excel 2013 solo tiene soporte para estos dos tipos de relaciones. Esto significa que una de las tablas no debe tener valores duplicados en la columna a la cual se les está relacionando (en el caso de arriba, por ejemplo, la tabla de productos no debe tener IDs de productos duplicados).
- Puedes agregar slicers
Se puede crear un filtro a partir de cualquier campo en estas tablas dinámicas. Siguiendo el caso de arriba, por ejemplo, se podría seguir dinamizando el reporte en profesión de los clientes o el SKU de cada producto.
Desventajas de un modelo de datos
- Compatibilidad
Los modelos de datos y relaciones solo están disponibles en las versiones de Excel 2013 en adelante.
- No es posible agrupar datos
En las tablas dinámicas regulares se pueden agrupar campos numéricos, de texto o de datos, pero con las tablas dinámicas de modelos de datos esto ya no es posible. Para ello debes crear otra tabla con el mapeo grupal y usarla como una relación.
Dale nueva vida a las tablas dinámicas con esta nueva función de Office y sorprende a todos tus colegas con el poder de las relaciones.
Cualquier duda nos leemos abajo.
