Skupina Dat v Kontingenční Tabulce aplikace Excel podle Měsíce a Roku

Navštívit Učení Týmu | Aktualizováno 21. listopadu roku 2020

Platí pro: Microsoft® Excel® 2013, 2016, 2019 a 365 (Windows)

Pokud máte platné termíny uvedené ve vašem zdroji dat, můžete seskupit podle měsíce, rok nebo jiné datum, období v kontingenční tabulce v aplikaci Excel. Existují dva společné přístupy k seskupení podle data. Můžete seskupit podle data v kontingenční tabulce pomocí funkce seskupení (k tomu může dojít automaticky v závislosti na vaší verzi aplikace Excel)., Alternativně můžete také vytvořit výpočty ve zdrojových datech pro extrahování názvu měsíce a roku z pole data a použití polí v kontingenční tabulce.

Doporučujeme článek: Jak Odstranit Prázdné Řádky v aplikaci Excel Sešity (Velké Strategie, Triky a Zkratky)

Zdroj dat je obvykle zapsány svisle s daty ve sloupcích a názvy polí v horní části každého sloupce údajů.

Seskupení podle měsíce a roku v kontingenční tabulce

klíč k seskupení měsíc a/nebo rok v kontingenční tabulce je zdrojem pole s platnými daty (například OrderDate)., V závislosti na vašem nastavení na Ovládacím Panelu na vašem zařízení, platná data mohou být zadána jako měsíc/den/rok, den/měsíc/rok nebo rok/měsíc/den (i když mohou být formátovány tak, aby se objeví v jiných ohledech).

v Excelu 2016 a novějších verzích, pokud přetáhnete pole data do oblasti řádků nebo sloupců kontingenční tabulky, Excel se ve výchozím nastavení seskupí podle přírůstků data.

nejjednodušší způsob, jak seskupit podle data, je kliknout pravým tlačítkem myši na buňku v poli datum v kontingenční tabulce a vybrat požadované přírůstky seskupení. Data můžete seskupovat podle čtvrtletí, let, měsíců a dnů., Zdrojová data nemusí obsahovat sloupec rok, čtvrtletí nebo měsíc.

seskupit podle měsíce a / nebo roku v kontingenční tabulce:

  1. klikněte v kontingenční tabulce.
  2. přetáhněte pole data do oblasti řádků nebo sloupců v podokně úloh otočných polí.
  3. Vyberte buňku pole data v kontingenční tabulce, kterou chcete seskupit. Excel může vytvořit rok a / nebo měsíc pole automaticky.
  4. klepněte pravým tlačítkem myši na buňku a v rozevírací nabídce vyberte skupinu. Můžete také kliknout pravým tlačítkem myši na pole datum v oblasti řádků nebo sloupců v podokně úloh PivotTable Fields. Zobrazí se dialogové okno.,
  5. klikněte na data, která chcete seskupit. Vyberte čtvrtiny, roky, měsíce nebo dny. Můžete kliknout na více než jeden, jako jsou roky a Měsíce.
  6. klikněte na OK.

Seskupování dialogové okno nabízí více možností pro seskupení podle data:

datum pole budou seskupeny a nové pole bude přidán do oblasti seznamu skupin (v případě, že neexistoval předem).

v následujícím příkladu Excel automaticky seskupeny podle data období v podokně úloh PivotTable Fields., K tomu dojde v roce 2016 nebo později, verze:

odstranit seskupení období pro pole datum:

  1. Vyberte pole datum buňku v kontingenční tabulce, která má být seskupeny.
  2. klepněte pravým tlačítkem myši na buňku a vyberte skupinu. Můžete také kliknout pravým tlačítkem myši na pole datum v oblasti řádků nebo sloupců v podokně úloh PivotTable Fields. Zobrazí se dialogové okno.
  3. zrušte výběr nebo klikněte na skupiny, které chcete odstranit.
  4. klikněte na OK.

Chcete-li odstranit všechny seskupení:

  1. Vyberte buňku pole data v kontingenční tabulce, která byla seskupena.,
  2. klepněte pravým tlačítkem myši na buňku a vyberte Ungroup.

ovládání automatického seskupení

v Excelu 2016 a novějších verzích jsou data automaticky seskupena, pokud jsou umístěna v oblasti řádků nebo sloupců kontingenční tabulky. Toto nastavení můžete vypnout nebo zapnout.

Chcete-li zakázat automatické seskupení pro kontingenční tabulky:

  1. klikněte na kartu Soubor v pásu karet.
  2. klikněte na možnosti. Zobrazí se dialogové okno.
  3. klikněte na Data v kategoriích vlevo. Pokud používáte starší verzi aplikace Excel, klikněte na Upřesnit v kategoriích vlevo.,
  4. zaškrtněte nebo zaškrtněte políčko Zakázat automatické seskupení sloupců Datum/Čas v kontingenčních tabulkách.
  5. klikněte na OK.

Níže je dialogové okno Možnosti aplikace Excel v 365:

Pokud zakážete automatické seskupení, stále můžete klepněte pravým tlačítkem myši a skupiny v kontingenční tabulky, ale skupiny nebude vytvořen automaticky, když přetáhnete pole do oblasti v kontingenční tabulce.,

Problémy s kontingenční tabulky mezipaměti

Když vytvoříte kontingenční tabulku a skupiny pomocí funkce Group, pole a skupiny jsou uloženy v kontingenční cache, ne ve zdroji dat. To může být problematické, pokud vytvoříte více kontingenčních tabulek na základě stejné kontingenční mezipaměti. To znamená, že všechny kontingenční tabulky, které sdílejí stejnou mezipaměť, budou také sdílet seskupení dat.

neexistuje snadný způsob, jak zjistit, které kontingenční tabulky sdílejí stejnou mezipaměť v aplikaci Excel. K nalezení těchto informací byste museli použít VBA (Visual Basic Pro aplikace).,

mezipaměť můžete odhalit změnou rozsahu zdrojových dat, ale to může být obtížné, pokud máte spoustu kontingenčních tabulek.

vytvoření polí datového období ve zdrojových datech

Další možností je vytvořit pole ve zdrojových datech, která vypočítávají data (například rok a Měsíc). Můžete použít rok a textové funkce extrahovat rok a Měsíc z platného data pole.

například pokud máte Datum ve sloupci A, můžete vytvořit výpočet pro extrahování roku ve sloupci B a měsíc ve sloupci C.,

vypočítat rok v B2 (za předpokladu, že je platné datum v A2), zadejte následující vzorec a potom zkopírujte vzorec:

=ROK(A2)

Pro výpočet na název měsíce v C2 (za předpokladu, že je platné datum v A2), zadejte následující vzorec a potom zkopírujte vzorec:

=TEXT(A2,“mmmm“)

Pokud zdroj dat je Excel tabulky, to je nejlepší použít strukturovaného odkazování na vzorce v každé řadě jako =ROK() nebo =TEXT(,“mmmm“), kde OrderDate je název pole a @ je specifikátor odkazovat na aktuální řádek nebo záznam.,

výhodou vytvoření polí ve zdrojových datech je, že můžete vytvořit kontingenční tabulku a poté seskupit podle těchto polí v kontingenční tabulce jednoduše přetažením do oblasti řádků nebo sloupců.Budete moci vytvořit další kontingenční tabulky, které nezahrnují seskupení dat.

Další výhodou vytváření polí ve zdrojových datech je, že můžete znovu použít pole data období v jiných zprávách, vzorcích nebo kráječích.

shrnutí

zvolená metoda závisí na vašich potřebách., Nepoužívali jsme zde datový model Power Pivot (který poskytuje jinou metodu seskupení podle data), ale je k dispozici pouze v určitých verzích aplikace Excel. Také tyto metody předpokládají fiskální rok od ledna do prosince. Pokud chcete třídit měsíce podle fiskálního roku, který začíná a končí v jiných měsících (například od dubna do března), musíte ve zdrojových datech vytvořit další výpočty.

přihlaste se k odběru dalších článků, jako je tento

našli jste tento článek užitečný? Pokud chcete dostávat nové články, připojte se k našemu seznamu e-mailů.,

Related

10 Great Excel Pivot Table Shortcuts

Excel Flash Fill Tricks to Clean and Extract Data (10 Examples)

How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways)

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *