Ks. Mark z Kansas wysłał w tym tygodniu pytanie dotyczące programu Excel:
Tabele przestawne programu Excel działają najlepiej, gdy dane są podzielone na możliwie największą liczbę rekordów, ale nie zawsze jest to najbardziej intuicyjny sposób ładowania danych do programu Excel. Na przykład ładowanie danych, takich jak rysunek 1, jest intuicyjne, ale najlepszy sposób analizy danych jest taki, jak na rysunku 2.
Najpierw przejrzę mniej niż doskonały sposób radzenia sobie z wieloma polami danych w programie Excel. Po drugie, przedstawię proste i szybkie makro konwertujące rysunek 1 na rysunek 2.
Jeśli Twoje dane są takie jak na rysunku 1, podczas kroku 3 z 4 Kreatora tabel przestawnych można przeciągnąć wszystkie cztery ćwiartki pól do obszaru danych tabeli przestawnej, jak pokazano po prawej stronie.
Oto wynik mniej niż doskonały. Domyślnie Excel ma wiele pól danych na dole strony. Możesz kliknąć szary przycisk „Dane” i przeciągnąć go w górę iw prawo, aby ćwiartki przesunęły się w poprzek strony. Brakuje jednak sum dla każdego regionu, a sumy za kwartał zawsze będą wydawać się nie na miejscu.
Tak więc ks. Mark trafił w sedno, gdy powiedział, że dane naprawdę muszą być w formacie pokazanym na rysunku 2, aby móc je właściwie przeanalizować. Poniżej znajduje się makro, które szybko przeniesie dane w formacie z rysunku 1 z arkusza Sheet1 do arkusza2 w formacie z rysunku 2. To makro nie jest wystarczająco ogólne, aby pracować z dowolnym zestawem danych. Jednak dostosowanie go do konkretnej sytuacji powinno być stosunkowo łatwe.
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
Po uruchomieniu tego makra dane będą w łatwiejszym do analizy formacie pokazanym na rysunku 2 powyżej. Teraz, gdy używasz tych danych do tabeli przestawnej, masz pełną kontrolę nad danymi, jak zwykle.
