Primero analizaremos las fórmulas en uso en las tablas auxiliares.
El rango en la columna C contiene la fórmula
=SI(CONTAR.SI($A$5:A5,A5)=1,A5,"")
En esta fórmula CONTAR.SI cuenta cuantas veces aparece el valor de la celda en la columna A en el rango. Si aparece una sola vez el resultado es el valor de la celda de la misma fila en la columna A. Si aparece más de una vez, el resultado es "blanco".
Con esta fórmula copiamos al rango en la columna C valores únicos de los valores que aparecen en la columna A.
El problema es que esta lista contiene celdas en blanco, y por lo tanto es poco "elegante" para ser usada como referencia para la lista de Validación de Datos.
Nuestra tarea ahora es reordenar la lista en la columna C de manera que los espacios en blanco aparezcan al final de la lista.
Para lograr esto utilizamos una nueva lista auxiliar en la columna D. Lo que queremos es dar un número de orden a las celdas en la columna C que contengan valores y dejar en blanco cuando la celda en la columna C no contenga ningún valor.
Para lograr esto usamos la fórmulas =SI(CELDA("contents",C5)="","",FILA(C5))
La función CELDA da como resultado el contenido de la celda analizada ("contents" es uno de los parámetros posibles; más información se puede obtener en la ayuda on-line de Excel). La función FILA da como resultado el número de fila de la celda. Si la función CELDA da como un resultado distinto de "blanco", el resultado de la fórmula será el número de fila. Este número nos ayudará a reordenar los valores en la lista auxiliar en la columna F.
En la columna F usamos la fórmula
INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))
La fórmula INDICE da como resultado el valor del miembro de la matriz indicada (en nuestro caso $C$5:$C$25) que ocupa el lugar indicado por el segundo argumento de la función.
Este segundo argumento utiliza la función COINCIDIR con la función (K.ESIMO.MENOR($D$5:$D$25,FILA()-4) como primer argumento.
Esta función devuelve el k-ésimo menor valor de un conjunto de datos. Una explicación sobre esta función se puede encontrar aquí.
La expresión FILA()-4, el segundo argumento de K.ESIMO.MENOR es la posición, dentro de la matriz de los datos que se van a devolver, determinada a partir del menor de los valores. Como empezamos nuestra lista de la fila 5, restamos 4 para obtener 1 en la primera celda del rango, 2 en la segunda y así sucesivamente.
De esta manera veremos aparecer en la lista en la columna F los valores de acuerdo a su aparición en la columna A.
Para evitar resultados #NUM! cuando la celda en la columna D está en blanco, anidamos la fórmula dentro de una función condicional SI, como explicamos en la entrada sobre el tema.
Categorías: Funciones&Formulas_, Varios_
Technorati Tags: Excel
muchas gracias, ahora lo entiendo mucho mejor.
ResponderBorrarHola. oye son muy buenos tus tips. De hecho ya practique varios. Pero tengo una duda y espero que me puedas ayudar. Deja te explico mi problema...
ResponderBorrarEn una hoja tengo una base de datos con autofiltros, y, en otra tengo una especie de reporte, en el que quiero vincular los datos que estan filtrados. P. ej. Tengo estos campos en la base da datos... Grupo, alumnos y materias. Cuando de click en un combo que hice de grupos, quiero, que me aparezcan los alumnos en una sola celda, con forma de lista, Como le hago?
Para este tipo de consultas es mejor que me contactes directamente por mi correo electrónico. Puedes mandarme un archivo de muestra para que me haga una idea del problema.
ResponderBorrarhola jorge
ResponderBorrarEstoy leyendo tus tips y son muy interesantes
mira necesito tu ayuda estoy haciendo un test de personalidad en excel, y kiero validar la celdas para ke solo eligas una opcion de cada columna, pero ke tampoco se eliga en la misma fila.
M L
Persuasivo x x
Gentil x
Humilde
Original
gracias...y espero entiendas lo ke kiero hacer.
espero tu respuesta...
bye
Hola,
ResponderBorrarno estoy seguro de haber entendido. Puedes mnadarme un archivo con un ejemplo?
Tengo un problema.
ResponderBorrarQue sucedería si debo de añadir un nuevo valor a la lista de validación de valores únicos.
Tendrías que corregir los rango de las fórmulas de acuerdo. También puedes usar rangos dinámicos usando nombres, como ya hemnos mostrado en esta nota del blog.
ResponderBorrarHola Jorge, una consulta...
ResponderBorrarEn este ejemplo, se podría ordenar la lista alfabéticamente?
Saludos, y gracias por compartir tu experiencia...
Andrés
Andrés, si, se puede. He tratado el tema en esta nota y también en esta.
ResponderBorrarHola , como agrego otra opcion a la lista?
ResponderBorrarAgregando el valor en la columna A (en el ejemplo sería en la celda A15) y modificando los rangos de las fórmulas de acuerdo.
ResponderBorrarTe sugiero que veas este post, donde muestro una técnica distinta.