Lista desplegable con ajuste automático en Excel

viernes, julio 10, 2009

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

lista desplegable Excel

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

lista desplegable Excel

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.

lista desplegable Excel

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))


lista desplegable Excel

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)

lista desplegable Excel

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


lista desplegable Excel

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


lista desplegable Excel

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:

13 comments:

Jose,  10 julio, 2009 16:05  

Muy bueno Jorge!!!

Lo unico que por ahi tienen estas formulas es que al especificar un rango (como vos pusiste en el ejemplo "INDICE($B$6:$B$19...") nos vemos luego obligados a actualizar la formula cada vez que se inserten filas o bien a insertar filas completas antes de, por ej, la fila 19, para que esa actualización se haga automaticamente.
Seria fantastico que la gente de Microsoft se ponga a trabajar en nuevas formulas o comandos para definición de rangos (algo al estilo $b$6:"ultimo dato hacia abajo"). Se entiende? Sera soñar demasiado?

Un abrazo, José

Giovanni Collado 10 julio, 2009 16:45  

Jorge:
Gracias eso me da una solución.
te agradezco la premura en tu respuesta.

Saludos
Giovanni

Jorge L. Dunkelman 11 julio, 2009 13:59  

José

eso existe en Excel, se llama "rangos dinámicos". He mostrado en algunas notas cómo crearlos.

José Moreno 15 julio, 2009 06:04  

Jorge pesar de no ser partidario de utilizar VBA a menos que sea estrictamente necesario, te dejo este archivo que me parece es una buena opción para resolver el mismo problema.

http://www.esnips.com/doc/67a3ee6c-7d83-47c5-8650-9dd20fbea6e5/Validacion-con-Tabla-Din%C3%A1mica

Espero sea de utilidad.

fofi 28 julio, 2009 02:53  

Jorge, implementé tu solucion, me ha servido mucho. Te envie un mail con un agregado para que lo veas, que era lo que me faltaba. MUCHAS GRACIAS!!

Anónimo,  20 agosto, 2009 00:58  

soy Rudajeli y apenas estoy aprendiendo en esto de excel modifique un poco la formula por que no entendi al gunas cosas como por ejemplo en la formula de K.ESIMO.MENOR se debe colocar Fila()-5 no entiendo porque si coloco otro numero no me funciona y lo otro que no entendi fue la formula =SI(LARGO(C6)=0,FILA(),"") y por eso no la coloque en mi ejercicio a cambio de esa coloque estas en la columna aux 2 coloque esto: =SI(C7="Pagada";"";B7)
en aux 3 coloques esto:=SI(ESERROR(K.ESIMO.MENOR($E$6:$E$27;FILA()-5));"";K.ESIMO.MENOR($E$6:$E$27;FILA()-5))
y en la validacion de datos utilice la columna de axu 3 y funciono bien
gracias

fofi,  21 agosto, 2009 02:24  

Estimado Rudajeli, intento una explicación, dado que llegó a mi correo…
K.ESIMO.MENOR arroja el el “K” menor valor en un rango o matriz:
Sintaxis : K.ESIMO.MENOR (matriz; k)
Por lo tanto se coloca FILA()-5 porque en el ejmplo se pretende ordenar usando del valor de la fila, y como el rango comienza en la fila 5 se le descuenta “5 filas” al valor desde donde se parte. De este modo, se parte del valor uno FILA()-5 = 1, es decir, cuando “k” valga 1 estaremos buscando el menor valor del rango. A la fila siguiente FILA() vale 7 , entonces FILA()-5 = 2, y entnces aquí “k” valdra 2 y colocará el segundo menor valor, y así sucesivamente.
Con respecto a la función =SI(LARGO(C6)=0;FILA();””) el condicional indica que si no tenemos escrito nada (función LARGO(texto), devuelve el nº de caracteres de un texto) en la C6 entonces colocará el valor de la fila, de lo contrario que no coloque nada. Este rango de valores lo utilizará en la función =INDICE con COINCIDIR.
PD.: la ayuda de Excel en la ventana cuando pide que se ingresen los argumentos de la función con la que estamos trabajando (extremo inferior izquierdo) es muy orientativa. Para examinar una función cualquiera, la colocamos en una celda cualquiera y cuando nos pide los argumentos hacemos clic en “ayuda con esta función”. Saludos cordiales.

Anónimo,  03 septiembre, 2009 12:33  

Gracias por el ejemplo!!!
Por favor, abusando de su paciencia, ¿y si lo que queremos es que en una lista desplegable referenciada a un rango de celdas con celdas en blanco, no aparezcan dichas celdas en blanco en nuestra lista desplegable?.

Es que me estoy comiendo las uñas,... :(

Muchas gracias por su Blog.

Rocio.Fuenlabrada.
Madrid.

Anónimo,  29 septiembre, 2009 05:58  

Me sumo a la pregunta de Rocio.

Gracias.

Jorge L. Dunkelman 29 septiembre, 2009 14:53  

Rocío
en primer lugar mis disculpas, recién hoy presté atención a tu comentario. En segundo lugar, muy feo eso de comerse la uñas!!!
Y en tercero, ¿te refieres los blancos que aparecen al final de la lista desplegable?

fofi 09 octubre, 2009 04:32  

Hola Rocío, te comento que el inconveniente de los “blancos” en el desplegable se soluciona con la formula DESREF aplicándola al rango que Jorge llama “Lista” para hacerlo dinámico, teniendo en cuenta que el “Alto” del mismo vendrá dado por el valor (ó cantidad) de blancos que hay en la columna “Estado”. Esto es posible dado que siempre existirá igual cantidad de blancos en la columna” Estado” que de celdas ocupadas con números de facturas en la columna origen del desplegable (o sea “Lista”):

=DESREF(Hoja1!$G$6; 0; 0; CONTAR.BLANCO(Hoja1!$C$6:$C$19); 1)

Anónimo,  08 junio, 2010 23:12  

Sería posible construir una lista desplegable que fuera solamente informativa?

Jorge L. Dunkelman 09 junio, 2010 05:53  

¿Podrías explicar con más detalle qué es una lista sólo informativa?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP