Úvodní stránka Excel(entní) taháky Vzorová data
 

Excel - Kontingenční tabulky - transformace(1)

K tomuto návodu si můžete stáhnout excelovský soubor s daty a řešením.

Kontingenční tabulky - transformace(1)

1. Transformace dat

  • Kontingenční tabulky jsou nejen mimořádně užitečným nástrojem pro analýzu numerických dat, ale lze jejich schopností úspěšně využít i pro jinak velmi obtížně dosažitelné transformace tabulek.

  • Jednou z častých úloh je "přeskupení" databáze do podoby, kterou zobrazuje obrázek napravo.

  • Nezáleží na tom, zda pracujete v malé firmě nebo velké korporaci - lze očekávat, že data máte uložena v databázových souborech, ze kterých požadované informace získáváte (nebo někdo jiný je získává) pomocí SQL dotazů. Výstupem jsou tabulky, které zachovávají databázovou strukturu, tj. každý řádek představuje individuální záznam a každý sloupec obsahuje veličinu společnou všem záznamům.

  • Tato struktura je vhodná pro analýzu dat, vazeb a souvislostí - jsou však situace, kdy požadujeme vytvoření přehledu podle druhého obrázku.
    Může jít například o zobrazení sestavy pro:
    1. výrobky a jejich jednotlivé varianty
    2. smlouvy a dodatky
    3. účty hlavní knihy a analytické účty
    4. faktury a jednotlivé položky
    5. kanceláře a položky inventáře

excel_kontingenční_tabulka_1

excel_kontingenční_tabulka_2


2. Vytvoření kontingenční tabulky

  • Nad daty vytvoříme kontingenční tabulku:
    • položku "Dodávka" přeneseme do řádkového pole
    • položku "Subdodávka" přeneseme do sloupcového pole
    • položku "Subdodávka" přeneseme také do datové oblasti a necháme Excel spočítat v kontingenční tabulce počet hodnot (případně součet - výsledné číslo má pouze pomocný význam)
        .


excel_kontingenční_tabulka_3

  • Výsledná kontingenční tabulka nám udává počet subdodávek ke každé dodávce.
      .


excel_kontingenční_tabulka_4

3. Vytvoření pomocné tabulky

  • Vložíme nový list a do něho zkopírujeme popis řádků a sloupců z kontingenční tabulky.

  • V pomocné tabulce nahradíme počty subdodávek spočtené v kontingenční tabulce kódy subdodávek.

  • Nahrazení dosáhneme pomocí vzorce =KDYŽ(kont!B5<>"";kont!B$4;""), který zapíšeme do první buňky přehledu (zde B5) a zkopírujeme do celé oblasti.
Poznámka:

Vzorec obsahuje smíšené formátování, které zajišťuje, že i při jeho kopírování do dalších buněk oblasti se bude stále odkazovat na 4. řádek obsahující kódy subdodávek.
=KDYŽ(kont!B5<>"";kont!B$4;"")
excel_kontingenční_tabulka_5


4. Uložení pomocné tabulky

Další krok může být považován za neobvyklý, ale výrazně nám dopomůže dosažení výsledku.

Uložíme list s pomocnou tabulkou jako textový soubor s položkami oddělenými tabulátory.

excel_kontingenční_tabulka_6
  • Potvrdíme informaci Excelu o tom, že ve formátu .TXT může uložit pouze aktivní list.

  • Můžeme potvrdit i vynechání formátovacích pravidel.

excel_kontingenční_tabulka_7

excel_kontingenční_tabulka_8

5. Import textového souboru

Opět načteme textový soubor, který jsme v předchozím kroku uložili.

Cílem tohoto postupu je odstranit prázdné buňky z pomocné tabulky.

Důležité je ve druhém kroku Průvodce importem textu aktivovat volbu "Posloupnost oddělovačů jako jeden".
excel_kontingenční_tabulka_9

excel_kontingenční_tabulka_10

excel_kontingenční_tabulka_11


6. Výsledná podoba transformované tabulky

Výslednou podobu dat můžeme ještě seřadit podle počtu variant - funkce POČET2 nám pomůže určit jejich počet a pak tabulku seřadíme sestupně podle těchto hodnot.
excel_kontingenční_tabulka_12


Úvodní stránka Excel(entní) taháky Vzorová data

 

 

Klíčová slova - Microsoft Excel,Dataspectrum,Jiří Číhař, Excel tipy, Excel,kontingenční tabulka,pivot table,transformace,průvodce,návod