Обща формула
=SUMPRODUCT(COUNTIF(data,data)-1)>0
Обобщение
Съдържа ли диапазон дублирани стойности? Ако искате да тествате диапазон (или списък) за дубликати, можете да го направите с формула, която използва COUNTIF заедно със SUMPRODUCT.
В примера има списък с имена в диапазона B3: B11. Ако искате да тествате този списък, за да видите дали има дублиращи се имена, можете да използвате:
=SUMPRODUCT(COUNTIF(B3:B11,B3:B11)-1)>0
Обяснение
Работейки отвътре навън, COUNTIF първо получава брой на всяка стойност в 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, ще има поне две единици, така че крайният резултат на TRUE означава, че списъкът съдържа дубликати.
Работа с празни клетки
Празните клетки в диапазона ще доведат до формулата по-горе, за да генерират неправилни резултати. За да филтрирате празни или празни клетки, можете да използвате следната алтернатива:
=SUMPRODUCT((COUNTIF(list,list)-1)*(list""))>0
Тук използваме списъка с логически изрази, за да принудим всички стойности, свързани с празни клетки, да бъдат нулирани.
