Fr. Mark iz Kansasa poslao je ovotjedno Excel pitanje:
Zaokretne tablice programa Excel najbolje funkcioniraju kada se podaci raščlanjuju na najviše mogućih zapisa, ali to nije uvijek najintuitivniji način učitavanja podataka u Excel. Na primjer, intuitivno je učitavati podatke poput slike 1, ali najbolji način analize podataka je kao na slici 2.
Prvo ću proći kroz Excel-ov manje savršen način rada s više podatkovnih polja. Drugo, demonstrirat ću jednostavan i brz makro za pretvaranje slike 1 u sliku 2.
Ako su vaši podaci slični slici 1, tijekom koraka 3 od 4 čarobnjaka za zaokretne tablice, moguće je povući sva polja od 4 četvrtine u podatkovno područje zaokretne tablice, kao što je prikazano desno.
Evo manje savršenog rezultata. Prema zadanim postavkama Excel ima više podatkovnih polja koja se spuštaju niz stranicu. Možete kliknuti na sivi gumb "Podaci" i povući ga gore i desno kako bi četvrtine išle preko stranice. Međutim, nedostaju vam ukupni iznosi za svaku regiju, a ukupni kvartal uvijek će vam se činiti neumjesnima.
Dakle, vlč. Mark je udario čavao u glavu kad je rekao da podaci doista moraju biti u formatu slike 2 da bi se mogli pravilno analizirati. Ispod je makronaredba koja će brzo premjestiti podatke u formatu sa slike 1 na Sheet1 na Sheet2 u formatu sa slike 2. Ova makronaredba nije dovoljno općenita za rad s bilo kojim skupom podataka. Međutim, trebalo bi biti relativno lako prilagoditi ga vašoj određenoj situaciji.
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
Nakon pokretanja ove makronaredbe, podaci će biti u jednostavnijem obliku za analizu prikazanom na gornjoj slici 2. Sada, kada ove podatke koristite za zaokretnu tablicu, imate potpunu kontrolu podataka kao i obično.
