Ventana corrediza variable en Excel

Question

Estoy usando Excel 2010, pero supongo que esto se solucionaría igual en cualquier versión de Excel.

Tengo datos que me gustaría resumir, esencialmente proporcionando un valor acumulativo móvil. Por ejemplo, en una tabla con 1000 filas, me gustaría una columna que muestre la suma del valor de la fila actual y las X filas anteriores, donde X es un número que me gustaría especificar en otra celda. Por ejemplo, el valor acumulativo de las últimas 6 filas o las últimas 5 u 8 o lo que sea.

Probé varias combinaciones usando DIRECCIÓN e INDIRECTO, ÍNDICE, etc., pero nada pareció funcionar.

A continuación se muestra un ejemplo que muestra los resultados deseados, donde el tamaño de la ventana es 2:

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |3
4 |15    |17
5 |10    |25
6 |11    |21
7 | 8    |19

y para ventana tamaño 3

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |
4 |15    |18
5 |10    |27
6 |11    |36
7 | 8    |29

Como dije, me gustaría que el tamaño de la ventana se especifique en otra celda de la hoja de cálculo (por ejemplo, denominada "Tamaño de ventana").

Si el tamaño de la ventana es tal que iría más allá de los datos válidos, sería bueno si devolviera un valor en blanco o 0, pero eso no es esencial. No me importa ignorar #Refs o ajustar manualmente el comienzo de la columna para tener en cuenta eso.

Me gustaría creer que esto se puede hacer con una fórmula y sin tener que recurrir a visual basic. ¿Puede?

Gracias, yosh

Answer 1

Aquí está el método de índice:

=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))

ÍNDICE es una función no volátil, ya que solo se recalcula cuando cambian los datos a los que hace referencia.

AmbosDESPLAZAMIENTO() e INDIRECTO()son funciones volátiles. La función volátil calcula cada vez que Excel vuelve a calcular. Entonces, si hay muchos, los cálculos se ralentizan con cálculos innecesarios.

Answer 2

Esto debería funcionar en el celular B2:

=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))

El valor de la ventana está en la celda $D$2.

Answer 3

Yo usaría la función de compensación.

=sum(offset(A2,0,0,D1))

donde D1 tiene el número de filas.