Nacházíte se: WALL.czExcel návod › Automatická aktualizace zdroje dat kontingenční tabulky
Kategorie: Excel návod

Automatická aktualizace zdroje dat kontingenční tabulky

ExcelPokud máte tabulku, kterou pravidelně doplňujete novými daty a tato tabulka zároveň slouží jako zdroj dat pro kontingenční tabulku, tak po každém doplnění daty musíte aktualizovat kontingenční tabulku spuštěním průvodce a nastavit novou oblast zdroje dat. V tomto tipu vám ukáži, jak tuto činnost automatizovat.

Přejděte do menu Vložit / Název / Definovat. Zobrazí se okno pro vložení názvu. Zadejte nový název zdroj. Do pole Odkaz vložte tento vzorec =NEPŘÍMÝ.ODKAZ("Data!$A$1:$H$"&POČET2(Data!$A:$A)) a potvrďte OK.

Automatická aktualizace zdroje dat

Tento vzorec vytvoří odkaz na tabulku $A$1:$H$ n  na listu s názvem Data. Výpočet POČET2(Data!$A:$A) doplní místo  n  skutečný počet obsažených buněk. Pokud používáte Excel 2007, tak do výpočtu zadejte číslo 1 048 576 (maximální počet řádků počet řádků na listu).

Nyní musíme v kontingenční tabulce nastavit automatický zdroj dat. Nastavte aktivní buňku do vaší kontingenční tabulky a spusťte průvodce kontingenční tabulkou Data / Kontingenční tabulka a graf .... Do pole Oblast vložte název, který jste vytvořili. V tomto případě je název zdroj.

Automatická aktualizace zdroje dat

Dokončete průvodce stlačením tlačítka Dokončit. Zdroj dat pro kontingenční tabulku se pak bude vždy automaticky přepočítávat po změně ve zdrojové tabulce. Po změně dat je potřeba kontingenční tabulku vždy aktualizovat. Pokud chcete i tuto část automatizovat, tak v editoru maker vložte do kódu listu toto makro:

Private Sub Worksheet_Activate() ActiveSheet.PivotTables("Kontingenční tabulka 2").PivotCache.Refresh End Sub

Vzorec si upravte podle vaší tabulky, to znamená, že místo posledního sloupce H vložte název posledního sloupce z vaší tabulky. Zde si můžete stáhnout soubor s příkladem a celý postup vyzkoušet.

print Formát pro tisk

Sdílet článek:

Komentáře rss

Zasílate odpověď ke stávajícímu příspěvku (zrušit).

Nemáte oprávnění přidávat příspěvky.

Uživatelské jméno
Heslo
     

Registrace >
Ztracené heslo >


, dotaz odpovědět
avatar
Zdravim, chtel bych se zeptat zda jde nastavit aby se kontingencni tabulka aktualizovala pokazde kdyz zadam data do ruznych radku v jednom sloupci, jinak receno "kdyz zapisu data do sloupce G dam enter a kontingencni tabulka se okamzite aktualizuje o zadane data" jde to?citovat
icon odpověděl(a)
admin
Příkaz pro refresh je potřeba vložit do události změny na listu.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, SRange As Range
Set SRange = Range("A:I") ' oblast buněk
For Each cell In Target
If Union(cell, SRange).Address = SRange.Address Then
ActiveSheet.PivotTables("Kontingenční tabulka 1").PivotCache.Refresh
End If
Next
End Sub
citovat
, Automat. aktualizace odpovědět
avatar
Stáhnul jsem si soubor s příkladem automatické aktualizace dat pomocí kódu VBA, který je vytvořen evidentně v Excelu 2003. Soubor s příkladem jsem otevřel v Excelu 2007 a uložil (jako .xlsm). Po opětovném otevření tohoto .xlsm v Excelu 2007 a přidání jednoho řádku mi to po přechodu na záložku s kontingenční tabulkou hlásí chybu "Run Time Error 1004 Odkaz není platný". V čem dělám chybu?citovat
odpověděl(a)
elninoslov
Obmedzenie počtu riadkov robí tie problémy. Nahraďte v Definovanom názve "zdroj" to číslo 65535 za ROWS(Data!$A:$A), a bude to fungovať.
Chcete aj vysvetlenie ? CountBlank počíta s novým počtom riadkov (Excel 2007), ale pôvodný vzorec je navrhnutý tak, že to vráti chybu, lebo je tam natvrdo napísané maximum riadkov. Lenže to maximum platilo v 2003. Teraz je to milión. Takže dostanete cca 65535-1046385=-980850 riadkov. Čo je blbosť, záporné číslo. Stačí takto ?citovat
odpověděl(a)
avatar
Díky za radu. Já jsem přehlédl, že je úprava pro Excel 2007 zmíněna výše v textu.citovat
, Dynamický zdroj dat odpovědět
Alfan
Mám zdroj dat pro kontingenční tabulku na listu "data".
Data jsou ve sloupcích "A" až "AN"
Definoval jsem si oblast data takto?
=NEPŘÍMÝ.ODKAZ("data!$A$1:$AN$"&POČET2(data!$A:$A))
A nefunguje to.
Mám cca 41000 řádků, ale takto definovaná oblast se "nastaví" jen do řádku 4414...
Ve sloupci "A" někdy data jsou někdy nejsou.
Můžete mi někdo prosím poradit.
Děkuji

P.S. Ještě bych se chtěl zeptat na jednu věc. Někdy se v buňce místo výsledku výpočtu vzzorce zobrazuje vzorec. Někde jsem před lety četl, jak se toho zbavit, ale už si to nepamatuji. Vím, že to bylo velmi jednoduché, nějak něco označit nebo něco nahradit něčím nebo co.
Díkycitovat
icon odpověděl(a)
admin
Podmínkou je, že data ve sloupci "A" musí být bez prázdých buněk. Vyzkoušejte když tak jiný sloupec - sloupec, ve kterém jsou data ve všech buňkách.citovat
, - odpovědět
avatar
Dobrý den,
lze toto nějakou úpravou vzorce aplikovat i na sloupce? (pokud přidám do zdroje dat nový sloupec, zahrnul by se automaticky do KT).citovat
, Automatická aktualizac odpovědět
avatar
Automatická aktualizace zdroje dat kontingenční tabulky
Je super, značně mi to ulehčilo práci.
Trochu jsem vylepšila, namísto maximálního počtu řádků dám funkci COUNTBLANK pro sloupec, o kterém vím, že neobsahuje data:
=NEPŘÍMÝ.ODKAZ("Data!$A1:$H"&(countblank(Data!$AA:$AA)-COUNTBLANK(Data!$A:$A)))

Annacitovat
icon odpověděl(a)
admin
K určení oblasti lze ještě použít funkci COUNTA. Celý vzorec by vypadal pak takto:
=NEPŘÍMÝ.ODKAZ("Data!$A1:$H"&COUNTA(Data!$A:$A)))citovat
, Doplnění - Excel 2007 odpovědět
avatar
Děkuji autorovi za článek a dovolím si jej doplnit pro Excel 2007

1. Pojmenovat oblast vstupních dat

na listu s prvotními daty, ze kterých se má tvořit KT (zde se jmenuje DATA) v menu na záložcd VZORCE, tlačítko DEFINOVAT NÁZEV zadat název zdrojové oblasti (zde použito ZDROJ), pro celý SEŠIT
Do pole ODKAZ vložit vzorec
=NEPŘÍMÝ.ODKAZ("Data!$A1:$H"&65537-COUNTBLANK(Data!$A:$A)) (nebo jinou oblast, ve které máme data)
(v menu na kartě DATA je i tlačítko SPRÁVCE NÁZVU pro případnou změnu jména)

2a. vytvoření nové KT
- spustit Průvodce kontingeční tabulkou a grafem, první volba "Seznam ... Excel"
- oblast dat zadat data!zdroj (dle jména, které jsme vytvořili - jméno ZDROJ na listu DATA)

2b. hotovou KT upravit tak, aby brala data z pojmenované oblasti

- kurzor umístit do oblasti KT a vybrat z menu MOŽNOSTI, ZMĚNIT ZDROJ DAT
- zadat nový zdroj dat = pojmenovanou oblast data!zdroj

3. auto-aktualizace KT pomocí VBA

- v editoru maker (alt+F11) rozbalit VBAproject, Microsoft Excel Object, 2x klik na list s vaší KT
- do okna vložit kód VBA
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("Kontingenční tabulka 2").PivotCache.Refresh
End Sub
- číslo, které uvedete za text "Kontingenční tabulka " najdete ve své KT:
klikněte do oblasti KT, menu MOŽNOSTI, v menu nahoře vidíte název své tabulky s hledaným číslem

Podrobněji (s obrázky) ve fóru zde
http://wall.cz/index.php?m=topic&id=829
nebo přímo v souboru zde
http://wall.cz/upload/postatt/kt.zipcitovat
, Název KT odpovědět
Jeza.m
Ahoj,
Jak zjistím název kontingenční tabulky pro použití v makru (v tomto případě "Kontingenční tabulka 2"), aniž bych si pouštěl záznam makra a klikal na jednotlivé KT?

To já jen tak jestli nějaká jiná možnost vůbec existuje.

Díky
M@citovat
icon odpověděl(a)
admin
Tato informace je uvedena v možnostech kontingenční tabulky.citovat
odpověděl(a)
Vasey
jmeno = ActiveCell.PivotTable.Namecitovat
, ad Aktualizace KontTab odpovědět
avatar
Dobrý den,
náhodou jsem se dostal na tento web, když jsem hledal stručnou a rychle použitelnou informaci o kontingenčních tabulkách v Excelu.
Soubor článků k tomuto tématu je naprosto vynikající, poskytuje přesně to, co je pro rychlé zvládnutí práce s těmito objekty potřeba. Mnohokrát děkuji autorovi Petrovi. Mám pouze drobnou věc: odkaz v článku Automatická aktualizace zdroje dat kontingenční tabulky je stejný jako pro článek Kont. tabulka I.
Díky i tak.
Zdraví
Jan Liškařcitovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

odpocet a storno tl.

PavDD • 28.3. 8:53

odpocet a storno tl.

Začátečník • 26.3. 14:39

odpocet a storno tl.

PavDD • 26.3. 10:22

odpocet a storno tl.

elninoslov • 26.3. 7:50

odpocet a storno tl.

PavDD • 26.3. 7:26

odpocet a storno tl.

elninoslov • 25.3. 22:34

odpocet a storno tl.

Začátečník • 25.3. 15:09