Pesquisa de site

Aprenda como criar tabelas dinâmicas automaticamente no Excel com VBA

As tabelas dinâmicas são uma ferramenta poderosa de visualização de dados, mas podem ser difíceis de configurar. Elimine a frustração desse processo com um script VBA.

As tabelas dinâmicas no Excel são essenciais para tornar os dados mais fáceis de entender e compreender. Uma tabela dinâmica pode condensar e processar dados em estruturas significativas. Os usuários do MS Excel os adotaram amplamente na indústria de dados.

Você sabia que pode automatizar suas tabelas dinâmicas no Excel e criá-las com um único clique? MS Excel integra-se bem com VBA e se tornou uma excelente ferramenta para automatizar tarefas repetitivas.

Veja como você pode automatizar uma tabela dinâmica com uma macro no MS Excel VBA.

Use um conjunto de dados práticos

Você pode baixar e usar um conjunto de dados fictício do Tableau para seguir o script VBA deste guia. O código VBA funcionará com qualquer outro conjunto de dados, com alguns ajustes básicos. Antes de começar, certifique-se de ter habilitado macros em sua pasta de trabalho do Excel.

Existem algumas colunas essenciais que você pode usar na tabela dinâmica. Para entender as nuances e a estrutura final da tabela, você pode criar manualmente uma tabela dinâmica básica com os seguintes elementos:

  • Filtro: Região
  • Linhas: Subcategoria
  • Colunas: Estado
  • Valores: Vendas

O pivô final deve ficar assim:

No entanto, você pode deixar o VBA fazer isso automaticamente, em vez de prepará-lo manualmente.

Como criar tabelas dinâmicas automaticamente no Excel

Para automatizar suas tabelas dinâmicas com VBA, abra um novo arquivo Excel e renomeie as planilhas da seguinte forma:

  • Primeira planilha: Macro
  • Segunda planilha: Dados

A planilha Macro contém o script da macro, enquanto a planilha Dados contém seus dados. Na planilha de macro, você pode inserir qualquer forma de sua escolha e atribuir a macro a ela. Clique com o botão direito na forma e clique em Atribuir Macro.

Na caixa de diálogo a seguir, clique no nome da sua macro e clique em OK. Esta etapa atribui a macro à forma.

1. Abra o editor de codificação Excel VBA

Pressione Alt + F11 para abrir o editor de código. Quando estiver no editor de código, clique com o botão direito no nome do arquivo, seguido de Inserir e Módulo. É importante lembrar que você escreverá todo o código VBA dentro de um módulo antes de executá-lo.

É uma boa prática usar um nome de módulo que corresponda à finalidade do código. Como esta é uma demonstração, você pode definir o nome do módulo da seguinte forma:

sub pivot_demo()

O nome do módulo termina com End Sub, que é o comando final de um módulo:

End Sub

2. Declarar Variáveis

Dentro do módulo, comece declarando variáveis para armazenar alguns valores definidos pelo usuário que você usará no script. Você pode usar a instrução Dim para declarar variáveis, como segue:

Dim PSheet As Worksheet, DSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim Last_Row As Long, Last_Col As Long
Dim sht1 as Variant

Você usará essas variáveis para o seguinte:

  • PSheet: A planilha de destino, onde o VBA criará um pivô.
  • DSheet: a planilha de dados.
  • PvtCache: um cache dinâmico contém o pivô.
  • PvtTable: o objeto da tabela dinâmica.
  • PvtRange: um intervalo de dados para o pivô.
  • Last_Row e Last_Col: Última linha e coluna preenchidas na planilha de dados (DSheet).
  • Sht1: Esta variável é uma variante.

3. Suprimir avisos e mensagens

Erros, avisos e mensagens desnecessários tornam seus códigos VBA mais lentos. Ao suprimir essas mensagens, você pode acelerar consideravelmente o processo.

Use o seguinte código:

On Error Resume Next
 
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Onde :

  • On Error Resume Next: Esta cláusula suprime quaisquer erros de tempo de execução.
  • Aplicação: A aplicação refere-se ao MS Excel.
  • DisplayAlerts: A propriedade DisplayAlerts define se os alertas devem ser mostrados.
  • ScreenUpdating: Esta propriedade define se as alterações devem ser atualizadas em tempo real ou apenas após a execução do código.

Quando esse código é executado, ele suprime quaisquer alertas, avisos e mensagens que o Excel mostraria de outra forma. Você pode desativar os parâmetros DisplayAlerts e ScreenUpdating definindo seus valores como False.

Perto do final do código, você pode ativá-los novamente definindo o valor como True.

4. Exclua todas as planilhas dinâmicas existentes

Para criar uma nova tabela dinâmica, você tem duas opções. Em primeiro lugar, exclua a planilha dinâmica existente e use o VBA para criar uma nova planilha para armazenar a planilha dinâmica. Como alternativa, você pode usar uma planilha existente para manter o pivô.

Neste guia, vamos criar uma nova planilha dinâmica para armazenar a tabela dinâmica.

O loop for each percorre cada planilha da pasta de trabalho e armazena o nome da planilha na variável sht1. Você pode usar qualquer nome de variável (sht1) para armazenar o nome da planilha. O loop percorre cada planilha da pasta de trabalho atual, procurando uma com o nome específico (Pivot).

Quando o nome da planilha corresponde, ele exclui a planilha e passa para a próxima planilha. Depois que o código verifica todas as planilhas, ele sai do loop e passa para a próxima parte do código, que adiciona uma nova planilha, Pivot.

Veja como você pode fazer isso:

For Each sht1 In ActiveWorkbook.Worksheets
    If sht1.Name = "Pivot" Then
        sht1.Delete
    End If
Next sht1
 
Worksheets.Add.Name = "Pivot"

5. Definir fonte de dados e planilhas dinâmicas

É fundamental criar variáveis para armazenar as referências das planilhas Pivot e Data. Eles funcionam como atalhos, que você pode consultar no restante do código.

Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")

6. Identifique a última linha e coluna usadas

Esta parte do código funciona dinamicamente, pois dimensiona a última linha e coluna preenchida nos dados.

Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col)

Onde :

  • Last_Row: Variável para armazenar o último número de linha preenchido, ou seja, 9995
  • Last_Col: variável para armazenar o último número da coluna preenchida, ou seja, 21
  • PvtRange: PvtRange faz referência a todo o intervalo de dados do pivô

7. Crie um cache dinâmico e uma tabela dinâmica

Um cache dinâmico contém a tabela dinâmica; portanto, você precisa criar um cache antes de criar uma tabela dinâmica. Você deve usar as referências de sintaxe do VBA para criar o cache dinâmico na planilha dinâmica.

Ao fazer referência ao cache dinâmico, você precisa criar uma tabela dinâmica. Como parte da tabela dinâmica, você pode definir a planilha, a referência da célula e o nome da tabela dinâmica.

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="MUODemoTable")
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="MUODemoTable")

Onde :

  • ActiveWorkbook: a pasta de trabalho atual onde você tem a planilha de dados e dinâmica.
  • PivotCaches.Create: Sintaxe padrão para criar um cache dinâmico.
  • SourceType: Como você tem os dados na pasta de trabalho, você pode defini-los como xlDatabase. Algumas outras opções incluem xlConsolidation, xlExternal ou xlPivotTable.
  • SourceData: você pode fazer referência ao intervalo dinâmico anterior como os dados de origem.
  • CreatePivotTable: comando padrão para criar a tabela dinâmica.
  • TableDestination: você precisa especificar as referências de planilha e célula onde deseja criar o pivô.
  • TableName: especifique um nome de tabela dinâmica.
  • CreatePivotTable: comando padrão para criar a tabela dinâmica dentro do cache dinâmico.

8. Insira linhas, colunas, filtros e valores

Como a tabela dinâmica está pronta, você precisa começar a adicionar os parâmetros nos filtros, linhas, colunas e valores de agregação. Você pode usar o comando pivotfields do VBA para começar a declarar os detalhes.

Para adicionar valores de filtro:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Region")
.Orientation = xlPageField
End With

Para adicionar valores de linha:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sub-Category")
.Orientation = xlRowField
End With

Para adicionar valores de coluna:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("State")
.Orientation = xlColumnField
End With

Para adicionar valores de agregação:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With

É essencial observar que você deve fazer referência à planilha ativa (planilha dinâmica), seguida do nome da tabela dinâmica e do nome da variável. Quando precisar adicionar filtros, linhas e colunas, você pode alternar entre várias sintaxes, que incluem o seguinte:

  • xlPageField: para adicionar filtros.
  • xlRowField: para adicionar linhas.
  • xlRowField: para adicionar colunas.

Finalmente, você pode usar o comando xlDataField para calcular as agregações de valores. Você pode usar outras funções agregadas como xlSum, xlAverage, xlCount, xlMax, xlMin e xlProduct.

9. Executando o código VBA do Excel para criar pivôs automáticos

Finalmente, quando todo o programa estiver pronto, você poderá executá-lo pressionando F5 ou clicando no botão play. Ao voltar para a planilha dinâmica em sua pasta de trabalho, você verá que uma nova tabela dinâmica está pronta para revisão.

Se quiser ver uma execução passo a passo de como o comando de código é executado linha por linha, você pode navegar até o editor de código e pressionar F8 várias vezes. Dessa forma, você pode ver como funciona cada linha de código e como o VBA cria seus pivôs automaticamente.

Aprendendo a codificar tabelas dinâmicas automaticamente

Os pivôs não estão restritos apenas ao MS Excel. Linguagens de programação como Python permitem criar pivôs otimizados com apenas algumas linhas de código.

A otimização de dados não pode ser mais fácil do que isso. Você pode escolher efetivamente seus comandos em Python e realizar uma estrutura dinâmica semelhante à do Excel com facilidade.

Artigos relacionados: