Fr. Mark do Kansas enviou a pergunta sobre o Excel desta semana:
As tabelas dinâmicas do Excel funcionam melhor quando os dados são divididos no maior número possível de registros, mas nem sempre é a maneira mais intuitiva de carregar os dados no Excel. Por exemplo, é intuitivo carregar dados como a Figura 1, mas a melhor maneira de analisar os dados é como na Figura 2.
Primeiro, examinarei a maneira nada perfeita do Excel de lidar com vários campos de dados. Em segundo lugar, vou demonstrar uma macro simples e rápida para converter a figura 1 para a figura 2.
Se seus dados forem como a figura 1, durante a etapa 3 de 4 do Assistente de tabela dinâmica, é possível arrastar todos os campos de 4 quartos para a área de dados da tabela dinâmica, conforme mostrado à direita.
Aqui está o resultado menos que perfeito. Por padrão, o Excel tem vários campos de dados descendo na página. Você pode clicar no botão cinza "Dados" e arrastá-lo para cima e para a direita para fazer os trimestres atravessarem a página. No entanto, estão faltando totais para cada região e os totais do trimestre sempre parecerão fora do lugar.
Então, Fr. Mark acertou em cheio quando disse que os dados realmente precisam estar no formato da Figura 2 para analisá-los adequadamente. Abaixo está uma macro que moverá rapidamente os dados no formato da Figura 1 na Planilha1 para a Planilha2 no formato da Figura 2. Essa macro não é geral o suficiente para funcionar com qualquer conjunto de dados. No entanto, deve ser relativamente fácil personalizá-lo para sua situação particular.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Após executar essa macro, os dados ficarão no formato mais fácil de analisar mostrado na figura 2 acima. Agora, quando você usa esses dados para uma tabela dinâmica, tem controle total dos dados normalmente.
