Dica de Excel – Aprenda a criar intervalos que se atualizam automaticamente

Eu gosto muito de fazer estudos financeiros no excel. É um hobby útil e, além disso, eu aprendo muito melhor repetindo o cálculo ou refazendo os passos. Uma das minhas dificuldades era em cálculos históricos com prazos pré-determinados e móveis, p.e. últimos 6 meses, últimos 12 meses, etc. Dependendo do que estava sendo calculado, e quantas vezes, isso torna a montagem de fórmulas e auditoria de cálculos mais trabalhosa e desgastante do que a análise em si. Sem contar a perda de tempo.

Então, para todos que tem curiosidade, costumam montar planilhas com cálculos de ações e outros ativos, ou simplesmente querem aprender algo, vou mostrá-los como criar intervalos de tempo que se atualizam automaticamente conforme você acrescenta novos dados mensais. Para isso, vocês vão conhecer a função DESLOC.

A função em si não realiza nenhum cálculo matemático direto. Como o nome já denuncia, ela desloca uma referência (célula ou células selecionadas) para as coordenadas que são determinadas na sintaxe. Pode parecer boba, mas as fórmulas mais poderosas do excel são justamente funções mais simples usadas em conjunto umas com as outras.

A sintaxe da função é DESLOC(referencia;linhas;colunas;altura;largura)

Onde,

Referencia: é a célula de “base” a partir de onde vamos começar a contar, é o ponto de referência para os outros argumentos.

Linhas: o número de linhas para cima (negativo) ou para baixo (positivo) a partir da referência para onde você quer deslocar a seleção.

Colunas: a mesma ideia que linhas, com o deslocamento para esquerda sendo feito com números negativos e para a direita com números positivos. (tanto linhas quanto colunas, se forem deixadas com o valor zero, indicam que a seleção vai continuar iniciando a partir da referência)

Altura e largura: é o número de linhas (altura) e colunas (largura) para baixo que queremos para a referência selecionada. Pode ser omitida.

E como isso funciona?

Um exemplo prático. A planilha com valores mensais de vários índices que uso.

Planilha IC de indices

Digamos que, para fins de estudo, nós queremos o valor acumulado do IPCA e do CDI dos últimos 6, 12, 24 e 36 meses. Uma forma de fazer seria criarmos uma coluna ao lado, somando 1 em cada valor percentual e, outra coluna multiplicando um mês pelo acumulado do mês anterior. Desta forma:

Aqui já encontramos o primeiro problema. Para cada período (6, 12, 24 e 36) precisaríamos de uma coluna OU teríamos que fazer cada cálculo separadamente, copiando e colando o resultado como valor em outra célula para guardá-lo. O que não é muito prático, além de ter que ser refeito caso haja algum erro, como selecionar meses a mais ou a menos, ou qualquer outro erro deste tipo.

Outra forma, seria manter a coluna com os fatores (a “+1”) e utilizarmos a função MULT, que simplesmente multiplica os valores selecionados. Ficaria assim:

Já melhora um pouco. Mas também temos que fazer uma função para cada período separadamente. Outro problema: quando atualizarmos com os dados do IPCA de fevereiro (ainda não estavam disponíveis na base do Bacen quando escrevi) teremos que alterar TODAS as seleções em TODAS  as funções. São somente quatro, mas no exemplo seriam IPCA e CDI, já sobem para oito. Se resolvermos comparar com o Ibov e mais um ou outro índice a coisa começa a ficar chata. Nem adianta tentar utilizar nomes de intervalos, pois eles teriam que ser alterados também.

Aqui entra o poder de várias funções menores trabalhando em conjunto.

Primeiro, vamos matar a coluna “+1”. Para facilitar o reuso dos intervalos equivalentes aos vários períodos, vamos utilizar a função DESLOC junto com a criação de nomes de intervalo. Assim, ao invés de ter que reescrever a função toda vez que quisermos utilizar os últimos 6 meses em algum cálculo, simplesmente escreveremos IPCA6m. Parece a mesma coisa que disse que era inútil anteriormente, mas espere.

A função ficaria assim (deixei numa célula para ficar mais fácil visualizar):

O ponto de referência é a célula B2 por ser a primeira célula numérica da coluna. Poderia ser qualquer outra, mas complicaria muito os cálculos. Para calcular os últimos 6 meses eu utilizo a função CONT.NÚM. Ela me retorna quantas células no intervalo que contem valores numéricos. Nesta fórmula, utilizei toda a coluna B (B:B), assim ela ignora o título e me diz quantos períodos eu tenho. Notem que no final da função foi incluído um “-6”, para que a fórmula ajuste para o período que eu quero, no caso os 6 últimos. Aqui você vai incluir 12 para 12 meses, e assim por diante em cada intervalo.

O argumento colunas fica zerado, pois os dados estão na mesma coluna da referência. O último argumento, altura, tem o mesmo valor do “fator de correção” que usamos na fórmula do cont.núm. Estamos dizendo pro excel que queremos que a seleção tenha 6 linhas de altura.

Mas o que exatamente esta fórmula faz? Ela busca o número total de linhas com resultado numérico, e seleciona os 6 últimos. Parece muito mais trabalhoso que as soluções iniciais, mas esta você fará somente uma vez, ainda mais se incluir como um intervalo nomeado e utilizá-la em quaisquer outras fórmulas. Quando eu incluir o valor de fevereiro, o intervalo será atualizado automaticamente sem nenhum esforço além da inclusão do dado, atualizando todos os outros resultados que utilizem o intervalo de 6 meses.

Antes que me esqueça: apesar de ter demonstrado a fórmula na mesma aba que os dados, o ideal é que a fórmula fique em uma aba separada. Na pior das hipóteses em uma coluna ao lado dos dados. Como a função cont.núm busca valores numéricos, ao colocá-la na mesma coluna onde estão os dados que você quer buscar gera um erro de referência redundante.

Para incluí-la como um intervalo com nome, vamos na guia “Fórmulas” e em “Definir nome”, conforme ilustrado abaixo:

Espero que esta dica ajude tanto quanto me ajudou. Com os cálculos sendo feito mais rapidamente poderei me dedicar mais a análise dos resultados ao invés de ficar perdendo tempo com as fórmulas.

Esta dica foi adaptada de um capítulo do livro Modelagem Financeira com Excel, de Juan Carlos Lapponi.