jueves, 16 de julio de 2015

Gráfico combinado de columnas y dispersión especial...

Hoy trataremos un tema interesante sobre gráficos.. de nuevo engañaremos a Excel para que nos muestre un gráfico según nuestra necesidad, esto es, combinar unas series de datos con tipo de gráfico de columnas, y otras series de líneas, adaptando o ajustando sus puntos a las columnas...
¿Raro?, seguro se ve mejor en la imagen siguiente.
Gráfico buscado:

Gráfico combinado de columnas y dispersión especial...

Debemos fijarnos dónde parecen los marcadores de las 'líneas'...
Lo normal de un gráfico combinado sería:

Gráfico combinado de columnas y dispersión especial...


La diferencia es clara, en nuestro gráfico objetivo, cada marcador se superpone a su columna correspondiente, mientras que en el gráfico 'normal', los marcadores de las tres líneas aparecen punteados en la misma posición media del punto X.

Partiremos del siguiente origen de datos, donde vemos nuestras series de datos...

Gráfico combinado de columnas y dispersión especial...


Unas representan 'Importes' y otras tres 'Cantidades', ambas de magnitudes muy diferentes.. lo que nos obliga a llevar las 'Cantidades' al Eje Secundario

Vamos a generar unas columnas auxiliares que utilizaremos como valores de nuestro eje horizontal (eje X), en lugar del rango A2:A6 que tenemos...


Como podemos comprobar no es más que el valor de cada año restándole 0,20, cero o sumándole 0,20.
Por ejemplo, para el primer año 2010, tendríamos: 2009,80 (2010-0,20), 2010 y 20101,20 (2010+0,20).
Con esto conseguiremos desplazar el punto del marcador de cada valor de las tres series.


Con todos los datos dispuestos, ya podemos empezar a construir nuestro gráfico.
Seleccionaremos el rango discontinuo: A1:A6 y E1:G6 e insertaremos un gráfico de dispersión con líneas rectas y marcadores, consiguiendo el siguiente gráfico:

Gráfico combinado de columnas y dispersión especial...



El siguiente paso es fundamental. Para cada una de las tres series modificaremos el rango para los Valores X de la serie:

Gráfico combinado de columnas y dispersión especial...


Así deberán quedar las tres series:
Serie PM Qty:
Nombre de la serie:=Hoja2!$E$1
Valores X de la serie: =Hoja2!$I$2:$I$6
Valores Y de la serie: =Hoja2!$E$2:$E$6

Serie PC Qty:
Nombre de la serie:=Hoja2!$F$1
Valores X de la serie: =Hoja2!$J$2:$J$6
Valores Y de la serie: =Hoja2!$F$2:$F$6

Serie PF Qty:
Nombre de la serie:=Hoja2!$G$1
Valores X de la serie: =Hoja2!$K$2:$K$6
Valores Y de la serie: =Hoja2!$G$2:$G$6

Consiguiendo el objetivo primero, los marcadores quedan representado en diferentes puntos... y no sobre una misma vertical.

Gráfico combinado de columnas y dispersión especial...



En el siguiente paso Agregaremos tres series de manera manual. Desde Seleccionar datos... Agregar Serie indicaremos datos del nombre y de los Valores Y de la serie, dejando vacío el de Valores X de la serie:

Gráfico combinado de columnas y dispersión especial...


las tres series a añadir serán:
Serie PM €:
Nombre de la serie:=Hoja2!$B$1
Valores X de la serie:
Valores Y de la serie: =Hoja2!$B$2:$B$6

Serie PC €:
Nombre de la serie:=Hoja2!$C$1
Valores X de la serie:
Valores Y de la serie: =Hoja2!$C$2:$C$6

Serie PF €:
Nombre de la serie:=Hoja2!$D$1
Valores X de la serie:
Valores Y de la serie: =Hoja2!$D$2:$D$6


Para el último paso nos iremos a Cambiar tipo de Gráfico y tendremos cuidado de dejarlos como se ve en la imagen siguiente:

Gráfico combinado de columnas y dispersión especial...


Esto es, para la
Serie PM €: gráfico columna agrupada + eje principal
Serie PC €: gráfico columna agrupada + eje principal
Serie PF €: gráfico columna agrupada + eje principal

Serie PM Qty: gráfico dispersión con líneas y marcadores + eje secundario
Serie PC Qty: gráfico dispersión con líneas y marcadores + eje secundario
Serie PF Qty: gráfico dispersión con líneas y marcadores + eje secundario.


El gráfico está listo, sólo faltaría 'maquillarlo' un poco.. por ejemplo, el Eje secundario horizontal, lo podemos configurar en las Opciones de eje > Opciones de Texto > Relleno de texto > Sin relleno...
y también Opciones de eje > Relleno > Sin relleno
y Opciones de eje > Línea > Sin línea.

Quitaríamos, si así queremos, las líneas de división verticales... etc...

7 comentarios:

  1. barbaro sigue que tenes una mas que aplicara lo planteado

    ResponderEliminar

  2. Hola Ismael te habla Walter de Perú, en primer lugar mis felicitaciones del caso por el gran aporte académico que haces con temas de excel.
    Quería hacerte una consulta respecto a macros de excel: "Solo utilizando el comando IF, deseo ingresar un código por ejemplo "aut1" en la celda digamos A1 y que en esa misma celda A1 me devuelva un comentario mas largo ejemplo "Existe corrosión en la ferretería de la maquina". A partir de eso ya podría generar una hoja de ruta, pero es lo que primero debo lograr.
    Este es lo que intente hacer pero no funciona ya que tengo que ingresar el código primero y luego darle "play" para que me devuelva el comentario mas largo; en cambio mi objetivo es que me devuelva el comentario instantáneamente al ingresar el codigo "aut1":
    Sub caso1()
    If Range("a1") = "aut1" Then
    Range("a1").Select
    ActiveCell.FormulaR1C1 = "Ferreteria con principios de oxidación"
    Else
    Range("a1").Select
    ActiveCell.FormulaR1C1 = "No hay problemas"
    End If
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola Leandro,
      tendrías que incluir tu código en la ventana de código de la hoja, pero asociado a un evento _Change:
      Sub worksheet_change()
      If Range("a1") = "aut1" Then
      Range("a1").Select
      ActiveCell.FormulaR1C1 = "Ferreteria con principios de oxidación"
      Else
      Range("a1").Select
      ActiveCell.FormulaR1C1 = "No hay problemas"
      End If
      End Sub

      Saludos

      Eliminar
  3. Muchas gracias smael, estoy buscando la manera de hacerlo a un rango de celdas espero me puedas ayudar

    ResponderEliminar
    Respuestas
    1. Hola, aplica un loop que recorra esas celdas para evaluarlas; algo del estilo:
      for each c in range("a1:a10")
      if....
      next c

      Saludos

      Eliminar
  4. Hola Ismael, soy Ricardo Olea de Chile, estoy buscando la manera de utilizar la plantilla de presupuestos del Excell, los números vienen formateados en Euros y necesito cambiarlos a pesos. No puedo encontrar la forma de cambiar el formato. ¿Es posible que me indiques?, de la manera tradicional no funciona: celda, formato; al pareer la plantilla está hecha en Open Office. Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola Ricardo,
      los formatos se cambian desde donde dices Formatos de celdas > números (monedas, etc)
      trata de modificarlo desde el formato de celdas personalizado.

      No importa en qué aplicación se creó (open office o cualquier otro) siempre que se haya podido abrir con Excel.. en todo caso, por si fuera el problema, guarda el fichero con extensión .xlsx

      Saludos

      Eliminar