Fr. A Kansasból érkezett Mark beküldte az e heti Excel kérdést:
Az Excel pivot táblázatok akkor működnek a legjobban, ha az adatokat a lehető legtöbb rekordra bontják, de ez nem mindig a leg intuitívabb módszer az adatok Excelbe történő betöltésére. Például intuitív olyan adatokat betölteni, mint az 1. ábra, de az adatok elemzésének legjobb módja a 2. ábra.
Először végigvezetem az Excel nem tökéletes megoldását több adatmező kezelésére. Másodszor, bemutatok egy egyszerű és gyors makrót az 1. ábra 2. ábrává alakítására.
Ha az adatai megegyeznek az 1. ábrával, akkor a PivotTable Wizard 4/4. Lépésében a négy negyedmezőt áthúzhatja a forgatótábla adatterületére, a jobb oldalon látható módon.
Itt van a kevésbé tökéletes eredmény. Alapértelmezés szerint az Excel több adatmezővel rendelkezik az oldalon. Kattintson a szürke "Data" gombra, és húzza felfelé és jobbra, hogy a negyedek átkerüljenek az oldalon. Hiányzik azonban az egyes régiók összesítése, és a negyedévi összesítések mindig helytelennek tűnnek.
Tehát Fr. Mark fején találta a szöget, amikor azt mondta, hogy az adatoknak a 2. ábra formátumában kell lenniük annak megfelelő elemzéséhez. Az alábbiakban bemutatunk egy makrót, amely gyorsan áthelyezi az adatokat az 1. lap 1. formátumában az 1. lapon a 2. lapra a 2. ábra formátumában. Ez a makró nem elég általános ahhoz, hogy bármilyen adatkészlettel működjön. Viszont viszonylag könnyűnek kell lennie az adott helyzethez való testreszabáshoz.
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
A makró futtatása után az adatok a fenti 2. ábrán látható könnyebben elemezhető formátumban lesznek. Most, amikor ezeket az adatokat egy forgatótáblához használja, teljes mértékben ellenőrizheti az adatokat, mint a szokásos módon.
