Fr. Mark fra Kansas sendte i denne uges Excel-spørgsmål:
Excel-pivottabeller fungerer bedst, når dataene opdeles i de mest mulige poster, men det er ikke altid den mest intuitive måde at indlæse dataene i Excel på. For eksempel er det intuitivt at indlæse data som figur 1, men den bedste måde at analysere data på er som i figur 2.
For det første vil jeg køre gennem Excels mindre end perfekte måde at håndtere flere datafelter på. For det andet vil jeg demonstrere en enkel og hurtig makro til at konvertere figur 1 til figur 2.
Hvis dine data er som figur 1, er det under PivotTable Wizard trin 3 af 4 muligt at trække alle 4 kvartfelter ind i dataområdet i pivottabellen, som vist til højre.
Her er det mindre end perfekte resultat. Som standard har Excel de flere datafelter ned ad siden. Du kan klikke på den grå "Data" -knap og trække den op og til højre for at få kvartalerne til at gå over siden. Du mangler dog totaler for hver region, og kvartalsummene vil altid virke malplacerede.
Så, Fr. Mark ramte neglen på hovedet, da han sagde, at dataene virkelig skal være i formatet i figur 2 for at kunne analysere det korrekt. Nedenfor er en makro, der hurtigt flytter data i formatet i figur 1 på ark1 til ark2 i formatet på figur 2. Denne makro er ikke generel nok til at arbejde med ethvert datasæt. Det skal dog være relativt let at tilpasse det til din særlige situation.
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
Efter at have kørt denne makro, vil dataene være i det lettere at analysere format vist i figur 2 ovenfor. Når du nu bruger disse data til en pivottabel, har du fuld kontrol over dataene som normalt.
