09 jul

Calculando a média dos últimos vinte lançamentos em planilha na nuvem

Muitas vezes, ao desenvolver nossas atividades nos deparamos com planilhas de cálculo onde são inseridos dados diariamente e em algumas destas situações, precisamos que seja calculada uma média móvel dos valores inseridos nelas. Digamos, por exemplo, a média dos 20 últimos valores inseridos.

Esta seria uma tarefa relativamente fácil usando o VBA (macros), contudo resolvendo desta forma estaríamos adicionando um problema.

Qual problema?

A planilha não funcionaria corretamente em alguns cenários, por não permitirem a execução de macros, por exemplo: Tablets Surface RT, Smartphones e Excel Online (nuvem).

Para que funcione em qualquer um destes cenários, precisamos usar exclusivamente fórmulas do Excel!

A solução é a fórmula abaixo:

=ARREDONDAR.PARA.CIMA(MÉDIA(ÍNDICE(K:K; CORRESP(1E+99; K:K)-20):ÍNDICE(K:K; CORRESP(1E+99; K:K)));2)

Agora passo a explica-la, passo-a-passo:

=ARREDONDAR.PARA.CIMA( … ;2) -> Apenas arredonda o resultado para duas casas decimais;

MÉDIA( … : … ) -> Calcula a média das células que estão entre as duas referências;

ÍNDICE( … ; … ) -> o primeiro item identifica a coluna a ser verificada, o segundo identifica a linha, em resumo, retorna uma referência de célula

CORRESP( … ; …) -> retorna a linha onde se encontra o número indicado na primeira parte, dentro da coluna indicada na segunda parte. A busca inicia na última linha digitada e vai subindo, caso não encontre o número exato, refaz buscando o mais próximo. Como o número informado na solução é muito inferior a zero e não será localizado, retornará a linha do último número inserido.

Espero que tenha ficado claro, qualquer dúvida é só postar!

Contador, Analista e Desenvolvedor de Sistemas, com especializações em Contabilidade, Finanças e Análise de Dados, além de mestrado em Contabilidade e Finanças e cursos de extensão em instituições de ensino internacionais, nas áreas financeira e de análise de dados (Yale University, University of Michigan e Johns Hopkins University). Professor Universitário de Graduação em Porto Alegre e Pós-Graduação nas cidades de Porto Alegre, Caxias do Sul, Osório e Miami (USA), palestrante em diversos eventos no Brasil e Estados Unidos, desde 2005. Master Coach Trainer, membro da ICF Brasil e IAC. Já treinou mais de 10.000 pessoas, desde o ano 2.000, no Brasil e nos EUA. Saiba mais na página Sobre.

Leave A Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

onze + quatro =