Usabilidade & infográficos

Mais um exemplo de infográfico didático, intuitivo e bem projetado. E uma excelente análise do Silvio Meira. E você, o que achou?

a web, o social, os jornais

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.

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.

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.

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.

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.

Gráfico em formato de velocímetro

“Graficozinho” simples, fácil de construir, intuitivo e com bastante aceitação do público em geral (apesar de alguns autores serem contra o seu uso).

Muitas técnicas de construção podem ser encontradas na Internet e eu particularmente gosto daquela mostrada pelo Chandoo, com algumas adaptações: basicamente um gráfico de pizza sobrepondo um gráfico de rosca para criar a ilusão do marcador sobre uma faixa de valores.

O primeiro passo é criar a tabela com a faixa de valores para as representações verde, amarelo, vermelho e branco (que será a parte de baixo do gráfico, sem nenhuma representação visual). Note que a faixa branca deve representar 50% do total de valores.

O segundo passo é criar o gráfico base, do tipo rosca. Veja o original e compare com o resultado após as seguintes alterações: retirada da legenda, formatação do ângulo da primeira fatia para rotação de 270 graus, alteração das cores de cada faixa de valores conforme as cores da tabela (verde, amarelo, vermelho e branco).

O terceiro passo é criar o marcador de posição, basicamente o gráfico de pizza. Acrescentei três novas linhas na tabela, sendo que a primeira será a posição do marcador e as outras duas são necessárias para complementar o gráfico de pizza. O valor para a linha complemento deve ser a fórmula 200 - Ajuste - Posição. Veja a tabela alterada e o novo gráfico base gerado, antes das modificações finais.

O quarto é último passo consiste em retirar a legenda, formatar o ângulo da primeira fatia para rotação de 270 graus, formatar para “sem preenchimento” os pontos de dados Posição e Complemento, formatar para azul o ponto de dados Ajuste, formatar o preenchimento da área do gráfico para “sem preenchimento” e posicionar o gráfico de pizza sobre o gráfico de rosca. O resultado pode ser visto abaixo.

Para alterar a posição do marcador basta alterar a linha Posição, respeitando a restrição de valores (0 a 99). Outras adaptações podem ser feitas, como a inclusão do rótulo dados, legendas, títulos, redefinição das faixas de valores, etc. Use de acordo com suas necessidades.

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.

Qual é o melhor gráfico para o meu dashboard?

Não vamos reinventar a roda, certo? Sendo assim, vou responder à esta pergunta com o trabalho do Stephen Few, que analisa cada tipo de gráfico em função do tipo de conteúdo (fonte dos dados).

Graph Selection Matrix

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

Gráficos de barras com preenchimento personalizado (Copas do Mundo)

Os gráficos de barras são ótimos para representar séries quantitativas de dados. São visualmente agradáveis, com boa usabilidade para o usuário final e fáceis de construir. E ainda podem ficar visualmente mais bonitos!


Neste exemplo substituí o preenchimento padrão das barras por uma imagem representando o conteúdo do
gráfico, ou seja, a bandeira de cada País.

Primeiramente criei a tabela com os dados, sem grandes complicações.

Criei um gráfico de barras comum e fiz algumas alterações gerais (ajustei a escala do eixo vertical, retirei os
títulos e legendas, aumentei o espaçamento entre as séries, retirei as linhas de grade).


Cliquei com o botão direito na primeira barra e selecionei a opção Formatar ponto de dados, opção
Preenchimento, opção Preenchimento com imagem ou textura.


Depois selecionei a opção “Inserir de Arquivo” e escolhi a imagem correta (a bandeira do respectivo País
representado por cada uma das barras). Marquei a opção Empilhar e dimensionar com 1 unidades/imagem.
Repeti a operação para cada uma das barras. Veja o original e compare com o final.

É possível fazer o mesmo com outros tipos de gráficos? Sim, totalmente possível. Que tal criar algum e
compartilhar conosco?


p.s.1: Utilizei o MS-Excel 2010, portanto, as opções de menu poderão variar de acordo com a versão
p.s.2: Agradecimentos especiais ao Adriano Estokero pela arte final nas bandeiras

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.

Dashboard garante viagem à Disney

Um certo professor costumava repetir em sala de aula: “Sou um cara que adora coisas práticas, e nada mais prático do uma boa teoria!”

A teoria colocada em prática neste artigo trata de um caso real de família com despesas mensais fechando no 0 x 0 (zero a zero). Ou seja, pagavam todas as contas e faziam seus investimentos, mas não “sobrava” dinheiro para a tão sonhada viagem à Disney.

Conforme orientação do Gustavo Cerbasi, conceituado autor do segmento de finanças pessoais, a recomendação é justamente não cortar os gastos relacionados ao lazer e sim aqueles relacionados aos chamados “gastos burocráticos”: água, luz, telefone, gás, supermercado, escola.

Portanto, construí para a família um dashboard focado no orçamento familiar, apontando justamente a “performance” nos gastos burocráticos.

Dois itens chamaram a atenção:

1)      Alto volume de ligações DDD, VC1 (ligação local para celular) e VC3 (ligação interurbana para celular) sempre para os mesmos números: familiares que residem em outra cidade ou filhos em passeios com os amigos.

2)      Volume de compras no supermercado não condizente com a rotina familiar, onde praticamente todos fazem refeições fora de casa, inclusive aos fins de semana.

Com base nos gráficos ficou claro que a melhor estratégia para o telefone era adotar um plano que possibilitasse o máximo de ligações ao menor custo. Leia-se: é para continuar falando, inclusive mais, porém, pagando menos. A solução óbvia foi o plano de uma determinada operada que permite ligações ilimitadas entre números de sua rede, inclusive interurbanos, à custo ZERO.

Já para o caso do supermercado a solução foi ainda mais simples: checar a geladeira, despensa e armários antes de ir ao supermercado, comprando apenas os itens realmente necessários.

Os resultados podem ser vistos abaixo. Redução em quase 50% da fatura de telefone (aproximadamente R$ 200,00 ao mês), além da redução de aproximadamente R$ 500,00 ao mês com o consumo de supermercado.

O que mais me chamou a atenção é que a família “já sabia” de tudo isso, mas, literalmente pagou pra ver. Deve ser coisa de São Tomé!

Redução de despesas com telefone

Reduçao de despesas com supermercado

P.S.: Sim, eles realizaram o sonho de ir à Disney.

Regras para fazer planilhas melhores

Larry’s Rules for Making Better Spreadsheets

Artigo que parece muito, muito simples, mas, não é.

O desafio aqui é: Como você pode cumprir cada uma destas regras?

Fotografando com o Excel

A utilidade da ferramenta Câmera é inversamente proporcional a quantidade de pessoas que a utilizam para construir Dashboards. Frase esquisita para dizer que poucas pessoas utilizam (conhecem) esta importante ferramenta que permite “fotografar” parte de uma planilha.

Vamos à prática.

Antes de tudo é necessário incluir o botão Câmera na barra de ferramentas.

Agora, selecione o intervalo de células que será “fotogrado”. Neste exemplo, o intervalo B2:G13. Clique no botão Câmera e note que o cursor é alterado para o formato de desenho, possibilitando que você defina o tamanho da imagem a ser gerada (a fotografia).

Agora basta preencher o local “fotogrado” com as informações relevantes para a “fotografia”. Tabela, textos ou até mesmo gráficos (inclusive, esta é uma das técnicas para construção de mini-gráficos). Se você tem múltiplos dashboards, utilize esta técnica para consolidar as informações num único dashboard (muito útil para, por exemplo, Gerenciamento de Programas).

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

Gráfico em formato de mapa

Qual é o melhor formato de gráfico para representar dados estatísticos de um País?

O mapa geográfico do País, ora bolas!

A maneira fácil, barata e rápida, portanto de melhor custo x benefício, que encontrei foi utilizar os templates do Choropleth Maps. Faça o download do template que você precisa e atualize e customize conforme suas necessidades.

De maneira geral, bastam apenas três pequenas atualizações para ter um gráfico pronto e com dados reais. Para este exemplo, utilizei dados do PIB brasileiro de 2007, distribuído por UF.

Na guia Control você encontrará um conjunto de informações chamado List of Metrics. Nada mais é do que a lista de valores para seleção do gráfico a ser exibido na guia Map, a que realmente mostrará o mapa.

Na guia Data você poderá preencher cada coluna com as informações que serão exibidas em formato gráfico. Neste exemplo preenchi apenas a primeira coluna com o PIB percentual de cada UF, alterando o título da coluna também para “PIB by UF %”.

Na guia Map já tinha o gráfico praticamente pronto, porém, fiz mais uma pequena customização, incluindo uma tabela com o número percentual do PIB por UF, em ordem decrescente.

E isso é tudo. Faça suas modificações, customizações, adaptações. Também fica a dica: procurando no Google será possível encontrar muitos outros templates. Aproveite.

P.S.: 56,5% do PIB brasileiro está concentrado apenas na região Sudeste!!!

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.

Nuvem de palavras no MS-EXCEL

Outro dia fiz um dashboard com uma nuvem de palavras e alguns amigos ficaram curiosos com o efeito. A pergunta mais freqüente foi: “Que tipo de gráfico é este?”, seguida da afirmação “Não sabia que o MS-Excel fazia este tipo de gráfico!”.

Bom, até onde eu saiba, o MS-Excel realmente não faz este tipo de gráfico!

O segredo é bem simples: encontre algum site que crie automaticamente sua nuvem de palavras e copie a imagem para o seu dashboard. Isso mesmo, bem simples. “E se eu quiser algo mais robusto, mais automático, capaz de gerar a nuvem de palavras diretamente no MS-Excel?” Se encontrar, me avise, com certeza também vou querer ;-)

De volta ao básico, gosto bastante do wordle.net, justamente pela facilidade de uso, permitindo ao usuário fornecer a lista de palavras 1) diretamente pelo site, num campo texto; 2) Através de um feed RSS ou 3) Através de um usuário del.icio.us.

Para simplificar, usei como exemplo os RSS da Globo, do Flamengo e do The New York Times. Enfim, crie sua nuvem de palavras e faça bonito no seu próximo dashboard.

Globo.com

Flamengo 

The New York Times