Обща формула
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Обобщение
За да маркирате първия дубликат в списък, можете да използвате формула, базирана на функцията COUNTIF. По желание можете да маркирате следващи дубликати с различен маркер. В показания пример формулата в клетка C4 е:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Тази формула е копирана надолу в колоната от C4 до C11.
Обяснение
В основата си тази формула се състои от два набора от функцията COUNTIF, увити във функцията IF. Външният IF + COUNTIF първо проверява дали въпросната стойност (B4) се появява повече от веднъж в списъка:
=IF(COUNTIF($B$4:$B$11,B4)>1
Ако не, външната функция IF връща празен низ ("") като краен резултат. Ако стойността се появи повече от веднъж, стартираме друга комбинация IF + COUNTIF. Това върши работата по маркиране на дубликати:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Тази част от формулата използва разширяваща се справка ($ B $ 4: B4), която се разширява, когато формулата се копира надолу в колоната. (Първият B4 в диапазона е абсолютен (заключен), вторият е относителен, така че се променя при копиране на формулата в списъка).
Не забравяйте, че тази част от формулата се изпълнява само ако първият COUNTIF върне число, по-голямо от 1. И така, на всеки ред формулата проверява броя вътре в диапазона до текущия ред. Ако броят е 1, маркираме дубликата с "x", тъй като това е първият, който сме виждали. Ако не е 1, знаем, че трябва да е последващ дубликат и маркираме с "xx"
Основна формула
За да маркирате първия дубликат в списък само с 0 или 1, можете да използвате тази съкратена формула, която използва разширяващ се диапазон и функцията COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Тази формула ще върне 1 само когато стойност е срещната два пъти - първото появяване ще върне нула:
За да се отбележи второто и всички следващи повторения, формулата във F5 по-горе е:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Забележка: И в двата примера добавянето на нула е просто лесен начин за принуждаване на TRUE и FALSE стойности на 1 и 0.
Също така използването на COUNTIFS вместо COUNTIF дава възможност за оценка на стойности в други колони като част от теста за дубликати. Всяка допълнителна колона също трябва да бъде въведена като разширяващ се диапазон.
