Como (casi) deje de usar VLOOKUP (usando Datos-Relaciones)

lunes, julio 15, 2013

Excel 2013 introdujo muchas innovaciones que, más aún que en las versiones anteriores, lo convierten en la herramienta ideal para los analistas de datos. Algunas, como PowerPivot, ya se han ganado su fama. Otras han pasado desapercibidas, como la nueva funcionalidad Datos-Relaciones


Una de las situaciones más corrientes cuando analizamos datos es la necesidad de unificar en una única tabla datos que se encuentran en dos o más tablas. Esto es necesario, en particular, cuando queremos analizar los datos con tablas dinámicas.

Veamos este ejemplo: en una hoja tenemos una tabla con los datos de ventas


y en otra una tabla con las categorías de los productos (madera, electricidad, etc.)


Para crear reportes con tablas dinámicas lo que haríamos con las versiones anteriores de Excel es usar VLOOKUP para combinar las categorías en la tabla de ventas. De esta manera podemos crear un informa de ventas por categorías y períodos, categorías y clientes, etc.

Con la nueva funcionalidad podemos combinar los datos de ambas tablas como en una consulta (query) en Access, sin necesidad de cargar nuestro modelo con miles de fórmulas VLOOKUP.

El primer paso es convertir las listas de datos en Tablas (Insertar-Tabla). Para facilitar el trabajo posterior cambiamos el nombre por defecto (Tabla1) por algo más significativo (tblVentas)


Hacemos lo mismo con la tabla de las categorías (tblCategorias)

El segundo paso es crear las relaciones entre las tablas. En la cinta activamos Datos-Herramientas de Datos-Relaciones y apretamos la opción Nuevo. En el formulario "Crear relación" definimos el campo (columna) común a ambas tabla (en nuestro ejemplo Producto)


Apretamos "Aceptar" y "Cerrar".

Para crear la tabla dinámica con los datos combinados seleccionamos alguna de las tablas y creamos la tabla dinámica (Insertar-Tabla dinámica)


En el formulario que se abre marcamos la opción "Agregar estos datos al modelo de datos"

Excel abre una nueva hoja con la plantilla de la tabla dinámica



En el área de definiciones "Campo de tabla…" activamos la opción "Todos". Esto nos permite ver todos los campos de ambas tablas y usarlas en nuestro informe dinámico.
Ahora podemos crear el informe Ventas por categorías


7 comments:

Anónimo,  03 agosto, 2013 19:40  

Simplemente Excelente!. Gracias Maestro, Un saludo desde Colombia.

Leonardo,  30 septiembre, 2013 19:01  

Hola estimado, tengo una duda, tengo un libro que tiene dos hojas, cada hoja se actualiza de una base de datos distintas y quisiera mantenerlas separadas, yo quisiera hacer una tabla dinamica que sume campos en comun de ambas hojas, como pudiera hacer esto? por ejemplo codigo de producto, mes, año. La tabla dinamica seria consolidar toda la informacion

Jorge Dunkelman 01 octubre, 2013 06:47  

¿Qué versión de Excel estás usando?

Anónimo,  07 noviembre, 2013 23:18  

Muchas gracias por tan buen trabajo, cada vez que voy hacer un análisis y veo que me hace falta una formula o quiero una idea recurro a tu blog, Gracias !!!

Anónimo,  19 noviembre, 2013 23:43  

Estoy probando con esto de las relaciones y me encuentro con un problema, en cuanto una tabla tiene relación con otra e intento crear una tabla dinámica utilizando la opción del modelo de datos para poder usar ambas, si pongo un campo de tipo fecha en las columnas, no me deja agrupar, para poder ver los datos por meses, trimestres, años, etc. Sin embargo si la tabla dinámica la construyo independiente, es decir, sin combinar con otra tabla, si me permite agrupar. Sabes a que puede deberse esto?
Un saludo y muchas gracias por tu magnifico trabajo.

Jorge Dunkelman 20 noviembre, 2013 07:02  

Por lo general se debe a que alguno de los elementos del campo no es fecha (por ejemplo, una celda en blanco). Tendrías que comprobar si en todas las tablas el campo contiene sólo fechas.

Anónimo,  23 noviembre, 2013 17:32  

Mucha gracias, tengo que trabajar "guardias" sabatinas y la mejor inversión de tiempo que encuentro es leer y practicar tu blog

Saludos desde México D.F.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP