Gráfico dinámico que muestra los últimos n puntos de la serie

El origen de esta nota es la consulta de un lector que me pregunta cómo hacer un gráfico que muestre siempre los 12 últimos puntos de una serie.

Como en casi todo gráfico dinámico, el truco está en usar rangos dinámicos definidos en nombres. Por lo general, y en esta nota, lo haremos con la ayuda de la función DESREF.

Supongamos esta tabla de ventas que muestra los 12 meses de año 2009 y que hemos representado con un gráfico.

Queremos que el gráfico muestre siempre los últimos n meses, siendo n un número que el usuario defina. Por ejemplo, si agregamos los enero y febrero del 2010 queremos ver los datos desde marzo del 2009 a febrero del 2010.

Como ya hemos mostrado, vamos a reemplazar los rangos estáticos de la serie de datos y de los valores del eje de las categorías, por rangos dinámicos

Antes de definir los rangos dinámicos, definimos un nombre que cuente cuántos períodos (datos) hay en la tabla. Lo hacemos basándonos en la columna de los períodos (la columna B en nuestro ejemplo) ya que puede haber períodos sin datos

cntPeriodos = =CONTARA(dinamico!$B:$B)-1

Restamos 1 del resultado de CONTARA para eliminar la cuenta del título de la columna.

El próximo paso es elegir una celda donde ponemos el número de meses que queremos mostrar (en nuestro ejemplo H2, y no por capricho como ser verá luego). También a H2 le ponemos nombre, pmtMeses

Ahora definimos nombres dinámicos para el eje de la Y, grfY y para el eje de la X, grfX

grfX =DESREF(dinamico!$B$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

grfY =DESREF(dinamico!$C$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

Una vez definidos los nombres reemplazamos los rangos estáticos en la fórmula SERIES, cuidándonos de dejar el nombre del cuaderno incluido el símbolo !

La explicación de estas fórmulas es la siguiente:

dinamico!$B$3;cntPeriodos-pmtMeses calcula desde donde comienza el rango. Por ejemplo si tenemos 14 períodos en la tabla (calculados por cntPeriodos) y queremos mostrar sólo los últimos 12 (pmtMeses), cntPeriodos – pmtMeses da 2; por lo tanto nuestro rango comienza en B5 y se entiende por 12 celdas hasta B16.

Ahora podemos aprovechar la ubicación de H2 para crear un título dinámico para el grafico

Cuando reemplazamos el valor de la celda H2, el título cambia de acuerdo al contenido del gráfico.

El archivo se puede descargar aquí.

Hipervínculos en Excel - referencias absolutas y relativas

El tema de los hipervínculos en Excel sigue siendo uno de los más consultados por mis lectores. Las preguntas más frecuentes tienen que ver con la "dirección".

La dirección es la referencia al archivo, objeto o páginas Web que se abrirá al pulsar el enlace. Técnicamente la referencia es un URL (Uniform Resource Locator).

El URL puede apuntar tanto a una página WEB como a un archivo.

De acuerdo con Office Online los hipervínculos, es decir la referencia URL, pueden ser absolutos o relativos.

Un URL absoluto es aquel que contiene la dirección completa que se compone de cuatro partes: el protocolo (http, ftp, file), la ubicación física (el servidor Web, o el lugar en la red o en la máquina), el path y el nombre del archivo.

Un URL relativo es aquel en el que falta alguna o algunas de las partes mencionadas.

En Excel los hipervínculos son, por defecto, relativos al cuaderno que los contiene.

Como ejemplo supongamos que tenemos un cuaderno con hipervínculos a imágenes de productos. El cuaderno está guardado en la dirección D:\Catalogo. Las imágenes están en el directorio D:\Catalogo\Productos

Abrimos un cuaderno nuevo, agregamos la lista de productos (en nuestro ejemplo los números de catálogo) y creamos el hipervínculo para el primero de la lista

Nótese que la dirección es absoluta. Esto se debe a que aún no hemos guardado el cuaderno. Lo mismo puede apreciarse apuntando al enlace

Después de guardar el cuaderno, la dirección se vuelve relativa

Ahora copiamos D:\Catalogo a un CD (D:\Catalogo incluye, obviamente, la carpeta Productos). De esta manera podremos entregar copias del catálogo a nuestros clientes.

Cuando nuestro cliente abra el cuaderno, los enlaces apuntarán a la ubicación del cuaderno (en nuestro ejemplo, E :\)

La dirección ha cambiado en relación a la ubicación del cuaderno.

Si queremos forzar dirección del hipervínculo podemos hacer lo siguiente:

Abrimos el menú Propiedades del cuaderno
En Excel Clásico: Archivo-Propiedades-Resumen;
En Excel 2007: botón del Office-Preparar-Propiedades-Propiedades Avanzadas
En la ventanilla Base del Hipervínculo ponemos la referencia deseada, de manera.

Rangos dinámicos y funciones volátiles

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.

Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).

Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula

Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)

Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)

Ahora podemos escribir nuestra fórmula de esta manera:

Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.

La receta clásica es usar la función DESREF como en este ejemplo

DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.

Para crear listas desplegables dependientes usamos la función INDIRECTO.

Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?

No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.

Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.

Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.

Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:

1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.

2 – Usar una combinación de INDICE y CONTARA:

=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))

Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.