Fr. Mark fra Kansas sendte inn ukens Excel-spørsmål:
Excel-pivottabeller fungerer best når dataene er delt inn i flest mulig poster, men dette er ikke alltid den mest intuitive måten å laste dataene inn i Excel. For eksempel er det intuitivt å laste inn data som figur 1, men den beste måten å analysere dataene er som i figur 2.
Først vil jeg gå gjennom Excels mindre enn perfekte måte å håndtere flere datafelter på. For det andre vil jeg demonstrere en enkel og rask makro for å konvertere figur 1 til figur 2.
Hvis dataene dine er som figur 1, er det under PivotTable Wizard trinn 3 av 4 mulig å dra alle de fire kvartfeltene inn i dataområdet til pivottabellen, som vist til høyre.
Her er det mindre enn perfekte resultatet. Som standard har Excel flere datafelter nedover siden. Du kan klikke på den grå "Data" -knappen og dra den opp og til høyre for å få kvartalene til å gå over siden. Du mangler imidlertid totaler for hver region, og kvartalsvisene vil alltid virke malplasserte.
Så, Fr. Mark traff spikeren på hodet da han sa at dataene virkelig må være i formatet på figur 2 for å kunne analysere det riktig. Nedenfor er en makro som raskt vil flytte data i formatet på figur 1 på ark 1 til ark 2 i formatet på figur 2. Denne makroen er ikke generell nok til å fungere med noe datasett. Det bør imidlertid være relativt enkelt å tilpasse det til din spesielle situasjon.
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
Etter å ha kjørt denne makroen, vil dataene være i det lettere å analysere formatet vist i figur 2 ovenfor. Nå, når du bruker disse dataene til en pivottabell, har du full kontroll over dataene som normalt.
