Estou usando o Excel 2010, mas presumo que isso seria resolvido da mesma forma em qualquer versão do Excel.
Tenho dados que gostaria de somar - fornecendo essencialmente um valor cumulativo móvel. Por exemplo, em uma tabela com 1.000 linhas, gostaria de uma coluna mostrando a soma do valor da linha atual e das X linhas anteriores, onde X é um número que gostaria de especificar em outra célula. Por exemplo, o valor cumulativo das últimas 6 linhas ou as últimas 5 ou 8 ou qualquer outra coisa.
Tentei várias combinações usando ADDRESS e INDIRECT, INDEX, etc. - mas nada pareceu 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 tamanho de 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, chamada "WindowSize").
Se o tamanho da janela for tal que ultrapasse os 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 dar conta disso.
Gostaria de acreditar que isso pode ser feito com uma fórmula e sem a necessidade de recorrer ao visual básico. Pode?
Obrigado, Yosh
Responder1
Aqui está o método de índice:
=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))
INDEX é uma função não volátil, pois só recalcula quando os dados a que se refere mudam.
AmbosOFFSET() e INDIRETO()são funções voláteis. A função volátil calcula sempre que o Excel recalcula. Portanto, se houver muitos deles, os cálculos ficarão mais lentos com cálculos desnecessários.
Responder2
Isso deve funcionar na célula B2:
=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))
O valor da janela está em cell $D$2.
Responder3
Eu usaria a função offset
=sum(offset(A2,0,0,D1))
onde D1 contém o número de linhas.