Rangos dinámicos con la función INDIRECTO de Excel.

Excel permite construir rangos dinámicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinámicos son aquellos cuya referencia (dirección) se expande o contrae con los cambios en el número de miembros del rango. Estos rangos se definen con fórmulas, por lo general con la función DESREF. También hemos visto que podemos referirnos dinámicamente a un rango usando su nombre como argumento de la función INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinámicos: INDIRECTO no acepta fórmulas como argumentos, sólo texto.
En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.




Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red

Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13

La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas

La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5

Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”

Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia

Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).

Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen

Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar

Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte

Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)

Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir

Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente

El archivo del ejemplo se puede descargar 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.

Listas desplegables en Excel con ajuste automático.

En Excel es my fácil crear listas desplegables. El método más práctico es usando Validación de Datos – Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado también como crear listas desplegables dependientes.

En relación a este tema recibo a menudo consultas sobre cómo ir eliminando de la lista los valores que ya han sido seleccionados.

Supongamos que estamos organizando en que mesa se sentarán los invitados a una cena o recepción. Para el caso supongamos una lista de 16 invitados que habrá que sentar en cuatro mesas, cuatro en cada una.

Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra








El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre “mesas”) de manera que cada vez que ubiquemos un comensal en la tabla éste desaparezca de la lista.

Mostraremos dos posibilidades: con columnas auxiliares y con fórmulas matriciales.

Con columnas auxiliares.

En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rótulo “Por ubicar”




El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la fórmula

=SI(CONTAR.SI(mesas,C2)=1,"",C2)

Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos







Ahora agregamos una nueva columna auxiliar (Aux1)



La fórmula

=CONTAR.SI($D$2:$D$17,"<="&D2)

da un número de orden a los valores del rango C2:C17 (como la función JERARQUIA a una serie de números).
En caso que el valor de celda sea vacío (la celda no está vacía ya que contiene una fórmula) el resultado es 0 (cero).
Nuestro próximo paso es crear una celda para controlar cuantas invitados nos quedan por ubicar, es decir, cuantos valores distintos de cero hay en el rango Aux1. Con este objetivo ponemos esta fórmula en la celda J1
=CONTAR.SI(E2:E17,">0")




Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta fórmula

=K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)




Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable



Para evitar que en la lista desplegable aparezcan los valores #NUM de las últimas dos celda, usamos el valor de la celda “control” para crear un rango dinámico que las excluya con la fórmula

=DESREF(invitados!$G$2,0,0,invitados!$J$1,1)



Aplicamos esta validación de datos al rango “mesas”.



A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando sólo aquellos que quedan por ubicar



Con fórmulas matriciales.

Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17.

La lista de invitados a ubicar la creamos en el rango E2:E17 con esta fórmula matricial

={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($D$2:$D$17,"<="&D2:D17),FILA()-1),CONTAR.SI($D$2:$D$17,"<="&D2:D17),0))}





Como puede verse, no obtenemos errores #NUM, pero las celdas al final de la lista no están vacías. De manera que necesitamos una celda de control, como en la solución con columnas auxiliares, para que la lista desplegable muestre sólo los nombres disponibles.
En la celda H1 ponemos esta fórmula matricial

={SUMA((CONTAR.SI($D$2:$D$17,"<="&D2:D17)<>0)*1)}





La lista desplegable la creamos con el fórmula

=DESREF('invitados matricial'!$E$2,0,0,'invitados matricial'!$H$1,1)

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.

Technorati Tags: MS Excel

Lista desplegable con ajuste automático en Excel

Esta es una variante de la técnica que mostré en la nota sobre cómo agregar valores únicos en una lista desplegable con validación de datos.

Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable?

Supongamos que esta es la lista de las facturas



En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada).

En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas



Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista

.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.

Empezamos por crear la primer columna auxiliar en el rango E6:E19.



En la celda E6 ponemos esta fórmula

=SI(LARGO(C6)=0,FILA(),"")

y la copiamos a todo el rango.

Esta columna nos da un “número de orden” que es de hecho un número entero único (el número de fila) que identifica a las facturas que aún no han sido pagadas.

Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula

=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19))

Como puede verse, sólo las facturas pendientes aparecen en la lista.

Tenemos un pequeño problema y es que cuando una factura ha sido pagada el valor de la fórmula es #NUM!.

Si usáramos el rango F6:F19 para crear la lista de los valores, también el error #NUM! aparecería en ella lo cual es poco elegante.

Una de las soluciones es crear una tercera columna en el rango G6:G19 con la fórmula

=SI(ESERROR(F6),"",F6)



Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"

Ahora usamos validación de datos para crear la lista desplegable en la celda C2

Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.

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.

Technorati Tags: MS Excel

Autocompletar en Validación de Datos

Toda persona que haya usado Excel por algún tiempo conoce la funcionalidad Validación de Datos. Una de las características más "sexy" de esta funcionalidad es la posibilidad de crear listas desplegables con facilidad.

En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.

Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.

Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.

Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).



En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3



Para poder seleccionar el objeto activamos primero el modo de diseño



Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto



En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete



También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.

Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.

Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.



Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito

Al poner B aparece automáticamente Bahamas



Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente

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.

Technorati Tags: MS Excel

Listas desplegables dependientes múltiples

En la nota anterior sobre listas desplegables dependientes mostramos cómo crearlas con validación de datos.
En esa nota vimos como crear una lista desplegable de países y ciudades. Una vez elegido el país, la segunda lista desplegable mostraba sólo ciudades de ese país.
Algunos lectores me consultan como hacer lo mismo pero con más de dos niveles de dependencia. Por ejemplo, continentes-países-ciudades.
La técnica es básicamente la misma. Incluimos las listas en rangos nominados (dentro de nombres, usando Insertar-Nombres-Definir) y luego usamos validación de datos con la opción Lista y en Origen usamos fórmulas con la función INDIRECTO.
Supongamos este cuaderno con cuatro hojas



En la hoja Continentes tenemos una lista de los continentes



Estos valores nos servirán como referencia a los nombres que contendrán la lista de países de cada continente. Definimos el rango A1:A7 dentro del nombre "continente"

continente=Continentes!$A$1:$A$7

En la hoja Países creamos campos con las listas de los países de cada continente. Por comodidad (la mía) he puesto sólo dos países por continente



Finalmente ponemos listas de las ciudades por países en la hoja Ciudades




Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear



Como queremos que Excel use la fila superior como rótulo para los nombres, señalamos la opción "crear nombres en fila superior".

El resultado será:

África=Paises!$A$2:$A$3
América_del_Norte=Paises!$B$2:$B$3
América_Central=Paises!$C$2:$C$3
América_del_Sur=Paises!$D$2:$D$3
Asia=Paises!$E$2:$E$3
Europa =Paises!$F$2:$F$3
Oceanía=Paises!$G$2:$G$3
Como verán Excel ha agregado "_" en los casos que el nombre del continente está formado por más de una palabra. Esto se debe a que por definición los nombres en Excel no pueden tener espacios en blanco (ni símbolos especiales).

Usamos el mismo método para definir los nombres en la hoja Ciudades, con este resultado

Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
Estados_Unidos=Ciudades!$I$2:$I$61
Guatemala=Ciudades!$J$2:$J$61
Colombia=Ciudades!$K$2:$K$61
Vietnam=Ciudades!$L$2:$L$61
Francia =Ciudades!$M$2:$M$61
Islas_Fidji=Ciudades!$N$2:$N$61

Como ven, todos los nombres tiene el mismo tamaño de rango (de la fila 2 a la 61), lo que hará que en la lista desplegable aparezcan espacios en blanco. Más adelante veremos como solucionar este problema.

Ahora que hemos definido todos los nombres, definimos las listas desplegables en la hoja "Elección"

En la celda B1 definimos la lista con la opción Lista y la fórmula "=continente". Esto crea una referencia al rango que contiene los nombres de los continentes



En la celda B2 creamos la lista de países que será dependiente del continente elegido en la celda B1



En este caso creamos la referencia al rango usando la función INDIRECTO. Además tenemos que usar la función SUSTITUIR para poner las líneas "_" en lugar de los espacios entre las palabras, para que el valor de la celda coincida con el nombre del rango

=INDIRECTO(SUSTITUIR(B1," ","_"))

Usamos la misma técnica en la celda B3, usando como referencia el valor de la celda B2



Si elegimos el continente América del Sur, podremos elegir sólo Venezuela o Colombia. Si elegimos Colombia podremos elegir una de las ciudades que hemos incluido en la columna Colombia de la hoja Ciudades



Si fuera necesario podríamos agregar más listas dependientes creando los campos adecuados (barrios, jurisdicciones, etc.).

La técnica que hemos mostrado adolece de un defecto estético, los espacios en blanco. Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento.
Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda B3 (ciudades)

=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)



Ahora la lista dependientes de ciudades no mostrará espacios en blanco.



La fórmula fue tomada del excelente sitio Contextures de Debra Dalgleish.

El cuaderno con el ejemplo y las fórmulas 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.