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:

1. Data: A data da venda.
2. Região: Região em que foi realizada a venda.
3. Meta: Valor da meta por dia.
4. 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:

1. Linha: Número da linha de topo em que se encontra a tabela, ela controla a rolagem da tabela para saber sua posição.
2. 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.

Curso 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: