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