Mantener datos ordenados con la función JERARQUIA en LibreOffice Calc

Ocasionalmente, en Calc, necesitaremos mantener una serie de datos ordenados según un determinado criterio.  Y no querremos que cada vez que cambien los valores, tengamos que abrir el menú Datos o pulsar sobre botones de ordenación.  Mediante la función JERARQUIA podremos mantener una copia de los datos permanentemente ordenados, aunque cambien los valores.  Usuarios de Excel pueden también usar el método descrito a continuación.

Pulsando sobre las imágenes se abrirán a tamaño grande en una ventana emergente y el libro de ejemplo lo podéis descargar al final del artículo.

1. Obtener los datos

En el archivo de ejemplo hay una serie de datos con Nombre, Apellidos y el importe de una Deuda.  Con el fin de poder comprobar el funcionamiento con mayor facilidad, hemos simulado el importe de la deuda con valores aleatorios entre 1.000 y 9.999.  Esto lo hemos hecho con la función ALEATORIO.ENTRE. De esta manera, pulsando Ctrl + Mayúsc + F9 se modificarán los importes y podremos ver como la lista con los datos ordenados se actualiza instantáneamente.

Hemos organizado la hoja en tres áreas bien definidas:

  • Los datos originales que permanecerán fijos.
  • El cálculo de la jerarquía.
  • Los datos ordenados.

Creando importes aleatorios

2. Evitar duplicidades

La función JERARQUIA que vamos a usar nos permite obtener la posición jerárquica de un valor dentro de una lista.  Si hay valores duplicados, la jerarquía devuelve la misma posición.  Por este motivo vamos a impedir que se produzcan valores duplicados incrementando cada importe en una pequeña cantidad. 

En nuestro ejemplo hemos obtenido el valor del número de fila mediante la función FILA y lo hemos dividido por cien.  Hemos escogido este incremento relativamente grande para poder mostrar el efecto que no es más que incrementar el importe en unos céntimos.  En un caso real, lo haríamos más pequeño, dividiendo el número de fila por cien mil o por un millón.

Evitando valores duplicados

3. Obtener la jerarquía del valor

Una vez resueltas las posibles duplicidades, ya podemos usar la función JERARQUIA. Ésta tiene tres argumentos, aunque sólo los dos primeros son obligatorios.

En la fórmula que se puede apreciar en F5:

=JERARQUIA(E5;$E$5:$E$19)

Lo que se hace es buscar qué orden jerárquico representa el valor contenido en la celda de la izquierda en relación a toda la columna de valores bajo el encabezado Corrección duplicados.  Por defecto, la jerarquía es según orden descendente, es decir, en nuestro ejemplo para el mayor importe nos dará el resultado 1.  Si lo quisiésemos ascendente, bastaría añadir un argumento adicional distinto de cero.  Por ejemplo, estableciendo la fórmula:

=JERARQUIA(E5;$E$5:$E$19;1)

En este caso sería el importe menor el que mostraría el resultado 1.

Obteniendo la jerarquía del valor

4. Obtener los datos

Ahora que ya tenemos bajo el encabezado Jerarquía descendente la jerarquía correspondiente a cada valor de la Deuda, ya podemos recuperar la información.

Para ello, hemos creado una columna Orden y mediante una función COINCIDIR, encontraremos la posición que ocupa este dato dentro de la lista de Jerarquía descendente.  Esta función la hemos anidado dentro de una función INDICE, que obtendrá el Nombre, Apellidos y Deuda correspondiente a la posición obtenida por COINCIDIR.

Detallamos un poco el comportamiento de la fórmula contenida en J5:

=INDICE(A$5:A$19;COINCIDIR($H5;$F$5:$F$19;0))

INDICE tiene dos argumentos:

  • El intervalo o rango de celdas que deseamos obtener.  En este caso (A$5:A$19) es el Nombre, pero hemos dejado la referencia a la columna A como relativa (sin $ delante) para que nos sirva también para Apellidos y Deuda.
  • La posición que deseamos obtener dentro del rango anterior.  Esta posición viene determinada por la función COINCIDIR que hemos anidado en ella (COINCIDIR($H5;$F$5:$F$19;0)).

A su vez, este COINCIDIR tiene tres argumentos:

  • El valor de la columna Orden de la misma fila ($H5).  Se ha fijado la referencia a la columna para que al copiarla bajo las columnas Apellidos y Deuda funcione con toda perfección.
  • La lista de todos los valores de Jerarquía descendente ($F$5:$F$19)
  • El tipo de búsqueda.  El cero (0) introducido aquí fuerza que la coincidencia deba ser exacta y no aproximada.

El resultado es que obtenemos la posición relativa dentro de la lista de cada uno de los valores orden y recuperamos esa posición de los datos originales.

Obteniendo los datos ordenados

5. Comprobar el funcionamiento cambiando los valores

Como el importe original de deuda lo hemos obtenido con una función ALEATORIO.ENTRE, bastará con pulsar Ctrl + Mayúsc + F9 para que se recalculen los mismos.  Entonces podremos comprobar que la lista de datos ordenados se actualizará instantáneamente.

Al cambiar los datos, la lista con los nuevos valores se actualiza instantáneamente

Posibles ampliaciones

El ejemplo presentado es forzosamente simple por su finalidad didáctica, pero se podrían establecer variaciones y ampliarlo para una mayor funcionalidad:

  • Disponer de varias columnas a ordenar y seleccionar el criterio mediante una validez de datos (la función INDIRECTO será necesaria para lograrlo)
  • JERARQUIA sólo funciona con valores numéricos.  Pensar como se podría resolver un criterio de ordenación alfabética.  Yo miraría de jugar con el código de los caracteres (función CODIGO

Seguro que se os puede ocurrir alguna aplicación.

Espero que lo hayáis disfrutado y gracias por llegar hasta aquí.

AdjuntoTamaño
ordenacion-con-formulas.ods22.83 KB