Ako spočítať unikátne názvy v bunkách alebo počet hodnôt bez duplicít

Po niekoľkých minútach hľadania na internete sa dostanete na stránky MS OFFICE, kde majú napísaný základný prehľad vzorcov. Určite mi dáte za pravdu, že nevyzerajú vôbec prívetivo a v prípade, že by sme chceli rozšíriť danú podmienku bez duplicít, tak to nemusí byť záležitosť 5 minút (aspoň pre mňa nebola).

Prejdime k príkladu, kde mám vygenerovaný zoznam 57 mien, z ktorých je presne 12 jedinečných, teda neopakujúcich sa.

V tomto prípade bol použitý nasledujúci vzorec:

{=SUM(IF(FREQUENCY(IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""); IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""))>0;1))}

Špeciálne zátvorky znamenajú. že ide o formu maticového počtu (napísaný vzorec aktivujeme pomocou Ctrl + Shift + Enter). Prejdime však na rozbor tohto vzorca:

IF(LEN($G$3:$G$59)>0;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Prvý stĺpec analyzuje bunky v stĺpci takto: Ak je dĺžka textového reťazca nenulová (t.z., že sa v bunke nachádza text), tak vráti pozíciu textu v stĺpci (Keďže meno Bernard sa nachádza ako 2. v poradí v tabuľke, tak funkcia MATCH() vráti číslo dva v oboch, oranžovo označených prípadoch). Ak by bunka bola prázdna, tak funkcia vráti prázdnu hodnotu “”.

{=FREQUENCY($H$3:$H$59;$H$3:$H$59)}

Ďalším kľúčovým prvkom je funkcia FREQUENCY(), ktorá funguje maticovým spôsobom. V nej uvedieme, že chceme pre každú hodnotu vo vypočítanom stĺpci nájsť, koľkokrát sa  v rámci stĺpca nachádza.

{=IF(I3:I59>0;1;0)}

Posledná podmienka už len vyhodnotí výsledok vzorca FREQUENCY() v zmysle, že ak je frekvencia daného čísla v tabuľke nenulová, tak vráti 1 ako jedinečná, inak 0 ako duplicita.

Posledná sčítacia funkcia už len spočíta nenulové hodnoty v predchádzajúcom stĺpci a tým pádom vráti počet mien bez opakovania.

Pokúsme sa pridať ďalšiu obmedzujúcu podmienku. Chceme vedieť počet špecifických mien, ku ktorým prináleží vek nad 30 rokov.

Ak si vyfiltrujeme všetky mená, ktoré majú vek nad 30 rokov a použijeme predchádzajúcu funkciu, zistíme, že máme už len 11 mien (viď template). Avšak my nechceme zbytočne filtrovať a upravovať rozsahy vo vzorci. Chceme pridať alebo pozmeniť len časť predchádzajúceho vzorca.

Pozmeníme preto hneď prvú časť vzorčeka takto:

         Definujeme si podmienku vek nad 30 rokov - IF($F$3:$F$59>30;1;0)

         Definujeme si podmienku neprázdna bunka - IF(LEN($G$3:$G$59)>0;1;0)

Ak obe podmienky platia, ich súčet musí dať dohromady číslo 2. Takže platí:

=IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Červenou farbou je zvýraznená zmena oproti pôvodnej podmienke. Ostatné vzorčeky sa nemenia, avšak aby sme obišli problém s výsledkom #N/A pri niektorých neplatných hodnotách, prerobíme poslednú sčítaciu funkciu na:

SUMIFS($R$3:$R$59;$R$3:$R$59;">0")

Takto spojené podmienky a funkcie budú vyzerať nasledujúco (pri spájaní do jednej funkcie použijeme len SUM() a nie SUMIFS(), lebo sa jedná o maticový počet):

=SUM(IF(FREQUENCY(IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""); 
IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""))>0;1))

Na základe použitého princípu môžeme pridať oveľa väčší rozsah podmienok. Stačí ak hodnoty, vstupujúce do FREQUENCY() sú založené na báze Ak{ (Platí Podmienka 1 = 1 inak 0) + (Platí Podmienka 2 = 1 inak 0) + (Platí Podmienka 3 = 1 inak 0) + … + (Platí Podmienka n = 1 inak 0) } = n tak nájdi pozíciu textu v rámci tabuľky, inak vráť prázdnu hodnotu.

Jedným z ďalších riešení je použiť VBA a naprogramovať si niečo ako vlastný COUNTIFS() bez duplicít. Čo môže byť obsahom nasledujúceho článku. Každopádne template na stiahnutie spolu s odkazom na web a facebook je k dispozícii kliknutím na nasledujúce obrázky: