Como dejé (casi) de usar BUSCARV, también con Excel 2010

En la nota Como casi dejé usar VLOOKUP mostré como crear reportes dinámicos a partir de dos o más matrices de datos usando la nueva funcionalidad de Excel 2013 Relaciones (Datos-Herramientas de Datos-Relaciones).

Esta funcionalidad no existe en forma nativa en Excel 2010, pero podemos agregarla instalando el complemento PowerPivot (en la nota del enlace se señalan todos los requisitos para la instalación).
No me voy a extender aquí sobre las bondades del PowerPivot (lo que muestro a continuación es la puntita de la punta del iceberg), pero a todos mis lectores que usen Excel 2010 o Excel 2013 les recomiendo ver este tutorial (incluye la descarga del archivo de datos para las prácticas).

Volviendo a nuestro tema, veamos cómo usamos el PowerPivot para crear un reporte dinámico con más de una fuente de datos.

Una vez instalado el complemento, veremos una nueva pestaña en la cinta

Como en el ejemplo de la nota anterior, nuestro cuaderno incluye dos hojas:

Ventas: las ventas de los productos (fecha, cliente, producto y suma)

• Producto: la categoría de cada producto (producto, categoría).
• Nuestro objetivo es crear un reporte de ventas por categoría.

Seleccionamos una celda de una de las tablas del cuaderno y en la pestaña del PowePivot apretamos "Datos de Excel-Crear Tabla Vinculada"

Excel abre la ventana del PowerPivot donde podemos ver que ha agregado la tabla seleccionada.

Hacemos lo mismo con la segunda tabla. Ahora ambas tablas aparecen en la ventana del PowerPivot

Ahora tenemos que crear una relación entre ambas tablas. El campo en común aquí es Producto. Una de las formas de hacerlo tenemos que cambiar la vista de la ventana a "Vista de diagrama"

Con el mouse conectamos el campo Producto de la tabla Categorías (donde los productos son valores únicos) con el campo Producto de la tabla de ventas

Otra forma de hacerlo es usando "Crear relaciones" en la pestaña "Diseñar"

Una vez definidas las relaciones, abrimos la pestaña Inicio del PowerPivot y usamos Tabla dinámica para general el informe

Excel genera una tabla dinámica a partir de ambas tablas como podemos ver en la ventana de la lista de campos

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

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