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

Ajuste automático de fecha en el calendario de Excel

Una de las notas más populares de este blog es Validar fechas en Excel con un calendario. Al presente registra más de 33 mil vistas y más de 130 comentarios.

Microsoft decidió retirar el control Calendario del paquete de Office 2010, pero muchos de mis lectores siguen usando versiones anteriores o han instalado el control independientemente.

Una de las consultas que recibo en relación a esa nota es cómo hacer para que el calendario se abra en la fecha corriente. Uno de los lectores puso en un comentario cómo hacerlo, pero por lo general los nuevos lectores no se detienen a leer todos los comentarios. Por ese motivo, mostraré en esta nota los pasos a dar para lograr ese efecto.

Creamos el userform con el control Calendario y ponemos los códigos de los eventos, tal como mostré en la nota mencionada.

Ahora agregamos un evento para establecer la fecha del calendario. En el editor de Vba seleccionamos el userform

apretamos F7 para abrir el módulo del control y agregamos este código al evento Activate del Userform

Private Sub UserForm_Activate()
    Calendar1.Value = Now
End Sub

Este evento hará que el calendario se abra siempre en la fecha del día corriente.