Fr. Kansasist pärit Mark saatis selle nädala Exceli küsimuse:
Exceli pöördtabelid töötavad kõige paremini siis, kui andmed on jaotatud võimalikult kirjeteks, kuid see pole alati kõige intuitiivsem viis andmete Excelisse laadimiseks. Näiteks on andmete laadimine nagu joonis 1 intuitiivne, kuid andmete analüüsimiseks on parim viis nagu joonisel 2.
Kõigepealt uurin Exceli vähem kui täiuslikku viisi mitme andmeväljaga tegelemiseks. Teiseks esitan lihtsa ja kiire makro, et teisendada joonis 1 jooniseks 2.
Kui teie andmed on nagu joonisel 1, on PivotTable-liigendtabeli viisardi 3. või 4. toimingu ajal võimalik lohistada kõik 4 veerandvälja pöördtabeli andmealale, nagu paremal näidatud.
Siin on vähem kui täiuslik tulemus. Vaikimisi on Exceli lehel allapoole mitu andmevälja. Võite klõpsata hallil nupul "Andmed" ja lohistada seda üles ja paremale, et veerandid läheksid üle lehe. Teil on aga puudu iga piirkonna kogusummad ja kvartali kogusummad tunduvad alati sobimatud.
Niisiis, Fr. Mark lõi naela pähe, kui ütles, et andmed peavad tõepoolest olema joonise 2 vormingus, et neid korralikult analüüsida. Allpool on makro, mis teisaldab andmed kiiresti lehel 1 joonisel 1 toodud vormingus lehele 2 joonise 2 vormingus. See makro ei ole ühegi andmekogumiga töötamiseks piisavalt üldine. Kuid seda peaks olema suhteliselt lihtne kohandada vastavalt oma konkreetsele olukorrale.
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
Pärast selle makro käivitamist on andmed ülaltoodud joonisel 2 näidatud hõlpsamini analüüsitavas vormingus. Nüüd, kui kasutate neid andmeid pöördtabeli jaoks, on teil täielik kontroll andmete üle nagu tavaliselt.
