• Generování reportů v excelu. Excel lifehacks pro ty, kteří se zabývají reportováním a zpracováním dat

    Excel je výkonná kancelářská aplikace, která umožňuje provádět složité výpočty, organizovat informace a vytvářet analytické výpočty. Bohužel mnoho uživatelů, kteří nerozumí složitosti Excelu, jej používá pouze jako jednoduchý nástroj pro počítání, jako je kalkulačka.

    Analyzujte v rekordním čase krátký čas Prezentované informace doplněné o číselné ukazatele lze na jejich základě využít k efektivním manažerským rozhodnutím, pomáhá kontingenční tabulka v Excelu a jak na ni lze snadno pochopit čtením doporučení pokročilých uživatelů.

    Kontingenční tabulka se vytvoří pomocí existující tabulky, kterou může být Excel nebo jiný databázový produkt.

    Pokud se uživatel rozhodne jít počáteční kurz jak vytvořit kontingenční tabulku v Excelu, pak je pro něj lepší procvičit si na takto vytvořené tabulkové verzi kancelářská aplikace. Po výborném pochopení základů tvorby kontingenčních tabulek v Excelu můžete přejít ke složitějším možnostem, kdy se indikátory získávají stahováním z dokumentů vytvořených zcela jinými aplikacemi.

    Primární požadavky

    Existuje řada požadavků, které musí být splněny, jinak kontingenční tabulka nebude provádět úkoly, které uživatel očekává.

    Hlavním požadavkem je, že všechny údaje musí být zadány ve formě tabulky, v jejímž horním rohu je samozřejmě hlavička (označující názvy sloupců).

    Kromě toho musí být zcela vyloučeny prázdné řádky a sloupce. Mimo tabulku v řádcích nebo sloupcích sousedících s používanou oblastí by neměly být žádné poznámky ani cizí položky.

    Je vhodné, aby všechny buňky tabulky obsahovaly také data, pokud možno se vyvarujte prázdným buňkám, protože to může negativně ovlivnit systematizaci a následnou analýzu informací.

    V Excelu uživatelé často kombinují více buněk. Pokud potřebujete vytvářet souhrnné sestavy, budete se muset takové agregaci vyhnout. Není přijatelné skrývat řádky nebo sloupce, o kterých se uživatel domnívá, že v této situaci nejsou důležité.

    Návod na vytvoření

    Jakmile uživatel splní všechny výše uvedené požadavky, můžete pokračovat další akce, což vám umožní pochopit, jak vytvořit kontingenční tabulku v Excelu.

    Nejprve musíte najít první buňku, která se nachází bezprostředně pod záhlavím tabulky, a aktivovat ji. Chcete-li to provést, musíte umístit kurzor myši na tuto buňku a kliknout levým tlačítkem myši.

    Poté v hlavní nabídce přejděte na kartu „Vložit“, po které se zobrazí její podnabídka, mezi kterou bude ikona s názvem položky „Kontingenční tabulka“. Kliknutím na něj se aktivuje průvodce tvorbou souhrnných sestav, pomocí kterého bude tvorba tabulkové analytické sestavy mnohem jednodušší.

    V dialogovém okně, které se objeví, vás tabulkový editor požádá o zadání rozsahu, který bude analyzován. Nejčastěji jej průvodce nezávisle určí správně, takže není třeba provádět změny.

    Je také nutné uvést, kde chce uživatel vytvořit souhrnnou sestavu:

    • na stejném listu, kde jsou k dispozici hlavní ukazatele;
    • na novém listu.

    Po přepnutí na nový list tam uživatel najde oblast pro tabulku a také seznam platných polí. Poté, co se uživatel rozhodl, které konkrétní indikátory by měly být analyzovány, musí přetáhnout vybraná pole myší do oblastí „Filtr“, „Sloupec“. Tímto způsobem můžete vytvářet sloupce souhrnné sestavy.

    Dále se čáry vykreslují přesně stejným způsobem přetažením požadovaných polí myší. Pokud existuje několik řádků, je důležité určit, které by měly být nahoře a které dole. Nejprve můžete uspořádat řádky ve stejném pořadí a sledovat, jak to bude pohodlné a jak správně se zobrazí souhrnné informace. Pokud je obtížné analyzovat data s tímto uspořádáním, pak lze vše snadno opravit - stačí podržet určité pole myší a pohybovat s ním nahoru nebo dolů.

    Nakonec zbývá udělat poslední oblast souhrnné zprávy „Hodnoty“. Přenese se do něj pole, které je klíčové pro analýzu, například celková hmotnost, celkové množství.

    Pokud vše uděláte přesně podle pokynů, zobrazí se v této fázi analytická zpráva. Pokud jsou zjištěny chyby, stačí prohodit pole, aktualizovat data a tabulka bude automaticky znovu vytvořena.

    Použití souhrnných sestav

    Uživatel se rozhodne vytvořit kontingenční tabulku, pokud je potřeba často využívat analytické činnosti. V tomto ohledu je také užitečné vědět, že tabulka je vybavena filtrem, který umožňuje systematizovat data podle zadaných kritérií.

    Použití filtru

    Pro začínající uživatele je užitečné vědět, jak aktualizovat kontingenční tabulku v Excelu a jak dotazovat na základě seskupování dat.

    Je důležité aktualizovat data v kontingenční tabulce, když uživatel provede nějaké změny v hlavní tabulce. Chcete-li aktualizovat data, měli byste kliknout myší v oblasti tabulky a zavolat extra menu. Položku „Aktualizovat“ v ní snadno najdete a jejím výběrem dojde k aktualizaci automaticky.

    Poměrně důležitou akcí je také použití filtru v souhrnných sestavách. Velmi často se zadávají denní data zaměstnanců a pracovník odpovědný za analytické reporty potřebuje analyzovat data za čtvrt nebo půl roku.

    Tento problém pomáhá vyřešit filtr. Už nebudete muset dělat nic složitého. Stačí kliknout myší (pravá klávesa) na libovolné datum kontextová nabídka vyberte „Skupina“, otevře se dialogové okno, ve kterém můžete určit metody seskupování, které jsou pro vás přijatelné. Poté bude tabulka automaticky přestavěna a její velikost bude výrazně zmenšena, takže bude mnohem snazší analyzovat data.

    Vytváření kontingenčních tabulek tedy není obtížné, pokud se nejprve procvičíte na jednoduché tabulce, dodržíte všechny požadavky a zadáte správná data. Jakmile si uživatel osvojí tyto dovednosti, rychle zažije výhody, protože analýza dat bude provedena v krátkém časovém období s absolutní přesností.

    S kontingenčními tabulkami Excelu musíte pracovat v různých oblastech. Můžete rychle zpracovávat velké množství informací, porovnávat a seskupovat data. To značně usnadňuje práci manažerů, obchodníků, vedoucích pracovníků, obchodníků, sociologů atd.

    Kontingenční tabulky umožňují rychle generovat různé sestavy o stejných datech. Kromě toho lze tyto sestavy flexibilně přizpůsobovat, upravovat, aktualizovat a podrobně propracovávat.

    Vytvořte sestavu pomocí Průvodce kontingenční tabulkou

    My máme tréninkový stůl s údaji:

    Každý řádek nám poskytuje komplexní informace o jedné transakci:

    • ve které prodejně se prodej uskutečnil;
    • jaké zboží a za jakou částku;
    • který z prodejců zkusil;
    • kdy (den, měsíc).

    Pokud se jedná o velký řetězec obchodů a prodeje jdou dobře, pak bude velikost stolu během jedné čtvrtiny děsivá. Bude velmi obtížné analyzovat data ve sto řádcích. A sestavení zprávy zabere více než jeden den. V takové situaci je kontingenční tabulka prostě nezbytná.

    Vytvořme sestavu pomocí Průvodce kontingenční tabulkou. V novém Excel verze Z nějakého důvodu je skrytý hluboko v nastavení:

    1. Vyberte "Soubor" - "Možnosti" - "Panel" rychlý přístup».
    2. V rozevíracím seznamu v levém sloupci: „Vyberte tým z“, vyberte „Všechny týmy“.
    3. V levém sloupci vyhledejte podle abecední pořadí a zvýrazněte: „Průvodce kontingenční tabulkou a grafem“. Klikněte na tlačítko mezi sloupci: „Přidat“, aby se nástroj přesunul do pravého sloupce a klikněte na OK.

    Nyní je nástroj na panelu rychlého přístupu, což znamená, že je vždy po ruce.

    1. Umístěte kurzor kamkoli do tabulky dat. Průvodce kontingenční tabulkou vyvoláme kliknutím na odpovídající nástroj, který se nyní nachází na panelu rychlého přístupu.
    2. V prvním kroku vybereme zdroj dat pro vytvoření kontingenční tabulky. Klikněte na „Další“. Chcete-li shromáždit informace do souhrnné zprávy z několika listů, vyberte: „v seznamu nebo databázi Data společnosti Microsoft Vynikat."
    3. Ve druhém kroku určíme rozsah dat, ze kterých bude sestava vycházet. Jelikož máme v tabulce kurzor, bude rozsah určen automaticky.
    4. Ve třetím kroku vás Excel vyzve k výběru umístění kontingenční tabulky. Klikněte na „Dokončit“ a rozvržení se otevře.
    5. Musíte určit pole, která se mají v přehledu zobrazit. Řekněme, že chceme znát prodejní částky pro každého prodejce. Zaškrtneme políčka a dostaneme:

    Hotovou zprávu lze formátovat a upravovat.

    Jak aktualizovat data v kontingenční tabulce Excel?

    To lze provést ručně i automaticky.

    1. Umístěte kurzor kamkoli do kontingenční tabulky. V důsledku toho se zobrazí karta „Práce s kontingenčními tabulkami“.
    2. V nabídce „Data“ klikněte na tlačítko „Aktualizovat“ (nebo na kombinaci kláves ALT+F5).
    3. Pokud potřebujete aktualizovat všechny přehledy v Excelový sešit, vyberte tlačítko „Aktualizovat vše“ (nebo kombinaci kláves CTRL+ALT+F5).

    Nastavení automatických aktualizací při změně dat:

    1. Na kartě „Práce s kontingenčními tabulkami“ (je třeba kliknout na sestavu) vyberte nabídku „Možnosti“.
    2. OTEVŘENO " Extra možnosti kontingenční tabulka." Otevře se průvodce.
    3. V části Data zaškrtněte políčko vedle položky Aktualizovat při otevírání souboru.

    Nyní pokaždé, když otevřete soubor se změněnými daty, automatická aktualizace kontingenční tabulka.

    Některá tajemství formátování

    Když ve zprávě shrnujeme velké množství dat, může být nutné seskupení k vyvození závěrů a přijetí některých rozhodnutí. Řekněme, že potřebujeme vidět výsledky za měsíc nebo čtvrtletí.

    Seskupení podle data v kontingenční tabulce Excel:

    1. Zdrojem informací je datová sestava.
    2. Protože potřebujeme seskupení podle data, vyberte libovolnou buňku s odpovídající hodnotou. Klikněte pravým tlačítkem myši.
    3. Z rozbalovací nabídky vyberte „Seskupení“. Otevře se nástroj jako tento:
    4. Do polí „Začátek“ a „Do“ Excel automaticky vyplnil počáteční a koncové datum rozsahu dat. Rozhodneme se pro krok seskupení. Pro náš příklad buď měsíce nebo čtvrtletí. Zaměřme se na měsíce.

    Dostáváme zprávu, která jasně ukazuje objemy prodejů podle měsíců. Pojďme experimentovat a nastavte krok na „Blocks“. Výsledkem je souhrnná tabulka jako:

    Pokud pro analýzu činnosti obchodního řetězce není důležité jméno prodejců, můžeme vygenerovat report se čtvrtletním ziskem.

    Chcete-li odstranit výsledky seskupení, musíte kliknout pravým tlačítkem na datovou buňku a kliknout na Oddělit seskupení. Nebo vyberte tuto možnost v nabídce „Struktura“.

    Práce s výsledky

    Máme souhrnnou zprávu takto:

    Výsledky můžete vidět podle měsíce (provádí seskupení) a podle názvů produktů. Udělejme zprávu pohodlnější ke studiu.

    Jak přidat součty na začátek kontingenční tabulky:

    1. „Práce s kontingenčními tabulkami“ - „Návrhář“.
    2. Na kartě „Rozvržení“ klikněte na „Mezisoučty“. Vyberte „Zobrazit všechny mezisoučty v záhlaví skupiny“.
    3. Výsledkem je následující typ zprávy:

    Již zde není ono přetížení, které znesnadňovalo vnímání informací.

    Jak odstranit mezisoučty? Stačí na kartě rozvržení vybrat „Nezobrazovat mezisoučty“:

    Obdržíme zprávu bez dalších částek:

    Podrobnosti informací

    Rozsáhlé souhrnné tabulky, které jsou sestavovány na základě „zahraničních“ tabulek, je třeba pravidelně rozvádět. Nevíme, odkud se ta částka v konkrétní částce vzala. Excel buňka. Můžete to ale zjistit, pokud rozdělíte kontingenční tabulku na několik listů.

    1. V březnu se prodaly manželské postele v hodnotě 23 780 Kč. Kde se tato postava vzala? Vyberte buňku s tímto množstvím a klikněte pravým tlačítkem a vyberte možnost:
    2. Na novém listu se otevře tabulka s údaji o prodeji produktů.

    Celou kontingenční tabulku můžeme přesunout na nový list výběrem tlačítka Přesunout na kartě Akce.

    Ve výchozím nastavení jsou do kontingenční tabulky umístěny úplně všechny informace ze sloupce, který přidáme do sestavy.

    V našem příkladu – VŠECHNY produkty, VŠECHNY termíny, VŠECHNY částky a obchody. Uživatel nemusí některé prvky potřebovat. Jednoduše zanesou přehled a zabrání vám soustředit se na to hlavní. Odstraňme nepotřebné prvky.

    1. Klikněte na šipku u názvu sloupce, kde upravíme množství informací.
    2. Z rozbalovací nabídky vyberte název pole. V našem příkladu se jedná o název nebo datum produktu. Zůstaneme u jména.
    3. Nastavte filtr podle hodnoty. Vynechme z přehledu informace o jednolůžkových postelích – zrušte zaškrtnutí u názvu produktu.

    Klikněte na OK – kontingenční tabulka se změní.

    Zpracování velkých objemů informací a vytváření složitých víceúrovňových reportů je bez použití automatizačních nástrojů poměrně obtížné. Excel 2010 je přesně tím nástrojem, který vám umožňuje zjednodušit tyto úkoly vytvořením kontingenčních (křížových) tabulek dat (kontingenční tabulka).

    Kontingenční tabulka v Excelu 2010 se používá pro:

    • identifikace vztahů ve velkém souboru dat;
    • seskupení dat podle různé znaky a sledování trendů ve skupinách;
    • hledání opakujících se prvků, detailování atd.;
    • vytváření snadno čitelných zpráv je nejdůležitější.

    Existují dva způsoby, jak vytvořit kontingenční tabulky. Podívejme se na každou z nich.

    Metoda 1: Vytvoření kontingenčních tabulek pomocí standardní Nástroj Excel 2010 „Kontingenční tabulka“

    Před vytvořením sestavy kontingenční tabulky se rozhodneme, co bude použito jako zdroj dat. Zvažme možnost se zdrojem umístěným ve stejném dokumentu.

    1. Začněte vytvořením jednoduché tabulky se seznamem prvků, které potřebujete ve své zprávě použít. Horní linie musí obsahovat záhlaví sloupců.

    2. Otevřete kartu „Vložit“ a v části „Tabulky“ vyberte nástroj „Kontingenční tabulka“.

    Pokud potřebujete vytvořit kontingenční graf spolu s kontingenční tabulkou, klikněte na šipku v pravém dolním rohu ikony „Pivot Table“ a vyberte „Pivot Chart“.

    3. V dialogovém okně Nová kontingenční tabulka, které se otevře, vyberte nově vytvořenou datovou tabulku nebo její rozsah. Chcete-li to provést, vyberte požadovanou oblast.

    Jako data pro analýzu můžete zadat externí zdroj: vyberte přepínač v příslušném poli a vyberte požadované připojení ze seznamu dostupných.

    5. Po potvrzení akce kliknutím na tlačítko „OK“ se vytvoří a otevře rozvržení zprávy. Zvažme to.

    V pravé polovině okna je vytvořen panel hlavních nástrojů pro správu – „Seznam polí kontingenční tabulky“. Všechna pole (záhlaví sloupců v tabulce zdrojových dat) budou uvedena v části „Vyberte pole, která chcete přidat do přehledu“. Zaškrtněte požadovaná políčka a vytvoří se sestava kontingenční tabulky s vybranými poli.

    Umístění polí lze ovládat - pojmenovat je řádky nebo sloupce přetažením do odpovídajících oken a také je třídit v pohodlná objednávka. Jednotlivé položky můžete filtrovat přetažením příslušného pole do okna Filtr. Okno „Hodnota“ obsahuje pole, pro které se provádějí výpočty a výsledky se sčítají.

    Další možnosti úprav sestav jsou k dispozici v nabídce Nástroje kontingenční tabulky na kartách Možnosti a Návrh. Téměř každý z nástrojů na těchto kartách má spoustu nastavení a doplňkových funkcí.

    Metoda 2: Vytvoření kontingenční tabulky pomocí nástroje Průvodce kontingenční tabulkou a grafem

    Chcete-li použít tuto metodu, budete muset udělat přístupný nástroj, která se ve výchozím nastavení na pásu karet nezobrazuje. Otevřete kartu "Soubor" - "Možnosti" - "Panel nástrojů Rychlý přístup". V seznamu Vybrat příkazy z vyberte příkazy na pásu karet. A níže ze seznamu příkazů vyberte „Průvodce kontingenční tabulkou a grafem“. Klikněte na tlačítko "Přidat". V horní části panelu rychlého přístupu se zobrazí ikona průvodce.

    Průvodce kontingenční tabulkou v Excelu 2010 se příliš neliší od podobného nástroje v Excelu 2007. Chcete-li pomocí něj vytvořit kontingenční tabulky, postupujte následovně.

    1. Klepněte na ikonu průvodce na panelu Rychlý přístup. V dialogovém okně vyberte požadovanou položku v seznamu zdrojů dat:

    • "v seznamu nebo databázi Microsoft Excel" - zdrojem bude databáze pracovních listů, pokud existuje;
    • „v externím zdroji dat“ - pokud existuje připojení k externí databázi, kterou bude třeba vybrat z dostupných;
    • „v několika rozsazích konsolidace“ – pokud je požadováno kombinování údajů z různých zdrojů;
    • „data v jiné kontingenční tabulce nebo kontingenčním grafu“ – jako zdroj se bere existující kontingenční tabulka nebo graf.

    2. Poté vyberte typ sestavy, kterou chcete vytvořit – „kontingenční tabulka“ nebo „kontingenční graf (s tabulkou)“.

    • Pokud je jako zdroj vybrán aktuální dokument, kde již existuje jednoduchý stůl s prvky budoucí zprávy nastavte rozsah pokrytí - kurzorem zvýrazněte požadovanou oblast. Dále vyberte, kam chcete tabulku umístit – na nový list nebo na aktuální list, a klikněte na „Dokončit“. Bude vytvořena kontingenční tabulka.
    • Pokud potřebujete konsolidovat data z několika zdrojů, vyberte přepínač v příslušné oblasti a vyberte typ sestavy. A pak budete muset uvést, jak vytvořit pole stránky budoucí kontingenční tabulky: jedno pole nebo několik polí.

    Když vyberete „Vytvořit pole stránky“, nejprve budete muset určit rozsahy zdrojů dat: vyberte první rozsah, klikněte na „Přidat“, poté další atd.

    Pro usnadnění mohou být rozsahy pojmenovány. Chcete-li to provést, vyberte jedno z nich v seznamu a zadejte počet polí stránky, která pro něj chcete vytvořit, a poté každému poli dejte název (štítek). Poté vyberte další rozsah a tak dále.

    • Při výběru vnější zdroj použitá data aplikace Microsoft Dotaz zahrnutý v Excelu 2010 nebo, pokud se chcete připojit k datům Office, použijte možnosti karty Data.
    • Pokud dokument již obsahuje sestavu kontingenční tabulky nebo kontingenční graf, můžete je použít jako zdroj. Chcete-li to provést, stačí zadat jejich umístění a vybrat požadovaný rozsah dat, po kterém se vytvoří nová kontingenční tabulka.

    Líbil se vám materiál?
    Podíl:

    Prosím ohodnoťte:

    z 5 hodnocení:

    Vraťte se na začátek článku Jak vytvořit kontingenční tabulku v Excelu 2010

    Nyní začněme workshopem, který vám poskytne první zkušenosti se zpracováním dat v PowerPivotu. Tento příklad vytvoří sestavu založenou na 1,8 milionu řádků souboru CSV obsahujícího data skladu. Ve skutečnosti může být počet záznamů neomezený (na rozdíl od Excelu, který umožňuje až 1 048 000 záznamů).

    Původní tabulka obsahuje 1,8 milionu záznamů umístěných v souboru CSV s názvem demo.csv. Část obsahu tohoto souboru, zobrazená v okně programu Poznámkový blok, je znázorněna na Obr. 10.2. Všimněte si, že záhlaví sloupců jsou na prvním řádku souboru CSV. Upozorňujeme, že možná budete muset odebrat nestandardní řádky v horní části ukázkového souboru, abyste předešli problémům při jejich vykreslování doplňkem PowerPivot.

    Rýže. 10.2. Tento soubor s 1,8 miliony řádků je pro Excel příliš velký

    Importujte textový soubor

    Chcete-li importovat soubor obsahující 1,8 milionu řádků do PowerPivot, postupujte takto:

    1. V okně Excelu 2010 vyberte kartu PowerPivot.
    2. Klikněte na ikonu PowerPivot Window. Na obrazovce se objeví okno aplikace PowerPivot. včetně dvou záložek: Home (Home) a Project (Design). Karta Domů je znázorněna na Obr. 10.3.
    3. Nejprve musíte importovat hlavní tabulku. Tato tabulka je vytvořena z velkého souboru CSV zobrazeného na obrázku. 10.2. Ve skupině příkazů Získat externí data klikněte na tlačítko Z textu. Na obrazovce se objeví dialogové okno Průvodce importem tabulky.
    4. Do pole Název přátelského připojení zadejte název připojení, například Historie prodeje. Klepněte na tlačítko Procházet a vyhledejte textový soubor. PowerPivot ve výchozím nastavení nepoužívá obsah prvního řádku jako záhlaví sloupců, takže během procesu náhledu se názvy sloupců zobrazují v následujícím matoucím formátu: Fl, F2, F3 atd. (obr. 10.4).
    5. Ujistěte se, že je jako oddělovač vybrán středník. V rozevíracím seznamu Oddělovač sloupců se zobrazuje řada standardních oddělovačů, jako je čárka, středník, svislá čára a tak dále.
    6. Zaškrtněte políčko Použít první řádek jako záhlaví sloupců. V okně náhledu se zobrazí skutečná záhlaví sloupců.
    7. Pokud chcete odmítnout import sloupce, zrušte zaškrtnutí příslušných políček. Textový soubor připraven k načtení do paměti RAM. Vezměte prosím na vědomí, že můžete výrazně snížit množství použitého paměť s náhodným přístupem pokud vyloučíte další sloupce, zejména pokud obsahují dlouhé textové hodnoty. Na Obr. Obrázek 10.5 ukazuje, jak data vypadají, když není zaškrtávací políčko zaškrtnuté.
    8. Po kliknutí na tlačítko Dokončit PowerPivot začne načítat soubor do paměti. Zobrazí se počet načtených řádků v současné doběčas (obr. 10.6).
    9. Po dokončení importu souboru se zobrazí počet načtených řádků - 1,8 mil. záznamů (obr. 10.7). Kliknutím na tlačítko Zavřít se vrátíte do okna PowerPivot.
    10. Okno PowerPivot zobrazuje 1,8 milionu záznamů. K jejich zobrazení můžete použít svislý pruh svitek. Můžete také třídit, měnit formát čísel nebo použít filtr (obrázek 10.8).

    Ahoj všichni! Dnešní materiál je pro ty, kteří nadále ovládají práci s aplikačními programy a nevědí, jak vytvořit kontingenční tabulku v Excelu.

    Po vytvoření obecné tabulky v kterémkoli z textové dokumenty, můžete jej analyzovat vytvořením kontingenčních tabulek v Excelu.

    Vytvoření kontingenční tabulky Excel vyžaduje splnění určitých podmínek:

    1. Data se vejdou do tabulky se sloupci a seznamy s názvy.
    2. Žádné nevyplněné formuláře.
    3. Žádné skryté předměty.

    Jak vytvořit kontingenční tabulku v Excelu: pokyny krok za krokem

    K vytvoření kontingenční tabulky potřebujete:

    Vytvořeno prázdná stránka, kde vidíte seznamy oblastí a oborů. Ze záhlaví se stala pole v naší nové tabulce. Kontingenční tabulka bude tvořena přetažením polí.

    Budou označeny zaškrtnutím a pro snazší analýzu je zaměníte v oblastech tabulky.

    Rozhodl jsem se, že analýzu dat provedu přes filtr podle prodejce, aby bylo jasné, kdo a za jakou částku každý měsíc prodal a jaký produkt.

    Vybíráme konkrétního prodejce. Podržte myš a přesuňte pole „Prodejce“ do „Filtru přehledů“. Nové pole se zaškrtne a pohled na tabulku se mírně změní.

    Pro zobrazení rozevíracího seznamu je důležité, v jakém pořadí název zadáme. Pokud se nejprve rozhodneme ve prospěch produktu v řádcích a poté uvedeme cenu, budou produkty v rozevíracím seznamu a naopak.

    Sloupec „Jednotky“ v hlavní tabulce zobrazoval množství zboží prodaného určitým prodejcem za konkrétní cenu.

    Chcete-li například zobrazit prodeje za každý měsíc, musíte pole „Datum“ nahradit „Názvy sloupců“. Kliknutím na datum vyberte příkaz "Skupina".

    Zadejte období data a krok. Potvrďte svou volbu.

    Vidíme takovou tabulku.

    Přesuňme pole "Částka" do oblasti "Hodnoty".

    Zobrazení čísel se stalo viditelným, ale potřebujeme formát čísel

    Chcete-li to opravit, vyberte buňky vyvoláním okna myší a výběrem „Formát čísel“.

    Vybereme formát čísla pro další okno a zaškrtneme „Oddělovač skupiny číslic“. Potvrďte tlačítkem „OK“.

    Návrh kontingenční tabulky

    Pokud zaškrtneme políčko, které potvrzuje výběr více objektů najednou, budeme moci zpracovávat data pro více prodejců najednou.

    Filtr lze použít na sloupce a řádky. Zaškrtnutím políčka u jednoho z typů produktů můžete zjistit, kolik toho prodal jeden nebo více prodejců.

    Parametry pole se také konfigurují samostatně. Na příkladu vidíme, že jistý romský prodejce v konkrétním měsíci prodával košile za konkrétní částku. Kliknutím myši v řádku „Součet podle pole...“ vyvoláme nabídku a vybereme „Parametry hodnotového pole“.

    Podívejte se na tabulku. Z toho jasně vyplývá, že za jeden měsíc prodejce prodal 2 košile.

    Nyní změníme tabulku a necháme filtr fungovat po měsících. Pole „Datum“ přeneseme do „Filtru přehledů“ a tam, kde je „Názvy sloupců“, bude „Prodejce“. Tabulka zobrazuje celé prodejní období nebo za konkrétní měsíc.

    Výběr buněk v kontingenční tabulce způsobí, že se zobrazí karta s názvem „Práce s kontingenčními tabulkami“ a budou zde další dvě karty „Možnosti“ a „Návrhář“.

    Ve skutečnosti se o nastavení kontingenčních tabulek dá mluvit velmi dlouho. Proveďte změny podle svého vkusu a dosáhněte tak pohodlného použití. Nebojte se tlačit a experimentovat. Jakoukoli akci můžete kdykoli změnit stisknutím kombinace kláves Ctrl+Z.

    Doufám, že jste se naučili veškerý materiál a nyní víte, jak vytvořit kontingenční tabulku v Excelu.

    souhrn Excel tabulky– jedná se o speciální typ konstrukce, která předpokládá přítomnost funkce pro okamžité generování zprávy o dokumentu.

    S jejich pomocí můžete snadno shrnout některá data stejného typu.

    V Excel program 2007 (MS Excel 2010|2013) kontingenční tabulka slouží především k sestavení matematické nebo ekonomické analýzy dat.

    Jak vytvořit kontingenční tabulku v Excelu

    Analýza dat dokumentů přispívá k rychlejšímu a správnějšímu řešení zadaných úkolů.

    Tabulkový procesor Excel zvládne i ty nejobjemnější a nejsložitější dokumenty. Sestavení reportu a sumarizace dat není pro program nijak složité.

    Chcete-li vytvořit jednoduchou souhrnnou tabulku samotnou, postupujte podle následujících pokynů:

    • Pomocí záložky „vložit“, která se nachází v hlavní nabídce panelu nástrojů programu, vyberte tlačítko pro vytvoření doporučených kontingenčních tabulek;

    Výběr vkládacího okna pro další tvorbu

    • V dialogovém okně programu, které se otevře, klikněte na rámeček znaku, který se vám líbí. Tím se rozvržení zpřístupní pro náhled uživatele.
      Vyberte nejvhodnější možnost, která dokáže plně zobrazit všechny informace;

    Rada! Další rozvržení kontingenční tabulky lze stáhnout z oficiálního webu společnosti Microsoft.

    Dialogové okno pro vytváření a prohlížení náhledů

    • Stiskněte OK a program okamžitě přidá vybranou tabulku (nebo prázdné rozvržení) na otevřený list dokumentu. Program také automaticky určí pořadí řádků podle poskytnutých informací;
    • Chcete-li vybrat prvky tabulky a uspořádat je ručně, seřaďte obsah. Data lze také filtrovat. Dashboard je v podstatě prototypem malé databáze.
      Filtrace je nezbytná v případě potřeby rychlý pohled pouze určité sloupce a řádky. Níže je uveden příklad kontingenční tabulky prodeje po filtrování obsahu.
      Rychle si tak můžete prohlédnout objemy prodejů v jednotlivých regionech (v našem případě Západ a Jih);

    Příklad filtrované tabulky

    Je vhodné použít již vytvořené rozvržení, uživatel však může vybrat prázdnou tabulku a samostatně ji vyplnit.

    V prázdná šablona musíte přidat pole, kalkulační vzorce, filtry.

    Prázdný formulář se vyplní přetažením požadovaných datových prvků do samostatných oblastí.

    Formulář pro vytvoření osobní souhrnné tabulky uživatelů

    Můžete také vytvořit propojené souhrnné tabulky na více listech dokumentu současně.

    Tímto způsobem můžete analyzovat data celého dokumentu nebo několika dokumentů/listů najednou.

    Můžete také analyzovat externí data pomocí kontingenčních tabulek.

    • Makra v Excelu - Návod k použití
    • Rozbalovací seznam v Excelu – Pokyny k vytvoření
    • Jak zmrazit řádek v Excelu - Podrobné pokyny.

    Není to poprvé, co je Renat hostujícím autorem na Lifehackeru. Již dříve jsme od něj publikovali vynikající materiál o tom, jak vytvořit tréninkový plán: a online zdroje, stejně jako vytvoření tréninkového plánu.

    Tento článek obsahuje jednoduché techniky, které umožňují... Budou užitečné zejména pro ty, kteří se zabývají manažerským reportingem, připravují různé analytické reporty na základě stahování z 1C a dalších reportů a vytvářejí z nich prezentace a diagramy pro management. Netvrdím absolutní novinku – v té či oné podobě se o těchto technikách pravděpodobně diskutovalo na fórech nebo.

    Jednoduché alternativy k VLOOKUP a HLOOKUP, pokud hledané hodnoty nejsou v prvním sloupci tabulky: LOOKUP, INDEX+MATCH

    Výsledek:

    V v tomto případě, kromě funkce CHAR (pro zobrazení uvozovek) je použita funkce IF, která umožňuje měnit text podle toho, zda je pozitivní trend prodeje, a funkce TEXT, která umožňuje zobrazit číslo v libovolném formát. Jeho syntaxe je popsána níže:

    TEXT ( význam; formát)

    Formát je zadán v uvozovkách stejně, jako kdybyste zadávali vlastní formát v okně Formát buněk.

    Složitější texty lze také automatizovat. V mé praxi docházelo k automatizaci dlouhých, ale rutinních komentářů manažerské výkaznictví ve formátu „INDIKÁTOR poklesl/zvýšil o XX oproti plánu především z důvodu zvýšení/snížení FAKTORU 1 o XX, zvýšení/snížení FAKTORU 2 o YY...“ s měnícím se seznamem faktorů. Pokud takové komentáře píšete často a proces jejich psaní lze algoritmizovat, měli byste jednou zkusit vytvořit vzorec nebo makro, které vám ušetří alespoň část práce.

    Jak uložit data v každé buňce po sloučení

    Při slučování buněk zůstane zachována pouze jedna hodnota. Excel vás na to upozorní, když se pokusíte sloučit buňky:

    Pokud byste tedy měli vzorec závislý na každé buňce, přestal by fungovat po jejich spojení (chyba #N/A na řádcích 3–4 příkladu):

    Chcete-li sloučit buňky a přesto zachovat data v každé z nich (možná máte vzorec, jako v tomto abstraktním příkladu; možná chcete sloučit buňky, ale uložit všechna data pro budoucnost nebo je záměrně skrýt), sloučte všechny buňky v list , vyberte je a poté pomocí příkazu Format Painter přeneste formátování do buněk, které potřebujete sloučit:

    Jak vytvořit souhrn z více zdrojů dat

    Pokud potřebujete vytvořit kontingenční tabulku z několika zdrojů dat najednou, budete muset přidat „Průvodce kontingenční tabulkou a grafem“ na pás karet nebo panel rychlého přístupu, který tuto možnost má.

    Můžete to udělat následovně: „Soubor“ → „Možnosti“ → „Panel nástrojů Rychlý přístup“ → „Všechny příkazy“ → „Průvodce kontingenční tabulkou a grafem“ → „Přidat“:

    Poté se na pásu karet objeví odpovídající ikona, kliknutím na ni vyvoláte stejného průvodce:

    Když na něj kliknete, objeví se dialogové okno:

    V něm musíte vybrat položku „V několika rozsazích konsolidace“ a kliknout na „Další“. V dalším kroku můžete vybrat „Vytvořit jedno stránkové pole“ nebo „Vytvořit pole stránky“. Pokud chcete vymyslet název pro každý zdroj dat sami, vyberte druhou možnost:

    V dalším okně přidejte všechny rozsahy, na jejichž základě bude souhrn vytvořen, a pojmenujte je:

    Poté v posledním dialogovém okně určete, kam bude sestava kontingenční tabulky umístěna – na existující nebo nový list:

    Sestava kontingenční tabulky je připravena. Ve filtru „Stránka 1“ můžete v případě potřeby vybrat pouze jeden ze zdrojů dat:

    Jak vypočítat počet výskytů textu A v textu B („MTS tarif SuperMTS“ - dva výskyty zkratky MTS)

    V v tomto příkladu Ve sloupci A je několik textových řádků a naším úkolem je zjistit, kolikrát se v každém z nich objeví hledaný text umístěný v buňce E1:

    Chcete-li tento problém vyřešit, můžete použít složitý vzorec skládající se z následujících funkcí:

    1. LEN - vypočítá délku textu, jediným argumentem je text. Příklad: DLTR („auto“) = 6.
    2. SUBSTITUTE - nahradí určitý text jiným v textovém řetězci. Syntaxe: SUBSTITUTE ( text; starý_text; nový_text). Příklad: SUBSTITUTE ("auto";"auto";"")= "mobil".
    3. UPPER - nahradí všechny znaky v řetězci velkými. Jediným argumentem je text. Příklad: KAPITÁLNÍ (“stroj”) = “STROJ”. Tuto funkci budeme potřebovat k vyhledávání bez ohledu na velikost písmen. Koneckonců, UPPER(“stroj”) = UPPER(”Stroj”)

    Chcete-li najít výskyt určitého textového řetězce v jiném, musíte odstranit všechny jeho výskyty v původním a porovnat délku výsledného řetězce s původním:

    DLstr(“Tarif MTS Super MTS“) – DLST („Supertarif“) = 6

    A pak vydělte tento rozdíl délkou řetězce, který jsme hledali:

    6 / LISTR („MTS“) = 2

    V původním je zahrnut přesně dvojnásobek řádku „MTS“.

    Zbývá napsat tento algoritmus v jazyce vzorců (textem označujeme text, ve kterém hledáme výskyty, a „hledat“ tím, jehož počet výskytů nás zajímá):

    =(DÉLKA( text)-DÉLKA(NAHRADIT(HORNÍ( text);VELKÁ PÍSMENA( hledaný);"")))/DÉLKA( hledaný)

    V našem příkladu vypadá vzorec takto:

    =(DÉLKA(A2)-DÉLKA(NAHRADIT(VELKÁ PÍSMENA(A2),VELKÁ PÍSMENA($E$1),“”)))/DÉLKA($E$1)

    Kontingenční tabulka je efektivní nástroj vypočítat, shrnout a analyzovat data, což usnadňuje hledání srovnání, vzorů a trendů.

    Nastavení kontingenční tabulky

    Příprava

    Pokud nemáte dostatek zkušeností s kontingenčními tabulkami nebo nevíte, kde začít, je nejlepší použít doporučená kontingenční tabulka. Při použití tohoto Excel funkce určuje vhodné rozvržení přiřazováním dat k nejlépe vyhovujícím oblastem v kontingenční tabulce. To poskytuje výchozí bod pro další experimenty. Po vytvoření doporučené kontingenční tabulky můžete prozkoumat různé orientace a změnit uspořádání polí, abyste získali požadované výsledky.

    V oblasti Název pole linky sloupců hodnoty

    Hodnoty v kontingenční tabulce


    Aktualizace kontingenčních tabulek

    Když do zdroje přidáte nová data, musíte aktualizovat všechny kontingenční tabulky, které jsou z něj vytvořeny. Chcete-li aktualizovat jednu kontingenční tabulku, můžete klikněte pravým tlačítkem myši kdekoli v jeho rozsahu a vyberte příkaz Aktualizace. Pokud máte více kontingenčních tabulek, nejprve vyberte libovolnou buňku v jakékoli kontingenční tabulce a poté vyberte páska otevřete kartu Analýza kontingenční tabulky, klikněte na šipku pod tlačítkem Aktualizace a vyberte tým Aktualizovat vše.

    Odstranit kontingenční tabulku

    Pokud již kontingenční tabulku nepotřebujete, jednoduše ji vyberte a stiskněte VYMAZAT. To neovlivní ostatní data a kontingenční tabulky nebo grafy. Pokud je kontingenční tabulka na samostatný list tam, kde již nepotřebujete data, která potřebujete, můžete tento list jednoduše smazat. Toto je nejjednodušší způsob, jak se zbavit kontingenční tabulky.

    Kontingenční tabulku lze nyní vložit do tabulky v Excelu Online.


    Práce se seznamem polí kontingenční tabulky

    V oblasti Pole kontingenční tabulky Nahoře zaškrtněte políčka u polí, která chcete přidat do kontingenční tabulky. Ve výchozím nastavení se do oboru přidávají nenumerická pole linky, pole hodnoty data a času - do oblasti sloupců a číselná pole - do oblasti hodnoty. Položky můžete také ručně přetáhnout do libovolného pole kontingenční tabulky. Pokud již nějakou položku nepotřebujete, jednoduše ji přetáhněte ze seznamu polí nebo zrušte zaškrtnutí jejího políčka. Možnost přeskupování prvků je jednou z vlastností kontingenčního stolu, díky kterému můžete rychle a snadno změnit jeho vzhled.

    Práce s hodnotami kontingenční tabulky

    Aktualizace kontingenčních tabulek

    Když do zdroje přidáte nová data, musíte na jejich základě aktualizovat všechny kontingenční tabulky. Chcete-li kontingenční tabulku obnovit, můžete kliknout pravým tlačítkem kamkoli v jejím rozsahu a vybrat Aktualizace


    Odstranit kontingenční tabulku

    Pokud již kontingenční tabulku nepotřebujete, jednoduše ji vyberte a stiskněte DELETE. To neovlivní ostatní data a kontingenční tabulky nebo grafy. Pokud je kontingenční tabulka na samostatném listu, který již neobsahuje data, která potřebujete, můžete tento list jednoduše odstranit. Toto je nejjednodušší způsob, jak se zbavit kontingenční tabulky.

    dodatečné informace

    Vždy můžete položit otázku specialistovi z komunity Excel Tech Community, požádat o pomoc v komunitě odpovědí a také navrhnout nová vlastnost nebo vylepšení webu

    K analýze velkých a složitých tabulek obvykle používají . Vzorce lze také použít k seskupování a analýze existujících dat. Vytvářejme jednoduché sestavy pomocí vzorců.

    Budeme používat ( Vložit/Tabulky/Tabulka), obsahující informace o prodeji šarží výrobku. Řádky tabulky poskytují údaje o dodávce šarže produktu a jeho prodeji. V článku byla použita podobná tabulka .

    Tabulka má následující sloupce:

    • Produkt– název šarže zboží, např. Pomeranče»;
    • Skupina– skupina produktů, například „ Pomeranče» jsou součástí skupiny « Ovoce»;
    • termín dodání– datum dodání Zboží Dodavatelem;
    • Oblast prodeje– Region, ve kterém byla šarže Zboží prodána;
    • Odbyt– Náklady, za které bylo možné dávku Zboží prodat;
    • Odbyt– doba skutečného prodeje Zboží v Regionu (ve dnech);
    • Zisk– poznámku, zda byl z prodané šarže Zboží vytvořen zisk.

    Přes Správce jmen Upravme tabulky na " Source_table"(viz ukázkový soubor).

    Pomocí vzorců vytvoříme 5 jednoduchých sestav, které umístíme na samostatné listy.

    Zpráva č. 1 Celkový prodej Produktů

    Pojďme zjistit celkový prodej každého produktu.
    Problém lze celkem jednoduše vyřešit pomocí funkce SUMIF(), ale samotná tvorba sestavy vyžaduje určité dovednosti práce s některými nástroji EXCELu.

    Pojďme tedy začít. Nejprve musíme vytvořit seznam názvů Produktů. Protože Ve sloupci Produkt zdrojové tabulky se názvy opakují, pak z něj musíme vybrat pouze hodnoty. To lze provést několika způsoby: pomocí vzorců (viz článek), prostřednictvím nabídky Data/Práce s daty/Odstranit duplikáty nebo pomocí . Pokud použijete první metodu, pak při přidávání nových produktů do zdrojové tabulky budou nové názvy zahrnuty do seznamu automaticky. Zde však pro jednoduchost použijeme druhý způsob. Pro tohle:

    • Přejděte na list s původní tabulkou;
    • Volání ( Data/ Sort & Filter/ Advanced);
    • Vyplňte pole podle obrázku níže: nastavte přepínač do polohy Zkopírujte výsledek na jiné místo; do pole Zdrojový rozsah zadejte $A$4:$A$530; Zaškrtněte políčko Pouze jedinečné položky.

    • Výsledný seznam zkopírujte na list, na kterém bude sestava umístěna;
    • Seřadit seznam produktů ( Data/ Třídění a filtrování/ Třídění od A do Z).

    Měli byste získat následující seznam.

    V buňce B6 Zadejte následující vzorec a zkopírujte jej na konec seznamu:

    SUMIF(tabulka_zdroje[produkt];A6;tabulka_zdroje[prodej])

    COUNTIF(původní_tabulka[produkt],A6)

    Zpráva č. 2 Prodej produktů podle krajů

    Pojďme zjistit celkový prodej každého produktu v regionech.
    Použijme seznam Produktů vytvořený pro Report č. 1. Obdobným způsobem získáme seznam názvů Regionů (do pole Počáteční rozsah zadejte $D$4:$D$530).
    Zkopírujte výsledný vertikální rozsah do Schránka a to vodorovně. Umístěte výsledný rozsah obsahující názvy oblastí do záhlaví sestavy.

    V buňce B 8 Zadáme následující vzorec:

    SUMIFS(Původní_tabulka[Prodej];
    Source_Table[Produkt];$A8;
    Source_Table[Prodejní oblast]; 7 $)

    Vzorec vrátí celkový prodej Produktu, jehož název je umístěn v buňce A8 , v Oblasti z buňky V 7 . Pozor na použití (odkazy $A8 a B$7), budete ho potřebovat při kopírování vzorce pro zbývající prázdné buňky tabulky.

    Výše uvedený vzorec nebude možné zkopírovat do buněk vpravo pomocí (toto bylo provedeno pro sestavu č. 1), protože v tomto případě v cele C8 vzorec bude vypadat takto:

    SUMIFS(Původní_tabulka[Prodej, dny];
    Source_Table[Group];$A8;
    Source_Table[Sales];7 C$)

    Zpráva č. 3 Filtrování produktů podle ziskovosti

    Vraťme se k původní tabulce. Každá šarže Zboží buď dosáhla zisku, nebo ne (viz sloupec Zisk v původní tabulce). Pojďme vypočítat tržby podle produktové skupiny v závislosti na ziskovosti. K tomu budeme filtrovat záznamy zdrojové tabulky podle pole Profit pomocí vzorců.

    FREQUENCY(Source_Table[Prodeje, dny];A7:A12)

    Chcete-li zadat vzorec, vyberte rozsah C6:C12 , pak dovnitř Formulář bar zadejte výše uvedený vzorec a klikněte CTRL+ POSUN+ ENTER.

    Stejný výsledek lze získat pomocí běžné funkce SUMPRODUCT():
    =SUMPRODUCT((Original_Table[Prodeje, dny]>A6)*
    (Source_Table[Prodeje, dny]<=A7))

    Výkaz č. 5 Statistika zásobování zbožím

    Nyní připravíme zprávu o dodávce Zboží za měsíc.
    Nejprve si vytvoříme seznam měsíců po roce. V původní tabulce je nejdříve datum dodání 7.11.2009. Můžete to vypočítat pomocí vzorce:
    =MIN(Zdrojová_tabulka[Datum doručení])

    Vytvořme seznam termínů – počínaje nejbližším datem doručení. K tomu použijeme vzorec:
    =MONTH($C$5,-1)+1

    V důsledku toho získáme seznam dat - první dny v měsících:

    Použitím vhodného formátu buněk změňme zobrazení dat:

    Vzorec pro výpočet počtu dodaných dávek Zboží za měsíc:

    SUMPRODUCT((Původní_tabulka[Datum doručení]>=B9)*
    (Zdrojová_tabulka[Datum doručení]

    Nyní přidáme řádky pro výpočet celkového počtu dávek pro každý rok. Abychom to udělali, trochu pozměňme tabulku a zvýrazněme rok, ve kterém byla dodávka provedena, v samostatném sloupci pomocí funkce YEAR().

    Nyní, pro zobrazení podle roku, vytvoříme strukturu pomocí položky nabídky:

    • Vyberte libovolnou buňku upravené tabulky;
    • Vyvolejte okno pomocí položky nabídky Data/Struktura/Mezisoučty;
    • Vyplňte pole, jak je znázorněno na obrázku:

    Po kliknutí na OK se tabulka změní následovně:

    Budou vytvořeny mezisoučty podle roku.
    Kliknutím na malá tlačítka v levém horním rohu listu můžete ovládat zobrazení dat v tabulce.

    Souhrn :

    Zprávy podobné těm vytvořeným lze přirozeně vytvářet pomocí nebo pomocí Filtr do zdrojové tabulky nebo pomocí jiných funkcí BDSUMM(), BIZULT(), BCOUNT() atd. Volba přístupu závisí na konkrétní situaci.

    Přečtěte si více: