domingo, marzo 02, 2014

Gráficos Excel - Fijar máximos del eje vertical con macro

En la nota anterior sobre el tema mencionamos que la solución tradicional para coordinar el máximo del eje de las Y (eje de los valores en términos de Excel) es usar una macro.
El método manual, con fórmulas y series auxiliares, tiene la ventaja que puede ser usado también en entornos donde las macros están bloqueadas, por ejemplo,  por el administrador de la red. Las deventajas son menores pero existen:

  • el valor máximo es determinado por el algoritmo de Excel, por lo que siempre será cercano al valor deseado pero no siempre exactamente igual;
  • hay que modificar cada uno de las gráficos que queremos coordinar.
Las macros son más flexibles y eficientes, ya que podemos establecer exactamente el valor que queremos que aparezca como máximo y podemos aplicarlas a todos los gráficos de la hoja.

El código es muy sencillo y podemos usarlo de dos maneras distintas: como sub (macro común) o como evento. Al usarlo como sub, tenemos que crear también la forma de disparar el código, por ejemplo, ligando la macro a un botón; al usar eventos podemos hacer que la macro corra cuando, por ejemplo, el usuario cambia algún dato en la tabla de los datos.

Como en el ejemplo de la nota anterior, tenemos dos gráficos basados en dos tablas de datos y en la celda A3 calculamos el valor máximo de ambas tablas (hemos creado el nombre "cellValMax" que se refiere a la celda A3)

definiciones del grafico
Como podemos ver en la imagen, la escala del eje de las Y es diferente en cada gráfico y por lo tanto la comparación visual es engañosa.
Como mencionamos más arriba podemos usar el código como sub (macro) o como evento; el código es el mismo pero la forma de hacerlo correr es distinta.

Uso del código como sub (macro)

En un módulo compun del editor de Vb, ponemos este código

Sub coordinar_max_graficos()
   Dim dbMax As Double
   Dim objCht As ChartObject

   dbMax = Range("cellValMax").Value
  
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart.Axes(xlValue)
        .MaximumScale = dbMax
        .MinimumScale = 0
      End With
   Next objCht
End Sub


En la hoja que contiene los gráficos agregamos un botón ligado a la macro




Uso del código como evento

Para usarlo como evento el código debe ir en un módulo de la hoja que contiene los gráficos. Una de las formas de acceder a este módulo es apunta con el mouse a la pestaña con el nombre de la hoja y activar la opción Ver código

ver codigo de a hoja

En nuestro caso vamos a crear un evento de manera que cuando el usuario haga algún cambio en la hoja, se dispare el código. Para esto usamos un evento del tipo Change

codigo del evento

Como puede apreciarse, el código es el mismo que en la rutina sub.

7 comentarios:

  1. Estimado y si yo quiero cambiar el eje horizontal de forma automática???

    También quiero que el valor máximo lo saque de una celda.

    Gracias!

    ResponderBorrar
  2. Se me olvido mencionarte que tengo un gráfico de dispersión, por lo que deseo variar sólo el máximo que se muestra del eje X.

    Además, que aparte en esa misma hoja, tengo unos gráficos circulares que no deseo que cambien, aunque cómo estos al no tener eje horizontal supongo que no variarán.

    Gracias nuevamente.

    ResponderBorrar
  3. En el código en lugar de

    With objCht.Chart.Axes(xlValue)

    usas

    With objCht.Chart.Axes(xlCategory)

    En el código publicado, el valor máximo esta en una celda. En lugar de referencia de tipo A1 uso un nombre que se refiere a la celda.

    ResponderBorrar
  4. Yo logre establecer los maximos y los minimos con el siguiente macro
    ActiveChart.Axes(xlCategory).MinimumScale = Sheets("DATOS").Range("D2")
    ActiveChart.Axes(xlCategory).MaximumScale = Sheets("DATOS").Range("E2")
    Ahora necesito algo igual pero para ejes secundarios
    Alguien sabe como puedo hacer eso

    ResponderBorrar
  5. Hola Marco,
    de la manera. Por ejemplo, para el eje vertical secundario

    ActiveChart.Axes(xlValue, xlSecondary)...

    Para investigar las definiciones y propiedades de los objetos puedes usar la grabadora de macros.

    ResponderBorrar
  6. para q se en solo un grafico especifico

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.