Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » kontingencni-tabulka » Kontingencni-tabulka-propojeni-dvou-Excel
Microsoft Excel logo

Kontingenční tabulka - propojení dvou Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak propojit dvě závislé tabulky pro zpracování dat pomocí (v) kontingenční tabulky, aneb zjednodušení práce v Excel za využití kontingenčních tabulek.

Úvodem do propojení kontingenčních tabulek

Mějme dvě tabulky v jedné máte seznam zaměstnanců (jméno, plat), v druhé seznam oddělení a potřebujete vědět kolik platu se vyplácí v jednotlivých odděleních.

Předpokládám, že tabulka zaměstnanci má i sloupec, podle kterého zjistím oddělení (id) a druhá tabulka oddělení má sloupec (primární) podle kterého oddělení mohu přiřadit (id) - neboli, toto id je i v tabulce zaměstnanci a takto mohu "propojit".

Pro přehlednost je článek rozdělen na kapitoly.

Data

Jako první budete potřebovat data. Dvě tabulky, jedna obsahuje jméno, příjmení plat, a id oddělení - ttabulka zaměstanci. Druhá tabulka jsou oddělení, která obsahuje název oddělení, číslo kanceláře a id oddělení.

Data pro kontingenční tabulku

Soubor data pro kontingenční tabulku soubor ve formátu *.xlsx ke stažení zdarma.


Tabulka jako tabulka

Před spojením dvou tabulek nastavíme tabulky jako tabulka. Pokud nevíte co a jak nastavit doporučuji článek: tabulka jako tabulka

Osobně bych si názvy tabulek (které vygeneruje Excel) ještě přejmenoval, ale v této ukázce nechám tabulky jako tabulky bez přejmenování tedy:

  • Tabulka1 (tabulka osoby)
  • Tabulka2 (oddělení)
Data jako tabulka pro kontingenční tabulku

Nastavení propojení

Při tvorbě kontingenčky musíte natavit propojení.

Označte první tabulku a přes menu vložení kontingenční tabulka. A v dialogovém okně Vytvořit kontingenční tabulku zatrhnout Přidat tahle data do datového modelu. Pozor, než Excel zpracuje může chvílí trvat tak se neděste na pomalejší počítačích (hlavně když je více dat) že Excel chvílí přemýšlí..

Propojení dat pro kontingenční tabulku

Kontingenční tabulku máte připravenou pro propojení. V menu Analýza klik na ikonu Relace (kdo dělá s SQL-kem tak už ví co a jak vyplnit). Pro ostatní v dialogovém okně Spravovat relace nastavte ručně, klikem na ikonu Nová. Funkce rozpoznat automaticky je úplně na ... no nepoužitelná.

Spravovat relace pro kontingenční tabulku

V zobrazeném dialogovém okně: Vytvořit relaci jen nastavíte. Divné je že se nastavuje nejprve tabulka s cizím sloupcem. Ale to nám nevadí. V našem případě tabulka1 je s cizím sloupce, jsou to osoby (zaměstnanci). Nebo-li (pokud zjednoduším). Několik pracovníků může pracovat v jednom oddělení. Do sloupce vložíte sloupec, které obsahuje hodnoty které se budou propojovat, v našem případě sloupec (id)

Následuje tabulka s primárním klíčem. Neboli tabulka2, ta kde sloupec id obsahuje jedinečné hodnoty, neboli dvě oddělení nemohouu mít stejný id. Zbývá sloupec a ten se jmenuje také id.

Propojení dat pro kontingenční tabulku

Klikem na OK se objeví v Spravovat relace daná relace.

Spravovat relace pro kontingenční tabulku

Klik na zavřít a už můžete na tvorbu kontingenční tabulky.

Zpracování dat

Pokud máte nastaveno Aktivní v polích kontingenční tabulky, vidíte jen jednu tabulku. Dokud nekliknete na Vše.

Vše pro kontingenční tabulku

Uvidíte názvy vašich tabulek (můžete rozkliknout ať vidíte názvy sloupců) a nastavíte co chcete vidět (má být vypočteno), mě zajímá oddělení a platy. takže zakliknu.

  • Plat z tabulky 1 do oblasti hodnoty
  • Název do oblasti řádky
Vše pro kontingenční tabulku

Možné problémy

Správně zvolit. Budete-li chtít zobrazit hodnoty podle sloupce s cizím klíčem už kontingenčka nedopočte. Více o tom sepíši v některém z příštích článků, jelikož se už dostaváme do teorie jak funguje SQL atd.


Další možností řešení

Pro řešení lze využít nejprve spojíte tabulky do jedné pomocí SVYHLEDAT a následně vytvoříte kontingenční tabulku nad jednou tabulkou.

Převedete do SQL (MySQL nebo jiné databáze) a zpracujete tam.


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor propojená kontingenční tabulka - řešení soubor ve formátu *.xlsx ke stažení zdarma.


Závěrem

Znáte nějaký další trik s kontingenčními tabulkami?


Článek byl aktualizován: 23.04.2017 19:31

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 1261

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.