S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Následující článek může někomu pomoci, ale celkově je už zastaralý a zbytečně komplikovaný. 

Doporučil bych spíše odebrat duplicity v Power Query, které bude mezi zdrojem a kontingenční tabulkou, nebo datový model (Power Pivot). Kontingenční tabulka vzniklá z datového modelu umí unikátní hodnoty "sama od sebe".

Více o unikátních hodnotách v Excelu, jejich počítání a zjišťování, v tomto článku.

 

 

Původní článek:

V jednom reportu jsem narazil na zajímavý problém. Měl jsem seznam jazykových kurzů ve škole. Ve sloupečcích bylo jméno lektora, jazyk kurzu a nějaký identifikátor kurzu. Vypadalo  to zhruba takto:

 

Obrazek1

Úkolem bylo zjistit, kolik jazyků který lektor vyučuje. A zjistit to kontingenční tabulkou. Zjistit, kolik kurzů lektor vyučuje, nebo které jazyky vyučuje, by zabralo asi tak vteřinu a půl. Pokud ale chci, aby tabulka počítala počty unikátních jazyků a vypadala takto:

 Obrazek6
tak už to taková legrace není. Prošel jsem nějaké návody na webu a následující postup se mi zdá nejsnazší.

Je třeba vytvořit si a do tabulky s daty použít nový sloupec, a tam zapsat takovýto (nebo obdobný) vzorec:

Vysvětlení:

Výstupem je 0 nebo 1, což závisí na dvou kritériích - na jméně lektora a na jazyce. Protože tato kritéria mají ve funkci COUNTIFS mezi sebou vztah AND (tedy musí platit obě), je výstupem funkce COUNTIFS v určitém řádku celkový počet řádků tohoto lektora na tomto jazyce. U buňky Novák / Angličtina je to počet buněk Novák / Angličtina ve sledované oblasti. Všimněte si, že oblast funkce COUNTIFS je díky šikovným absolutnm odkazům "roztahovací" a sama funkce tedy zjišťuje, kolikrát se tato kombinace lektora a jazyka objevila pouze odshora až k příslušnému řádku (tedy ne v celé oblasti, ale jen od buňky nahoru). S použitím samotné funkce COUNTIFS by byl výsledek takovýto:

 Obrazek2
My si ale pro kontingenční tabulku potřebujeme připravit data tak, aby se za každou kombinaci lektora  a jazyka "pričetla" jen jednička.

Proto z funkce COUNTIFS vytvoříme podmínku - její výsledek budeme porovnávat s jedničkou. Funkce tedy bude zobrazovat 1 v případě, že se jedná o první výskyt, a 0 v ostatních případech. Tím zaručíme, že v novém sloupci bude 1 pouze u prvního výskytu, a jinde budou nuly. Výsledek bude vypadat takto:

 Obrazek3
Když pak poslední sloupec dáme do pole hodnot kontingenční tabulky, číslo bude ukazovat, kolik jazyků lektor vyučuje.
Obrazek4
 
 

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

3 Komentářů

  1. Dobrý den,
    pokud by jste netrval na celkové sumě 6, která hlásí celkový počet kombinací lektor-jazyk, ale naopak chtěl vědět celkový počet vyučovaných jazyků, můžete v excelu 2013 (u nižších to nemám ověřeno) použít KT přímo: v řádkovém poli Jméno lektora, v datech Jazyk s nastavením datového pole Jednoznačný počet.

     
  2. Dobrý den,
    nestačilo by odstranit duplicity a pak to jednoduše v KT sečíst?

     
  3. Jo, jasně, to by šlo, ale musel byste to dělat pokaždé znovu, po každé změně dat, a navíc byste tím “znehodnotil” původní tabulku.

     

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář