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 jsou automaticky uzavřeny 730 dnů po vydání článku.

Komentáře rss

stop Uzamčeno - nelze přidávat nové příspěvky.

icon, -
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
icon, Automatická aktualizac
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 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
icon, Doplnění - Excel 2007
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
icon, Název KT
avatar
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 icon odpověděl(a)
admin
Tato informace je uvedena v možnostech kontingenční tabulky.citovat
icon odpověděl(a)
avatar
jmeno = ActiveCell.PivotTable.Namecitovat
icon, ad Aktualizace KontTab
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

Novinky

Formulář Faktura III

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

Aktivní diskuse

Máte někdo starší sešit? ;)

kp57 • 20.12. 0:22

Čtení z HTML?

kp57 • 19.12. 21:15

Čtení z HTML?

PeSy • 19.12. 14:57

Makro pro odesílání emailu - konečná verze

Marekkoc • 19.12. 12:22

Harmonogram projektu + provázání M.Outlook

kecaduj • 19.12. 9:50

Máte někdo starší sešit? ;)

eLCHa • 19.12. 7:41

Máte někdo starší sešit? ;)

eLCHa • 19.12. 7:35