tabla - Selección de rango dinámico de gráfico de Excel

graficos dinamicos independientes

Question

Tengo un cliente que tiene una solicitud simple pero complicada para una configuración de hoja de Excel, y no puedo por el mundo de por dónde empezar. Estoy dibujando un espacio en blanco.

Tenemos un rango de datos. Ejemplo:

Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1

Tengo una tabla construida encima de eso. Cambie los datos, los cambios en los gráficos, proteja la hoja de trabajo para evitar que otros idi ... er ... usuarios cambien los datos antiguos. Sencillo.

Lo que quiero que suceda: cuando agregue el próximo Q1 por debajo del Q4, el gráfico "automágicamente" selecciona el 4Q más reciente. Entonces cuando actualizo la información a:

Quarter Data
2010Q1 1
2010Q2 3
2010Q3 4
2010Q4 1
2011Q1 7

El gráfico mostrará los datos de los últimos 4 trimestres (2010Q2 hasta 2011Q1). El objetivo es mantener los datos "antiguos" en la misma hoja, pero hacer que los gráficos se actualicen en los trimestres más recientes.

Estoy pensando en ubicaciones de datos "fijos", invierta los datos (nuevos datos en la parte superior) e inserte una fila cada trimestre nuevo:

Quarter Data
2011Q2 9
2011Q1 7
2010Q4 1
2010Q3 4
2010Q2 3
2010Q1 1

Pero esto implicará muchos cambios en las hojas de Excel ya existentes y esperaba que haya una "solución" más fácil / mejor.

EDITAR: @Lance Roberts ~ Corriendo con su sugerencia:
- Poco más detalles ... Los datos se configuran de manera tal que la información de la columna está en A, pero los datos para varias tablas están en B +. La Tabla 1 es B / C. Table2 es D / E. Etc.
- Los datos también están en una hoja diferente a las tablas.

Pasando por: Esta Descripción de Compensación , lo que he intentado hacer es ajustar similar a tal:

NAME FORMULA OFFSET(range, rows, columns, height, width )
DATA0 =OFFSET(''DATASHEET''!$A$2, COUNTA(''DATASHEET''!$A:$A - 8, 0, 8, 1)
DATA1 =OFFSET(''DATASHEET''!$A$2, COUNTA(''DATASHEET''!$A:$A - 8, 1, 8, 1)
DATA2 =OFFSET(''DATASHEET''!$A$2, COUNTA(''DATASHEET''!$A:$A - 8, 2, 8, 1)

El objetivo es unir la longitud / ubicación de los datos B / C / etc a A. Entonces, si agrego una columna en A, las cosas vinculadas a Data1 / 2 se ajustan en consecuencia (o 3/4/5 / etc, que son gráficos diferentes en diferentes hojas)

Quiero que las celdas de datos sean recogidas por la primera fila, y luego un número de desplazamiento para obtener datos x columnas por encima. Las variaciones en la fórmula no parecen estar funcionando.

1 problema que aún no he resuelto: los datos no se alinean correctamente:

"Datos" es siempre, última columna debajo del segundo al último trimestre. El último trimestre siempre está vacío. Los datos se desplazan hacia la derecha (en este ejemplo, bajo 3Q10 - NO debajo de la columna correcta. 11 debe estar debajo de 4Q10. 9.5 debe estar debajo de 2Q10).

Sé que estoy obteniendo algo simple mal ...

Parece estar funcionando. Lo primero que tuve que cambiar fue CountA - 9 (no CountA - 8). El siguiente fue el "desplazamiento de columna" (0, 1, 2, 3, ...). También divida algunas cosas para hacerlo más compartimentado (tengo que capacitar a otra persona sobre cómo hacer esto para sus necesidades de informes).

Gracias Lance :)

Answer 1

Si el cuadro está en la misma hoja que los datos:

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

=OFFSET(TESTRANGE, COUNTA($A:$A) - 5, 0, 4, 2)

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 agrega una nueva fila, cambiará el gráfico.

Answer 2

Sé que esta es una vieja pregunta, pero quería compartir una alternativa que puede ser más fácil.

Cambie su rango de datos cuatrimestrales a una tabla de Excel. Seleccione el rango y presione Ctrl + T. En la Tabla de inserción, asegúrese de que esté seleccionado el rango de datos correcto, y que Mi tabla tenga encabezados esté marcada, y presione OK. Esto convierte el rango simple en una estructura de datos especial con propiedades mágicas.

Luego crea un nuevo rango que enlace a las últimas cuatro filas de esta tabla y crea un gráfico basado en este nuevo rango. Esto se ilustra a continuación. La tabla es el rango con formato especial en A1: B9 (puede elegir un estilo menos en la cara), y el rango de trazado es D1: E5.

Las fórmulas en las celdas D2 a D5 están debajo. Copie D2: D5 y pegue en E2: E5 para completar las fórmulas en nuestro rango de trazado.

D2: = ÍNDICE (Tabla1 [Trimestre], FILAS (Tabla1 [Trimestre]) - 3) D3: = ÍNDICE (Tabla1 [Trimestre], FILAS (Tabla1 [Trimestre]) - 2) D4: = ÍNDICE (Tabla1 [Trimestre], FILAS (Tabla 1 [Trimestre]) - 1) D5: = INDICE (Tabla1 [Trimestre], FILAS (Tabla1 [Trimestre]))

Table1 es el nombre asignado a la Tabla, y Quarter es el nombre de la primera columna de la Tabla (y también el encabezado de la columna). No necesita escribir todo esto, solo seleccione la columna en la Tabla. A medida que la Tabla se expande o se contrae, la Tabla 1 [Trimestre] realiza un seguimiento de los cambios.

Ahora agrega un nuevo punto de datos. La Tabla se expande, y nuestra pequeña área de ensayo en D1: E5 se vincula a las últimas cuatro filas de la tabla.

Y a medida que agregamos años de datos, las fórmulas y el cuadro continúan.