sábado, 18 de junho de 2011

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.

sexta-feira, 10 de junho de 2011

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.