Ordenar listas numéricas con fórmulas en Excel

Nada más sencillo que ordenar listas o tablas en Excel. Un clic al icono correspondiente (ascendente o descendente) y Excel ordena la lista. Pero en ciertas situaciones queremos que la lista se ordene automáticamente al ir agregando o quitando datos.

Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio, pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el caso de listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).

Supongamos un rango donde vamos agregando fechas (recordemos que la fechas son números en Excel)

Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en forma aleatoria.

Para ordenar esta lista en orden ascendente usamos esta fórmula

=K.ESIMO.MENOR(lstFechas,FILA()-1)

donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, con la fórmula

=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))

Para ordenar la lista en orden descendente usamos esa otra fórmula

=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)

Una desventaja de este método es que cada vez que agregamos un valor a lista debemos copiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”



Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.

Formato condicional en los últimos n valores de una serie

El lector Willy me consultaba en la nota de ayer cómo aplicar un fondo con formato condicional a las celdas que participan en el cálculo (las últimas n celdas de la serie). La consulta es interesante ya que esto nos permite un control visual efectivo de los valores comprendidos en el cálculo. Si bien le respondí en un comentario en la nota, vale la pena ampliar el tema.

Formato condicional funciona con fórmulas booleanas, es decir, que el resultado debe ser VERDADERO o FALSO. Sólo cuando el resultado es VERDADERO, se aplica el formato elegido.

Dado esto, nuestra táctica será calcular si la fila de la celda evaluada cae dentro del rango de la fórmula. Por ejemplo, si queremos marcar las últimas 10 filas

seleccionamos el rango C5:C160 (siguiendo con nuestro ejemplo) y en “formato condicional-nueva regla-utilice fórmula…” usamos

Y(FILA(C5)>=CONTAR($C$5:$C$160)+5-$C$1,FILA(C5)<=CONTAR($C$5:$C$160)+4)

donde C1 contiene el número de valores a incluir en el cálculo.

La fórmula funciona así: calculamos el número fila de la primer celda en el rango con

 CONTAR($C$5:$C$160)+5-$C$1

 Calculamos el número de fila del última valor en el rango con

 CONTAR($C$5:$C$160)+4

 donde 4 es el número de filas por encima de la primer fila del rango de valores.

 Ahora usamos la función FILA para comparar si el número de fila de la celda evaluada cae dentro del rango relevante. Esto lo hacemos con la función Y, que da VERDADERO sólo si todas las condiciones dentro la fórmula se cumplen.

 Personalmente prefiero una técnica distinta. Por algún motivo que no termino de comprender, muchos usuarios evitan usar columnas auxiliares en los modelos de Excel. Esto nos lleva casi siempre a crear fórmulas complicadas cuyo principal problema reside en la dificultad de controlar los resultados (o recordar que quisimos hacer cuando volvemos a trabajar con el modelo dos semanas más tarde).

 En el caso que estamos analizando, mi propuesta es la siguiente:

 1 – insertamos 3 columnas a la izquierda de la tabla de datos (dos columnas son suficientes)

 2 – En la celda B2 ponemos la fórmula “=CONTAR(E5:E160)+4”, que calcula la última fila con valores en el rango (tal como hicimos en la fórmula anterior)

 3 – En la celda A2 ponemos “=B2-E1+1” que calcula la primer fila del rango (E1 contiene el número de valores que queremos incluir en el cálculo)

 4 – En la celda B5 ponemos la fórmula “=Y(FILA(E5)>=$A$2,FILA(E5)<=$B$2)” y la copiamos a lo largo del rango a evaluar (en nuestro ejemplo B5:B160)

 Como se ve, creamos una serie de valores VERDADERO (cuando la fila cae dentro del rango) o FALSO (cuando está fuera del rango). Ahora usamos esto valores para accionar el formato condicional 

Como puede apreciarse, la fórmula en el formato condicional es obviamente sencilla, lo mismo que las fórmulas en las columnas auxiliares.







El último toque es ocultar las columnas auxiliares

Descarga del archivo del ejemplo

Cálculos con los últimos n valores de una serie en Excel

Un compañero de trabajo quería calcular el promedio de los últimos 12 valores de una serie. En su caso, el precio promedio de una serie de productos

Para calcular el promedio de los últimos doce meses usaremos esta fórmula

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-12,0,12,1))

A medida que agreguemos (o quitemos) valores, la fórmula se ajustará automáticamente.

Sobre el funcionamiento de la función DESREF ya hemos escrito en este blog. El “truco” aquí es que usamos CONTAR para encontrar la última celda no vacía (suponemos que contienen números) y luego “retrocedemos” 12 puntos atrás.
En nuestro ejemplo

CONTAR(B5:B160)-12

da 2 (14-12) lo que nos “lleva” a B7; luego nos extendemos hasta B18 usando 12 como argumento en DESREF.

Un detalle a tomar en cuenta es que el rango de valores debe ser continuo (sin celdas vacías entre dos o más valores); en caso contrario el resultado será incorrecto.

Podemos modificar la fórmula de manera que la cantidad de valores a considerar en el cálculo se determine de forma dinámica

Usamos el valor en la celda B1 como argumento para determinar la cantidad de meses a tomar en cuenta

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-B1,0,B1,1))

Apéndice: en esta nota muestro como resaltar las celdas comprendidas en el cálculo con formato condicional