jueves, 30 de mayo de 2013

Dos escalas diferentes en el eje vertical de un gráfico de Excel.

Una lectora del blog me planteaba la forma de aplicar sobre un mismo eje vertical dos escalados diferentes:
...Quisiera hacer un gráfico de columnas en el cual el eje vertical tuviera una escala de valores personalizada. Tiene que ser en %, pero al tener unos valores muy bajos estas columnas querría poder situar valores del 0 al 10%, es decir, que aparecieran 0%, 1%, 2%...y así hasta el 10% y luego continuar hacia arriba con valores ya del 20%, 40%, 60% y así hasta el 100%...


Veremos hoy cómo es posible separar el eje vertical en dos partes, cada una de ellas con un escalado diferente. El trabajo será bastante completo ya que tocaremos diferentes herramientas de gráficos, como los ejes secundarios, formatos de etiquetas de ejes o las opciones del eje, entre otras...

Veamos el planteamiento de datos y el resultado al que llegaremos... eso sí, tiene un pequeño defecto visual, pero sin duda es lo más cercano al tipo de gráfico solicitado:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.


En la imagen podemos ver cómo la serie de datos se adapta al eje vertical, y cómo éste tiene dos escalas diferentes, una primera de 0% a 10% (incrementando de uno en uno) y una segunda de 10% al 100% aumentando de diez en diez).

Vamos con la explicación. En primer lugar construiremos dos series auxiliares al rango de datos, en C1:D11. Estas dos series las construiremos con una fórmula condicional que determine si el valor a representar del rango B2:B11 es mayor o menor a 10%.
La fórmula para el rango C2:C11 será:
=SI(B2<=10%;B2;NOD())

y para el rango D2:D11
=SI(B2>10%;B2;NOD())

Así la primera serie sólo representará los valores entre 0% y 10%, mientras que la segunda el resto, es decir, del 10% hasta el 100%:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.



Construimos un gráfico de columnas a partir de nuestros nuevos datos, seleccionamos el rango A1:A11 y C1:D11:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.



En el siguiente paso llevaremos la segunda serie de datos 'datos>10%' al Eje secundario, para lo cual desde el Formato de serie de datos marcaremos dicha opción Trazar serie de datos en Eje secundario:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.



Atención al siguiente paso, ya que es el más importante. Con él engañaremos al gráfico.
Seleccionamos el Eje vertical principal (el asociado a la serie de datos 'datos10%'), y configuramos las Opciones del eje como se ven en la imagen:
Mínimo: 0 equivale al 0%
Máximo: 0,2 equivale al 20% (ojo el doble de nuestro punto de inflexión en el gráfico de una escala a otra que es el 10%)
Unidad mayor: 0,01 salto de intervalo de 1% en 1%

Dos escalas diferentes en el eje vertical de un gráfico de Excel.
haz clic en la imagen


Para acabar con este Eje vertical principal, daremos un formato de número personalizado tipo: [<=0,1]0,00%;;;:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.
haz clic en la imagen



De manera similar repetimos la operación para el Eje secundario vertical. Asi que seleccionamos este eje (el que corresponde a la serie de datos 'datos>10%') y en las Opciones del eje damos los siguiente valores:
Mínimo: -1 equivale al -100% (ampliamos el rango mostrado hasta -100% - aunque no haya tales valores- para compensar el espacio de la otra serie de datos 'datos10%'
Máximo: 1 equivale al 100%
Unidad mayor: 0,2 salto de intervalo de 20% en 20%
Etiquetas del eje: Bajo que nos lleva las etiquetas junto al eje vertical principal
El eje horizontal cruza en el Valor del eje: -1,0 (lleva el inicio de las columnas al valor -100%.

Dos escalas diferentes en el eje vertical de un gráfico de Excel.
haz clic en la imagen


Aplicamos el formato de número personalizado a estas etiquetas con el tipo: [>=0,2]*W0,00%;;;

Dos escalas diferentes en el eje vertical de un gráfico de Excel.
haz clic en la imagen



Ya hemos acabado... casi, un par de detalles más. Sobre el gráfico resultante:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.


Nos queda eliminar la Leyenda, para lo cual la seleccionaremos y presionaremos Suprimir. Y para acabar daremos el mismo color a ambas series de datos.
El resultado es:

Dos escalas diferentes en el eje vertical de un gráfico de Excel.



Quizá no sea el gráfico más vistoso del mundo, falla la 'linealidad' de las etiquetas del eje vertical... pero desde luego los datos mostrados guardan una perfecta relación con la escala mostrada... Consiguiendo el objetivo, los valores pequños entre 0% y 10% se miden por unidades, mientras que el resto, los porcentajes superiores al 10% se miden en decenas, tal y como preguntaba la lectora.

13 comentarios:

  1. Hola: por favor necesito realizar una gráfica donde se parta el eje horizontal en dos escalas, similar al ejemplo pero invertido. El eje horizontal (X) inicia con valores de concentración muy bajos (7, 15, 30, 60, 120, 240) que se van duplicando y llego a valores muy altos (>3000), pero lo que me interesa que se vea en el gráfico son los valores bajos donde se describe una curva, ya que después de 1000 la curva se hace constante en una linea horizontal. Los valores del eje vertical (Y) son porcentajes que va desde el 0 al 100%. Por favor me enseñen a hacerla. Muchas gracias. Pamela.

    ResponderEliminar
    Respuestas
    1. Hola Pamela,
      intentaré dar una solución y subir la explicación al blog en próximas fechas.

      Un saludo cordial

      Eliminar
  2. Hola Ismael,
    Enhorabuena por tu foro, la verdad es que es de gran ayuda.
    Tengo una pregunta, Como puedo hacer lo mismo pero con valores absolutos? Es decir, por ejemplo, los valores inferiores a 1000 que la escala sea de 100 en 100 y para el resto de valores que sea de 2000 en 2000. Muchas gracias por tu ayuda. Un saludo

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias !

      en principio todo lo que dependa del escalado se configura en las Opciones del Eje, por lo que tendrías que seleccionar el Eje secundario e indicar la unidad mayor y menos en 2.000

      Espero haberte entendido
      Saludos cordiales

      Eliminar
  3. Buen día Ismael,
    he estado investigando y no he tenido éxito para lo que necesito, y encontré tu foro y es algo muy similar a lo que he estado buscando, solo que no me acepta la fórmula que propones, quizá se deba a que no estoy utilizando una columna de datos sino 4.

    Agradecería pudieras orientarme un poco. Te comento lo que estoy trabajando.

    Tengo que graficar el historial productivo de algunos cultivos durante 4 años (2009, 20010, 2011 y 2012. Tengo una primer columna con el nombre del cultivo, y las siguientes 4 columnas corresponden una a cada año transcurrido. el problema es que al graficar me aparecen la mayoría de los datos en un rango de 1 a 100, otros de 1000 a 2000 y solo un valor se eleva hasta los 4000, así que cuando lo grafico solo se ve una barra gigante y los valores menores a 100 no se aprecian, por lo que me gustaría en el mismo eje me gustaría poder tener una primer escala del 0 al 100 con rangos de 20 en 20 quizá, y una segunda escala de 101 a 4000 con rangos de 1000 en 1000 y no lo he logrado. Podrás orientarme al respecto. Muchas gracias.

    PRODUCTO SUPERFICIE 2009 (has) SUPERFICIE 2010 (has) SUPERFICIE 2011 (has) SUPERFICIE 2012 (has)
    PASTOS Y PRADEDRAS 4,028 4,262 4,262 4,262
    MAÍZ GRANO 1,880 1,581 1,553 1,929
    AGAVE 1,219 1,252 1,270 1,270
    SORGO GRANO 87 443 48 113
    CHILE VERDE 28 31 16 29
    CACAHUATE 5 2 6 24
    SANDIA 13 26 30 16
    JITOMATE 0 0 0 12
    MARALFALFA 0 0 5 5
    ALFALFA VERDE 18 9 9 1
    PEPINO 0 6 16 1

    ResponderEliminar
    Respuestas
    1. Hola Jaime,
      intentar esta técnica para cuatro escalas, en principio es imposible.. ya que un gráfico sólo tiene dos ejes verticales (una principal y uno secundario), por lo que como mucho podremos dividir en dos nuestra escala.
      Mi recomendación, es apliques al eje vertical principal la escala logarítmica desde el formato de ejes, no es lo más vistoso, pero al menos verás todos los datos...

      Saludos

      Eliminar
  4. Gracias por la respuesta y la recomendación.

    Las tomaré en cuenta para la continuación de mi trabajo!!!

    ResponderEliminar
  5. Hola,
    ¿Esto también se puede hacer con excel 2003?
    Gracias!

    ResponderEliminar
    Respuestas
    1. Hola Markel,
      en versión 2003!!!... bueno, en teoría si es posible ya que no se emplea ninguna herramienta ni función que no existía en esa versión.
      Saludos

      Eliminar
  6. Hola¡¡¡ Se pueden en un mismo gráfico poner cinco series y cada serie con una escala diferente????

    ResponderEliminar
    Respuestas
    1. Hola Begoña,
      en teoría no es posible, ya que sólo contamos con dos ejes verticales (el principal y el secundario).
      :(
      Saludos

      Eliminar
  7. Hola, no me queda en mi gráfico, tengo excel 2003 y no encuentro la manera de poner "bajo" en el eje secundario casi al final. Muchas gracias
    Cecilia

    ResponderEliminar
    Respuestas
    1. Hola Cecilia,
      Creo recordar que en los gráficos de esa versión no existía tal opción.
      :(

      Lo siento
      Saludos

      Eliminar