Jak vyhledat a změnit zdrojová data kontingenční tabulky aplikace Excel. Pokud chybí zdrojová data, pokuste se je znovu vytvořit. Chcete-li uložit zdrojová data pomocí souboru kontingenční tabulky, změňte možnosti.

POZNÁMKA: U maker, která pomáhají spravovat zdrojová data, přejděte na stránku zdrojových dat kontingenčních tabulek. Pokud chcete, aby makro zrušilo otočení zdrojových dat, přejděte na stránku Unpivot Excel Data Makro.

Vyhledejte a opravte zdrojová data

V tomto videu uvidíte, jak vyhledat kontingenční tabulku. zdroj dat, pak zkontrolujte zdroj dat a ujistěte se, že obsahuje všechny řádky a sloupce, které potřebujete. V případě potřeby upravte zdroj dat tak, aby obsahoval nové řádky nebo sloupce.

POZNÁMKA: Dlouho termínové řešení, jako zdroj dat použijte pojmenovanou tabulku Excel. Pokud se přidají nové řádky, upraví se automaticky.

Písemné pokyny jsou pod videem a můžete si stáhnout ukázkový soubor, který bude následovat s videem.

Vyhledejte zdrojová data

Po vytvoření kontingenční tabulky můžete přidat do zdrojových dat nové záznamy nebo změnit stávající záznamy. velký sešit, může být obtížné najít přesný zdroj pro kontingenční tabulku, pokud existuje několik tabulek nebo seznamů.

POZNÁMKA: Pokud jste si koupili můj doplněk Pivot Power Premium, klikněte na Kontingenční tabulka Informace a poté klikněte na Přejít na Zdrojová data.

Chcete-li vyhledat zdrojová data kontingenční tabulky, postupujte takto:

1. Vyberte libovolnou buňku v kontingenční tabulce.
2. Na pásu karet na kartě Nástroje kontingenční tabulky klikněte na kartu Analýza (v aplikaci Excel 2010 klikněte na kartu Možnosti).
3. Ve skupině Data klikněte na horní část příkazu Změnit zdroj dat.

Otevře se dialogové okno Změnit zdroj dat kontingenční tabulky a zdrojovou tabulku nebo rozsah můžete zobrazit v pole Tabulka / Rozsah. Může to být odkaz na list a rozsah buněk, například

• Objednávky! $ A $ 1: $ H $ 9

nebo název tabulky, například

• Sales_East

Za dialogovým oknem vidíte na listu rozsah zdrojů obklopený pohyblivým okrajem.

Na níže uvedeném snímku obrazovky končí rozsah zdrojových dat na řádku 9 a na řádek 10 byl přidán nový záznam. Tento záznam se v kontingenční tabulce nezobrazí, pokud není upraven zdroj dat.

Upravte rozsah zdrojových dat

Pokud jsou zdrojovými daty kontingenční tabulky statická reference na konkrétní list a rozsah, automaticky se neupraví, když se přidají nová data. Na výše uvedeném snímku obrazovky se rozsah zdroje dat vztahuje k objednávkám! $ A $ 1: $ H $ 9 a lze jej ručně upravit tak, aby zahrnoval řádek 10.

Ruční úprava rozsahu statického zdroje:

Rozsah zdroje můžete v případě potřeby upravit, takže obsahuje veškerá nová data, ale lepším řešením je vytvořit dynamický zdroj. a na tom založte kontingenční tabulku. Pokyny jsou uvedeny níže.

Změňte zdrojová data

Někdy budete možná muset kontingenční tabulku změnit, aby používala jiný zdroj dat. V tomto příkladu byla vytvořena nová tabulka, která obsahuje pouze data pro východní r egion. Kontingenční tabulka se změní tak, aby tento zdroj používala, a poté ji lze odeslat správci v oblasti Východu, aniž by byla odhalena data oblasti Západ.

POZNÁMKA: Po změně na jinou zdrojovou tabulku , možná budete muset některá pole přejmenovat nebo přidat do kontingenční tabulky různá pole.

Chcete-li změnit zdrojová data kontingenční tabulky aplikace Excel, postupujte takto:

1. Vyberte libovolnou buňku v kontingenční tabulce.
2. Na pásu karet na kartě Nástroje kontingenční tabulky klikněte na kartu Analýza (v aplikaci Excel 2010 klikněte na kartu Možnosti).
3. V datech skupiny, klikněte na horní část příkazu Změnit zdroj dat.
4. Otevře se dialogové okno Změnit zdroj dat kontingenční tabulky a v poli Tabulka / rozsah se zobrazí zdrojová tabulka nebo rozsah. V tomto snímku obrazovky níže je aktuálním zdrojem dat tabulka Sales_Data, která obsahuje data z obou oblastí.

   

5. Smazat stávající zdroj dat , a zadejte nový název tabulky nebo vyberte nový zdroj dat e rozsah. Tabulka / rozsah se změní na Sales_East, kde jsou uvedena pouze východní data.

   

6. Klikněte na OK.

Dynamický zdroj pro kontingenční tabulku

Místo použití statického rozsahu jako zdroje dat kontingenční tabulky je lepším řešením vytvořit dynamický rozsah, který se automaticky upraví ve velikosti.

Vytvořit dynamický zdroj – tabulka Excel

Dynamický zdroj – pojmenovaný rozsah

Dynamický zdroj – dynamické pole

Použít dynamický zdroj

Dynamický zdroj – tabulka Excel

Namísto použití statického rozsahu jako zdroje dat kontingenční tabulky je lepším řešením vytvoření dynamického rozsahu založeného na pojmenované tabulce Excel. Zde jsou napsány pokyny nebo se podívejte na video níže.

Poznamenejte si název tabulky, abyste jej mohli použít jako zdroj kontingenční tabulky

Dynamický zdroj – pojmenovaný rozsah

Pokud nemůžete použít tabulku Excel jako zdrojová data, vytvoříte dynamický pojmenovaný rozsah na základě vzorce INDEX nebo OFFSET. Zde jsou psané pokyny nebo se podívejte na video níže.

Video ukazuje, jak vytvořit dynamický rozsah s funkcí OFFSET v aplikaci Excel 2007 a kroky jsou podobné i v pozdějších verzích.

POZNÁMKA: Pamatujte si název rozsahu, abyste jej mohli použít jako zdroj kontingenční tabulky

Dynamický zdroj – dynamické pole

Pokud máte verzi aplikace Excel, která podporuje nové funkce, například SORT nebo UNIQUE, můžete tyto funkce použít k vytvoření dynamických polí. Tato technika používá pouze vzorce – žádná makra .

V sekci Stažení získáte ukázkový soubor Filtrovaná zdrojová data. Ukazuje, jak nastavit pojmenovaný rozsah pouze s viditelnými řádky z pojmenované tabulky aplikace Excel.

Zde je filtr d data na jiném listu, pouze se 2 opakováními a 3 kategoriemi z viditelných řádků.

Poté, na základě těchto filtrovaných dat můžete vytvořit kontingenční tabulku.

Použít dynamický zdroj pro kontingenční tabulku

Po vytvoření dynamického zdroje, který obsahuje kontingenční tabulku data, pro použití těchto zdrojových dat postupujte takto:

1. Vyberte libovolnou buňku v kontingenční tabulce.
2. Na pásu karet na kartě Nástroje kontingenční tabulky klikněte na Analyzovat karta (v aplikaci Excel 2010 klikněte na kartu Možnosti).
3. Ve skupině Data klikněte na horní část příkazu Změnit zdroj dat.
4. V dialogovém okně Změnit zdroj dat kontingenční tabulky zrušte zaškrtnutí políčka Tabulka / rozsah.
5. Do pole Tabulka / rozsah zadejte název tabulky nebo pojmenovaného rozsahu, který jste vytvořili. V tomto příkladu je dynamickým zdrojem pojmenovaná tabulka – tblOrders.
6. Klikněte na OK

Uložit zdrojová data pomocí kontingenční tabulky

Když vytvoříte kontingenční tabulku v sešitu, záznamy zdrojových dat se uloží do speciální oblasti paměti – kontingenční mezipaměti. Poté, když zavřete soubor, může Excel uložit zdrojová data do této kontingenční mezipaměti nebo vymazat tuto paměť.

Obě možnosti mají své výhody i nevýhody:

Pokud uložíte zdrojová data:

• Soubor bude větší
• Soubor se může otevřít rychleji

Pokud zdrojová data neuložíte:

• Výplň bude menší
• Soubor se může otevřít pomalu, zatímco je znovu sestavena mezipaměť mezipaměti
• Po otevření souboru musí být obnovena kontingenční tabulka

Jak změnit nastavení

Chcete-li zapnout ukládání nastavení zdrojových dat zapnuto nebo vypnuto:

• Klikněte pravým tlačítkem na buňku v kontingenční tabulce a klikněte na Možnosti kontingenční tabulky.
• Na kartě Data v části Údaje kontingenční tabulky , přidejte nebo odeberte zaškrtnutí z Uložit zdrojová data pomocí souboru
• Klikněte na OK.

Obnovit data při otevření

Pokud se rozhodnete obrátit vypnout možnost Uložit zdrojová data pomocí souboru, měli byste zapnout možnost Obnovit data při otevření souboru. Toto nastavení je těsně pod zaškrtávacím políčkem Uložit zdrojová data.

Jinak se vám zobrazí zpráva, když se pokusíte filtrovat data nebo provést jiné změny rozložení.

• „Kontingenční tabulka byla uložena bez podkladových dat. K aktualizaci sestavy použijte příkaz Obnovit data. “

Pokud se vám zobrazí tato zpráva, klikněte na OK a poté ručně obnovte kontingenční tabulku.

A vyhněte se nepříjemným zapněte možnost Obnovit data při otevření souboru.

Znovu vytvořit zdrojová data kontingenční tabulky

Pokud omylem odstraníte list, který obsahuje zdrojová data pro vaši kontingenční tabulku, nebo pokud obdrželi jste soubor bez dat kontingenční tabulky, můžete jej pomocí funkce Zobrazit podrobnosti kontingenční tabulky znovu vytvořit.

POZNÁMKA: Toto nebude fungovat u všech kontingenčních tabulek, ale je stojí za vyzkoušení, obnovit zdrojová data.

Chcete-li se pokusit znovu vytvořit zdrojová data pro kontingenční tabulku, použijte funkci Zobrazit podrobnosti:

1. Ujistěte se, že že žádná z položek v polích kontingenční tabulky není skrytá – vymažte všechny filtry a průřezy, které byly použity.
2. Kontingenční tabulka nemusí obsahovat všechna pole – stačí se ujistit, že tam je alespoň jedno pole ve Val oblast.
3. Zobrazit součty řádků a sloupců. Pokud celkové součty nejsou viditelné, vyberte buňku v kontingenční tabulce a na pásu karet v části Nástroje kontingenční tabulky klikněte na kartu Analýza. Ve skupině Rozvržení klikněte na Celkové součty a poté na Řádky a Sloupce na Zapnuto.
4. Poklepejte na buňku celkového součtu v pravé dolní části kontingenční tabulky. Tím by se měl vytvořit nový list se souvisejícími záznamy z původních zdrojových dat.

Opravit extrahované Data

Pokud obnovujete zdrojová data, možná budete muset provést nějaké další změny, abyste je obnovili do původního stavu.

• Přejmenujte tabulku pomocí názvu původních zdrojových dat, pokud je znám.
• Pokud původní zdrojová data obsahovala vzorce, budete je muset znovu vytvořit, protože funkce Zobrazit podrobnosti exportuje pouze data.
• Sloupce v extrahovaných datech budou ve stejném pořadí jako v původních zdrojových datech.
• Extrahovaná data budou formátován ve výchozím formátu tabulky sešitu. Můžete použít jiný formát tabulky nebo použít vlastní formátování.
• Poznámka: Pokud jste provedli změny ve zdrojových datech a neaktualizovali kontingenční tabulku, tyto změny nebudou v extrahovaných datech.

Připojení k extrahovaným datům

Pokud přejmenujete tabulku, která byla vytvořena během procesu Zobrazit podrobnosti, a použijete stejný název jako tabulka, která původně obsahovala zdroj data, kontingenční tabulka se může automaticky připojit k novým zdrojovým datům.

Pokud ne, můžete se připojit k znovu vytvořeným zdrojovým datům – postupujte podle pokynů v části výše: Použít dynamický zdroj pro kontingenční tabulku – použijte název tabulky, který jste dali nové tabulce.

Stáhnout ukázkový soubor

Příklad zdroje otáčení: Chcete-li postupovat podle výukových programů, můžete si stáhnout soubor DateAmt.zip. Soubor ZIP je ve formátu xlsx a neobsahuje makra.

Příklad filtrovaných zdrojových dat: Tento příklad je určen pro verze aplikace Excel, které mají nové funkce, například SORT a UNIQUE. Dow Načtěte tento ukázkový soubor a uvidíte, že kontingenční tabulka je vytvořena pouze z viditelných řádků v tabulce Excel ve filtru. Soubor se zipem je ve formátu xlsx a neobsahuje makra.

Nástroje kontingenční tabulky

Chcete-li ušetřit čas při vytváření, formátování a úpravách kontingenčních tabulek, použijte nástroje v mém Pivot Doplněk Power Premium. Pouhými několika kliknutími můžete:

• zkopírovat formátování z jedné kontingenční tabulky a použít ji na jinou kontingenční tabulku.
• změnit všechny hodnoty z Count na Sum
• odebrat „Sum of“ ze všech nadpisů

a ještě mnohem více!

Nenechte si ujít naše Excel tipy