Tato část kurzu podrobně popisuje, jak vytvořit kontingenční tabulku v Excelu. Tento článek byl napsán pro Excel 2007 (stejně jako novější verze). Pokyny pro starší verze Excelu naleznete v samostatném článku: Jak vytvořit kontingenční tabulku v Excelu 2003?

Jako příklad zvažte následující tabulku, která obsahuje údaje o prodeji společnosti za první čtvrtletí roku 2016:

Nejprve si vytvořte velmi jednoduchou kontingenční tabulku, která bude ukazovat celkový objem prodeje každého z prodejců podle výše uvedené tabulky. Chcete-li to provést, musíte provést následující:

Kontingenční tabulka bude vyplněna celkovými hodnotami prodejů pro každého prodejce, jak je znázorněno na obrázku výše.

Pokud chcete zobrazit objemy prodeje v peněžních jednotkách, musíte přizpůsobit formát buněk, které tyto hodnoty obsahují. Nejjednodušší způsob, jak to udělat, je vybrat buňky, jejichž formát chcete upravit, a vybrat formát Měnový(Měna) v sekci Číslo(Číslo) na kartě Domov(Domů) Pás karet nabídky Excel (jak je uvedeno níže).

V důsledku toho bude kontingenční tabulka vypadat takto:

Upozorňujeme, že výchozí formát měny závisí na nastavení vašeho systému.

Mnoho kancelářských uživatelů se často setkává s řadou problémů, když se snaží vytvářet a upravovat jakékoli kancelářské dokumenty. Často je to způsobeno tím, že společnosti používají několik kancelářských programů, jejichž principy fungování se mohou značně lišit. Obzvláště problematická může být kontingenční tabulka Excel.

Naštěstí se relativně nedávno objevil MS Office 2010-2013, který nejenže obsahuje řadu aktualizovaných programů pro zpracování textových souborů, tabulek, databází a prezentací, ale umožňuje s nimi pracovat více pracovníků současně, což je ve firemním prostředí prostě neocenitelné. .

Proč nové verze?

Abyste této problematice porozuměli, musíte si představit všechny nejvýznamnější změny, které se u tohoto kancelářského balíku udály.

Stejně jako dříve je druhým nejoblíbenějším programem Excel, který umožňuje nejen vytvářet jednoduché tabulky, ale dokonce i poměrně složité databáze. Stejně jako u ostatních komponent k němu přibylo tlačítko „Kancelář“, na které si můžete dokument uložit ve vámi požadovaném formátu, změnit jej nebo zadat požadovaná opatření k jeho ochraně. Modul zabezpečení dokumentů byl výrazně aktualizován.

Pokud jde o nejvýznamnější změny v Excelu, je třeba poznamenat, že bylo opraveno mnoho chyb ve vzorcích, proto se v předchozích verzích často vyskytovaly poměrně závažné chyby ve výpočtech.

Je třeba říci, že novou verzi Balíček není jen sadou kancelářských programů, ale také mocným nástrojem vhodným pro řešení i velmi složitých problémů. Jak jistě chápete, dnes se podíváme na kontingenční tabulku Excelu.

Co to je a k čemu to je?

Nejvýraznější změnou v nových verzích Office je jejich kompletně přepracované rozhraní, které tvůrci nazvali Ribbon. Ve zdrojích je všech 1 500 příkazů pohodlně seskupeno do kategorií, takže je nebudete muset dlouho hledat. Aby vývojáři plně vyhověli těmto parametrům, přidali do Excelu také vylepšené kontingenční tabulky.

Dnes se podíváme na Excel „pro figuríny“. Kontingenční tabulky jsou speciální nástroj, který vizuálně seskupuje výsledky procesu. Jednoduše řečeno, s jejich pomocí můžete vidět, kolik určitého zboží každý prodejce během své pracovní směny prodal. Kromě toho se používají v případech, kdy je nutné:

• Připravte si analytické výpočty pro psaní závěrečných zpráv.
• Vypočítejte každý jejich ukazatel zvlášť.
• Seskupit data podle typu.
• Proveďte filtrování a hloubkovou analýzu přijatých dat.

V tomto článku se podíváme na jednoduchými způsoby jejich vytvoření. Dozvíte se, jak vám kontingenční tabulka v Excelu pomáhá vytvářet a analyzovat časové řady, vytvářet a analyzovat podrobněji předpověď.

Vytvořte požadovaný dokument

Jak tedy vytvořit kontingenční tabulku v Excelu? Nejprve si musíme vytvořit jednoduchý dokument, do kterého zadáme data pro následnou analýzu. V každém sloupci musí být jeden parametr. Vezměme si jednoduchý příklad:

• Prodejní doba.
• Prodaná položka.
• Celkové prodejní náklady.

Tak je vytvořeno spojení mezi všemi parametry v každém konkrétním sloupci: předpokládejme, že tenisky byly prodány v 9 hodin ráno a zisk byl n-rublů.

Formátování tabulky

Po přípravě všech počátečních informací umístěte kurzor do první buňky prvního sloupce, otevřete kartu „Vložit“ a poté klikněte na tlačítko „Kontingenční tabulka“. Okamžitě se objeví, ve kterém můžete provádět následující operace:

• Pokud okamžitě kliknete na tlačítko „OK“, kontingenční tabulka Excelu se okamžitě zobrazí na samostatném listu.
• Plně přizpůsobte výstup.

V druhém případě nám vývojáři dávají možnost určit rozsah buněk, do kterých se budou požadované informace zobrazovat. Poté musí uživatel určit, kde přesně bude nová tabulka vytvořena: na existujícím listu nebo na nově vytvořeném listu. Kliknutím na „OK“ ihned před sebou uvidíte hotový stůl. Tím je vytváření kontingenčních tabulek v Excelu dokončeno.

Na pravé straně listu jsou oblasti, se kterými budete pracovat. Pole lze přetáhnout do samostatných oblastí, poté se data z nich zobrazí v tabulce. V souladu s tím bude samotný otočný stůl umístěn na levé straně pracovní plochy.

Podržením levého tlačítka myši na poli „Produkt“ jej odešlete do „Názvy řádků“ a stejným způsobem přeneste položku „Součet všech prodejů“ do „Hodnoty“ (na levé straně listu) . Takto můžete získat objem prodeje za celé analyzované období.

Vytváření a seskupování časových řad

Chcete-li analyzovat prodeje za konkrétní časová období, musíte do samotné tabulky vložit odpovídající položky. Chcete-li to provést, musíte přejít na list „Data“ a poté vložit tři nové sloupce bezprostředně za datum. Vyberte sloupec pomocí a poté klikněte na tlačítko „Vložit“.

Je velmi důležité, aby všechny nově vytvořené sloupce byly umístěny uvnitř již existující tabulky se zdrojovými daty. V tomto případě nebudete muset kontingenční tabulky znovu vytvářet v Excelu. Jednoduše přidáte nová pole s údaji, které požadujete.

Použité vzorce

Nově vytvořené sloupce mohou být například pojmenovány „Rok“, „Měsíc“, „Měsíce-roky“. Abychom získali data, která nás zajímají, budeme muset do každého z nich zadat samostatný vzorec pro výpočty:

• Do „Roční“ vložíme vzorec ve tvaru: „=ROK“ (vztahuje se na datum).
• Měsíc musí být doplněn výrazem: „=MĚSÍC“ (rovněž s odkazem na datum).
• Do třetího sloupce vložíme vzorec ve tvaru: „= PŘIPOJIT“ (vztahuje se na rok a měsíc).

Podle toho dostaneme tři sloupce se všemi původními daty. Nyní musíte přejít do nabídky „Shrnutí“, kliknout pravým tlačítkem myši na libovolné volné místo v tabulce a poté v okně, které se otevře, vybrat „Aktualizovat“. Poznámka! Pečlivě pište vzorce do kontingenčních tabulek Excelu, protože jakákoli chyba povede k nepřesným prognózám.

Jak utratit podle roku?

Opět klikněte levým tlačítkem myši na položku „Rok“ a poté ji přetáhněte do „Názvy sloupců“, poté se v kontingenční tabulce začnou zobrazovat výsledky prodeje konkrétního typu produktu za všechny roky. Co když také potřebujete analyzovat měsíční tržby? Stejným způsobem podržte LMB „Měsíc“ a přetáhněte jej pod roční sloupec.

Chcete-li získat vizuální přehled o měsíčních změnách tržeb podle roku, je třeba pole „Měsíc“ přetáhnout přímo do sloupce pro rok, který byl dříve přesunut do „Názvy sloupců“. Po provedení této operace uvidíte tabulku v následujícím zobrazení:

• Celkový prodej konkrétního produktu za celý rok.
• Dynamika prodeje každého z nich podle roku.

Odstranění dat z výsledků

Předpokládejme, že potřebujeme z výsledků vyhledávání odstranit data za říjen, protože pro ně nemáme kompletní statistiky. V kontingenční tabulce vyhledejte „Filtr přehledu“ a přetáhněte na něj „Rok - Měsíc“.

Nad tabulkou se zobrazí filtr, ve kterém musíte zaškrtnout políčko vedle položky „Vybrat více prvků“. Poté v zobrazeném seznamu zrušte zaškrtnutí políčka Říjen a klikněte na „OK“.

Můžete tedy přidávat a odebírat prvky z filtru a vytvářet analýzy, které jsou pro vás skutečně relevantní a potřebné. Program automaticky vypočítá ukazatele pro měsíce, které vás zajímají, a jasně zobrazí dynamiku změn ukazatelů.

Vypočítáváme předpovědi

Zkusme předpovídat prodejní výsledky na nějakou dobu dopředu. Chcete-li to provést, musíte nejprve zakázat obecné součty, aby do vytvořené prognózy nevnášely nepřesnosti.

Chcete-li to provést, umístěte kurzor do pole „Grand Total“ a poté klikněte na tlačítko „Delete Grand Total“. Poté spustíme kontingenční tabulku Excel způsobem popsaným výše (klikněte pravým tlačítkem myši a vyberte „Obnovit“).

V důsledku toho tato data zmizí z naší kontingenční tabulky. Chcete-li vytvořit vizuální zobrazení prognóz prodeje, umístěte kurzor na rok, od kterého plánujeme začít, a poté klikněte levým tlačítkem na tlačítko „model předpovědi grafu“.

Poté se objeví krásný a informativní graf, který vizuálně prezentuje předpokládané výsledky nákupní aktivity podle prognózy, která vás zajímá. Ušetříte tak energii a peníze, které lze použít na nákup skutečně potřebného zboží.

K vytvoření kontingenční tabulky používáme jiné zdroje dat

Je čas podívat se na pokročilejší lekce Excelu. Kontingenční tabulku lze vytvořit z dokumentů, které byly upraveny jinými uživateli.

K tomu nám pomůže funkce „Merge“. Tento program umožňuje automaticky zkombinovat text dokumentu a potřebné adresy, jména nebo jiné potřebné informace k vytvoření personalizovaných předpovědí, což je v našem případě vyžadováno.

Funkce hromadné korespondence v novějších verzích Office zůstává prakticky nezměněna oproti Office 2003. Dotazy do databází za účelem výběru zdroje dat však lze provádět pomocí Microsoft Query. Chcete-li získat data kontingenční tabulky Excel, potřebujete dva objekty:

• Hlavní dokument, který obsahuje informace, které potřebujete, a také pole, která Excelu říkají, jaká data má do dokumentu vložit. Hlavní tabulka je standardní tabulkový dokument, ale v dialogovém okně Pomocník hromadné korespondence musíte určit, že má být použita jako hlavní.
• Zdroj dat, který obsahuje informace potřebné k vytvoření prognózy. Novou tabulku můžete vytvořit stejným způsobem, jak jsme uvedli výše.

Když dojde k procesu vytváření souhrnných dokumentů, program vytvoří jednu kopii pro každý ze záznamů, které jsou obsaženy v souboru zdroje dat. Tyto soubory lze zobrazit jako samostatné oddíly nového dokumentu nebo použít jako oddíly v kontingenční tabulce.

Stačí kliknout levým tlačítkem myši na pole, které potřebujete, a místo názvů polí, které jsou zobrazeny v hlavním dokumentu, uvidíte zdrojová data. Pole lze vložit kamkoli do dokumentu.

Použité kategorie

Také zde můžete použít kategorii ASK, což znamená „požadavek“, a FILLIN, což znamená „vyplnit“, NEXT a NEXTIF, ELSE. Můžete také nakonfigurovat parametry těch položek prognózy, které se zobrazí v hotové tabulce. Požadovaný záznam najdete pomocí speciálního filtru nebo pouhým seřazením dat, jak jsme již probrali výše.

Když je vše připraveno k vytvoření sloučeného dokumentu, dialogové okno Sloučit dává uživateli příležitost identifikovat a opravit jednotlivé chyby dříve, než k nim dojde. Když je vše zaškrtnuto, můžete bezpečně stisknout tlačítko „Sloučit“. Pokud musí být fúze provedena prostřednictvím e-mailem, budete požádáni o instalaci poštovního programu (pokud jej nemáte nainstalovaný).

Pokud během procesu slučování dojde k nějaké chybě, znamená to nesprávně zadané pole v dokumentu někoho jiného nebo jsou použitá data prezentována v nepodporovaném formátu. Proto stojí za to tento postup znovu zopakovat.

Obecně Excel (práce s kontingenčními tabulkami, o kterých diskutujeme) umožňuje nejen provádět poměrně složité analýzy kupní síly, ale také na jejich základě vytvářet prognózy.

Pojem „kontingenční tabulka“ nám vlastně nic neříká. Jednoduchá definice by mohla znít takto: Kontingenční tabulka je sestava, která umožňuje zobrazit data pohodlnější a srozumitelnější formou. Nejčastěji se kontingenční tabulky používají k uspořádání zobrazení dat z databáze, ale je možné vytvářet kontingenční tabulky i z dat umístěných v knize. Důležitým požadavkem je strukturálně uspořádaná tabulka (jako registr), kde jsou pole a záznamy (řádky). Například tabulka se seznamem zaměstnanců má tato pole: Příjmení, Jméno, Prostřední jméno, Pozice, Datum narození, Plat atd.

Proces vytváření kontingenční tabulky je jednoduchý a zároveň složitý, ale když se je naučíte sestavovat, stane se vaším hlavním nástrojem pro prezentaci velkého množství dat. Hned upozorňuji, že písemně vysvětlit proces tvorby je poměrně obtížné, proto doporučuji po přečtení zhlédnout videoukázku. V jednom článku také nelze pokrýt všechny možnosti práce s kontingenčními tabulkami, takže článků bude více.

Pro vytvoření první kontingenční tabulky použijeme jako základ knihu vytvořenou v lekci „“, odkaz.

Otevřete knihu na listu „Možnost 1“

OTEVŘENO " Data – kontingenční tabulka…»

Otevře se okno Průvodce kontingenční tabulkou. Dali jsme vše jako na obrázku:

V dalším okně musíte zadat rozsah dat, ze kterých chcete kontingenční tabulku sestavit. Pomocí myši vyberte všechny řádky a sloupce v tabulce „Možnost 1“ (ve výchozím nastavení již vybráno).

V příkladu videa jsem uvedl rozsah "Možnost 1"!$A:$G

To je nutné, pokud je tabulka neustále aktualizována daty a neustálé vytváření tabulky může být velmi obtížné nebo jednoduše líné :) Specifikoval jsem tedy rozsah sloupců, ale rozsah řádků je omezen pouze schopnostmi Excelu (v roce 2003 je 65536 řádků, v letech 2007 - 2010 více než 1 milion řádků). Tato metoda má však drobnou nevýhodu: v tabulkách se objevuje kritérium „prázdné“ (uvidíte později). I když mě to moc netrápí.

V tomto kroku určíme, kde tabulku vytvořit. Odcházíme " nový list" Můžete také okamžitě vytvořit rozvržení (podle mého názoru je pohodlnější to udělat pomocí metody popsané níže, je to vizuálnější) nebo nastavit některé parametry do tabulky. To vše lze ale v budoucnu napravit.

Klikněte na " Připraven».

Uvidíme následující obrázek

Toto je rozložení našeho stolu. Levá strana by měla obsahovat kritéria, jako jsou jména protistran, typy transakcí atd. V horní části jsou také textová kritéria. Rozdíl je v tom, že levá část se projeví jako pás karet s rozdělením dat a horní část nám umožní vybrat kritérium, hlavní (velká) oblast obsahuje data (množství, množství atd.). O něco výše oblast umožňuje rozdělit tato data například podle data nebo měsíce atp.

Tabulka je vytvořena přetažením polí z " Seznam polí kontingenční tabulky» do požadovaných oblastí. Přetáhněte pole do následujících zón:

Typ operace – přetáhněte do levé zóny

Dodavatel – také vlevo, ale mírně vpravo od Typu operace;

Přetáhneme název produktu do horní zóny;

Střídavě přetahujeme Množství a Množství do největší zóny;

Pro rozdělení podle data přetáhněte pole Datum do zóny těsně nad datovou oblastí;

Výsledkem je následující tabulka:

Tabulka se ukázala být příliš přeplněná součty a v polích Součet a Množství se nepočítá součet hodnot, ale jejich počet.

Pojďme to napravit.

Chcete-li změnit možnost výpočtu, musíte přesunout ukazatel myši nad řádek „Množství v poli Množství“ a „Pole Množství v poli Množství“ tak, aby vypadal jako černá šipka:

Jedním kliknutím levým tlačítkem myši by se měly zvýraznit všechny řádky skupiny „Množství podle pole Množství“ jako na obrázku výše.

Nyní klikněte pravým tlačítkem myši a v kontextu vyberte položku „ Možnosti pole»

V okně parametrů výpočtu, které se otevře, vyberte „ Součet»

Udělejte totéž pro řádky skupiny „Množství podle množství“/

Nyní skryjme nepotřebné celkové řádky. Pro tento účel také vybíráme skupiny:

A v kontextové nabídce vyberte položku „ Skrýt»

V důsledku toho byste měli získat tabulku, která vypadá takto:

Všechno. Nyní můžete zobrazit různé informace pomocí výběrových kritérií. Podívejme se například, kdo nám nosí kondenzované mléko, v jakém množství a za jaké množství. Chcete-li to provést, klikněte do pole názvu produktu na obrázku šipky a vyberte ze seznamu:

Získáte tabulku jako:

Změnou množiny a pořadí polí můžeme zobrazit a vypočítat data v jakékoli pro nás vhodné formě, ať už jsou to data podle dne, dodavatele, jména, celkového součtu atd. Chápu, že téma je poměrně složité a je velmi těžké ho podrobně popsat. Podívejte se proto na níže uvedenou videoukázku, abyste to zajistili.

To je prozatím vše. V budoucnu se naučíme sestavovat tabulky pomocí dat z databází a sestavovat grafy založené na kontingenčních tabulkách.

Přiložený soubor: svodnaya_excel.zip

Video: Vytvoření kontingenční tabulky v Excelu

Kontingenční tabulky jsou považovány za jeden z nejvýkonnějších nástrojů Excelu pro práci s daty. Existují proto, aby zjednodušily složitou a těžkopádnou tabulku a učinily výsledky výpočtů jednoduchými, srozumitelnými a přístupnými. Ale! Ne každou tabulku lze použít k vytvoření souhrnné tabulky.

Tabulka musí mít formu běžného seznamu, to znamená, že záhlaví sloupců může být pouze v prvním řádku. Příklad takové tabulky jsem uvedl na obr. P1.1.

Pokud máte v tabulce nějaké přechodné nadpisy nebo mezisoučty, je třeba je odstranit. Abychom nevysvětlovali slovy všechny výhody kontingenční tabulky, ukážu to na příkladu. Klikněte na tlačítko Kontingenční tabulka ve skupině Tabulky nabídky Vložit (obr. A1.2).

Otevře se okno. V něm musíte nejprve vybrat tabulku nebo rozsah tabulky, pro který kontingenční tabulku vytváříte, a určit, kam ji umístit. Můžete jej umístit na nový list, nebo jej můžete umístit vedle původního stolu, na stejný list, podle toho, co je pro vás výhodnější (obr. A1.3).

Na stejný list jsem vložil kontingenční tabulku. V pravé části okna se objevil návrhářský panel pro vytvoření kontingenční tabulky a v horní části je nová skupina záložek - Práce s kontingenčními tabulkami.

Jak se vyrábí kontingenční tabulka?

Na pravé straně panelu s názvem Seznam polí kontingenční tabulky vidíte seznam záhlaví sloupců z tabulky zobrazené na obrázku. P1.1. Z těchto polí nyní můžete, jako z konstruktoru, vytvořit novou tabulku. Chcete-li to provést, musíte název pole přetáhnout myší do požadované oblasti. Rozhodl jsem se, že budu mít názvy měsíců ve sloupcích kontingenční tabulky a příjmení v řádcích a pole Hodnoty vyplním hodnotami ze sloupce Received. To znamená, že souhrnná tabulka bude obsahovat pouze údaje o přijatých penězích. Výsledek je znázorněn na Obr. P1.4.

Kontingenční tabulka se tak nazývá, protože přináší všechny vaše výsledky do jednoduché tabulky, která shrnuje celkové výsledky. V v tomto případě tabulka shrnuje data (ve sloupci Values ​​je v poli Received uvedeno Součet). Mimochodem, otevřete seznam tlačítka Výpočty na záložce Parametry (obr. A1.5).

Výsledky jsem nastavil podle průměrné hodnoty, a jak můžete vidět na Obr. P1.5, nyní kontingenční tabulka nepočítá součet podle měsíce a příjmení, ale průměrnou hodnotu: průměrnou mzdu po měsíci a průměr podle zaměstnance. Kromě toho můžete vytvořit souhrnný graf na základě hodnot v kontingenční tabulce (obr. A1.6).

Tím se zobrazí skupina karet s názvem Práce s kontingenčními grafy, což pro vás není nic nového. Na to všechno jsme se podívali, když jsme se podívali, jak fungují běžné diagramy. Mimochodem, povšimněte si prosím: Prohodil jsem řádky a sloupce, takže součty se nyní počítají na základě hodnoty ve sloupci Zbývající (viz obr. A1.6). Udělal jsem to jen proto, abyste věděli, že hodnoty sloupců, řádků a hodnotových polí lze zamíchat tak, aby vyhovovaly vašim potřebám.

Můžete také vložit řez do kontingenční tabulky. Jedná se o doplňkový filtr, který umožňuje ještě více zpřehlednit výsledek (obr. A1.7).

Ve skupině Seřadit a filtrovat na kartě Možnosti klikněte na tlačítko Vložit průřez a vyberte parametr, podle kterého chcete data filtrovat. Uvedl jsem měsíc. Nyní můžete v okně řezu vybrat konkrétní měsíc a kontingenční tabulka zobrazí pouze údaje týkající se tohoto měsíce (viz obr. A1.6). K dispozici budete mít také celou záložku - Řezné nástroje. Mimochodem, do tabulky můžete vložit nejen jeden plátek, ale několik.

Řekl jsem vám nejjednodušší techniky pro práci s kontingenčními tabulkami. Pokud toto pochopíte, můžete pochopit i vše ostatní. Jen nezapomeňte, že před vytvořením kontingenční tabulky je na to potřeba připravit zdrojovou tabulku, tedy ujistit se, že neobsahuje žádné mezihlavičky a součty. Pokud by vám stále nebylo něco jasné nebo byste si chtěli možnosti kontingenčních tabulek prostudovat podrobněji, pak doporučuji obrátit se na materiály na speciálním webu Kontingenční tabulky Excel 2010, který je celý věnován metodám práce s daty v kontingenčních tabulkách Excel 2010.

Kontingenční tabulky jsou nezbytné pro sumarizaci, analýzu a prezentaci dat umístěných ve „velkých“ zdrojových tabulkách, v různých sekcích. Podívejme se na proces vytváření jednoduchých kontingenčních tabulek.

Kontingenční tabulky (Vložit/Tabulky/Kontingenční tabulka) může být užitečné, pokud jsou současně splněny následující podmínky:

• existuje zdrojová tabulka s mnoha řádky (záznamy), mluvíme o několika desítkách a stovkách řádků;
• je nutné provést analýzu dat, která vyžaduje vzorkování (filtrování) dat, jejich seskupování (sumarizace, počítání) a prezentace dat v různých sekcích (příprava reportů);
• Tuto analýzu je obtížné provést na základě původní tabulky jinými prostředky: ( CTRL+SHIFT+L), ;
• zdrojová tabulka splňuje určité požadavky (viz níže).

Uživatelé se často vyhýbají používání Kontingenční tabulky, protože Jsme si jisti, že jsou příliš složité. Skutečně, zvládnutí jakéhokoli nového nástroje nebo metody vyžaduje úsilí a čas. V důsledku toho by však účinek zvládnutí něčeho nového měl převyšovat vynaložené úsilí. V tomto článku zjistíme, jak vytvořit a používat Kontingenční tabulky.

Příprava zdrojové tabulky

Začněme požadavky na zdrojovou tabulku.

• každý sloupec musí mít nadpis;
• Každý sloupec musí obsahovat hodnoty pouze v jednom formátu (například sloupec Datum dodání musí obsahovat všechny hodnoty pouze ve formátu datum; sloupec „Dodavatel“ - názvy firem pouze v textovém formátu nebo můžete zadat Kód dodavatele v číselném formátu);
• tabulka by neměla obsahovat zcela prázdné řádky a sloupce;
• Do buněk je třeba zadat „atomické“ hodnoty, tzn. pouze ty, které nelze rozdělit do různých sloupců. Do jedné buňky například nemůžete zadat adresu ve formátu: „Město, Ulice, Číslo budovy“. V opačném případě musíte vytvořit 3 sloupce se stejným názvem Kontingenční tabulka bude fungovat neefektivně (pokud potřebujete informace, například podle města);
• Vyhněte se tabulkám se „špatnou“ strukturou (viz obrázek níže).

Místo vytváření opakujících se sloupců ( region 1, region 2, ...), ve které bude množství prázdných buněk, přehodnoťte strukturu tabulky, jak je znázorněno na obrázku výše (Všechny hodnoty prodeje by měly být v jednom sloupci a neměly by být rozloženy do několika sloupců. implementovat to, možná budete muset vést podrobnější záznamy (viz obrázek výše), spíše než ukazovat celkové prodeje pro každý region).

Podrobnější tipy na stavbu stolů přinášíme ve stejnojmenném článku.

Trochu usnadňuje proces výstavby Kontingenční tabulka skutečnost, že pokud původní ( Vložit/Tabulky/Tabulka). Chcete-li to provést, nejprve uveďte zdrojovou tabulku do souladu s výše uvedenými požadavky, poté vyberte libovolnou buňku tabulky a vyvolejte okno nabídky Vložit/Tabulky/Tabulka. Všechna pole v okně se automaticky vyplní, klikněte na OK.

Nyní zaškrtneme v Seznamu polí vedle pole Prodej.

Protože Vzhledem k tomu, že buňky ve sloupci Prodej jsou v číselném formátu, automaticky se zobrazí v části Seznam polí hodnot.

Pomocí několika kliknutí myší (přesně šesti) jsme vytvořili zprávu o prodeji pro každý produkt. Stejného výsledku by bylo možné dosáhnout pomocí vzorců (viz článek).
Potřebujete-li například určit objemy prodeje pro každého dodavatele, zrušte zaškrtnutí políčka Produkt v seznamu polí a zaškrtněte pole Dodavatel.

Procházení dat kontingenční tabulky

Pokud máte dotazy ohledně toho, jaká data ze zdrojové tabulky byla použita k výpočtu určitých hodnot Kontingenční tabulka, pak stačí dvakrát kliknout myší na konkrétní hodnotu v Kontingenční tabulka takže se vytvoří samostatný list s řádky vybranými z původní tabulky. Podívejme se například, jaké záznamy byly použity k sumarizaci prodeje produktu „Pomeranče“. Chcete-li to provést, dvakrát klikněte na hodnotu 646720. Vytvoří se samostatný list pouze s řádky zdrojové tabulky související s Produktem „Pomeranče“.

Aktualizace kontingenční tabulky

Pokud po stvoření Kontingenční tabulka byly do zdrojové tabulky přidány nové záznamy (řádky), pak tato data nebudou automaticky brána v úvahu Kontingenční tabulka. Aktualizovat Kontingenční tabulka vyberte kteroukoli z jeho buněk a vyberte položku nabídky: nabídka Práce s kontingenčními tabulkami/Možnosti/Data/Obnovit. Stejného výsledku lze dosáhnout prostřednictvím kontextové nabídky: vyberte libovolnou buňku Kontingenční tabulka Aktualizace.

Odebrání kontingenční tabulky

Vymazat Kontingenční tabulka možné několika způsoby. První je jednoduše odstranit list z Kontingenční tabulka(pokud na něm nejsou jiná užitečná data, např. původní tabulka). Druhým způsobem je smazat pouze Kontingenční tabulka: vyberte libovolnou buňku Kontingenční tabulka, lis CTRL+ A(vše bude zvýrazněno Kontingenční tabulka), stiskněte klávesu Vymazat.

Změna funkce součtů

Při tvorbě Kontingenční tabulka seskupené hodnoty se standardně sčítají. Při řešení problému zjišťování objemů prodeje pro každý Produkt jsme se nestarali o funkci součtů – všechny prodeje související s jedním Produktem byly sečteny.
Pokud potřebujete například spočítat počet prodaných šarží každého Produktu, musíte změnit funkci součtů. Chcete-li to provést v Kontingenční tabulka Součty podle/množství.

Změna pořadí řazení

Nyní si ten náš trochu upravíme Konsolidovaná zpráva. Nejprve změňme pořadí řazení názvů Produktů: seřaďte je v obráceném pořadí od Z do A. K tomu použijte rozevírací seznam v záhlaví sloupce obsahujícího názvy Produktů, přejděte do nabídky a vybrat Řazení od Z do A.

Nyní předpokládejme, že produkt Baranka je nejdůležitějším produktem, takže je potřeba jej zobrazit na prvním řádku. Chcete-li to provést, vyberte buňku s hodnotou Baranki a umístěte kurzor na okraj buňky (kurzor by měl vypadat jako křížek se šipkami).

Poté klikněte levým tlačítkem a přetáhněte buňku na nejvyšší pozici v seznamu, přímo pod záhlaví sloupce.

Po uvolnění tlačítka myši se hodnota Baranka přesune na první pozici v seznamu.

Změna formátu číselných hodnot

Nyní přidáme oddělovač pro skupiny číslic pro číselné hodnoty (pole Prodej). Chcete-li to provést, vyberte libovolnou hodnotu v poli Prodej, klepněte pravým tlačítkem myši na místní nabídku a vyberte položku nabídky Formát čísel…

V okně, které se zobrazí, vyberte formát čísla a zaškrtněte políčko Oddělovač tisíců skupin.

Přidávání nových polí

Předpokládejme, že je nutné připravit zprávu o prodeji Produktů, ale v členění podle Regionu prodeje. Chcete-li to provést, přidejte pole Oblast prodeje zaškrtnutím příslušného políčka v seznamu polí. Pole Oblast prodeje bude přidáno do oblasti Názvy řádků v Seznamu polí (do pole Produkt). Změnou v oblasti Názvy linek Seznam polí, pořadí polí Produkt a Prodejní region, dostaneme následující výsledek.

Zvýrazněním libovolného názvu produktu a kliknutím na položku nabídky Práce s kontingenčními tabulkami/ Možnosti/ Aktivní pole/ Sbalit všechna pole, lze sbalit Kontingenční tabulka zobrazit pouze prodeje podle regionu.

Přidávání sloupců

Výsledkem přidání pole Oblast prodeje do oblasti řádku bylo Kontingenční tabulka rozšířena na 144 řádků. To není vždy výhodné. Protože prodej se uskutečnil pouze v 6 regionech, pak má smysl pole Region prodeje umístit do oblasti sloupců.

Kontingenční tabulka bude mít následující podobu.

Vyměňte sloupce

Chcete-li změnit pořadí sloupců, musíte uchopit záhlaví sloupce Kontingenční tabulka přetáhněte jej na požadované místo.

Odebírání polí

Jakékoli pole lze z kontingenční tabulky odebrat. Chcete-li to provést, musíte na něj umístit kurzor myši v seznamu polí (ve filtru sestav, názvy sestav, názvy sloupců, hodnoty), stisknout levé tlačítko myši a přetáhnout pole, které chcete odstranit, za okraj seznamu polí.

Dalším způsobem je zrušit zaškrtnutí políčka vedle pole, které chcete odstranit, v horní části seznamu polí. Ale v tomto případě bude pole okamžitě odstraněno ze všech oblastí seznamu polí (pokud bylo použito v několika oblastech).

Přidání filtru

Předpokládejme, že je nutné připravit report o prodeji Skupin produktů, a to ve 2 verzích: jedna pro šarže produktů, které přinášely zisk, druhá pro nerentabilní. Pro tohle:

• Kontingenční tabulka, klikněte na položku nabídky ;
• Zaškrtněte políčka vedle polí Skupina, Prodej a Zisk v Seznamu polí;
• Přesuňte pole Zisk z oblasti Názvy řádků v Seznamu polí do oblasti Filtr sestavy;

Pohled na výsledek Kontingenční tabulka by mělo být takto:

Nyní pomocí Rozbalovací (rozbalovací) seznam v buňce B1 (Pole Zisk) můžete například sestavit report o prodejích skupin produktů, které přinesly zisk.

Po kliknutí na tlačítko OK se zobrazí hodnoty prodeje pouze ziskových šarží.

Vezměte prosím na vědomí, že v seznamu polí Kontingenční tabulka Naproti poli Profit se objevila ikona filtru. Filtr můžete odstranit zrušením zaškrtnutí políčka v seznamu polí.

Filtr můžete vymazat prostřednictvím nabídky Práce s kontingenčními tabulkami/Možnosti/Akce/Vymazat/Vymazat filtry.

Data jsou dostupná také prostřednictvím rozevíracího seznamu v záhlaví řádků a sloupců Kontingenční tabulka.

Více součtů pro jedno pole

• Vymažeme dříve vytvořený přehled: vyberte libovolnou hodnotu Kontingenční tabulka, klikněte na položku nabídky Práce s kontingenčními tabulkami/Možnosti/Akce/Vymazat/Vymazat vše;
• Zaškrtněte políčka vedle polí Produkt a Prodej v horní části Seznamu polí. Pole Prodej bude automaticky umístěno do oblasti Hodnoty;
• Přetáhněte další kopii pole Prodej do stejné oblasti Hodnoty. V Kontingenční tabulka Objeví se 2 sloupce počítající částky prodeje;

• PROTI Kontingenční tabulka vyberte libovolnou hodnotu v poli Prodej, klikněte pravým tlačítkem na kontextovou nabídku a vyberte Součty podle/množství. Problém je vyřešen.

Deaktivace celkových řádků

Součtový řádek lze deaktivovat prostřednictvím nabídky: Práce s kontingenčními tabulkami/ Návrhář/ Rozvržení/ Celkové součty. Nezapomeňte nejprve vybrat libovolnou buňku Kontingenční tabulka.

Seskupování čísel a dat

Předpokládejme, že potřebujete připravit zprávu o načasování prodeje. V důsledku toho potřebujete získat následující informace: kolik šarží Produktu bylo prodáno v období od 1 do 10 dnů, v období 11-20 dnů atd. Pro tohle:

• Vymažeme dříve vytvořený přehled: vyberte libovolnou hodnotu Kontingenční tabulka, klikněte na položku nabídky Práce s kontingenčními tabulkami/Možnosti/Akce/Vymazat/Vymazat vše;
• Zaškrtněte políčko vedle pole Prodej (datum skutečného prodeje Produktu) v horní části Seznamu polí. Pole Prodej bude automaticky umístěno do oblasti Hodnoty;
• vyberte jednu hodnotu pole Prodej v Kontingenční tabulka, vyvolejte kontextovou nabídku pravým tlačítkem myši a vyberte Součty podle/množství.
• Přetáhněte další kopii pole Prodej do oblasti Názvy řádků;

Nyní Kontingenční tabulka ukazuje, kolik šarží Produktu bylo prodáno za 5, 6, 7, ... dní. Celkem je to 66 linek. Seskupme hodnoty v krocích po 10. Chcete-li to provést:

• Vyberte jednu hodnotu Kontingenční tabulka ve sloupci Názvy řádků;
• Z nabídky vyberte Skupina podle oboru;
• Vyplňte okno, které se objeví, jak je znázorněno na obrázku níže;

• Klepněte na tlačítko OK.

Nyní Kontingenční tabulka ukazuje, kolik šarží Produktu bylo prodáno v období od 1 do 10 dnů, v období 11-20 dnů atd.

Chcete-li zrušit seskupení hodnot, vyberte Zrušit seskupení v nabídce Práce s kontingenčními tabulkami/možnostmi/skupinou.

Podobné seskupení lze provést pomocí pole Datum dodání. V tomto případě okno Skupina podle oboru bude vypadat takto:

Nyní Kontingenční tabulka ukazuje, kolik šarží Produktu bylo dodáno každý měsíc.

Podmíněné formátování buněk kontingenční tabulky

Do buněk Kontingenční tabulka pravidla můžete aplikovat stejně jako na buňky v pravidelném rozsahu.
Vyzdvihněme například buňky s 10 nejvyššími objemy prodeje. Pro tohle:

• Vyberte všechny buňky obsahující prodejní hodnoty;
• Vyberte položku nabídky Domů/ Styly/ Podmíněné formátování/ Pravidla pro výběr první a poslední hodnoty/ Top 10 prvků;
• Klepněte na tlačítko OK.