Určení počtu jedinečných hodnot mezi duplicitními položkami

Programy pro práci s textem, tabulkami, prezentacemi

Moderátor: Moderátoři Živě.cz

Odeslat příspěvekod dred235 6. 9. 2018 12:32

Ahoj,

prosím potřeboval bych poradit funkci v Excelu

Mám tabulku, kde v buňkách jednoho sloupce jsou různé např. webové adresy, z nichž některé se opakují.
Na konci potřebuji součet, ale pouze jedinečných hodnot, když tam bude nějaká adresa např. 3x, tak do toho součtu by to mělo započíst jen jednou.

Na příkladu sloupec buňek A1 až A10, kde jsou různé adresy (příp. prázdné buňky)
a do buňky A11 bych potřeboval nějakou funkci, která mi vyhodí počet jedinečných hodnot z daného seznamu.

A1 - "www.seznam.cz"
A2 - "www.google.com"
A3 - "www.facebook.com"
A4 - "www.seznam.cz"
A5 - "www.novinky.cz"
A6 - "www.novinky.cz"
A7 - "www.novinky.cz"
A8 - prázdná
A9 - prázdná
A10 - "www.seznam.cz"

A11(Počet jedinečných adres) - 4

Předem děkuji za rady.
dred235
Kolemjdoucí

Odeslat příspěvekod Doggg 6. 9. 2018 17:21

Doggg
Junior

Odeslat příspěvekod dred235 7. 9. 2018 08:59

:-D No to jsem samozřejmě udělal v první řadě, ale prostě nedohledal žádné funkční řešení.
Ani vzorce přímo z webu office nejsou v pořádku

https://support.office.com/cs-cz/articl ... 29fbc1e273

Vzorce (zaměření na ten, který by mohl umět určit z textových informací a prázdných buněk) vyhazují,
že někde ve funkci je chyba...

=SUMA(KDYŽ(ČETNOSTI(KDYŽ(DÉLKA(A2:A10)>0,POZVYHLEDAT(A2:A10,A2:A10,0),""), KDYŽ(DÉLKA(A2:A10)>0,POZVYHLEDAT(A2:A10,A2:A10,0),""))>0,1))

nebo anglicky

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

Řešení pomocí filtru není pro můj účel přijatelné - hodnoty v buňkách se budou měnit a potřebuji,
aby daný soubor byl vždy aktuální se správnou hodnotou jedinečných hodnot.

Takže pokud někdo víte, prosím o konkrétní odpověď, jak by měl vzorec vypadat.

Děkuji.
dred235
Kolemjdoucí

Odeslat příspěvekod Stoura 1 7. 9. 2018 09:38

Pro čísla (ale nikoliv texty) funguje tohle:
=SUMA(SIGN(ČETNOSTI(a1:a10;a1:a10)))


-------
Pro texty bych vedle udělal skrytý sloupec - například B, kde bych z B1 rozkopíroval vzorec
=1/countif(a$1..a$10 ;a1)

Součet sloupce dá hledaný počet jedinečných výskytů. Výsledek je dobré zaokrouhlit.

=zaokrouhlit(suma(b1..b10))
Stoura 1
Junior
Uživatelský avatar

Odeslat příspěvekod dred235 7. 9. 2018 12:42

S tím countif by to fungovalo, kdyby v tom primárním sloupci A nebyly prázdné buňky, u nichž mi to vyhodí error "dělení nulou" a součet pak nelze provést...takže takto to taky bohužel není zatím řešení :hm

A přitom tento "problém" na první pohled vypadá jednoduše, kdo první najde řešení má bludišťáka :-))
dred235
Kolemjdoucí

Odeslat příspěvekod zivan 7. 9. 2018 12:50

No tak do toho skryteho sloupce pridas jeste podminku KDYŽ, test na JE.PRÁZDNÉ, pokud je prázdné, tak 0, pokud ne, tak ten vzorec od Stoury 1 :)
Lenovo Moto G5 Plus, Lenovo Thinkpad X220 (12,5" IPS, i5-2410, 16GB RAM, 500GB mSata Samsung 850 EVO + 1TB Samsung HDD) + 29" LG 29UM65 + 22" Eizo S2202W
zivan
Junior

Odeslat příspěvekod Stoura 1 7. 9. 2018 13:26

Omlouvam se, nevyrobil jsem si prazdnou bunku.
Jedno reseni psl Zivan.
Jine reseni je soucet udelat jako
=SUMIF(B1:B10;">0")
coz kupodivu odfiltruje chybne bunky
Stoura 1
Junior
Uživatelský avatar

Odeslat příspěvekod Aluminium 20. 9. 2018 07:53

Ahoj,
ta funkce ze support.office.com funguje, jen je potřeba vyměnit čárky za středníky. Proto to házelo tu hlášku, že je ve vzorci chyba. Plus tam byla mezera před jedním KDYŽ.

Tady je opravený vzorec:
=SUMA(KDYŽ(ČETNOSTI(KDYŽ(DÉLKA(A2:A10)>0;POZVYHLEDAT(A2:A10;A2:A10;0);"");KDYŽ(DÉLKA(A2:A10)>0;POZVYHLEDAT(A2:A10;A2:A10;0);""))>0;1))

A potom ještě je potřeba tu buňku s tím vzorcem dát jako maticový vzorec, tj. dát tam F2 a potom CTRL+SHIFT+ENTER.
Aluminium
Kolemjdoucí
Uživatelský avatar

Odeslat příspěvekod Hlava 23. 7. 2019 17:51

*
Naposledy upravil Hlava dne 23. 7. 2019 19:25, celkově upraveno 1
Hlava
Junior

Odeslat příspěvekod Hlava 23. 7. 2019 19:06

Dá se zde výše uvedený vzorec =SUMA(SIGN(ČETNOSTI(a1:a10;a1:a10)))

přepsat pomocí SUMIFS tak, aby se v něm daly použít další podmínky?
Mi se moc nadaří přijít na to, jak do toho původního vzorce dostat další podmínku.

Prakticky potřebuji dvě:
- aby to vysčítávalo jen údaje max. rok staré
- aby to vysčítávalo údaje jen pro určité IČ (dle sloupce, kde jsou zapsány IČ)
Hlava
Junior


Kdo je online

Uživatelé procházející toto fórum: Žádní registrovaní uživatelé a 0 návštevníků