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.
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.
Caro colega blogueiro!
Conforme prometido disponibilizei hoje a linha de tempo da blogosfera financeira. Seu blog está no estudo. Passe lá depois para ver se está tudo certo.
http://abacusliquid.com/blogosfera/finansfera-timeline/
Um bom fds!
CurtirCurtir
Valeu, AL! Obrigado!
CurtirCurtir
Não achei seu e.mail no blog.
Se puder, manda um e.mail para contabilidadewjr@gmail.com para eu mandar a planilha para você.
Abraço.
CurtirCurtir
ok. Enviado!
Podia jurar que havia deixado este contato no blog…
Um abraço!
CurtirCurtir
Boa noite IC,
Vi sua resposta, muito agradecido novamente. Pois é, gosto muito do Excel, porém me falta um pouco mais de disciplina para estudar a fundo. Gosto também do R – project, onde tive contato em uma cadeira da universidade. Vou melhor me organizar e estudar mais.
Sobre a planilha, consegui fazer, porém um detalhe me intrigou, pois ela me retornou apenas o resultado dos último 11 meses, mesmo eu colocando 12 (-12;0;12). Creio que algum detalhe faltou.
CurtirCurtir
Walter,
Reproduzi o cálculo aqui com 12 meses para saber se deixei algo passar, e tudo funcionou. Veja se não ocorreu algo do tipo o título da coluna ser um número, ou um dos valores estar formatado como texto, ou ainda, um dos valores está com um ponto no lugar da vírgula, o que alteraria o resultado, já que a fórmula conta somente valores numéricos. Caso não funcione, sinta-se a vontade para me enviar a planilha por e-mail, ou explicar como posso replicá-la, ok?
Eu tenho interesse em conhecer estes programas mais específicos para estatística, mas a falta de tempo e disciplina me impediram até hoje. Acabo ficando na minha zona de conforto do excel.
Caso eu consiga, escreverei sobre a experiência aqui!
Um abraço!
CurtirCurtir
Boa noite caro IC;
Hoje refiz a planilha e deu certo, na resposta anterior o IPCA acumulado dos últimos 12 meses não estava fechando com as informações oficiais (BC e outros sites), que é de 10,36% (eita inflação que não baixa viu). A diferença de hoje é que onde tem o título da coluna, estava no formato Geral e apenas alterei para Texto, não sei se era por isso, mas quando alterei deu certo. Eu uso como fonte dos dados o site http://www.ipeadata.gov.br, lá baixo a planilha com toda a série histórica, porém, como é apenas para uso pessoal, somente estou usando dados de Janeiro de 2013 para cá. Vou lhe enviar um e.mail, com a planilha e algumas dúvidas, e ver o que você me sugere.
No mais, muito obrigado e boa semana.
CurtirCurtir
Ok, fico no aguardo!!
Um abraço!
CurtirCurtir
Boa noite caro amigo,
Mas dessa forma, como fica o “acumulado”?
CurtirCurtir
Walter,
Ótima pergunta. Como costumo escrever os textos de uma só fez, algumas vezes acabo me perdendo no meio.
Não me recordo a solução que utilizei na época, e muito provavelmente não possuo mais a planilha.
Porém, uma solução seria usando a fórmula MULT com o intervalo “móvel” adicionado de “+1” como fórmula matricial.
Usando o exemplo do texto, com o intervalo nomeado “IPCA6m”, ficaria assim:
=MULT((IPCA6m+1))
Mais ao invés de apertar ENTER, seria [CTRL]+[SHIFT]+{ENTER] ao mesmo tempo.
Não consigo confirmar se esta solução daria o valor correto, estou supondo de cabeça. Em breve confiro e confirmo. Qualquer coisa faço um novo texto explicando, ok?
Um abraço!
CurtirCurtir
Boa noite IC, obrigado pelo retorno,
Fiz do jeito que você me falou IC, e não obtive o resultado desejado. Estou tentando fazer essa planilha, para um controle de depósito mensal, onde eu corrijo os valores mensalmente a depositar pela inflação acumulada do IGP – DI dos últimos 12 meses. Ficarei grato se puder me ajudar nesse trabalho. Abraço.
CurtirCurtir
Walter,
Acabei de testar aqui novamente, e funcionou. Lembrando que tudo neste texto só funciona se os valores estiverem formatados em percentual. Vou tentar detalhar melhor:
A fórmula que escrevi antes “=MULT(IPCA6m+1)” quando transformada em fórmula matricial (SHIFT+CTRL+ENTER), vai retornar o valor como fator, ou seja, somado de 1. Se a célula estiver formatada para %, o valor estará maior que o correto em 100 p.p. Assim, se o resultado correto for 10%, na célula apareceria 110%, pois a fórmula retornaria 1,1 ao invés de 0,1.
Para corrigir isto, basta modificar a fórmula da seguinte forma “=MULT(IPCA6m+1)-1”. Desta forma, o resultado já estará pronto para ser formatado em percentual.
Não sei se isso ajuda. Caso ainda assim você não consiga, tente me enviar um email detalhando o que está ocorrendo e me avise aqui sobre o envio, pois quase não tenho conferido minha caixa de entrada.
Um abraço!
CurtirCurtir
Bom dia, obrigado IC,
Vi seus post sobre a falta de tempo, e até me identifiquei um pouco em alguns sentidos, mas muito obrigado pelo retorno, tenho que me dedicar mais ao Excel, que tanto gosto, mas ultimamente eu só tenho feito planilhas simples, pois minha demanda aqui é pouca no dia – a – dia. Vou refazer aqui a planilha nesse feriado e lhe posto o resultado, com mais detalhes. Obrigado, e bom feriadão pra você.
CurtirCurtir
Walter,
Sem problemas! Vou continuar respondendo os comentários. Vai ser difícil mesmo é escrever alguma coisa!
Dedique-se sim! É um grande diferencial. Poucas pessoas tem familiaridade com o Excel, e isso acaba sendo um grande fator de destaque, além de facilitar e muito algumas tarefas do cotidiano!
Bom feriado!
Um abraço!
CurtirCurtir