Barra de Rolagem Excel – Tabela e gráfico com barra de rolagem no Excel
Aprenda como usar a barra de rolagem no Excel utilizando uma tabela e um gráfico como exemplo.
No artigo você aprenderá passo-a-passo como utilizar a barra de rolagem e ainda truques como definir o seu tamanho automaticamente e criar botões para ir ao primeiro e ao último registro.
O resultado final é demonstrado no gif abaixo aonde o gráfico e a tabela do Excel são movimentados pela barra de rolagem.
Base de Dados do exemplo de Tabela e o Gráfico com Rolagem
A base de dados que estamos usando no nosso exemplo de relatório é uma base de vendas aonde temos os seguintes campos:
- Data: A data da venda.
- Região: Região em que foi realizada a venda.
- Meta: Valor da meta por dia.
- Venda: Valor efetivo da venda.
Estes dados não estão consolidados, ou seja, pode haver mais de uma linha com a mesma data e região, assim como a meta que é por linha.
Desta forma iremos consolidar estas informações.
Consolidando os dados para o Relatório no Excel
Apontando para a base de dados criamos uma tabela dinâmica.
Para tanto clique na guia Inserir->Tabela Dinâmica e selecione os dados de Tabela/Intervalo à partir da base de dados, no caso da planilha a tabela tVendas.
Nos campos de tabela dinâmica arraste Data para Linhas, Meta e Venda para valores e Região para Colunas.
Clicando sobre a tabela dinâmica clique na guia Análise de Tabela Dinâmica e clique em Campos, Itens e Conjuntos.
Clique em Campo Calculado e inclua um campo chamado Resultado % e coloque este cálculo: = SEERRO(Venda/Meta;0).
Este campo calculado é o resultado percentual da venda sobre a meta, quanto a meta alcançou da meta.
A sua tabela dinâmica ficará com a seguinte aparência:
Veja que no topo temos as regiões, na esquerda as datas e as colunas de Meta, Venda e Resultado %.
Preparando os Cálculos para a Barra de Rolagem da Tabela Excel
Para controle da barra de rolagem no Excel nós iremos criar dois campos:
- Linha: Número da linha de topo em que se encontra a tabela, ela controla a rolagem da tabela para saber sua posição.
- Máximo: Quantidade de registros que existem na tabela.
Na célula ao lado de Linha o número deverá ir de 0 ao maior número possível da tabela.
Na célula ao lado de Máximo você deve colocar uma fórmula para identificar a quantidade total de registros, no caso: =CONT.NÚM(A:A)-10.
A fórmula conta quantos números há na coluna A e reduz 10 porque serão sempre exibidas 10 linhas no total.
Acima temos um exemplo em que a barra de rolagem está na posição 165 e o máximo é 210.
Realizando os Cálculos do Relatório e Gráfico Excel
Para não realizar os cálculos no relatório, mas sim nesta planilha auxiliar que chamamos de Cálculo, nós iremos utilizar a função DESLOC.
A função DESLOC realiza o deslocamento de dados no Excel, por exemplo A1, deslocar 1 linha, fica A2, deslocar 2 linhas, fica A3, e assim por diante. De uma forma simplificada.
Veja mais sobre DESLOC neste artigo: Aprenda DESLOC de uma vez por todas!
Utilizamos então a seguinte estrutura para os cálculos:
Usamos a função: =DESLOC($A4;$M$2;0) , no caso estamos deslocando a célula A4 que é referente à primeira data na nossa tabela dinâmica, e o M2 se refere ao campo Linha, que criamos anteriormente.
Desta forma, ao mudar a linha estas informações são deslocadas.
As outras células seguem o mesmo princípio da função desloc, sempre buscando a primeira informação de cada uma das colunas correspondentes.
Finalizando o Relatório e Gráfico com Barra de Rolagem no Excel
Por fim temos então o nosso relatório e gráfico.
Os mesmos serão alterados quando a barra de rolagem for alterada, para isso monte um esqueleto da tabela conforme abaixo:
No caso das datas no canto substitua pela fórmula =Cálculos!O4 esta fórmula simplesmente retornará a informação da célula O4 da planilha Cálculos referente à primeira linha da data.
Arraste esta célula para que os dados sejam preenchidos nesta coluna.
Repita a operação para todas as outras colunas.
Após isso vamos criar nossa barra de rolagem no Excel.
Clique na guia Desenvolvedor, caso não esteja aparecendo siga este tutorial: Habilitar guia desenvolvedor.
Nesta guia no grupo Controles clique em Inserir e selecione Barra de rolagem.
Desenhe ela arrastando ao lado da tabela.
Clique em Inserir -> Formas e insira dois triângulos, um acima e um abaixo da barra de rolagem. Estes triângulos serão para ir para o primeiro e para o último registros.
A aparência tabela com a barra de rolagem ficará conforme abaixo:
Na barra de rolagem clique com o botão direito e em Formatar Controle aponte o vínculo de célula para a célula ao lado de Linha na planilha Cálculos.
Veja que está marcado abaixo em Cálculos!$M$2:
O campo Valor atual é o valor que está a barra de rolagem no momento, valor mínimo é o 0 referente a primeira linha, ou seja, não deslocar nada.
A alteração incremental é mesmo 1, ou seja, incrementa de um em um, e a mudança de página é de 10 em 10, se clicar no intervalo da barra de rolagem será passado de 10 e 10 itens.
Clique na barra ela já está mudando os itens neste momento.
Alterando o Valor Máximo da Barra de Rolagem Excel com VBA
O valor máximo da barra de rolagem não é vinculado a uma célula como o campo Vínculo de célula que é responsável pela posição da barra.
Para resolver isso utilizamos o seguinte código:
Sub lsAtualizarBarra() ActiveSheet.Shapes.Range(Array("Scroll Bar 1")).Select With Selection .Value = 88 .Min = 0 .Max = Calculos.Range("M3").Value .SmallChange = 1 .LargeChange = 10 .LinkedCell = "Cálculos!$M$2" .Display3DShading = True End With Relatorio.Range("B7").Select End Sub
No campo .Max, refernete ao Scroll Bar 1, ou seja, a barra de rolagem, nós mudamos para Calculos.Range(“M3”).Value, passando então o valor calculado da quantidade de registros máximo.
Desta forma, quando a base for atualizada a barra de rolagem será também atualizada e ficará correta.
Coloque então este código no evento Worksheet_Activate da planilha de Relatório como abaixo. Assim ao mudar de planilha e ir para a planilha de Relatório ela automaticamente irá atualizar o maior valor da barra de rolagem.
Private Sub Worksheet_Activate() lsAtualizarBarra End Sub
Por fim para os triângulos acima e abaixo da barra de rolagem use as seguintes macros que podem ser adicionadas no módulo:
Public Sub lsPrimeiro() Calculos.Range("M2").Value = 0 End Sub Public Sub lsUltimo() Calculos.Range("M2").Value = Calculos.Range("m3").Value End Sub
No primeiro, o valor de M2 que controla a barra recebe 0, indo ao primeiro registro, e em lsUltimo temos que M2 recebe o valor de M3 que é referente ao maior registro, o último.
Para o triângulo de cima da barra, clique com o botão direito e em Atribuir Macro selecione lsPrimeiro, e para o triângulo no final selecione lsUltimo.
Agora ao clicar nos botões eles automaticamente irão para o primeiro e último registro.
Criando o Gráfico de Barra de Rolagem Excel
Para o gráfico selecione as colunas Percentuais e pressione ALT+F1.
O gráfico irá ser criado automaticamente e você pode ajustar ele como precisar.
Ao clicar na barra de rolagem a mesma já é calculada automaticamente ao mudar a posição.
Download da Planilha de Tabela e Gráfico com Barra de Rolagem no Excel
Abaixo você tem o download da planilha de tabela e gráficos com barra de rolagem no Excel.
Basta colocar seu e-mail para fazer parte da nossa newsletter gratuita e o download irá iniciar automaticamente.
Baixe a planilhaCurso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: