Fr. Mark från Kansas skickade in veckans Excel-fråga:
Excel-pivottabeller fungerar bäst när data delas upp i så många poster som möjligt, men det här är inte alltid det mest intuitiva sättet att ladda in data i Excel. Det är till exempel intuitivt att ladda in data som figur 1, men det bästa sättet att analysera data är som i figur 2.
Först kommer jag att gå igenom Excels mindre än perfekt sätt att hantera flera datafält. För det andra kommer jag att demonstrera ett enkelt och snabbt makro för att konvertera figur 1 till figur 2.
Om dina data är som figur 1, under PivotTable Wizard steg 3 av 4, är det möjligt att dra alla fyra kvartalfält till dataområdet i pivottabellen, som visas till höger.
Här är det mindre än perfekta resultatet. Som standard har Excel flera datafält som går ner på sidan. Du kan klicka på den grå "Data" -knappen och dra den uppåt och åt höger för att få kvarteren att gå över sidan. Du saknar dock totalsummor för varje region och kvartssummorna verkar alltid på sin plats.
Så, Fr. Mark slog spiken på huvudet när han sa att data verkligen måste vara i formatet i figur 2 för att kunna analysera det ordentligt. Nedan finns ett makro som snabbt flyttar data i formatet i Figur 1 på Ark 1 till Ark 2 i formatet i Figur 2. Detta makro är inte tillräckligt generellt för att fungera med någon datamängd. Det bör dock vara relativt enkelt att anpassa det till din specifika 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 att ha kört detta makro kommer data att vara i det lättare att analysera formatet som visas i figur 2 ovan. Nu när du använder dessa data för en pivottabell har du full kontroll över data som normalt.
