domingo, junio 08, 2008

Listas ordenadas en validación de datos de Excel - segunda nota

Esta es una ampliación a mi nota anterior sobre el tema. A partir del comentario de un lector he notado la posibilidad de usar una función matricial en lugar de columnas auxiliares.
Básicamente usaremos las mismas funciones, pero combinándolas en una única fórmula matricial.
Volviendo a nuestro ejemplo, tenemos esta lista



donde hemos creado dos columnas: B para obtener el número de orden de los miembros de la lista y en C la lista ordenada alfabéticamente para crear nuestra validación de datos ordenada.

Para mostrar la solución con fórmula matricial empezamos por crear una nueva hoja, "matricial", y poniendo un rango dinámico para la lista de valores de la columna A en el nombre "frutasm". En la columna B obtendremos la lista ordenada, usando una fórmula matricial



Para el rango de la columna B creamos nombre con rango dinámico, "ordenm"



La fórmula en la columna B es una combinación de las fórmulas que usamos en la solución no matricial (en la hoja "con formulas"), pero utilizando rangos de celdas (matrices) en lugar de una celda por fila

={INDICE(frutasm,COINCIDIR(FILA()-1,CONTAR.SI(frutasm,"<="&frutasm),0))} Como en toda fórmula matricial, la introducimos en el rango de celdas relevante (en nuestro ejemplo B2:B10) usando la combinación de teclas Ctrl+Mayúsculas+Enter.


Para evitar ver los resultados #N/A debidos a las celdas vacías, podemos aplicar formato condicional, pero esto no evitará que los valores #N/A aparezcan en la lista desplegable.



Ahora tenemos que corregir la definición de la lista de validación de datos usando el nombre "ordenm"



Ahora podemos agregar nuevos valores a la lista en la columna A, y estos aparecerán ordenados en la columna B y en la lista desplegable






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.

5 comentarios:

  1. Hola Jorge,
    De entrada agradezco tu esfuerzo y apoyo que encontramos en tu página.
    Mi consulta es la siguiente: tengo una base de datos de la cual debe extraer tres datos partiendo de uno inicial, es decir, tengo un listado de modelos de vehículos(dato inicial de la consulta), un solo modelo de vehículo puede tener 3 tipos de motor (2do dato), dos tipos de combustible (3er dato) y dos tipos de transmisión (4to dato), en tus notas he podido ver como hacer esta validación pero solo con dos datos (como en el ejemplo PAIS y CIUDADES.

    De nuevo agradezco tus gentiles aportes.

    Con amistad y aprecio.

    ResponderBorrar
  2. Hola Jorge:
    Enhorabuena por éste magnífico blog y tu extraordinario trabajo.
    Mi consulta es la siguiente:
    He creado una hoja de cálculo con varias listas desplegables que cada una comienza con la expresión "NO".En ésta posición no se selecciona nada,y deja los valores a cero,pero se debe hacer manualmente.
    He intentado crear un botón con una macro ,para colocar la selección de la lista automáticamente
    en ésta posición y dejar la hoja limpia de los valores anteriores.No lo he podido resolver.
    Gracias por tu ayuda.

    ResponderBorrar
  3. No me queda claro qué significa "no se selecciona nada,y deja los valores a cero".
    En lugar de "NO" podrías comenxzar las listas con una celda en blanco de manera que si usuario selecciona este valor la celda queda vacía.
    Para borrar los valores de la selección anterior puedes fijarte en mi comestario del 20/06/2007 en esta nota

    ResponderBorrar
  4. Apreciable JORGE
    Espero me puedas ayudar: en una columna tengo que insertar un dato de una lista que esta en otra hoja, ejemplo en A tengo que introducir "001/001/2010", "001/004/2010",003/004/2010", etc, esto lo hice con validación por lista (=NCONTRATO que es mi lista de datos), pero al mismo tiempo quiero impedir que se pueda capturar un dato dos veces, ¿como puedo lograrlo?

    ResponderBorrar
  5. En esta nota muestro cómo hacer para que los valores utilizados vayan desapareciendo de la lista desplegable.

    ResponderBorrar

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