Valores únicos en lista de Validación de Datos – Explicación de las fórmulas

martes, agosto 01, 2006

En la entrada sobre valores únicos en una lista de Validación de datos, usamos fórmulas un tanto complicadas. Me han pedido una explicación más detallada sobre las fórmulas, así que aquí va!

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:

11 comments:

Anónimo,  01 agosto, 2006 23:26  

muchas gracias, ahora lo entiendo mucho mejor.

Anónimo,  06 septiembre, 2006 20:39  

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

En 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?

Jorge L. Dunkelman 06 septiembre, 2006 20:58  

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.

Anónimo,  31 mayo, 2007 18:14  

hola jorge
Estoy 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

Jorge L. Dunkelman 02 junio, 2007 09:26  

Hola,
no estoy seguro de haber entendido. Puedes mnadarme un archivo con un ejemplo?

Petit Comite 18 agosto, 2008 16:11  

Tengo un problema.

Que sucedería si debo de añadir un nuevo valor a la lista de validación de valores únicos.

Jorge L. Dunkelman 21 agosto, 2008 22:25  

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.

Anónimo,  15 enero, 2013 23:28  

Hola Jorge, una consulta...
En este ejemplo, se podría ordenar la lista alfabéticamente?

Saludos, y gracias por compartir tu experiencia...
Andrés

Jorge L. Dunkelman 17 enero, 2013 18:14  

Andrés, si, se puede. He tratado el tema en esta nota y también en esta.

Anónimo,  28 agosto, 2015 01:42  

Hola , como agrego otra opcion a la lista?

Jorge Dunkelman 28 agosto, 2015 11:28  

Agregando 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.
Te sugiero que veas este post, donde muestro una técnica distinta.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP