LibreOffice Calc: Ordenar una tabla de forma automática

Vamos a ver como ordenar una tabla sobre otra de forma automática (o dinámica) a medida que cambiamos valores.

En el ejemplo partimos de una tabla que contiene poblaciones y su temperatura. Nuestro deseo es obtener otra tabla ordenada de menor a mayor en función de las temperaturas.

Para resolver este ejemplo usaremos unas funciones que no son tan comunes.

La primera función es JERARQUÍA(). Un ejemplo fácil: tenemos una lista de VALORES y queremos indicar para cada valor su posición en una hipotética lista de menor a mayor:

VALORES	14	22	11	8	3	7	15
JERARQUÍA	5	7	4	3	1	2	6

Significado: Por ejemplo el VALOR 14 estaría en la posición 5 de la jerarquía. El VALOR 3, estaría en la posición 1 de la jerarquía. El VALOR 22 estaría en la posición 7 de la jerarquía.

Vamos ahora con nuestro ejemplo de hoja de cálculo. Fijate en los valores de la columna Temperatura: (24, 24, 21, 18, 21, 22, 24)

Si queremos ver estos valores jerárquicamente de menor a mayor, el primer valor (el más pequeño) sería el 18, pero ahora tenemos una sorpresa, ya que el valor 21 se repite 2 veces. La función les da el mismo valor, el segundo puesto. La función JERARQUÍA() devuelve el siguiente resultado:

Temperatura: (24, 24, 21, 18, 21, 22, 24)

JERARQUÍA: (05, 05, 02, 01, 02, 04, 05)

Significado: La temperatura 24, está en la posición jerárquica número 5, la temperatura 22 está en la posición jerárquica número 4, la temperatura 18, al ser la más baja, está en la posición 1.

Llama la atención que no exista posición 3 en la jerarquía y que sólo llega hasta 5 cuando hay 7 temperaturas. La razón es que hay valores repetidos que ocupan la misma posición jerárquica.

Usando la función jerarquía(), la fórmula para saber en que posición está la temperatura de 18 grados suponiendo un orden ascendente, sería la siguiente:

=JERARQUIA(18;C2:C8;1

El resultado sería 1, es decir, en primer lugar. Esto lo podríamos traducir como: “dime el valor jerárquico donde está la temperatura de 18 grados dentro del rango de valores que van de C2 a C8. Utiliza una jerarquía ascendente, de menor a mayor (lo indica el 1 final de la fórmula)”.

Vamos a ir a la hoja de cálculo y aplicaremos la formula para cada una de las temperaturas:

=JERARQUIA(C2;$C$2:$C$8;1)

Obtenemos el siguiente resultado:
 

Vemos que no aparece el valor jerárquico 3 y en cambio se repiten las jerarquías 2 (2 veces) y 5 (3 veces). Esto nos obliga a retocar un poco nuestra fórmula, ya que no queremos valores repetidos.

Lo ideal es tener valores jerárquicos del 1 al 7, es decir, sin repeticiones. Esto lo conseguimos modificando la fórmula:

=JERARQUIA(C2;$C$2:$C$8;1) + CONTAR.SI($C$2:C2;C2) – 1

La función CONTAR.SI() cuenta cuantas veces hay un valor en un rango de valores. Por ejemplo:

=CONTAR.SI(C2:C8;24)

El resultado sería 3. Nos dice cuantas veces se repite el valor 24 entre las temperaturas (C2:C8).

La fórmula al completo básicamente calcula la jerarquía para cada temperatura y le suma cuantas veces se repite ese valor hasta ese instante. Al final restamos uno.

Un poco lio, pero fíjate en “Noreña” con una temperatura de 21 grados. Si recorremos la tabla desde la primera posición (Avilés con 24) hasta “Noreña” con 21, ¿Cuantas veces contamos el valor 21? pues 1 vez. Ahora fíjate en “La Pola” con una temperatura también de 21 grados. Si recorremos la tabla desde la primera posición (Avilés con 24) hasta “La Pola” con 21, ¿Cuantas veces contamos el valor 21? pues 2 veces.
 

Por tanto “Noreña”, tenía como jerarquía 2 y ahora le sumamos 1 y le restamos 1. Resultado 2.

En cambio “La Pola” tenía como jerarquía 2 y ahora le sumamos 2 y le restamos 1. Resultado 3.

De esta forma cada lugar con respecto a su temperatura tiene una jerarquía diferente de 1 a 7. Oviedo tiene la jerarquía 1 por tener la temperatura más baja, mientras que Llanes tiene una jerarquía de 7 por tener la temperatura más alta y estar al final de la lista (coincide la temperatura con Avilés y Gijón).
 

Ahora ya tenemos clasificados jerárquicamente los 7 valores de las temperaturas.

Ya es hora de crear la nueva tabla con los valores ordenados de menor a mayor según la temperatura.

Pero antes vamos a ver otras 2 funciones:

La función, COINCIDIR(). Si tenemos un rango, por ejemplo los lugares que van de B2 a B8 (de Avilés a Llanes) y queremos saber una localidad en que posición de la lista está escribimos algo como:

=COINCIDIR(“Nava”;B2:B8;0)

El resultado es 6, es decir es el sexto valor de la lista. No estamos teniendo en cuenta si están o no ordenados los lugares. El cero final en la función indica que tiene que ser el valor exacto.

La función, INDICE(). Si tenemos un rango, por ejemplo los lugares que van de B2 a B8 (de Avilés a Llanes) y queremos saber que localidad hay en la casilla 3, escribimos:

=INDICE(B2:B8;3)

El resultado es “Noreña”.

Como ves ambas funciones son complementarias, COINCIDIR() devuelve la posición de un valor en una la lista y la función INDICE, dada la posición devuelve el contenido.

Bueno, todo listo. Nos colocamos en la hoja de calculo sobre el lugar que queremos que aparezca la tabla ordenada.

Para simplificar las fórmulas añadimos una columna donde numeramos cada posición (del 1 al 7).

En la nueva columna “Lugar” (a partir de la casilla B13 en el ejemplo) es donde pegamos y extendemos la fórmula:

=INDICE($B$2:$B$8;COINCIDIR(A13;$D$2:$D$8;0))

En la columna nueva “Temperatura” (a partir de la casilla C13) es donde pegamos y extendemos la fórmula:

=INDICE($C$2:$C$8;COINCIDIR(A13;$D$2:$D$8;0))

El resultado FINAL es el esperado:

Más trucos:

Si no te gusta la primera columna que numera de 1 a 7, la podemos quitar, pero las formulas a utilizar serían un poco más complejas al tener que usar la función FILA(), que devuelve el número de la fila donde está la fórmula. Por ejemplo si tenemos en la casilla A100 la fórmula:

=FILA()

El resultado sería 100, es decir el número de la fila.

Usando la función FILA() para la columna “Lugar” la formula sería:

=INDICE($B$2:$B$8;COINCIDIR(FILA()-12;$D$2:$D$8;0))

Fijate al usar “FILA()-12”, si estamos en la fila 13, el resultado sería 1.

Para la columna “Temperatura” sería:

=INDICE($C$2:$C$8;COINCIDIR(FILA()-12;$D$2:$D$8;0))

Otro truco. Para las temperaturas también se puede usar la función K.ESIMO.MENOR(). Con esta función podemos ordenar automáticamente una lista de valores. La lista no tiene por que estar ordenada. Simplemente necesita el rango de la lista y la posición elegida, como resultado devuelve el valor de la lista que va en esa posición suponiendo una lista ordenada de menor a mayor.

Por ejemplo si tenemos un rango denominado LISTA_DE_VALORES sin ordenar:

LISTA_DE_VALORES (49, 23, 88, 5)

¿Qué valor es el primero (el menor) si queremos ordenación de menor a mayor?

=K.ESIMO.MENOR(LISTA_DE_VALORES;1)

El resultado sería 5, el valor más pequeño de la lista.

¿El segundo menor?

=K.ESIMO.MENOR(LISTA_DE_VALORES;2)

El resultado sería 23.

Para nuestro ejemplo, en la columna “Temperatura” la fórmula a expandir desde la fila 13 sería:

=K.ESIMO.MENOR($C$2:$C$8;FILA()-12)