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

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:

 

Ú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:

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

• =IF(COUNTIFS($B$1:B2;B2;$C$1:C2;C2)=1;1;0)

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:

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: