Análise de Resultados! (fórmulas: Indireto, SomarProduto e SomaSe)

Oi Pessoal!

Recebi um comentário no post Índice+Corresp (PROCV mais eficiente!), enviado pelo Rafael Ovelha, falando sobre a fórmula Indireto. Após pesquisar melhor sobre essa fórmula em alguns fóruns, resolvi escrever esse post.

Criei uma planilha usando as fórmulas Indireto, SomarProduto e SomaSe, onde montei uma tabela resumo de despesas por período, com classificação por natureza (tipo de despesa). Quem fizer o download (vide * abaixo) do arquivo vai perceber que se trata de uma tabela simples, onde procurei demonstrar, de forma clara e objetiva, como criar planilhas inteligentes para análise/ controle de resultados.

É claro que podemos elaborar algo bem mais completo e complexo, entretanto, meu principal objetivo aqui no blog é dar uma ideia resumida de Excel avançado e VBA, com exemplos simples, mas que reflitam as atividades do pessoal de Controladoria/ Finanças voltadas para planejamento e controle de orçamento.

Então, vou deixar sempre a parte do “completo e complexo” por conta da criatividade e necessidade individual de cada um dos leitores que acompanham o blog!

* Vocês podem efetuar o download da planilha de apoio clicando aqui!

Bom, vamos começar pela fórmula SomaSe, que na minha opinião é a mais fácil das três.

Usando a tabela abaixo como exemplo, podemos obter o valor total das despesas classificadas como ESCRITÓRIO (na coluna B) no mês de fevereiro (coluna D), escrevendo o seguinte:

=SOMASE(B:B;”ESCRITÓRIO”;D:D)


Nesta expressão, pedimos ao Excel que efetue a soma de todos os valores da coluna D, que foram classificados como “ESCRITÓRIO” na coluna B. Em outras palavras, é como se a fórmula percorre-se toda a coluna B, desde a primeira linha, e considerasse na soma todas as despesas com escritório.

Neste exemplo, o resultado será 8.300, correspondente a soma dos seguintes valores: 5.000 + 1.500 + 1.200 + 600.

Agora vamos entender como funciona a fórmula SomarProduto, calculando o total de despesas com COMISSÕES nos meses de janeiro e fevereiro, conforme figura abaixo.

Para isso, devemos escrever o seguinte:

=SOMARPRODUTO(C1:E1;C9:E9)

Como o próprio nome sugere, a fórmula SomarProduto efetua a soma dos produtos dos intervalos de células que a compõe. Simplificando, podemos dizer que o Excel multiplica cada célula do primeiro range por cada célula do segundo range (células que estejam na mesma ordem nos dois intervalos) e, no final, soma todos os resultados das multiplicações. Assim, podemos concluir que, necessariamente, os dois ranges da fórmula devem possuir o mesmo tamanho.

Para este exemplo, a fórmula acima é equivalente à seguinte expressão matemática:

1×1.500 + 1×100 + 0x1.500 = 1.600

Agora sim, vamos falar da fórmula Indireto!

Quando procurei por essa fórmula nas funções do Excel, encontrei o seguinte texto descritivo: “Retorna uma referência indicada por um valor de texto”.

A partir daí, tive a ideia de usar esta fórmula para montar uma tabela comparativa de despesas por período, vinculando a escolha do mês de análise à posição (coluna) deste mês na planilha.

Exemplificando, como podem reparar na tabela abaixo, o mês de fevereiro encontra-se na coluna G. Então, para obtermos a soma de todos os valores desta coluna, basta escrevermos =SOMA(G:G).

Mas como efetuar uma soma dinâmica, onde poderemos alterar o mês de análise sem precisar alterar a fórmula de soma?

É nesta parte que entra a fórmula Indireto

O que fiz foi vincular cada um dos meses a uma posição (coluna na planilha) e incluir esta referência na soma dos valores, usando a fórmula Indireto. Com isso, a fórmula ficou da seguinte forma: =SOMA(INDIRETO(W7)). Onde o valor da célula W7 representa o range com os valores que queremos somar (G:G).

Obs: reparem que para alterar o valor da célula W7, usei a fórmula Procv, vinculando o mês selecionado a coluna onde estão as despesas desse mês. (para entender melhor como funciona a fórmula Procv, procure por esta fórmula na pesquisa do blog!

Para deixar a fórmula mais inteligente, troquei a fórmula Soma por SomaSe, criando um resumo das despesas por classificação. Além disso, usei a fórmula SomarProduto para calcular o total acumulado do período na coluna R, ou seja, a soma de janeiro até o mês selecionado.

Espero que esse post ajude bastante, principalmente o pessoal de Controladoria, na hora de montar análises para as apresentações mensais de resultado!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

3 Respostas

  1. Não consigo baixar o arquivo no rapidshare.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s