jueves, 10 de diciembre de 2009

Los ejes secundarios en un Gráfico de Excel.

Supongamos una tabla de datos con distintos campos con valores muy dispares:


Si a partir de esta tabla generaramos un gráfico de columnas, por ejemplo, veríamos el siguiente resultado:


Como se puede observar la diferencia entre las magnitudes de ambos campos es demasiado grande como para que quede adecuadamente reflejada en el gráfico, es decir, los valores de lo facturado (en millones) hace invisible el número de empleados (en unidades o decenas).
Una posible solución a este problema es llevar los datos de una de las series, en el ejemplo el número de empleados, a un Eje secundario. Haciendo click en la serie de datos dentro del gráfico y una vez seleccionada la serie con el botón derecho del ratón, desplegamos el menú contextual concreto:


Elegimos la opción de Dar formato a series de datos... y en la siguiente ventana diálogo seleccionaremos Trazar serie en Eje Secundario


Si, como en el ejemplo, se hace difícil seleccionar la serie en cuestión siempre podremos marcarla yéndonos al complemento de 'Herramientas de gráficos' y en su pestaña de 'Formato', dirigirnos al grupo de opciones de 'Selección de datos', para desplegar el 'elemento del gráfico' deseado. Los pasos siguientes serán los mismos que los explicados en el párrafo anterior.


haz click en la imagen


Obtenemos el resultado, donde vemos que una de las series 'Facturado' se referencia con el 'Eje principal' con su escala correspondiente, y la serie 'Empleados' con el 'Eje secundario', i.e., el opuesto al Eje principal, igualmente con su escala apropiada.


Para hacer más visible los datos del gráfico cambiaremos el Tipo de gráfico de la serie 'Empleados'. Aprovechando que ya tenemos seleccionada la serie, desde el botón derecho del ratón accedemos a la opción de Cambiar Tipo de gráfico de serie y seleccionaremos uno de Línea, tras aceptar tendremos el gráfico final buscado:

45 comentarios:

  1. Gracias por el post.
    Estoy trabajando en un gráfico similar (facturación vs. empleados). El problema es que cuando la facturación queda en negativo, el valor 0 del eje secundario coincide con el menor valor del eje principal; entonces a veces ocurre que el número de empleados queda por debajo del eje principal y queda raro, como si hubiese un número negativo de personas.

    ¿Existe alguna manera de hacer coincidir el valor 0 de los dos ejes verticales con el eje horizontal?

    Gracias
    Fran

    ResponderEliminar
  2. Hola Fran,
    subí un post contestando a tu pregunta
    http://excelforo.blogspot.com/2010/01/escalas-en-ejes-de-graficos.html
    espero que te sirva y conteste tu pregunta.
    Perdón por el retraso....
    Un saludo

    ResponderEliminar
  3. Hola y perdón por el retraso aún mayor :)
    Sí que es muy útil.
    Gracias

    ResponderEliminar
  4. Hola, gracias por la explicacion, tenia una pregunta ...se puede poner mas de un eje secundario cada uno con su respectiva escala???
    Gracias
    Atte Carlos

    ResponderEliminar
  5. Hola Carlos,
    sólo es posible trabajar con un eje secundario... es un tema de restricción espacial al trabajar sobre un plano, en total tenemos un eje principal ,de las X, y su secundario u opuesto y otro principal, de las Y, y su secundario que es el que habitualmente más se emplea.
    Saludos

    ResponderEliminar
  6. bueno muchas gracias por la pronta respuesta...
    bueno tendre k hacer el grafico en otro software no keda otra ps...Sigue asi con el blog estan muy buenas las explicaciones ...
    Saludos
    Atte Carlos

    ResponderEliminar
  7. Hola ...
    tengo un pequeño problema , hice una gráfica en excel 2007 y el eje vertical me aparece con números repetidos osea en ves de ir 0 1 2, va así 0 0 0 1 1 1 2 2 2.... que puedo hacer para arreglarlo ... gracias

    ResponderEliminar
    Respuestas
    1. hola, pudiste solucionar eso? xq yo tengo el mismo problema!!

      Eliminar
  8. Buenas tardes,
    probablemente la solución pase por solucionarlo en la tabla de datos, agrupándolo como quieras en el origen de datos...
    Envíame si quieres un ejemplo del fichero en Excel y lo revisaré.
    Saludos

    ResponderEliminar
  9. hOLA ESTA SUPER PADRE!! gRACIAS!! ESPERO SIGAN SUBIEN MAS Y MAS!! QUISIERA QUE ME AYUDARAS CON ESTO:
    Se ha solicitado un préstamo de S/. 17 000 a una TEA del 20% en seis cuotas mensuales. Elaborar un cuadro de amortización de la deuda con estos datos. Graficar la amortización en barras y los intereses en linea
    Seria TEB=0.095445115? m=2??
    DE la misma forma podria graficar la amortizacion con la tasa de interes? amortizacion en barras(eje horizontal) y el interes en linea (eje vertical)? Muchas gracias, disculpa mi ignorancia recien estoy aprendiendo y tu blog m ayuda mucho, gracias gracias. Liz

    ResponderEliminar
  10. Hola Liz,
    sería mejor me enviaras un mail a
    excelforo@gmail.com
    para poder enviarte una solución...
    De todas formas échale un vistazo a
    http://excelforo.blogspot.com/2009/06/hipotecas-pago-pagoprin-y-pagoint.html
    donde podrás ver cómo construir una tabla de préstamo distinguiendo principal e intereses, sólo faltaría construir la gráfica sobre esa tabla...
    Espero tu correo.
    Slds

    ResponderEliminar
  11. Buenas, en el excel 2007 que tengo en la opcion de dar formato a la serie de datos no me aparece la opción del eje secundario, tengo q actualizar algo dentro del programa??

    gracias
    gonzaqui

    ResponderEliminar
  12. Hola!! me pasa lo mismo que gonzaqui!!

    no aparece la opción de eje secundario, qué podrá pasar?

    gracias!

    Javiera.

    ResponderEliminar
  13. Hola Gonzaqui y Javiera,
    en principio no es necesario actualizar nada, esta herramienta es estándar y todos lo tenemos habilitado de origen; sin ver los ficheros es difícil dar una solución, pero habría que verificar que tenéis al menos dos series de datos, ya que sólo así esta Opción de serie se podrá marcar.
    Espero con esto sea suficiente..
    Slds

    ResponderEliminar
  14. yo tenia el mismo aparente problema. La cuestion es que la opción de eje secundario no se habilita si intentas un gráfico con columnas(3D) y no uno solo con barras(2D).

    ResponderEliminar
  15. Hola LB,
    es cierto que con los gráficos 3D se pierden algunas funcionalidades... a cambio, puedes añadir otras, que de otra manera sería imposible.
    Slds y gracias por tu comentario.

    ResponderEliminar
  16. Un gusto en saludarlo , mi consulta es la siguiente tengo un archivo Excel con datos mensuales ,totales mensuales y totales anuales , el cual realice el grafico correspondiente pero tengo un problema ……..necesito dejar el total anual totalmente separado de los meses pero en el mismo grafico ,lo realice con la tabla de eje secundario pero esta está ligada a cada movimiento de escala del eje de los meses ,si pudiera ayudarme eternamente agradecido.

    ResponderEliminar
  17. Hola José Miguel...
    bueno, pues la clave del asunto es crear una serie de datos independiente para esos totales,
    (te he enviado un ejemplo a tu correo)
    Como verás es algo más laborioso que tu planteamiento, pero la única forma que de manera independiente puedes leer un dato por el eje secundario y el resto por el primario.
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      Estoy intentando representar en una misma gráficas tres variables y independientes. Logro poner una de ellas en el eje y principal y otra en el eje y secundario. Pero al poner la tercera en el secundario me lo considera con la escala del anterior que llega hasta 700 mientras que esta última sólo llega a 2.
      Igualmente, si pudiera ayudarme le estaré muy agradecido

      Eliminar
    2. Hola Tsalawaly,
      la cosa parece complicada, ya que una de las variables la has llevado ya al Eje Secundario, dejando por tanto dos de las varibales (con grandes diferencias de unidad de medida entre ellas) en el Eje principal... Supongo que el problema es que no se verá la Serie más pequeña (que llega hasta 2). La única solución que se me ocurre, si no puedes llevarla al Eje secundario por tener el mismo problema, es que apliques una Escala Logarítmica en las opciones del Eje principal, para que se hagna visibles los datos de ambas series...
      Espero te oriente en la solución.
      Un saludo

      Eliminar
    3. Ok, voy a intentar representarlo así. A ver como queda.
      Muchas gracias!

      Eliminar
    4. Buenas tardes,

      Estoy interesado en la respuesta que le indicaste a Jose Miguel ya que tengo el mismo inconveniente. Podrías enviarmela a mi correo también.

      Muchas gracias.

      Saludos,

      Federico.

      Eliminar
    5. Hola Federico,
      envíame un correo a
      excelforo@gmail.com
      pidiéndome el archivo en particular.
      Por favor, describe tu problema.... que entre tanto correo a veces me pierdo
      ;-)
      Un cordial saludo

      Eliminar
  18. Es muy grato saludarlo y poder aprender un poco más de esta herramienta.
    Mi consulta es si se puede (en ese mismo ejemplo) los datos de facturado, como una barra AL COSTADO de las barras de empleados y no encima. He intentado hacerlo con una data similar y no he podido, pero si se puede cuando el gráfico es lineal y de dispersión.
    Muchas gracias por su respuesta.

    ResponderEliminar
    Respuestas
    1. Hola,
      pues hasta donde yo sé, al desplazar una serie a un eje secundario siempre lo superpone a otras series; sin embargo podríamos engañar a Excel, generando una serie de datos vacía, para que la superposición sea completa sobre la vacía.
      Subiré un post para explicar el 'engaño' o truco.
      Slds

      Eliminar
  19. necesito poner un eje secundario sdicional al que ya tengo . podrian decirme como hacerlo

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      ¿un eje secundario adicional?... no creo sea posible, date cuenta que estamos trabajando en un gráfico bidimensional, por lo que sóloo es posible un eje secundario al eje de las X y otro secundario al eje de las Y...
      Slds

      Eliminar
  20. quiero graficar dos series de datos
    serie 1 poniendo en eje x
    serie 2 poniendo en eje y o viceversa
    pero en exel 2007 no se puede hacer en diseño de lineas. lo que se es que se grafica serie 1 eje y vs eje x como 1, 2, 3, etc. Lo mismo pasa con serie 2 y da dos lineas de series 1 y 2 de dos colores

    ResponderEliminar
    Respuestas
    1. Hola,
      a mi entender Excel no habilita tal posibilidad de medir de manera independiente una serie sobre un eje y al mismo tiempo una segunda serie sobre el eje perpendicular.
      Rebuscaré sobre los diferentes tipos de gáficos, por si acaso existe alguno diferente al de líneas que pueda representar algo parecido a lo que necesitas.
      Slds y suerte

      Eliminar
  21. Hola, estoy intentado realizar este grafico, pero no me sale la opicion de eje secundario dentro de opiciones de serie, ni tampoco en la pestaña de diseño de ejes, solo me salen primarios. Hay algún otro sitio para encontrarlo, porque me estoy volviendo loca y seguro que tiene que ser de lo mas facil

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Cristina,
      entiendo estás replicando el gráfico tipo columnas.
      Debes asegurarte que tienes seleccionada la serie que quieres 'mandar' al Eje secundario, esto es lo más importante, una vez segura que tienes esa serie marcada, lo más sencillo, es presionar botón derecho del ratón y buscar la opción Dar formato a serie de datos, seguro te debe salir la posibilidad de Eje primario/Eje secundario.

      Si después de verificar esto sigue sin salirte (no lo creo posible) envíame el fichero a excelforo@gmail.com
      Un saludo

      Eliminar
    2. Gracias pero me sigue sin salir, no se si será por mi excel, te mando un correo con una tabal simple

      Gracias

      Eliminar
    3. OK, Cristina.
      ya te he contestado por mail... el problema es que tu tipo de gráfico es 3D, y no permitía esa posibilidad, por eso que en el anterior comentario te preguntaba por el tipo de gráfico.
      Slds

      Eliminar
  22. tengo un problema, he buscado la manera de hacer un grafico con tres ejes Y y los tres graficados contra un mismo eje X de manera individual pero no he encontrado como hacerlo, solamente de dos ejes Y contra un mismos eje X de manera individual.

    ResponderEliminar
    Respuestas
    1. Hola!!
      ¿tres ejes Y diferentes?... ¿dónde estaría el tercero colocado?
      Lo siento, pero no visualizo el problema...

      Eliminar
  23. Hola... muchas gracias por el blog, es de gran utilidad... tengo un problema justo como el que se describe en el ejemplo... tengo 2 ejes Y (uno primario y uno secundario) con un eje X (horizontal, solo es uno ya que ambos ejes comparten los mismos datos), diferentes escalas por razones lógicas de origen de datos. El problema es que cuando realizo el gráfico tal como se describe en el ejemplo me resulta como el ejemplo (cambiando el tipo de gráfico a convertirlo en uno de lineas) me funciona bien, pero yo quiero que las barras se separen, por mas que me fuí a la instrucción superponer y sobrepuesta en excel (tengo 2007) no realiza ninguna modificación y si es necesario que lo presente así en ese formato de gráfico (o sea los datos de ambos en barras y separados). Si existiera alguna solución , lo agradeceré
    =D

    ResponderEliminar
    Respuestas
    1. Hola !, muchas gracias ;-)
      me alegro te haya servido algo de lo publicado.

      Respecto a tu cuestión en principio no hay truco en lo que ves, de todas formas, si lo prefieres envíame a
      excelforo@gmail.com
      el fichero con el ejemplo y le echo un vistazo a ver si doy con lo que necesitas...
      Slds cordiales

      Eliminar
  24. Hola Ismael. Te escribo desde México. Tengo un gráfico en donde en el eje 'Y' coloco reclamaciones de clientes y en el eje 'X' los meses del año. Cada mes actualizo este gráfico, y el problema que tengo es que en la opción de Seleccionar origen de datos/Editar etiquetas de eje horizontal tengo que hacer el proceso de edición dos veces porque a la primera no actualiza los meses. ¿Cuál puede ser la causa? Gracias!

    ResponderEliminar
    Respuestas
    1. Hola Isaías,
      suele ser muy habitual en Excel 2007 y superiores que no reconozca bien las etiquetas del eje X... no hay un motivo claro, pero por experiencia 'falla' cuando tiene cabecera.
      De todas formas si queires que le eche un vistazo, envíamelo a
      excelforo@gmail.com

      Un saludo

      Eliminar
    2. Hola Ismael. Gracias por tu respuesta. ¿A qué te refieres con "cabecera"?

      Eliminar
    3. Hola,
      la cabecera sería el encabezado de los datos, los rótulos o primera fila del origen de datos.
      Slds

      Eliminar
    4. Entiendo. Muchas gracias por tu ayuda!!

      Eliminar
  25. Hola, me parece muy interesante!!, Una pregunta, se pueden tener mas de un eje secundario?, es decir poder tener mas de dos ejes de ordenadas (Y) en el mismo gráfico?

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      un gráfico sólo puede tener un eje principal y secundario vertical y horizontal... es un tema de dimensiones, al trabajar en un plano, tendrís principal y enfrentado a éste su secundario.
      Saludos

      Eliminar