martes, 16 de julio de 2013

Desplazar el valor del eje de un gráfico en Excel.

En la entrada anterior (ver) vimos como interacturar con un control de formulario (tipo Barra de desplazamiento) sobre el aspecto de un gráfico.
Hoy veremos una técnica similar que persigue mostrar una sección de los valores a representar, segmentando un continuo de las etiquetas del eje horizontal. Para ello deberemos trabajar con los Nombres definidos aplicados a la construcción de la Serie de datos (método ya empleado en otras entradas del blog, por ejemplo).


Veamos el efecto a conseguir:

Desplazar el valor del eje de un gráfico en Excel.



Comencemos el trabajo. La idea es clara, tenemos muchos datos y no queremos mostrarlo todos a la vez, ya que quedaría demasiado aglutinado.
El primer paso será construir el tipo de gráfico que mejor nos convenga, en este caso he optado por un sencillo gráfico de columnas; para tal cosa seleccionamos todo el origen de datos A2:B32 y desde la ficha Insertar > grupo Gráficos > Columnas > Columna agrupada.
Configuramos algo el diseño, eliminando la Leyenda y el Título de gráfico (si es nuestro deseo), añadimos las etiquetas de datos a la serie y adaptamos las etiquetas del eje horizontal, hasta que consigamos un gráfico como este:

Desplazar el valor del eje de un gráfico en Excel.



Fuera del área del gráfico añadimos una barra de desplazamiento horizontal (control de formulario) con la siguiente configuración:

Desplazar el valor del eje de un gráfico en Excel.


Como vemos hemos definido el rango de la barra desde 1 hasta 21 (ya que tenemos en total 30 registros y queremos mostrarlos en rangos de 10 en 10; y que al desplazaarnos por la barra siempre haya 10 puntos representados (si llevaramos el rango del control desde 1 hasta 30, al desplazarnos a los valores finales no veríamos esas 10 columnas...).
También es importante indicar que la celda vinculada al control (barra de desplazamiento) es la celda E26, que hemos llamado previamente (hemos asignado el nombre definido) 'Despl':
Despl =DesplazamientoGrafico!$E$26
el valor que toma queda oculta debajo de la misma barra.


En el siguiente paso construimos nuestros nombres definidos para los Años (SerieX) y los Importes (SerieY), es decir, para los valores del eje horizontal y de la serie de datos:
SerieX =DESREF(DesplazamientoGrafico!$A$2;Despl;0;10;1)
SerieY =DESREF(SerieX;0;1)

en ambos casos, para otorgar ese 'dinamismo' empleamos la función DESREF en su forma de rango.
Por ejemplo, la SerieX con fórmula =DESREF(DesplazamientoGrafico!$A$2;Despl;0;10;1) configura un rango que comienza un número de celdas por debajo de la A2 (el número que indica el control o barra de desplazamiento en su celda vinculada) con una altura fija de 10 valores.


El último paso es siempre delicado, ya que consiste en incorporar a la función de la Serie de datos los nombres definido creados. Para esto seleccionamos la Serie de datos y presionamos la opción de de Seleccionar datos, donde accederemos a las Ediciones de las Entradas de la Serie y también de las Etiquetas del eje horizontal:

Desplazar el valor del eje de un gráfico en Excel.


En uno y otro incluiremos los nombres definidos 'SerieX' y 'SerieY', pero con el nombre del Libro delante:
=DesplazamientoEjeGrafico.xlsx!SerieY
=DesplazamientoEjeGrafico.xlsx!SerieX
para la Serie y la Etiquetas del eje respectivamente.


Listo, hemos finalizado nuestro trabajo. Ya podemos desplazarnos por los diferentes años (de 10 en 10) empleando para ello la Barra de desplazamiento.

13 comentarios:

  1. Buen día Ismael,le comento que estoy realizando un grafico combinado de 4 series donde una serie es Dispersión y las otras series son Lineales, me gustaría que las opciones del Eje Y en la opción del eje mínimas y máximas tomara el valor de dos celdas en la hoja de cálculo que previamente determine una celda mínima y la otra celda máxima, este cambio de opción de indicar el mínimo y el máximo del eje siempre lo hago manualmente en opciones dar formato eje. Mi pregunta es si se podría esta opción de los ejes dependiendo del valor de la celda que uno destine?

    ResponderEliminar
    Respuestas
    1. Hola,
      si es posible.. por ejemplo puedes aplicar un código similar al siguiente:
      http://excelforo.blogspot.com.es/2010/07/vba-grafico-dinamico-con-autoescalado.html
      en la última parte aparece la instrucción para el Escalado del Eje.. sólo tendrías que modificar los valores del ejemplo por:
      =Range(celda1).Value
      =Range(celda2).Value
      celda1 y celda2 serán las referencias de celdas donde tengas ese mínimo y máximo.

      Slds

      Eliminar
    2. Hola Ismael, Gracias por su pronta respuesta, pero le comento que no he podido, por otra parte le comento que no soy muy avanzado en excel. Este es el código que he colocado, donde los valores que colocan en la celda es en Q2 y Q3:

      'macro que genera un gráfico adaptado al número de valores, ajustando su escalado.
      Sub Macro1()
      'definimos las variables que emplearemos en el desarrollo de la macro
      Dim max, min As Variant
      Dim valores As Variant
      Dim nombrehoja, rangocelda As String

      'definimos los cálculos que permitirán el ajuste automático del escalado del gráfico.
      max = Application.WorksheetFunction.max(Range("Q2:Q").Value)
      min = Application.WorksheetFunction.min(Range("Q3:Q").Value)

      'terminamos con el desarrollo que proporciona el escalado del eje Y.
      With ActiveChart.Axes(xlValue)
      .MinimumScale = min * 0.95
      .MaximumScale = max * 1.05
      End With

      'por último la macro nos devuelve a una celda activa de la Hoja de cálculo activa
      Sheets(nombrehoja).Range(rangocelda).Select
      End Sub

      Que estaré haciendo mal?

      Eliminar
    3. Hola, así a primera vista, y leyendo el primer mail, las líneas donde defines que es max y min
      'definimos los cálculos que permitirán el ajuste automático del escalado del gráfico.
      max = Range("Q2").Value
      min = Range("Q2").Value

      y por último la última línea
      Sheets(nombrehoja).Range(rangocelda).Select

      no has definido qué es 'nombrehoja' ni 'rangocelda'... por lo que también te fallará

      Revisa el link que te adjunté.
      Saludos

      Eliminar
    4. Hola Ismael, le comento que coloco el siguiente código y él no me está tomando los valores:

      Sub Macro1()
      'definimos las variables que emplearemos en el desarrollo de la macro
      Dim max, min As Variant
      Dim valores As Variant
      Dim nombrehoja, rangocelda As String

      'asignamos a las variables nombrehoja y rangocelda un valor
      nombrehoja = ActiveSheet.Name
      rangocelda = ActiveCell.Address
      fin = Cells(Worksheets("Nivel1").Range("Q" & Rows.Count).End(xlUp).Row, 1).Row
      rngtit = "Q2:Q" & fin
      rngdat = "Q3:Q" & fin

      'definimos los cálculos que permitirán el ajuste automático del escalado del gráfico.
      max = Range("Q2").Value
      min = Range("Q3").Value

      'por último la macro nos devuelve a una celda activa de la Hoja de cálculo activa
      Sheets(nombrehoja).Range(rangocelda).Select
      End Sub

      El rango de la tabla es el siguiente: 'Nivel1'!$B$19:$M$50

      Mil gracias y espero que me tengas paciencia ya que no soy muy avanzado

      Eliminar
    5. Hola,
      sólo te queda aplicar esas variables que has compuesto: max y min al gráfico que tengas construido.
      Revisa el link de la primera contestación.

      Saludos cordiales

      Eliminar
  2. Buen día, tengo en una hoja de Excel una lista de alumnos con CI, Apellido, Nombre, Lugar de Nacimiento, Localidad si es extranjero(ex) y País de Origen si es Extranjero(ex). Me piden localizar la posición donde se encuentra y el país de origen.
    Eje.
    20 - República de Colombia
    25 - República del Ecuador
    31 - República de Perú
    si me pueden ayudar. Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      desconozco el criterio a aplicar o qué estarías buscando.. no queda claro en tu comentario.
      Pero en general para realizar búsquedas de este estilo se emplean las funciones COINCIDIR e INDICE anidadas. En primer lugar localizamos la posición dentro de un rango con COINCIDIR (quizá en tu caso sobre la C.I.), y una vez tenemos la posición del rango anidamos en INDICE sobre el rango de País.
      Saludos cordiales

      Eliminar
  3. Hola Ismael, que pena molestar tanto, esto colocando el rango como me indicas y el max y el min son las celdas max= Q2 y min= R2 mi hoja en en excel se llama Nivel1 y la información del gráfico va desde las celdas B19 a M50.

    Ya he revisado tu código y coloco el siguiente código pero sigue sin funcionarme, que estaré haciendo mal?

    Sub Macro1()
    'definimos las variables que emplearemos en el desarrollo de la macro
    Dim max, min As Variant
    Dim nombrehoja, rangocelda As String

    'asignamos a las variables nombrehoja y rangocelda un valor

    nombrehoja = ActiveSheet.Name
    rangocelda = ActiveCell.Address
    fin = Cells(Worksheets("Nivel1").Range("R" & Rows.Count).End(xlUp).Row, 1).Row
    rngtit = "Q2:Q" & fin
    rngdat = "R2:R" & fin

    'definimos los cálculos que permitirán el ajuste automático del escalado del gráfico.

    max = Range("Q2:Q").Value
    min = Range("R2:R").Value

    'por último la macro nos devuelve a una celda activa de la Hoja de cálculo activa

    Sheets(nombrehoja).Range(rangocelda).Select
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola,
      a parte de definir correctamente max y min, te queda,como ya te indicaba , aplicarlo al gráfico que tengas construido...
      Esta parte
      With ActiveChart.Axes(xlValue)
      .MinimumScale = min
      .MaximumScale = max

      .MinorUnit = unit / valores
      .MajorUnit = unit / valores
      End With

      Revisa el código, línea por línea del link.
      Saludos

      Eliminar
    2. Hola Ismael, mil gracias, te comento que no he podido, mi gráfica tiene 11 series, pero una de las series es de dispersión. sera que por el tipo de gráfico no me sale este código? te comento que no soy muy avanzado en excel,con el código siguiente me sale el error donde me indica que el error esta en la linea:

      fin = Cells(Worksheets("Nivel1").Range("R" & Rows.Count).End(xlUp).Row, 1).Row

      El código que estoy utilizando es el siguiente:

      Sub Macro1()
      'definimos las variables que emplearemos en el desarrollo de la macro

      Dim rngtit, rngdat As String
      Dim fin As Integer
      Dim max, min As Variant
      Dim unit, valores As Variant
      Dim nombrehoja, rangocelda As String

      'asignamos a las variables nombrehoja y rangocelda un valor

      nombrehoja = ActiveSheet.Name
      rangocelda = ActiveCell.Address

      fin = Cells(Worksheets("Nivel1").Range("R" & Rows.Count).End(xlUp).Row, 1).Row

      rngtit = "Q2:Q" & fin
      rngdat = "R2:R" & fin

      'definimos los cálculos que permitirán el ajuste automático del escalado del gráfico.


      max = Range("Q2:Q").Value
      min = Range("R2:R").Value

      With ActiveChart.Axes(xlValue)
      .MinimumScale = min
      .MaximumScale = max
      .MinorUnit = unit / valores
      .MajorUnit = unit / valores
      End With
      'por último la macro nos devuelve a una celda activa de la Hoja de cálculo activa

      Sheets(nombrehoja).Range(rangocelda).Select
      End Sub

      Eliminar
    3. Hola,
      envíame el fichero a
      excelforo@gmail.com
      lo revisaré y te diré algo...
      Slds

      Eliminar
  4. Hola Ismael, gracias, acabe de enviarte el fichero.

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.