ORDENAR VALORES UTILIZANDO FÓRMULAS

Hola a todos:

Estos días he recibido multitud de consultas y hoy me gustaría escribir un post muy sencillo sobre la posibilidad de ordenar valores utilizando únicamente fórmulas.

En la consulta inicial me preguntan cómo se pueden ordenar una serie de valores siempre numéricos tanto de menor a mayor como a la inversa, de mayor a menor.

Bien, esto lo podemos hacer con una sencilla fórmula, veamos los datos:

En este caso es una relación de personas con una cifra que ha alcanzado (de lo que sea, nos vale como ejemplo).

La cuestión es ahora cómo ordenar esos números sin utilizar el filtro o el comando de ordenar de la cinta de opciones.

Pues bien, podemos utilizar la siguiente función:

=K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2))

Con  K.esimo.Mayor vamos a obtener el número mayor de un rango, en este caso, al mover la fórmula nos va a ir mostrando el mayor según el rango seleccionado.

Y el resultado será el siguiente:

Como podéis ver, conseguir ordenar el rango de Mayor a Menor. Si necesitamos invertirlo y hacerlo de Menor a Mayor, utilizaremos K.esimo.Menor.

Pero el lector me solicitó si también podía conseguir que se mostrase en otra columna los nombres relacionados con los números ordenados. Y en efecto, sí podemos obtenerlos, y lo vamos a hacer con la siguiente fórmula:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2));$B$2:B10;0)+1;1))

Dado que con la fórmula inicial podemos ordenar los números en relación a la lista inicial, con la función Coincidir podemos obtener la fila en la que se encuentra nuestro número antes de ordenarlo, y  si sabemos la fila, con la función Dirección podemos obtener la celda que nos interesa de la columna A para obtener el nombre.

Así:

He indicado con flechas en rojo la dirección con la que obtenemos las celdas de referencia que nos interesa.

Ahora, para obtener el nombre, utilizaremos la función indirecto:

Y así, finalmente obtenemos toda la información que necesitamos.

Bien!!

Pero a los pocos días el mismo lector me solicitó si de alguna manera podría incluir rangos dinámicos en ambas fórmulas para no tener que ir actualizando los rangos a mano cada vez que el listado se fuese incrementando.

Pues eso también se puede realizar, la manera es incluyendo la función Desref en las dos fórmulas, y lo haremos sustituyendo en ambas fórmulas la parte que hace referencia a los rangos por:

DESREF($B$2; 0; 0; CONTARA(B:B))

Y así ya podemos obtener la misma información, pero con rangos dinámicos:

Para ordenar:

=K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA(B:B));FILAS($B$2:B2))

Para obtener los nombres:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA($B:$B));FILAS($B$2:B2));DESREF($B$2; 0; 0; CONTARA($B:$B));0)+1;1);1)

En resumen:

Y eso es todo!. Como podéis observar, lo hemos conseguido.

Es importante comentar que está fórmula es solo válida para datos numéricos y en los que no existen duplicados. Si contamos con duplicados tendríamos que trabajar con una columna auxiliar en la que introducir algún elemento para «desempatar» esos datos, por ejemplo añadiendo una cifra muy pequeña a los números originales.

Descarga el archivo de ejemplo pulsando en: ORDENAR VALORES UTILIZANDO FÓRMULAS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies