Excel Drilldown tabela dinâmica sempre na mesma planilha
O detalhamento de dados da tabela dinâmica com o duplo clique é um excelente recurso do Excel, neste artigo é demonstrado como fazer drilldown dos dados da tabela dinâmica sempre na mesma planilha, tornando a planilha muito mais profissional.
A tabela dinâmica é uma das melhores ferramentas do Excel, porém ela possui algumas limitações como por exemplo a que demonstro como burlar neste artigo.
Uma das principais funcionalidades de uma tabela dinâmica é realizar o Drilldown dos dados, exportando estas informações para uma nova planilha, o problema é exatamente este, sempre é uma nova planilha, e logo você tem que ficar excluindo sempre estes dados.
No código comentado abaixo e na planilha em anexo, nós temos um exemplo de como, ao realizar o Drill Drown, abrir estes dados sempre em uma mesma planilha e criar também um hiperlink automaticamente para voltar a planilha inicial.
A planilha se torna bastante prática, dado que você não perderá mais o tempo de excluir a planilha criada, e também não precisará mais ficar procurando a planilha aonde você estava antes.
Abaixo o código comentado, ele deve ser colocado na planilha aonde está a tabela dinâmica.
Coloque estes dados em um módulo, conforme demonstrado no vídeo. Este código é adaptável á qualquer tabela dinâmica.
Public Sub lsExpandirDados() On Error GoTo Sair Dim lPlanCriada As String Dim lPlanilhaOriginal As String Application.ScreenUpdating = False 'Captura o nome da planilha aonde está a tabela dinâmica lPlanilhaOriginal = ActiveSheet.Name & "!" & ActiveCell.Address 'Desabilitar mostrar alertas Application.DisplayAlerts = False 'Limpa a planilha dados Sheets("Dados").Range("A:XFD").Clear 'Abre os detalhes dos registros da tabela dinâmica Selection.ShowDetail = True 'Guarda o nome da tabela criada lPlanCriada = ActiveSheet.Name 'Copiar os dados abertos Selection.Copy 'Colar as informações na tabela dados Sheets("Dados").Select Range("A1").Activate Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Excluir a planilha criada Worksheets(lPlanCriada).Delete 'Organizar as colunas Selection.EntireColumn.AutoFit Sheets("Dados").Range("B2").Select ActiveWindow.FreezePanes = True Worksheets("Dados").Range("A1").Select Selection.End(xlToRight).Select Worksheets("Dados").Cells(1, ActiveCell.Column + 2).Select ActiveCell.Value = "Voltar" 'Criar o hyperlink ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ lPlanilhaOriginal, TextToDisplay:="Voltar" Sair: Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
No vídeo no topo deste artigo é demonstrado passo-a-passo como incluir este código fonte e adaptar a sua tabela dinâmica.
Para executar este código inclua no código fonte da planilha em que há a tabela dinâmica o seguinte código:
'Evento que é chamado antes do duplo clique da planilha Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Sub que realiza a abertura dos dados lsExpandirDados End Sub
Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.
Abraço
Marcos Rieper
