Como Cambiar gráfico entre mensual y trimestral
En esta publicación, quiero responder una pregunta hecha por un lector. La pregunta era de Nick:
“Tengo un gráfico en una hoja de gráfico separada, donde me gustaría agregar un objeto de desarrollador (botón, cuadro de opción o casilla de verificación) para controlar si los datos se muestran trimestralmente o mensualmente. ¿Alguna idea de cómo hacerlo?"
En esta publicación, vamos a responder esa misma pregunta.
Tabla de contenido
- Guión
- Creando el gráfico
- Mover el gráfico a una hoja separada
- Crear la lista desplegable
- Crear rangos con nombres dinámicos
- Rangos con nombre utilizados en el gráfico
- Cambiar el formato de la serie de gráficos
- Crear un título de gráfico dinámico
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0027 Cambiar gráfico entre mensual y trimestral.zip
Tener acceso
Aquí está nuestro escenario. Como se muestra en la captura de pantalla siguiente, hay datos mensuales y trimestrales.
A partir de esto, queremos crear un gráfico en una hoja de gráfico, en el que podamos cambiar fácilmente entre mostrar valores mensuales o trimestrales.
Creando el gráfico
Comencemos creando un gráfico y formateándolo.
Seleccione las celdas que contienen los datos mensuales y luego haga clic en Insertar gráfico de columnas agrupadas.
Formatee el gráfico como desee. En este ejemplo, tengo:
- Aumentó el ancho del espacio al 50%.
- Cambió el color de relleno a gris oscuro.
- Se eliminaron las líneas de cuadrícula principales.
- Establezca el mínimo del eje vertical para que siempre comience en cero
- Se eliminó el eje vertical.
- Etiquetas de datos agregadas
- Coloqué las etiquetas de datos para que estén dentro del extremo.
- Se formatearon las etiquetas de datos para que fueran blancas, en negrita, tamaño 11.
El gráfico formateado ahora se ve así:
Mover el gráfico a una hoja separada
Los gráficos pueden estar en el anverso de una hoja de trabajo o en una hoja separada. Cambiar entre ellos es bastante sencillo.
Haga clic derecho en el gráfico, seleccione Mover gráfico… en el menú.
Seleccione Nueva hoja y asigne un nombre a la hoja del gráfico. Elegí el valor predeterminado de Chart1. Haga clic en Aceptar .
El gráfico ahora pasa a una hoja separada por sí solo.
Crear la lista desplegable
A continuación, creemos una lista desplegable, que usaremos para cambiar entre las opciones mensuales y trimestrales.
Ingrese los valores Mensual y Trimestral en una lista en la hoja de trabajo (he usado las celdas G3 G4 en la Hoja 1)
Seleccione la hoja del gráfico y luego haga clic en Desarrollador Insertar cuadro combinado (control de formulario) . Si bien he usado un cuadro combinado, cualquier objeto con un enlace de celda funcionará.
El ratón cambia a una pequeña cruz. Haga clic y arrastre el mouse sobre el gráfico en la posición donde desea colocar el cuadro desplegable. Podemos moverlo fácilmente y cambiar su tamaño más tarde si no está del todo bien.
Haga clic derecho en el menú desplegable, seleccione Control de formato...
Para el rango de entrada, use la lista que creamos hace unos momentos.
Para el enlace de celda, seleccione cualquier celda de la hoja de trabajo. He usado la celda H3 en la Hoja1.
La celda elegida como enlace de celda muestra la posición seleccionada dentro del menú desplegable. Si se selecciona Mensual en el menú desplegable, la celda H3 muestra 1, si se selecciona Trimestral, se mostrará 2.
El cuadro desplegable ahora puede cambiar entre las opciones de la lista; No cambiará el gráfico todavía, ya que actualmente no está vinculado.
Crear rangos con nombres dinámicos
La clave para la funcionalidad de cambio es utilizar un rango con nombre como fuente del gráfico. Entonces, creemos algunos rangos con nombre.
Haga clic en Administrador de nombres de fórmulas .
En el cuadro de diálogo Administrador de nombres, haga clic en Nuevo .
Asigne un nombre al rango nombrado (estoy usando ChartSource en este ejemplo).
En el cuadro Se refiere a, ingresaremos una fórmula. Hay muchas fórmulas que podemos utilizar para crear un rango dinámico . Para este escenario, usaremos ELEGIR .
Los argumentos de la función ELEGIR son:
= ELEGIR (núm_índice, Valor1, Valor2, [Valor3]...)
- Index_num = Especifica qué argumento de valor seleccionar
- Valor1 = El valor a devolver si Index_num es 1
- Valor2 = El valor a devolver si Index_num es 2
- Valor3 = El valor a devolver si Index_num es 3… etc.
ELEGIR puede manejar hasta 254 valores; en nuestro ejemplo, solo usaremos 2.
- Valor1 serán los valores mensuales.
- Valor2 serán los valores trimestrales.
Index_num hará referencia al enlace de la celda del cuadro desplegable. Por lo tanto, cuando cambia el menú desplegable, ELEGIR devuelve los datos mensuales o trimestrales.
=ELEGIR(Hoja1!$H$3,Hoja1!$B$3:$B$14,Hoja1!$E$3:$E$6)
Haga clic en Aceptar para cerrar el cuadro Nuevo nombre.
Repita los mismos pasos para crear un rango con nombre para las etiquetas.
Ahora deberíamos tener dos rangos con nombre configurados. Uno para los datos del gráfico y el otro para las etiquetas del gráfico.
Haga clic en Cerrar para cerrar el cuadro de diálogo Administrador de nombres.
Rangos con nombre utilizados en el gráfico
A continuación, usemos los rangos con nombre en el gráfico. Haga clic derecho en el gráfico y haga clic en Seleccionar datos… en el menú.
Haga clic en el botón Editar en la sección Entradas de leyenda.
Se abre el cuadro de diálogo Editar serie. En el campo de valores de la serie, reemplace el rango con el rango nombrado. Necesitamos conservar el nombre de la hoja al comienzo del rango nombrado. Si el nombre de la hoja tiene un espacio, debe tener comillas simples alrededor del nombre, seguidas de ! (por ejemplo, = 'Mi hoja'! ChartSource). Sin embargo, si el nombre de la hoja no tiene espacios, entonces no necesita comillas simples (por ejemplo, = Hoja1! ChartSource, como se puede ver en nuestro ejemplo a continuación).
Haga clic en Aceptar para cerrar la ventana Editar serie.
Repita nuevamente para las etiquetas del gráfico.
Haga clic en Aceptar para cerrar el cuadro de diálogo Seleccionar fuente de datos.
Ahora podemos probar el menú desplegable. Debería ser posible cambiar entre las opciones mensual y trimestral.
Si el formato de su gráfico cambia al cambiar entre gráficos (como se muestra arriba), no se preocupe, podemos solucionarlo.
Cambiar el formato de la serie de gráficos
Comience con el gráfico formateado seleccionado.
Haga clic en Opciones de archivo . En la ventana Opciones de Excel, seleccione Avanzado , desplácese hacia abajo hasta la sección del gráfico y desmarque la opción Propiedades siguen el punto de datos del gráfico para el libro actual . Haga clic en Aceptar para cerrar la ventana de opciones.
Ahora, intente nuevamente con el menú desplegable del gráfico; el formato debe permanecer.
Crear un título de gráfico dinámico
Actualmente, el título del gráfico solo dice Valor. Hagamos eso también más dinámico. En la celda I3 de la Hoja1, ingrese la siguiente fórmula:
=ÍNDICE(G3:G4,H3) "Existencias"
Esto mostrará Stock mensual o Stock trimestral según la selección desplegable.
Para agregar esto al título del gráfico, haga clic en el encabezado, ingrese el símbolo = en la barra de fórmulas y luego haga clic en la celda que contiene el título que creamos.
Conclusión
Ahí lo tenemos; una hoja de gráfico donde el usuario puede seleccionar entre una opción trimestral o mensual.
Hay muchas técnicas de gráficos que utilizan la metodología de rango con nombre . Si bien hemos utilizado ELEGIR para cambiar la fuente del gráfico, ÍNDICE, DESPLAZAMIENTO y BUSCAR XL también son buenas opciones alternativas para aplicar esta metodología.
Deja una respuesta