Como Cambiar gráfico entre mensual y trimestral

Índice
  1. Guión
  2. Creando el gráfico
  3. Mover el gráfico a una hoja separada
  4. Crear la lista desplegable
  5. Crear rangos con nombres dinámicos
  6. Rangos con nombre utilizados en el gráfico
  7. Cambiar el formato de la serie de gráficos
  8. Crear un título de gráfico dinámico
  9. Conclusión

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

Guión

Aquí está nuestro escenario. Como se muestra en la captura de pantalla siguiente, hay datos mensuales y trimestrales.

Escenario Mensual Trimestral

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.

Cuadro final

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.
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í:

Gráfico de ejemplo: primera vista

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ú.

Gráfico de movimiento con clic derecho

Seleccione Nueva hoja y asigne un nombre a la hoja del gráfico. Elegí el valor predeterminado de Chart1. Haga clic en Aceptar .

Mover gráfico a hoja de gráfico

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á.

Insertar control de formulario desde el menú Desarrollador

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...

Control de formato desplegable con clic derecho

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.

Ingrese valores en el cuadro de control de formato

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 .

Fórmula de cinta - Administrador de nombres

En el cuadro de diálogo Administrador de nombres, haga clic en Nuevo .

Administrador de nombres - 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)

Rango con nombre con Elegir fórmula dentro

Haga clic en Aceptar para cerrar el cuadro Nuevo nombre.

Repita los mismos pasos para crear un rango con nombre para las etiquetas.

Rango con nombre para etiquetas de gráficos

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.

Rangos con nombre creados en el administrador de nombres

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 derecho en el gráfico y luego seleccione los datos.

Haga clic en el botón Editar en la sección Entradas de leyenda.

Editar datos del gráfico

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).

Utilice el rango con nombre de ChartSource como fuente para el gráfico

Haga clic en Aceptar para cerrar la ventana Editar serie.

Repita nuevamente para las etiquetas del gráfico.

Aplicar rango con nombre de ChartLables

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.

Tabla de conmutación

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.

Desmarque las Propiedades, siga los puntos de datos del gráfico para el libro de trabajo actual.

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.

Incluir título dinámico para el gráfico

Conclusión

Ahí lo tenemos; una hoja de gráfico donde el usuario puede seleccionar entre una opción trimestral o mensual.

Cuadro final

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

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Este sitio web utiliza cookies para mejorar la experiencia del usuario y garantizar un funcionamiento eficiente. Al utilizar nuestro sitio web, aceptas todas las cookies de acuerdo con nuestra política de cookies. Leer mas...