Fr. Mark iz Kansasa je ta teden poslal vprašanje v Excelu:
Vrtilne tabele Excela najbolje delujejo, če so podatki razčlenjeni na največ možnih zapisov, vendar to ni vedno najbolj intuitiven način za nalaganje podatkov v Excel. Na primer, intuitivno je naložiti podatke, kot je slika 1, vendar je najboljši način za analizo podatkov kot na sliki 2.
Najprej bom pregledal Excel-ov manj popoln način obravnave več podatkovnih polj. Drugič, prikazal bom preprost in hiter makro za pretvorbo slike 1 v sliko 2.
Če so vaši podatki podobni sliki 1, lahko med korakom čarovnika vrtilne tabele 3 od 4 povlečete vsa 4 četrtinska polja v podatkovno območje vrtilne tabele, kot je prikazano na desni.
Tukaj je manj kot popoln rezultat. Excel ima privzeto več podatkovnih polj, ki gredo navzdol po strani. Lahko kliknete sivi gumb "Podatki" in ga povlečete navzgor in desno, da četrtine gredo čez stran. Vendar za vsako regijo manjkajo skupni zneski in skupni zneski za četrtino bodo vedno videti na mestu.
Torej, p. Mark je udaril žebelj v glavo, ko je dejal, da morajo biti podatki resnično v obliki slike 2, da jih lahko pravilno analiziramo. Spodaj je makro, ki bo podatke v obliki s slike 1 na Sheet1 hitro premaknil na Sheet2 v obliki slike 2. Ta makro ni dovolj splošen za delo s katerim koli naborom podatkov. Vendar pa bi moralo biti razmeroma enostavno prilagoditi vašo situacijo.
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 zagonu tega makra bodo podatki v lažje analizirani obliki, prikazani na sliki 2 zgoraj. Zdaj, ko uporabljate te podatke za vrtilno tabelo, imate popoln nadzor nad podatki kot običajno.
