lunes, 6 de febrero de 2012

VBA: Modificar etiquetas de datos en un gráfico de Excel.

En esta ocasión, partiendo del gráfico apilado visto en una entrada anterior, mediante una macro, conseguiremos personalizar las Etiquetas de datos de los diferentes puntos de nuestras Series.
Nuestro objetivo es conseguir este aspecto en nuestra ya conocida gráfica:

VBA: Modificar etiquetas de datos en un gráfico de Excel.


La modificación consiste en añadir un Texto personalizado en las Etiquetas de datos de los diferentes puntos que componen nuestra serie, añadiendo no solo un texto 'Total' que no existe en la Tabla de datos, si no además debajo, en una nueva línea, el sumatorio de ambos semestres.
Para no dispersarnos con el código de nuestra macro, supondremos ya existen las Etiquetas de datos, y ya tienen la posición y formato de color y fuente. Sobre esta premisa, el desarrollo de nuestra macro, a incluir en un Módulo del Explorador de proyectos, dentro de nuestro Editor de VBA (Alt+F11), escribimos:

Sub ModificarEtiqueta()
ActiveSheet.ChartObjects("4 Gráfico").Activate
'contamos el número de puntos existentes en las series
puntos = ActiveChart.SeriesCollection(2).Points.Count

Application.ScreenUpdating = False
'pasamos por todos los puntos de la serie con el bucle FOR...NEXT
For i = 1 To puntos
    'definimos el texto y valor aincluir en la etiqueta
    ActiveChart.SeriesCollection(2).Points(i).DataLabel.Text = "Total" & Chr(10) & _
    (Cells(2, i + 1).Value + Cells(3, i + 1).Value)
Next i
Application.ScreenUpdating = True
End Sub


Al ejecutar nuestra macor conseguiremos el formato deseado.

12 comentarios:

  1. No encontre lo que buscaba, y me colgue de este tema, ya que no enontre tampoco una opcion para crear un tema o comentario...
    Tengo una duda el como hacerle
    Tengo una tabla por llamarla algo depto1 y de ella se derivan 3 graficas... pero en la misma hoja y sobre los mismos datos manejo otros 7 u 8 Deptos, como le puedo hacer para que los graficos queden relacionados con los datos de cada tabla ya que al copiar deja los valores de la primera tabla... Espero me puedan ayudar...
    tux.corp.0987@live.com.mx
    tux.corp.0987@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Admintux,
      supongo te refieres a que esos tres gráficos de los que hablas se obtengan a partir de las 7 u 8 tablas (Deptos); si es así, podrías configurar la Selección de datos con rangos discontinuos sobre esas tablas... aunque necesitarías que todas tuvieran una estructura común.
      Slds

      Eliminar
  2. Perdon pero igual que el compa de antes no se donde hay una sección de dudas pero tengo problemas para generar un gráfico de columna apilada, te pondré el ejemplo con tu gráfico digamos que tienes 2 vendedores por lo tanto tienes 4 series de datos, 2 del primer semestre uno de cada vendedor, y otros 2 del segundo semestre nuevamente uno de cada vendedor mi duda es como graficar para que en cada mes salgan 2 barras con 2 series cada uno y no una sola barra con 4 series, osea quiero 2 barras (cada barra representa a cada vendedor) con 2 series cada una (semestre 1 y 2) en cada mes se puede hacer? te dejo mi correo ojala puedas ayudarme demon_sero@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      si es posible realizar el tipo de gráfico que pides, es necesario aplicar etiquetas de categorías de múltiples niveles.
      En esta entrada doy respuesta a tu cuestión:
      http://excelforo.blogspot.com.es/2012/05/un-grafico-de-excel-con-etiquetas-de.html
      Espero te sirva.
      Un saludo
      P.D.: para realizar cuestiones puedes emplear los comentarios del blog, o bien mandar un correo a excelforo@gmail.com

      Eliminar
  3. Hola,

    Yo he tratado de agreagar etiquetas a un gráfico usando el siguiente código:

    ************************************************************
    With graph
    .SeriesCollection.NewSeries
    .SeriesCollection(1).XValues = Application.Transpose(Application.Index(arr_hora, 0, 1))
    .SeriesCollection(1).Values = Application.Transpose(Application.Index(arr_hora, 0, 2))
    .SeriesCollection(1).Format.Fill.Visible = msoFalse
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = "Duración de llamada"
    .SeriesCollection(2).XValues = Application.Transpose(Application.Index(arr_hora, 0, 1))
    .SeriesCollection(2).Values = Application.Transpose(Application.Index(arr_hora, 0, 3))
    .SeriesCollection(2).HasDataLabels = True
    For i = 0 To rango2 - 1
    .SeriesCollection(2).Points(i).DataLabel.Text = sheets("data").Cells(inicio, 6).Value
    inicio = inicio + 1
    Next i

    graph.SetElement (msoElementChartTitleAboveChart)
    graph.ChartTitle.Text = "Graph"
    .Refresh
    End With

    *************************************************************

    Pero marca el error 1004, parámetro no válido en la línea donde se hace referencia a Points. No tengo idea de que esta mal, no tengo experiencia programando gráficos, y mi intención es que en lugar de que aparezcan las etiquetas automáticas (las cuales son números), aparezca los datos de una celda determinada.

    Espero puedan orientarme, gracias de antemano.

    Saludos cordiales.

    ResponderEliminar
    Respuestas
    1. Hola,
      es difícil decirlo con el código que adjuntas, pero probablemente el error se produzca por que el bucle lo comienza desde cero:
      For i = 0 To rango2 - 1
      .SeriesCollection(2).Points(i).DataLabel.Text = sheets("data").Cells(inicio, 6).Value
      inicio = inicio + 1
      Next i

      prueba con
      For i = 1 To rango2 - 1
      ...

      Slds cordiales

      Eliminar
    2. Muchas gracias Ismael,

      Exactamente ese era el error, que iniciaba el bucle en cero.

      Ya todo funciona perfecto.

      Saludos y gracias de nuevo.

      Eliminar
    3. ;-)
      A veces nos obcecamos y no vemos el bosque.. a mi me pasa constantemente.
      Un cordial saludo

      Eliminar
    4. Aprovechando de su conocimiento, habrá manera de modificar el texto del eje horizontal en un gráfico? Por ejemplo: si se esta graficando cuantas compras hubo por mes, en lugar de que diga mes 1, mes 2, mes 3, mes 4; cambiarlo por Enero, Febrero, Marzo...

      Saludos.

      Eliminar
    5. Hola,
      imagina que tus datos están en A1:B7, y en A2:A7 tienes numerados los mese 1,2,3...
      Construyes tu gráfico (por ejemplo de columnas) y en las etiquetas del eje horizontal te aparecerá, logicamente 1, 2, 3... o Mes 1, mes 2, etc.
      para que te aparezca como indicas, podrías seleccionar la serie de datos y sustituir la función SERIES por
      =SERIES(Hoja1!$B$1;{"ene"\"feb"\"mar"\"abr"\"may"\"jun"};Hoja1!$B$2:$B$7;1)
      manualmente, o marcarla en otro sitio donde lo tengas...
      ojo, por que es importante que el orden coincida con el original...

      Espero te sirva.
      Slds

      Eliminar
  4. Bueno días,
    En relación al tema de los graficos. Estoy confeccionando uno de disperción donde se reflajaran los riesgos de una empresa. Tengo tres columnas: La primera con el nombre del riesgo que puede ser de 1 a 200. La segunda con la probabilidad de ocurrencia, con valores de puede ser de 1 a 5 (eje X) y la tercera con el impcato, puede ser de 1 a 5 (eje Y). El problema surge cuando intento colocar el respectivo nombre dentro de cada Punto de dato, manualmente es posible seleccionando una por una cada serie pero como son 200 es poco practico ¿Existe una forma de automatizar este proceso? Muchas Gracias

    ResponderEliminar