{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Pivot","Podklady - VBA kódy"],"not_rendered":{"charts":0,"images":27,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Pivot","index":0,"n_rows":58,"n_cols":11,"rows_kept":58,"cut":false,"hidden":false,"merged":[]}
["ID","Jmeno","Země","DruhSportu","Prodavaný \nprodukt","Zisk","Datum prodeje","Rok","Mesic"]
[1,"Fantomas","CZ","Golf","Odpalovací hůl",66.599999999999994,"2006-01-01",2006,1]
[2,"Pavel","SK","Hokej","Hokejka",87,"2006-01-05",2006,1]
[3,"Fantomas","CZ","Golf","Golfový míček",48,"2006-02-07",2006,2]
[4,"Fantomas","CZ","Hokej","Chrániče",106,"2006-02-15",2006,2]
[5,"Pavel","SK","Golf","Odpalovací hůl",61.2,"2006-03-26",2006,3]
[6,"Fantomas","CZ","Hokej","Chrániče",116.6,"2006-03-10",2006,3,null,"KT základ"]
[7,"Pavel","SK","Golf","Golfový míček",31.5,"2006-04-13",2006,4]
[8,"Pavel","SK","Tenis","Raketa",41.4,"2006-04-10",2006,4]
[9,"Monika","SK","Tenis","Míček",48,"2006-05-24",2006,5]
[10,"Rakosniček","CZ","Golf","Golfový míček",55.5,"2006-05-02",2006,5]
[11,"Krakonoš","CZ","Golf","Golfový míček",58.5,"2006-06-09",2006,6]
[12,"Fantomas","CZ","Golf","Odpalovací hůl",206.7,"2006-06-18",2006,6]
[13,"Rakosniček","CZ","Hokej","Chrániče",153.69999999999999,"2006-07-28",2006,7]
[14,"Pavel","SK","Hokej","Hokejka",57,"2006-07-13",2006,7,null,"Řádky"]
[15,"Fantomas","CZ","Tenis","Raketa",240,"2006-08-11",2006,8]
[16,"Pavel","SK","Hokej","Chrániče",93,"2006-08-07",2006,8]
[17,"Rakosniček","CZ","Hokej","Chrániče",148.4,"2006-09-02",2006,9]
[18,"Fantomas","CZ","Golf","Odpalovací hůl",39.6,"2006-09-16",2006,9]
[19,"Krakonoš","CZ","Hokej","Puk",75,"2006-10-02",2006,10,null,"Sloupce"]
[20,"Monika","SK","Golf","Odpalovací hůl",61.2,"2006-10-07",2006,10]
[21,"Fantomas","CZ","Hokej","Chrániče",148.4,"2006-11-22",2006,11]
[22,"Pavel","SK","Hokej","Hokejka",76,"2006-11-06",2006,11]
[23,"Krakonoš","CZ","Tenis","Raketa",197.2,"2006-12-21",2006,12]
[24,"Pavel","SK","Tenis","Míček",127.2,"2006-12-18",2006,12,null,"Filtry"]
[25,"Monika","SK","Hokej","Chrániče",201.4,"2007-01-27",2007,1]
[26,"Pavel","SK","Golf","Odpalovací hůl",55.8,"2007-01-13",2007,1]
[27,"Rakosniček","CZ","Hokej","Hokejka",156.4,"2007-02-22",2007,2]
[28,"Monika","SK","Hokej","Puk",292.5,"2007-02-05",2007,2]
[29,"Krakonoš","CZ","Golf","Odpalovací hůl",41.4,"2007-03-23",2007,3]
[30,"Rakosniček","CZ","Tenis","Raketa",185.5,"2007-03-12",2007,3]
[31,"Fantomas","CZ","Tenis","Míček",157.5,"2007-04-13",2007,4,null,"Filtry"]
[32,"Krakonoš","CZ","Hokej","Hokejka",142.80000000000001,"2007-04-27",2007,4]
[33,"Pavel","SK","Hokej","Chrániče",57,"2007-05-19",2007,5]
[34,"Fantomas","CZ","Hokej","Hokejka",136,"2007-05-22",2007,5]
[35,"Rakosniček","CZ","Golf","Golfový míček",57,"2007-06-10",2007,6]
[36,"Krakonoš","CZ","Hokej","Puk",49,"2007-06-13",2007,6]
[37,"Pavel","SK","Golf","Odpalovací hůl",50.4,"2007-07-20",2007,7]
[38,"Rakosniček","CZ","Golf","Odpalovací hůl",46.8,"2007-07-14",2007,7]
[39,"Krakonoš","CZ","Tenis","Raketa",54,"2007-08-17",2007,8,null,"KT - přepočet"]
[40,"Monika","SK","Tenis","Míček",270,"2007-10-15",2007,10]
[41,"Pavel","SK","Golf","Odpalovací hůl",46.8,"2007-11-03",2007,11]
[42,"Krakonoš","CZ","Hokej","Chrániče",111,"2007-11-15",2007,11]
[43,"Pavel","SK","Hokej","Chrániče",42,"2007-12-03",2007,12]
[44,"Monika","SK","Golf","Golfový míček",120,"2007-12-03",2007,12,null,"Řádky sloupe / sbalit / rozbalit"]
[45,"Monika","SK","Hokej","Hokejka",115,"2007-12-03",2007,12]
[46,"Pavel","SK","Hokej","Puk",75,"2007-12-04",2008,12]
[47,"Fantomas","CZ","Hokej","Puk",75,"2007-12-05",2009,12]
[48,"Rakosniček","CZ","Hokej","Puk",75,"2007-12-06",2010,12]
[49,"Krakonoš","CZ","Tenis","Míček",55,"2007-12-07",2011,12]
[50,"Rakosniček","CZ","Tenis","Míček",55,"2007-12-08",2012,12]
[51,"Monika","SK","Tenis","Raketa",78,"2007-12-09",2012,12]
[null,null,null,null,null,null,null,null,null,null,"Design"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,"Další designové úpravy"]

{"sheet":"Podklady - VBA kódy","index":1,"n_rows":215,"n_cols":4,"rows_kept":215,"cut":false,"hidden":false,"merged":[]}
["Kódy pro kontingenční tabulku"]
[]
[]
[null,"Vytvoření KT",null,"Odstranění KT "]
[null,"' ----------------------",null,"' ---------------"]
[null,"' -----------------------",null,"' ---------------"]
[null,"Dim sht As Worksheet"]
[null,"Dim pvtCache As PivotCache",null,"Sheets(\"KT2\").PivotTables(\"Pivot2\").TableRange2.Clear"]
[null,"Dim pvt As PivotTable"]
[null,"Dim StartPvt As String",null,"'  ' Smazat na aktivním listě"]
[null,"Dim SrcData As String",null,"'  ActiveSheet.PivotTables(\"Pivot2\").TableRange2.Clear"]
[]
[null,"' Data z kterých se vytvoří KT"]
[null,"SrcData = ActiveSheet.Name & \"!\" & Range(\"A1:I52\").Address(ReferenceStyle:=xlR1C1)",null,"Odstranění všech KT "]
[null,"    ' ' bude na jiném listě"]
[null,"    ' ' bude pojmenovaná oblast",null,"    Dim WB As Workbook, WS As Worksheet, PT As PivotTable"]
[null,"    "]
[null,"' Nový sešit do kterého příjde KT, nekontroluje, zda existuje",null,"    If MsgBox(\"Smazat všechny kontingenčky v aktivním sešitě?\", _"]
[null,"Set sht = Sheets.Add",null,"        vbYesNo + vbDefaultButton2, \"Vše smazat?\") = vbNo Then Exit Sub"]
[null,"' list s KT si pojmenuji, budu pak odkazovat",null,"    On Error Resume Next"]
[null,"    sht.Name = \"KT2\"",null,"    For Each WS In ActiveWorkbook.Worksheets"]
[null,null,null,"        For Each PT In WS.PivotTables"]
[null,"    ' ' přidát list na konec",null,"            WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp"]
[null,"    ' Set sht = ThisWorkbook.Sheets.Add(After:= ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))",null,"        Next PT"]
[null,"             ",null,"    Next WS"]
[null,"' od které pozice se bude KT tvořit (klasicky od A3)"]
[null,"StartPvt = sht.Name & \"!\" & sht.Range(\"A3\").Address(ReferenceStyle:=xlR1C1)"]
[]
[null,"' Vytvořit kontingenční tabullka (Pivot Table) Cache ze zdrojových dat"]
[null,"Set pvtCache = ActiveWorkbook.PivotCaches.Create( _"]
[null,"SourceType:=xlDatabase, _"]
[null,"SourceData:=SrcData)"]
[]
[null,"' Vytvoření kontingenčky z cache, pod jménem at se lze odkazovat"]
[null,"Set pvt = pvtCache.CreatePivotTable( _"]
[null,"TableDestination:=StartPvt, _"]
[null,"TableName:=\"Pivot2\")"]
[]
[null,"Pole KT"]
[]
[null,"Řádky",null,"Sloupce"]
[null,"' ---- Přidat řádkové pole do KT",null,"' sloupce v KT"]
[null,"Dim pvt As PivotTable",null,"Dim pvt As PivotTable"]
[]
[null,"'Sheets(\"KT2\").Activate",null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"pvt.PivotFields(\"Jmeno\").Orientation = xlRowField",null,"pvt.PivotFields(\"Země\").Orientation = xlColumnField"]
[]
[]
[null,"'    ' Druhá možnost"]
[null,"'        Sheets(\"KT2\").Select"]
[null,"'        With ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Jmeno\")"]
[null,"'            .Orientation = xlRowField"]
[null,"'            .Position = 1"]
[null,"'        End With"]
[null,"    "]
[null,"'    ' pokud jméno s netisknutelnými znaky"]
[null,"'    With ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Jméno \" & Chr(10) & \"prodavače\").Orientation = xlRowField"]
[null,"        "]
[]
[null,"Řádky pozice",null,"Sloupce pozice"]
[null,"' uspořádaní pořadí",null,"' uspořádaní pořadí"]
[null,"Dim pvt As PivotTable",null,"Dim pvt As PivotTable"]
[]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")",null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"pvt.PivotFields(\"DruhSportu\").Orientation = xlRowField",null,"pvt.PivotFields(\"Země\").Orientation = xlColumnField"]
[null,"'přehodit pozici",null,"'přehodit pozici"]
[null,"pvt.PivotFields(\"DruhSportu\").Position = 1",null,"pvt.PivotFields(\"Země\").Position = 1"]
[]
[]
[]
[null,"Filtry",null,"Smaž filtry"]
[null,"' Filtry v KT",null,"Dim pf As PivotField"]
[null,"Dim pvt As PivotTable"]
[null,null,null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Rok\")"]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"pvt.PivotFields(\"Jmeno\").Orientation = xlPageField",null,"'Smaž filtry"]
[null,"pvt.PivotFields(\"Rok\").Orientation = xlPageField",null,"    pf.ClearAllFilters"]
[]
[]
[null,"Filtry který potřebuju",null,"Více filtrů"]
[null,"Dim pf As PivotField",null,"Dim pf As PivotField"]
[]
[null,"'filtr v pivotce (je už umístěn)",null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Rok\")"]
[null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Rok\")",null," pro jistotu vymažu a zapnu multivýběr"]
[null,null,null,"    pf.ClearAllFilters"]
[null,"' pro jistotu vymažu",null,"    pf.EnableMultiplePageItems = True"]
[null,"    pf.ClearAllFilters"]
[null,null,null,"' co nechci vidět"]
[null,"'Filter pro rok 2010",null,"    pf.PivotItems(\"2008\").Visible = False"]
[null,"    pf.CurrentPage = \"2010\"",null,"    pf.PivotItems(\"2010\").Visible = False"]
[null,null,null,"    pf.PivotItems(\"2011\").Visible = False"]
[]
[]
[]
[null,"Hodnoty - součet",null,"Hodnoty"]
[null," '---------------------"]
[null," ' KT hodnoty"]
[null," '---------------------"]
[]
[null,"Dim pvt As PivotTable",null,"'počet"]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")",null,"Dim pvt As PivotTable"]
[null,null,null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"'musím kontrolovat zda (ne)existuje název"]
[null,"pvt.AddDataField pvt.PivotFields(\"Zisk\"), \"Součet z Zisk\", xlSum",null,"pvt.AddDataField pvt.PivotFields(\"Zisk\"), \"Počet z Zisk\", xlCount"]
[]
[]
[]
[]
[null,"Odstranit hodnoty",null,"Odstranit hodnoty"]
[null,null,null,"Dim pvt As PivotTable"]
[null,null,null,"Dim pf As PivotField"]
[null,null,null,"Dim pi As PivotItem"]
[null,"    With Sheets(\"KT2\").PivotTables(\"Pivot2\").DataFields(\"Součet z Zisk\")"]
[null,"        .Parent.PivotItems(.Name).Visible = False",null," Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"    End With"]
[null,null,null,"'Ulož do proměné pole pod názvem"]
[null,null,null,"  For Each pf In pvt.DataFields"]
[null,null,null,"    If pf.SourceName = \"Zisk\" Then Exit For"]
[null,null,null,"  Next"]
[null,null,null,"  "]
[null,null,null,"'Odstraň počitatelné pole"]
[null,null,null," pf.DataRange.Cells(1, 1).PivotItem.Visible = False"]
[]
[]
[null,"Přepočet KT",null,"Přepočet všechny KT"]
[null,"'Refresh A Single Pivot Table",null,"'Refresh všechny pivotky"]
[null,"Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotCache.Refresh"]
[null,null,null,"ActiveWorkbook.RefreshAll"]
[null,"'  'Přepočet aktivní list"]
[null,"'  ActiveSheet.PivotTables(\"Pivot2\").PivotCache.Refresh"]
[]
[]
[null,"Změna zdrojových dat"]
[]
[]
[null,"Design KT"]
[]
[null,"Zbalit pole",null,"Rozbalit pole"]
[null,"Dim pf As PivotField",null,"Dim pf As PivotField"]
[]
[null,"' ' pro aktivní list",null,"' ' pro aktivní list"]
[null,"' Set pf = ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Mesic\")",null,"' Set pf = ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Mesic\")"]
[null,"' ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Rok\").ShowDetail = False",null,"' ActiveSheet.PivotTables(\"Pivot2\").PivotFields(\"Rok\").ShowDetail = False"]
[]
[null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Rok\")",null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Rok\")"]
[]
[null,"'sbalit musím mít rok měsíc",null,"'rozbalit"]
[null,"pf.ShowDetail = False",null,"pf.ShowDetail = True"]
[]
[null,"'Expand Pivot Field"]
[null,"'  pf.ShowDetail = True"]
[]
[]
[null,"Pivotka design",null,"Pivotka řádky sloupce"]
[null,"' ----------------",null,"' -------------------"]
[null,"' pivotka design",null,"' Design pivotka hodnoty"]
[null,"' -----------------",null,"' -----------------------"]
[null,"' Poznámka: Vííce položek v poli hodnoty"]
[null,null,null,"' Set pf = ActiveSheet.PivotTables(\"Pivot1\").PivotFields(\"Rok\")"]
[null,"Dim pvt As PivotTable",null,"Set pf = Sheets(\"KT2\").PivotTables(\"Pivot2\").PivotFields(\"Mesic\")"]
[]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")",null,"'Change Data's Number Format"]
[null,null,null," pf.DataRange.NumberFormat = \"###0\""]
[null,"' Formát čísel"]
[null,"pvt.DataBodyRange.NumberFormat = \"#,##0;(#,##0)\"",null,"'Change Data's Fill Color"]
[null,null,null," pf.DataRange.Interior.Color = RGB(150, 100, 241)"]
[null,"' Barva pozadí"]
[null,"pvt.DataBodyRange.Interior.Color = RGB(200, 200, 200)"]
[]
[null,"' Barva fontu"]
[null,"' ...."]
[null,"' ...."]
[]
[]
[null,"Celkové součty vypnout",null,"Celkové součty Zapnout"]
[null,"' ----------------------",null,"' ----------------------"]
[null,"' Souhrny / celkové součty / Total Vyppnutí",null,"' Souhrny / celkové součty / Total Zapnutí"]
[null,"' ----------------------",null,"' ----------------------"]
[null,"Dim pvt As PivotTable",null,"Dim pvt As PivotTable"]
[null,"' 'Aktivní list",null,"' 'Aktivní list"]
[]
[null,"' Set pvt = ActiveSheet.PivotTables(\"Pivot1\")",null,"' Set pvt = ActiveSheet.PivotTables(\"Pivot1\")"]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")",null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[]
[null,"' Vypnout pro řádky a sloupce",null,"' Vypnout pro řádky a sloupce"]
[null,"    pvt.ColumnGrand = False",null,"    pvt.ColumnGrand = True"]
[null,"    pvt.RowGrand = False",null,"    pvt.RowGrand = True"]
[]
[]
[null,"Zobrazení kompaktně"]
[null,"Dim pvt As PivotTable"]
[]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[]
[null,"'Rozložení sestavy - Kompaktně"]
[null,"    pvt.RowAxisLayout xlCompactRow"]
[]
[]
[null,"Zobrazení osnova"]
[null,"Dim pvt As PivotTable"]
[]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"    "]
[null,"' Rozložení sestavy - Osnova"]
[null,"    pvt.RowAxisLayout xlOutlineRow"]
[]
[]
[null,"Zobrazení tabulka"]
[null,"Dim pvt As PivotTable"]
[]
[null,"Set pvt = Sheets(\"KT2\").PivotTables(\"Pivot2\")"]
[null,"    "]
[null,"'Rozložení sestavy - Zobraz tabulkově"]
[null,"    pvt.RowAxisLayout xlTabularRow"]