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
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.
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 .
Eu usaria a função de deslocamento
=sum(offset(A2,0,0,D1))
em que D1 tem o número de linhas.