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!$A$1:$H$"&POČET2(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 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.
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.
Formát pro tisk
Facebook
Google +
Linkuj
QR kód
E-mailem
PDF
\n
RSS nejnovější články
Komentáře
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
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
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
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
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
=NEPŘÍMÝ.ODKAZ("Data!$A1:$H"&COUNTA(Data!$A:$A)))citovat
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
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
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