Crear un Gráfico de Variación o Comparación en EXCEL

Paso #1: Hacer un Gráfico sencillo de Columnas (Vídeo: 00:56)

Seleccionar la tabla donde está la información, Ficha: Insertar, Gráfico de Columna.

Paso #2: Aprendiendo la función DESREF( ). (Vídeo: 02:17)

La función DESREF( ) devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver.

Vamos a ver primero como es la sintaxis de la función DESREF tiene los siguientes argumentos:

DESREF(ref, filas, columnas, [alto], [ancho])

◾ Referencia (Obligatorio): La referencia en la que desea basar el desplazamiento. Referencia debe referirse a una celda o rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!

◾ Filas (Obligatorio):  Es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo que la referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima).

◾ Columnas (Obligatorio):  Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).

◾ Alto (Opcional):  Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.

◾ Ancho  (Opcional):  Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

Ok, ahora bien, en este ejemplo necesitamos sacar el PROMEDIO DE VENTAS, iniciando desde un número de mes y una cantidad de meses, por lo que primero necesito obtener el rango de las celdas de la columna VENTAS a los que le deseo sacar el promedio, entonces uso la función DESREF( ) para ello:

Fíjate que lo que arroja esa función son los datos de ese rango que dá como resultado (Recuerda que si no tienes la versión 365, te arrojará solo el primer valor, no te preocupes, igual vas a poder realizar el ejercicio)

Ahora si deseamos sacar el PROMEDIO sería simplemente precederle a la fórmula la función PROMEDIO( ):

Paso #3: Aplicando la Función DESREF( ) al gráfico. (Vídeo: 09:35)

Vamos hacer una tabla auxiliar solo para poder ver cómo se comportan las formulas que haremos, que luego llevaremos a nuestro gráfico directamente:

Para los datos de VENTAS, la fórmula sería:Para los datos de MESES, la fórmula sería idéntica a la de VENTAS, lo único que cambia es la referencia que sería el encabezado de la columna de MES:

Paso #4: Asignar nombre a las fórmulas y asignárselos al gráfico. (Vídeo: 16:14)

Luego vamos a la Ficha: [Fórmulas], Icono: [Administrador de Nombres], luego en el Botón: “Nuevo”, y luego colocamos el nombre de nuestra fórmula, y en se refiere a pegamos la fórmula:

Y el mismo procedimiento lo repetimos para la fórmula que nos arroja los MESES.

Ahora vamos asignar estos nombres a los rangos de nuestro gráfico: le damos click derecho al gráfico, y luego “Seleccionar Datos”.

Seleccionamos la serie “VENTAS” y le damos a [Modificar], y procedemos a colocar en Valores de la serie el nombre de la fórmula que creamos para los datos de Ventas, antecediéndole el nombre de la hoja,

Y el mismo procedimiento hacemos con las etiquetas del eje horizontal.

Ya está casi listo nuestro gráfico, solo falta agregar la barra de desplazamiento.

Paso #5: Agregar la Barra de Desplazamiento. (Vídeo: 21:35)

Si no tienes la ficha Programador ( o Desarrollador) activada, dale click Derecho en cualquier de las fichas que tengas, y luego Personalizar Cinta de Opciones, y luego en la lista de la derecha activa el check en “Programador” (o “Desarrollador” dependiendo de la versión). Luego en la Ficha: [Programador], icono: [Insertar], Icono “Barra de Desplazamiento” y luego colocas el tamaño de la barra que desees:

Luego lo editamos, dándole click derecho y luego “Formato de Control”.

Lo más importante es colocar en “Vincular con la celda”: la referencia donde está el # de mes de inicio.

Y listo ya tenemos nuestro gráfico con barra de desplazamiento, donde irá desplazándose a través de los diferentes meses.

Paso #6: Mantener FIJA la escala del Eje Vertical en todo el gráfico. (Vídeo: 23:59)

Primero realiza una formula en cualquier parte de la hoja para obtener el máximo de todos los valores de la columna VENTAS:

Luego debes agregar una serie nueva al gráfico, le damos click derecho al gráfico, y luego “Seleccionar Datos”.

Y luego en [Agregar], asignamos un nombre a la serie: “MAX”, y en valores de la serie vamos hacer referencia a donde hicimos previamente la función MAX 😊:

Luego a esta nueva serie vamos a quitarle el relleno, porque lo único que necesitamos de ella es que marque siempre el mismo valor máximo vertical por lo que no necesitamos realmente verla en el gráfico, así que la seleccionamos y le quitamos el relleno:

Y listo ya tenemos nuestro Gráfico con Barra de Desplazamiento, solo basta con movernos por las flechitas.