https://www.youtube.com/watch?v=bo16ObVMrSo

Gráfico Google Analytics em Excel

Neste artigo é demonstrado e disponibilizado um gráfico semelhante ao encontrado no Google Analytics, este gráfico permite a comparação dinâmica de métricas, mudando ainda os intervalos de datas e as formas como os períodos são exibidos.

Google:

Excel:

Funcionamento

Esta planilha tem vários detalhes interessantes:

1. A base de dados pode ser atualizada sem afetar os cálculos, ela segue o conceito de dashboard.
2. A base só é limitada pela quantidade de linhas no Excel.
3. Não há cálculos na planilha de visualização do gráfico.
4. É possível comparar as três métricas da planilha no mesmo gráfico em dois eixos, mudando do gráfico do primeiro para o segundo eixo apenas com a seleção na combobox.
5. O período é selecionado através de um formulário VBA com um calendário desenvolvido pelo site e que pode ser utilizado em várias outras aplicações no Excel.
6. O gráfico pode ser exibido de várias formas diferentes, por Dia, Semana, Mês e Ano.
7. Há um código VBA que é executado sempre que é alterada alguma métrica, período ou agrupamento dos dados. Este código realiza o ajuste dos eixos Y do gráfico utilizando como base os valores máximos e mínimos, de modo que a visualização fique ótima.
8. Todas estas funcionalidades são dinâmicas e podem ser utilizadas em um dashboard e adaptada com o uso do recurso câmera.
9. A planilha é totalmente desbloqueada, inclusive o VBA e você poderá estudar o funcionamento.

Base de dados

Na base de dados as informações são estão dispostas no formato de tabela, ou seja, os dados estão na verticais aonde os cabeçalhos indicam os dados que são apresentados logo abaixo, os tipos de dados são semelhantes para cada coluna e as informações são referentes ao cabeçalho indicado e não misturados. Isto é o mínimo necessário para realizar uma boa análise de dados.

Cálculos

Na planilha de cálculos os dados da base são trabalhados com fórmulas dinâmicas que envolvem funções LIN, ÍNDICE, NÚMSEMANA, MÊS, ANO, DESLOC, CONT.SE, SE, SEERRO, SOMASES e FUNÇÕES MATRICIAIS.

Isto demonstra como a criação de dashboards vai muito além do simples criar um gráfico e vai muito mais para a preparação destes dados com relação a sua base e aos cálculos realizados.

GRÁFICO

O gráfico é baseado nos dados da planilha de cálculo, e utiliza intervalos nomeados com fórmulas para que o seu intervalo mude o conforme as alterações realizadas nas combobox e nos campos de datas.

Este intervalo nomeado faz com que os dados sejam ajustados e mudem a medida em que as informações são mudadas nas configurações.

O código abaixo é utilizado para ajustar os eixos Y do gráfico conforme as informações são alteradas, de modo a melhorara a visualização dos dados. As informações são baseadas nos valores máximo e mínimo de cada série de dados.

Sub lsAjustarEixos()
    ActiveSheet.ChartObjects("Gráfico 1").Activate
    ActiveChart.Axes(xlValue).MinimumScale = Plan2.Range("S12").Value
    ActiveChart.Axes(xlValue).MaximumScale = Plan2.Range("S13").Value
    
    ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = Plan2.Range("S14").Value
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = Plan2.Range("S15").Value
End Sub

DOWNLOAD

Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.

Baixe a planilha

Abraço

Marcos Rieper

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: