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:
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:
Fuera del área del gráfico añadimos una barra de desplazamiento horizontal (control de formulario) con la siguiente configuración:
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:
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.
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:
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:
Fuera del área del gráfico añadimos una barra de desplazamiento horizontal (control de formulario) con la siguiente configuración:
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:
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.
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?
ResponderEliminarHola,
Eliminarsi 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
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:
Eliminar'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?
Hola, así a primera vista, y leyendo el primer mail, las líneas donde defines que es max y min
Eliminar'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
Hola Ismael, le comento que coloco el siguiente código y él no me está tomando los valores:
EliminarSub 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
Hola,
Eliminarsó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
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.
ResponderEliminarEje.
20 - República de Colombia
25 - República del Ecuador
31 - República de Perú
si me pueden ayudar. Gracias
Hola,
Eliminardesconozco 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
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.
ResponderEliminarYa 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
Hola,
Eliminara 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
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:
Eliminarfin = 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
Hola,
Eliminarenvíame el fichero a
excelforo@gmail.com
lo revisaré y te diré algo...
Slds
Hola Ismael, gracias, acabe de enviarte el fichero.
ResponderEliminar