poner - Gráficos de Excel: la configuración de la serie termina dinámicamente

ordenar grafico ascendente excel 2010

Question

Tengo una hoja de cálculo con muchos gráficos y una hoja con muchos datos que alimentan esos gráficos.

He trazado los datos en cada gráfico usando

=Sheet1!$C5:$C$3000

Esto básicamente traza los valores en C5 a C3000 en un gráfico.

Regularmente, solo quiero ver un subconjunto de los datos, es decir, tal vez solo quiera ver las primeras 1000 filas, por ejemplo. Actualmente para hacer esto, tengo que modificar la fórmula en cada uno de mis gráficos, lo que lleva tiempo.

¿Conoces una manera de simplificar esto? Idealmente, si pudiera simplemente tener una celda en una sola hoja de la que lea en el número de fila y trazar todos los gráficos de C5 a C, sería mejor ''número de fila''.

Cualquier ayuda sería muy apreciada.

Answer 1

+ 1s para la solución de nombre.

Tenga en cuenta que los nombres realmente no realmente hacen referencia a los rangos, sino que hacen referencia a las fórmulas . Es por eso que puede establecer un nombre para algo como "= OFFSET (...)" o "= COUNT (...)". Puede crear constantes con nombre, simplemente haga que el nombre haga referencia a algo así como "= 42".

Las fórmulas con nombre y las fórmulas de matriz son las dos técnicas de hoja de cálculo que me encuentro aplicando una y otra vez a las hojas de cálculo no del usuario.

Answer 2

Podrías mirar rangos dinámicos. Si usa la función OFFSET, puede especificar una celda inicial y la cantidad de filas y columnas para seleccionar. Este sitio tiene información útil sobre cómo asignar un nombre a un rango de DESPLAZAMIENTO.

Answer 3

Puede establecer el rango de un gráfico dinámicamente en Excel. Puede usar algo como el siguiente código VBA para hacerlo:

Private Sub Worksheet_Change(ByVal Target as Range)
    Select Case Target 
    Case Cells(14, 2)
        Sheet1.ChartObjects(1).Chart.SetSourceData Range("$C5:$C$" & Cells(14,2))
    ...
    End Select
End Sub

En este caso, la celda que contiene el número de la última fila para incluir es B14 (recuerde la fila primero cuando se refiera al objeto Cells). También podría usar una variable en lugar de la referencia de Celdas si quisiera hacer esto completamente en código. (Esto funciona tanto en 2007 como en 2003.) Puede asignar este procedimiento a un botón y hacer clic en él para actualizar su gráfico una vez que actualice la celda que contiene la última fila.

Sin embargo, esto puede no ser exactamente lo que desea hacer ... No conozco una forma de usar una fórmula directamente dentro de un gráfico para especificar los datos de origen.

Editar: Y como PConroy señala en un comentario, puede poner este código en el evento Cambiar para esa hoja de trabajo, de modo que ni un botón ni una combinación de teclas sean necesarios para ejecutar el código. También puede agregar código para que actualice cada gráfico solo cuando se edite la celda correspondiente.

He actualizado el ejemplo anterior para reflejar esto.

Answer 4

Esto se puede lograr en dos pasos:

• Crear un rango con nombre dinámico
• Agregue un código VBA para actualizar el origen de datos de gráficos al rango con nombre

Crear una dinámica llamada Rango

Ingrese el número de filas en su rango de datos en una celda en su hoja de datos.

Cree un rango con nombre en su hoja de datos (Insertar - Nombre - Definir) llamado MyRange que tiene una fórmula similar a esta:

=OFFSET(Sheet1!$A$1,0,0,Sheet1!$D$1,3)

Actualiza la fórmula para que coincida con tu diseño

• Sheet1! $ A $ 1 configura esto en la parte superior izquierda de tu rango de datos
• Sheet1! $ D $ 1 establece esto en la celda que contiene el número de filas
• 3 establecer este valor para el número de columnas

Pruebe que el rango nombrado funciona:

Seleccione los menús desplegables Editar - Ir a, escriba MyRange en el campo de referencia. Su área de datos para el gráfico debe ser seleccionada.

Agregue un código VBA

Abra el IDE de VBA (Alt-F11)

Seleccione Sheet1 en la ventana de VBAProject e inserte este código

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Address <> "$D$1" Then Exit Sub
    ''Change $D$1 to the cell where you have entered the number of rows
    ''When the sheet changes, code checks to see if the cell $D$1 has changed

       ThisWorkbook.Sheets("Sheet1").ChartObjects(1).Chart.SetSourceData _
         Source:=ThisWorkbook.Sheets("Sheet1").Range("MyRange")
    ''  ThisWorkbook.Sheets("Chart1").SetSourceData _
         Source:=ThisWorkbook.Sheets("Sheet1").Range("MyRange")
    ''The first line of code assumes that chart is embedded into Sheet1
    ''The second line assumes that the chart is in its own chart sheet
    ''Uncomment and change as required

    ''Add more code here to update all the other charts

End Sub

Cosas que observar

No utilice directamente el rango con nombre como fuente de datos para el gráfico. Si ingresa el rango con nombre "MyRange" como Datos de origen - Rango de datos para el gráfico, Excel convertirá automáticamente el rango nombrado en un rango real. Cualquier cambio futuro en su rango con nombre, por lo tanto, no actualizará su gráfico.

El rendimiento puede verse afectado por los enfoques enumerados anteriormente.

La función OFFSET en el rango con nombre es "volátil", lo que significa que se vuelve a calcular cada vez que se calcula una celda en el libro de trabajo. Si el rendimiento es un problema, reemplácelo con la fórmula INDEX .

=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,Sheet1!$D$1,2)

El código se dispara cada vez que se cambian datos en Sheet1. Si el rendimiento es un problema, cambie el código para que se ejecute solo cuando se solicite (es decir, a través de un botón o menú).

Answer 5

OK, tuve que investigar un poco más, así es cómo hacer que funcione, completamente dentro de la hoja de cálculo (sin VBA):

Usando A1 como el final de su rango deseado, y el cuadro está en la misma hoja que los datos:

Nombre la primera celda de los datos (C5) como un rango con nombre, digamos TESTRANGE.
Creó un rango nombrado MYDATA como la siguiente fórmula:

= DESPLAZAMIENTO (TESTRANGE, 0, 0, Sheet1! $ A $ 1, 1)

Ahora, vaya a la pestaña SERIE del cuadro de diálogo DATOS FUENTE del gráfico, y cambie su declaración VALORES a:

= Sheet1! MYDATA

Ahora, cada vez que cambie el valor de la celda A1, cambiará la tabla.

Gracias a Robert Mearns por atrapar los defectos en mi respuesta anterior.

Answer 6

Una manera fácil de hacerlo es simplemente ocultar las filas / columnas que no desea incluir: cuando va al gráfico, excluye automáticamente las filas / columnas ocultas.