Gráfico tipo velocímetro en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Creamos un gráfico tipo velocímetro en Excel (basado en un medidor de velocidad) donde la posición de la aguja nos muestra el valor deseado a representar.

Gráfico tipo velocímetro en Excel

Deseamos realizar un gráfico tipo velocímetro que nos permita representar las ventas de los delegados, respecto a los objetivos previstos.

  • ORIGEN DE DATOS

La hoja "Velocímetro" mostrará los valores de los delegados y el gráfico que los represente.
La hoja "Datos" mostrará los valores necesarios para representar el gráfico.

  1. HOJA: VELOCÍMETRO. Ir a: Barra de etiquetas de hojas / Hoja1 / Botón derecho del ratón o tecla contextual / Cambiar nombre Escribir: Velocímetro; O también, Ir a: Pestaña Inicio / Grupo: Celdas / Formato / Cambiar el nombre de la hoja / Escribir: Velocímetro. Nombre para identificar el contenido de la hoja (es importante ya que en las fórmulas se incluye el nombre de la hoja).

  2. Ir a la hoja Velocímetro.

  3. DELEGADO. Ir a: celda A2 Escribir: Juan. Como el nombre del delegado.

  4. OBJETIVO. Ir a: celda B2 Escribir: 30.000. Como el importe objetivo aplicado al delegado.

  5. REAL. Ir a: celda C2 / Escribir: 52.000. Como el importe real obtenido por el delegado.

  6. PORCENTAJE. Ir a: celda D2 Escribir:

    =SI(Y(C2>0;B2<0); 1; SI(Y(C2<0;B2>0); 0; SI((C2/(B2*2))>1,2; 1,2; C2/(B2*2)))).

    Como el porcentaje del real y el objetivo.

     ABCD
    1DelegadoObjetivoReal%
    2Juan25.000,00 €3200064%
  7. HOJA: DATOS. Ir a: Hoja2 / Barra de etiquetas de hojas / Botón derecho del ratón o tecla contextual / Cambiar nombre Escribir: Datos; O también Ir a: Pestaña Inicio / Grupo: Celdas / Formato / Cambiar el nombre de la hoja / Escribir: Datos. Nombre para identificar el contenido de la hoja (es importante ya que en las fórmulas se incluye el nombre de la hoja).

  8. Ir a Hoja: Datos / Clic sobre celda A2 / Escribir:

    =Velocímetro!A2&" – "&TEXTO(Velocímetro!C2;"#.##,00 €")

    Fórmula que mostrará el nombre del delegado y el importe real con formato de moneda, que aplicaremos al gráfico.

Hoja Excel con función en celda A2
  • ESCALA: DATOS REFERENCIA

  1. Clic sobre celda C1 / Escribir: Escala. Como título para los datos que incorpora.

  2. Clic sobre celda E1 / Escribir: Aguja. Como título para los datos que incorpora.

  3. Clic sobre celda C2 / Escribir: 2. Como primer valor de la escala del velocímetro (área de color rojo).

  4. Clic sobre celda C3 / Escribir: 2. Como primer valor de la escala del velocímetro (área de color amarillo).

  5. Clic sobre celda C4 / Escribir: 2. Como primer valor de la escala del velocímetro (área de color verde).

  6. Clic sobre celda C5 / Escribir: 6. Como primer valor de la escala del velocímetro (área sin relleno).

Rango C2:C5 con datos de referencia
  • AGUJA: DATOS REFERENCIA

Insertamos las fórmulas que nos permitan aplicar el giro de la aguja, según el valor obtenido.

  1. Clic sobre celda E3 / Escribir: 0.

  2. Clic sobre celda E4 / Escribir:

    =COS(Velocímetro!D2*PI())/F2.
  3. Clic sobre celda F3 / Escribir: 0.

  4. Clic sobre celda F4 / Escribir:

    =SENO(Velocímetro!D2*PI())/F2.
  5. Clic sobre celda F2 / Escribir: 1,6. El valor permite representar la longitud de la aguja.

Datos para aguja y longitud de línea
  • INSERTAR GRÁFICO: VELOCÍMETRO

Insertamos un gráfico de anillo para la escala y otro de dispersión para la aguja, que nos muestre el valor real respecto a los objetivos.

  1. Ir a: Hoja "Velocímetro".

  2. Ir a: Cinta de opciones / Pestaña: Insertar / Grupo: Gráficos / Dispersión / Dispersión con líneas rectas. Insertará el área del gráfico vacío.

  3. SELECCIONAR DATOS. Clic sobre el área del gráfico. Lo seleccionamos para asignarle el origen de los datos.

  4. Ir a: Cinta de opciones / Pestaña Diseño (Herramientas de gráficos) / Grupo: Datos / Seleccionar datos. Mostrará el cuadro de diálogo para seleccionar el origen de datos.

    Ventana de origen de datos Excel
  5. SERIE ESCALA. Ir a: Entradas de leyenda (Series) / Pulsar el botón Agregar. Muestra el cuadro de diálogo Modificar serie, para añadir la primera serie.

    Ventana modificar serie
  6. Ir a: Nombre de la serie / Escribir: =Datos!$C$1. Celda que contiene el encabezado de la serie.

  7. Ir a: Valor Y de la serie / Escribir: =Datos!$C$2:$C$5. Rango que contiene la referencias para la escala.

  8. Pulsar el botón Aceptar. Añade la serie y cierra el cuadro de diálogo.

  9. SERIEAGUJA. Ir a: Entradas de leyenda (Series) / Pulsar el botón Agregar. Muestra el cuadro de diálogo Modificar serie, para añadir la segunda serie.

    Ventana modificar serie
  10. Ir a: Nombre de la serie / Escribir: =Datos!$E$1. Celda que contiene el encabezado de la serie.

  11. Ir a: Valor X de la serie / Escribir: =Datos!$E$3:$E$4.

  12. Ir a: Valor Y de la serie / Escribir: =Datos!$F$3:$F$4.

  13. Pulsar el botón Aceptar. Añade la serie y cierra el cuadro de diálogo.

  14. Pulsar el botón Aceptar. Asigna el origen de datos al gráfico.

Área de gráfico con vista aguja y escala
  • CAMBIAR TIPO GRÁFICO

Modificamos el gráfico de dispersión de la ESCALA por un gráfico de tipo anillo.

  1. SELECCIONAR SERIE ESCALA. Ir a: Cinta de opciones / Pestaña Formato (Herramientas de gráficos) / Grupo: Selección actual / Elementos del gráfico /Seleccionar: Serie "Escala"; O también, Clic sobre ella en el área del gráfico.

  2. GRÁFICO DE ANILLOS. Ir a: Cinta de opciones / Pestaña Diseño (Herramientas de gráficos) / Grupo: Tipo / Cambiar tipo de gráfico. Mostrará el cuadro de diálogo de cambiar tipo de gráfico.

    Ventana cambiar tipo de gráfico
  3. Ir a: Cuadro combinado / Clic sobre: Combinación personalizada. Tipo de gráfico deseado para representar la escala del velocímetro en el gráfico.

  4. Ir a: Nombre de la serie Escala / Seleccionar: Anillo.

  5. Pulsar el botón Aceptar. Cambia el gráfico y cierra la ventana.

Área de gráfico con tipo anillo
  • PROPIEDADES ESCALA

EJE VERTICAL. Adecuamos los valores del eje vertical y horizontal para que la aguja se sitúe en el centro de la escala.

  1. SELECCIONAR EJE. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Selección actual / Seleccionar: Eje Vertical (Valor). Elemento deseado para aplicar formato.

  2. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Formato de forma; O también, botón derecho del ratón o tecla contextual / Dar formato al eje. Mostrará el panel de dar formato al eje.

    Panel dar formato al eje, opciones de eje
  3. Ir a: Categoría / Opciones del eje.

  4. Ir a: Límites: Mínima / Escribir: -1. Para sobreponer los dos gráficos.

  5. Ir a: Límites: Máxima / Escribir: 1. Para sobreponer los dos gráficos.

  6. Ir a: El eje horizontal cruza / Clic sobre Valor del eje / Escribir: 0. Para sobreponer los dos gráficos.

  7. SELECCIONAR EJE. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Selección actual / Seleccionar: Eje Horizontal (Valor). Elemento deseado para aplicar formato.

  8. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Formato de forma; O también, botón derecho del ratón o tecla contextual / Dar formato al eje. Mostrará el panel de dar formato al eje.

    Panel dar formato al eje, opciones de eje
  9. Ir a: Categoría / Opciones del eje.

  10. Ir a: Límites: Mínima / Escribir: -1. Para sobreponer los dos gráficos.

  11. Ir a: Límites: Máxima / Escribir: 1. Para sobreponer los dos gráficos.

Área de gráfico con gráficos sobrepuestos
  • OCULTAR ELEMENTOS DEL GRÁFICO

  1. Clic sobre el área del gráfico. Mostrará los botones de opciones sobre el gráfico.

  2. Pulsar el botón Elementos de gráfico + / Desactivar la casilla: Leyenda. Ocultará el área de leyenda en el gráfico.

  3. Pulsar el botón Elementos de gráfico + / Desactivar la casilla: Líneas de la cuadrícula. Ocultará la Líneas de la cuadrícula en el gráfico.

  4. Pulsar el botón Elementos de gráfico + / Desactivar la casilla: Ejes. Ocultará los ejes en el gráfico.

    Área del gráfico con elementos ocultos
  • FORMATO DE LAS SERIES

  1. SELECCIONAR SERIE ESCALA. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Selección actual / Seleccionar: Serie Escala. Elemento deseado para aplicar formato.

  2. Ir a: Cinta de opciones / Pestaña: Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Formato de forma; O también, botón derecho del ratón o tecla contextual / Dar formato a serie de datos. Mostrará el panel de dar formato al eje.

    Panel de serie de datos
  3. Opciones de serie Ir a: Ángulo del primer sector / Aplicar el 270% de giro.

  4. Ir a: Sección de anillos / Aplicar el 0% sin intervalo.

  5. Ir a: Tamaño del agujero del anillo / Aplicar el 72%.

Gráfico adaptado a las opciones de serie
  • RELLENO DE FORMA

Aplicamos color de relleno para las áreas 1, 2, 3 y 4 de la serie "Escala".

  1. Ir a: Área del gráfico / Clic sobre la serie "Escala" / Clic sobre el área 1. Se mostraran los puntos de control en el área 1 de la escala.

  2. Ir a: Pestaña Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Relleno de forma. Aplicar color rojo o el deseado.

  3. Ir a: Área del gráfico / Clic sobre la serie "Escala" / Clic sobre el área 2. Se mostraran los puntos de control en el área 2 de la escala.

  4. Ir a: Pestaña Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Relleno de forma. Aplicar color amarillo o el deseado.

  5. Ir a: Área del gráfico / Clic sobre la serie "Escala" / Clic sobre el área 3. Se mostraran los puntos de control en el área 3 de la escala.

  6. Ir a: Pestaña Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Relleno de forma. Aplicar color verde o el deseado.

  7. Ir a: Área del gráfico / Clic sobre la serie "Escala" / Clic sobre el áreas 4. Se mostraran los puntos de control en el área 4 de la escala.

  8. Ir a: Pestaña Formato (Herramientas de gráficos) / Grupo: Estilos de forma / Relleno de forma / Sin relleno. Para ocultar visualmente el área.

Gráfico con opciones de relleno
  • ETIQUETAS DESCRIPCIÓN

Insertamos cuadros de texto vinculados que nos muestre información en el gráfico.

  1. DELEGADO/REAL. Ir a: Pestaña Insertar / Grupo: Texto Cuadro de texto.

  2. Clic sobre el gráfico / Ir a: Barra de fórmulas / Clic en su interior / Escribir: =Datos!$A$2. Mostrará el nombre del delegado junto al importe real.

  3. POSICIÓN. Clic sobre el cuadro de texto y mantener pulsado / Arrastrar hasta situar sobre la parte inferior de la escala.

  4. OBJETIVOS. Ir a: Pestaña Insertar / Grupo: Texto / Cuadro de texto.

  5. Clic sobre el gráfico / Ir a: Barra de fórmulas / Clic en su interior / Escribir: =Velocímetro!$B$2. Mostrará el importe de los objetivos.

  6. POSICIÓN. Clic sobre el cuadro de texto y mantener pulsado / Arrastrar hasta situar sobre el punto 2 de la escala (color amarillo).

  7. PORCENTAJE. Ir a: Pestaña Insertar / Grupo: Texto Cuadro de texto.

  8. Clic sobre el gráfico / Ir a: Barra de fórmulas / Clic en su interior / Escribir: =Velocímetro!$D$2. Mostrará el porcentaje entre real y objetivos.

  9. POSICIÓN. Clic sobre el cuadro de texto y mantener pulsado / Arrastrar hasta la izquierda del punto 3 de la escala (color verde).

Gráfico con cuadros de texto de descripción

  

Nivel de dificultad: Avanzado Excel Avanzado

Deja un comentario

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