Listas desplegables con valores dependientes

Supongamos que queremos crear un formulario en Excel con dos celdas: hora de comienzo y hora de finalización. Las horas las elegimos de celdas desplegables, que creamos fácilmente con validación de datos. Pero en nuestro modelo queremos que las horas que aparezcan en la la celda “hora de finalización” dependan de la hora elegida en la celda “hora de comienzo

Para lograr que la lista desplegable de la celda “Hasta” se adapte a la hora introducida en la celda “De” tenemos que usar rangos dinámicos que crearemos con la función DESREF.

Los pasos para crear este modelo:

En el rango A3:A26 definimos las horas del día (en este ejemplo usamos una escala de una hora, pero podemos usar medias horas o cualquier otra escala). Definimos el nombre “rngHorarioSencillo” que se refiere a este rango.

Definimos el nombre “celdaDeSencillo” que se refiere a la celda E4.

En la celda C8 ponemos la fórmula

=COINCIDIR(celdaDeSencillo,rngHorarioSencillo,0)

y creamos el nombre “controlHora2Sencillo” que se refiere a la celda C8. Como alternativa podemos crear un nombre que contenga la fórmula en lugar de ponerla en una celda. En mi opinión, el uso de celdas nos permite crear modelos más claros y fáciles de administrar.

Para crear la lista desplegable de la celda “De” usamos el nombre “rngHorarioSencillo”

Para crear la lista desplegable en la celda “Hasta” usamos un rango dinámico definido por esta fórmula:

=DESREF(sencillo!$A$3,controlHora2Sencillo,,CONTARA(rngHorarioSencillo)-controlHora2Sencillo)

Otro detalle que podemos agregar es un evento que borre el contenido de la celda “Hasta” cuando cambiamos el valor de la celda “De”. En el módulo Vbe de la hoja ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("celdaDeSencillo").Address Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub


Finalmente ocultamos las columnas A y B.


Un caso más complejo es cuando queremos que el modelo incluya varias líneas donde la hora de comienzo de una línea depende de la hora de finalización de la línea anterior

En este caso emplearemos prácticamente la misma técnica, pero en lugar de celdas de control incluiremos las fórmulas en nombres.

En la primer celda del formulario definimos la lista desplegable con el nombre “rngHorario”, que se refiere a todo el rango de horas

Ahora tenemos que definir los controles para las celdas “De” y “Hasta”. En el caso de “De” tenemos que referirnos a la celda “Hasta” inmediata anterior (la que se encuentra una fila por arriba). Esto lo hacemos definiendo la fórmula en un nombre pero asegurándonos que la referencia a la fila sea relativa (sin el signo $).

Al definir el control para el campo “De” seleccionamos la celda D5 y definimos el nombre “hora1Ref” con esta fórmula

=COINCIDIR(complejo!$E4,rngHorario,0)

Para definir el control para el campo “Hasta” seleccionamos previamente la celda E4 y definimos el nombre “hora2Ref” con esta fórmula

=COINCIDIR(complejo!$D4,rngHorario,0)

Estos controles nos sirven ahora para definir los rangos dinámicos de las listas desplegables.

Para las celdas D5:D9 definimos el nombre “rngHora1Comp” con la fórmula

=DESREF(complejo!$A$3,hora1Ref,,CONTARA(rngHorario)-hora1Ref)

Para las celdas E4:E9 definimos el nombre “rngHora2Comp” con la fórmula

=DESREF(complejo!$A$3,hora2Ref,,CONTARA(rngHorario)-hora2Ref)

El archivo con el ejemplo puede descargarse aquí.

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.

Fórmulas con condiciones múltiples en Excel

Supongamos un formulario en Excel donde controlamos un proceso de aprobación de créditos compuesto de tres partes: presentación, proceso y aprobación

Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”



La solución más inmediata es crear una fórmula con la función SI

=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")

Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.

Podemos superar este problema creando una fórmula con la función ELEGIR

=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")

Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.

Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes

=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")



MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha



También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.

La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)

La fórmula es (o parece!) sencilla

=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)

Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus



Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método

Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).

El archivo con los ejemplos se puede descargar aquí.

Tablas Dinámicas en Excel 2010 – Segmentación de Datos

En la nota anterior empecé a exponer las mejoras introducidas en Excel 2010 en comparación a las versiones anteriores. Vamos a tratar otra mejora substancial al funcionamiento de las tablas dinámicas: la segmentación de datos (Slicers, en la interfaz en inglés).

Como en la nota anterior usamos la base de datos NorthWind para crear nuestro ejemplo

La posibilidad de filtrar los datos de la tabla existe en todas las versiones de Excel

Si bien el filtrado ha sido mejorado considerablemente en Excel 2007, todavía existían muchas limitaciones:
Si queremos elegir más de un ítem tenemos que marcar y desmarcar uno por uno (en Excel 2007 podemos usar filtros con criterios)

• Los filtros no reflejan la organización jerárquica de los datos Si los datos están organizados en forma jerárquica (en nuestro ejemplo Tipo de Producto-->Producto). Por ejemplo, si agregamos los productos al capo de la filas y filtramos por Beverages (bebidas), seguiremos viendo todos los productos de todas las categorías.

• No podemos configurar la apariencia de los filtros, ni su ubicación. Esto limita en gran medida la posibilidad de usarlo directamente en nuestros dashboards, en particular si el usuario no tiene suficiente experiencia en el uso de Excel.

• Y, tal vez lo más importante, si creamos más de una tabla para la misma base de datos, tenemos que filtrar cada tabla por separado.

Todas estas limitaciones han sido superadas en Excel 2010 con la introducción de la segmentación de datos.
Para activar la segmentación de datos tenemos que seleccionar alguna de las celdas de la tabla dinámica

Vamos a empezar por insertar dos segmentos



Como puede verse, podemos mover y cambiar el diseño de los filtros. Cuando activamos uno de los filtros, aparece en la cinta el menú Herramientas de Segmentación de Datos. No me voy a extender aquí en todas la posibilidades pero si llamar la atención al menú Configuracion

Ahora crearemos una segunda tabla basada en los mismos datos para mostrarlos en forma porcentual. Para hacer el uso más evidente, vamos a exhibir en las tablas los productos.

Naturalmente queremos aplicar un filtro simultáneamente a ambas tablas. Esto la hacemos con el menú Conexiones de tabla dinámica (en la cinta de Herramientas de Segmentación o con el menú contextual del cuadro de segmentación)

Este video muestra dos efectos del filtro: 1) el filtro ProductName se sincroniza con la elección en el filtro CategoryName; 2) ambas tablas muestran los cambios en forma sincronizada



Creo que todo esto, junto con las posibilidades de configuración personalizada (y todavía no hablamos de Vba), muestran que en Excel 2010 tenemos un herramienta aún más poderosa para el desarrollo de nuestros informes o dashboards.