Matricule-se
Voltar para o blog
VBA

Excel VBA para Análise de Dados

Raquel Araújo

Você já usou o VBA para análise de dados? Nesta aula vamos criar uma tabela dinâmica, colocar os filtros e inserir o gráfico pelo VBA!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fala Impressionadores! Hoje eu quero te mostrar como utilizar o VBA para análise de dados! Isso mesmo, eu vou te ensinar como usar o VBA para análise de dados dentro do Excel!

Nessa aula vou te mostrar:

  • Como filtrar informações no VBA?
  • Como ordenar informações no VBA?
  • Como criar uma tabela dinâmica no VBA?
  • Como gerar gráficos dinâmicos no VBA?

Essas são todas ações que você consegue fazer dentro do Excel normalmente, só que utilizando o VBA você deixa o processo mais rápido e automático.

Isso quer dizer, que se precisa fazer uma tarefa mais de uma vez, por mais complexa que seja, você pode automatizá-la com o VBA!

Já imaginou, aqueles trabalhos que você demora quase uma semana para finalizar, todas aquelas análises de dados. Imagina fazer isso com um clique após criar seu código?

VBA para Análise de Dados

Obs. Todos os códigos estão prontos na planilha que deixei disponível no material didático desta aula separados por tópico, recomendo que baixe o material e depois acompanhe a explicação.

Como filtrar informações no VBA?

A ideia aqui é te passar o código e posteriormente explicar cada parte dele.

Codigo
Código

Sub analiseDados()

Dim abaDados As Worksheet

Dim tabelaDados As ListObject

'Como filtrar informações no VBA

Set abaDados = Sheets("Dados")

Set tabelaDados = abaDados.ListObjects("TabelaDados")

With tabelaDados

    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes

    With .Range

        .AutoFilter Field:=4, Criteria1:="Lápis"

        .AutoFilter Field:=5, Criteria1:=">50"

    End With

O primeiro passo é levar as informações da nossa tabela para dentro do VBA.

VBA para Análise de Dados
tabela

Passar os códigos do Excel para o VBA é um processo que pode custar muito tempo, existem alguns modos de otimizar seu código, temos uma aula explicando tudo isso passo a passo, vou deixar o link aqui para você -> 4 passos para deixar seu código 16x mais rápido!

Trazendo as informações do Excel para o VBA

Codigo
Dim abaDados As Worksheet

Dim tabelaDados As ListObject
  • abadados será o nome da nossa planilha (tudo junto sem espaço no nome)
  • Worksheet corresponde a aba que estamos utilizando (aba ou planilha)
  • A segunda linha corresponde a um objeto que dentro do VBA vai armazenar a nossa planilha de Dados
Codigo
Set abaDados = Sheets("Dados")

Set tabelaDados = abaDados.ListObjects("TabelaDados")
  • Vamos agora atribuir nossos dados na aba, então toda vez que nos referirmos a abaDados estamos nos referindo aos Dados atribuídos
Codigo
With tabelaDados

    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes
  • Nesta linha estamos estabelecendo uma condição usando o If, então logo depois do If vamos verificar se existe filtro na tabela e se ela está filtrada ou não, ou seja, este código retorna um resultado booleano (verdadeiro ou falso).
  • Então se o resultado for verdadeiro e a tabela estiver filtrada o código executa o que estiver após o Then que, neste caso, é mostrar todas as informações.
Codigo
With .Range

        .AutoFilter Field:=4, Criteria1:="Lápis"

        .AutoFilter Field:=5, Criteria1:=">50"

    End With
  • No último bloco desse código estamos fazendo o que queremos efetivamente, então estamos usando o Range para se referir a uma célula.
  • Na segunda linha estamos informando ao código a coluna que estamos fazendo o filtro, no caso coluna 4 e por qual critério, procuramos pela palavra “Lápis”.

Obs. Agora vamos testar a macro, lembre-se de sempre salvar a macro antes de testar!

Ícone VBAVBA Impressionador

Como automatizar praticamente qualquer tarefa repetitiva que você faça dentro do Excel. Aprenda a criar integrações do Excel com o PowerPoint, Word, Outlook e muito mais. Aprenda a criar automações para resolver tudo com apenas um clique em um botão. Tudo isso com uma única linguagem que junta todos esses programas.

Começar agoraSeta para a direita
Fundo VBATelas VBA

Clique sobre as linhas do .AutoFilter usando o atalho F8 e observe que a planilha será filtrada por lápis e acima de 50 como programamos.

Como ordenar informações no VBA?

Codigo
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes

    With .Sort

        .SortFields.Clear

        .SortFields.Add Key:=Range("TabelaDados[[#All],[Item]]"), Order:=xlDescending

        .Apply

    End With

End With

Você vai observar neste segundo tópico que a lógica se repete um pouco, ficando cada vez mais fácil de entender.

Codigo
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes
  • Precisamos antes de ordenar a planilha retirar os filtros que colocamos no código anterior, para isso tornamos a usar a condição If.
Codigo
With .Sort

        .SortFields.Clear

        .SortFields.Add Key:=Range("TabelaDados[[#All],[Item]]"), Order:=xlDescending

        .Apply

    End With
  • Feito isso vamos usar o Sort, que significa ordenar:
  • Na segunda linha (SortFields.Clear) estamos definindo onde vamos ordenar limpando os campos
  • Na terceira vamos inserir um campo de ordenação
  • Por fim usamos o Apply para executar a função

Como criar uma tabela dinâmica usando o VBA?

Codigo
Dim novaAba As Worksheet

Dim tabelaDinamica As PivotTable  //PivotTable significa tabela dinâmica.

Set novaAba = Sheets.Add

novaAba.Name = "Tabela Dinâmica"

Nestas linhas de código estamos criando uma aba e ela será igual, ou seja, ela vai receber a tabela dinâmica.

Codigo
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tabelaDados).CreatePivotTable _

    TableDestination:=novaAba.Range("A1"), TableName:="TabelaDadosDinâmica"

Na linha acima estamos definindo que a planilha é uma planilha de dados, aqui determinamos que será aberta uma nova aba e também qual nome essa aba vai ter.

Codigo
Set tabelaDinamica = novaAba.PivotTables("TabelaDadosDinâmica")

Quando a planilha for aberta podemos formatar e organizar de forma manual como fazemos com a tabela dinâmica normalmente, mas também podemos automatizar esses ajustes pelo VBA. Vamos aprender como?

Codigo
With tabelaDinamica

    With .PivotFields("Região")

        .Orientation = xlColumnField

        .Position = 1

    End With

    With .PivotFields("Item")

        .Orientation = xlRowField

        .Position = 1

    End With

Observe que estamos acrescentando a coluna Região e o Item na tabela dinâmica.

Codigo
.AddDataField .PivotFields("Total"), "Soma", xlSum

Nesta linha vamos definir como vai aparecer o resultado e qual cálculo estamos fazendo.

Codigo
.PivotFields("Soma").NumberFormat = "_-$ * #,##0_-;-$ * #,##0_-;_-$ * "" - ""_-;_-@_-"

End With

Nesta última linha vamos formatar o campo de soma, este código acima cheio de # e caracteres podemos adquirir clicando no atalho Control + 1 ou indo na guia página inicial em formatação no Excel.

Usando este atalho irá aparecer uma janela, nela selecione qual a formatação desejada e após isso clique em personalizado -> copie o código.

formatar células
formatar células

Este código precisa de sutis alterações, quando for inserir no ambiente do VBA, a questão é que o VBA está em inglês e por isso:

  • Tudo que é ponto deve virar vírgula e vice-versa
  • Outra coisa que devemos retirar é o R do R$, deixando apenas o $
  • E as aspas simples do meio do código devem ser modificadas para aspas duplas

Resultado

resultado
resultado

Como colocar uma tabela dinâmica no VBA?

Codigo
Dim graficoDinamico As Shape

Nesta linha estamos declarando o gráfico como shape que seria uma forma

Codigo
Set graficoDinamico = novaAba.Shapes.AddChart2(297, xlColumnStacked)

Aqui informamos em qual aba o gráfico vai aparecer, nesta aba adicionamos um gráfico e entre parênteses colocamos o estilo e tipo do gráfico.

Obs. Você pode usar o atalho F2 para encontrar o tipo do gráfico que deseja declarar.

Feito isso o gráfico já está criado, falta apenas ajustar a posição dele na tela, isso as duas linhas de código abaixo estão fazendo, colocamos o gráfico na célula G1 ao lado da planilha dinâmica.

Codigo
graficoDinamico.Top = novaAba.Range("G1").Top

graficoDinamico.Left = novaAba.Range("G1").Left

End Sub

Resultado

resultado - final
resultado – final

Agora o código está pronto, seja qual for a alteração feita na planilha, entrando no ambiente VBA e clicando em F5 esta tabela surge instantaneamente seguindo esta programação que fizemos!

Código completo

VBA
Sub analiseDados()

Dim abaDados As Worksheet

Dim tabelaDados As ListObject

'Como filtrar informações no VBA

Set abaDados = Sheets("Dados")

Set tabelaDados = abaDados.ListObjects("TabelaDados")

With tabelaDados

    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes

    With .Range

        .AutoFilter Field:=4, Criteria1:="Lápis"

        .AutoFilter Field:=5, Criteria1:=">50"

    End With

    'Como ordenar informações no VBA

    If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'Remover filtros existentes

    With .Sort

        .SortFields.Clear

        .SortFields.Add Key:=Range("TabelaDados[[#All],[Item]]"), Order:=xlDescending

        .Apply

    End With

End With

'Como criar uma tabela dinâmica pelo VBA

Dim novaAba As Worksheet

Dim tabelaDinamica As PivotTable

Set novaAba = Sheets.Add

novaAba.Name = "Tabela Dinâmica"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tabelaDados).CreatePivotTable _

    TableDestination:=novaAba.Range("A1"), TableName:="TabelaDadosDinâmica"

Set tabelaDinamica = novaAba.PivotTables("TabelaDadosDinâmica")

With tabelaDinamica

    With .PivotFields("Região")

        .Orientation = xlColumnField

        .Position = 1

    End With

    With .PivotFields("Item")

        .Orientation = xlRowField

        .Position = 1

    End With

    .AddDataField .PivotFields("Total"), "Soma", xlSum

    .PivotFields("Soma").NumberFormat = "_-$ * #,##0_-;-$ * #,##0_-;_-$ * "" - ""_-;_-@_-"

End With

'Como gerar gráficos dinâmicos pelo VBA

Dim graficoDinamico As Shape

Set graficoDinamico = novaAba.Shapes.AddChart2(297, xlColumnStacked)

graficoDinamico.Top = novaAba.Range("G1").Top

graficoDinamico.Left = novaAba.Range("G1").Left

End Sub

Conclusão – VBA para Análise de Dados

Você já sabia fazer uma tabela dinâmica com gráfico no VBA? Fiz essa aula para te apresentar o código e a estrutura por trás dessa tabela.

Embora possamos fazer esses ajustes pelo Excel, usar o VBA garante rapidez e você vai notar que mesmo que a tabela seja filtrada de outra forma ou que o usuário retire a tabela dinâmica você consegue retornar ao resultado programado em poucos cliques.

Espero que tenham gostado da aula! Eu fico por aqui! Abraço,

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!

Quer aprender mais sobre VBA com um minicurso básico gratuito?
Raquel Araújo

Autor do artigo

Raquel Araújo

Redatora de Publicações

Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.

Ver mais posts