Ak potrebujeme v programe Excel zistiť počet rôznych hodnôt, môžeme to urobiť dvomi spôsobmi. Buď pomocou výpočtu alebo nám pomôže kontingenčná tabuľka.
Zistenie počtu rôznych položiek pomocou výpočtu.
Ten výpočet bude veľmi jednoduchý. Aby sme ale rozumeli, ako funguje, pripravíme si napríklad takúto tabuľku.
Naša úloha je zistiť, koľko rôznych hodnôt, teda koľko jedinečných miest, sa v tabuľke nachádza. Nezaujíma nás, ktoré sú to, ale chceme zistiť ich počet.
Výsledný výpočet, ku ktorému sa nakoniec dopracujeme, nepotrebuje žiadne pomocné výpočty. My si ale spravíme dva pomocné stĺpce pre porozumenie, čo vlastne robíme. Najskôr musíme vypočítať, koľko krát sa jednotlivé mestá nachádzajú v tabuľke. Je to veľmi jednoduché, použijeme na to funkciu COUNTIF. Ak ju nepoznáte, prečítajte si najskôr článok COUNTIF v Exceli.
Zistili sme počet, koľko krát sa jednotlivé položky nachádzajú v rozsahu údajov. Teda, že Bratislava je v rozsahu 4 krát, Trnava 3 krát a Trenčín jeden krát. Ak by sme z týchto hodnôt spravili súčet, dostali by sme číslo 26, ktoré nám je na nič. Nemá zmysel takéto niečo robiť. My potrebujeme do konečného súčtu každú položku pripočítať len jeden krát.
Tak si dajme trošku matematiky 🙂 . Bratislava je v tabuľke 4 krát. Do výsledného súčtu potrebujeme pripočítať jednotku, čiže každá Bratislava sa bude podieľať 1/4. Podobne Trnava je v tabuľku 3 krát, tak každá „prispeje“ do konečného súčtu 1/3. Myslím (verím), že už rozumieme. Poďme do ďalšieho pomocného stĺpca urobiť 1/počet výskytov.
Pre lepšie porozumenie ešte naformátujeme výsledok na číselný formát Zlomky. Tento krok, samozrejme, nemá vplyv na výsledok.
Tak, je to dúfam jasné. Pri súčte tých zlomkov sa každá hodnota do výsledného počtu zaráta jeden krát. Napríklad Bratislava: 4*1/4=1. Môžeme spraviť kontrolný súčet, a výsledok bude 3. Čiže v našej tabuľke máme 3 rôzne položky, tri rôzne mestá.
Zistenie počtu hodnôt jedným vzorcom
Už som na začiatku článku upozornil, že na to, aby sme v Exceli vypočítali počet jedinečných hodnôt, nepotrebujeme pomocné stĺpce, pomocné výpočty. Tie tam boli iba pre pochopenie, ako sa dopracujeme k výsledku. K výsledku sa dá dospieť aj jedným vzorcom. Čo je ale dôležité? Keďže budeme robiť vzorec poľa (maticový vzorec), tak musíme vedieť, či robíme vo verzii Excel 365 alebo v nejakej staršej verzii. V Excel 365 sa vzorce automaticky správajú ako vzorce dynamického poľa, v starších verziách ich musíme potvrdiť CTRL + SHIFT + ENTER. Preto sa niekedy vzorce poľa nazývajú aj CSE vzorce.
Vzorec je jednoduchý, ideme robiť súčet z 1 / počet výskytov. Keďže vzorec nebudeme kopírovať, nemusíme relatívne odkazy v rozsahu A2:A9 meniť na absolútne odkazy $A$2:$A$9. Vzorec na výpočet počtu rôznych hodnôt vidíte na ďalšom obrázku. Dôležité je, že ak ste vo verzii Excel 365, potvrdíte vzorec klávesou ENTER, ak ste v staršej verzii potvrdíte vzorec CTRL + SHIFT + ENTER.
Výpočet je možné spraviť aj pomocou funkcie SUMPRODUCT. Ak máte záujem, pozrite si jej použitie vo videu na konci článku.
Ide to aj bez vzorcov, počet rozdielnych hodnôt spraví kontingenčná tabuľka.
Všetkých, ktorí sa potešili, že náš príklad môžeme vyriešiť aj bez vzorcov upozorňujem: Áno, môžeme, ale až od verzie Excel 2016. V prípade, že používate staršiu verziu, kontingenčná tabuľka nedokáže zistiť počet rôznych položiek.
Návod je jednoduchý, pomôžeme si opäť aj obrázkami. Najskôr sa nastavíme do tabuľky, kde chceme zistiť počet rôznych položiek. Na karte Vložiť, v skupinovom rámčeku Tabuľky dáme príkaz Kontingenčná tabuľka.
V dialógovom okne Vytvorenie kontingenčnej tabuľky skontrolujeme resp. vyberieme rozsah údajov, ďalej vyberieme umiestnenie (ja som dal na rovnaký hárok od bunky C3). A teraz to najdôležitejšie, začiarkneme políčko Pridať tieto údaje do dátového modelu. Bez tohto zaškrtnutia by nám kontingenčná tabuľka nedokázala spraviť počet rôznych položiek.
Takže kontingenčná tabuľka je vytvorená a my musíme spraviť ešte dve veci. Prvá je, že do oblasti Hodnoty pridáme pole Mesto.
No a druhá vec, keďže pole Mesto nie je číselné pole, tak v hodnotách kontingenčnej tabuľky sa automaticky spraví počet (count). My však nechceme počet hodnôt, ale počet rôznych hodnôt. Takže musíme pole hodnoty nastaviť na iný súhrn. To spravíme jednoducho viacerými spôsobmi. Napríklad, nastavíme sa v tabuľke do poľa hodnoty a na karte Analýza kontingenčnej tabuľky vyberieme príkaz Nastavenie poľa. Tam na karte Zhrnúť hodnoty podľa vyberieme možnosť Počet rôznych položiek.
Hotovo. Výsledok je, samozrejme, opäť 3. Čiže v našej tabuľke sa nachádzajú tri rozdielne hodnoty, teda tri mestá.
Ešte raz návod vo videu
Ako som už v článku písal, na konci je video s návodom. Či už ste sa rozhodli si o počte rôznych výskytov prečítať v článku alebo si to pozrieť na videu, odporúčam si aj sadnúť za počítač, spustiť Excel a všetko si prakticky natrénovať.