Gráfico com barra de rolagem
Em muitos casos temos gráficos com longos períodos de tempo de dados, e visualizar todos eles em um mesmo gráfico fica ruim, porém pior ainda é ter que fazer um gráfico novo sempre que se quer visualizar um tempo diferente no gráfico.
Uma forma bem prática de resolver isso é usando as barras de rolagem, aquela barra que aparece na lateral da tela para podermos visualizar mais pra cima ou mais pra baixo um site, ou até mesmo o próprio Excel.
Você vai precisar de:
PROCV (Clique aqui para saber como usar)
ÍNDICE + CORRESP (Clique aqui para saber como usar)
CONT.VALORES (Clique aqui para saber como usar)
Um gráfico simples (Clique aqui para saber como fazer)
Uma base de dados grande o suficiente para criar um gráfico que ficaria ruim com todos os dados exibidos.
Aba DESENVOLVEDOR habilitada no Excel.
Sugiro que você assista ao vídeo abaixo, nele tudo é explicado no detalhe e com 4 exemplos diferentes, mas caso prefira seguir com a leitura, é só continuar mais abaixo.
Coloque o vídeo em tela cheia para assistir normalmente
Habilitando a Aba DESENVOLVEDOR
Para inserir a barra de rolagem é preciso habilitar uma aba extra no menu principal do Excel, que por padrão vem desabilitada. Veja o passo a passo de como habilitá-la.
- Clique no botão Arquivo no topo esquerdo superior do Excel.
- Vá em Opções. Uma nova janela será exibida.
- Vá em Personalizar Faixa de Opções no menu à esquerda.
- No quadro da direita marque a opção Desenvolvedor.
- Aperte OK e uma nova aba aparecerá no menu principal superior do Excel.
Preparação para o Gráfico
Primeiro, tenha em mente a quantidade de dados que você quer exibir no gráfico. Por exemplo, sua base de dados tem 300 linhas mas você quer que no gráfico apareçam sempre somente 20 linhas, então tenha em mente 20 linhas.
No meu caso meus dados tem 151 linhas porém quero exibir apenas 12 no gráfico.
Deixarei tudo em uma mesma aba do Excel para facilitar a visualização do que estou fazendo, mas você pode montar da maneira que preferir, nada aqui precisa ser precisa estar visível ao usuário, mas precisa existir.
Quadro 1
Crie um quadro no seguinte modelo.
Este será usado de base para o funcionamento da barra de rolagem.
No campo Mínimo você deve inserir em qual posição seus dados iniciam (ou qual linha da planilha). No meu caso inseri o valor 3 pois meus dados iniciam na linha 3.
No campo Posição vou inserir o mesmo valor que coloquei em Mínimo, mais pra frente isso mudará automaticamente.
No campo Máximo inseri uma fórmula com CONT.VALORES. A fórmula é:
=CONT.VALORES(B:B)+G2-12-1
Onde:
B:B ► É a primeira coluna onde estão os meus dados. A função CONT.VALORES irá contar quantas células não estão vazias nesta coluna.
G2 ► É a célula onde está o valor Mínimo, ou seja 3.
-12 ► É o número de dados que quero exibir no gráfico.
-1 ► Como tenho um cabeçalho sobre meus dados na coluna B então devo desconsiderá-lo da conta, para contar somente quantos dados eu realmente possuo para serem exibidos.
Assim, o valor máximo será igual a linha em que o último dado – 13 estará. Neste caso, meus dados vão até a linha 153 do Excel e iniciam na linha 3, então esta fórmula irá retornar o número 142, ou seja, 153+3-12-1 = 142.
Quadro 2
Agora crie um quadro com a quantidade de linhas igual a quantidade de dados que você quer exibir no gráfico, no meu caso são 12.
Na primeira coluna dele é onde estarão as datas (ou qualquer outro tipo de dado) que servirá de guia para o restante dos dados.
Na primeira célula desta coluna vamos inserir a função ÍNDICE. A função que utilizei foi:
=ÍNDICE(B:B;G3)
Onde:
B:B ► É a primeira coluna onde estão os meus dados.
G3 ► É a célula onde o valor Posição está.
Está fórmula irá buscar o conteúdo da célula da posição presente. Caso a posição presente fosse 3 esta fórmula iria retornar a data 01/01/2015 pois é este o dado contido na linha 3 da coluna B.
Na segunda (e demais) células desta coluna iremos inserir uma fórmula semelhante a anterior, mas agora com ÍNDICE + CORRESP. Veja como fiz:
=ÍNDICE(B:B;CORRESP(F7;B:B;0)+1)
Onde:
B:B ► É a primeira coluna onde estão os meus dados.
F7 ► É a célula imediatamente anterior a atual.
Funciona assim: A função ÍNDICE irá buscar na coluna B o dado que estiver na coluna de número ‘resultado da função CORRESP’ + 1. E a função CORRESP irá buscar em qual linha o dado que está inserido na célula F7 está na coluna B.
Assim, se o conteúdo de F7 = 01/01/2015 que está na linha 3 da coluna B, então a fórmula irá retornar o valor 02/01/2015 pois é o conteúdo que está na linha 4 da coluna B.
Está fórmula deve ser copiada até a última linha do quadro que busca os dados a serem exibidos. No meu caso esta fórmula está no intervalo F8:F18.
Buscando os dados
Agora temos que buscar os dados correspondentes as datas (ou qualquer outro tipo de dado) que foi inserido na primeira coluna do quadro 2.
Assim na segunda e terceira coluna do quadro 2 (se a quantidade de dados for maior ou menor a 2 adeque o quadro a sua necessidade) irão conter uma função PROCV. Fiz assim:
Para a segunda coluna:
=PROCV(F7;B:D;2;FALSO)
Onde:
F7 ► É a célula correspondente onde está a data a ser buscada na tabela de dados.
B:D ► São as colunas onde a tabela de dados está.
2 ► É o número da coluna em quem o primeiro conjunto de dados está na tabela de dados.
FALSO ► Pois queremos uma correspondência exata.
Para a terceira coluna:
=PROCV(F7;B:D;3;FALSO)
Onde:
F7 ► É a célula correspondente onde está a data a ser buscada na tabela de dados.
B:D ► São as colunas onde a tabela de dados está.
3 ► É o número da coluna em quem o segundo conjunto de dados está na tabela de dados.
FALSO ► Pois queremos uma correspondência exata.
Montando o gráfico
Fazer o gráfico não tem segredo algum. Apenas selecione o Quadro 2 e insira o gráfico deseja do normalmente.
Tudo já deve estar funcionando nesse ponto. Dá pra testar alterando o valor que está no campo Posição. Só precisamos montar a barra de rolagem que ao movimentá-la o valor do campo Posição seja alterado automaticamente.
Criando a barra de rolagem
Chegamos ao X da questão!
Vá na aba Desenvolvedor que habilitamos lá no início do artigo, e no campo Inserir procure por Barra de rolagem (Controle de formulário). Adicione este item e o modele como preferir utilizando o mouse.
Quando estiver pronto clique com o botão direito sobre a Barra de rolagem recém criada e vá em Formatar Controle.
Uma nova janela será exibida com uma série de opções. Faça assim:
Valor atual: Insira o valor que estiver sendo exibido no campo Posição do quadro 2.
Valor mínimo: Insira o valor que está no campo Mínimo do quadro 1.
Valor máximo: Insira o valor que está no campo Máximo do quadro 1.
Alteração incremental: Indica em quanto o valor do campo Posição do quadro 1 irá aumentar/diminuir sempre que for dado um clique na setinha da barra de rolagem. Por enquanto deixe 1, depois se preferir pode mudar.
Mudança de página: Não precisa alterar, deixe o valor padrão.
Vínculo da célula: Insira a referência a célula do campo Posição do quadro 1. Ou apenas selecione este campo e depois clique sobre a célula onde está o campo Posição e o Excel automaticamente preencherá para você.
E está pronto!
Agora sempre que você mexer na barra de rolagem os dados do quadro 2 irão mudar, mudando também o gráfico.
Acompanhe o Função Excel
