O. Mark z Kansasu poslal tento týždeň otázku v programe Excel:
Kontingenčné tabuľky programu Excel fungujú najlepšie, keď sú údaje rozdelené do čo najväčšieho počtu záznamov, čo však nie je vždy najintuitívnejší spôsob načítania údajov do programu Excel. Napríklad je intuitívne načítať údaje, ako je obrázok 1, ale najlepší spôsob, ako analyzovať údaje, je ako na obrázku 2.
Najskôr si prejdem menej ako dokonalý spôsob práce s viacerými údajovými poľami v Exceli. Po druhé, ukážem jednoduché a rýchle makro na prevod obrázka 1 na obrázok 2.
Ak sú vaše údaje podobné ako na obrázku 1, počas kroku 3 až 4 v Sprievodcovi kontingenčnou tabuľkou je možné pretiahnuť všetky polia štvrtej štvrtiny do dátovej oblasti kontingenčnej tabuľky, ako je to znázornené vpravo.
Tu je výsledok menej ako dokonalý. V predvolenom nastavení má Excel niekoľko údajových polí smerujúcich nadol po stránke. Môžete kliknúť na sivé tlačidlo „Údaje“ a presunúť ho hore a doprava, aby sa štvrtiny pohybovali po celej stránke. Chýbajú vám však súčty pre každý región a súčty za štvrťrok sa budú vždy javiť ako nepatričné.
Takže, o. Mark zasiahol klinec po hlavičke, keď povedal, že údaje musia byť skutočne vo formáte na obrázku 2, aby sme ich mohli správne analyzovať. Ďalej je uvedené makro, ktoré rýchlo presunie údaje vo formáte na obrázku 1 na hárku1 do hárku2 vo formáte na obrázku 2. Toto makro nie je dostatočne všeobecné na to, aby fungovalo s ľubovoľnou množinou údajov. Malo by však byť pomerne ľahké prispôsobiť ho vašej konkrétnej situácii.
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 spustení tohto makra budú údaje vo formáte, ktorý sa dá ľahšie analyzovať, ako je to znázornené na obrázku 2 vyššie. Keď teraz použijete tieto údaje pre kontingenčnú tabuľku, máte nad nimi kontrolu ako obvykle.
