Обща формула
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Обобщение
Excel съдържа вградена предварителна настройка за подчертаване на дублиращи се стойности с условно форматиране, но работи само на ниво клетка. Ако искате да маркирате цели редове, които са дубликати, ще трябва да използвате вашата собствена формула, както е обяснено по-долу.
Ако искате да маркирате дублиращи се редове в несортиран набор от данни и не искате да добавяте помощна колона, можете да използвате формула, която използва функцията COUNTIFS за преброяване на дублирани стойности във всяка колона от данните.
Например, ако имате стойности в клетките B4: D11 и искате да маркирате цели дублиращи се редове, можете да използвате доста грозна формула:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Именувани диапазони за по-чист синтаксис
Причината горната формула да е толкова грозна е, че трябва да заключим напълно всеки диапазон на колона, след което използваме смесена препратка, за да тестваме всяка клетка във всяка колона. Ако създадете именувани диапазони за всяка колона в данните: col_a, col_b и col_c, формулата може да бъде написана с много по-чист синтаксис:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Обяснение
Във формулата COUNTIFS отчита броя на появяванията на всяка стойност в клетка в нейната „родителска“ колона. По дефиниция всяка стойност трябва да се появи поне веднъж, така че когато броят> 1, стойността трябва да бъде дубликат. Препратките са внимателно заключени, така че формулата ще върне вярно само когато всички 3 клетки в ред се появят повече от веднъж в съответните им колони.
Опцията за помощна колона "изневерява", като комбинира всички стойности в ред заедно в една клетка, използвайки конкатенация. Тогава COUNTIF просто преброява колко пъти тази обединена стойност се появява в колона D.
Помощна колона + конкатенация
Ако нямате нищо против да добавите помощна колона към данните си, можете доста да опростите формулата за условно форматиране. В помощна колона обединете стойности от всички колони. Например, добавете формула в колона E, която изглежда така:
=B4&C4&D4
След това използвайте следната формула в правилото за условно форматиране:
=COUNTIF($E$4:$E$11,$E4)>1
Това е много по-просто правило и можете да скриете помощната колона, ако искате.
Ако имате наистина голям брой колони, можете да използвате функцията TEXTJOIN (Excel 2016 365), за да извършите конкатенация, като използвате диапазон:
=TEXTJOIN(",",TRUE,A1:Z1)
След това можете да използвате COUNTIF, както по-горе.
ПРОДУКТ
Ако използвате версия на Excel преди 2007 г., можете да използвате SUMPRODUCT по следния начин:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
