Üldine valem
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Kokkuvõte
Loendis esimese duplikaadi märkimiseks võite kasutada funktsiooni COUNTIF põhinevat valemit. Soovi korral saate märgistada järgnevad duplikaadid erineva markeriga. Näidatud näites on lahtris C4 valem:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
See valem on veerust alla kopeeritud C4-st C11-ni.
Selgitus
Põhimõtteliselt koosneb see valem funktsiooni COUNTIF kahest komplektist, mis on mähitud IF-funktsiooni. Väline IF + COUNTIF kontrollib kõigepealt, kas kõnealust väärtust (B4) kuvatakse loendis mitu korda:
=IF(COUNTIF($B$4:$B$11,B4)>1
Kui ei, tagastab väline IF-funktsioon lõpliku tulemina tühja stringi (""). Kui väärtus ilmub rohkem kui üks kord, käivitame teise kombinatsiooni IF + COUNTIF. See teeb duplikaatide märgistamise tööd:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
See valemi osa kasutab laienevat viidet ($ B $ 4: B4), mis laieneb valemi veergu kopeerimisel. (Vahemiku esimene B4 on absoluutne (lukustatud), teine on suhteline, nii et see muutub, kui valem loendist alla kopeeritakse).
Pidage meeles, et see valemi osa täidetakse ainult siis, kui esimene COUNTIF tagastab arvu, mis on suurem kui 1. Seega kontrollib valem igal real loendit vahemikus kuni praeguse reani. Kui arv on 1, tähistame duplikaadi tähega "x", kuna see on esimene, mida oleme näinud. Kui see pole 1, teame, et see peab olema järgmine duplikaat ja märkime tähega "xx"
Põhivalem
Loendis esimese duplikaadi märgistamiseks ainult 0 või 1 abil saate kasutada seda eemaldatud valemit, mis kasutab laienevat vahemikku ja funktsiooni COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
See valem tagastab 1 ainult siis, kui väärtus on kaks korda leitud - esimene kord tagastab nulli:
Teise ja kõigi järgnevate esinemiste märgistamiseks on ülaltoodud F5 valem:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Märkus. Mõlemas näites on nulli lisamine lihtsalt lihtne TRUE ja FALSE väärtuste sundimiseks väärtustele 1 ja 0.
Samuti võimaldab COUNTIFS-i kasutamine COUNTIF-i asemel duplikaatide testi osana teiste veergude väärtusi hinnata. Iga täiendav veerg tuleb sisestada ka laieneva vahemikuna.
