Vr. Mark uit Kansas stuurde de Excel-vraag van deze week:
Excel-draaitabellen werken het beste wanneer de gegevens worden opgesplitst in de meeste records die mogelijk zijn, maar dit is niet altijd de meest intuïtieve manier om de gegevens in Excel te laden. Het is bijvoorbeeld intuïtief om gegevens te laden zoals in afbeelding 1, maar de beste manier om de gegevens te analyseren is zoals in afbeelding 2.
Eerst zal ik de minder dan perfecte manier van Excel doornemen om met meerdere gegevensvelden om te gaan. Ten tweede zal ik een eenvoudige en snelle macro demonstreren om figuur 1 naar figuur 2 te converteren.
Als uw gegevens zijn zoals in figuur 1, is het tijdens PivotTable Wizard stap 3 van 4 mogelijk om alle 4 kwartvelden naar het gegevensgebied van de draaitabel te slepen, zoals rechts wordt weergegeven.
Hier is het minder dan perfecte resultaat. Excel heeft standaard meerdere gegevensvelden die naar beneden op de pagina gaan. U kunt op de grijze "Data" -knop klikken en deze omhoog en naar rechts slepen om de kwartalen over de pagina te laten lopen. U mist echter totalen voor elke regio en de kwarttotalen zullen altijd misplaatst lijken.
Dus, Fr. Mark sloeg de spijker op zijn kop toen hij zei dat de gegevens echt het formaat van Figuur 2 moeten hebben om ze goed te kunnen analyseren. Hieronder ziet u een macro die snel gegevens zal verplaatsen in het formaat van Figuur 1 op Blad1 naar Blad2 in het formaat van Figuur 2. Deze macro is niet algemeen genoeg om met een dataset te werken. Het zou echter relatief eenvoudig moeten zijn om het aan uw specifieke situatie aan te passen.
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
Nadat u deze macro hebt uitgevoerd, hebben de gegevens de eenvoudiger te analyseren indeling die wordt weergegeven in figuur 2 hierboven. Wanneer u deze gegevens nu voor een draaitabel gebruikt, heeft u de volledige controle over de gegevens zoals normaal.
