Janela deslizante variável no Excel

0

Estou usando o Excel 2010, mas presumo que isso seja resolvido da mesma maneira em qualquer versão do Excel.

Eu tenho dados que gostaria de somar - essencialmente fornecendo um valor cumulativo deslizante. Por exemplo, em uma tabela com 1.000 linhas, gostaria de uma coluna mostrando a soma do valor da linha atual e as linhas X anteriores, em que X é um número que gostaria de especificar em outra célula. Por exemplo, o último valor cumulativo de 6 linhas ou o último 5 ou 8 ou o que quer que seja.

Eu tentei várias combinações usando ADDRESS e INDIRECT, INDEX, etc. - mas nada parecia funcionar.

Aqui está um exemplo, mostrando os resultados desejados, onde o tamanho da janela é 2:

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

e para o tamanho da janela 3

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

Como eu disse, gostaria que o tamanho da janela fosse especificado em outra célula da planilha (por exemplo, "WindowSize").

Se o tamanho da janela é tal que iria além dos dados válidos, seria bom se retornasse um valor em branco ou 0 - mas isso não é essencial. Não me importo de ignorar #Refs ou ajustar manualmente o início da coluna para explicar isso.

Eu gostaria de acreditar que isso pode ser feito com uma fórmula e sem ter que recorrer ao visual básico. Pode?

Obrigado Yosh

    
por yosh m 03.05.2016 / 16:29

3 respostas

1

Aqui está o método do Índice:

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

ÍNDICE é uma função não volátil, pois só recalcula quando os dados a que se refere mudam.

Os dois OFFSET () e INDIRECT () são funções voláteis. A função volátil calcula todas as vezes que o Excel recalcula. Então, se há muitos deles, isso atrasa os cálculos com cálculos desnecessários.

    
por 03.05.2016 / 18:44
1

Isso deve funcionar na célula B2 :

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

O valor da janela está na célula $D$2 .

    
por 03.05.2016 / 17:04
1

Eu usaria a função de deslocamento

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

em que D1 tem o número de linhas.

    
por 03.05.2016 / 18:26