Kontingenční tabulka počítající unikátní, jedinečné hodnoty

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
 
One comment on “Kontingenční tabulka počítající unikátní, jedinečné hodnoty
  1. Josef Moravec napsal:

    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 zveřejněna.

Vyhledávání

Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
Nezařazené

Nyní otevřený kurz

Jednodenní Excel v praxi

  • 14.3.2017
  • V Praze
Další info / přihláška

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

FB CZ

TOPlist