Listas desplegables en Excel con ajuste automático.

sábado, octubre 17, 2009

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



listas desplegables con ajuste automatico




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”


listas desplegables con ajuste automatico

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


listas desplegables con ajuste automatico


listas desplegables con ajuste automatico

Ahora agregamos una nueva columna auxiliar (Aux1)

listas desplegables con ajuste automatico

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


listas desplegables con ajuste automatico

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)


listas desplegables con ajuste automatico

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

listas desplegables con ajuste automatico

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)

listas desplegables con ajuste automatico

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

listas desplegables con ajuste automatico

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

listas desplegables con ajuste automatico

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


listas desplegables con ajuste automatico


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


listas desplegables con ajuste automatico


La lista desplegable la creamos con el fórmula

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

listas desplegables con ajuste automatico


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:

11 comments:

Juan Bernardo Jiménez 19 octubre, 2009 21:11  

Hola Jorge, Buenísimo el tema, me sirve demasiado pero no entiendo como funciona la formula que le da jerarquia al texto, la del contar.si.

Mil gracias

Jorge L. Dunkelman 20 octubre, 2009 17:33  

CONTAR.SI funciona tanto con números como con texto. Si ponemos CONTAR.SI(A1:A100,"Juan") obtenemos el número de veces que la palabra Juan aparece en el rango. Si queremos saber cuantas palabras "menores" a Juan, alfabéticamente, hay en el rango usamos como criterio <&"Juan", es decir una concatenación del operador "menor que" con la palabra del criterio.

Juano 30 octubre, 2009 04:42  

Definitivamente increible, yo ya estaba pensando que solo por me dio de una macro lo podia hacer....Gracias, gracias, gracias....

Anónimo,  08 enero, 2010 18:05  

Hola mi estimado tengo un problema cuando defino el nombre con la funcion desref no me muestra nada, la formula la tengo asi:
=DESREF('Catalogo De Barrios'!$L$3,0,0,'Catalogo De Barrios'!$I$203,1)

Lo que me muestra es en valor es:

{...}


entonces al llamar a la variable BOACO_8 en la lista desplegable no me muestra nada, quisiera saber por que?

Tambien quise hacerlo de un solo asi:

=DESREF('Catalogo De Barrios'!$L$3,0,0,SUMA((CONTAR.SI($L$3:$L$202,"<="&L3:L202)<>0)*1),1)

y no me muestra nada

Otra pregunta hermano la formula la quise hacer de un solo en forma matricial asi:

{=INDICE($L$3:$L$202,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($L$3:$L$202,"<="&$L$3:$L$202),CONTAR.SI($N$3:$N$202,">0")-FILA()+3),CONTAR.SI($L$3:$L$202,"<="&$L$3:$L$202),0),0)}

pero ahi todo bien pero cuando lo quiero dejar de un solo sin ninguna columna extra quedaria asi

{=INDICE($L$3:$L$202,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI($L$3:$L$202,"<="&$L$3:$L$202),CONTAR.SI(CONTAR.SI($L$3:$L$202,"<="&$L$3:$L$202),">0")-FILA()+3),CONTAR.SI($L$3:$L$202,"<="&$L$3:$L$202),0),0)}


Me manda error, la verdad dime si me entendiste por que he tratado y tratado y nada hermano esto me tiene loco, necesito de tu ayuda porfa y gracias tus post son super esto si es excel avanzado

Jorge L. Dunkelman 08 enero, 2010 18:25  

Por favor, mandame el archivo. Es difícil analizar los problemas sin el contexto. Y por favor, lee la nota en la pestaña Ayuda.

Anónimo,  08 enero, 2010 19:22  

Estimado descubri cual es el error la formula esta correcta en la definicion de los nombres pero al momento de mandar a llamar ese nombre con la funcion indirecto este no me muestra nada y no se por que ya que si solo pongo el nombre de la variable no hay problema me muestra los valores en el combobox pero si la llamo con la funcion indirecto no me muestra nada, eso pasa por que la definicion del nombre la tengo hecha con la función desref por eso es pero excel no deberia de no mostrar nada, esta es mi duda favor aclararme si estoy mal en algo

Jorge L. Dunkelman 09 enero, 2010 07:53  

Si usás Excel 2003 o 2007 una de las soluciones es usar Listas (o Tablas) como muestro en esta nota.
Para versiones anteriores de Excel podés fijarte en la solución que muestro en esta otra nota

Marko 30 mayo, 2012 19:48  

Hola, ojala aun puedas responder.. lo que necesito es que en un cuadro no se repitan los valores (sin usar validación de datos) es mas que nada para realizar un kardex de entradas y salidas, con su respectivo cuadro de saldos, pero en los saldos no tendrán que repetirse los datos de los "productos"... es decir pueden existir diferentes entradas y salidas en varios tiempos pero en saldos solo deberá aparecer un listado sin duplicidad de datos..

Jorge L. Dunkelman 01 junio, 2012 17:28  

No me queda claro que es lo que quieres hacer pero, ¿por qué no hacerlo conuna tabla dinámica? (suponiendo que se trata de totalizar datos).

Si se trata de hacer una lista desplegable que no sea con validación de datos, puedes usar controles pero tendrías que programarlos.

Anónimo,  08 agosto, 2014 01:42  

por fa enviamelo en la hoja excel a mi correo: j_cortez4@hotmail.com

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP