Fr. Mark z Kansasu poslal tento týden Excel otázku:
Kontingenční tabulky aplikace Excel fungují nejlépe, když jsou data rozdělena na nejvíce možných záznamů, ale toto není vždy nejintuitivnější způsob načtení dat do aplikace Excel. Například je intuitivní načítat data, jako je obrázek 1, ale nejlepší způsob, jak data analyzovat, je jako na obrázku 2.
Nejprve projdu méně než dokonalým způsobem řešení aplikace Excel s více datovými poli. Zadruhé ukážu jednoduché a rychlé makro pro převod obrázku 1 na obrázek 2.
Pokud jsou vaše data jako na obrázku 1, je možné během kroku 3 ze 4 Průvodce kontingenční tabulkou přetáhnout všechna 4 čtvrtinová pole do datové oblasti kontingenční tabulky, jak je znázorněno vpravo.
Zde je výsledek méně než dokonalý. Ve výchozím nastavení má aplikace Excel několik datových polí směřujících dolů po stránce. Můžete kliknout na šedé tlačítko „Data“ a přetáhnout ho nahoru a doprava, aby se čtvrtiny pohybovaly po stránce. Chybí vám však součty pro každý region a součty za čtvrtletí se vždy zdají být na místě.
Takže, Fr. Mark zasáhl hřebík po hlavě, když řekl, že data musí být opravdu ve formátu na obrázku 2, aby byla správně analyzována. Níže je makro, které rychle přesune data ve formátu obrázku 1 na listu 1 do listu 2 ve formátu obrázku 2. Toto makro není dostatečně obecné pro práci s libovolnou sadou dat. Mělo by však být relativně snadné jej přizpůsobit konkrétní situaci.
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 spuštění tohoto makra budou data ve snadněji analyzovatelném formátu, jak je znázorněno na obrázku 2 výše. Nyní, když použijete tato data pro kontingenční tabulku, máte nad daty plnou kontrolu jako obvykle.
