Criando suas próprias fórmulas no Excel

O Excel possui uma quantidade considerável de fórmulas que nos permitem os mais variados tipos de análises. Tudo fica ainda melhor quando podemos combinar duas ou mais fórmulas entre si.

Porém, muitas vezes nos vemos diante de desafios que requerem ainda mais poder de cálculo. É quando surge a necessidade das Funções Definidas pelo Usuário (User Defined Functions), ou seja, as nossas próprias fórmulas.

Um exemplo simples: suponha uma planilha onde seja necessário calcular em vários locais diferentes a comissão de um determinado vendedor, com uma regra simples: se a venda for menor que R$ 50.000,00 a comissão será de 10%, se for maior a comissão será de R$ 5%. Espalhamos esta regra nos vários pontos da planilha e num dado momento a regra muda. O que acontece? Basta alterar a regra num único ponto e toda a planilha será automaticamente atualizada, ou seja, muito melhor do que revisar e reescrever a fórmula em vários lugares.

Como fazer?

Crie uma nova planilha e exiba a lista de macros. Escreva “Comissao” no campo Nome da Macro e clique no botão Criar. Clique no menu Inserir e depois na opção Módulo.

Insira a seguinte fórmula:

Function Comissao(ValorVenda)

    If ValorVenda <= 50000 Then Comissao = ValorVenda * 0.1 Else Comissao = ValorVenda * 0.05

End Function

Feche o editor do Visual Basic e retorne ao Excel.

Crie uma planilha simples, com a lista de vendedores e seus respectivos valores de venda. 

Crie uma nova coluna chamada “Comissão” e insira a fórmula Comissao(A2). Repita a operação para as demais linhas desta coluna e formate o número.

Observa na barra de fórmulas a sintaxe correta. Note que para os três primeiros vendedores a comissão será de 10% (valor abaixo de R$ 50.000,00) e os dois últimos a comissão será de 5% (valor acima de R$ 50.000,00).

Considerações gerais:

• Lembre de salvar o documento como “Pasta de Trabalho Habilitada para Macro do Excel”
• Quer mais ideias e exemplos para suas UDF's? Visite o ozgrid.com
• Criou uma excelente UDF e quer compartilhar aqui no blog? Poste seu comentário.

Pesquisando numa tabela com as funções Match (Corresp) e Offset (Desloc)

As funções Match e Offset, Corresp e Desloc respectivamente em Português, são muito úteis para a pesquisa de dados numa tabela de dados, especialmente quando estamos lidando com grandes volumes de dados.

Para a elaboração dos Dashboards são ainda mais úteis, visto que permitem extrair fatias de dados de uma tabela com base em critérios específicos. Uma vez fatiada a tabela, basta posicionar os dados nos locais desejados.

Apenas para efeitos didáticos, vamos imaginar uma tabela contendo a relação de alunos e as várias disciplinas que estão cursando, com suas respectivas médias para cada matéria.

Para a tabela acima, as informações a serem retornadas serão: 1) Média do aluno numa respectiva matéria; 2) Média geral do aluno; 3) Média geral da matéria. Os critérios utilizados serão: 1) Nome do aluno; 2) Disciplina.

Numa segunda tabela, na área destacada em cinza (Aluno e Matéria), criei duas listas de valores, com base na coluna A (relação de alunos) e na linha 1 (relação de matérias) apenas para facilitar a seleção de valores como critérios de pesquisa.

Na coluna “Nota” incluí a seguinte fórmula:

=DESLOC(A1;CORRESP(A12;A2:A7;0);CORRESP(B12;B1:G1;0))

Explicando:

• CORRESP(A12;A2:A7;0) retorna o número da linha correspondente na coluna Aluno ao valor inserido na célula A12, ou seja, linha 5
• CORRESP(B12;B1:G1;0) retorna o número da coluna correspondente na linha 1 ao valor inserido na célula B12, ou seja, coluna G
• A função Desloc retorna o valor inserido na célula identificada pelas duas fórmulas Corresp, ou seja, o valor inserido na posição G5. Temos então a média do aluno.

 Na coluna “Média geral do aluno” incluí a seguinte fórmula:

=MÉDIA(DESLOC(A1;CORRESP(A12;A2:A7;0);1;1;6))

Explicando:

• A1 fixa o início da pesquisa na célula A1
• CORRESP(A12;A2:A7;0) retorna o número da linha correspondente na coluna Aluno ao valor inserido na célula A12, ou seja, linha 5
• 1 fixa que o início da pesquisa ocorrerá na primeira coluna à direita de A1
• 1 fixa que a pesquisa será realizada apenas em uma linha, no caso a linha 5
• 6 fixa em seis colunas a quantidade de colunas a ser pesquisada
• A fórmula média calcula a média de valores do intervalo A5:G5

 Na coluna “Média geral da matéria” incluí a seguinte fórmula:

=MÉDIA(DESLOC(A1;1;CORRESP(B12;B1:G1;0);6;1))

Explicando:

• A1 fixa o início da pesquisa na célula A1
• 1 fixa que o início da pesquisa ocorrerá na primeira linha abaixo de A1
• CORRESP(B12;B1:G1;0) retorna o número da coluna correspondente na linha A1 ao valor inserido na célula B12, ou seja, coluna G
• 6 fixa em seis linhas a quantidade de linhas a ser pesquisada
• 1 fixa em uma coluna a quantidade de colunas a ser pesquisada
• A fórmula média calcula a média de valores do intervalo G2:G7

Fica claro que as duas fórmulas são complementares e que juntas podem facilitar tremendamente a montagem dos Dashboards, inclusive aqueles que requerem atualização dinâmica por meio de critérios de pesquisa.

Funções SOMASE e CONT.SE com múltiplos critérios de pesquisa

Hoje recebi uma planilha com um dashboard bem interessante: gráficos refinados, informações bem estruturadas e apresentadas, vários resumos de informações mais relevantes.

Resolvi fazer uma “engenharia reversa” da estrutura da planilha e, para minha surpresa, existiam muitas colunas, com vários filtros e somas, para simplesmente criar vários critérios de pesquisa numa única planilha! Muito complicado para fazer, mais complicado ainda para uma outra pessoa manter e quase impossível de explicar...

A solução? Utilizar as funções SOMASES e CONT.SES. Isso mesmo, com um baita “S” no final.

Para a função SOMASES vamos imaginar uma tabela que contenha o lucro individual para cada produto de uma empresa, classificando os produtos quanto ao público (Masculino ou Feminino) e a origem (Fabricação própria ou Revenda).

Suponha que desejamos saber qual é soma do lucro dos produtos para o público Feminino E de Fabricação própria. A fórmula ficaria assim, retornando o valor 24:

=SOMASES(D2:D13;B2:B13;"Feminino";C2:C13;"Fabricação própria")

Para saber quantos produtos se enquadram nos mesmos critérios, a fórmula fica assim, retornando o valor 3:

=CONT.SES(B2:B13;"Feminino";C2:C13;"Fabricação própria")

Este é apenas um exemplo básico, mas, as funções permitem usos mais refinados, como a entrega dos critérios de pesquisa como referência a outras células, inclusão de caracteres curinga para pesquisa apenas parte de uma sentença, entre outros. E acima de tudo, muito mais fácil para organizar seus dashboards.

Calculando dias úteis entre duas datas

Levanta a mão quem ainda não precisou calcular a quantidade de dias úteis entre duas datas!

Todos os caminhos levam à Roma, mas, alguns são mais fáceis. Ao invés de criar fórmulas mirabolantes, que tal utilizar alguma totalmente pronta?

Antes de mais nada, habilite as Ferramentas de Análise do Excel. Clique no menu Ferramentas, Suplementos e marque a opção Ferramentas de Análise. A localização desta opção pode variar dependendo da versão do Excel, então, na dúvida consulta o menu Ajuda.

A primeira fórmula é a DiaTrabalhoTotal, que calcula a quantidade de dias úteis entre duas datas, permitindo excluir os finais de semana e feriados. Explicando resumidamente o assistente de fórmulas temos os seguintes parâmetros:

Data inicial: é a célula que contem a data inicial do período

Data final: é a célula que contem a data final do período

Feriados: é o intervalo de células que contem a relação de datas a serem consideradas como feriados. Este parâmetro é opcional. Como boa prática, quando preciso utilizar este parâmetro, costumo nomear o intervalo de células para facilitar o entendimento. Fica a seu critério.

A segunda fórmula calcula o próximo dia útil após X dias úteis. Muito útil para o cálculo de prazos legais e datas limite. Também permite incluir a lista de feriados. Resumidamente os parâmetros são:

Data inicial: é a célula que contem a data inicial do período

Dias: é a quantidade de dias úteis a serem acrescidos na data inicial

Feriados: idem à primeira fórmula

Descobrindo o trimestre para uma data específica

Na consolidação de dados para criação de dashboards ou até mesmo no dia-a-dia dos profissionais de finanças é bastante comum a necessidade de descobrir a qual trimestre uma determinada data pertence.

Uma fórmula bastante simples resolve este problema. A lógica é: pegue um mês qualquer e acrescente o número 2, divida tudo por 3, faça o arredondamento para baixo e pronto, você tem o trimestre relativo ao mês em questão.

Por exemplo, Janeiro é o mês 1. Portanto, 1 + 2 = 3, dividido por 3 = 1 (primeiro trimestre). Maio é o mês 5. Portanto, 5 + 2 = 7, dividido por 3 = 2,33, que arredondado para baixo = 2 (segundo trimestre). E assim por diante.

Para fazer no Excel são necessárias duas fórmulas:

INT, que arredonda o valor informado em um número inteiro.

MÊS, que retorna em formato numérico o mês relativo à data informada.

A fórmula final fica assim: =INT((MÊS(célula que contem a data, por exemplo, A1)+2)/3)

Gráficos com legendas dinâmicas (legendas com fórmulas)

Você passa horas tabulando dados, diagramando a estrutura de informação, produzindo gráficos, escolhendo cores e quando termina o seu dashboard se depara com aquela “legendinha” sem graça do Excel?

Eu costumo incrementar as legendas com informações mais claras, precisas e direcionadas ao resultado a ser apresentado.

Na tabela abaixo temos as variações de receita, despesa e lucro de uma determinada empresa na última década.

Um gráfico convencional (padrão do Excel, sem nenhuma adaptação) ficaria assim:

Incluí em duas células, por meio do comando Inserir símbolo, uma seta para cima e outra para baixo. Depois incluí uma outra coluna na tabela, acrescentando a seguinte fórmula (adaptando para cada item da planilha): =SE(C2>B2;$H$1;$I$1)&" Receita em "&TEXTO(C2/B2-1;"0%")

Explicando rapidamente: se a variação é positiva, seta para cima. Caso contrário, seta para baixo. A função texto acrescenta o símbolo de percentual e formata as casas decimais. $H$1 e $I$1 são referências para as células que contêm os símbolos. O caracter & concatena tudo num único texto. O resultado final fica assim: 

Quanto ao gráfico, inseri três caixas de texto abaixo de cada item (receita, despesa e lucro). Selecionei a primeira caixa de texto, depois cliquei na barra de fórmulas e apontei como referência a célula que contem a fórmula criada anteriormente. Repeti a operação para os demais itens e o resultado final ficou assim.

Subtotal x Soma

Quando se utiliza dashboards dinâmicos, ou seja, aqueles que possibilitarão interação com o usuário, deve-se tomar alguns cuidados para evitar falhas de comportamento de alguns objetos gráficos.

Um ponto realmente frágil é a utilização da fórmula SOMA para sumarização de dados em tabelas. Uma alternativa interessante e de eficiência garantida é a fórmula SUBTOTAL.

Explicarei com o exemplo abaixo.

Suponha que você tem uma coluna “Quantidade”, com cinco linhas e valores de 1 a 5 distribuídos entre elas.

Acima desta coluna há uma sumarização dos dados, com o soma de todos os valores. A diferença é que uma coluna utiliza a fórmula SUBTOTAL e a outra utiliza SOMA.

Suponha que, utilizando o filtro já ativo, sejam desmarcados os itens 1, 2 e 3. A célula com a fórmula SUBTOTAL apresentará o valor 9 (correto), ao passo que a outra apresentará o valor 15 (errada). Ou seja, mesmo com o filtro ativado a fórmula SOMA continua a somar todos os valores.

Num outro exemplo, agora com o filtro totalmente desativado (todos os valores devem ser mostrados), vamos ocultar as linhas referentes aos valores 2 e 3 (não esquecendo de alterar o parâmetro da função SUBTOTAL para 109). Note que a célula com a fórmula SUBTOTAL apresentará o valor 10 (correto), ao passo que a outra apresentará o valor 15 (errada)

Placar final: SUBTOTAL 2 x 0 SOMA

Cansado de apanhar da VLOOKUP (PROCV)?

Eu também. Ficou tudo mais fácil de depois que descobri o Quick Reference Card: VLOOKUP troubleshooting tips no blog da Microsoft.

Retornando os maiores ou menores valores de uma lista

Quem já utilizou a função Maior() para descobrir quais são os N maiores itens de uma lista levanta a mão! Levanta a outra mão quem utilizou a função Menor() para descobrir quais são os N menores itens de uma lista!

Geralmente utilizamos estas funções para retornar um único valor, o maior ou o menor da lista. Utilizando estas funções para retornar os N maiores ou N menores números de uma lista tornamos as informações muito mais ricas.

Por exemplo, podemos responder as seguintes perguntas:

Quais foram os cinco produtos mais lucrativos?

Quais foram as três maiores médias escolares?

Quais foram as 10 cidades com menor IDH no Brasil?

Para isto, basta preencher o segundo parâmetro da fórmula com a quantidade de informações a serem retornadas. Decida o que fazer com os dados retornados (Somar? Calcular a média?) e bingo!

No exemplo abaixo calculei a soma dos cinco produtos mais lucrativos com a seguinte fórmula:

=SOMA(MAIOR(B2:B11;{1;2;3;4;5}))

Também calculei a média de lucro dos cinco produtos mais lucrativos com a seguinte fórmula:

=MÉDIA(MAIOR(B2:B11;{1;2;3;4;5}))

Adapte e utilize conforme sua necessidade.