Cambiar el rango de una gráfica desde una fórmula/código

peguren · el 9 de mayo

Muy buenas,

estoy tratando de dado la siguiente tabla y en función de 3 parámetros que yo seleccione se dibuje automaticamente una grafica con los datos elegidos.

[TABLE=width: 800]

[TR]

[TD]Defecto: D3[/TD]

[TD=class: xl65][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]Fecha: 03-may[/TD]

[TD=class: xl66][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]Periodo: 4

Con estos 3 parámetros seleccionados, no sé como hacerle llegar al gráfico los valores pa ra que muestre solamente los datos en color azul. Entiendo que debería pasarle valores como los siguientes al gráfico

Serie =Tableo!$B$4:$J$4

Etiquera = =Tableo!$D$1:$H$1[/TD]

[TD=class: xl65][/TD]

[/TR]

[/TABLE]

[TABLE=width: 800]

[TR]

[TD=class: xl67, width: 80]Defecto[/TD]

[TD=class: xl68, width: 80, align: right]01-may[/TD]

[TD=class: xl68, width: 80, align: right]02-may[/TD]

[TD=class: xl68, width: 80, align: right]03-may[/TD]

[TD=class: xl68, width: 80, align: right]04-may[/TD]

[TD=class: xl68, width: 80, align: right]05-may[/TD]

[TD=class: xl68, width: 80, align: right]06-may[/TD]

[TD=class: xl68, width: 80, align: right]07-may[/TD]

[TD=class: xl68, width: 80, align: right]08-may[/TD]

[TD=class: xl68, width: 80, align: right]09-may[/TD]

[/TR]

[TR]

[TD=class: xl67]D1[/TD]

[TD=align: right]50[/TD]

[TD=align: right]40[/TD]

[TD=align: right]30[/TD]

[TD=align: right]20[/TD]

[TD=align: right]10[/TD]

[TD=align: right]5[/TD]

[TD=align: right]0[/TD]

[/TR]

[TR]

[TD=class: xl67]D2[/TD]

[TD=align: right]2[/TD]

[TD=align: right]0[/TD]

[TD=align: right]23[/TD]

[TD=align: right]45[/TD]

[TD=align: right]68[/TD]

[TD=align: right]12[/TD]

[TD=align: right]40[/TD]

[TD=align: right]15[/TD]

[TD=align: right]20[/TD]

[/TR]

[TR]

[TD=class: xl67]D3[/TD]

[TD=align: right]25[/TD]

[TD=align: right]23[/TD]

[TD=align: right]56[/TD]

[TD=align: right]26[/TD]

[TD=align: right]56[/TD]

[TD=align: right]45[/TD]

[TD=align: right]78[/TD]

[TD=align: right]10[/TD]

[TD=align: right]6[/TD]

[/TR]

[TR]

[TD=class: xl67]D4[/TD]

[TD=align: right]52[/TD]

[TD=align: right]256[/TD]

[TD=align: right]63[/TD]

[TD=align: right]25[/TD]

[TD=align: right]30[/TD]

[TD=align: right]20[/TD]

[TD=align: right]10[/TD]

[/TR]

[TR]

[TD=class: xl67]D5[/TD]

[TD=align: right]23[/TD]

[TD=align: right]12[/TD]

[TD=align: right]23[/TD]

[TD=align: right]10[/TD]

[TD=align: right]2[/TD]

[TD=align: right]5[/TD]

[TD=align: right]0[/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[TD=class: xl65][/TD]

[TD][/TD]

[/TR]

[/TABLE]

Muchas gracias de antemano por la ayuda [TABLE=width: 800]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[TR]

[TD][/TD]

[/TR]

[/TABLE]

serviprofer · el 9 de mayo

Hola, Peguren.

Aunque yo creo entender bastante el enunciado de tu problema, recuerda que una de las reglas de este foro es que subas un archivo de ejemplo, aunque sea con datos ficticios, más de un colaborador te insistirá en eso.

Yo lo haría mediante programación sigiuiendo estos pasos:

1. Los datos de entrada.

Acá te comento que no es clara la relación que hay entre tu dato "Período" y la tabla de datos que muestras. Si realmente es un hecho que del valor de esa variable "período" va a depender los datos que quieres tomar entonces debes explicar cuál es la relación.

Todos esos valores que reportas como tu entrada los colocaría en varias celdas en una hoja.

2. Mediante una macro o un botón ActiveX insertado en la hoja, programaría las cosas de forma que los datos que deben ser graficados de acuerdo a tus valores de entrada se reasignen dinámicamente.

Lo que quieres hacer no es para nada difícil usando programación. No te lo he querido hacer porque cómo no es clara cuál es la relación entre el parámetro "período" y los datos para graficar, lo que te hubiera hecho obviamente no cumpliría completamente con tu requerimiento.

Saludos,

Elio.

peguren · el 10 de mayo

Muchas gracias por tu ayuda Elio,s

adjtunto archivo con datos y una explicación más detallada. La primera vez no pude subirlo porque trataba de cargar un archivo xlsx que no se admitia, soy nuevo en le foro y algo torpe.

Por favor indícame como lo harías si vía codigo o fórmula y envíame el código/fórmula.

Yo he probado con DESREF pero no he conseguido que funcione como quiero.

Muchas gracias una vez más por tu ayuda

Pablo

Prueba.rar

serviprofer · el 10 de mayo

Dicho de otra forma:

"Perído" es el número de días que sumados a la fecha de inicio me dará como resultado la fecha final.

Ya te paso el libro automatizado para que obtengas el gráfico de forma dinámica con sólo presionar un botón ActiveX que estará insertado en tu hoja.

Elio.

- - - - - Mensaje combinado - - - - -

Listo. Problema resuelto. En el adjunto tienes el archivo con el libro de Excel habilitado para macros (.xlsm).

Te comento algunas condiciones bajo las cuales funcionará el código.

1. Trabajará con los defectos desde "D1 hasta "D5", es decir, desde la fila 2 hasta la fila 6 en la columna A. Si deseas en un futuro ampliar la lista de defectos sólo tendrás que modificar el código de programación en el lugar correspondiente.

2. No podrás cambiar el gráfico incrustado y colocarlo como una hoja de gráfico, debe permanecer así, incrustado.

3. Siempre las fechas deben comenzar desde la columna B.

4. No importa cuál sea la última fecha, no importa en cuál columna se encuentre, el código la encontrará.

5. Si cambias la posición de las celdas de los valores de entrada (Cuadro de mando) tendrás entonces que modificar las referencias en el código de programación en el lugar correspondiente.

A continuación te transcribo el código del evento Click del botón ActiveX que se encuetra en la hoja:

Private Sub CommandButton1_Click()

Dim fila As Long, intUltimaColFecha As Integer, strDefectoCuadroMando As String, intFilaDefecto As Long, _

col As Integer, intColFechaInicio As Integer, strFechaInicioCuadroMando As String, intPeriodo As Integer

intUltimaColFecha = Range("B1").End(xlToRight).Column

strDefectoCuadroMando = Range("B8").Value

' Bucle para obtener la fila de coincidencia del defecto:

intFilaDefecto = 0

For fila = 2 To 6 'Estas son las filas de los defectos, es decir, desde A2 hasta A6.

If strDefectoCuadroMando = Range("A" & fila).Value Then

intFilaDefecto = fila

Exit For

End If

Next fila

If intFilaDefecto = 0 Then

MsgBox "El defecto que has colocado en el Cuadro de Mando no existe", vbOKOnly, "Error:"

Else

'Bucle para obtener la columna de la fecha de inicio:

strFechaInicioCuadroMando = Range("B9").Value

For col = 2 To intUltimaColFecha

If strFechaInicioCuadroMando = Cells(1, col).Value Then

intColFechaInicio = col

Exit For

End If

Next col

' GRAFICACIÓN DE FORMA DINÁMICA

intPeriodo = Range("B10").Value

ChartObjects(1).Chart.SeriesCollection(1).XValues = Range(Cells(1, intColFechaInicio), Cells(1, intColFechaInicio + intPeriodo))

ChartObjects(1).Chart.SeriesCollection(1).Values = Range(Cells(intFilaDefecto, intColFechaInicio), Cells(intFilaDefecto, intColFechaInicio + intPeriodo))

End If

End Sub

Saludos,

Elio.

Prueba.rar

peguren · el 10 de mayo

Muchísimas gracias Elio,

esto es lo que estaba buscando!!!

Esto lo quiero aplicar en un Excel en el que los datos están en la pestaña de nombre "Tablero" y en otras pestañas estan los gráficos y el botón Graficar, referenciados a los los datos en el "Tablero". Entiendo que para hacer esto tendría que referenciar todos los Rangos al nombre de la pestaña "Tablero"??? ¿Cómo lo puedo hacer??

Muchas gracias

Pablo

serviprofer · el 12 de mayo

Me alegro.

Si el botón "Graficar" va a estar en una hoja diferente a la hoja donde están los datos, en el código deberás cambiar esto:

intUltimaColFecha = Range("B1").End(xlToRight).Column

por esto:

intUltimaColFecha = WorkSheets("Tablero").Range("B1").End(xlToRight).Column

Te coloco las demás líneas que deberás cambiar:

1. Cambiar:

If strDefectoCuadroMando = Range("A" & fila).Value Then

por:

If strDefectoCuadroMando = WorkSheets("Tablero").Range("A" & fila).Value Then

2. Cambiar:

strFechaInicioCuadroMando = Range("B9").Value

Por:

strFechaInicioCuadroMando = WorkSheets("Tablero").Range("B9").Value

3. Cambiar:

If strFechaInicioCuadroMando = Cells(1, col).Value Then

Por:

If strFechaInicioCuadroMando = WorkSheets("Tablero").Cells(1, col).Value Then

4. Cambiar:

intPeriodo = Range("B10").Value

Por:

intPeriodo = WorkSheets("Tablero").Range("B10").Value

En cuanto a los gráficos, debes indicarme exactamente cómo estarán (los gráficos) finalmente dispuestos en tu libro:

1. ¿Como hoja de gráfico? o,

2. ¿Como un gráfico incrustado en una hoja de cálculo?

El código que te pasé funciona SÓLO para cuando el gráfico está incrustado en una hoja de cálculo.

Si la respuesta es que el gráfico continuará como un gráfico incrustado, PERO, en una hoja diferente a la hoja donde están los datos, como me has comentado, entonces cambia el código para la parte de graficación tal como te muestro a continuación:

' GRAFICACIÓN DE FORMA DINÁMICA

intPeriodo = WorkSheets("Tablero").Range("B10").Value

WorkSheets("Mi nueva hoja de grafico incrustado").ChartObjects(1).Chart.SeriesCollection(1).XValues = WorkSheets("Tablero").Range(Cells(1, intColFechaInicio), Cells(1, intColFechaInicio + intPeriodo))

WorkSheets("Mi nueva hoja de grafico incrustado").ChartObjects(1).Chart.SeriesCollection(1).Values = WorkSheets("Tablero").Range(Cells(intFilaDefecto, intColFechaInicio), Cells(intFilaDefecto, intColFechaInicio + intPeriodo))

COMENTARIOS FINALES:

El 1 que ves en

ChartObjects(1)

en el código anterior para la graficación, significa que te estás refiriendo al primer gráfico que incrustaste en la hoja. En este caso he hecho referencia a una hoja ficticia llamada "Mi nueva hoja de grafico incrustado". Tú cámbia ese nombre por el nombre real de la hoja donde tengas incrustado el gráfico.

Saludos,

Elio.

peguren · el 12 de mayo

Muchas gracias Elio,

ya he hecho los cambios pero en la fase de dibujar el gráfico me hago un lío y no consigo dibujarlo.

Te mando el archivo tal y como lo tengo. Las casillas en amarillo de las hojas individuales son donde están los datos del cuadro de mando, Nombre defecto, Fecha y Periodo.

Los datos estan en el Tablero 2G y para cada defecto se abre una nueva ficha, una vez he creado una lo que suelo hacer es copiar la pestaña mediante el procedimiento Mover/copiar. No sé si esto puede afectar al codigo que me has pasado, yo creo que no, ya que el nombre de la solapa/ficha cambia (es el nº iD que viene en el Tablero 2G y se lo meto a mano).

Muchas gracias una vez más Elio por tu inestimable ayuda.

Saludos

Pablo

Tablero 2G.rar

serviprofer · el 13 de mayo

Oye, Pablo, ahora lo reviso, pero, estoy detectando algo que veo y que no pensé que después lo cambiarías:

Siempre estuve creyendo que las fechas iban a ser fechas continuas pertenecientes a un único mes. Eso puede ser peligroso en el algoritmo que te pasé, porque, si lo analizas bien, lo que hice allí fue sumar el "Período" a la fecha de inicio. Como ahora veo que puedes tener juntas fechas de dos meses diferentes, pienso que eso puede hacer que el algoritmo que te pasé falle.

- - - - - Mensaje combinado - - - - -

Problema resuelto. Me ha dado bastante dolor de cabeza, porque Excel no me daba un error específico, sino sólo un "Error de la aplicación". Tuve que echar mano a un pequeño código para una función, para convertir un número de columna en su letra correspondiente. Eso lo conseguí aquí: Convert a column number to a column letter

La función es la siguiente, la encontrarás en tu módulo de usuario:

Public Function ColNum2Lett(Col As Integer) As String

If Col > 26 Then

ColNum2Lett = ColNum2Lett((Col - (Col Mod 26)) / 26) + Chr(Col Mod 26 + 64)

Else

ColNum2Lett = Chr(Col + 64)

End If

End Function

He decidido cambiar esa parte del algoritmo, por lo que, ahora, lo que hago es que sumo el "Período" a la columna correspondiente a la fecha de inicio, y no como antes, que sumaba el "período" a la fecha de inicio. Asñi ha quedado ahora:

Format(intColFechaInicio + intPeriodo)

De todas formas, si tú llegas a detectar un error de más o menos 1, ya sabes que lo que debes hacer es sumar o restar un 1 a la fórmula de arriba, dentro del paréntesis. Ese trozo de código pertenece a la parte de graficación.

Por acá te coloco el código del botón de comando:

Private Sub CommandButton2_Click()

Dim fila As Long, intUltimaColFecha As Integer, strDefectoCuadroMando As String, intFilaDefecto As Long, _

Col As Integer, intColFechaInicio As Integer, strFechaInicioCuadroMando As String, intPeriodo As Integer, _

rango As Range, x As Integer, strColFechaInicio As String, strColFechaFinal As String, aux As String

intUltimaColFecha = Worksheets("Tablero 2G").Range("C11").End(xlToRight).Column

strDefectoCuadroMando = Range("C29").Value

' Bucle para obtener la fila de coincidencia del defecto:

intFilaDefecto = 0

For fila = 12 To 50 'Estas son las filas de los defectos, es decir, desde A2 hasta A6.

If strDefectoCuadroMando = Worksheets("Tablero 2G").Range("B" & fila).Value Then

intFilaDefecto = fila

Exit For

End If

Next fila

If intFilaDefecto = 0 Then

MsgBox "El defecto que has colocado en el Cuadro de Mando no existe", vbOKOnly, "Error:"

Else

'Bucle para obtener la columna de la fecha de inicio:

strFechaInicioCuadroMando = Range("B32").Value

For Col = 3 To intUltimaColFecha

If strFechaInicioCuadroMando = Worksheets("Tablero 2G").Cells(11, Col).Value Then

intColFechaInicio = Col

Exit For

End If

Next Col

' GRAFICACIÓN DE FORMA DINÁMICA

intPeriodo = Range("D32").Value

strColFechaInicio = ColNum2Lett(intColFechaInicio)

strColFechaFinal = ColNum2Lett(intColFechaInicio + intPeriodo)

aux = strColFechaInicio & Format(intFilaDefecto) & ":" & strColFechaFinal & Format(intFilaDefecto)

ChartObjects(1).Chart.SeriesCollection(1).XValues = Worksheets("Tablero 2G").Range(strColFechaInicio & Format(11) & ":" & strColFechaFinal & Format(11))

ChartObjects(1).Chart.SeriesCollection(1).Values = Worksheets("Tablero 2G").Range(strColFechaInicio & Format(intFilaDefecto) & ":" & strColFechaFinal & Format(intFilaDefecto))

End If

End Sub

Tal parece que de la forma que estaba planteado antes el código para la sección de graficación, se genera un error desconocido cuando los datos de origen y el gráfico NO están en la misma hoja de Excel.

Saludos,

Elio.

- - - - - Mensaje combinado - - - - -

Creo que ya puedes dar el tema por cerrado. Si me preguntas otra cosa adicional es posible que no te responda antes del 16/05 ya que estaré un tanto ocupado.

Tablero 2G.rar