Listas ordenadas en validación de datos de Excel

viernes, junio 06, 2008

La situación es la siguiente: en una hoja de Excel tenemos una lista que nos sirve como base para crear una lista desplegable con validación de datos. Esta lista cambia de tanto en tanto. Si queremos mantener la lista actualizada sin necesidad de realizar cambios en forma manual, definimos un rango dinámico como ya hemos mostrado en el pasado.
Por comodidad queremos que la lista desplegable aparezca ordenada alfabéticamente. El problema reside en que a lista que nos sirve de base no sigue ningún orden preestablecido. Una posibilidad, obviamente, es ordenar la lista manualmente cada vez que ésta cambie. Pero, como ya habrán supuesto, queremos que esto suceda en forma automática.
Tenemos dos posibilidades: programar un evento o utilizar fórmulas y columnas auxiliares.
Empezaremos por la segunda opción. Supongamos esta lista (la misma que usamos en la nota sobre cómo agregar valores únicos en la lista desplegable de validación de datos)



Definimos un nombre con un rango dinámico



y creamos una lista desplegable con validación de datos



Al abrir la lista desplegable, veremos los miembros de la lista en el mismo orden que figuran en el rango original



Para ordenarlos con fórmulas empezamos por crear una columna auxiliar, donde le daremos a cada miembro un número de orden con la técnica que mostramos en la nota sobre cómo ordenar texto con fórmula en Excel. Creamos la columna auxiliar "No. de orden" y usamos esta fórmula:

=CONTAR.SI(frutas,"<="&A2)

Ahora creamos una segunda columna auxiliar, donde ordenamos la lista usando las funciones INDICE y COINCIDIR

=INDICE(frutas,COINCIDIR(FILA()-1,orden,0))



Hemos creado un segundo nombre, "orden", que es también un rango dinámico que contiene los números de orden de la columna auxiliar. En la fórmula que usamos en la columna auxiliar "Lista ordenada", usamos la función FILA para obtener el número de orden deseado sin necesidad de cambiarlo manualmente en cada celda.

Todo lo que nos queda por hacer ahora es crear una nombre que contenga el rango de los valores ordenados, es decir, de la columna "Lista ordenada"



Ahora reemplazamos el nombre "Frutas" en la definición de validación de datos, por el nombre "lista_ordenada"



La lista desplegable aparecerá ahora ordenada alfabéticamente



Podemos hacer lo mismo sin columnas auxiliares, programando un evento. Este código hará que con cada cambio nuestra lista se reordene alfabéticamente.

En el módulo Vba de la hoja correspondiente (en nuestro caso, la hoja "evento"), ponemos este código



Como pueden ver, usamos el nombre que define el rango dinámico en nuestro código. La sentencia On error resume next, nos permite borrar todas la lista en la hoja sin que aparezca un mensaje de error.

Ahora al agregar un nuevo valor, por ejemplo "Melón", este aparecerá automáticamente en el lugar deseado.



El archivo con el 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.

8 comments:

Latitud39sur 18 junio, 2008 21:42  

Hola Jorge. Realmente valioso para mi tu tema sobre almanaques en celdas. No se si es el lugar para mi pregunta: tengo una libreta de facturas del 1 al 100. Con excel representé los 2 procesos de mi taller; y cada uno factura. ¿como hago para no repetir los números de facturas? es decir, si un rubro del proceso A tomó la factura 1, el rubro del B debe tomar la 2 y así sucesivamente.

Anónimo,  12 agosto, 2009 23:05  

gracias jorge me gusto y me sirvio mucho esta aclaracion

Anónimo,  12 agosto, 2009 23:06  

gracias parcero me gusto y me sirvio mucho esta aclarcion

Xavier 09 julio, 2011 13:54  

Hola maestro,
felicidades por sl bloc, el mejor en lengua castellana, sin lugar a dudas...
Es la primera vez que comento algo y me da un no se que hacerlo en una entrada que lleva tanto tiempo.
El caso, quiero aplicar el ejemplo de ordenar a traves de un evento y no hay manera de que funcione. La lista de origen, es una hoja distinta y la validacion de datos forma parte de una plantilla que uso para escandellar productos que fabricamos.
Gracias por su tiempo.
PD: forma parte del trabajo de sintesis en el grado de Marqueting que estoy ya preparandopara el proximo curso

Jorge L. Dunkelman 09 julio, 2011 17:28  

Xavier,

te recomiendo que descargues el archivo con el ejemplo para que puedas ver como configurar el evento y donde ubicarlo.

Xavier 09 julio, 2011 21:28  

Muchisimas gracias Jorge,
te debo una caña.
Xavier

Manuel RoBa 14 octubre, 2015 20:01  

Gracias por el aporte. La opción de hacerlo con columnas auxiliares me arroja error:

"Hay un valor no disponible para la fórmula o función. ¿Cómo puedo soucionarlo?

Jorge Dunkelman 17 octubre, 2015 23:03  

Tienes que ubicar la celda donde hay un error de tipo N/A y corregirla.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP