Gráfico da Situação dos Mananciais

Esses dias estava olhando uma notícia do UOL que possuía um gráfico animado bem interessante.

Resolvi comprar a ideia e fazer um parecido no Excel. Ele está aqui para ser baixado, e o resultado você vê abaixo:

 

Este gráfico mostra a evolução do nível dos mananciais de São Paulo gerenciados pela SABESP e mostra os dados desde 1-jan-2003.

Separo abaixo a forma como construí este gráfico dos mananciais:

Parte 1 – base de dados para o gráfico

A partir do artigo do UOL comecei  a dar uma fuçada até chegar aqui. Tem um gráfico muito legal construído por Miguel Peixe. Nele, tem o link para o código fonte que foi usado no github, e lá consegui baixar um CSV com os dados deste 2003, no formato CSV.

(atualização: enquanto escrevia este artigo recebi resposta do Miguel me passando o link para os dados atualizados diariamente. Aqui. Desde já, agradeço pelo ajuda.)

Basicamente fiz uma importação de arquivo CSV separando os campos por vírgula (,). Isto está na planilha base de dados.

Parte 2 – transformar a base em tabela

Para facilitar a atualização dos dados no gráfico, resolvi transformar o intervalo de dados em uma Tabela. Para fazer isto, basta clicar em qualquer célula do intervalo e ir em Inserir>Tabela. Veja abaixo:

Parte 3 – organizar os dados em uma tabela dinâmica

Nesta etapa criei uma tabela dinâmica para poder organizar melhor os dados. Isto é opcional, daria até para organizar os dados assim, mas como sou fã de carteirinha da tabela dinâmica…

Para fazê-la, é simples. Clique em qualquer lugar da tabela criada e vá em Inserir>Tabela Dinâmica. Veja:

Depois de pronta, vamos organizá-la da seguinte forma:

Parte 4 – filtrar os dados a serem utilizados no gráfico de situação dos mananciais

Na planilha grafico construí uma estrutura para poder fazer a animação do gráfico.

Inicialmente coloquei alguns dados na parte superior:

 

Nesta área, a única coisa que precisa ser alterada é o intervalo de dias que a macro irá usar para atualizar nosso gráfico.

Os demais cálculos são feitos usando funções máximo e mínimo. Gosto de usar também intervalos nomeados, e por isso apliquei às células os seguintes nomes. Para aprender a fazê-lo, veja este artigo.

B5: valData

B6: valStep

B7: valMenorData

B8: valMaiorData

B9: valQuantoFoi

Parte 5 – construir a estrutura para o gráfico principal usando as funções INDICE, CORRESP e ORDEM.EQ

Agora precisamos montar a estrutura que será usada para montarmos o gráfico.

Usaremos um gráfico de BARRAS, uma vez que vamos comparar os ítens (ainda que tenhamos data, o objetivo não é ver a evolução, e sim a posição de cada manancial/reservatório naquela data). A noção temporal será data pela animação. Aqui tem tutorial que ensina a fazer gráfico de barras.

Como conceitualmente um gráfico de barras deve ser sempre em ordem DECRESCENTE, precisaremos determinar a posição de cada manancial para saber qual virá em primeiro, em segundo, etc.

Veja:

Na coluna B apenas digitei o nome de cada Manancial. Ali não tem nenhuma fórmula.

Na Coluna C preciso trazer o volume armazenado daquele manancial, naquela data. Para isto, usei as funções CORRESP e ÍNDICE para trazer os dados de cada Manancial.

Na Coluna D estou trazendo o dado do DIA ANTERIOR para poder calcular, na coluna E, a variação de um dia para o outro.

Na coluna A estou trazendo a posição daquele manancial em relação aos demais. VEja que no exemplo o Rio Grande, com 83,9% é o manancial com o maior volume.

Para fazer isto, usei a função ORDEM.EQ (função nova no Excel 2013, que pode ser trocada pela função ORDEM em versões mais antigas, sem problema algum). Outro exemplo desta função para se fazer sorteios sem repetição aqui.

Parte 6 – design da tabela para o gráfico

Da tabela feita acima, precisamos trazer os dados ordenados e formatados, já que serão eles que serão lidos pelos usuários.

Na coluna A, usamos a função MENOR para poder ordenar os valores calculados na tabela acima. Na 1ª célula trazemos o 1º menor, na 2ª o segundo menor, etc.

Nas Colunas B, C, D usamos o PROCV para poder trazer os dados correspondentes àquele manancial, baseados na tabela construída na etapa 5.

Nas colunas E e F pego o valor daquele manancial e o coloco na E se for menor de 25%, se for maior do que 25% coloco na coluna F (escolhi este valor aleatoriamente, pode ser qualquer um; basta trocar 0,25 que está nas fórmulas das colunas E e F pelo que preferir). Esta estrutura fará com que nosso gráfico se transformar em um gráfico de formato condicional, onde o manancial que estiver abaixo de 25% ficará com a barra de uma cor diferente – e o mais legal – AUTOMATICAMENTE.

Parte 7 – montando o gráfico com a barra de progresso na planilha

Logo acima do gráfico dos mananciais temos uma barra de progresso que vai avançando à medida em que as datas vão avançando.

Esta barra de progresso é um gráfico de barras comum, no qual retiramos todos os objetos, à exceção da série. Ele irá trazer da planilha o %  que aquela data representa em relação à data máxima.

Veja o vídeo abaixo:

http://fd-vianna.wistia.com/medias/5q80d4j646?embedType=seo&videoWidth=640

---

 [optinform]

---

 

Parte 8 – montando o gráfico de situação de mananciais a partir da planilha

Uma vez montado a barra de progresso, vamos criar nosso gráfico de barras.

Vamos criá-lo clicando em uma célula em branco, tal como feito na Parte 7. Depois, clicamos nele com o botão direito e selecionamos Editar Dados e adicionamos as séries conforme aparece a seguir:

 

 

[alert-warning]SUPER IMPORTANTE: quando adicionar os dados ao gráfico, selecione também a célula com o título (linha 19). Isto é importante para melhorar o layout do gráfico (mostrarei um pouco mais à frente).[/alert-warning]

Lembre-se de que estamos usando um gráfico empilhado. Isto significa que temos duas séries de dados: uma abaixo do limite (sendo que neste exemplo não aparece porque não temos nenhum manancial abaixo de 25%) e uma acima do limite.

Vamos formatar a barra do acima do limite, como foi feito antes na barra de progresso. Escolha uma cor de preenchimento, uma borda (no meu caso deixarei sem ela) e na propriedade LARGURA DO ESPAÇAMENTO, coloque um valor baixo (colocarei 15%):

Para podermos formatar a barra do abaixo, vamos digitar na célula B5 uma data que contenha pelo menos um dado menor do que 25%. Digite lá 20-3-15 e aparecerão dois mananciais abaixo de 25%. Formate-os com uma cor diferente.

Veja que aparece no eixo vertical uma numeração sequencial, sendo que o número 1 está vazio. Ele aparece assim porque marcamos a linha de titulo como se tivesse valor, e assim o Excel não consegui plotar a informação. Conceitualmente o gráfico de barras deve estar em ordem decrescente. Para que ele fique certo, clique sobre este eixo vertical com o botão direito e vá em Formatar Eixo. formate a opção de eixo chamada CATEGORIAS EM ORDEM INVERSA:

 

Agora pode APAGAR este eixo. O nome dos mananciais virá de outra maneira.

Agora vamos formatar o eixo de valores. Vamos definir o Eixo Minimo, Máximo, o Formato de Número e as marcas de escala. Veja:

Lembra que selecionamos o titulo como se fosse uma parte da série? Era para que a 1ª barra não ficasse colada no eixo. Assim há um espaço entre o eixo e a barra do 1º manancial.

Agora formate a área do gráfico para que fique sem borda e sem preenchimento:

Precisamos colocar o nome dos mananciais/reservatórios junto do gráfico.

Para isto, vamos usar o recurso de câmera do Excel. Na minha opinião, este recurso é um dos mais úteis e incríveis que você possa usar em suas planilhas.

Ele funciona como se fosse um ‘espelho’, refletindo uma determinada imagem. Imagine que esta ‘imagem’ seja uma área da planilha. E com a facilidade de se diagramar, porque este recurso cola uma imagem, e portanto você pode arrastá-la para onde quiser.

Antes de usarmos este recurso, vamos aplicar (mais) uma formatação condicional para que o nome do manancial mude de cor caso esteja abaixo dos 25%.

Selecionamos o intervalo com o nome dos mananciais, vamos em Início>Formatação Condicional>Mais Regras.

 

Agora, selecionaremos o intervalo que vai da célula B20 até C25. Dê um CTRL c.

Para colar a fotografia, faça conforme abaixo (pode clicar em qualquer célula para executar esta ação:

 

 

Agora basta alinhar esta fotografia com  o gráfico. Dá um pouco de trabalho, mas o resultado final é bom:

Agora falta automatizar as datas. Se você alterar a data manualmente ele alterará as informações, mas precisamos deixar isto automático.

Para entrar no módulo VBA PRIMEIRAMENTE salve o arquivo com extensão XLSM ou XLSB. Manter o arquivo na extensão XLSX fará com que TODAS as macros sejam apagadas.

Somente arquivos nessas 2 extensões mantém as macros criadas.

---

[optinform]

---

 

Agora, aperte a tecla ALT F11. Na tela que surgir, clique em Inserir>Módulo:

Veja que do lado esquerdo aparecerá um Módulo1 (na imagem acima já aparece o módulo criado no arquivo.

É neste módulo onde colocaremos o nosso código VBA, como segue (ao final de cada linha aparece a explicação do mesmo. Para saber mais sobre macros e vba, clique aqui.

Depois disto basta fechar a tela do VBA e salvar a planilha.

Para podermos acionar a macro, basta desenhar alguma autoforma (que está no meu Inserir) e depois clicar sobre ela e selecionar a opção ATRIBUIR MACRO:

 

Para finalizar, vamos colocar a data em cima do gráfico. Vá em Inserir>Caixa de Texto. Desenhe uma caixa de texto.

Em seguida, clique no contorno dela, vá na barra de fórmulas, digite o sinal de = e clique na célula onde as datas serão colocadas pela macro.

Depois, dê enter e formate a caixa de texto como preferir:

 

Agora é testar!

O que achou? Comente aqui embaixo o que você achou deste gráfico? Fez? Deu certo? Foi promovido? 😉