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
 

1 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.

Napsat komentář

Vaše emailová adresa nebude publikována.

smazat formulářOdeslat komentář