Všeobecný vzorec
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Zhrnutie
Ak chcete označiť prvý duplikát v zozname, môžete použiť vzorec založený na funkcii COUNTIF. Voliteľne môžete následné duplikáty označiť inou značkou. V zobrazenom príklade je vzorec v bunke C4:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Tento vzorec bol skopírovaný dolu v stĺpci od C4 do C11.
Vysvetlenie
Tento vzorec sa v jadre skladá z dvoch množín funkcie COUNTIF zabalených do funkcie IF. Vonkajší IF + COUNTIF najskôr skontroluje, či sa príslušná hodnota (B4) v zozname vyskytuje viackrát:
=IF(COUNTIF($B$4:$B$11,B4)>1
Ak nie, vonkajšia funkcia IF vráti prázdny reťazec ("") ako konečný výsledok. Ak sa hodnota objaví viackrát, spustíme ďalšie kombinácie IF + COUNTIF. Toto robí prácu s označovaním duplikátov:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Táto časť vzorca používa rozširujúci odkaz ($ B $ 4: B4), ktorý sa rozširuje pri kopírovaní vzorca dolu v stĺpci. (Prvá B4 v rozsahu je absolútna (uzamknutá), druhá je relatívna, takže sa mení pri kopírovaní vzorca do zoznamu).
Pamätajte, že táto časť vzorca sa vykoná, iba ak prvý COUNTIF vrátil číslo väčšie ako 1. Takže v každom riadku skontroluje vzorec počet v rozsahu až po aktuálny riadok. Ak je počet 1, označíme duplikát znakom „x“, pretože je to prvý, ktorý sme videli. Ak nie je 1, vieme, že musí ísť o následný duplikát, a označíme ho „xx“
Základný vzorec
Ak chcete označiť prvý duplikát v zozname iba s 0 alebo 1, môžete použiť tento zbavený vzorec, ktorý používa rozširujúci sa rozsah a funkciu COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Tento vzorec vráti hodnotu 1, iba ak sa hodnota vyskytla dvakrát - prvý výskyt vráti nulu:
Na označenie druhého a všetkých nasledujúcich výskytov je vzorec v F5 vyššie:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Poznámka: V obidvoch príkladoch je pridanie nuly iba jednoduchým spôsobom, ako vynútiť TRUE a FALSE hodnoty na 1 a 0.
Tiež použitie COUNTIFS namiesto COUNTIF umožňuje vyhodnotiť hodnoty v iných stĺpcoch ako súčasť testu na duplikáty. Každý ďalší stĺpec je tiež potrebné zadať ako rozširujúci sa rozsah.
