segunda-feira, 18 de julho de 2011

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.

sábado, 28 de maio de 2011

Gráfico Pizza de Pizza

O gráfico de pizza é um dos mais populares e fáceis de entender. Funcionam muito bem desde que os itens da série de dados sejam mais ou menos proporcionais, o que não é o caso, por exemplo, quando falamos do PIB brasileiro e sua “belíssima” distribuição de renda.

No gráfico abaixo, gerado a partir do Censo de 2008, gerei uma pizza simples para demonstrar o percentual de contribuição de cada Estado Brasileiro na composição do PIB. Vemos um gráfico praticamente incompreensível em função da quantidade excessiva de valores muito pequenos.


Utilizando a mesma fonte de dados, gerei um novo gráfico, desta vez do tipo Pizza de Pizza, obtendo o seguinte gráfico-base.


Retirei o título, posicionei a legenda no centro inferior e reduzi o tamanho da fonte, adicionei e formatei o rótulo de dados para exibir o percentual de cada Estado. Em Formatar série de dados, Opções de série: 1) Dividi a série em valor percentual contendo todos os valores menores que 2%, alterei a largura do espaçamento para 300% e o tamanho da segunda plotagem para 120%.

O resultado fica muito mais compreensível: 85% do PIB brasileiro é gerado por 10 Estados e uma Unidade da Federação, enquanto os outros 15% são gerados por 16 Estados.

sábado, 21 de maio de 2011

Gráfico do Quadrante Mágico

Um dos gráficos mais didáticos que conheço é o “Quadrante Mágico”, muito utilizado pelos órgãos independentes de pesquisa para avaliar e classificar empresas nos mais diversos aspectos e características. Também é conhecido por gráfico de setor.

Basicamente consiste num gráfico tipo “X,Y”, porém dividindo o plano cartesiano em 4 quadrantes. Cada quadrante terá uma característica determinante para todo o grupo. Não entrarei nos detalhes conceituais do gráfico, porém, se você necessitar de mais informações, consulte a Wikipedia.

Primeiramente, necessitamos de um gráfico simples com os 4 quadrantes. Crie uma tabela de dados conforme abaixo e na sequência crie um gráfico de colunas empilhadas, alterne entre linhas/colunas para fazer o empilhamento, reduza a largura do espaçamento entre colunas para 0%, configure a escala do eixo Y para mínimo 0 e máximo 2, retire a legenda e os rótulos dos eixos, retire as bordas de cada coluna e altere a cor de cada quadrante conforme sua preferência. Reserve.


O segundo passo é criar o gráfico com a distribuição dos “players” conforme os seus próprios critérios de classificação. Neste exemplo criei uma tabela fictícia com vários “players” e suas respectivas avaliações. Depois gerei um gráfico de bolhas e retirei o título, a legenda, as linhas de grade, os rótulos dos eixos, ajustei o tamanho das bolhas para largura 50, retirei o preenchimento de todo o gráfico (para obter a transparência necessária à sobreposição), alterei a cor das bolhas para verde, incluí o rótulo de cada quadrante e adicionei o rótulo de dados com o nome do “player”.


O próximo passo é simplesmente posicionar o gráfico de bolhas sobre o gráfico de colunas para obter o resultado abaixo. Um gráfico simples, claro, direto ao ponto e muito fácil de entender.

domingo, 15 de maio de 2011

Gráfico em formato de tornado

Este tipo de gráfico é muito utilizado para apresentar as características da demografia de um País, geralmente agrupando sexo e idade da população. É bastante conhecido como Pirâmide etária, pirâmide demográfica ou pirâmide populacional.

Como quero demonstrar apenas a técnica de construção do gráfico, vou utilizar os dados já existentes a partir do Censo 2010 sobre a população brasileira. A tabela original apresenta o percentual de homens/mulheres por faixa de idade. Acrescentei mais uma coluna transformando os percentuais masculinos em números negativos. Esta é a coluna que gera o lado esquerdo do “tornado”, porém, será apresentado de forma invertida.


Selecione as colunas “Homens (negativo)” e “Mulheres” e crie um gráfico de barras 2D empilhadas. O resultado será este “monstrinho”, a partir do qual faremos toda a formatação para criar o “tornado”.


Altere em “Selecionar dados” o rótulo do eixo horizontal (categorias) de forma a apontar para a coluna “Idade” e altere em entradas de legenda (série) o nome da série “Homens (negativo”). Posicione a legenda no centro inferior.

Formate o eixo X marcando as opções “Valores em ordem inversa” e “Eixo vertical cruza em valor máximo do eixo”. Formate o eixo Y marcando a opção “Categorias em ordem inversa” e formate o número para personalizado 0;0.

Formate as linhas de grade, fontes, cores das barras. O resultado final pode ser visto abaixo.

domingo, 8 de maio de 2011

Gráfico em formato de termômetro

Este é um gráfico muito, muito simples de se construir, praticamente sem requerer domínio do MS-Excel. Particularmente gosto de utilizar para monitorar o progresso de atividades em desenvolvimento, como é o caso de projetos de Tecnologia da Informação.

Útil para antecipar os alertas que demonstram criticidade de riscos, custos acima do planejado, datas de conclusão perdidas, grande quantidade de erros durante a fase de testes entre outros.

Primeiro vamos construir o gráfico propriamente dito, sendo necessário apenas um gráfico de coluna cuja tabela de dados tenha apenas uma célula, ou seja, aquela que registra a “temperatura”. Veja o gráfico padrão e o gráfico final, após a retirada do título, legendas, linhas de grade, ajuste da escala para valor fixo (mínimo de 0 e máximo de 100), redimensionamento, alteração de cor de preenchimento da coluna e retirada da borda.



Basicamente, vamos desenhar o termômetro manualmente. O bulbo é apenas um objeto círculo incluído na parte inferior do gráfico, o capilar é um objeto retângulo que envolve a coluna do gráfico e a substância termométrica é apenas o gráfico de colunas preenchendo o retângulo. Altere as bordas e cores de preenchimento e o gráfico final está praticamente pronto, conforme abaixo.


Conforme a necessidade é possível incluir limites que indiquem qual é “temperatura” crítica, incluir o rótulo de dados, etc. Adapte como desejar.