Tabela dinâmica do VBA do Excel

A Tabela Dinâmica do VBA ajuda a resumir os relatórios de um grande conjunto de dados. O Pivot pode considerar como uma forma minúscula de todo o conjunto de dados. A visualização rápida de grandes conjuntos de dados é possível através de uma tabela dinâmica. Uma tabela dinâmica é uma maneira fácil de filtrar dados de acordo. A partir dos dados disponíveis, você pode realçar os dados da maneira que desejar. Uma tabela dinâmica permite combinar os enormes dados, analisar os dados e produzir relatórios que atendam aos seus requisitos de negócios.

O excel oferece uma tabela dinâmica interna que é fácil de criar aplicando uma tabela dinâmica sobre uma folha de dados. É possível gerar um relatório automaticamente no Excel depois que os dados são fornecidos. Os códigos VBA ajudarão você a criar uma tabela dinâmica automática.

Como criar uma tabela dinâmica usando o Excel VBA?

As etapas comuns para inserir uma tabela dinâmica é primeiro inserir uma tabela dinâmica no menu Inserir e selecionar as tabelas que você deseja alterar em uma tabela dinâmica. A tabela selecionada se tornará os dados de origem e a tabela dinâmica será criada de acordo.

Para criar um relatório dinâmico, você deve selecionar os campos para os filtros, valores, etc. De maneira semelhante, essas etapas podem ser automatizadas usando os códigos VBA. Precisamos de uma planilha de dados do Excel para operar com uma tabela dinâmica. Vamos aprender como criar uma tabela dinâmica por meio dos códigos VBA do Excel.

Você pode fazer o download deste modelo do Excel de tabela dinâmica do VBA aqui - Modelo do Excel de tabela dinâmica do VBA

Etapa 1 : A tabela dinâmica deve ser considerada como um objeto de referência. Crie um objeto para a tabela dinâmica em outros códigos. Esse objeto será usado para apontar a tabela dinâmica. Nomeie a variável como pvtable. Declare uma função e objeto de tabela dinâmica.

Código:

 Sub Tabela Dinâmica () Dim pvtable As Sub Tabela Dinâmica Sub

Etapa 2: para manter os arquivos de dados, precisamos de um cache dinâmico para declarar a origem dos dados. Antes de criar uma tabela dinâmica, declare uma variável de caixa dinâmica. Declare a variável pvcache para cache dinâmico.

Código:

 Dim pvcache As PivotCache

Etapa 3: os dados estão na planilha e devem especificar um intervalo para atingir a célula que você precisa. Na tabela dinâmica, os dados de origem são espalhados como linhas e colunas, para apontar um intervalo específico que precisamos de uma variável. Defina pvrange como uma variável de intervalo.

Código:

 Dim pvrange As Range

Etapa 4: Uma planilha precisa inserir a tabela dinâmica que você precisa criar. Declare uma variável como uma planilha. Defina pvsheet como uma planilha.

Código:

 Dim pvsheet As Worksheet

Etapa 5: você precisa de uma variável semelhante para usar a folha de dados, que deve conter os dados que deseja plotar como uma tabela dinâmica. Portanto, a variável datasheet é declarada como pdsheet.

Código:

 Dim pdsheet As Worksheet

Etapa 6: você precisa de mais duas variáveis ​​com o tipo de dados longo para indicar a última linha e coluna usadas para a criação da tabela dinâmica. Pode ser qualquer linha ou coluna; portanto, existem chances de haver um número de linhas e colunas além do limite do tipo de dados inteiro. Vamos chamá-lo de plr e plc.

Código:

 Dim plr As Long Dim plc As Long

Etapa 7: a próxima etapa é excluir se já houver alguma tabela dinâmica criada. Isso ajudará a evitar confusões em que tabela os dados de origem serão plotados. Portanto, exclua a planilha da tabela dinâmica anterior e crie uma nova planilha para inserir a tabela dinâmica.

Código:

 On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = Planilhas falsas ("pvsheet"). Exclua 'para excluir a tabela dinâmica existente na planilha Worksheets.Add After: = ActiveSheet' para adicionar uma nova planilha ActiveSheet.Name = " pvsheet "'para renomear a planilha em" pvsheet "No erro Ir para 0

Etapa 8: atribua uma variável de objeto para a planilha dinâmica e a planilha de dados, respectivamente, às variáveis ​​pvsheet e pdsheet. Isso será usado ainda mais para especificar as planilhas.

Código:

 Definir pvsheet = planilhas ("pvsheet") Definir pdsheet = planilhas ("pdsheet")

Etapa 9: uma vez definidas as planilhas, o próximo item é: precisamos da última linha e coluna usadas para criar um relatório dinâmico. Encontre a última linha e coluna usadas usando as variáveis ​​declaradas plr e plc.

Código:

 'duas variáveis ​​a encontrar Última linha e coluna usada em pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

Etapa 10: como mencionamos anteriormente, os dados na planilha são especificados pelo intervalo de células. Você deve definir o alcance do pivô na próxima etapa. Já está declarado como variável para usar o intervalo pivot "pvrange".

Código:

 'inicializando o intervalo de dados da tabela dinâmica Configure pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)

Como o intervalo é definido usando a propriedade resize dos intervalos de células, o redimensionamento do intervalo de ajuste será ajustado de acordo. Portanto, o pvrange se ajustará se houver alguma adição ou exclusão de linhas ou colunas.

Etapa 11: é hora de definir o cache dinâmico, que é a fonte da tabela dinâmica. Use o objeto pvcache para definir o cache de origem.

Código:

 'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)

Etapa 12: aqui, o relatório de vendas de diferentes produtos será convertido em uma tabela dinâmica. Crie uma tabela dinâmica em branco na qual você pode adicionar mais o conjunto de dados.

Código:

 'nova tabela dinâmica em branco Defina pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Etapa 13: Depois que o pivô é inserido, é necessário especificar os diferentes campos que você deseja inserir na tabela dinâmica. Então, insira o campo da primeira linha. Aqui a primeira linha começa com o produto.

Código:

 'Inserir produto na linha arquivada com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Terminar com

Etapa 14: O próximo é especificar o segundo campo que você deseja inserir na tabela dinâmica. Da mesma maneira, insira a rua do campo da segunda linha na tabela dinâmica.

Código:

 'Inserir rua na linha arquivada e posição 2 Com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Terminar com

Etapa 15: insira o próximo campo na tabela dinâmica e a próxima é uma cidade. Dê o código para inserir o campo da cidade.

Código:

 'Inserir cidade na coluna arquivada com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 Terminar com

Etapa 16: até isso, os dados inseridos eram do tipo texto. Agora precisamos inserir o número de vendas na tabela dinâmica.

Código:

 'Insira a coluna Vendas no campo de dados Com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Terminar com

Etapa 17: você inseriu os campos que precisam criar uma tabela dinâmica. E a tabela dinâmica está quase concluída agora, você pode definir o formato da tabela dinâmica. Isso especificará o tipo de tabela através do estilo da tabela. O layout do eixo de linha também é definido da maneira que você deseja.

Código:

 'defina o formato Tabela Dinâmica pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

Etapa 18: para mostrar os itens de valores arquivados da linha em forma de tabela, adicione o código abaixo na parte inferior.

Código:

 'para mostrar a tabela dinâmica no formulário tabular pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

Etapa 19: pressione o botão executar ou pressione F5 para executar o código. Isso produzirá uma tabela dinâmica a partir da planilha de fonte de dados. Os dados fornecidos em linhas e colunas serão alterados para a tabela dinâmica. A tabela dinâmica ficará visível na planilha da tabela dinâmica.

Verifique a saída e você pode ver que a fonte de dados é convertida na tabela dinâmica, conforme abaixo, as colunas mencionadas são convertidas nos formulários de filtro.

Os campos dinâmicos são visíveis no lado direito. Você pode fazer alterações de acordo com sua necessidade de como os dados precisam ser exibidos.

Para sua referência, eu dei o código abaixo.

Código:

 Sub PivotTable () Dim pvtable Como PivotTable Dim pvcache Como PivotCache Dim pvrange como Range Dim pvsheet como planilha Dim pdsheet como planilha Dim plr como longo Dim dim plc com duração de erro Continuar próxima Application.DisplayAlerts = falso Application.ScreenUpdating = False Worksheets ("pvsheet "). Exclua 'para excluir a tabela dinâmica existente na planilha Worksheets.Add After: = ActiveSheet' para adicionar uma nova planilha ActiveSheet.Name =" pvsheet "'para renomear a planilha para" pvsheet "No erro GoTo 0 definir pvsheet = Planilhas ("pvsheet") Defina pdsheet = Worksheets ("pdsheet") 'duas variáveis ​​para localizar Última linha e coluna usada na pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'inicializando o intervalo de dados da tabela dinâmica Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nova tabela dinâmica em branco Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Inserir produto na linha arquivada com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 final com' Inserir rua para Linha arquivada e posição 2 Com pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Terminar com 'Inserir cidade na coluna arquivada com pvsheet.PivotTables ("Sales_Report"). PivotFields (" Cidade ") .Orientation = xlColumnField .Position = 1 Terminar com 'Inserir coluna de vendas no campo de dados Com pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 Terminar com' defina o formato Tabela Dinâmica pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" "para mostrar a tabela dinâmica no formulário tabular pvsheetReturnLayoutTamanho da Aplicação" .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Coisas para lembrar

  • Especifique a planilha de origem onde os dados devem fornecer para uma tabela dinâmica.
  • A linha e coluna inicial devem ser mencionadas corretamente.
  • Use os objetos necessários para identificar o cache dinâmico, o intervalo, o início e o final das linhas.
  • Organize a fonte de dados com o formato adequado, pois esse é o processo de automação.

Artigos recomendados

Este é um guia para a Tabela Dinâmica do VBA. Aqui discutimos como criar uma tabela dinâmica usando códigos VBA no excel, juntamente com um exemplo e um modelo para download do excel. Você também pode consultar os seguintes artigos para saber mais -

  1. Como excluir a tabela dinâmica no Excel?
  2. Usando o VBA no PowerPoint
  3. Tabela dinâmica com várias folhas
  4. Como criar um fatiador de tabela dinâmica?
  5. Atualizar tabela dinâmica no VBA
  6. VBA On Error Resume Next (Exemplos)
  7. Atualização de tela do VBA do Excel

Categoria:

VBA