Generička formula
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Sažetak
Da biste označili prvi duplikat na popisu, možete upotrijebiti formulu koja se temelji na funkciji COUNTIF. Po želji možete naknadne duplikate označiti drugim markerom. U prikazanom primjeru formula u ćeliji C4 je:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Ova je formula kopirana niz stupac, od C4 do C11.
Obrazloženje
U osnovi, ova se formula sastoji od dva skupa funkcije COUNTIF umotana u funkciju IF. Vanjski IF + COUNTIF prvo provjerava pojavljuje li se predmetna vrijednost (B4) više puta na popisu:
=IF(COUNTIF($B$4:$B$11,B4)>1
Ako nije, vanjska funkcija IF vraća konačan rezultat kao prazan niz (""). Ako se vrijednost pojavi više puta, pokrećemo još jednu kombinaciju IF + COUNTIF. Ovaj obavlja posao označavanja duplikata:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Ovaj dio formule koristi referencu za proširivanje ($ B $ 4: B4) koja se širi dok se formula kopira niz stupac. (Prvi B4 u rasponu je apsolutni (zaključan), drugi je relativan, pa se mijenja pri kopiranju formule s popisa).
Imajte na umu da se ovaj dio formule izvršava samo ako je prvi COUNTIF vratio broj veći od 1. Dakle, u svakom retku formula provjerava broj unutar raspona do trenutnog retka. Ako je broj 1, duplikat označavamo s "x", jer je to prvi koji smo vidjeli. Ako nije 1, znamo da mora biti sljedeći duplikat i označavamo s "xx"
Osnovna formula
Da biste prvi duplikat na popisu označili samo s 0 ili 1, možete upotrijebiti ovu uklonjenu formulu koja koristi opseg koji se širi i funkciju COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Ova će formula vratiti 1 samo kad je vrijednost dva puta nađena - prva pojava vratit će nulu:
Za označavanje drugog i svih sljedećih pojavljivanja, formula u F5 gore je:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Napomena: U oba primjera dodavanje nule samo je jednostavan način prisiljavanja vrijednosti TRUE i FALSE na 1 i 0.
Također, upotreba COUNTIFS umjesto COUNTIF omogućuje procjenu vrijednosti u drugim stupcima kao dio testa za duplikate. Svaki dodatni stupac također treba unijeti kao opseg koji se širi.
