Обща формула
=COUNTIF(data,A1)>1
Обобщение
Забележка: Excel съдържа много вградени "предварително зададени настройки" за подчертаване на стойности с условно форматиране, включително предварително зададено за подчертаване на дублирани стойности. Ако обаче искате повече гъвкавост, можете да маркирате дубликати със собствената си формула, както е обяснено в тази статия.
Ако искате да маркирате клетки, които съдържат дубликати в набор от данни, можете да използвате проста формула, която връща TRUE, когато дадена стойност се появи повече от веднъж.
Например, ако искате да маркирате дубликати в диапазона B4: G11, можете да използвате тази формула:
=COUNTIF($B$4:$G$11,B4)>1
Забележка: при условно форматиране е важно формулата да бъде въведена спрямо "активната клетка" в селекцията, която в този случай се приема за B4.
Обяснение
COUNTIF просто отчита броя на появяванията на всяка стойност в диапазона. Когато броят е повече от 1, формулата връща TRUE и задейства правилото.
Когато използвате формула, за да приложите условно форматиране, формулата се оценява спрямо активната клетка в селекцията по време на създаването на правилото. В този случай обхватът, който използваме в COUNTIF, се заключва с абсолютен адрес, но B4 е напълно относителен. Така че правилото се оценява за всяка клетка в диапазона, като B4 се променя и $ B $ 4: $ G $ 11 остават непроменени.
Променлив брой дубликати + именувани диапазони
Вместо да кодирате твърдо числото 1 във формулата, можете да направите препратка към клетка, за да направите броя на дубликатите променлив.
Можете да разширите тази идея и да улесните четенето на формулата, като използвате именувани диапазони. Например, ако назовете G2 „дупчета“, а диапазонът B4: G11 „данни“, можете да пренапишете формулата така:
=COUNTIF(data,B4)>=dups
След това можете да промените стойността в G2 на всичко, което ви харесва, и правилото за условно форматиране ще реагира незабавно, като открои клетката, която съдържа стойности, по-големи или равни на числото, което сте поставили в посочения диапазон "дупчета".
