sábado, julio 29, 2006

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

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 comentarios:

  1. 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.

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

    ResponderBorrar
  3. 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.

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. 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?

    ResponderBorrar
  7. 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.

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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.-

    ResponderBorrar
  11. 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.

    ResponderBorrar
  12. 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.

    ResponderBorrar
  13. 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.

    ResponderBorrar
  14. 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.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.