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:
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.
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.
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
Dim abaDados As Worksheet
Dim tabelaDados As ListObject
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
Obs. Agora vamos testar a macro, lembre-se de sempre salvar a macro antes de testar!
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?
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.
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
Como criar uma tabela dinâmica usando o VBA?
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.
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.
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?
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.
.AddDataField .PivotFields("Total"), "Soma", xlSum
Nesta linha vamos definir como vai aparecer o resultado e qual cálculo estamos fazendo.
.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.
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:
Resultado
Como colocar uma tabela dinâmica no VBA?
Dim graficoDinamico As Shape
Nesta linha estamos declarando o gráfico como shape que seria uma forma
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.
graficoDinamico.Top = novaAba.Range("G1").Top
graficoDinamico.Left = novaAba.Range("G1").Left
End Sub
Resultado
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
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
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,
Para acessar outras publicações de VBA, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.