Jak seskupit data v kontingenční tabulce aplikace Excel. Příklady seskupení dat, čísel a textových polí. Jak řešit problémy se seskupením a vyhnout se problémům.
Seskupování dat
V kontingenční tabulce můžete seskupit data, čísla a textová pole. Můžete například seskupit data objednávky podle roku a měsíce nebo seskupit výsledky testů v pásmech po 10. Můžete ručně vybrat textové položky v poli kontingenční tabulky a seskupit vybrané položky. To vám umožní rychle zobrazit mezisoučty pro konkrétní sadu položek v kontingenční tabulce.
Toto video ukazuje, jak seskupit data podle měsíce a roku a jak ručně seskupit textové položky. Pod videem jsou napsané kroky.
Automatické seskupování dat
V aplikaci Excel 2016 a novějších verzích jsou data automaticky seskupena, když do kontingenční tabulky přidáte pole s datem. Seskupení v aplikaci Excel 2016 můžete vrátit zpět a tuto funkci vypnout v novějších verzích.
Vypnout automatické seskupování dat
V aplikaci Excel 2019 a Excel 365 můžete změnit možnost aplikace Excel nastavení, zapnutí nebo vypnutí této funkce seskupování dat
POZNÁMKA: Toto je nastavení na úrovni aplikace a ovlivní všechny vaše sešity aplikace Excel.
Zapnutí funkce automatického seskupení data vypnuto:
- Na pásu karet klikněte na kartu Soubor a poté klikněte na Možnosti
- Vlevo klikněte na kategorii Data
- Na konci v části Možnosti dat zaškrtněte políčko „Zakázat automatické seskupování sloupců Datum / Čas v kontingenčních tabulkách“
- Nové nastavení použijete kliknutím na OK.
Vrátit zpět seskupení dat
U všech verzí (Excel 2016 a novější) můžete data ručně oddělit pomocí jedné z těchto metod :
Klávesová zkratka
- Přidat pole Datum do kontingenční tabulky
- Chcete-li akci vrátit zpět, okamžitě stiskněte Ctrl + Z automatické seskupení data
- Seskupená pole jsou odstraněna a původní pole Datum zůstane v kontingenční tabulce
Oddělit příkaz
- Přidejte pole Datum do kontingenční tabulky
- Kdykoli klikněte pravým tlačítkem na libovolnou položku nebo nadpis pole data
- V nabídce klikněte pravým tlačítkem myši na Oddělit
- Seskupená pole jsou odstraněna a původní pole Datum zůstane v kontingenční tabulce
Data seskupení
V kontingenční tabulce je několik předdefinovaných v možnostech seskupení dat – sekundy, minuty, hodiny, dny, měsíce, čtvrtletí a roky.
Můžete vybrat jednu nebo více z těchto možností a vytvořit požadovaný typ seskupení data. Níže uvedené příklady ukazují, jak seskupit data:
– podle měsíce a roku
– podle týdne
– podle období 4 týdnů
Seskupit data podle měsíce a roku
Chcete-li seskupit položky do pole Datum
- Klepněte pravým tlačítkem na jedno z dat v kontingenční tabulce.
- V rozbalovací nabídce klikněte na skupinu
- V dialogovém okně Seskupení vyberte jednu nebo více možností z seznam „Podle“.
- Chcete-li omezit seskupená data, můžete nastavit počáteční a koncové datum zadáním dat do polí „Od“ a „Konec“
- Kliknutím na OK zavřete dialogové okno.
Data skupiny podle týdne
Seskupení položek v poli Datum podle týdne
- Klepněte pravým tlačítkem na jedno z dat v kontingenční tabulce.
- V rozbalovací nabídce klikněte na Seskupit
- V dialogovém okně Seskupení vyberte ze seznamu „Podle“ dny.
- U položky „Počet dnů“ vyberte 7
- Určuje se rozsah týdnů. do data v poli „Začínáme na“, pokud je to nutné, upravte to. V níže uvedeném příkladu byl jako počáteční datum zadán 1. leden 2012.
- Klikněte na OK
Seskupit data podle období 4 týdnů
Podívejte se na toto video a podívejte se, jak seskupit pole s datem do období 4 týdnů, a nastavit počáteční datum. Pod videem jsou uvedeny písemné pokyny.
Data skupiny podle období 4 týdnů
V tomto příkladu má out společnost 13 prodejních období každý rok, přičemž každé období má 4 týdny. V dialogovém okně Seskupování kontingenčních tabulek neexistuje žádná možnost seskupení podle týdnů. Místo toho můžeme použít možnost Dny.
S trochou jednoduché aritmetiky můžeme vypočítat počet dní za období:
- 4 týdny za období
- 7 dní za týden
- 7 x 4 = 28 dní za období
Chcete-li data seskupit do období 4 týdnů, postupujte takto:
- Klikněte pravým tlačítkem u data a klikněte na Seskupit
- V okně Seskupení klikněte na Dny a vyberte jej.
- Pokud jsou vybrány jiné typy seskupení, klikněte na ně a zrušte jejich výběr
- Nastavte počet dní na 28
- POZNÁMKA: Chcete-li seskupit data do období 1 týdne, použijte 7 jako počet dní.
Použití výchozího počátečního data
Jako počáteční datum Excel automaticky vybere první datum ve zdrojových datech. Na výše uvedené obrazovce vidíte, že 1. leden 2013 je počátečním datem v těchto zdrojových datech.
Pokud se přihlásím v Outlooku, toto datum připadne na úterý.
Pokud v okně Seskupení kliknu na OK, Excel vytvoří období, která začínají v úterý. Ve seskupených datech uvedených níže přechází první období od úterý 1. ledna 2013 do pondělí 28. ledna 2013.
Opravte počáteční datum
Namísto použití výchozího počátečního data můžete zadat jiné datum, abyste získali požadované počáteční datum.
V tomto příkladu Změním počáteční datum na 31. prosince 2012. Toto datum připadá na pondělí, takže to nutí všechna seskupení začít v pondělí, které odpovídá prodejním obdobím společnosti.
Oprava počátečního data:
- Klikněte pravým tlačítkem na jednu ze seskupených buněk data v kontingenční tabulce
- Klikněte na skupinu
- V okně Seskupení změňte počáteční datum – 31. prosince 2012
- POZNÁMKA: Zaškrtávací políčko Automaticky pro „Začíná na“ je automaticky zrušeno – ponechejte nezaškrtnuté.
- Klikněte na OK
Tečky jsou nyní seskupeny správně, přičemž jako počáteční datum pro každé období je uvedeno pondělí.
Seskupení podle data přidává další položky
V tomto krátkém videu se dozvíte, jak skrýt text u dalších položek, které se vytvoří, když seskupíte pole s datem nebo použijete časová osa v aplikaci Excel 2013 k filtrování podle období data. Pod videem jsou uvedeny písemné pokyny.
Stáhněte si ukázkový soubor pro další položky ve seskupení podle data. Soubor se zipem je ve formátu xlsx a neobsahuje makra.
Seskupení podle data přidává další položky
Tyto další položky nelze odstranit, můžete však změnit titulky a nahradit text mezerami. Nebo můžete přidat nová pole do zdrojových dat, vypočítat rok a rok / měsíc pro každé datum a místo seskupení použít nová pole v kontingenční tabulce.
V aplikaci Excel 2013 můžete pomocí filtru Časová osa můžete rychle filtrovat podle roku, čtvrtletí, měsíce nebo dne.
Stáhněte si ukázkový soubor pro extra položky v seskupení data. Soubor se zipem je ve formátu xlsx a neobsahuje makra.
Seskupení čísel
Seskupení použijte k vytvoření stručného shrnutí namísto zobrazení dlouhého seznamu jednotlivých čísel. Toto video ukazuje kroky a písemné pokyny jsou pod videem.
Seskupování čísel
Místo vypisování čísel jednotlivě do pole Řádek je můžete seskupit a vytvořit stručné shrnutí údajů.
V tomto příkladu původní kontingenční tabulka zobrazuje prodané jednotky na produkt. Ve sloupci C se zobrazuje počet objednávek, kde bylo dané množství prodáno.
Chcete-li shrnout objednávky, seskupte jednotky po desítkách. Takže místo toho, aby se 3 a 9 zobrazovaly v samostatných řádcích, budou shrnuty do seskupení 1-10.
Seskupení čísel
- Klepněte pravým tlačítkem na jeden čísel jednotek v kontingenční tabulce.
- V rozbalovací nabídce klikněte na Skupinu
- V dialogovém okně Seskupení zadejte 1 do pole Začínáme na
- V tomto příkladu je nejvyšší počet jednotek 50 a v případě potřeby můžete zadat vyšší počet.
- Kliknutím na OK použijete seskupení
V kontingenční tabulce se nyní jednotky zobrazují ve skupinách – 1 – 10, 11 – 20 atd.
Chcete-li vytvořit výstižnější rozložení, můžete přesunout pole seskupených jednotek do oblasti Sloupce jako nadpisy.
Seskupení textových položek
Dialogové okno Seskupení není k dispozici, pokud se pokusíte seskupit položky do textového pole. Místo toho můžete položky ručně seskupit.
Podívejte se na toto video a podívejte se na postup vytváření skupin z textových položek. Poté přejmenujte skupinu nebo přidejte další položky. Později můžete jednu nebo více skupin oddělit. Pod videem jsou napsané pokyny.
Seskupení textových položek
Dialogové okno Seskupení není k dispozici, pokud se pokusíte seskupit položky do textového pole. Místo toho je můžete ručně seskupit položky.
- Vyberte položky, které chcete seskupit
- Klikněte pravým tlačítkem na jednu z vybraných položek a klikněte na Seskupit
Tím se vytvoří nové pole v seznamu polí s původním názvem pole a číslem, například City2
Nové pole je umístěno nad původním polem v rozložení kontingenční tabulky a seskupené položky mají obecný název, například Group1.
Přejmenování skupiny:
- Klikněte na buňky záhlaví s výchozím názvem např Skupina1
- Zadejte nový název, například Kampaň A.
Přidat více položek do skupiny:
- Vyberte všechny položky ve skupině
- Stiskněte klávesu Ctrl a vyberte položky, které chcete přidat do skupiny
- Klikněte pravým tlačítkem na jednu z vybraných položek a klikněte na skupinu
- POZNÁMKA: Pokud byla skupina přejmenována, bude tento název přepsán výchozím názvem.
Oddělení textových položek
Poté, co ručně seskupíte textové položky, možná budete chtít oddělit některé nebo všechny, skupin v kontingenčním poli.
Chcete-li oddělit jednu skupinu:
- Klikněte pravým tlačítkem na položku ve skupině
- Klikněte na Oddělit, a skupina se vrátí k jednotlivým položkám.
Oddělení jedné skupiny:
- Klikněte nahoře záhlaví skupiny nebo kliknutím na horní část kontingenčního pole vyberte všechny položky
- klepněte pravým tlačítkem u položky ve skupině
- Klikněte na Oddělit a všechny vybrané skupiny se vrátí k jednotlivým položkám.
Problémy při seskupování dat
Pokud se pokusíte seskupit datum nebo číslo pole, může se zobrazit chybová zpráva „Tento výběr nelze seskupit.“ Může to být způsobeno datovým modelem nebo prázdnými buňkami nebo dříve seskupenými položkami. Podrobnosti naleznete níže.
Datový model
Pokud jste při vytváření kontingenční tabulky zaškrtli pole „Přidat tato data do datového modelu“ nebude možné položky seskupit. Místo normální kontingenční tabulky je to Power Pivot založený na OLAP a funkce seskupení není k dispozici.
Prázdné buňky nebo text
V normální kontingenční tabulce (nikoli v datovém modelu) je seskupení problém obvykle nastane, když pole obsahuje záznamy s
- prázdnou buňkou v poli datum / číslo nebo
- textovou položkou v poli datum / číslo.
Chcete-li problém vyřešit
- U prázdných buněk vyplňte datum / číslo (v případě potřeby použijte fiktivní datum / číslo).
- Pokud je v poli datum / číslo text, odeberte jej.
- Pokud jsou čísla rozpoznávána jako text, použijte některou z technik ke změně textu na reálná čísla. Poté se vraťte k tomuto výukovému programu kontingenční tabulky a zkuste postup seskupení.
Předchozí seskupení
Pokud ve sloupci data nemáte prázdné buňky ani text, může po předchozím seskupení dat zůstat seskupené pole.
- Zkontrolujte seznam polí, abyste zjistili, zda existuje druhá kopie pole s datem, např Datum2.
- Pokud existuje, přidejte jej do oblasti řádků a oddělte jej.
- Potom byste měli být schopni znovu seskupit pole s datem.
Seskupení v jedné kontingenční tabulce ovlivní další
Pokud vytvoříte dvě kontingenční tabulky založené na stejné tabulce Excel v Excel 2007, když změníte seskupení v jedné kontingenční tabulce, stejné seskupení se objeví v druhé kontingenční tabulce.
Protože jste vytvořili dvě kontingenční tabulky ze stejných zdrojových dat, standardně používají stejnou kontingenční mezipaměť, seskupenou položky jsou v obou tabulkách stejné.
Chcete-li v každé kontingenční tabulce použít různé seskupení, budete muset pro každou kontingenční tabulku vytvořit samostatnou kontingenční mezipaměť.
Použijte následující metodu navrženou v diskusních skupinách Excel od Davea Peterson.
Vytvoření samostatné kontingenční mezipaměti pro druhou kontingenční tabulku:
- Vystřihněte druhou kontingenční tabulku a vložte ji do nového sešitu.
- Změňte seskupení druhé kontingenční tabulky.
- Vystřihněte druhou kontingenční tabulku z nového sešitu a vložte ji zpět do původního sešitu.
Stáhněte si Ukázkový soubor
Stáhněte si ukázkový soubor ZIP pro tento výukový program
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 doplňku Pivot 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 mnohem více!
Další výukové programy
Vymazat staré položky v kontingenční tabulce
Vypočítané pole – počet
Vypočítané Položky
Souhrnné funkce