Jak změnit kontingenční tabulku. Vytváření a seskupování časových řad
Tento příspěvek představuje kolekci jednoduchých a elegantních nástrojů pro práci s kontingenčními tabulkami v Excelu. Čemu se anglicky říká tipy a triky. Udělejte si čas a přečtěte si tipy zde. Kdo ví, třeba konečně najdete odpověď na otázku, která vás už delší dobu trápí?
Tip 1: Automaticky aktualizujte kontingenční tabulky
Někdy chcete, aby se kontingenční tabulky aktualizovaly automaticky. Řekněme, že jste vytvořili kontingenční tabulku pro manažera. Je nepravděpodobné, že jej budete moci pravidelně aktualizovat, pokud vám správce nepovolí přístup k jeho notebooku. Lze povolit automatická aktualizace kontingenční tabulka, která se spustí při každém otevření sešitu:
- Klikněte pravým tlačítkem na kontingenční tabulku a dovnitř kontextová nabídka vybrat předmět Možnosti kontingenční tabulky.
- V zobrazeném dialogovém okně Možnosti kontingenční tabulky vyberte kartu Data.
- Zaškrtněte políčko Obnovit při otevírání souboru.
Rýže. 1. Povolte možnost Obnovit při otevírání souboru
Zaškrtávací políčko Obnovit při otevírání souboru by měl být nastaven pro každou kontingenční tabulku zvlášť.
Stáhněte si poznámku ve formátu nebo formátu, příklady ve formátu (soubor obsahuje kód VBA).
Tip 2: Aktualizujte všechny kontingenční tabulky sešitu najednou
Pokud váš sešit obsahuje více kontingenčních tabulek, může být jejich aktualizace současně problematická. Existuje několik způsobů, jak překonat tyto obtíže:
Metoda 1: Můžete nastavit každou kontingenční tabulku, která je součástí sešitu, aby se automaticky aktualizovala při otevření sešitu (podrobnosti viz Tip 1).
Metoda 3: Pomocí kódu VBA aktualizujte všechny kontingenční tabulky v sešitu na vyžádání. Tento přístup zahrnuje použití metody RefreshAll objektu Workbook. Chcete-li použít tuto techniku, vytvořte nový modul a zadejte následující kód:
Sub Refresh_All()
ThisWorkbook.RefreshAll
Tip 3: Seřaďte datové položky v náhodném pořadí
Na Obr. Obrázek 2 ukazuje výchozí pořadí, ve kterém jsou oblasti zobrazeny v kontingenční tabulce. Regiony jsou seřazeny do abecední pořadí: Západ, Sever, Středozápad a Jih. Pokud vaše podnikové zásady vyžadují, aby se jako první zobrazila oblast Západ a poté oblast Středozápad, Sever a Jih, proveďte ruční řazení. Jednoduše zadejte Midwest do buňky C4 a stiskněte klávesu Vstupte. Pořadí řazení regionů se změní.
Tip 4: Převeďte kontingenční tabulku na pevně zakódované hodnoty
Účelem vytvoření kontingenční tabulky je sumarizace a zobrazení dat ve vhodném formátu. Zdrojová data pro kontingenční tabulku jsou uložena samostatně, což představuje určitou režii. Převedení kontingenční tabulky na hodnoty vám umožní používat její výsledky bez přístupu k původním datům nebo mezipaměti kontingenční tabulky. Způsob převodu kontingenční tabulky závisí na tom, zda je ovlivněna celá tabulka nebo jen její část.
Chcete-li převést část kontingenční tabulky, postupujte takto:
- Vyberte data kontingenční tabulky, která chcete zkopírovat, klikněte pravým tlačítkem a vyberte z kontextové nabídky kopírovat(nebo na klávesnici zadejte Ctrl+C).
- Klepněte pravým tlačítkem myši kdekoli na listu a vyberte příkaz z místní nabídky Vložit(nebo stiskněte Ctrl+V).
Pokud potřebujete převést celou kontingenční tabulku, postupujte takto:
- Vyberte celou kontingenční tabulku, klikněte pravým tlačítkem a vyberte z kontextové nabídky kopírovat. Pokud kontingenční tabulka neobsahuje oblast FILTRY, můžete použít klávesová zkratka Ctrl+Shift+*.
- Klepněte pravým tlačítkem myši kdekoli na listu a vyberte možnost z místní nabídky Speciální vložka.
- Vyberte možnost Hodnoty a klikněte OK.
Před převodem kontingenční tabulky je vhodné odstranit mezisoučty, protože nejsou zvlášť užitečné v samostatné datové sadě. Chcete-li odstranit všechny mezisoučty, přejděte do nabídky Návrhář -> Mezisoučty -> Nezobrazovat mezisoučty. Chcete-li odebrat konkrétní mezisoučty, klepněte pravým tlačítkem myši na buňku, ve které se počítají mezisoučty. Vyberte položku v kontextové nabídce Možnosti pole a v dialogovém okně Možnosti pole V kapitole Výsledek vyberte přepínač Ne. Po kliknutí na tlačítko OK mezisoučty budou smazány.
Tip 5. Plnění prázdné buňky v polích LINE
Po převodu kontingenční tabulky se v listu zobrazují nejen hodnoty, ale také celá datová struktura kontingenční tabulky. Například data uvedená na Obr. 3 byly získány na základě kontingenční tabulky s rozložením v tabulkové formě.
Rýže. 3. Použití této převedené kontingenční tabulky bez vyplnění prázdných buněk na levé straně je problematické
Vezměte prosím na vědomí, že pole Kraj A Prodejní trh zachová stejnou strukturu řádků, která je přítomná, když jsou data v oblasti ROWS kontingenční tabulky. V Excelu 2013 existuje rychlý způsob Vyplnění buněk v oblasti ROW hodnotami. Klikněte do oblasti kontingenční tabulky a poté procházejte nabídkou Konstruktér -> Rozvržení sestavy -> (obr. 4). Poté můžete kontingenční tabulku převést na hodnoty, které vám poskytnou tabulku dat bez mezer.
Rýže. 4. Po použití příkazu Opakujte všechny popisky prvků všechny prázdné buňky jsou vyplněny
Tip 6: Hodnocení číselných polí v kontingenční tabulce
V procesu třídění a řazení polí obsahujících velký počet datových prvků není vždy snadné určit číselné pořadí analyzovaného datového prvku. Navíc, pokud je kontingenční tabulka převedena na hodnoty, přiřazení číselné hodnosti každému datovému prvku zobrazenému v celočíselném poli značně usnadní analýzu vygenerované datové sady. Otevřete kontingenční tabulku podobnou té, která je znázorněna na obr. 5. Vezměte prosím na vědomí, že stejný indikátor - Částka podle pole Objem prodeje- se zobrazí dvakrát. Klikněte pravým tlačítkem na druhou instanci indikátoru a vyberte příkaz z místní nabídky Dodatečné výpočty -> Seřadit od největšího po nejmenší(Obr. 6.)
Po vytvoření hodnosti můžete upravit popisky polí a formátování (obrázek 14.9). Výsledkem bude krásná hodnocená zpráva.
Tip 7: Zmenšete velikost sestavy kontingenční tabulky
Při generování sestavy kontingenční tabulky Excel vytvoří snímek dat a uloží je do mezipaměti kontingenční tabulky. Mezipaměť kontingenční tabulky je speciální oblast paměti, která ukládá kopii zdroje dat pro rychlejší přístup. Jinými slovy Excel vytvoří kopii dat a poté je uloží do mezipaměti přidružené k sešitu. Mezipaměť kontingenční tabulky poskytuje optimalizaci pracovního postupu. Jakékoli změny provedené v kontingenční tabulce, jako je změna umístění polí, přidání nových polí nebo skrytí jakýchkoli prvků, jsou dokončeny rychleji a požadavky na systémové prostředky jsou mnohem skromnější. Hlavní nevýhodou mezipaměti kontingenční tabulky je, že v podstatě zdvojnásobí velikost souboru sešitu pokaždé, když kontingenční tabulku vytvoříte od začátku.
Smažte původní data. Pokud sešit obsahuje zdrojovou datovou sadu a kontingenční tabulku, velikost jeho souboru se zdvojnásobí. Původní data tedy můžete bezpečně smazat a funkčnost vaší kontingenční tabulky to vůbec neovlivní. Po odstranění původních dat nezapomeňte uložit komprimovanou verzi souboru sešitu. Po odstranění zdrojových dat můžete kontingenční tabulku používat jako obvykle. Jediným problémem je, že kontingenční tabulku nelze aktualizovat kvůli chybějícím zdrojovým datům. Pokud potřebujete původní data, dvakrát klikněte na průsečík řádku a sloupce v oblasti součtů (na obr. 7 je to buňka B18). To způsobí, že Excel vypíše obsah mezipaměti kontingenční tabulky do nového listu.
Tip 8: Vytvořte automaticky se rozšiřující rozsah dat
Pravděpodobně jste se nejednou setkali se situacemi, kdy jste museli denně aktualizovat sestavy kontingenčních tabulek. Potřeba toho nejčastěji vzniká, když se do zdroje dat neustále přidávají nové záznamy. V takových případech budete muset před přidáním nových záznamů do nové kontingenční tabulky předefinovat dříve používaný rozsah. Předefinování původního rozsahu dat pro kontingenční tabulku není obtížné, ale může být zdlouhavé, když to musíte dělat často.
Řešením tohoto problému je převedení původního rozsahu dat na tabulku před vytvořením kontingenční tabulky. Díky Excel tabulky Můžete vytvořit pojmenovaný rozsah, který se může automaticky rozšiřovat nebo zmenšovat v závislosti na množství dat, která obsahuje. K rozsahu můžete také přiřadit jakoukoli komponentu, graf, kontingenční tabulku nebo vzorec, což vám umožní sledovat změny v sadě dat.
Chcete-li implementovat popsanou techniku, vyberte zdrojová data a poté klikněte na ikonu tabulky umístěnou na kartě Vložit(obr. 8) nebo stiskněte Ctrl+T (T English). Klikněte OK v okně, které se otevře. Všimněte si, že ačkoli nemusíte přepsat rozsah zdrojových dat v kontingenční tabulce, budete muset při přidávání zdrojových dat do rozsahu v kontingenční tabulce kliknout na tlačítko Aktualizace.
Tip 9: Porovnejte běžné tabulky pomocí kontingenční tabulky
Pokud děláte srovnávací analýza dvě různé tabulky, je vhodné použít kontingenční tabulku, která výrazně ušetří čas. Předpokládejme, že existují dvě tabulky, které zobrazují informace o zákaznících za rok 2011 a 2012 (obr. 9). Malé velikosti těchto tabulek jsou zde uvedeny pouze jako příklady. V praxi se používají tabulky, které jsou mnohem větší.
Proces porovnání vytvoří jednu tabulku, ze které se vytvoří kontingenční tabulka. Ujistěte se, že máte způsob, jak označit data spojená s těmito tabulkami. V uvažovaném příkladu k tomu slouží sloupec Fiskální rok(obr. 10). Po zkombinování dvou tabulek použijte výslednou kombinovanou datovou sadu k vytvoření nové kontingenční tabulky. Naformátujte kontingenční tabulku tak, aby jako značku tabulky používala oblast Sloupce kontingenční tabulky (identifikátor označující původ tabulky). Jak je znázorněno na Obr. 11, roky jsou v oblasti sloupců a informace o zákaznících jsou v oblasti řádků. Datová oblast obsahuje objemy prodeje pro každého zákazníka.
Tip 10: Automaticky filtrujte kontingenční tabulku
Jak víte, automatické filtry nelze použít v kontingenčních tabulkách. Existuje však trik, jak povolit automatické filtry v kontingenční tabulce. Principem použití této techniky je umístit ukazatel myši napravo od posledního nadpisu kontingenční tabulky (buňka D3 na obrázku 12) a poté přejít na pás karet a vybrat příkaz Data -> Filtr. Od této chvíle se ve vaší kontingenční tabulce objeví automatický filtr! Můžete například vybrat všechny zákazníky s nadprůměrnými transakčními kurzy. Automatické filtry přidávají do kontingenční tabulky další úroveň analýzy.
Tip 11: Převeďte datové sady zobrazené v kontingenčních tabulkách
Nejlepší rozložení pro zdrojová data převedená do kontingenční tabulky je tabulkové rozložení. Tento typ rozvržení má následující vlastnosti: neexistují žádné prázdné řádky ani sloupce, každý sloupec má záhlaví, každé pole má v každém řádku odpovídající hodnotu a sloupce neobsahují opakující se skupiny dat. V praxi se často setkáváme s datovými soubory podobnými tomu, co je znázorněno na Obr. 13. Jak vidíte, názvy měsíců se objevují v řadě podél horního okraje tabulky a slouží jako popisky sloupců a skutečná data. V kontingenční tabulce vytvořené z tabulky, jako je tato, by to vedlo k nutnosti spravovat 12 polí, z nichž každé představuje jiný měsíc.
Chcete-li tento problém vyřešit, můžete jako mezikrok použít kontingenční tabulku s několika konsolidovanými rozsahy (další podrobnosti viz). Chcete-li převést datovou sadu, která má maticový styl, na datovou sadu vhodnější pro vytváření kontingenčních tabulek, postupujte takto:
Krok 1: Spojte všechna nesloupcová pole do jednoho sloupce. Chcete-li vytvořit kontingenční tabulky s více konsolidovanými rozsahy, musíte vytvořit jeden sloupec dimenze. V tomto příkladu je vše, co nepatří do pole měsíce, považováno za dimenzi. Proto pole Prodejní trh A Popis služby by měly být spojeny do jednoho sloupce. Chcete-li sloučit pole do jednoho sloupce, jednoduše zadejte vzorec, který obě pole zřetězí pomocí středníku jako oddělovače. Pojmenujte nový sloupec. Zadaný vzorec se zobrazí v řádku vzorců (obr. 14).
Rýže. 14. Výsledek zřetězení sloupců Prodejní trh A Popis služby
Po vytvoření zřetězeného sloupce převeďte vzorce na hodnoty. Chcete-li to provést, vyberte nově vytvořený sloupec, stiskněte Ctrl+C a poté spusťte příkaz Vložit -> Speciální vložka -> Hodnoty. Nyní můžete sloupce odstranit Prodejní trh A Popis služby(obr. 15).
Rýže. 15. Sloupce odstraněny Prodejní trh A Popis služby
Krok 2: Vytvořte kontingenční tabulku s více rozsahy konsolidace. Nyní musíte zavolat hovor známý mnoha uživatelům předchozí verze Excelový mistr kontingenčních tabulek a grafů. Chcete-li vyvolat tohoto průvodce, stiskněte kombinaci kláves Alt+D+P. Bohužel tato klávesová zkratka je pro anglickou verzi Excelu 2013. Ruská verze odpovídá kombinaci kláves Alt+D+N. Ale z mně neznámých důvodů to nefunguje. Na panelu však můžete zobrazit starého dobrého průvodce kontingenční tabulkou rychlý přístup, cm. . Po spuštění průvodce vyberte přepínač V několika rozsazích konsolidace. Klikněte Dále. Nastavte spínač Vytvořte pole stránky a klikněte Dále. Určete pracovní rozsah a klikněte Připraveno(prohlednout detaily). Vytvoříte si kontingenční tabulku (obrázek 16).
Krok 3: Dvakrát klikněte na průsečík řádku a sloupce v řádku celkového součtu. V této fázi budete mít k dispozici souhrnnou tabulku (obr. 16), která obsahuje několik rozsahů konsolidace, což je prakticky zbytečné. Vyberte buňku na průsečíku řádku a sloupce celkového součtu a dvakrát na ni klikněte (v našem příkladu buňku N88). Obdržíte nový list, jehož struktura se podobá struktuře na obr. 17. Tento list je ve skutečnosti transponovanou verzí původních dat.
Krok 4. Rozdělení sloupce Řádek do samostatných polí. Zbývá rozdělit sloupec Čára do samostatných polí (návrat k původní struktuře). Bezprostředně za sloupec přidejte jeden prázdný sloupec Čára. Zvýrazněte sloupec A a poté přejděte na kartu Pás karet Data a klikněte na tlačítko Text sloupce. Na obrazovce se objeví dialogové okno Průvodce rozdělením textů do sloupců. V prvním kroku vyberte přepínač S oddělovači a klikněte na tlačítko Další. V dalším kroku vyberte přepínač středník a klikněte Připraveno. Naformátujte text, přidejte nadpis a převeďte původní data do tabulky stisknutím Ctrl+T (obr. 18).
Rýže. 18. Tato datová sada je ideální pro vytvoření kontingenční tabulky (srovnej s obrázkem 13)
Tip 12: Zahrnutí dvou formátů čísel do kontingenční tabulky
Nyní se podívejme na situaci, kdy normalizovaná datová sada znesnadňuje vytvoření kontingenční tabulky vhodné pro analýzu. Příklad je znázorněn na Obr. 19, která obsahuje dva různé ukazatele pro každý prodejní trh. Všimněte si sloupce D, který identifikuje indikátor.
I když tato tabulka může být příkladem dobrého formátování, není až tak skvělá. Upozorňujeme, že některé metriky musí být zobrazeny v číselném formátu, zatímco jiné musí být zobrazeny v procentuálním formátu. Ale v původní databázi pole Význam je typu Double. Když vytvoříte kontingenční tabulku z datové sady, nemůžete stejnému poli přiřadit dva různé formáty čísel Význam. Platí zde jednoduché pravidlo: jedno pole odpovídá jednomu číselnému formátu. Zkouším přiřadit číselný formát pole, kterému byl přiřazen formát procent, způsobí, že se procentuální hodnoty změní na běžná čísla, která končí znakem procenta (obrázek 20).
K vyřešení tohoto problému se používá vlastní číselný formát, který zformátuje jakoukoli hodnotu větší než 1,5 jako číslo. Pokud je hodnota menší než 1,5, je formátována jako procento. V dialogovém okně Formát buňky vyberte kartu (všechny formáty) a v terénu Typ zadejte následující formátovací řetězec (obr. 21): [=1.5]$# ##0; [<1,5]0,0%
Rýže. 21. Použijte vlastní formát čísel, ve kterém jsou čísla menší než 1,5 formátována jako procenta
Získaný výsledek je znázorněn na Obr. 22. Jak vidíte, každý indikátor je nyní naformátován správně. Recept uvedený v této radě samozřejmě není univerzální. Spíše ukazuje směr, kterým lze experimentovat.
Tip 13: Vytvořte rozložení četnosti pro kontingenční tabulku
Pokud jste někdy vytvářeli frekvenční rozdělení pomocí funkce Excel Frekvence, pak asi víte, že jde o velmi obtížný úkol. Navíc po změně datových rozsahů musí vše začít znovu. V této části se dozvíte, jak vytvořit jednoduché rozdělení frekvencí pomocí jednoduché kontingenční tabulky. Nejprve vytvořte kontingenční tabulku s daty v oblasti řádků. Věnujte pozornost obr. 23, kde v oblasti čáry je pole Hlasitost odbyt.
Klikněte pravým tlačítkem na libovolnou hodnotu v oblasti řádků a vyberte možnost z místní nabídky Skupina. V dialogovém okně Seskupování(obr. 24) určete hodnoty parametrů, které určují začátek, konec a krok rozdělení frekvencí. Klepněte na tlačítko OK.
Rýže. 24. V dialogovém okně Seskupování konfigurovat parametry distribuce frekvence
Pokud přidáte pole do kontingenční tabulky Zákazník(obr. 25), získáme rozdělení četnosti zákaznických transakcí vzhledem k velikosti objednávek (v dolarech).
Rýže. 25. Nyní máte k dispozici distribuci zákaznických transakcí podle velikosti objednávek (v dolarech)
Výhodou této techniky je, že filtr sestavy kontingenční tabulky lze použít k interaktivnímu filtrování dat na základě jiných sloupců, jako je např. Kraj A Prodejní trh. Uživatel má také možnost rychle nakonfigurovat intervaly distribuce frekvence kliknutím pravým tlačítkem myši na libovolné číslo v oblasti řádků a poté výběrem možnosti Skupina. Pro přehlednost prezentace lze přidat souhrnné schéma (obr. 26).
Tip 14: Použijte kontingenční tabulku k distribuci sady dat mezi listy v sešitu
Analytici často potřebují vytvářet různé sestavy kontingenčních tabulek pro každý region, trh, manažera a tak dále. Dokončení tohoto úkolu obvykle zahrnuje zdlouhavý proces kopírování kontingenční tabulky do nového listu a následnou změnu pole filtru tak, aby odráželo příslušnou oblast a správce. Tento proces se provádí ručně a opakuje se pro každou analýzu. Obecně ale platí, že vytváření jednotlivých kontingenčních tabulek můžete delegovat na Excel. V důsledku použití parametru Pro každý prvek v oblasti polí filtru se automaticky vytvoří samostatná kontingenční tabulka. Chcete-li tuto funkci použít, jednoduše vytvořte kontingenční tabulku, která obsahuje pole filtru (obrázek 27). Umístěte kurzor kamkoli do kontingenční tabulky a na kartu Analýza ve skupině týmů Kontingenční tabulka klikněte na rozevírací seznam Možnosti(obr. 28). Poté klikněte na tlačítko Zobrazit stránky filtru přehledů.
Rýže. 28. Klikněte na tlačítko Zobrazit stránky filtru přehledů
V zobrazeném dialogovém okně (obr. 29) můžete vybrat pole filtru, pro které budou vytvořeny samostatné kontingenční tabulky. Vyberte příslušné pole filtru a klikněte OK.
Rýže. 29. Dialogové okno Zobrazení stránek filtru sestav
Pro každý prvek pole filtru bude vytvořena kontingenční tabulka umístěná na samostatném listu (obr. 30). Všimněte si, že štítky listů jsou pojmenovány stejně jako prvky pole filtru. Vezměte prosím na vědomí, že parametr Zobrazit stránky filtru lze použít pro filtrování polí jedno po druhém.
Tip 15: Použijte kontingenční tabulku k distribuci sady dat mezi jednotlivé sešity
V tipu 14 jsme použili speciální možnost oddělení kontingenční tabulky podle prodejních trhů na různých listech sešitu. Pokud potřebujete rozdělit počáteční údaje pro různé prodejní trhy v samostatných knihách můžete použít malý kód VBA. Chcete-li začít, umístěte pole, podle kterého chcete filtrovat, do oblasti polí filtru. Umístěte pole Objem prodeje do rozsahu hodnot (obr. 31). Následující kód VBA postupně vybere každý prvek FILTER a zavolá funkci Ukázat detaily, vytvoření nového datového listu. Tento list se poté uloží do nového sešitu
KódVBA.
Sub ExplodeTable()
Dim PvtItem as PivotItem
Dim PvtTable as PivotTable
Dim strfield As PivotField
„Změna proměnných podle skriptu
ConststrFieldName = "Trh prodeje" '<—Изменение имени поля
Const strTriggerRange = "A4" ‘<—Изменение диапазона триггера
„Změňte název kontingenční tabulky (v případě potřeby)
SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") ‘<—Изменение названия сводной
'Procházet každý prvek vybraného pole
Pro každou položku PvtItem v tabulce PvtTable.PivotFields(strFieldName).PivotItems
PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name
Range(strTriggerRange).ShowDetail = True
„Pojmenování dočasného listu
ActiveSheet.Name = "TempSheet"
„Kopírování dat do nového sešitu a odstranění dočasného listu
ActiveSheet.Cells.Copy
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs_
Filename:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "
ActiveWorkbook.Close
Sheets(" Tempsheet ").Odstranit
Application.DisplayAlerts = True
Zadejte tento kód do nového modulu VBA. Zkontrolujte hodnoty následujících konstant a proměnných a v případě potřeby je změňte:
- Const strFieldName. Název pole použitého k oddělení dat. Jinými slovy, je to pole, které je umístěno v oblasti stránek filtru/kontingenční tabulky.
- Const strTriggerRange. Spouštěcí buňka, která ukládá jedno číslo z datové oblasti kontingenční tabulky. V našem případě je spouštěcí buňka A4 (viz obr. 31).
V důsledku provedení kódu VBA se data pro každý prodejní trh uloží do samostatného sešitu.
Poznámka byla napsána na základě knihy Jelen, Alexander. . Kapitola 14.
Kontingenční tabulky jsou nástrojem pro interaktivní zobrazení dat. Umožňují vám překládat nekonečné řádky a sloupce dat do čitelné a prezentovatelné podoby. Položky můžete seskupit, například kombinovat oblasti země podle okresů, filtrovat výsledky, měnit vzhled a vkládat speciální vzorce, které provedou nové výpočty.
Kontingenční tabulky získaly svůj název podle své schopnosti interaktivně přetahovat pole, což vám umožňuje dynamicky měnit vzhled, čímž získáte zcela novou perspektivu pomocí stejného zdroje dat. Upozorňujeme, že samotná zdrojová data se nemění a nezávisí na tom, jaký typ zobrazení zvolíte. Proto jsou kontingenční tabulky ideální pro .
Struktura kontingenční tabulky
Kontingenční tabulka se skládá ze čtyř oblastí: Filtry, Sloupce, Řádky a Hodnoty. V závislosti na tom, kam data umístíte, se bude měnit vzhled kontingenční tabulky. Podívejme se na funkci jednotlivých oblastí podrobněji.
Rozsah hodnot
Veškeré výpočty výchozích dat probíhají v této oblasti. Na obrázku je rozsah hodnot zvýrazněn červeným obdélníkem. Tento příklad ukazuje hlavní součty rozdělené podle federálních okresů.
Do tohoto pole se zpravidla přetahují údaje, které je třeba vypočítat - celková plocha území, průměrný příjem na hlavu atd.
Oblast čáry
Zdrojová data přenesená do tohoto pole jsou umístěna na levé straně kontingenční tabulky a představují jedinečné hodnoty tohoto pole. Řádková oblast má obvykle alespoň jedno pole, i když je možné, že nemá žádná pole. Na obrázku vyznačeno žlutě.
Zde se obvykle umísťují údaje, které je třeba seskupovat a kategorizovat, jako je název kraje nebo produktů.
Oblast sloupců
Oblast Sloupce obsahuje záhlaví, která jsou v horní části kontingenční tabulky (označená zeleně). V tomto příkladu obsahuje oblast sloupců jedinečný seznam klíčových metrik okresu.
Oblast sloupců je ideální pro vytváření matice dat nebo indikaci časového trendu.
Oblast filtrů
V horní části kontingenční tabulky je volitelná oblast filtrů s jedním nebo více poli (na obrázku hnědá). V příkladu je filtr nastaven na příjmové rozsahy obyvatel země.
V závislosti na volbě filtru se mění vzhled kontingenční tabulky. Pokud chcete izolovat nebo naopak soustředit se na konkrétní data, musíte data umístit do tohoto pole.
Vytvoření kontingenční tabulky
Nyní, když máte představu o struktuře kontingenční tabulky, můžeme ji začít vytvářet.
Můžete si stáhnout ukázkový soubor
- Klikněte na libovolnou buňku uvnitř tabulky se zdrojovými daty (ty, které použijete k vytvoření kontingenční tabulky)
- Přejděte na kartu Vložit –> Stůl -> kontingenční tabulka, jak je znázorněno na obrázku.
- Vytvoření kontingenční tabulky Určíme zdroj dat a místo, kam chceme kontingenční tabulku umístit. Upozorňujeme, že ve výchozím nastavení Excel umístí sestavu na nový list v aktuálním sešitu. Chcete-li změnit umístění, vyberte na existující list a zadejte požadovaný rozsah.
- Klepněte na tlačítko OK.
V tomto okamžiku jste vytvořili prázdnou sestavu kontingenční tabulky na novém listu.
Rozložení kontingenční tabulky
Nalevo od prázdné kontingenční tabulky uvidíte dialogové okno Pole kontingenční tabulky, jak je znázorněno na obrázku.
Pole, která potřebujete, můžete do kontingenční tabulky přidat jednoduchým přetažením pojmenovaných rozsahů do jedné ze čtyř oblastí kontingenční tabulky – Filtry, Sloupce, Řádky nebo Hodnoty.
Vezměte prosím na vědomí, že pokud dialogové okno Pole kontingenční tabulky se nezobrazí, klepněte pravým tlačítkem kamkoli do kontingenční tabulky a vyberte Zobrazit seznam polí.
Nyní, než začneme přetahovat pole, musíme se rozhodnout, co chceme vidět. Odpověď na tuto otázku vám dá představu o tom, které pole umístit do které oblasti.
V našem příkladu chceme vidět hlavní ukazatele krajů seskupené podle okresů. Chcete-li to provést, musíte přidat pole Federální okres A Kraj do regionu Po řádcích. A pole Rozloha území, Obyvatelstvo A Peněžní příjem do regionu Významy.
V seznamu polí, která chcete přidat do sestavy, zaškrtněte pole Federální okres. Nyní v oblasti Struny a data pole se objevila v kontingenční tabulce. 
V seznamu s poli, která chcete přidat do sestavy, zaškrtněte políčka vedle hlavních indikátorů
Vezměte prosím na vědomí, že pokud zaškrtneme políčka vedle polí s textovými hodnotami, Excel ve výchozím nastavení umístí tyto hodnoty do oblasti řádků s číselnými hodnotami do oblasti hodnot.
Vytvořili jsme jednoduchou souhrnnou tabulku, která zobrazuje hlavní ukazatele federálních okresů a nepotřebujeme provádět další kroky k úpravě zdrojových dat.
Úprava kontingenční tabulky
Jednou z úžasných vlastností kontingenčních tabulek je schopnost přidávat neomezený počet polí pro analýzu. Chcete se například podívat na území okresu jako celku a každého kraje zvlášť. Chcete-li to provést, klepněte na libovolné místo v kontingenční tabulce a vyvolejte dialogové okno Pole kontingenční tabulky a přesunout pole Kraj do regionu Čáry. Podívejte se, jak se změnil váš stůl.
Použití filtrů v kontingenční tabulce
Často potřebujeme vytvořit sestavu pro různé typy dat, například analyzovat pouze konkrétní kraje. Místo plýtvání časem změnou zdrojových dat použijme oblast filtrů. Přetáhněte pole Federální okres do regionu Filtry. Nyní můžete změnit vzhled kontingenční tabulky nastavením filtru na požadovaný kraj.
Aktualizace kontingenční tabulky
Postupem času se původní data mění a přidávají se do nich nové řádky a sloupce. Chcete-li aktualizovat kontingenční tabulku, použijte příkaz Aktualizace Chcete-li to provést, klepněte pravým tlačítkem myši kdekoli v tabulce a vyberte Aktualizace.
Existují situace, kdy se změní struktura zdrojových dat, například potřebujete přidat nové řádky do tabulky s daty. Tento typ změny ovlivní rozsah zdroje dat a musí být hlášen do kontingenční tabulky. Jednoduchá aktualizace v tomto případě nebude fungovat, musíte rozšířit rozsah zdroje dat.
Klepněte levým tlačítkem na libovolné místo v kontingenční tabulce. Pojďme na kartu Práce s kontingenčními tabulkami -> Analýza –> Zdroj dat.
V zobrazeném dialogovém okně Změnit zdroj dat kontingenční tabulky nastavit změněný rozsah dat.
Sečteno a podtrženo
Tento článek se podíval na příklad vytvoření jednoduché kontingenční tabulky, s jejíž pomocí můžeme přesněji a podrobněji posoudit potřebné parametry.
Excelové tabulky jsou mocným nástrojem pro analýzu dat. Někdy musíte analyzovat tabulky sestávající ze stovek nebo dokonce tisíců záznamů. Zde nám pomáhá nástroj zvaný „kontingenční tabulky“, který nám umožňuje jediným pohledem prohlédnout si celé pole dat. Změnou určitých parametrů kontingenčních tabulek můžeme snadno vytáhnout informace, které nás zajímají.
Chcete-li vytvořit kontingenční tabulku na kartě " Vložit" ve skupině " Tabulky» klikněte na tlačítko « Kontingenční tabulka"(Obr. 1).
Rýže. 1 Vytvořte kontingenční tabulku.
V okně, které se objeví, v poli „ Vyberte tabulku» nebo bude automaticky vybrán celý váš stůl. Pokud chcete pro analýzu vybrat pouze část tabulky, klikněte na tlačítko na konci vstupního řádku a pomocí myši vyberte požadovanou část tabulky. Je nutné dát pozor na to, že horní řádek zvoleného rozsahu musí obsahovat názvy sloupců, aby je bylo možné dále zpracovávat jako názvy polí, pro které bude sestava kontingenční tabulky generována. Po ručním výběru nebo úpravě klikněte znovu na tlačítko na konci vstupního řádku. Kontingenční tabulku lze zobrazit na aktuálním listu nebo můžete vybrat nový. Dále klikněte na " OK"(obr.2).
Rýže. 2 Vyberte rozsah kontingenční tabulky.
Nyní jste otevřeli list, ve kterém je vytvořena kontingenční tabulka. Vpravo od kontingenční tabulky je seznam polí (obr. 3). Výběrem polí tvoříme kontingenční tabulku a pořadí výběru je důležité.
Rýže. 3 Nová kontingenční tabulka.
V závislosti na tom, která pole jsou vybrána jako první, se změní vzhled kontingenční tabulky. Pořadí, ve kterém jsou pole vybrána, se zobrazuje vpravo dole jako „ Názvy linek».
Rýže. 4 Generovaná kontingenční tabulka.
Kontingenční tabulky v Excelu umožňují analyzovat data, která jsou v jednom velkém rozsahu. Zdrojovou tabulku, která bude použita pro analýzu, lze navíc vytvořit buď v Excelu, nebo v jiné databázi či jiném dokumentu.
Vše, co musíte udělat, je vybrat potřebná záhlaví pro řádky a sloupce ze stávajících. Můžete také použít filtr na celou kontingenční tabulku jako celek nebo pouze na hodnoty, které jsou uvedeny v názvech řádků a sloupců.
Nyní o požadavcích, které je třeba dodržovat při jeho vytváření v Excelu. Zdrojová data musí být prezentována ve formě tabulky, která musí mít nadpis sloupců, tedy záhlaví. Pro tyto účely je vytvoření chytré tabulky v Excelu perfektní. Také by neměl obsahovat prázdné řádky, sloupce nebo buňky. Neměly by zde být žádné skryté řádky/sloupce ani sloučené buňky.
Jak vytvořit
Nyní se podívejme na příklad vytvoření kontingenční tabulky. Předpokládejme, že máme údaje o tržbách obchodu s oblečením: který prodejce, jaké číslo, jaké položky prodal a za jakou částku.
Chcete-li vytvořit kontingenční tabulku, vyberte libovolnou buňku z původní, přejděte na kartu „Vložit“ a klikněte na tlačítko "kontingenční tabulka".
Zobrazí se následující dialogové okno. V něm musíte uvést rozsah nebo název tabulky se zdrojovými daty a zde je můžete vybrat z jiného zdroje. Poté označte fixem, kam umístit sestavu, která se má vytvořit. Udělejme to na novém listu. Klepněte na tlačítko OK.
V otevřeném Excelový sešit vznikne nový list, na který se umístí dosud prázdná kontingenční tabulka.
Na pravé straně se zobrazí seznam polí a oblastí. Pole jsou všechna záhlaví sloupců, která byla v původním rozsahu. Pomocí myši je přetáhneme do jedné ze čtyř níže uvedených oblastí. Tím se vytvoří kontingenční tabulka.
Přidaná pole budou označena zaškrtnutím. V oblastech tabulky můžete změnit jejich umístění, abyste dosáhli vzhledu, který je nejvhodnější pro analýzu vašich zdrojových dat.
Jak uspořádat pole
Nyní se rozhodneme, na jakém principu budeme data analyzovat. Potřebujete například zjistit, který prodejce prodal který produkt v každém měsíci a za jakou částku.
Údaje vyfiltrujeme podle vybraného sortimentu prodejců. To znamená, že vybereme prodejce a v tabulce se zobrazí zboží, které prodává. Klikněte levým tlačítkem na pole „Vendor“ a přetáhněte jej do oblasti „Filtr sestav“. Tabulka se změnila a přidané pole je nyní označeno zaškrtnutím.
Pro řádky vyberte „Produkty“. Podobně přetáhněte požadované pole do oblasti "Názvy řádků".
Vezměte prosím na vědomí, že do oblasti můžete vložit několik polí. Například v řádcích vybereme produkty a uvedeme cenu. Produkty se staly rozbalovacími seznamy, které zobrazují cenu. Pokud nejprve zadáte cenu a poté produkty, z rozbalovacího seznamu se stane cena. Zde záleží na pořadí polí.
Pokud bychom měli v původní tabulce sloupec „Units“. Pak by tato souhrnná tabulka ukázala, který prodejce, ve kterém měsíci, kolik jednotek zboží prodal za určitou cenu.
Do regionu "Názvy sloupců" Přetáhněte pole „Datum“. Chcete-li zobrazit prodeje ne za každý den, ale například po měsíci, klikněte pravým tlačítkem na libovolné datum a z nabídky vyberte „Skupina“.
Tabulka bude mít následující podobu.
Nyní přetáhněte pole „Částka“ do oblasti „Hodnoty“.
Jak vidíte, zobrazovala se pouze čísla, i když v původním rozsahu byl nastaven číselný formát buněk pro tento sloupec, může být i peněžní nebo finanční. Ve sloupci „Datum“ byl také vhodný formát buňky – datum.
Chcete-li to opravit, vyberte požadovaný rozsah buněk v souhrnu a stiskněte pravé tlačítko myši. poté vyberte z nabídky "Formát čísel".
V dalším okně vyberte „Numerical“, můžete zaškrtnout políčko "Oddělovač bitových skupin" a klikněte na "OK".
Jak pracovat s daty
Poté, co vezmeme v úvahu všechna potřebná pole, můžeme začít pracovat s kontingenční tabulkou v Excelu. Výběr prodejce: zaškrtnutím políčka můžete vybrat jednoho, několik nebo všechny najednou "Vybrat více položek".
Můžete také použít filtr pro řádky a sloupce. V příkladu se jedná o produkty a měsíce. Zaškrtnutím políček Oblek a Kalhoty můžete například zjistit, za kolik je prodali všichni prodejci nebo konkrétní prodejce.
V oblasti Hodnoty můžete nakonfigurovat nastavení pro pole. Příklad zobrazuje součet hodnot: Romové prodali v únoru košile v hodnotě 1 800,00. uvidíme kolik to bude kusů. Klikněte levým tlačítkem myši na řádek "Součet podle polí..." a vyberte z nabídky "Možnosti hodnotového pole".
V dalším okně vyberte ze seznamu „Množství“, vyberte, co je vhodné konkrétně pro váš případ, a klikněte na „OK“.
Nyní, když se podíváme na hodnoty, můžeme pochopit, že Romové v únoru prodali dvě košile.
Nyní udělejme obecný filtr pro tabulku podle měsíce. Změníme oblasti: ve „Filtru sestav“ přetáhneme pole „Datum“ do "Názvy sloupců"- "Prodavač".
Taková souhrnná tabulka ukáže, kolik a jaké zboží každý prodejce prodal za celé období nebo za konkrétní měsíc.
Pozor také na pásku. Když vyberete libovolné buňky z kontingenční tabulky, zobrazí se v ní karta "Práce s kontingenčními tabulkami" se dvěma podzáložkami „Parametry“ a „Návrhář“.
V mé praxi se úkol analyzovat data dříve shromážděná v kontingenční tabulce objevil více než jednou. Předpokládejme, že při analýze prodeje jste použili počáteční data prezentovaná v následujícím formátu (tabulka 1):
Na základě těchto dat je snadné vytvořit kontingenční tabulku v Excelu, něco jako (Tabulka 2):
Pokud chcete rozdělit klienty podle objemu prodeje, abyste získali souhrnnou tabulku jako (Tabulka 3):
pak narazíte na potíže, protože takovou tabulku na základě zdrojových dat nebudete moci vytvořit (tabulka 1).
Stáhněte si článek ve vzorovém formátu v nebo
Pokud se jedná o jednorázový postup, nejprve vytvoříte tabulku. 2 a poté na jeho základě vytvořte tabulku. 3. Ale co když chcete podepřít stůl. 3 je aktuální, když se změní zdrojová data (tabulka 1)!?
Nevím standardních metod v Excelu, který vám to umožní. Možnost v průvodci kontingenční tabulkou „vytvořit tabulku na základě dat umístěných... v jiné kontingenční tabulce“ bohužel nefunguje:
Při použití průvodce je nová kontingenční tabulka „založena“ na stejných zdrojových datech jako původní kontingenční tabulka. Pro usnadnění použijte soubor Excel s příkladem. Přejděte na „standardní“ list. Poskytuje podrobné prodejní údaje: číslo a datum objednávky, položky zahrnuté v objednávce a množství zboží
Na základě těchto dat vytvořte standardním způsobem kontingenční tabulku a poté spusťte Průvodce kontingenční tabulkou, vyberte možnost „vytvořit tabulku na základě dat umístěných... v jiné kontingenční tabulce“, klikněte na „Další“:
V kroku 2 vyberte, na základě které kontingenční tabulky vytvoříte novou tabulku:
Je vidět, že druhý souhrn „spoléhá“ na stejná data jako první:
Můžete experimentovat a ujistit se, že datová sada druhé kontingenční tabulky nezávisí na typu první kontingenční tabulky (tedy na tom, jaká pole a jak jsme vybrali v první tabulce), ale závisela pouze na zdrojových datech. (sloupce A–D).
Ve skutečnosti jsme vytvořili kopii první kontingenční tabulky. Standardní metody Excelu tedy nejsou pro řešení našeho problému vhodné. Použijeme několik malých triků.
Příklad je uveden v souboru Excel na listu „trik“. Nejprve vytvořte pojmenovaný dynamický rozsah na základě zdrojových dat – „out1“ pomocí funkce OFFSET (můžete vidět, jak to udělat). Pojmenovaný rozsah nás ušetří problémů při přidávání zdrojových dat a umožní nám aktualizovat všechny kontingenční tabulky pouhým kliknutím na tlačítko „Aktualizovat“:
Při vytváření kontingenční tabulky určete, že zdrojová data jsou rozsah s názvem "ref1":
U kontingenční tabulky vypněte celkové součty:
Vytvořte pojmenovaný rozsah pro kontingenční tabulku také pomocí funkce OFFSET:
Proto jsme vypnuli součty – aby se do tohoto rozmezí nevešly!
Spusťte Průvodce kontingenční tabulkou (z panelu nástrojů Rychlý přístup) a vyberte „Vytvořit tabulku na základě dat umístěných... v seznamu nebo databázi MS Excel“, klikněte na „Další“:
Ve druhém kroku zadejte rozsah obsahující zdrojová data pro druhou kontingenční tabulku – „sv1“, klikněte na „Další“:
Umístěte druhé shrnutí vedle prvního:
Seskupte jednotlivé hodnoty do rozsahů a vytvořte souhrnný graf:
Experimentujte se zdrojovými daty přidáváním/odebíráním řádků. Stačí postupně aktualizovat první a druhou kontingenční tabulku a všechny provedené změny budou zohledněny.
