Gráfico de columnas con barra de desplazamiento

Hay en ocasiones en que el número de datos que queremos graficar es tan elevado que, al generar nuestro gráfico de columnas, la información es difícil de visualizar, perdiendo eficacia el gráfico.. 

Esta situación podemos subsanarla añadiendo al gráfico una barra de desplazamiento que nos permita ver todos los datos por intervalos.

Veamos como conseguirlo paso a paso, y recuerda: si quieres recibir información sobre nuevas publicaciones no olvides suscribirte

Partimos de la tabla de habitantes por países de la UE en el año 2020 que puedes descargarte aquí.

Crear el gráfico.

Al tratarse de una tabla, nos situamos en cualquiera de las celdas de la tabla y desde el menú Insertar seleccionamos gráfico de columnas. Automáticamente ya toma todo el rango de celdas de la tabla. Otra de las ventajas que tiene el trabajar con una tabla tabla es que cualquier modificación se ve reflejada automáticamente en el gráfico.

Necesitamos la función DESREF()

En lugar de mostrar los datos de los 28 países, vamos a tratar que se muestren los datos en grupos de seis países, para que la información se presente de forma más clara. Para ello tenemos que utilizar la función DESREF().

Esta función de búsqueda consta de 5 argumentos:

• Ref. Indica la celda a partir de la cual queremos iniciar la búsqueda.
• Fila. Indica el número de filas que queremos desplazarnos hacia arriba (signo negativo) o hacia abajo (signo positivo).
• Columna. Indica el número de columnas que queremos desplazarnos hacia la izquierda (signo negativo) o hacia la derecha (signo positivo).
• [Alto]. Argumento optativo, con el indicamos una vez estamos en la celda marcada (por las coordenadas anteriores: Ref., Fila y Columna), cuantas filas de registros queremos obtener.
• [Ancho]. Argumento optativo, con el que indicamos el número de columnas que queremos mostrar.

A efectos didácticos

Vamos a ver con un par de ejemplos como podrimos usar la función DESREF() y el resultado que obtenemos. Nos situamos en una celda en blanco donde escribimos la función DESREF:

• Como Ref. Marcamos el encabezado de la columna Países
• Fila. Indicamos 1, es decir queremos que a partir de esa posición bajar una fila.
• Columna. Indicamos 0, es decir no queremos cambiar de columna.
• [Alto]. Indicamos 6, queremos que nos muestre los datos de 6 filas.
• [Ancho]. Indicamos 1, queremos que nos muestre los datos de una única columna.

Repetimos la misma operación pero ahora marcando como referencias la celda donde está el encabezado Habitantes.

Si sois usuarios de Microsoft 365, obtendréis este resultado:

Nos muestra un listado de 6 países y sus habitantes, empezando en la primera fila siguiente al encabezado. Si no los sois, no pasa nada a efectos de construir nuestro gráfico.

Fijaros, que si en Fila indicamos 10 en lugar de 1, el resultado es:

En lugar de estar cambiando el numero de fila manualmente en cada una de las fórmulas, vamos a hacer que este dato este vinculado a una celda, con lo cual nuestras fórmulas DESREF quedarán del siguiente modo:

• Para los países: =DESREF($B$8;$F$6;0;6;1)
• Para el N.º de habitantes: =DESREF($C$8;$F$6;0;6;1)

¡Es importante que las referencia a celdas queden fijadas de modo absoluto, para después incorporarlas al gráfico.!

La barra de desplazamiento.

En este proceso de automatización del número de fila, que se muestra en la celda F6, vamos hacer que este cambie mediante una barra de desplazamiento. Para ello desde la pestaña Programador  o Desarrollador, según vuestra versión, hacemos clic sobre el botón Barra de desplazamiento, y lo "dibujamos" en nuestra hoja de cálculo (por ejemplo debajo del gráfico).

 

Una vez dibujado, hacemos clic con el botón derecho sobre la barra de desplazamiento y clicamos sobre Formato de control, donde establecemos los siguientes parámetros.

• Valor actual: 1
• Valor mínimo: 1
• Valor máximo: 23. Al tener 28 países y mostrarlos de 6 en 6 cunado nos situemos en la fila 23, nos mostrará los registros que van desde la 23 hasta la 28. Si ponemos un numero superior el gráfico aparecería sin datos, e inferior, no nos permitiría graficar todos los países.
• Incremento: 1. Cada vez que hagamos un clic en las flechas derecha o izquierda de la barra de desplazamiento, aumentará el numero de filas en uno.
• Cambio de página: 6. Cuando hagamos clic en el interior de la barra espaciadora el valor de la fila aumentar en 6 (numero de países que se muestran cada vez).
• Vincular con la celda: indicamos la celda donde queremos que se muestren esto datos, celda que es a donde hemos referenciado el argumento Fila de nuestras funciones DESREF. Y, por último, aceptamos.

De este modo cada vez que trabajemos con la barra de desplazamiento los datos mostrados por la función DESREF variarán. Pero ahora queremos que estos datos incluirlos directamente en nuestro gráfico, para que nos muestre los países de 6 en 6, es decir que el rango de datos del grafico muestre sólo el intervalo de 6 países.

Asignar nombre a las funciones.

El problema es que no podemos poner directamente estas fórmulas en el gráfico, para ello tenemos que asignarle un nombre a cada una de las funciones DESREF, y esto ya es independiente de que uses Microsoft 365 o no.

Para asignar nombres desde la pestaña Fórmulas, hacemos clic en el comando Asignar nombre, y cumplimentamos los datos de la ventana:

Haremos esta operación para las dos funciones DESREF: a una le llamamos países y en el apartado Se refiere a, copiamos la función que escribimos para dichos datos. Repetimos la operación para la función relativa al N.º de habitantes.

Incorporar las funciones al gráfico.

Ahora ya estamos en condiciones de llevar estas funciones a nuestro gráfico. Para ello, hacemos clic en nuestro grafico y desde la pestaña Diseño de gráfico, clicamos en el comando Seleccionar datos.

Seleccionamos la serie Habitantes (un clic) y clicamos en modificar

En el apartados valores de la serie, borramos parte de los datos, debemos conservar el nombre de la hoja y el signo de exclamación y, seguido, escribimos el nombre que le dimos la función DESREF relativa al número de habitantes. Hecho esto aceptamos.

A continuación en el apartado etiquetas, presionamos el botón Editar.

Y el rango de rótulos como en el caso anterior, dejamos la identificación de la hoja, y escribimos el nombre asignada a la función DESREF relativa a los países. Y aceptamos

De este modo nuestro gráfico tan solo nos muestra un bloque de 6 países, y con la barra de desplazamiento vamos visualizando nuevos países,

Formato del gráfico. 

El ancho de columna.

Vamos a retocar un poco nuestro gráfico, ahora que solo se muestran 6 países vamos a hacer un poco mas anchas las columnas.

Hacemos un clic, con el botón derecho de nuestro ratón, sobre cualquiera de las columnas y seleccionamos la opción Dar formato a serie de datos, y en el ancho de rango escribimos 100%

Valor del eje.

Si sois un poco observadores, os habréis dado cuenta que al desplazarnos con nuestra barra el el de valores va cambiando conforme cambian los países lo cual da una sensación de no continuidad, pues el valor del eje cambia en función de los valores que se grafican en cada momento.

Esta situación podríamos solucionarla de la siguiente manera. Hacemos clic sobre el eje de valores con el botón derecho del ratón, y seleccionamos la opción Dar formato al eje y en el apartado máximo escribimos el numero de habitantes más alto. Pero al hacerlo de este modo, lo dejamos fijo y ante una eventual modificación de datos que supusiese un valor mayor, este no se vería recogido.

Añadimos un nuevo dato al gráfico: el valor máximo

Para automatizarlo hagamos las siguientes operaciones:

• En  una celda en blanco, igual que hicimos con las funciones DESREF, escribamos la función MAX(), seleccionando la columna de N.º de habitantes: =MAX(Tabla2[Habitantes])
• Demos un nombre hasta formula: =MAX(Tabla1[Habitantes])

• Hacemos clic sobre el gráfico y desde la pestaña Diseño de gráfico, clicamos en el comando Seleccionar datos. 
• Clicamos en el botón Agregar, y cumplimentamos la ventana del siguiente modo:

• Y aceptamos dos veces, hasta visualizar el gráfico:

Nos muestra una única columna, que indica el valor máximo. Ahora lo que procede es esconderla:

• Hacemos un clic, con el botón derecho de nuestro ratón, sobre cualquiera de las columnas y seleccionamos la opción Dar formato a serie de datos, y en el campo superposición de series indicamos 100%, con lo cual la columna relativa al máximo se superpone, ocultando la columna azul. 

• Hacemos un clic sobre la columna de color naranja, y en la ventana Formato de serie marcamos sin relleno y sin línea. De este modo la columna Máximo se hace invisible y mantiene fija la escala del eje.