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