Cómo agregar una barra de desplazamiento a un gráfico en Excel

En ocasiones, los datos subyacentes de un gráfico no se ajustan a la ventana del mismo. Cuando esto sucede, Excel intenta acomodar cortando algunos valores. Puedes hacer el gráfico más grande, pero eso no siempre es factible, especialmente en una hoja de tablero. Además, es posible que tengas más puntos que incluso una ventana grande puede mostrar. En estos casos, puedes agregar una barra de desplazamiento que permita al usuario desplazarse por los datos. Aunque he visto algunas técnicas complejas para implementar esta solución, mi técnica requiere solo 10 pasos rápidos y sencillos.

Nota: En el paso 10, inserto una columna para centrar los componentes del tablero. Al hacerlo, cambian muchas de las celdas y rangos referenciados a lo largo de los nueve primeros pasos. Si estás trabajando con la demo descargable, no dejes que esas diferencias te confundan, todas estarán desplazadas por una columna.

1: Determina tus necesidades

Los datos en la hoja titulada "Gráfico desplazable" tienen un registro para cada mes, comenzando desde el 31 de enero de 2010 y terminando el 30 de septiembre de 2012. Desafortunadamente, hay más puntos de los que el gráfico puede mostrar (Figura A).

Figura A

Este gráfico de línea simple compensa la gran cantidad de puntos inhibiendo la visualización de algunos de ellos.

Para generar el gráfico, selecciona los datos, haz clic en la pestaña Insertar, haz clic en la opción Línea en el grupo Gráficos y selecciona la primera opción (la más simple). En Excel 2003, elige Gráfico desde el menú Insertar. En realidad, no utilizaremos este gráfico, pero es recomendable crear un gráfico predeterminado para ver qué tan bien se ajustará la ventana del gráfico a los datos.

2: Agrega el control de la barra de desplazamiento

Una vez que sepas el tamaño de la ventana del gráfico, incrusta una barra de desplazamiento en la hoja de tablero. Podrías trabajar en la misma hoja que los datos, pero como estamos simulando un entorno de tablero, trabajaremos en una nueva hoja llamada Tablero. Para generar el gráfico, haz clic en la pestaña Desarrollador y elige Formulario de barra de desplazamiento en la opción Insertar controles del grupo Controles. En Excel 2003, este control se encuentra en la barra de herramientas Formularios. Arrastra la barra de desplazamiento para ajustar su tamaño. La posición no es importante en este momento, puedes moverla más adelante.

Si la pestaña Desarrollador no está visible, haz clic en la ficha Archivo (el botón de Office en Excel 2007). Luego, elige Opciones en el panel izquierdo o Opciones de Excel, respectivamente. En el panel izquierdo, elige Personalizar cinta de opciones. Selecciona Desarrollador en la lista de fichas principales a la derecha y haz clic en Aceptar. En Excel 2007, selecciona Mostrar ficha Desarrollador en la cinta y haz clic en Aceptar.

Vale la pena invertir en minería de datos

3: Configura las propiedades de la barra de desplazamiento

Las propiedades determinan el comportamiento de la barra de desplazamiento, así que haz clic en Propiedades en el grupo Controles y configúralas como se muestra en Figura B. Haz clic derecho en el control de 2003 para acceder a sus propiedades. Luego, haz clic en Aceptar para volver a la hoja. Estas configuraciones determinan cómo se comporta el control:

• Valor actual: 1 — Almacena el valor actual del control, 1 es un valor de anclaje.
• Valor mínimo: 1 — Primer valor del control.
• Valor máximo: 33 — Inicialmente, utiliza el número de registros en tu rango de datos.
• Cambio incremental: 1 — Este es el número de puntos de datos que la barra de desplazamiento se desplazará con cada clic (aumenta o disminuye el valor actual).
• Cambio de página: Representa el número de puntos de datos que se muestran. Como estamos mostrando valores mensuales, 12 parece adecuado.
• Vínculo de celda: B1 — Utiliza cualquier celda vacía que desees. Esta celda almacenará el valor actual del control.

Figura B

Personaliza estas configuraciones para adaptarlas a tus datos.

Para ver cómo funciona la barra de desplazamiento, mueve el control o haz clic en las flechas y observa cómo cambia el valor en la celda vinculada (B1).

4: Genera los datos del gráfico

La forma más sencilla de mostrar un subconjunto de datos en un gráfico es crear un subconjunto de datos. Debido a que estamos viendo valores mensuales, mostrar 12 puntos a la vez parece razonable. Utilizaremos una matriz de funciones INDICE() para crear el subconjunto. Primero, copia las etiquetas de encabezado del rango de datos a la hoja de Tablero. Luego, en la esquina superior izquierda de la tabla de subconjunto (Tablero!A4 en nuestro ejemplo), ingresa la siguiente función:

=INDICE('Gráfico desplazable'!A2:A34,$B$1)

El primer argumento hace referencia a la primera columna de datos (no hagas referencia a la celda de encabezado). El segundo argumento hace referencia a la celda vinculada de la barra de desplazamiento en la hoja de tablero.

5: Copia las fórmulas

Después de ingresar la fórmula de anclaje, cópiala a la columna B. Luego, copia las fórmulas en las celdas A4:B4 a las celdas A5:B15. Figura C muestra los resultados: una matriz que comprende las primeras 12 filas de datos en el rango de datos original.

Figura C

Con el valor de la barra de desplazamiento establecido en 1, la matriz muestra las primeras 12 filas de datos.

6: Detecta un problema

En este punto, puedes usar el control de la barra de desplazamiento para actualizar los valores de la matriz y, posteriormente, el gráfico. Por ejemplo, haz clic en la flecha derecha cinco veces y la matriz muestra las filas 6 a 18 en lugar de las filas 2 a 13. A medida que haces clic en las flechas del control, el valor en la celda vinculada (B2) cambia, actualizando el segundo argumento en las funciones INDICE().

Fase de análisis: Entendiendo lo que el cliente quiere

La Figura D muestra lo que sucede cuando mueves la barra de desplazamiento hacia la extrema derecha: obtienes varias filas de datos no válidos. No puedes permitir que la barra de desplazamiento muestre el último registro como la primera fila de datos en la matriz. En su lugar, la última vista disponible debería mostrar los últimos 12 registros (filas 23 a 34).

Figura D

La barra de desplazamiento va demasiado lejos.

7: Ajusta una propiedad

Afortunadamente, la solución es sencilla. Haz clic derecho en la barra de desplazamiento y elige Formato de control en el submenú resultante. Cambia la propiedad Valor máximo a 22 y haz clic en Aceptar. Ahora, la última vista posible muestra las últimas 12 filas de datos, como se muestra en Figura E. Si añades o eliminas registros, debes actualizar esta propiedad aumentando o reduciendo la configuración en la cantidad de registros que agregues o elimines.

Figura E

La matriz siempre debe mostrar 12 registros válidos.

8: Grafica la matriz

En lugar de graficar el rango de datos original, puedes graficar la matriz en la hoja de Tablero. Para hacerlo, selecciona el rango de la matriz - A3:B15 - y utiliza las instrucciones del paso 1 para incrustar un gráfico de línea en la hoja de Tablero, como se muestra en Figura F. Recuerda, la matriz no tiene que estar junto al gráfico. Lo estoy haciendo así para simplificar el ejemplo, pero puedes mostrar ambos elementos o solo el gráfico en tu tablero.

Figura F

El gráfico muestra la matriz, no el rango de datos original.

Con las piezas en su lugar, utiliza la barra de desplazamiento para actualizar el gráfico. Para hacerlo, haz clic en las flechas o arrastra el control. Como se muestra en Figura G, tanto la matriz como el gráfico se ajustan adecuadamente.

Figura G

El gráfico muestra los valores de la matriz, que cambian a medida que manipulas la barra de desplazamiento.

10: Agrega un toque de pulido

La técnica ahora está completa, pero querrás posicionar y dar formato al gráfico y a la barra de desplazamiento de manera ventajosa. Es posible que hayas notado que el eje horizontal no muestra las fechas reales de la columna A. Si eso es importante, soluciónalo de la siguiente manera:

1. Haz clic derecho en el eje horizontal y elige Formato de eje.
2. Bajo Tipo de eje (en el panel Opciones de eje, que debería ser el valor predeterminado), haz clic en la opción Eje de texto.
3. Haz clic en Aceptar.

También puedes considerar aumentar el ancho del control para que coincida con el gráfico. También inserté una nueva columna a la izquierda de la matriz para centrar los componentes un poco. (Como mencioné anteriormente, esto significa que las celdas y rangos referenciados en todo este artículo no coincidirán con la demo descargable). Además, es posible que desees inhibir las líneas de cuadrícula en la hoja haciendo clic en la pestaña Vista y desmarcando las opciones Líneas de cuadrícula, Barra de fórmulas y Encabezados en el grupo Mostrar.

Los 12 errores más comunes al trabajar con el objeto Recordset en Access

Finalmente, protege el contenido de la hoja de la siguiente manera:

1. Haz clic derecho en B1, la celda vinculada que almacena el valor actual de la barra de desplazamiento, y elige Formato de celdas.
2. En la pestaña Protección, desmarca Bloqueado.
3. Desde la lista desplegable Formato en el grupo Celdas (en la pestaña Inicio), elige Proteger hoja.
4. Selecciona solo la opción Seleccionar celdas desbloqueadas y haz clic en Aceptar. (Ingresa una contraseña si lo deseas).

Después de proteger la hoja, como se muestra en Figura H, lo único que se puede seleccionar es la barra de desplazamiento.

Figura H

Embellece la hoja para que se parezca más a un tablero y no olvides protegerla.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Big Data, allí encontraras muchos artículos similares a Cómo agregar una barra de desplazamiento a un gráfico en Excel , tenemos lo ultimo en tecnología 2023.