WALL.CZ
Automatická aktualizace zdroje dat kontingenční tabulky
Automatická aktualizace zdroje dat kontingenční tabulky
Pokud 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!$A1:$H"&65536-COUNTBLANK(Data!$A:$A)) a potvrďte OK.
Tento vzorec vytvoří odkaz na tabulku $A$1:$H$ n na listu s názvem Data. Výpočet 65536-COUNTBLANK(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.
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:
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.
Autor:  Petr |
Vydáno: 20.6.2009 |
Přečteno: 3519x |
 Tisk článku |
 Pošli článek
|
Související články:
Kontingenční tabulka I.Kontingenční tabulka II.
Kontingenční tabulka III.
Kontingenční tabulka IV.
Kontingenční tabulka - externí data MySQL
Uživatelské menu
WALL na Facebooku
RSS nejnovější články
Menu
On-line nástroje
Novinky
Příspěvky čtenářů
Komentáře
Doplnění - Excel 2007
(13.1.2010 10:27) odpovědět
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.zip
Název KT
(29.10.2009 20:31) odpovědět
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@
Petr - Hlavni administrátor
(2.11.2009 20:38)
Tato informace je uvedena v možnostech kontingenční tabulky.
ad Aktualizace KontTab
(8.10.2009 11:12) odpovědět
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ř