Обща формула
=SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1
Обобщение
Excel съдържа вградена предварителна настройка за подчертаване на дублиращи се стойности с условно форматиране, но работи само на ниво клетка. Ако искате да намерите и маркирате дублиращи се колони, ще трябва да използвате собствена формула, както е обяснено по-долу.
За да маркирате дублиращи се колони, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата, използвана за подчертаване на дублиращи се колони, е:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1
Обяснение
Този подход използва SUMPRODUCT, за да преброи появата на всяка стойност в таблицата, един по ред. Само когато една и съща стойност се появява на едно и също място във всичките три реда, се генерира брой. За клетка B4 формулата се решава по следния начин:
=SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1 =SUMPRODUCT(((1,1,1,1))*((1,0,1,0))*((1,0,1,0)))>1 =SUMPRODUCT((1,0,1,0))>1 =2>1 =TRUE
Имайте предвид, че препратките към редове са напълно абсолютни, докато препратките към клетки са смесени, като само редът е заключен.
С помощен ред
Ако нямате нищо против да добавите помощен ред към вашите данни, можете доста да опростите формулата за условно форматиране. В помощен ред обединете всички стойности в колоната. След това можете да използвате COUNTIF на този един ред, за да преброите стойности, които се появяват повече от веднъж, и да използвате резултата, за да задействате условно форматиране в цялата колона.
