La función JERARQUIA: la ordenación de valores.

Un usuario escribió:

La cuestión que deseo plantear es la siguiente: necesito construir una función o algo así que me devuelva de un rango de celdas la posición (posición y contenido de la celda sería lo ideal) de la celda/s que cumplan una determinada condición (o condiciones, si tal cosa es posible). En la lengua de andar por casa que por ejemplo, a partir de un rango de celdas pueda sacar una lista con las posiciones de las celdas donde haya datos que sean menores que uno dado (o iguales o diferentes o otra condición que se desee imponer) y cuales son los valores que corresponden a cada celda de la lista obtenida.

.
Partiremos, para intentar solucionar esta cuestión, del siguiente listado de valores:

Nuestra condición a aplicar sobre los valores será escoger los que estén entre 100 y 350:
=SI(Y(valor>=100;valor<=350);valor;"")
con lo que obtenemos una nueva lista sólo con los valores que cumplen nuestra restricción.

Ahora los ordenaremos, para lo cual utilizaremos la función de Excel JERARQUIA.
=JERARQUIA(número; referencia; [orden])
Debemos tener cuidado con esta función, ya que si tuvieramos valores repetidos dentro de nuestro rango de estudio la función JERARQUIA asigna la misma jerarquía a los números duplicados, por lo que para poder resolver este inconveniente, incluiremos una nueva función
=CONTAR.SI(rango; criterio)
con esta función conseguiremos determinar qué valores están repetidos en nuestro rango de datos, y cuantas veces.
Tenemos, por tanto, en este segundo paso, las siguientes funciones:
=JERARQUIA(valor;rango_valores_restringidos;1)
de donde obtenemos en qué posición se encuentra cada valor respecto a los valores que cumplen la condición inicial como si nuestro listado se ordenara en forma ascendente;
y la función
=CONTAR.SI($C$2:$C2;valor)
nos fijamos que el rango determinado en el primer argumento de la función es móvil en cuanto a las filas, es decir, según copiamos la función el rango va creciendo. hemos conseguido con esto saber fila a fila cuantas veces se ha repetido un valor.

Por último, uniendo en una sola función lo anteriormente expuesto, tenemos el siguiente orden:

la función final que hemos logrado es:
=SI($C2="";CONTAR($C$2:$C$11)+1;JERARQUIA($C2;$C$2:$C$11;1)+CONTAR.SI($C$2:$C2;C2)-1)
hemos incluido nuestra función dentro de un condicional SI para eliminar los errores de aquellos valores que no cumplían la condición inicial (valores entre 100 y 350).
Sólo nos queda obtener el resultado final:

Adjunto fichero para su análisis.

Jerarquia.xls

Hosted by eSnips