lunes, julio 15, 2013

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

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


9 comentarios:

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

    ResponderBorrar
  2. 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

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

    ResponderBorrar
  4. 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 !!!

    ResponderBorrar
  5. 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.

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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.

    ResponderBorrar
  8. Buenas tardes,

    cuál es la diferencia entre seguir el camino de powerpivot o el de datos-relaciones para unificar tables de datos en una sola tabla dinámica.

    Gracias.

    Saludos

    ResponderBorrar
  9. Con PowerPivot tenemos más opciones, como crear columnas calculadas.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.