Общая формула
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Резюме
Содержит ли диапазон повторяющиеся значения? Если вы хотите проверить диапазон (или список) на наличие дубликатов, вы можете сделать это с помощью формулы, которая использует СЧЁТЕСЛИ вместе с СУММПРОИЗВ.
В этом примере список имен находится в диапазоне B3: B11. Если вы хотите проверить этот список, чтобы увидеть, есть ли повторяющиеся имена, вы можете использовать:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Объяснение
Работая изнутри наружу, СЧЁТЕСЛИ сначала подсчитывает каждое значение в B3: B11 в диапазоне B3: B11. Поскольку мы предоставляем диапазон (массив) ячеек для критериев, COUNTIF возвращает в результате массив счетчиков. В показанном примере этот массив выглядит так:
(1; 2; 1; 1; 1; 1; 1; 2; 1)
Далее вычитается 1, что дает такой массив:
(0; 1; 0; 0; 0; 0; 0; 1; 0)
Обратите внимание, что каждая 1 в массиве (т.е. элементы, которые появляются только один раз) была преобразована в ноль.
Затем SUMPRODUCT добавляет элементы в этот массив и возвращает результат, который в данном случае представляет собой число 2, которое затем проверяется на значение> 0.
Каждый раз, когда список содержит дубликаты, в массиве будет как минимум две единицы, суммированные функцией SUMPRODUCT, поэтому окончательный результат ИСТИНА означает, что список содержит дубликаты.
Обработка пустых ячеек
Пустые ячейки в диапазоне приведут к тому, что приведенная выше формула будет давать неверные результаты. Чтобы отфильтровать пустые или пустые ячейки, вы можете использовать следующую альтернативу:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Здесь мы используем список логических выражений "", чтобы обнулить все значения, связанные с пустыми ячейками.
