Kontingenční tabulka aplikace Excel z více listů
Kontingenční tabulky z více listů představují koncept, kdy by k jedné tabulce měly být přidány dvě nebo více tabulek a pole lze vybrat podle požadavku z jednoho místa.
Jedním slovem, různými tabulkami, lze data připojit z různých listů pomocí některých technik a zkratek.
Jak vytvořit kontingenční tabulku z více listů?
Implementace kontingenčních tabulek z více listů má některé další procesy. Tento proces lze provést také různými způsoby. Níže jsou uvedeny různé metody pro vytvoření kontingenční tabulky z více listů v aplikaci Excel.
Proces č. 1
Pro tento proces potřebujeme data na více pracovních listech. Např. List1, list2, list3 atd.
Ne. listů by mělo být dva nebo více než 2.
- Krok 1: Klikněte na Alt + D a poté na P. Zobrazí se následující dialogové okno.
- Krok 2: V tomto dialogovém okně vyberte Více rozsahů konsolidace a klikněte na DALŠÍ.
- Krok 3: V tomto kroku přejdete po kliknutí na DALŠÍ na krok 2 a v tomto dialogovém okně vyberte „Vytvořím pole stránky“.
- Krok 4: Po výběru výše uvedených možností klikněte znovu na DALŠÍ. Okno se také přesune do třetího kroku. V tomto kroku se otevře další krok po kliknutí na tlačítko DALŠÍ, jak je znázorněno níže,
- Krok 5: V tom vyberte rozsah tabulky 1 a poté klikněte na tlačítko PŘIDAT a poté vyberte rozsah jiné tabulky v jiném listu a poté klikněte na PŘIDAT. Nyní budou dvě tabulky přidány do jedné tabulky.
- Krok 6: Nyní vyberte pole Stránka jako 1 pod možností Rozsahy. Poté pojmenujte dva listy podle lepšího porozumění, takže pokud klikneme na tabulku 1, název tabulky se pro lepší pochopení zobrazí v kontingenční tabulce.
- Krok 7: Nyní existuje možnost vybrat, zda bude kontingenční tabulka vložena do nového listu nebo existujícího listu. Ve výchozím nastavení bude vybrán nový list, jak je znázorněno na obrázku.
- Krok 8: Nakonec klikněte na Dokončit. Nyní bude kontingenční tabulka vytvořena v novém listu.
- Krok 9: Ve výchozím nastavení se hodnota zobrazí pro Count. Měli bychom změnit hodnotu v nadpisech součtu. Za tímto účelem přejděte do libovolné buňky, která obsahuje hodnotu a klikněte pravým tlačítkem myši a poté vyberte možnost „Shrnout hodnoty podle“ a v
- Krok 10: Poté můžeme odstranit sloupce, které skutečně potřebujeme, a nepotřebujeme je. Toho lze dosáhnout výběrem možnosti „Sloupcové štítky“. Můžeme zkontrolovat štítky sloupců, podle toho, co je nutné.
- Krok 11: Nyní je tu další sloupec pro Grand Totals. Obecně nepotřebujeme velké součty sloupců. Lze jej tedy odstranit kliknutím pravým tlačítkem na hodnotu a výběrem možnosti Možnosti kontingenční tabulky a zobrazí se dialogové okno, jak je znázorněno níže.
- Krok 12: Přejděte na kartu „Součty a filtry“ a zrušte zaškrtnutí možnosti „Zobrazit celkové součty pro řádky“. Pokud je to nutné, můžete si jej ponechat nebo zrušit zaškrtnutí možnosti. Nyní klikněte na, OK.
- Krok 13: Nyní budou pro sloupy k dispozici pouze celkové součty pro sloupce.
- Krok 14: Posledním krokem by byla změna názvu kontingenční tabulky; může být uživatelsky definovaný nebo související s daty v kontingenční tabulce.
Toto je první proces k vytvoření kontingenční tabulky z více listů v aplikaci Excel. V této kontingenční tabulce, stejně jako v běžné kontingenční tabulce, lze podle požadavku přetahovat pouze pole z pravé strany.
Proces č. 2
V této metodě by měl být společný řádek v obou tabulkách. To bude fungovat jako primární klíč pro první tabulku a cizí klíč pro druhou tabulku.
Zde použijeme list 4, list 5, abychom vytvořili kontingenční tabulku z více listů v aplikaci Excel.
Druhou metodu budeme analyzovat pomocí následujícího příkladu.
- Krok 1: V listech 3 a 4 vyberte tabulku, kliknutím na CTRL + T vyberte celá data a nakreslete tabulku pro úplná data. Nyní bude do dat vložena tabulka. Název se zobrazí pro tabulku v levém rohu. Tento proces lze provést pro všechny tabulky obsahující data.
Filtry budou přidány ve výchozím nastavení; pokud je nepotřebujeme, můžeme je vypnout kliknutím na klávesovou zkratku CTRL + SHIFT + L nebo jednoduše přejděte na kartu „Data“ a klikněte na možnost Filtry. Poté budou filtry deaktivovány.
Chcete-li nyní vytvořit kontingenční tabulku pro následující data, postupujte podle následujících kroků.
- Krok 1: Klikněte na kartu Vložit a klikněte na kontingenční tabulky. Nyní se zobrazí dialogové okno a v něm budete dotázáni, zda by měla být kontingenční tabulka vytvořena na novém listu nebo na stejném listu.
Je dobré použít v Excelu novou možnost archu.
- Krok 2: Nakonec zaškrtněte políčko „Přidat tuto tabulku do datového modelu“. Toto je důležitý krok v tomto procesu vytváření kontingenční tabulky.
- Krok 3: Kontingenční tabulka se nyní vytvoří v novém listu a na pravé straně máme všechna pole související s kontingenční tabulkou.
- Krok 4: Přejděte na kartu „Analyzovat “ -> Vztahy -> Nové.
- Krok 5: Tabulka je aktuální tabulka. Související tabulka je tabulka, která má být přidána do kontingenční tabulky. Související sloupec je sloupec, který je stejný v obou tabulkách; toto je z první tabulky; nazývá se také primární klíč. Sloupec je stejný sloupec ve druhém sloupci, který se nazývá cizí klíč.
- Krok 6: Nyní klikněte na ok.
- Krok 7: Nyní můžeme vybrat požadovaná pole podle požadavku. Pole lze vybrat ze všech tabulek v kontingenčních tabulkách. Za tímto účelem nejprve požádá o vytvoření nové kontingenční tabulky.
- Krok 8: Klikněte na Ano a můžete vybrat pole ze všech tabulek a vytvořit kontingenční tabulku.
Proces č. 3
První a nejdůležitější věcí v této metodě je přiřadit název všem datům. Ilustrujeme to na příkladu. Níže je uveden příklad tohoto procesu.
- Krok 1: Vytvořte tabulku pro všechna data pomocí CTRL + T a odeberte filtry. Nyní přejděte na kartu „Data“ -> klikněte na „Z jiných zdrojů“ -> Z Microsoft Query -> Soubory Excel.
- Krok 2: Poté vyberte list, ve kterém jste vytvořili data.
- Krok 3: V tomto kroku se zobrazí další okno pro výběr tabulek všech listů v sešitu. Tabulky lze vybrat podle požadavku, jak je uvedeno níže. Vybrali jsme list č. 5 a 6.
- Krok 4: Po výběru tabulek z listů klikněte na Další; dostanete toto dialogové okno a kliknete na OK.
Toto dialogové okno říká, že bez připojení k tabulkám nelze provést dotaz a nyní se připojit k tabulkám.
- Krok 5: Poté se zobrazí okno a v něm budou přítomna všechna data patřící do všech tabulek. V tomto okně budou dvě části. Jeden je „Rozsah jmen“ a druhý je „Datová oblast“.
- Krok 6: Nyní přejděte do nabídky Soubor a vyberte možnost „Vrátit data do aplikace Microsoft Excel“.
- Krok 7: Nyní se zobrazí okno „Importovat data. "
- Krok 8: V tomto okně, protože chceme vytvořit kontingenční tabulku, vyberte možnost „Zpráva kontingenční tabulky“ a klikněte na OK.
- Krok 9: Nyní je kontingenční tabulka připravena a můžeme ji vytvořit podle požadavku.
Poznámka: Důvodem pro vložení tabulky do dat je, že v případě, že v budoucnu budou nějaká data připojena na poslední, lze kontingenční tabulku automaticky obnovit novými daty pouhým obnovením stránky.
Věci k zapamatování
- V jednom sešitu by měly být dvě nebo více tabulek k vytvoření kontingenční tabulky.
- Pokud jsou do kterékoli z tabulek přidána nějaká nová data po vytvoření kontingenční tabulky, pak, abychom odráželi změny, musíme kontingenční tabulku aktualizovat ručně.
- Pokaždé, když vytvoříme kontingenční tabulku, měla by být vytvořena v novém listu, aby správně odrážel změny.
- Když do aplikace Excel přetáhneme atributy z kterékoli z tabulek do kteréhokoli ze 4 polí, vygeneruje se souhrn sestavy během několika sekund.
- Hlavní věcí při vytváření kontingenční tabulky je, že žádná buňka, sloupec nebo řádek by neměly zůstat prázdné, pokud také neexistuje žádná hodnota; mělo by to být „0“ nebo jakákoli jiná hodnota. Jinak se kontingenční tabulka nezobrazí správně.
- Tip pro rychlý nástroj: Rychlý odkaz kontingenční tabulky lze přidat na rychlý panel nástrojů pomocí následujícího procesu.
Klikněte na Přizpůsobit lištu nástrojů Rychlý přístup, přejděte na Další příkazy -> Oblíbené příkazy -> Vyberte kontingenční tabulky -> klikněte na PŘIDAT.
Nyní bude na panel nástrojů pro rychlý přístup přidána možnost Kontingenční tabulka pro snadný přístup.
