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.