Validación de Datos en Excel - Agregar valores únicos a la lista desplegable

sábado, julio 29, 2006

Todo usuario que lleve algún tiempo trabajando con Excel, conoce la funcionalidad Validación de Datos.
Esta funcionalidad permite controlar el tipo de datos que son introducidos en un rango determinado. Existen varias posibilidades






Las más interesantes, en cuanto a posibilidades y flexibilidad, son Lista y Personalizada.
Lista permite generar una lista desplegable de la cual puede el usuario elegir valores válidos.
Ya hemos mostrado diferentes técnicas para poblar la lista, en especial haciendo uso de nombres, para referirnos a rangos que se encuentran en otra hoja y para establecer referencias dinámicas.
En una entrada anterior mostraba como crear listas desplegables dependientes. Es decir, que los valores de una lista de validación de datos dependan de la elección de un determinado valor en otra lista.

Hoy veremos como agregar valores únicos a una lista desplegable de validación de datos.
El archivo con el ejemplo se puede descargar aquí.
La técnica consiste en generar una lista de valores únicos en un rango de alguna hoja, que será la referencia de la lista de validación de datos.
Para lograrlo usamos una tabla auxiliar con dos rangos.


En el rango "Valores únicos" usamos la fórmula =SI(CONTAR.SI($A$5:A6,A6)=1,A6,"")
Esta fórmula nos permite establecer si un valor aparece más de una vez. Es importante prestar atención a la definición del rango $A$5:A6, donde la primer celda tiene una referencia absoluta y la segunda una referencia relativa.

En el rango "No. De Orden" generamos un número que nos servirá de argumento en la fórmula que generará la lista de valores únicos.
=SI(CELDA("contents",C5)="","",FILA(C5))
El número es el número de fila sólo par los valores que aparecen por primera vez. Esta fórmula usa como argumento el resultado de la fórmula del rango "Valores únicos".

En el rango "Lista" usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

para hacer aparecer los valores que tiene número de orden.
Para evitar resultados #¡NUM!, agregamos una condición (en color verde) para evaluar resultados de error

=SI(ESERROR(INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))),"",INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25)))

Una explicación detallada de las fórmulas se puede leer aquí.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags:

16 comments:

Anónimo,  31 julio, 2006 00:47  

hola podrias dar un poco mas de explicacion de cada paso, al ser formulas muy complejas, es muy dificil de entender para un usuario de un nivel bajo-intermedio unas explicaciones tan concretas.

muchas gracias y enhorabuena por su blog.

Jorge L. Dunkelman 31 julio, 2006 18:49  

Hola,
en estos días estoy muy ocupado con un proyecto. Prometo publicar una explicación detallada en breve.

Anónimo,  08 enero, 2007 09:28  

Hola Jorge.
Desde hace un tiempo que no se pueden bajar los archivos xls de ejemplos que ofreces. ¿Existe algún otro sitio desde el que se puedan bajar? Gracias.

Jorge L. Dunkelman 08 enero, 2007 20:59  

Hola, estoy buscando una alternativa al Filelodge, que se ha vuelto imposible. Espero poder resolverlo en pocos días.
Entre los proyectos del 2007 figura abrir un sitio propio para estos archivos, pero esto llevará un tiempo.
Mientras tanto, si me das tu dirección e-mail te puedo mandar el archivo.

Anónimo,  18 agosto, 2007 17:37  

Hoal Jorge muy buenos tus blogs, por favor me puedes ayudar, tengo una columna con datos y no quiero que haya duplicados, y cuando valido datos no me permite introducir datos nuevos Gracias

Jorge L. Dunkelman 18 agosto, 2007 19:17  

Hola, sigue las instrucciones que aparecen en la nota sobre evitar duplicados con validación de datos.

PabloM 10 enero, 2009 00:28  

Jorge: tu blog es excelente y es un verdadero orgullo que seas argentino. Me creía un usuario avanzado antes de conocerlo pero ahora me doy cuenta lo mucho que me falta aprender. Para mí sos un genio! Muchas gracias.
Mi consulta es:
Quiero usar como validación de celda dentro de la opción Lista - Origen un rango sin duplicados que se ajusta dinámicamente (tal como lo haces en el archivo "valores unicos en lista desplegable.xls" rango Lista usando desref)pero que pueda elegirse con la función indirecto (como lo haces en la nota de los paises y capitales).
Es posible? Cómo?

Jorge L. Dunkelman 10 enero, 2009 11:46  

Hola

INDIRECTO sólo acepta rangos como argumento, por eso cuando tratás de pasar como argumento un rango definido con DESREF, el resultado es un error. Una solución es definir los rangos de valores únicos con suficiente filas para que a medida que se vayan agregando valores éstos aparezcan en la lista.

PabloM 14 enero, 2009 15:11  

Jorge:
Como hago para que la Lista desplegable de validación de celda aparezca con el primer registro seleccionado en lugar de el último? Como agregué varias filas en blanco ahora cuando despliego la lista me aparece seleccionado el último dato que es un blanco y debo subir a ver los datos moviendo la barra de desplazamiento.

PabloM 14 enero, 2009 15:48  

Jorge: Usando tu nota de "Listas desplegables dependientes múltiples" encontré la solución. En lugar de definir la validación de la Lista con la función: =Indirecto(q9)
lo hago con:
=DESREF(INDIRECTO(Q9),0,0,CONTARA(INDIRECTO(Q9))-CONTAR.BLANCO(INDIRECTO(Q9)),1)
y así obtengo solo los registros con datos en la lista sin incluir los vacíos. Insisto, sos un genio! Gracias.

fofi 10 julio, 2009 02:55  

No tengo mas que felicitarte por tu blog, es sencillamente genial, y para mí motivo de consulta permanente. Apelando a tu paciencia paso a plantearte mi situación, lo mas conciso que puedo:

Tengo en una columna un listado de una cantidad muy importante de números de Facturas (mas de 2000, y se irán agregando), las cuales se van cargando (columna “abonadas”-lista desplegable-) conforme se abonan. El problema es el siguiente : Cómo puedo hacer para que la lista se vaya reduciendo en la medida en que las voy cargando en la columna de “abonadas”.
Desde ya Muchas gracias!!, saludos Héctor.-

Jorge L. Dunkelman 10 julio, 2009 07:21  

Fofi,
tenés que usar una columna auxiliar para indicar el estado de la factura (abonada, no abonada). Luego usamos esa columna como criterio para crear la lista de valores. Por ejemplo, supongamos que la lista de facturas está en el rango A2:A15. En el rango B2:B15 ponemos "abonada" donde corresponda. EN el rango C2:C15 ponemos esta fórmula:

=SI(ESBLANCO(B2),A2,"")

En el rango D2:D15 ponemos

=SI(LARGO(C2)<>0,FILA(),"")

Finalmente en E2:E15 creamos la lista de valores con

=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))

Los valores #NUM! los ocultamos con formato condicional.

Anónimo,  24 enero, 2010 14:40  

Jorge, en la validacion de datos, la fecha para desplegar la lista solo me aparece cuando doy click a la celda. Es posible hacer que siempre aparezca en todas las celdas con validacion? Tengo plantillas en las cuales no se como indicar visualmente que algunas celdas poseen listas desplegable.

Jorge L. Dunkelman 24 enero, 2010 16:32  

No, las flechas sólo aparecen cuando se selecciona la celda.
Como alternativa podés poner combobox de la barra de formularios o de la barra de controles ActiveX.

Marcelo Javier Barbero 24 junio, 2012 18:44  

Excelente todo el desarrollo Jorge, esta soluciones son muy útiles para la comunidad. Una pregunta, como tendría que hacer para que en la columna "F" los valores aparezcan ordenados alfabéticamente?
Saludos
Marcelo B.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP