Kontingenční tabulka počítající unikátní, jedinečné hodnoty
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:
Ú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:
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:
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
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.
Dobrý den,
nestačilo by odstranit duplicity a pak to jednoduše v KT sečíst?
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.