Obecný vzorec
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
souhrn
Chcete-li označit první duplikát v seznamu, můžete použít vzorec založený na funkci COUNTIF. Volitelně můžete označit následné duplikáty jinou značkou. V ukázaném příkladu je vzorec v buňce C4:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Tento vzorec byl zkopírován dolů do sloupce z C4 na C11.
Vysvětlení
V jádru je tento vzorec složen ze dvou sad funkce COUNTIF zabalených do funkce IF. Vnější IF + COUNTIF nejprve zkontroluje, zda se dotyčná hodnota (B4) v seznamu objeví více než jednou:
=IF(COUNTIF($B$4:$B$11,B4)>1
Pokud ne, vnější funkce IF vrací prázdný řetězec ("") jako konečný výsledek. Pokud se hodnota objeví více než jednou, spustíme další kombinaci IF + COUNTIF. Toto dělá práci s označováním duplikátů:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Tato část vzorce používá rozšiřující odkaz ($ B $ 4: B4), který se rozšiřuje při kopírování vzorce dolů do sloupce. (První B4 v rozsahu je absolutní (uzamčen), druhý je relativní, takže se mění při kopírování vzorce do seznamu).
Nezapomeňte, že tato část vzorce se provede pouze v případě, že první COUNTIF vrátil číslo větší než 1. Vzorec tedy na každém řádku kontroluje počet v rozsahu až po aktuální řádek. Pokud je počet 1, označíme duplikát znakem „x“, protože je to první, který jsme viděli. Pokud to není 1, víme, že to musí být následný duplikát, a označíme to „xx“
Základní vzorec
Chcete-li označit první duplikát v seznamu pouze s 0 nebo 1, můžete použít tento odstraněný vzorec, který používá rozšiřující se rozsah a funkci COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Tento vzorec vrátí 1 pouze v případě, že se hodnota vyskytla dvakrát - první výskyt vrátí nulu:
Chcete-li označit druhý a všechny následující výskyty, je vzorec v F5 výše:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Poznámka: V obou příkladech je přidání nuly jen jednoduchým způsobem, jak vynutit hodnoty PRAVDA a NEPRAVDA na 1 a 0.
Také použití COUNTIFS namísto COUNTIF umožňuje vyhodnotit hodnoty v jiných sloupcích jako součást testu na duplikáty. Každý další sloupec je také třeba zadat jako rozšiřující se rozsah.
